DML : RETURNING CLAUSE

We can use the RETURNING clause within the DML (UPDATE/DELETE) to grab the modified info
by the statement into variables(collections).

The same can be done in 3 steps

1. Execute the DML statement
2. Query(select) and get the new values into variable.
3. Use the variables in the code.

This can be avoided by using the RETURNING Clause.

1.Execute the DML Statement and Grab the modified values into Variables on the fly.
2. Use the variables in the code.

Note: 

If the Update/Delete affects only one row, we can use the RETURNING Clause and grab the modified data into the Scalar variables.

If the Update/Delete affects multiple rows, we need to use BULK COLLECT and COLLECTION to grab the modified data using the RETURNING Clause.

Example:

Declare

type emp_rec is record (empno scott.emp.empno%type,sal scott.emp.sal%type);

type emp_rec_t is table of emp_rec;

old_sal_rec_t emp_rec_t := emp_rec_t();

new_sal_rec_t emp_rec_t := emp_rec_t();

Begin

select empno,sal bulk collect into old_sal_rec_t from scott.emp where deptno=10;

update scott.emp set sal=sal*1.1 where deptno=10 returning empno,sal bulk collect into new_sal_rec_t;

dbms_output.put_line('Number of employees got the pay hike:'||SQL%ROWCOUNT);

for i in old_sal_rec_t.first..old_sal_rec_t.last

loop

dbms_output.put_line(old_sal_rec_t(i).empno || ':' || old_sal_rec_t(i).sal);

dbms_output.put_line(new_sal_rec_t(i).empno || ':' || new_sal_rec_t(i).sal);

end loop;

End;

/