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
EXECUTE Privilege To call an invoker-rights routine straightforwardly, the users should have the EXECUTE privilege on that routine. By yielding the privilege, you permit a user

Write a query to find academics that are authors and that have only ever coauthored papers with authors from institutes in the same state as their own. List their academic number,

Project Description: I want to rebuild it and add better content to it It will include up to 5 forms The data will be saved on SQL server and the data access layer could b

Closing a Cursor The CLOSE statements disable the cursor, and the result set becomes undefined. An illustration of the CLOSE statement as shown: CLOSE c1;

query to Find the account numbers of all customers whose balance is more than 10,000 $

LOB Types The large object (LOB) datatypes like BFILE, BLOB, CLOB, and NCLOB store the blocks of unstructured data (like graphic images, text, video clips, and sound waveforms)

Functions   The function is a subprogram that calculates a value. The Functions and procedures are structured similar, except that the functions have a RETURN clause. You can

Parameter and Keyword Description: collection_name: This keyword identifies the index-by table, nested table, or varray formerly declared within the present scope. cu

Deleting Objects You can use the DELETE statement to eradicate objects from an object table. To eradicate objects selectively, you use the WHERE clause, as shown below: BEG

DBMS_OUTPUT: The Package DBMS_OUTPUT enables you to display output from the PL/SQL subprograms and blocks, that makes it easier to test and debug them. The procedure put_ line