How To Create Nested Table Inside PL/SQL Block

Welcome to the second tutorial of the PL/SQL Collection series. In this tutorial we will learn the first type of Collection that is “Nested Table”. A table inside a table is the simplest definition one can come up with and it is correct in every way because a table which is embedded inside another table is exactly what the name nested table suggests.

But, if we have to define the collection ‘Nested table’ in a more fancy and technical way then we can say Nested tables are one-dimensional structures that are persistent and unbounded in nature. They are accessible in SQL as well as 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 a random ordered set.

Definition
Nested tables are one-dimensional structures that are persistent and unbounded in nature. They are accessible in SQL as well as 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.

Suggested reading: Introduction to PL/SQL Collection

A nested table can be created inside the PL/SQL block [Tut1] or in database as a collection type object (Schema Object). In case of the former nested table behaves as a one-dimensional array without any index type or any upper limit.

So for the time being let’s concentrate on how to create Nested Table inside PL/SQL block and leave the rest for the next tutorial.

Syntax for Creating Nested Table

DECLRE 
TYPE nested_table_name IS TABLE OF element_type [NOT NULL];

I’ve explained this very syntax in detail in my video tutorial on my YouTube channel. I highly recommend you to refer to that video.

Example: How to Create Nested Table inside a PL/SQL Block?

The following example is only for demonstrating how to create nested table, there is nothing fancy about it.

SET SERVEROUTPUT ON;
DECLARE
   TYPE my_nested_table   IS TABLE OF number;
    var_nt  my_nested_table :=  my_nested_table (9,18,27,36,45,54,63,72,81,90);
BEGIN
   DBMS_OUTPUT.PUT_LINE ('Value Stored at index 1 in NT is ' ||var_nt (1)); 
   DBMS_OUTPUT.PUT_LINE ('Value Stored at index 2 in NT is ' ||var_nt (2));
   DBMS_OUTPUT.PUT_LINE ('Value Stored at index 3 in NT is ' ||var_nt (3));
END;
 /

Above example is a very simple one in which we created a nested table and named it ‘my_nested_table’ (line number 3). In the next line (line number 4) we created an instance of the same collection and used it to initialize the nested table and store some data into it. In the execution section we access the stored data individually using the index number, the same way we used to do in arrays.

Instead of accessing data one by one manually using index we can use loops and cycle through each element of the collection nested table. 

 SET SERVEROUTPUT ON;
 DECLARE
   TYPE my_nested_table   IS TABLE OF number;
   var_nt  my_nested_table :=  my_nested_table (9,18,27,36,45,54,63,72,81,90);
 BEGIN
   FOR i IN 1..var_nt.COUNT
   LOOP
     DBMS_OUTPUT.PUT_LINE ('Value stored at index '||i||'is '||var_nt(i));
   END LOOP;
 END;
 /

That is another example of how to create nested table in which we cycle through the data and display it back to the user using For Loop.

That’s it on how to create nested tables in PL/SQL blocks. Stay tuned as in the next tutorial we will learn how to create nested tables as database collection objects and what all data dictionary views you can use to get the information of the nested tables that are stored in your database.

That’s all. Thanks for reading & have a great day!

RELATED ARTICLESMORE FROM AUTHOR