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
Literature review

Organi c Evolution Evolution is a slow continuous, irreversible and natural process of change to give rise to advance and diverse forms of life i.e. formation of new specie

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

1. a. Write a trigger that fires when a part's price is updated. The trigger will write a record into a table called PriceUpdates. The record should contain the information of

Problem: (a) Define the following terms: (i) data mining. (ii) OLAP. (b) Differentiate between snowflake schema and star schema. Support your answer with appropriate

Overriding Default Locking By default, the Oracle locks the data structures for you automatically. Though, you can request exact data locks on rows or tables when it is to you

Using Pragma RESTRICT_REFERENCES: The function called from the SQL statements should obey certain rules meant to control the side effects. To check for violation of the rules,

Parameter and Keyword Description: record_type_name: This identifies the user-defined type specifier that is used in the subsequent declarations of the records. NOT N

Short-Circuit Evaluation When computing a logical expression, the PL/SQL uses short-circuit evaluation. That is, the PL/SQL stops computing the expression as soon as the result

Using TRIM This process has two forms. The TRIM removes an element from the end of the collection. The TRIM(n) removes the n elements from the end of the collection. For e.g.