Choosing Between Nested Tables and Associative Arrays Nested tables and associative arrays differ in persistence and ease of parameter passing. A nested table can be stored in a database column; therefore, you can use a nested table to simplify SQL operations in which you join a single-column table with a larger table.
An associative array cannot be stored in the database. An associative array is appropriate for the following: The number of elements is known in advance. The elements are usually accessed sequentially. When stored in the database, varrays keep their ordering and subscripts. A varray is stored as a single object. If a varray is less than 4 KB, it is stored inside the table of which it is a column; otherwise, it is stored outside the table but in the same tablespace.
You must store or retrieve all elements of a varray at the same time, which is appropriate when operating on all the elements at once. However, this might be impractical for large numbers of elements. Nested tables are a good choice when: Index values are not consecutive.
There is no set number of index values. You must delete or update some elements, but not all elements at once. You would create a separate lookup table, with multiple entries for each row of the main table, and access it through join queries.
Nested table data is stored in a separate store table, a system-generated database table. When you access a nested table, the database joins the nested table with its store table.
This makes nested tables suitable for queries and updates that only affect some elements of the collection. You cannot rely on the order and subscripts of a nested table remaining stable as the nested table is stored in and retrieved from the database, because the order and subscripts are not preserved in the database. Defining Collection Types To create a collection, you define a collection type and then declare variables of that type.
A collection type created at schema level is a standalone stored type. A collection type created inside a package is a packaged type. Collections follow the same scoping and instantiation rules as other types and variables.
Collections are instantiated when you enter a block or subprogram, and cease to exist when you exit. In a package, collections are instantiated when you first reference the package and cease to exist when you end the database session. In the following example, you define a type that stores up to dates: The keys need not be consecutive. An initialization clause is not allowed. There is no constructor notation for associative arrays. Associative arrays can store data using a primary key value as the index, where the key values are not sequential.
Example creates a single element in an associative array, with a subscript of rather than 1. That way, you can pass collections to stored subprograms and from one subprogram to another. Example declares a nested table as a parameter of a packaged subprogram. See Example and Example See Defining and Declaring Records.
To initialize a nested table or varray, you use a constructor, a system-defined function with the same name as the collection type. This function constructs collections from the elements passed to it. You must explicitly call a constructor for each varray and nested table variable. Associative arrays, the third kind of collection, do not use constructors. Constructor calls are allowed wherever function calls are allowed.
Example initializes a nested table using a constructor, which looks like a function with the same name as the collection type. Example initializes a varray using a constructor, which looks like a function with the same name as the collection type. The subscript determines which element is processed. To reference an element, you specify its subscript using the following syntax: The allowed subscript ranges are: For nested tables, For associative arrays with a numeric key, For associative arrays with a string key, the length of the key and number of possible values depends on the VARCHAR2 length limit in the type declaration, and the database character set.
Example shows how to reference an element in a nested table. You can assign the value of an expression to a specific element in a collection using the syntax: Assigning a value to a collection element can raise exceptions, for example: Usually, the subscript must be an integer.
Example shows that collections must have the same data type for an assignment to work. Having the same element type is not enough.
In the same way, assigning the value NULL to a collection makes it atomically null. Comparisons such as greater than, less than, and so on are not allowed. This restriction also applies to implicit comparisons.
If you want to do such comparison operations, you must define your own notion of what it means for collections to be greater than, less than, and so on, and write one or more functions to examine the collections and their elements and return a true or false value.
For nested tables, you can check whether two nested table of the same declared type are equal or not equal, as shown in Example You can also apply set operators to check certain conditions within a nested table or between two nested tables, as shown in Example Because nested tables and varrays can be atomically null, they can be tested for nullity, as shown in Example They cannot be ordered, because there is no greater than or less than comparison.
For example, you can create a nested table of varrays, a varray of varrays, a varray of nested tables, and so on. Example , Example , and Example are some examples showing the syntax and possibilities for multilevel collections. Collection methods make collections easier to use, and make your applications easier to maintain. You invoke a collection method using dot notation. For detailed syntax, see Collection Method Call.
You cannot invoke a collection method from a SQL statement.