SELECT BULK COLLECT v14
The following shows the syntax for the BULK COLLECT
clause with the SELECT INTO
statement. For details on the SELECT INTO
statement, see SELECT INTO.
SELECT <select_expressions> BULK COLLECT INTO <collection> [, ...] FROM ...;
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. select_expressions
must match all fields in the target collections in number, order, and type-compatibility.
This example uses 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
This 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