PL/SQL - (Nested) tables (Table Of without indexing)

About

A “table of” data type without the index-by clause is a list of elements (collection).

For a table of indexed by, see PL/SQL - Associative Arrays (Index-By Tables) - Table Of Indexed By - Map

Articles Related

Syntax

TYPE table_of_type IS TABLE OF VARCHAR2(30) ; -- default indexed by integer  -- List of Called a Nested table

Syntax

Construction

set serveroutput on
DECLARE
   TYPE listVarchar2 IS TABLE OF VARCHAR2(30);
   myList    listVarchar2;
BEGIN
   IF myList.EXISTS(1) = FALSE THEN
        DBMS_OUTPUT.PUT_LINE('No Constructor');
        myList := listVarchar2(); -- Null Constructor
        myList := listVarchar2('1','2','3','4');
        DBMS_OUTPUT.Put_Line('Loop:');
        FOR i IN myList.FIRST..myList.LAST LOOP
           DBMS_OUTPUT.Put_Line('element(' || i || '): ' || myList(i));
        END LOOP;
    END IF;
END;
/

Loop:
element(1): 1
element(2): 2
element(3): 3
element(4): 4

Methods

Using Collection Methods :

Checking If a Collection Element Exists (EXISTS Method)
Counting the Elements in a Collection (COUNT Method)
Checking the Maximum Size of a Collection (LIMIT Method)
Finding the First or Last Collection Element (FIRST and LAST Methods)
Looping Through Collection Elements (PRIOR and NEXT Methods)
Increasing the Size of a Collection (EXTEND Method)
Decreasing the Size of a Collection (TRIM Method)
Deleting Collection Elements (DELETE Method)
Applying Methods to Collection Parameters

Management

Null

set serveroutput on;
DECLARE
    type t_tabOfInteger is table of Integer;
    l_Integers t_tabOfInteger;
BEGIN
    IF (l_Integers is NULL) THEN
        DBMS_OUTPUT.PUT_LINE('t_tabOfInteger is null');
    ELSE 
        DBMS_OUTPUT.PUT_LINE('t_tabOfInteger is not null');
    END IF;
    l_Integers := null;
END;

t_tabOfInteger is null

Count

set serveroutput on;
DECLARE
    type t_tabOfInteger is table of Integer;
    l_Integers t_tabOfInteger := t_tabOfInteger(1,2,3);
BEGIN
    IF (l_Integers is not NULL) THEN
        DBMS_OUTPUT.PUT_LINE('l_integers has (' || l_Integers.count || ') elements ');
    ELSE
        DBMS_OUTPUT.PUT_LINE('Error: 06531. 00000 -  "Reference to uninitialized collection"');
    END IF;
END;

l_integers has (3) elements

Empty

l_integers not initalized is not empty
l_integers after initalization without element is empty

set serveroutput on;
DECLARE
    type t_tabOfInteger is table of Integer;
    l_Integers t_tabOfInteger;
BEGIN
    IF (l_Integers is empty) THEN
        DBMS_OUTPUT.PUT_LINE('l_integers not initalized is empty');
    ELSE
        DBMS_OUTPUT.PUT_LINE('l_integers not initalized is not empty');
    END IF;
    l_integers:= t_tabOfInteger();
    IF (l_Integers is empty) THEN
        DBMS_OUTPUT.PUT_LINE('l_integers after initalization without element is empty');
    ELSE
        DBMS_OUTPUT.PUT_LINE('l_integers after initalization without element is not empty');
    END IF;
END;

Exist (Element exist)

 
IF n.EXISTS(1) = FALSE  THEN
   DBMS_OUTPUT.PUT_LINE('element 1 doesn''t exist.');
   -- Extend   
   n.extend(1);
   n(1) := 10;
END IF;
   
IF n.EXISTS(1)  THEN
   DBMS_OUTPUT.PUT_LINE('element 1 exists with value ' || n(1) );
   DBMS_OUTPUT.PUT_LINE('Last : ' || n.LAST);
END IF;

MEMBER OF

answer := 4 MEMBER OF nt1; -- false, no element matches

SUBMULTISET

 
 TYPE nested_typ IS TABLE OF NUMBER;
  nt3 nested_typ := nested_typ(2,3,1,3);
  nt4 nested_typ := nested_typ(1,2,4);
answer := nt1 SUBMULTISET OF nt3; -- true, all elements match
answer := nt1 NOT SUBMULTISET OF nt4; -- also true

CARDINALITY

 
howmany := CARDINALITY(nt3); -- number of elements in nt3
howmany := CARDINALITY(SET(nt3)); -- number of distinct elements

Extend

    
DECLARE

   TYPE t_var IS TABLE OF VARCHAR2(30);
   t_vars t_var := t_var('One');
   
BEGIN
  
   -- Add an element at the end
   t_vars.extend(1);
   t_vars(t_vars.LAST) := 'Two';
   
   -- Loop through the elemnt
   FOR i IN t_vars.FIRST..t_vars.LAST
   LOOP
       DBMS_OUTPUT.Put_Line('element(' || i || '): ' || t_vars(i));
   END LOOP;

END;
/

PL/SQL procedure successfully completed.

element(1): One
element(2): Two

Set (Make a set of distinct element, Test if it's a set)

-- Make CollectionVariable a set (no duplicate)
SET(CollectionVariable);
-- Test if CollectionVariable is a set and return a boolean
CollectionVariable IS A SET;
-- Test if CollectionVariable is not a set and return a boolean
CollectionVariable IS NOT A SET;

Example:

SET serveroutput ON;
DECLARE
    
    type t_tabOfInteger IS TABLE OF INTEGER;
    l_Integers t_tabOfInteger := t_tabOfInteger( 1, 2, 2 );
    
    PROCEDURE PRINT(
            l_Integers t_tabOfInteger )
    AS
    
        l_boolean BOOLEAN;
        
    BEGIN
    
        l_boolean := l_Integers is a set;
        DBMS_OUTPUT.PUT_LINE( 'l_Integers is ' || case when l_boolean = false then 'not ' else ''  end || 'a set ' );
        
        FOR i IN l_Integers.first .. l_Integers.last
        LOOP
            DBMS_OUTPUT.PUT_LINE( 'Element: ' || l_Integers( i ) );
        END LOOP;
    
    END;
    
BEGIN

    DBMS_OUTPUT.PUT_LINE( 'Before set, 2 is a duplicate' );
    PRINT( l_Integers );
    l_Integers := SET( l_Integers );
    DBMS_OUTPUT.PUT_LINE( 'After set, their is no duplicate anymore' );
    PRINT( l_Integers );
    
END;

Before set, 2 is a duplicate
l_Integers is not a set 
Element: 1
Element: 2
Element: 2
After set, their is no duplicate anymore
l_Integers is a set 
Element: 1
Element: 2

Loop

Dbms_Output.Put_Line('Loop:');
FOR i IN n.FIRST..n.LAST
LOOP
   Dbms_Output.Put_Line('element(' || i || '): ' || n(i));
END LOOP;

Loop elements:

DECLARE
   TYPE t_var IS TABLE OF VARCHAR2(30);
   t_vars t_var := t_var('One');
BEGIN
   -- Before extends
   DBMS_OUTPUT.PUT_LINE  ('before extends, COUNT is ' || t_vars.COUNT || ' elements');
   DBMS_OUTPUT.PUT_LINE  ('before extends, FIRST is ' || t_vars.FIRST );
   DBMS_OUTPUT.PUT_LINE  ('before extends, LAST is ' || t_vars.LAST);
   IF (t_vars.LIMIT IS NULL ) THEN
    DBMS_OUTPUT.PUT_LINE  ('before extends, LIMIT is NULL');
   END IF;
   
   -- Extends
   DBMS_OUTPUT.PUT_LINE  ('We extend t_vars of 1');
   t_vars.extend(1);
   
   -- After extends
   DBMS_OUTPUT.PUT_LINE  ('after extends, COUNT is ' || t_vars.COUNT);
   DBMS_OUTPUT.PUT_LINE  ('after extends, FIRST is ' || t_vars.FIRST );
   DBMS_OUTPUT.PUT_LINE  ('after extends, LAST is ' || t_vars.LAST );
   IF (t_vars.LIMIT IS NULL ) THEN
    DBMS_OUTPUT.PUT_LINE  ('after extends, LIMIT is NULL');
   END IF;
   
END;
/

before extends, COUNT is 1 elements
before extends, FIRST is 1
before extends, LAST is 1
before extends, LIMIT is NULL
We extend t_vars of 1
after extends, COUNT is 2
after extends, FIRST is 1
after extends, LAST is 2
after extends, LIMIT is NULL

Fetch

PL/SQL - Bulk Collect - Fetch collection of (records|Collection)

Destruction

n := NULL;
IF n.EXISTS(1) = FALSE THEN
   DBMS_OUTPUT.PUT_LINE('element 1 doesn''t exists.');
END IF;

Select

table_collection_expression

The select will work

in SQL if the type is created as schema level. If it's a a package type definition, the table function will not work.
in PLSQL if the type is created at package level.

CREATE TYPE phone AS TABLE OF NUMBER;   
/

SELECT t.COLUMN_VALUE from table(phone(1,2,3)) t;

COLUMN_VALUE
------------
          1
          2
          3

Print

PROCEDURE print_nested_table(the_nt nested_typ) IS
     output VARCHAR2(128);
  BEGIN
     IF the_nt IS NULL THEN
        DBMS_OUTPUT.PUT_LINE('Results: <NULL>');
        RETURN;
     END IF;
     IF the_nt.COUNT = 0 THEN
        DBMS_OUTPUT.PUT_LINE('Results: empty set');
        RETURN;
     END IF;
     FOR i IN the_nt.FIRST .. the_nt.LAST
     LOOP
        output := output || the_nt(i) || ' ';
     END LOOP;
     DBMS_OUTPUT.PUT_LINE('Results: ' || output);
  END;