Understanding RETURNING INTO Clause In Oracle Database.

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.

You cannot use the RETURNING INTO clause for remote or parallel deletes.

SQL> DECLARE
  2     nempno   NUMBER;
  3     nsal     NUMBER;
  4     vename   emp.ename%TYPE;
  5  BEGIN
  6     INSERT INTO emp (empno,
  7                      ename,
  8                      job,
  9                      mgr,
 10  hiredate,
 11                      sal,
 12                      comm,
 13                      deptno)
 14          VALUES (1111,
 15                  'TEST-NAME',
 16                  'TEST-JOB',
 17                  NULL,
 18  SYSDATE,
 19                  1000,
 20                  100,
 21                  10)
 22       RETURNING empno
 23            INTO nempno;
 24
 25     DBMS_OUTPUT.PUT_LINE ('Inserted Empno ::: ' || nempno);
 26
 27        UPDATE emp
 28           SET sal = sal + 4000
 29         WHERE empno = nempno
 30     RETURNING sal
 31          INTO nsal;
 32
 33     DBMS_OUTPUT.PUT_LINE ('Updated Sal ::: ' || nsal);
 34
 35     DELETE FROM emp
 36           WHERE empno = nempno
 37       RETURNING ename
 38            INTO vename;
 39
 40     DBMS_OUTPUT.PUT_LINE ('Deleted Ename ::: ' || vename);
 41
 42     COMMIT;
 43  END;
 44  /

--OUTPUT :::
Inserted Empno ::: 1111
Updated Sal ::: 5000
Deleted Ename ::: TEST-NAME

SQL> DECLARE
  2  TYPE NumList IS TABLE OF NUMBER;
  3  depts  NumList := NumList(10,20,30);
  4
  5 TYPE enum_t IS TABLE OF employees.employee_id%TYPE;
  6 e_ids  enum_t;
  7
  8 TYPE dept_t IS TABLE OF employees.department_id%TYPE;
  9 d_ids  dept_t;
  10
  11 BEGIN
  12 FORALL j IN depts.FIRST..depts.LAST
  13   DELETE FROM emp_temp
  14   WHERE department_id = depts(j)
  15   RETURNING employee_id, department_id
  16   BULK COLLECT INTO e_ids, d_ids;
  17
  18 DBMS_OUTPUT.PUT_LINE ('Deleted ' || SQL%ROWCOUNT || ' rows:');
  19
  20 FOR i IN e_ids.FIRST .. e_ids.LAST
  21  LOOP
  22   DBMS_OUTPUT.PUT_LINE ('Employee #' || e_ids(i) || ' from dept #' || d_ids(i));
  23  END LOOP;
  24 END;
  25 /

--OUTPUT :::
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