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 an unordered set.
Suggested reading: Introduction to PL/SQL Collection
A nested table can be created inside the PL/SQL block 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.
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.
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!
You can DOWNLOAD SQL script and presentation used in the Video and in this article.