RETURNING BULK COLLECT v17
Syntax
You can add BULK COLLECT
to the RETURNING INTO
clause of a DELETE
, INSERT
, or UPDATE
command:
{ <insert> | <update> | <delete> } RETURNING { * | <expr_1> [, <expr_2> ] ...} BULK COLLECT INTO <collection> [, ...];
For information on the RETURNING INTO
clause, see Using the RETURNING INTO clause. insert
, update
, and delete
are the same as the INSERT
, UPDATE
, and DELETE
commands described in INSERT, UPDATE, and DELETE.
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 following the RETURNING
keyword must match all fields in the target collections in number, order, and type-compatibility. Specifying *
returns all columns in the affected table.
Note
The use of *
is an EDB Postgres Advanced Server extension and isn't compatible with Oracle databases.
The clerkemp
table created by copying the emp
table is used in the examples that follow.
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)
Examples
This example increases all employee salaries by 1.5, stores the employees’ numbers, names, and new salaries in three associative arrays, and 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
This example 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
This example deletes all rows from the clerkemp
table and returns information on the deleted rows into an associative array. It then displays the array.
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