PL/SQL Nested tables

       

         Nested tables are user defined data types, which are used to store multiple data items into single unit. Basically nested table is an unconstrained table. Generally, we are not allowed to store Index by table permanently into Database. To overcome them problem oracle 8.0 introduced nested tables, arrays to store permanently in Database using SQL. Nested table is an extension of index by table. Generally in index table, we are not allowed to add or remove indexes, but in nested table we are adding or removing indexes using extend from collection methods.

   By default in nested table indexes start with 1 and also these indexes are conceptions. This is a user defined type.so, we are creating two-step process. Before we are storing data into nested table, array we must initialize through constructor. Here constructor name is same as type name.

Syntax:-  Type typename  is table of data type (size);

               Variable name typename:=typename();

Example:-

DECLARE

   TYPE t1 IS TABLE OF NUMBER (10);

   V_t   t1 := t1 ();

BEGIN

   V_t.EXTEND (300);

   V_t (300) := 60;

   DBMS_OUTPUT.put_line (v_t (300));

END;

          

          

          

Note: - we can also store actual through the constructor itself, in this case oracle server start indexes 1 onwards.

Example:-

DECLARE

   TYPE t1 IS TABLE OF NUMBER (10);

   V_t   t1

            := t1 (10,

                   20,

                   30,

                   40);

BEGIN

   DBMS_OUTPUT.put_line (v_t.FIRST);

   DBMS_OUTPUT.put_line (v_t.LAST);

   DBMS_OUTPUT.put_line (v_t.COUNT);

   DBMS_OUTPUT.put_line (v_t.COUNT);

END;

Using bulk collect:-

Example:-

DECLARE

   TYPE t1 IS TABLE OF VARCHAR2 (20);

   V_t   t1 := t1 ();

BEGIN

   SELECT ename

     BULK COLLECT INTO v_t

     FROM emp;

   FOR I IN v_t.FIRST .. v_t.LAST

   LOOP

      DBMS_OUTPUT.put_line (v_t (i));

   END LOOP;

END;

         

You May Like:

PL/SQL Bulk Collect And Bulk bind

PL/SQL Index by tables (or) PL/SQL tables (or) associative arrays

PL/SQL Ref Cursor

PLSQL Record