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
INSERT Command in SQL Loosely speaking, INSERT takes the rows of a given source table and adds them to the specified target table, retaining all the existing rows in the targ


An analyst in the quality assurance office reviews the time lapse between receiving an order and shipping an order. Any orders that have not been shipped within a day of the order

Use Bulk Binds If SQL statements execute inside a loop using the collection elements as bind variables, context switching between the PL/SQL & SQL engines can slow down the ex

At times, customers make mistakes in submitting their orders and call to cancel the order. Brewbean's wants to create a trigger that automatically updates the stock level of all pr

Passing Cursor Parameters You use the OPEN statement to pass the parameters to a cursor. Unless you want to accept the default values, each proper parameter in the cursor decl

Consider the schema for FreeChecking Bank, that we designed given below. Translate the given ER schema into SQL CREATE TABLE statements (indicating primary key, unique and foreign

Semidifference via NOT IN and a subquery SELECT StudentId FROM IS_CALLED WHERE Name = 'Devinder' AND StudentId NOT IN (SELECT StudentId FROM IS_ENROLLED_ON WHER

Package Specification The package specifications contain the public declarations. The scopes of these declarations are local to your database representation and global to the

WHILE-LOOP The WHILE-LOOP statement relates a condition with the series of statements enclosed by the keywords LOOP and END LOOP, as shown: WHILE condition LOOP sequence_of_sta