PL/SQL Nested Tables
Nested Table Types in SQL *Plus
This article describes how to create and use the single dimensional structures known as nested tables. These collections can be used in PL/SQL code as types or as types within Tables. Nested Table Type Topics include: creating a table with this type, inserting a record with this type and updating a record with this type.
PL/SQL Nested Table Using Initialized Values
To get started using a Nested Table, create a code block that declares a nested table type and instantiates a nested table of this created type (Lines 2 & 4). This code iterates through a FOR Loop 3 times to show that the Nested Table has been initialized with 3 Null members. The values of the Nested table are then assigned and another FOR Loop iterates 3 times to output the values of the Nested Table once again.
The results from DBMS_OUTPUT are as expected. 3 lines with no results from the null values and 3 lines with the populated data.
PL/SQL Nested Table Using Extend
The next example declares a type of Nested Table on line 2 and then declares a variable of this new type, Animal_Table on line 3 again. This time, the values of the array and the size of the array are not instantiated. This sample uses the Extend method of the Nested Table to add members within a FOR loop. An IF statement is used to assign the values according to the value of the integer i as the FOR loop is executed. A 2nd loop at line 22 iterates through the nested table to output the values.
The values are output to the screen as expected.
Creating and Using Nested Table Database Types
The next section creates a Nested Table type in the database schema for use as a column in a Table. First, the type is declared and created. Then the Table is created with an ID, Name and an address of the Nested Table type created.
Note the additional code syntax on Line 5 that tells Oracle that the column is a nested table type. Without this code, an ORA-22913 error will be generated and the table will not be created.
An INSERT statement into this table requires a call to the type name, emp_addr, and the data is contained inside of the parentheses on Line 3.
Create a SELECT statement to view the data in the Employees table using the keyword column_value and THE with a subquery. The information appears to be the data entered by the previously executed INSERT statement.
Retrieving Data from a PL/SQL Nested Table Type
To get all of the data for the inserted employee, a UNION ALL query will be used with the hidden rownum column to sort the data. The first part of the query uses a SELECT statement to pull the information from a subquery which utilizes the UNION ALL keywords to join 2 additional SELECT statements.
The first SELECT statement on Line 3 simply selects the number 1 (as a sort order), the rownum column and the empname column. The second SELECT statement on Line 8 selects the number 2, rownum and column_value keyword from the addr Nested Table column type. The query returns the data in the correct order as expected.
To UPDATE the data in the Nested Table column addr, a special syntax using the THE keyword and Subquery is required. This example shows how to update the street address portion of the column.
Using the SELECT syntax with column_value, a quick verification that the UPDATE was successful is displayed.
Add another employee to the database using the format for the Nested Table by calling the Type emp_addr.
A UNION ALL select statement with a couple of adjustments to query only Employee with empid 2 (Lines 7 and 12) will prove that the addition was successful.
