Table of Contents Previous Next


3 Stored Procedure Language : 3.12 Working with Collections

3.12.1 TABLE()
Use the TABLE() function to transform the members of an array into a set of rows. The signature is:
TABLE(collection_value)
collection_value is an expression that evaluates to a value of collection type.
The TABLE() function expands the nested contents of a collection into a table format. You can use the TABLE() function anywhere you use a regular table expression.
The TABLE() function returns a SETOF ANYELEMENT (a set of values of any type). For example, if the argument passed to this function is an array of dates, TABLE() will return a SETOF dates. If the argument passed to this function is an array of paths, TABLE() will return a SETOF paths.
You can use the TABLE() function to expand the contents of a collection into table form:
The MULTISET UNION operator combines two collections to form a third collection. The signature is:
coll_1 MULTISET UNION [ALL | DISTINCT] coll_2
coll_1 and coll_2 specify the names of the collections to combine.
Include the ALL keyword to specify that duplicate elements (elements that are present in both coll_1 and coll_2) should be represented in the result, once for each time they are present in the original collections. This is the default behavior of MULTISET UNION.
Include the DISTINCT keyword to specify that duplicate elements should be included in the result only once.
The following example demonstrates using the MULTISET UNION operator to combine two collections (collection_1 and collection_2) into a third collection (collection_3):
The resulting collection includes one entry for each element in collection_1 and collection_2. If the DISTINCT keyword is used, the results are the following:
The resulting collection includes only those members with distinct values. Note in the following example that the MULTISET UNION DISTINCT operator also removes duplicate entries that are stored within the same collection:
Collections can be used to more efficiently process DML commands by passing all the values to be used for repetitive execution of a DELETE, INSERT, or UPDATE command in one pass to the database server rather than re-iteratively invoking the DML command with new values. The DML command to be processed in such a manner is specified with the FORALL statement. In addition, one or more collections are given in the DML command where different values are to be substituted each time the command is executed.
FORALL index IN lower_bound .. upper_bound
{ insert_stmt | update_stmt | delete_stmt };
index is the position in the collection given in the insert_stmt, update_stmt, or delete_stmt DML command that iterates from the integer value given as lower_bound up to and including upper_bound.
Note: If an exception occurs during any iteration of the FORALL statement, all updates that occurred since the start of the execution of the FORALL statement are automatically rolled back. This behavior is not compatible with Oracle databases. Oracle allows explicit use of the COMMIT or ROLLBACK commands to control whether or not to commit or roll back updates that occurred prior to the exception.
The FORALL statement creates a loop – each iteration of the loop increments the index variable (you typically use the index within the loop to select a member of a collection). The number of iterations is controlled by the lower_bound .. upper_bound clause. The loop is executes once for each integer between the lower_bound and upper_bound (inclusive) and the index is incremented by one for each iteration. For example:
Creates a loop that executes four times – in the first iteration, the index (i) is set to the value 2; in the second iteration, the index is set to the value 3, and so on. The loop executes for the value 5 and then terminates.
The following example creates a table (emp_copy) that is an empty copy of the emp table. The example declares a type (emp_tbl) that is an array where each element in the array is of composite type, composed of the column definitions used to create the table, emp. The example also creates an index on the emp_tbl type.
t_emp is an associative array, of type emp_tbl. The SELECT statement uses the BULK COLLECT INTO command to populate the t_emp array. After the t_emp array is populated, the FORALL statement iterates through the values (i) in the t_emp array index and inserts a row for each record into emp_copy.
The following example uses a FORALL statement to update the salary of three employees:
SQL commands that return a result set consisting of a large number of rows may not be operating as efficiently as possible due to the constant context switching that must occur between the database server and the client in order to transfer the entire result set. This inefficiency can be mitigated by using a collection to gather the entire result set in memory which the client can then access. The BULK COLLECT clause is used to specify the aggregation of the result set into a collection.
The BULK COLLECT clause can be used with the SELECT INTO, FETCH INTO and EXECUTE IMMEDIATE commands, and with the RETURNING INTO clause of the DELETE, INSERT, and UPDATE commands. Each of these is illustrated in the following sections.
The BULK COLLECT clause can be used with the SELECT INTO statement as follows. (Refer to Section 3.4.3 for additional information on the SELECT INTO statement.)
SELECT select_expressions BULK COLLECT INTO collection
If a single collection is specified, then collection may be a collection of a single field, or it may be a collection of a record type. If more than one collection is specified, then each collection must consist of a single field. select_expressions must match in number, order, and type-compatibility all fields in the target collections.
The following example shows the use of the BULK COLLECT clause where the target collections are associative arrays consisting of a single field.
The BULK COLLECT clause can be used with a FETCH statement. (See Section 3.8.3 for information on the FETCH statement.) Instead of returning a single row at a time from the result set, the FETCH BULK COLLECT will return all rows at once from the result set into the specified collection unless restricted by the LIMIT clause.
FETCH name BULK COLLECT INTO collection [, ...] [ LIMIT n ];
If a single collection is specified, then collection may be a collection of a single field, or it may be a collection of a record type. If more than one collection is specified, then each collection must consist of a single field. The expressions in the SELECT list of the cursor identified by name must match in number, order, and type-compatibility all fields in the target collections. If LIMIT n is specified, the number of rows returned into the collection on each FETCH will not exceed n.
The following example uses the FETCH BULK COLLECT statement to retrieve rows into an associative array.
The BULK COLLECT clause can be used with a EXECUTE IMMEDIATE statement to specify a collection to receive the returned rows.
EXECUTE IMMEDIATE 'sql_expression;'
BULK COLLECT INTO collection [,...]
[USING {[bind_type] bind_argument} [, ...]}];
collection specifies the name of a collection.
bind_type specifies the parameter mode of the bind_argument.
A bind_type of IN specifies that the bind_argument contains a value that is passed to the sql_expression.
A bind_type of OUT specifies that the bind_argument receives a value from the sql_expression.
A bind_type of IN OUT specifies that the bind_argument is passed to sql_expression, and then stores the value returned by sql_expression.
bind_argument specifies a parameter that contains a value that is either passed to the sql_expression (specified with a bind_type of IN), or that receives a value from the sql_expression (specified with a bind_type of OUT), or both (specified with a bind_type of IN OUT).
If a single collection is specified, then collection may be a collection of a single field, or a collection of a record type; if more than one collection is specified, each collection must consist of a single field.
The BULK COLLECT clause can be added to the RETURNING INTO clause of a DELETE, INSERT, or UPDATE command. (See Section 3.4.7 for information on the RETURNING INTO clause.)
{ insert | update | delete }
RETURNING { * | expr_1 [, expr_2 ] ...}
BULK COLLECT INTO collection [, ...];
insert, update, and delete are the INSERT, UPDATE, and DELETE commands as described in Sections 3.4.4, 3.4.5, and 3.4.6, respectively. If a single collection is specified, then collection may be a collection of a single field, or it may be a collection of a record type. If more than one collection is specified, then each collection must consist of a single field. The expressions following the RETURNING keyword must match in number, order, and type-compatibility all fields in the target collections. If * is specified, then all columns in the affected table are returned. (Note that the use of * is an Advanced Server extension and is not compatible with Oracle databases.)
The clerkemp table created by copying the emp table is used in the remaining examples in this section as shown below.
The following example deletes all rows from the clerkemp table, and returns information on the deleted rows into an associative array, which is then displayed.

3 Stored Procedure Language : 3.12 Working with Collections

Table of Contents Previous Next