Home arrow Oracle arrow Page 2 - Database Interaction with PL/SQL, part 2

UPDATE with RETURNING clause - Oracle

This article picks up where part one left off. You will learn more about how to use %ROWTYPE and %TYPE, and be introduced to TYPE, RECORD, and TABLE declarations in PL/SQL.

TABLE OF CONTENTS:
  1. Database Interaction with PL/SQL, part 2
  2. UPDATE with RETURNING clause
  3. TYPE with RECORD declaration
  4. Accessing more than one row in PL/SQL (TYPE with TABLE)
By: Jagadish Chatarji
Rating: starstarstarstarstar / 20
May 31, 2005

print this article
SEARCH DEV SHED

TOOLS YOU CAN USE

advertisement

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;



 
 
>>> More Oracle Articles          >>> More By Jagadish Chatarji
 

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort
   

ORACLE ARTICLES

- Oracle Java Security Woes Continue
- Oracle's New IaaS Cloud Option: There's a Ca...
- Oracle Acquires Eloqua to Boost Cloud Presen...
- Choosing Innovation: Oracle Survey Insights
- Oracle Fixes Privilege Escalation Bug
- Oracle`s Communications Service Availability...
- Oracle Releases Exalytics, Taleo Plans
- Oracle Releases Communications Network Integ...
- Oracle Releases Communications Data Model 11...
- Oracle Releases PeopleSoft PeopleTools 8.52
- Oracle Integrates Cloudera Apache Distro, My...
- Oracle Releases MySQL 5.5.18
- Oracle Announces NoSQL Database Availability
- Sorting Database Columns With the SELECT Sta...
- Retrieving Table Data with the LIKE Operator

Developer Shed Affiliates

 


Dev Shed Tutorial Topics: