Four Oracle Data Constructs with a description of Nested Table
- Nested table: A construct for embedding a sub-table within a table (see the paragraph below for a detailed description)
- ADT: A method for grouping related items together
- DEREF: The command that displays the row that an OID points to
- OID: A persistent Oracle construct to identify a row
- CREATE TYPE: The command to create an object type definition
In the database, a nested table is a column type that stores an unspecified number of rows in no particular order.
When you retrieve a nested table value from the database into a PL/SQL nested table variable, PL/SQL gives the rows consecutive indexes, starting at 1. Using these indexes, you can access the individual rows of the nested table variable. The syntax is variable_name(index). The indexes and row order of a nested table might not remain stable as you store and retrieve the nested table from the database.
The amount of memory that a nested table variable occupies can increase or decrease dynamically, as you add or delete elements.
An uninitialized nested table variable is a null collection. You must initialize it, either by making it empty or by assigning a non-NULL value to it.
Example 3-1 defines a local nested table type, declares a variable of that type (initializing it with a constructor), and defines a procedure that prints the nested table.
The example invokes the procedure three times: After initializing the variable, after changing the value of one element, and after using a constructor to the change the values of all elements. After the second constructor invocation, the nested table has only two elements. Referencing element 3 would raise
Example 3-1 Nested Table of Local Type
TYPE Roster IS TABLE OF VARCHAR2(15); -- nested table type
-- nested table variable initialized with constructor:
names Roster := Roster('D Caruso', 'J Hamil', 'D Piro', 'R Singh');
PROCEDURE print_names (heading VARCHAR2) IS
FOR i IN names.FIRST .. names.LAST LOOP -- For first to last element
names(3) := 'P Perez'; -- Change value of one element
names := Roster('A Jansen', 'B Gupta'); -- Change entire table
Output displayed below
Database Performance Techniques