Postgres Plus Advanced Server Oracle Compatibility Developer's Guide
4.11 Collection Methods
Collection methods are functions and procedures that provide useful information about a collection that can aid in the processing of data in the collection. The following sections discuss the collection methods supported by Advanced Server.
4.11.1 COUNT
COUNT is a method that returns the number of elements in a collection. The syntax for using COUNT is as follows:
collection.COUNT
collection is the name of a collection.
For a varray, COUNT always equals LAST.
The following example shows that an associative array can be sparsely populated (i.e., there are “gaps” in the sequence of assigned elements). COUNT includes only the elements that have been assigned a value.
DECLARE
TYPE sparse_arr_typ IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
sparse_arr sparse_arr_typ;
BEGIN
sparse_arr(-100) := -100;
sparse_arr(-10) := -10;
sparse_arr(0) := 0;
sparse_arr(10) := 10;
sparse_arr(100) := 100;
DBMS_OUTPUT.PUT_LINE('COUNT: ' || sparse_arr.COUNT);
END;
The following output shows that there are five populated elements included in COUNT.
COUNT: 5
4.11.2 DELETE
The DELETE method deletes entries from a collection. You can call the DELETE method in three different ways.
Use the first form of the DELETE method to remove all entries from a collection:
collection.DELETE
Use the second form of the DELETE method to remove the specified entry from a collection:
collection.DELETE(subscript)
Use the third form of the DELETE method to remove the entries that are within the range specified by first_subscript and last_subscript (including the entries for the first_subscript and the last_subscript) from a collection.
collection.DELETE(first_subscript, last_subscript)
If first_subscript and last_subscript refer to non-existent elements, elements that are in the range between the specified subscripts are deleted. If first_subscript is greater than last_subscript, or if you specify a value of NULL for one of the arguments, DELETE has no effect.
Note that when you delete an entry, the subscript remains in the collection; you can re-use the subscript with an alternate entry. If you specify a subscript that does not exist in the call to the DELETE method, DELETE does not raise an exception.
The following example demonstrates using the DELETE method to remove the element with subscript 0 from the collection:
DECLARE
TYPE sparse_arr_typ IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
sparse_arr sparse_arr_typ;
v_results VARCHAR2(50);
v_sub NUMBER;
BEGIN
sparse_arr(-100) := -100;
sparse_arr(-10) := -10;
sparse_arr(0) := 0;
sparse_arr(10) := 10;
sparse_arr(100) := 100;
DBMS_OUTPUT.PUT_LINE('COUNT: ' || sparse_arr.COUNT);
sparse_arr.DELETE(0);
DBMS_OUTPUT.PUT_LINE('COUNT: ' || sparse_arr.COUNT);
v_sub := sparse_arr.FIRST;
WHILE v_sub IS NOT NULL LOOP
IF sparse_arr(v_sub) IS NULL THEN
v_results := v_results || 'NULL ';
ELSE
v_results := v_results || sparse_arr(v_sub) || ' ';
END IF;
v_sub := sparse_arr.NEXT(v_sub);
END LOOP;
DBMS_OUTPUT.PUT_LINE('Results: ' || v_results);
END;
COUNT: 5
COUNT: 4
Results: -100 -10 10 100
COUNT indicates that before the DELETE method, there were 5 elements in the collection; after the DELETE method was invoked, the collection contains 4 elements.
4.11.3 EXISTS
The EXISTS method verifies that a subscript exists within a collection. EXISTS returns TRUE if the subscript exists; if the subscript does not exist, EXISTS returns FALSE. The method takes a single argument; the subscript that you are testing for. The syntax is:
collection.EXISTS(subscript)
collection is the name of the collection.
subscript is the value that you are testing for. If you specify a value of NULL, EXISTS returns false.
The following example verifies that subscript number 10 exists within the associative array:
DECLARE
TYPE sparse_arr_typ IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
sparse_arr sparse_arr_typ;
BEGIN
sparse_arr(-100) := -100;
sparse_arr(-10) := -10;
sparse_arr(0) := 0;
sparse_arr(10) := 10;
sparse_arr(100) := 100;
DBMS_OUTPUT.PUT_LINE('The index exists: ' ||
CASE WHEN sparse_arr.exists(10) = TRUE THEN 'true' ELSE 'false' END);
END;
The index exists: true
Some collection methods raise an exception if you call them with a subscript that does not exist within the specified collection. Rather than raising an error, the EXISTS method returns a value of FALSE.
4.11.4 EXTEND
The EXTEND method increases the size of a collection. There are three variations of the EXTEND method. The first variation appends a single NULL element to a collection; the syntax for the first variation is:
collection.EXTEND
collection is the name of a collection.
The following example demonstrates using the EXTEND method to append a single, null element to a collection:
DECLARE
TYPE sparse_arr_typ IS TABLE OF NUMBER;
sparse_arr sparse_arr_typ := sparse_arr_typ(-100,-10,0,10,100);
v_results VARCHAR2(50);
BEGIN
DBMS_OUTPUT.PUT_LINE('COUNT: ' || sparse_arr.COUNT);
sparse_arr.EXTEND;
DBMS_OUTPUT.PUT_LINE('COUNT: ' || sparse_arr.COUNT);
FOR i IN sparse_arr.FIRST .. sparse_arr.LAST LOOP
IF sparse_arr(i) IS NULL THEN
v_results := v_results || 'NULL ';
ELSE
v_results := v_results || sparse_arr(i) || ' ';
END IF;
END LOOP;
DBMS_OUTPUT.PUT_LINE('Results: ' || v_results);
END;
COUNT: 5
COUNT: 6
Results: -100 -10 0 10 100 NULL
COUNT indicates that before the EXTEND method, there were 5 elements in the collection; after the EXTEND method was invoked, the collection contains 6 elements.
The second variation of the EXTEND method appends a specified number of elements to the end of a collection.
collection.EXTEND(count)
collection is the name of a collection.
count is the number of null elements added to the end of the collection.
The following example demonstrates using the EXTEND method to append multiple null elements to a collection:
DECLARE
TYPE sparse_arr_typ IS TABLE OF NUMBER;
sparse_arr sparse_arr_typ := sparse_arr_typ(-100,-10,0,10,100);
v_results VARCHAR2(50);
BEGIN
DBMS_OUTPUT.PUT_LINE('COUNT: ' || sparse_arr.COUNT);
sparse_arr.EXTEND(3);
DBMS_OUTPUT.PUT_LINE('COUNT: ' || sparse_arr.COUNT);
FOR i IN sparse_arr.FIRST .. sparse_arr.LAST LOOP
IF sparse_arr(i) IS NULL THEN
v_results := v_results || 'NULL ';
ELSE
v_results := v_results || sparse_arr(i) || ' ';
END IF;
END LOOP;
DBMS_OUTPUT.PUT_LINE('Results: ' || v_results);
END;
COUNT: 5
COUNT: 8
Results: -100 -10 0 10 100 NULL NULL NULL
COUNT indicates that before the EXTEND method, there were 5 elements in the collection; after the EXTEND method was invoked, the collection contains 8 elements.
The third variation of the EXTEND method appends a specified number of copies of a particular element to the end of a collection.
collection.EXTEND(count, index_number)
collection is the name of a collection.
count is the number of elements added to the end of the collection.
index_number is the subscript of the element that is being copied to the collection.
The following example demonstrates using the EXTEND method to append multiple copies of the second element to the collection:
DECLARE
TYPE sparse_arr_typ IS TABLE OF NUMBER;
sparse_arr sparse_arr_typ := sparse_arr_typ(-100,-10,0,10,100);
v_results VARCHAR2(50);
BEGIN
DBMS_OUTPUT.PUT_LINE('COUNT: ' || sparse_arr.COUNT);
sparse_arr.EXTEND(3, 2);
DBMS_OUTPUT.PUT_LINE('COUNT: ' || sparse_arr.COUNT);
FOR i IN sparse_arr.FIRST .. sparse_arr.LAST LOOP
IF sparse_arr(i) IS NULL THEN
v_results := v_results || 'NULL ';
ELSE
v_results := v_results || sparse_arr(i) || ' ';
END IF;
END LOOP;
DBMS_OUTPUT.PUT_LINE('Results: ' || v_results);
END;
COUNT: 5
COUNT: 8
Results: -100 -10 0 10 100 -10 -10 -10
COUNT indicates that before the EXTEND method, there were 5 elements in the collection; after the EXTEND method was invoked, the collection contains 8 elements.
Note: The EXTEND method cannot be used on a null or empty collection.
4.11.5 FIRST
FIRST is a method that returns the subscript of the first element in a collection. The syntax for using FIRST is as follows:
collection.FIRST
collection is the name of a collection.
The following example displays the first element of the associative array.
DECLARE
TYPE sparse_arr_typ IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
sparse_arr sparse_arr_typ;
BEGIN
sparse_arr(-100) := -100;
sparse_arr(-10) := -10;
sparse_arr(0) := 0;
sparse_arr(10) := 10;
sparse_arr(100) := 100;
DBMS_OUTPUT.PUT_LINE('FIRST element: ' || sparse_arr(sparse_arr.FIRST));
END;
FIRST element: -100
4.11.6 LAST
LAST is a method that returns the subscript of the last element in a collection. The syntax for using LAST is as follows:
collection.LAST
collection is the name of a collection.
The following example displays the last element of the associative array.
DECLARE
TYPE sparse_arr_typ IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
sparse_arr sparse_arr_typ;
BEGIN
sparse_arr(-100) := -100;
sparse_arr(-10) := -10;
sparse_arr(0) := 0;
sparse_arr(10) := 10;
sparse_arr(100) := 100;
DBMS_OUTPUT.PUT_LINE('LAST element: ' || sparse_arr(sparse_arr.LAST));
END;
LAST element: 100
4.11.7 LIMIT
LIMIT is a method that returns the maximum number of elements permitted in a collection. LIMIT is applicable only to varrays. The syntax for using LIMIT is as follows:
collection.LIMIT
collection is the name of a collection.
For an initialized varray, LIMIT returns the maximum size limit determined by the varray type definition. If the varray is uninitialized (that is, it is a null varray), an exception is thrown.
For an associative array or an initialized nested table, LIMIT returns NULL. If the nested table is uninitialized (that is, it is a null nested table), an exception is thrown.
4.11.8 NEXT
NEXT is a method that returns the subscript that follows a specified subscript. The method takes a single argument; the subscript that you are testing for.
collection.NEXT(subscript)
collection is the name of the collection.
If the specified subscript is less than the first subscript in the collection, the function returns the first subscript. If the subscript does not have a successor, NEXT returns NULL. If you specify a NULL subscript, PRIOR does not return a value.
The following example demonstrates using NEXT to return the subscript that follows subscript 10 in the associative array, sparse_arr:
DECLARE
TYPE sparse_arr_typ IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
sparse_arr sparse_arr_typ;
BEGIN
sparse_arr(-100) := -100;
sparse_arr(-10) := -10;
sparse_arr(0) := 0;
sparse_arr(10) := 10;
sparse_arr(100) := 100;
DBMS_OUTPUT.PUT_LINE('NEXT element: ' || sparse_arr.next(10));
END;
NEXT element: 100
4.11.9 PRIOR
The PRIOR method returns the subscript that precedes a specified subscript in a collection. The method takes a single argument; the subscript that you are testing for. The syntax is:
collection.PRIOR(subscript)
collection is the name of the collection.
If the subscript specified does not have a predecessor, PRIOR returns NULL. If the specified subscript is greater than the last subscript in the collection, the method returns the last subscript. If you specify a NULL subscript, PRIOR does not return a value.
The following example returns the subscript that precedes subscript 100 in the associative array, sparse_arr:
DECLARE
TYPE sparse_arr_typ IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
sparse_arr sparse_arr_typ;
BEGIN
sparse_arr(-100) := -100;
sparse_arr(-10) := -10;
sparse_arr(0) := 0;
sparse_arr(10) := 10;
sparse_arr(100) := 100;
DBMS_OUTPUT.PUT_LINE('PRIOR element: ' || sparse_arr.prior(100));
END;
PRIOR element: 10
4.11.10 TRIM
The TRIM method removes an element or elements from the end of a collection. The syntax for the TRIM method is:
collection.TRIM[(count)]
collection is the name of a collection.
count is the number of elements removed from the end of the collection. Advanced Server will return an error if count is less than 0 or greater than the number of elements in the collection.
The following example demonstrates using the TRIM method to remove an element from the end of a collection:
DECLARE
TYPE sparse_arr_typ IS TABLE OF NUMBER;
sparse_arr sparse_arr_typ := sparse_arr_typ(-100,-10,0,10,100);
BEGIN
DBMS_OUTPUT.PUT_LINE('COUNT: ' || sparse_arr.COUNT);
sparse_arr.TRIM;
DBMS_OUTPUT.PUT_LINE('COUNT: ' || sparse_arr.COUNT);
END;
COUNT: 5
COUNT: 4
COUNT indicates that before the TRIM method, there were 5 elements in the collection; after the TRIM method was invoked, the collection contains 4 elements.
You can also specify the number of elements to remove from the end of the collection with the TRIM method:
DECLARE
TYPE sparse_arr_typ IS TABLE OF NUMBER;
sparse_arr sparse_arr_typ := sparse_arr_typ(-100,-10,0,10,100);
v_results VARCHAR2(50);
BEGIN
DBMS_OUTPUT.PUT_LINE('COUNT: ' || sparse_arr.COUNT);
sparse_arr.TRIM(2);
DBMS_OUTPUT.PUT_LINE('COUNT: ' || sparse_arr.COUNT);
FOR i IN sparse_arr.FIRST .. sparse_arr.LAST LOOP
IF sparse_arr(i) IS NULL THEN
v_results := v_results || 'NULL ';
ELSE
v_results := v_results || sparse_arr(i) || ' ';
END IF;
END LOOP;
DBMS_OUTPUT.PUT_LINE('Results: ' || v_results);
END;
COUNT: 5
COUNT: 3
Results: -100 -10 0
COUNT indicates that before the TRIM method, there were 5 elements in the collection; after the TRIM method was invoked, the collection contains 3 elements.
4.11.11 Using the MULTISET UNION Operator
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):
DECLARE
TYPE int_arr_typ IS TABLE OF NUMBER(2);
collection_1 int_arr_typ;
collection_2 int_arr_typ;
collection_3 int_arr_typ;
v_results VARCHAR2(50);
BEGIN
collection_1 := int_arr_typ(10,20,30);
collection_2 := int_arr_typ(30,40);
collection_3 := collection_1 MULTISET UNION ALL collection_2;
DBMS_OUTPUT.PUT_LINE('COUNT: ' || collection_3.COUNT);
FOR i IN collection_3.FIRST .. collection_3.LAST LOOP
IF collection_3(i) IS NULL THEN
v_results := v_results || 'NULL ';
ELSE
v_results := v_results || collection_3(i) || ' ';
END IF;
END LOOP;
DBMS_OUTPUT.PUT_LINE('Results: ' || v_results);
END;
COUNT: 5
Results: 10 20 30 30 40
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:
DECLARE
TYPE int_arr_typ IS TABLE OF NUMBER(2);
collection_1 int_arr_typ;
collection_2 int_arr_typ;
collection_3 int_arr_typ;
v_results VARCHAR2(50);
BEGIN
collection_1 := int_arr_typ(10,20,30);
collection_2 := int_arr_typ(30,40);
collection_3 := collection_1 MULTISET UNION DISTINCT collection_2;
DBMS_OUTPUT.PUT_LINE('COUNT: ' || collection_3.COUNT);
FOR i IN collection_3.FIRST .. collection_3.LAST LOOP
IF collection_3(i) IS NULL THEN
v_results := v_results || 'NULL ';
ELSE
v_results := v_results || collection_3(i) || ' ';
END IF;
END LOOP;
DBMS_OUTPUT.PUT_LINE('Results: ' || v_results);
END;
COUNT: 4
Results: 10 20 30 40
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:
DECLARE
TYPE int_arr_typ IS TABLE OF NUMBER(2);
collection_1 int_arr_typ;
collection_2 int_arr_typ;
collection_3 int_arr_typ;
v_results VARCHAR2(50);
BEGIN
collection_1 := int_arr_typ(10,20,30,30);
collection_2 := int_arr_typ(40,50);
collection_3 := collection_1 MULTISET UNION DISTINCT collection_2;
DBMS_OUTPUT.PUT_LINE('COUNT: ' || collection_3.COUNT);
FOR i IN collection_3.FIRST .. collection_3.LAST LOOP
IF collection_3(i) IS NULL THEN
v_results := v_results || 'NULL ';
ELSE
v_results := v_results || collection_3(i) || ' ';
END IF;
END LOOP;
DBMS_OUTPUT.PUT_LINE('Results: ' || v_results);
END;
COUNT: 5
Results: 10 20 30 40 50
4.11.12 Using the FORALL Statement
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 Oracle compatible. 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:
FORALL i IN 2 .. 5
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.
CREATE TABLE emp_copy(LIKE emp);
DECLARE
TYPE emp_tbl IS TABLE OF emp%ROWTYPE INDEX BY BINARY_INTEGER;
t_emp emp_tbl;
BEGIN
SELECT * FROM emp BULK COLLECT INTO t_emp;
FORALL i IN t_emp.FIRST .. t_emp.LAST
INSERT INTO emp_copy VALUES t_emp(i);
END;
The following example uses a FORALL statement to update the salary of three employees:
DECLARE
TYPE empno_tbl IS TABLE OF emp.empno%TYPE INDEX BY BINARY_INTEGER;
TYPE sal_tbl IS TABLE OF emp.ename%TYPE INDEX BY BINARY_INTEGER;
t_empno EMPNO_TBL;
t_sal SAL_TBL;
BEGIN
t_empno(1) := 9001;
t_sal(1) := 3350.00;
t_empno(2) := 9002;
t_sal(2) := 2000.00;
t_empno(3) := 9003;
t_sal(3) := 4100.00;
FORALL i IN t_empno.FIRST..t_empno.LAST
UPDATE emp SET sal = t_sal(i) WHERE empno = t_empno(i);
END;
SELECT * FROM emp WHERE empno > 9000;
empno | ename | job | mgr | hiredate | sal | comm | deptno
-------+--------+---------+-----+----------+---------+------+--------
9001 | JONES | ANALYST | | | 3350.00 | | 40
9002 | LARSEN | CLERK | | | 2000.00 | | 40
9003 | WILSON | MANAGER | | | 4100.00 | | 40
(3 rows)
The following example deletes three employees in a FORALL statement:
DECLARE
TYPE empno_tbl IS TABLE OF emp.empno%TYPE INDEX BY BINARY_INTEGER;
t_empno EMPNO_TBL;
BEGIN
t_empno(1) := 9001;
t_empno(2) := 9002;
t_empno(3) := 9003;
FORALL i IN t_empno.FIRST..t_empno.LAST
DELETE FROM emp WHERE empno = t_empno(i);
END;
SELECT * FROM emp WHERE empno > 9000;
empno | ename | job | mgr | hiredate | sal | comm | deptno
-------+-------+-----+-----+----------+-----+------+--------
(0 rows)
4.11.13 Using the BULK COLLECT Clause
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.
4.11.13.1 SELECT BULK COLLECT
The BULK COLLECT clause can be used with the SELECT INTO statement as follows. (Refer to Section 4.4.3 for additional information on the SELECT INTO statement.)
SELECT select_expressions BULK COLLECT INTO collection [, ...] FROM ...;
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.
DECLARE
TYPE empno_tbl IS TABLE OF emp.empno%TYPE INDEX BY BINARY_INTEGER;
TYPE ename_tbl IS TABLE OF emp.ename%TYPE INDEX BY BINARY_INTEGER;
TYPE job_tbl IS TABLE OF emp.job%TYPE INDEX BY BINARY_INTEGER;
TYPE hiredate_tbl IS TABLE OF emp.hiredate%TYPE INDEX BY BINARY_INTEGER;
TYPE sal_tbl IS TABLE OF emp.sal%TYPE INDEX BY BINARY_INTEGER;
TYPE comm_tbl IS TABLE OF emp.comm%TYPE INDEX BY BINARY_INTEGER;
TYPE deptno_tbl IS TABLE OF emp.deptno%TYPE INDEX BY BINARY_INTEGER;
t_empno EMPNO_TBL;
t_ename ENAME_TBL;
t_job JOB_TBL;
t_hiredate HIREDATE_TBL;
t_sal SAL_TBL;
t_comm COMM_TBL;
t_deptno DEPTNO_TBL;
BEGIN
SELECT empno, ename, job, hiredate, sal, comm, deptno BULK COLLECT
INTO t_empno, t_ename, t_job, t_hiredate, t_sal, t_comm, t_deptno
FROM emp;
DBMS_OUTPUT.PUT_LINE('EMPNO ENAME JOB HIREDATE ' ||
'SAL ' || 'COMM DEPTNO');
DBMS_OUTPUT.PUT_LINE('----- ------- --------- --------- ' ||
'-------- ' || '-------- ------');
FOR i IN 1..t_empno.COUNT LOOP
DBMS_OUTPUT.PUT_LINE(t_empno(i) || ' ' ||
RPAD(t_ename(i),8) || ' ' ||
RPAD(t_job(i),10) || ' ' ||
TO_CHAR(t_hiredate(i),'DD-MON-YY') || ' ' ||
TO_CHAR(t_sal(i),'99,999.99') || ' ' ||
TO_CHAR(NVL(t_comm(i),0),'99,999.99') || ' ' ||
t_deptno(i));
END LOOP;
END;
EMPNO ENAME JOB HIREDATE SAL COMM DEPTNO
----- ------- --------- --------- -------- -------- ------
7369 SMITH CLERK 17-DEC-80 800.00 .00 20
7499 ALLEN SALESMAN 20-FEB-81 1,600.00 300.00 30
7521 WARD SALESMAN 22-FEB-81 1,250.00 500.00 30
7566 JONES MANAGER 02-APR-81 2,975.00 .00 20
7654 MARTIN SALESMAN 28-SEP-81 1,250.00 1,400.00 30
7698 BLAKE MANAGER 01-MAY-81 2,850.00 .00 30
7782 CLARK MANAGER 09-JUN-81 2,450.00 .00 10
7788 SCOTT ANALYST 19-APR-87 3,000.00 .00 20
7839 KING PRESIDENT 17-NOV-81 5,000.00 .00 10
7844 TURNER SALESMAN 08-SEP-81 1,500.00 .00 30
7876 ADAMS CLERK 23-MAY-87 1,100.00 .00 20
7900 JAMES CLERK 03-DEC-81 950.00 .00 30
7902 FORD ANALYST 03-DEC-81 3,000.00 .00 20
7934 MILLER CLERK 23-JAN-82 1,300.00 .00 10
The following example produces the same result, but uses an associative array on a record type defined with the %ROWTYPE attribute.
DECLARE
TYPE emp_tbl IS TABLE OF emp%ROWTYPE INDEX BY BINARY_INTEGER;
t_emp EMP_TBL;
BEGIN
SELECT * BULK COLLECT INTO t_emp FROM emp;
DBMS_OUTPUT.PUT_LINE('EMPNO ENAME JOB HIREDATE ' ||
'SAL ' || 'COMM DEPTNO');
DBMS_OUTPUT.PUT_LINE('----- ------- --------- --------- ' ||
'-------- ' || '-------- ------');
FOR i IN 1..t_emp.COUNT LOOP
DBMS_OUTPUT.PUT_LINE(t_emp(i).empno || ' ' ||
RPAD(t_emp(i).ename,8) || ' ' ||
RPAD(t_emp(i).job,10) || ' ' ||
TO_CHAR(t_emp(i).hiredate,'DD-MON-YY') || ' ' ||
TO_CHAR(t_emp(i).sal,'99,999.99') || ' ' ||
TO_CHAR(NVL(t_emp(i).comm,0),'99,999.99') || ' ' ||
t_emp(i).deptno);
END LOOP;
END;
EMPNO ENAME JOB HIREDATE SAL COMM DEPTNO
----- ------- --------- --------- -------- -------- ------
7369 SMITH CLERK 17-DEC-80 800.00 .00 20
7499 ALLEN SALESMAN 20-FEB-81 1,600.00 300.00 30
7521 WARD SALESMAN 22-FEB-81 1,250.00 500.00 30
7566 JONES MANAGER 02-APR-81 2,975.00 .00 20
7654 MARTIN SALESMAN 28-SEP-81 1,250.00 1,400.00 30
7698 BLAKE MANAGER 01-MAY-81 2,850.00 .00 30
7782 CLARK MANAGER 09-JUN-81 2,450.00 .00 10
7788 SCOTT ANALYST 19-APR-87 3,000.00 .00 20
7839 KING PRESIDENT 17-NOV-81 5,000.00 .00 10
7844 TURNER SALESMAN 08-SEP-81 1,500.00 .00 30
7876 ADAMS CLERK 23-MAY-87 1,100.00 .00 20
7900 JAMES CLERK 03-DEC-81 950.00 .00 30
7902 FORD ANALYST 03-DEC-81 3,000.00 .00 20
7934 MILLER CLERK 23-JAN-82 1,300.00 .00 10
4.11.13.2 FETCH BULK COLLECT
The BULK COLLECT clause can be used with a FETCH statement. (See Section 4.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.
DECLARE
TYPE emp_tbl IS TABLE OF emp%ROWTYPE INDEX BY BINARY_INTEGER;
t_emp EMP_TBL;
CURSOR emp_cur IS SELECT * FROM emp;
BEGIN
OPEN emp_cur;
FETCH emp_cur BULK COLLECT INTO t_emp;
CLOSE emp_cur;
DBMS_OUTPUT.PUT_LINE('EMPNO ENAME JOB HIREDATE ' ||
'SAL ' || 'COMM DEPTNO');
DBMS_OUTPUT.PUT_LINE('----- ------- --------- --------- ' ||
'-------- ' || '-------- ------');
FOR i IN 1..t_emp.COUNT LOOP
DBMS_OUTPUT.PUT_LINE(t_emp(i).empno || ' ' ||
RPAD(t_emp(i).ename,8) || ' ' ||
RPAD(t_emp(i).job,10) || ' ' ||
TO_CHAR(t_emp(i).hiredate,'DD-MON-YY') || ' ' ||
TO_CHAR(t_emp(i).sal,'99,999.99') || ' ' ||
TO_CHAR(NVL(t_emp(i).comm,0),'99,999.99') || ' ' ||
t_emp(i).deptno);
END LOOP;
END;
EMPNO ENAME JOB HIREDATE SAL COMM DEPTNO
----- ------- --------- --------- -------- -------- ------
7369 SMITH CLERK 17-DEC-80 800.00 .00 20
7499 ALLEN SALESMAN 20-FEB-81 1,600.00 300.00 30
7521 WARD SALESMAN 22-FEB-81 1,250.00 500.00 30
7566 JONES MANAGER 02-APR-81 2,975.00 .00 20
7654 MARTIN SALESMAN 28-SEP-81 1,250.00 1,400.00 30
7698 BLAKE MANAGER 01-MAY-81 2,850.00 .00 30
7782 CLARK MANAGER 09-JUN-81 2,450.00 .00 10
7788 SCOTT ANALYST 19-APR-87 3,000.00 .00 20
7839 KING PRESIDENT 17-NOV-81 5,000.00 .00 10
7844 TURNER SALESMAN 08-SEP-81 1,500.00 .00 30
7876 ADAMS CLERK 23-MAY-87 1,100.00 .00 20
7900 JAMES CLERK 03-DEC-81 950.00 .00 30
7902 FORD ANALYST 03-DEC-81 3,000.00 .00 20
7934 MILLER CLERK 23-JAN-82 1,300.00 .00 10
4.11.13.3 EXECUTE IMMEDIATE BULK COLLECT
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.
4.11.13.4 RETURNING BULK COLLECT
The BULK COLLECT clause can be added to the RETURNING INTO clause of a DELETE, INSERT, or UPDATE command. (See Section 4.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 INSERT, UPDATE, and DELETE commands as described in Sections 4.4.4, 4.4.5, and 4.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 a Postgres Plus Advanced Server extension and is not Oracle compatible.)
The clerkemp table created by copying the emp table is used in the remaining examples in this section as shown below.
CREATE TABLE clerkemp AS SELECT * FROM emp WHERE job = 'CLERK'; SELECT * FROM clerkemp; empno | ename | job | mgr | hiredate | sal | comm | deptno -------+--------+-------+------+--------------------+---------+------+-------- 7369 | SMITH | CLERK | 7902 | 17-DEC-80 00:00:00 | 800.00 | | 20 7876 | ADAMS | CLERK | 7788 | 23-MAY-87 00:00:00 | 1100.00 | | 20 7900 | JAMES | CLERK | 7698 | 03-DEC-81 00:00:00 | 950.00 | | 30 7934 | MILLER | CLERK | 7782 | 23-JAN-82 00:00:00 | 1300.00 | | 10 (4 rows)
The following example increases everyone’s salary by 1.5, stores the employees’ numbers, names, and new salaries in three associative arrays, and finally, displays the contents of these arrays.
DECLARE
TYPE empno_tbl IS TABLE OF emp.empno%TYPE INDEX BY BINARY_INTEGER;
TYPE ename_tbl IS TABLE OF emp.ename%TYPE INDEX BY BINARY_INTEGER;
TYPE sal_tbl IS TABLE OF emp.sal%TYPE INDEX BY BINARY_INTEGER;
t_empno EMPNO_TBL;
t_ename ENAME_TBL;
t_sal SAL_TBL;
BEGIN
UPDATE clerkemp SET sal = sal * 1.5 RETURNING empno, ename, sal
BULK COLLECT INTO t_empno, t_ename, t_sal;
DBMS_OUTPUT.PUT_LINE('EMPNO ENAME SAL ');
DBMS_OUTPUT.PUT_LINE('----- ------- -------- ');
FOR i IN 1..t_empno.COUNT LOOP
DBMS_OUTPUT.PUT_LINE(t_empno(i) || ' ' || RPAD(t_ename(i),8) ||
' ' || TO_CHAR(t_sal(i),'99,999.99'));
END LOOP;
END;
EMPNO ENAME SAL
----- ------- --------
7369 SMITH 1,200.00
7876 ADAMS 1,650.00
7900 JAMES 1,425.00
7934 MILLER 1,950.00
The following example performs the same functionality as the previous example, but uses a single collection defined with a record type to store the employees’ numbers, names, and new salaries.
DECLARE
TYPE emp_rec IS RECORD (
empno emp.empno%TYPE,
ename emp.ename%TYPE,
sal emp.sal%TYPE
);
TYPE emp_tbl IS TABLE OF emp_rec INDEX BY BINARY_INTEGER;
t_emp EMP_TBL;
BEGIN
UPDATE clerkemp SET sal = sal * 1.5 RETURNING empno, ename, sal
BULK COLLECT INTO t_emp;
DBMS_OUTPUT.PUT_LINE('EMPNO ENAME SAL ');
DBMS_OUTPUT.PUT_LINE('----- ------- -------- ');
FOR i IN 1..t_emp.COUNT LOOP
DBMS_OUTPUT.PUT_LINE(t_emp(i).empno || ' ' ||
RPAD(t_emp(i).ename,8) || ' ' ||
TO_CHAR(t_emp(i).sal,'99,999.99'));
END LOOP;
END;
EMPNO ENAME SAL
----- ------- --------
7369 SMITH 1,200.00
7876 ADAMS 1,650.00
7900 JAMES 1,425.00
7934 MILLER 1,950.00
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.
DECLARE
TYPE emp_rec IS RECORD (
empno emp.empno%TYPE,
ename emp.ename%TYPE,
job emp.job%TYPE,
hiredate emp.hiredate%TYPE,
sal emp.sal%TYPE,
comm emp.comm%TYPE,
deptno emp.deptno%TYPE
);
TYPE emp_tbl IS TABLE OF emp_rec INDEX BY BINARY_INTEGER;
r_emp EMP_TBL;
BEGIN
DELETE FROM clerkemp RETURNING empno, ename, job, hiredate, sal,
comm, deptno BULK COLLECT INTO r_emp;
DBMS_OUTPUT.PUT_LINE('EMPNO ENAME JOB HIREDATE ' ||
'SAL ' || 'COMM DEPTNO');
DBMS_OUTPUT.PUT_LINE('----- ------- --------- --------- ' ||
'-------- ' || '-------- ------');
FOR i IN 1..r_emp.COUNT LOOP
DBMS_OUTPUT.PUT_LINE(r_emp(i).empno || ' ' ||
RPAD(r_emp(i).ename,8) || ' ' ||
RPAD(r_emp(i).job,10) || ' ' ||
TO_CHAR(r_emp(i).hiredate,'DD-MON-YY') || ' ' ||
TO_CHAR(r_emp(i).sal,'99,999.99') || ' ' ||
TO_CHAR(NVL(r_emp(i).comm,0),'99,999.99') || ' ' ||
r_emp(i).deptno);
END LOOP;
END;
EMPNO ENAME JOB HIREDATE SAL COMM DEPTNO
----- ------- --------- --------- -------- -------- ------
7369 SMITH CLERK 17-DEC-80 1,200.00 .00 20
7876 ADAMS CLERK 23-MAY-87 1,650.00 .00 20
7900 JAMES CLERK 03-DEC-81 1,425.00 .00 30
7934 MILLER CLERK 23-JAN-82 1,950.00 .00 10
