SQL statements 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 to transfer the entire result set. You can mitigate the inefficiency 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 EXECUTEIMMEDIATE statements, and with the RETURNING INTO clause of the DELETE, INSERT, and UPDATE statements. Each of these is illustrated in the following topics:
SELECT BULK COLLECT
The BULK COLLECT clause can be used with the SELECT INTO statement as follows:
SELECT select_expressions BULK COLLECT INTO collection
[, ...] FROM ... ;If a single collection is specified, 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, 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 BULKCOLLECT 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 10The 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 10FETCH BULK COLLECT
The BULK COLLECT clause can be used with a FETCH statement. Instead of returning a single row at a time from the result set, FETCH BULK COLLECT will return all rows at a time 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, 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, 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 BULKCOLLECT 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 10EXECUTE 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 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 INOUT).
If a single collection is specified, 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.
RETURNING BULK COLLECT
The BULK COLLECT clause can be added to the RETURNING INTO clause of a DELETE, INSERT, or UPDATE statement:
{ insert | update | delete }
RETURNING { * | expr_1 [, expr_2 ] ...}
BULK COLLECT INTO collection [, ...] ;insert, update, and delete are the INSERT, UPDATE, and DELETE statements as described in INSERT, UPDATE, and DELETE respectively. If a single collection is specified, 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, 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, all columns in the affected table are returned. ( Note that the use of * is an extension for PolarDB for PostgreSQL(Compatible with Oracle) and is not compatible with Oracle databases.)
The clerkemp table created by copying the emp table is used in the remaining examples in this topic.
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.00The 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.00The 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