The RETURNING INTO
clause specifies the variables in which to store the values returned by the statement to which the clause belongs.
The variables can be either individual variables or collections. If the statement affects no rows, then the values of the variables are undefined.
The static RETURNING INTO
clause belongs to a DELETE, INSERT,
or UPDATE
statement. The dynamic RETURNING INTO clause belongs to the EXECUTE IMMEDIATE
statement.
Note - You cannot use the RETURNING INTO
clause for remote or parallel deletes.variable cannot have the data type BOOLEAN.
The RETURNING INTO
clause with the BULK COLLECT
clause (also called the RETURNING BULK COLLECT INTO
clause) can appear in an INSERT, UPDATE, DELETE,
or EXECUTE IMMEDIATE
statement. With the RETURNING BULK COLLECT INTO
clause, the statement stores its result set in one or more collections.
DROP TABLE emp_temp; CREATE TABLE emp_temp AS SELECT * FROM employees ORDER BY employee_id; DECLARE TYPE NumList IS TABLE OF employees.employee_id%TYPE; enums NumList; TYPE NameList IS TABLE OF employees.last_name%TYPE; names NameList; BEGIN DELETE FROM emp_temp WHERE department_id = 30 RETURNING employee_id, last_name BULK COLLECT INTO enums, names; DBMS_OUTPUT.PUT_LINE ('Deleted ' || SQL%ROWCOUNT || ' rows:'); FOR i IN enums.FIRST .. enums.LAST LOOP DBMS_OUTPUT.PUT_LINE ('Employee #' || enums(i) || ': ' || names(i)); END LOOP; END; /
Result:
Deleted 6 rows: Employee #114: Raphaely Employee #115: Khoo Employee #116: Baida Employee #117: Tobias Employee #118: Himuro Employee #119: Colmenares
In a FORALL
statement, the DML statement can have a RETURNING BULK COLLECT INTO
clause. For each iteration of the FORALL
statement, the DML statement stores the specified values in the specified collections, without overwriting the previous values, as the same DML statement would do in a FOR LOOP
statement.
DROP TABLE emp_temp; CREATE TABLE emp_temp AS SELECT * FROM employees ORDER BY employee_id, department_id; DECLARE TYPE NumList IS TABLE OF NUMBER; depts NumList := NumList(10,20,30); TYPE enum_t IS TABLE OF employees.employee_id%TYPE; e_ids enum_t; TYPE dept_t IS TABLE OF employees.department_id%TYPE; d_ids dept_t; BEGIN FORALL j IN depts.FIRST..depts.LAST DELETE FROM emp_temp WHERE department_id = depts(j) RETURNING employee_id, department_id BULK COLLECT INTO e_ids, d_ids; DBMS_OUTPUT.PUT_LINE ('Deleted ' || SQL%ROWCOUNT || ' rows:'); FOR i IN e_ids.FIRST .. e_ids.LAST LOOP DBMS_OUTPUT.PUT_LINE ( 'Employee #' || e_ids(i) || ' from dept #' || d_ids(i) ); END LOOP; END; /
Result:
Deleted 9 rows: Employee #200 from dept #10 Employee #201 from dept #20 Employee #202 from dept #20 Employee #114 from dept #30 Employee #115 from dept #30 Employee #116 from dept #30 Employee #117 from dept #30 Employee #118 from dept #30 Employee #119 from dept #30
DECLARE TYPE NumList IS TABLE OF NUMBER; depts NumList := NumList(10,20,30); TYPE enum_t IS TABLE OF employees.employee_id%TYPE; e_ids enum_t; TYPE dept_t IS TABLE OF employees.department_id%TYPE; d_ids dept_t; BEGIN FOR j IN depts.FIRST..depts.LAST LOOP DELETE FROM emp_temp WHERE department_id = depts(j) RETURNING employee_id, department_id BULK COLLECT INTO e_ids, d_ids; END LOOP; DBMS_OUTPUT.PUT_LINE ('Deleted ' || SQL%ROWCOUNT || ' rows:'); FOR i IN e_ids.FIRST .. e_ids.LAST LOOP DBMS_OUTPUT.PUT_LINE ( 'Employee #' || e_ids(i) || ' from dept #' || d_ids(i) ); END LOOP; END; /
Result:
Deleted 6 rows: Employee #114 from dept #30 Employee #115 from dept #30 Employee #116 from dept #30 Employee #117 from dept #30 Employee #118 from dept #30 Employee #119 from dept #30
If you like dEexams.com and would like to contribute, you can write your article here or mail your article to admin@deexams.com . See your article appearing on the dEexams.com main page and help others to learn.