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
Tautology - Equivalences Rules: If there Tautologies are not all the time as much easy to note as the one above so than we can use these truth tables to be definite that a sta

OUT Mode An OUT parameter returns values to the caller of a subprogram. Within the subprogram, an OUT parameter act like a variable. That means that you can use an OUT formal

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

Biconditional - SQL The biconditional p ↔ q can be expressed in Tutorial D by p = q and the same is true of SQL. The question then arises as to whether, in SQL, p = q is equiv

Implicit Cursor Attributes The Implicit cursor attributes returns the information about the execution of an INSERT, DELETE, UPDATE, or SELECT INTO statement. The cursor attribu

Avoiding Collection Exceptions   In many cases, if you reference a nonexistent collection element, then PL/SQL raises a predefined exception. Consider the illustration shown b

Positional Notation The first procedure call uses the positional notation. The PL/SQL compiler relates the first actual parameter, account, with the first proper parameter, ac

Semidifference via EXCEPT and JOIN - SQL SELECT * FROM (SELECT StudentId FROM IS_CALLED WHERE Name = 'Devinder' EXCEPT DISTINCT CORRESPONDING SELECT StudentId

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

IS NULL Operator The IS NULL operator returns the Boolean value TRUE whenever its operand is null or FALSE if it is not null. The comparisons including the nulls always yield NU