Working with nested tables v16
A nested table is a type of collection that associates a positive integer with a value.
A nested table has the following characteristics:
- You must define a nested table type. After that, you can declare nested table variables of that nested table type. Data manipulation occurs using the nested table variable, also known simply as a table.
- When you declare a nested table variable, the nested table doesn't yet exist. It is a null collection. You must initialize the null table with a constructor. You can also initialize the table by using an assignment statement where the right-hand side of the assignment is an initialized table of the same type.
Initialization of a nested table is mandatory in Oracle but optional in SPL.
- The key is a positive integer.
- The constructor establishes the number of elements in the table. The
EXTENDmethod adds elements to the table. For details, see Collection methods.
Using the constructor to establish the number of elements in the table and using the
EXTENDmethod to add elements to the table are mandatory in Oracle but optional in SPL.
- The table can be sparse. There can be gaps in assigning values to keys.
- An attempt to reference a table element beyond its initialized or extended size results in a
TYPE IS TABLE statement to define a nested table type in the declaration section of an SPL program:
tbltype is an identifier assigned to the nested table type.
datatype is a scalar data type such as
rectype is a previously defined record type.
objtype is a previously defined object type.
You can use the
CREATE TYPE command to define a nested table type that's available to all SPL programs in the database. See SQL reference for more information about the
CREATE TYPE command.
To use the table, you must declare a variable of that nested table type. The following is the syntax for declaring a table variable:
table is an identifier assigned to the nested table.
tbltype is the identifier of a previously defined nested table type.
Initialize a nested table using the nested table type’s constructor:
tbltype is the identifier of the nested table type’s constructor, which has the same name as the nested table type.
expr1, expr2, … are expressions that are type-compatible with the element type of the table. If you specify
NULL, the corresponding element is set to null. If the parameter list is empty, then an empty nested table is returned, which means the table has no elements. If the table is defined from an object type, then
exprn must return an object of that object type. The object can be the return value of a function or the object type’s constructor. Or the object can be an element of another nested table of the same type.
If you apply a collection method other than
EXISTS to an uninitialized nested table, a
COLLECTION_IS_NULL exception is thrown. For details, see Collection methods.
This example shows a constructor for a nested table:
Reference an element of the table using the following syntax:
table is the identifier of a previously declared table.
n is a positive integer.
If the table type of
table is defined from a record type or object type, then
[.element ] must reference an individual field in the record type or attribute in the object type from which the nested table type is defined. Alternatively, you can reference the entire record or object by omitting
This example shows a nested table where it's known that there are four elements:
The following is the output from the example:
This example reads the first 10 employee names from the
emp table, stores them in a nested table, and then displays the results from the table. The SPL code is written to assume that the number of employees to return isn't known beforehand.
An empty table with the constructor
emp_tbl_typ() is created as the first statement in the executable section of the anonymous block. The
EXTEND collection method is then used to add an element to the table for each employee returned from the result set. See Extend.
The following is the output:
This example shows how you can use a nested table of an object type. First, create an object type with attributes for the department name and location:
This anonymous block defines a nested table type whose element consists of the
dept_obj_typ object type. A nested table variable is declared, initialized, and then populated from the
dept table. Finally, the elements from the nested table are displayed.
The parameters that make up the nested table’s constructor,
dept_tbl_typ, are calls to the object type’s constructor
dept_obj_typ. The following is the output from the anonymous block: