Use the returning clause -improve performance of application, PL-SQL Programming

Use the RETURNING Clause

Frequently, the application requires information about the row affected by a SQL operation, for illustration, to produce a report or take a subsequent action. The INSERT, UPDATE, & DELETE statements can involve a RETURNING clause that returns column values from the affected row into the PL/SQL variables or host variables. This removes the requirement to SELECT the row after an insert or update, or before a delete. As a result, less network round trips, less server CPU time, smaller number of cursors, and less server memory are needed.

In the illustration below, you update the salary of an employee and at similar time retrieve the employee's name and new salary into the PL/SQL variables.

PROCEDURE update_salary (emp_id NUMBER) IS

name VARCHAR2(15);

new_sal NUMBER;

BEGIN

UPDATE emp SET sal = sal * 1.1

WHERE empno = emp_id

RETURNING ename, sal INTO name, new_sal;

Posted Date: 10/5/2012 4:59:07 AM | Location : United States







Related Discussions:- Use the returning clause -improve performance of application, Assignment Help, Ask Question on Use the returning clause -improve performance of application, Get Answer, Expert's Help, Use the returning clause -improve performance of application Discussions

Write discussion on Use the returning clause -improve performance of application
Your posts are moderated
Related Questions
Change Sql file into CSV for product registration on Magento Project Description: I have a set of files that are in Sql format and could like for a developer to help me with

"Not Enforced" Table Constraints A constraint that is not enforced is not really a constraint within the meaning of the act, but SQL does have such a concept and it needs to b

Create the four tables and populate them with the given data. Answer the following queries in SQL. 1. Get all part-color/part-city combinations. Note: Here and subsequently, the

Cursor Variables Similar to a cursor, cursor variable points to the current row in the result set of a multi-row query. But, dissimilar a cursor, a cursor variable can be opene

Sequential Control Dissimilar to the IF and LOOP statements, the GOTO and NULL statements are not important to the PL/SQL programming. The configuration of PL/SQL is such that th

Enrolment was split - SQL Example shows how relvars IS_CALLED and IS_ENROLLED_ON can be derived from the original ENROLMENT relvar, using projection in the initial assignment

Remote Operations: As the illustration shows below, the PL/SQL subprograms can execute the dynamic SQL statements which refer to the objects on a remote database: PROCEDURE

Need for Dynamic SQL: You need dynamic SQL in the situations as follows: 1) You would like to execute a SQL data definition statement (like CREATE), a data control statemen

SQL Functions The PL/SQL uses all the SQL functions involving the following aggregate functions that summarize the whole columns of the Oracle data: GROUPING, AVG, COUNT, STDDE

The SQL ‘CREATE TABLE' scripts for all the tables you have implemented. Note that your tables must correspond exactly to the ERD you have provided in 1. above, or you will lose ma