Database Interaction with PL/SQL, part 2 - UPDATE with RETURNING clause
(Page 2 of 4 )
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;
Next: TYPE with RECORD declaration >>
More Oracle Articles
More By Jagadish Chatarji
