How to create nested table in PL/SQL block

 What are nested table?

   Ø A table inside a table or a table embedded with another table known as nested table.

 Definition of nested table:

   Ø Nested table are one dimensional structure which are persistent and unbounded in nature.

   Ø They are accessible in SQL and PL/SQL and can be used in tables, records and object definitions. Since it is an unbounded PL/SQL collection hence it can hold any number of elements in an unordered set.

Syntax:

Creating Nested table:

Declare

Type nested_table_name is table of element_type [ NOT NULL];

Explanation:

 v We should create nested table in DECLARATION section.

  v To create nested table we use TYPE statement which is followed by the TYPE keyword.

  v NESTED_TABLE_NAME is the Name of your Nested table which is user defined.

  v IS TABLE OF resource free and followed by ELEMENT_TYPE.

  v ELEMENT_TYPE should to be primitive data type or user defined data type.

Example: 1

1. DECLARE

      TYPE ex_nested_table IS TABLE OF VARCHAR2 (50);

       v_nt   ex_nested_table := ex_nested_table ('my', 'nested', 'table');

   BEGIN

         for i in 1..v_nt.count

         loop

          DBMS_OUTPUT.put_line ('The string '||i||'is'||' '||v_nt (i));

         end loop;

   END;

Output:

Example: 2

1. DECLARE

      TYPE new_nested_table IS TABLE OF NUMBER;

      var_nt   new_nested_table

                        := new_nested_table (2, 4, 6, 8, 10, 12, 14, 16, 18, 20);

   BEGIN

      DBMS_OUTPUT.put_line ('Index 1 value is :' || var_nt (1));

   END;

Output:

Example: 3

1. DECLARE

      TYPE new_nested_table IS TABLE OF NUMBER;

    

      var_nt   new_nested_table

                        := new_nested_table (2, 4, 6, 8, 10, 12, 14, 16, 18, 20);

   BEGIN

      for i in 1..var_nt.count

      LOOP

         DBMS_OUTPUT.put_line ('The value of ' || i ||'is'||' '||  var_nt (i));

      END LOOP;

   END;

Output:

 

 

 

 

 

 

Share: