SELECT BULK COLLECT v13
The BULK COLLECT
clause can be used with the SELECT INTO
statement as follows. (Refer to SELECT INTO 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