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 and FETCH INTO 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 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
The BULK COLLECT clause can be used with a FETCH statement. (See Section 4.7.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.9.4.3 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
