PL/SQL

SamOraTech.com By Sameer Wadhwa

Let us consider a scenario where you update a row in the table and you want to see the results . Till now what you mostly do is, update the table , and then do select to see the updated values. As of Oracle 8 with the RETURNING CLAUSE feature you can immediately see the results of DML without doing select.

Let us see first how it works in the following update :-

Example 1 :-

SQL> set autoprint on;

SQL> var x char(50);

SQL> var y number;

SQL> update scott.emp set ename='SAMEER' where ename='FORD'

RETURNING ename,deptno INTO :x , :y ;

Ename à :X

Deptno à :Y

1 row updated.

SQL> print x

------------------------------------------------------------

SAMEER

SQL> print y

Y

----------

This is one of the way to use the RETURNING CLAUSE to returns the value of fields in the defined variables.

Example 2 :-

set serveroutput on;

Declare

UpdatedEname EMPNO.Ename%Type;

UpdatedDeptno DEPTNO.Deptno%Type;

BEGIN

UPDATE scott.emp

set ename= 'Sameer',

Ename à UpdatedEname

Deptno à UpdatedDeptno

deptno= (select deptno from dept where dname='SALES')

where ename='FORD'

RETURNING

ename,deptno

INTO

UpdatedEname, UpdatedDeptno;

Dbms_output.put_line('value of Updated Emp Name is '||UpdatedEname);

Dbms_output.put_line('value of Updated Deptno is '||UpdatedDeptno);

END;

Listing 1

ora816 SamSQL :> /

value of Updated Empname is Sameer

value of Updated Deptno is 10

PL/SQL procedure successfully completed.

RETURNING CLAUSE IN DYNAMIC SQL.

Example 3:-

create or replace procedure samplsql2

(VarEmpno IN varchar2,

VarSalPercentage IN Number,

UpdatedSalary OUT Number)

sql_stmt varchar2(2000);

BEGIN

/* Making a Dynamic Sql Statement for Oracle version 8.1.7 onwards */

sql_stmt := 'UPDATE Scott.emp

SET sal = (sal+ sal * :a)

where empno = :b

RETURNING sal

INTO :c';

/* Executing a Dynamic SQL for Oracle Version 8.1.7 onwards */

EXECUTE IMMEDIATE sql_stmt USING VarSalPercentage,VarEmpno RETURNING INTO UpdatedSalary;

/* The other way of writing the above execute immediate for backward compatibility */

/* EXECUTE IMMEDIATE sql_stmt USING VarSalPercentage,VarEmpno,OUT UpdatedSalary; */

SAL à :C

VarSalPercentage à :a

VarEmpno à :b

UpdatedSalary à :c

/* VarSalPercentage maps to bind variable :a */

/* VarEmpno maps to bind varibale :b */

/* UpdatedSalary maps to bind variable :C */

/* SAL maps to bind varibale :C */

END;

show errors

Listing 2

Execution

----------

Set serveroutput on

Set Autoprint on

Var x number;

ora816 SamSQL :> execute samplsql2('7876',.1,:x);

PL/SQL procedure successfully completed.

----------

903.5

Explanation :-

EXECUTE IMMEDIATE sql_stmt USING VarSalPercentage , VarEmpno RETURNING INTO UpdatedSalary;

Here in the above Execute immediate Statement , the variable VarSalPercentage will map the first bind variable that is a (percentage of increased salary), VarEmpno will map to b that is empno whose salary we want to increase. After executing the dynamic sql the updated salary will return to variable UpdatedSalary by RETURNING CLAUSE.

USE OF RETURNING WITH DELETE

DECLARE

sql_stmt VARCHAR2(200);

dept_id NUMBER(2) := 30;

old_loc VARCHAR2(13);

BEGIN

sql_stmt :=

'DELETE FROM depttest WHERE deptno = :1 RETURNING loc INTO :2';

EXECUTE IMMEDIATE sql_stmt USING dept_id RETURNING INTO old_loc;

dbms_output.put_line(old_loc);

END;

ora816 SamSQL :> /

CHICAGO

Here Dept_id is mapped to bind variable :1 and old_loc is mapped to bind variable :2. When delete executed with respect to dept_id 30 , the location belongs to this dept_id is returned to the variable old_loc.

One more example to clear about the behavior of RETURNING clause.

create or replace PROCEDURE testplsql

(VDummy1 IN NUMBER,

VDummy2 IN VARCHAR2,

v_name IN VARCHAR2,

V_Out OUT VARCHAR2)

sql_str varchar2(500);

BEGIN

/* Building a SQL String */

sql_str := '

update t

set x = :1,

y = :2,

z = (select ename from emp where ename=:3)

where x = 1

Returning Y into :4' ;

--- EXECUTE IMMEDIATE sql_str using Vdummy1,Vdummy2,v_name,out V_out;

-- OR

EXECUTE IMMEDIATE sql_str using Vdummy1,Vdummy2,v_name RETURN INTO V_out;

/* Vdummy1 maps to bind variable :1 */

/* Vdummy2 maps to bind varibale :2 */

/* V_name maps to bind variable :3 */

/* V_out maps to bind varibale :4 */

commit;

END;

show errors

Listing 3

ora816 SamSQL :> var x char(50);

ora816 SamSQL :> set serveroutput on;

ora816 SamSQL :> set autoprint on;

ora816 SamSQL :> execute testplsql(1,'This is a test by Sameer','Denis',:x);

PL/SQL procedure successfully completed.

X

-------------------------------------------------------------------------------

This is a test by Sameer

Point to know about RETURNING CLAUSE

1. Only DML Statement that returns single row results can use RETURNING CLAUSE.

2. Returning value must match the type of the Return variable.

Conclusion :- Use of Returning clause helps you to increase the performance and reduce the overhead of making unnecessary call to the database to select the updated values.

End of Article
	Copyright © Oracle Techniques All Rights Reserved

Let us see first how it works in the following update :-

SQL> print x

Y

Example 2 :-

Example 3:-

Explanation :-

X

Point to know about RETURNING CLAUSE

Conclusion :- Use of Returning clause helps you to increase the performance and reduce the overhead of making unnecessary call to the database to select the updated values.

End of Article

Copyright © Oracle Techniques All Rights Reserved