RETURNING BULK COLLECT v13
The BULK COLLECT
clause can be added to the RETURNING INTO
clause of a DELETE, INSERT
, or UPDATE
command. (See Using the RETURNING INTO Clause for information on the RETURNING INTO
clause.)
{ <insert> | <update> | <delete> } RETURNING { * | <expr_1> [, <expr_2> ] ...} BULK COLLECT INTO <collection> [, ...];
insert, update
, and delete
are the INSERT, UPDATE
, and DELETE
commands as described in INSERT, UPDATE, and DELETE, 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 an Advanced Server extension and is not compatible with Oracle databases.)
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