UPDATE with RETURNING clause - Oracle

UPDATE in PL/SQL can be enhanced with the RETURING clause to achieve wonderful results. This clause actually eliminates the use of the SELECT statement after an UPDATE to check the back table (or row) and present the updated result to the user. Let me explain this in more detail.

Let us consider that we need to increase the salary of an employee by 500 and return the updated salary to the user. Generally, in this situation, we would write our program as the following:

declare

v_sal emp.sal%type;

v_empno emp.empno%type := &empno;

begin

update emp set sal = sal + 500

where empno = v_empno;

select sal into v_sal from emp

where empno = v_empno;

dbms_output.put_line ('Salary increased to ' || v_sal);

end;

This is a very old and traditional way of writing the program. We can observe from the above program that there will be two round trips to the database. One round trip is for the UPDATE statement, and the other is for the SELECT statement. We can turn those two round trips to the database into one by using the RETURNING clause. The following program illustrates that.

declare

v_sal emp.sal%type;

v_empno emp.empno%type := &empno;

begin

update emp set sal = sal + 500

where empno = v_empno

RETURNING sal INTO v_sal;

dbms_output.put_line ('Salary increased to ' || v_sal);

end;

I hope the above program is self-explanatory. But, what if I want a larger number of values to be returned? We can still use the same clause, but separated by commas, as shown in the following example.

Declare

v_ename emp.ename%type;

v_sal emp.sal%type;

v_empno emp.empno%type := &empno;

begin

update emp set sal = sal + 500

where empno = v_empno

RETURNING ename,sal INTO v_ename, v_sal;

dbms_output.put_line ('Salary increased to ' || v_sal || ' for ' || v_ename);

end;