Collections in PL/SQL – Part Two

2. Nested Table

Nested Table is an another collection type. The difference from associative array I mentioned earlier is that need to initialize before using it. Besides nested tables can use as table columns.

CREATE [ OR REPLACE ] TYPE type_name AS | IS TABLE OF element_datatype [ NOT NULL ];

Example;

    DECLARE
        TYPE TEmpLstType IS TABLE OF VARCHAR2 (20);
        emp_list TEmpLstType := TEmpLstType ( );  //  must initialize
    BEGIN
       emp_list.EXTEND;
       emp_list (1) := ‘Mehmet’;
       emp_list.EXTEND (2);
       emp_list (1) := ‘John’;
       emp_list (2) := ‘David’;
       FOR rw IN emp_list.FIRST .. emp_list.LAST
       LOOP
           DBMS_OUTPUT.PUT_LINE (emp_list (l_row));
       END LOOP;
    END;

With EXTEND method, it is opened an empty room in the emp_list nested table.
You can use nested tables as table columns. For example,

       CREATE TYPE TEmpAddresType AS TABLE OF VARCHAR2(100);
    CREATE TABLE employees (
        id NUMBER,
        name VARCHAR2(50),
        address_list TEmpAddresType )
    NESTED TABLE address_list STORE AS address_list_ntable;

The address list will store in the address_list_ntable. address_list_ntable cannot query. If you do, 
          select * from address_list_table;

then oracle will raise “ORA-22812: cannot reference nested table column’s storage table”.

3. VARRAY (variable-sized arrays)

This type are alike nested table. The difference from nested table is that using for small arrays. It can also use as table columns like nested table.

   CREATE [ OR REPLACE ] TYPE type_name AS | IS VARRAY (max_elements) OF element_datatype    [  NOT NULL ];

     CREATE TYPE TEmpAddresVtype IS VARRAY (2) OF VARCHAR2 (100); // have max 100 elements
    CREATE TYPE TEmpBirthdateVtype IS VARRAY (1) OF VARCHAR2 (100);
    CREATE TABLE employees (
         name VARCHAR2(100),
         address_lst TEmpAddresVtype,
         birthdate_lst TEmpBirthdateVtype );

    DECLARE
        address_list   TEmpAddresVtype := TEmpAddresVtype ( );
        birthdate_list TEmpBirthdateVtype := TEmpBirthdateVtype ( );
    BEGIN
        address_list.EXTEND (2);
        address_list (1) := ‘MASLAK/ISTANBUL’;
        address_list (2) := ‘ZEYTINBURNU/ISTANBUL’;
        birthdate_list.EXTEND;
        birthdate_list (1) := ‘01.02.1979′;
        INSERT INTO employees ( name, address_lst, birthdate_lst )
        VALUES ( ‘ahmet’, address_list, birthdate_list );
    END;    

Finally VARRAY and Nested Table are same. But VARRAY is for small arrays and nested table is for big arrays because of storing array elements in background table.