FETCH BULK COLLECT v15
You can use the BULK COLLECT
clause with a FETCH
statement. Instead of returning a single row at a time from the result set, the FETCH BULK COLLECT
returns 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> ];
For information on the FETCH
statement, see Fetching rows from a cursor.
If you specify a single collection, then collection
can be a collection of a single field, or it can be a collection of a record type. If you specify more than one collection, then each collection
must consist of a single field. The expressions in the SELECT
list of the cursor identified by name
must match all fields in the target collections in number, order, and type-compatibility. If you specify LIMIT n
, the number of rows returned into the collection on each FETCH
doesn't exceed n
.
This 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