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

Assignment Help:

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;


Related Discussions:- Use the returning clause -improve performance of application

Select into statement - syntax, SELECT INTO Statement   The SELECT INT...

SELECT INTO Statement   The SELECT INTO statement retrieve data from one or more database tables, and then assigns the selected values to the variables or fields. Syntax:

Using savepoints, Using Savepoints The scope of the savepoint is a tra...

Using Savepoints The scope of the savepoint is a transaction in which it is defined. The Savepoints defined in the major transaction are not related to the savepoints defined

Overloading method in pl/sql, Overloading: Similar to packaged subprog...

Overloading: Similar to packaged subprograms, methods of the same type can be overloaded. That is, you can use similar name for various methods if their formal parameters diff

Example of wrap operator - sql, Example of WRAP Operator - SQL The eff...

Example of WRAP Operator - SQL The effect of Example can be obtained in SQL but note that one needs to write down not only the names of the columns being wrapped but also the

Deriving predicates from predicates in sql, Deriving Predicates from Predic...

Deriving Predicates from Predicates in SQL The corresponding section in the theory book describes how predicates can be derived from predicates using (a) the logical connectiv

Exit statement - syntax, EXIT Statement   You can use the EXIT stateme...

EXIT Statement   You can use the EXIT statement to exit a loop. The EXIT statement has 2 forms: the conditional EXIT WHEN and the unconditional EXIT. With the either form, you

Use of table comparisons - sql, Use of Table Comparisons - SQL Table ...

Use of Table Comparisons - SQL Table comparisons where it is noted that although table expressions cannot be compared, we have TABLE (t) to convert a table expression t into

I want customer management program, This is a Customer Management project. ...

This is a Customer Management project. Customer data is presented in a text file. The program will load this text data into its DB columns. The data mapping is user definable. User

Keyword and parameter description in pl sql, Keyword and Parameter Descript...

Keyword and Parameter Description: label_name: This is an undeclared identifier which optionally labels the PL/SQL block. When used, label_name should be enclosed by the do

Primary key - sql, Primary Key - SQL A PRIMARY KEY specification carri...

Primary Key - SQL A PRIMARY KEY specification carries an implicit NOT NULL constraint on each column of the specified key. When more than one key constraint is required, the k

Write Your Message!

Captcha
Free Assignment Quote

Assured A++ Grade

Get guaranteed satisfaction & time on delivery in every assignment order you paid with us! We ensure premium quality solution document along with free turntin report!

All rights reserved! Copyrights ©2019-2020 ExpertsMind IT Educational Pvt Ltd