In this tutorial, you will learn how to declare and initialize Oracle PL/SQL collections (Nested Tables).

Oracle PL/SQL – Nested tables

Nested tables are very similar to the PL/SQL tables, which are known in Oracle as index-by tables. Nested tables extend the functionality of index-by table by adding extra collection methods (known as table attributes for index-by tables) and by adding the ability to store nested tables within a database table, which is why they are called nested tables.

Nested tables can also be manipulated directly using SQL, and have additional predefined exceptions available.

Other than these extra features, the basic functionality of a nested table is the same as a PL/SQL table. A nested table can be thought as off as a database table with two columns-key and value. Like index-tables, nested tables can be sparse, and the keys do not have to be sequential.

Declaring a Nested Table

The syntax for creating a nested table type i

TYPE table_name is TABLE OF table_type [NOT NULL];

where table_nameis the name of the new type, and table_type is the type of each element in the nested table. Table_type can be a built-in type, a user-defined object type, or an expression using % TYPE.

Note

The only syntactic difference between index-by tables and nested tables is the presence of the INDEX BY BINARY_INTEGER clause. If this clause is not present, then the type is a nested table type. If this clause is present, then the type is an index-table type.

The following declarative section of code shows some valid table declarations:

DECLARE
--Define a table type based on an object type
TYPE t_ClassTab IS TABLE OF Classobj;

--A type based on%ROWTYPE
Type t_StudentsTab IS TABLE Of students%ROWTYPE;

--Variables of the above types
v_ClassList t_ClassesTab;
v_StudentList t_StudentsTab;

#plsql #oracle-database #database #sql #oracle #backend #database-design #coding