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

Parameter and keyword description - object types, Parameter and Keyword Des...

Parameter and Keyword Description: type_name: This identifies a user-defined type specifier that is used in the subsequent declarations of the objects. AUTHID Clause:

Write a pl/sql anonymous block that accepts an employee id, Based on the EM...

Based on the EMPLOYEE table created in Assignment #1, write a PL/SQL anonymous block that accepts an employee ID from the user input and finds whether the employee ID is in the EMP

Manipulating local collections - pl/sql, Manipulating Local Collections ...

Manipulating Local Collections Within PL/SQL, to manipulate the local collection, by using the  TABLE and CAST operators . The operands of CAST are a collection declared locally

Miller-urey''s experimental procedure, MILLER-UREY' S EXPERIMENTAL PROCEDU...

MILLER-UREY' S EXPERIMENTAL PROCEDURES - They recreated the probable conditions on the primitive earth in the laboratory. An atmosphere containing hydrogen, ammonia, me

Loop statements, LOOP Statements The LOOP statements execute a series o...

LOOP Statements The LOOP statements execute a series of statements at multiple times. The loops enclose the series of statements that is to be repeated. The PL/SQL provides typ

Calculate the number of tuples, Question: (a) The objective of query opti...

Question: (a) The objective of query optimization is to choose the most efficient strategy for implementing a given relational query, thereby improving the system performance. On

Data types and representations, Data Types and Representations This ex...

Data Types and Representations This explains the concept possible representation, abbreviated possrep, and explains how these can be used in conjunction with constraints to de

Product-specific packages, Product-specific Packages The Oracle and dif...

Product-specific Packages The Oracle and different Oracle tools are supplied with the product-specific packages which help you to build the PL/SQL-based applications. For illus

Dbms, DBMS: The answer to this question is of course given in of the t...

DBMS: The answer to this question is of course given in of the theory book. This book is concerned with SQL DBMSs and SQL databases in particular. Soon we will be looking a

Relational operators-comparison operators, Relational Operators The rela...

Relational Operators The relational operators permit you to compare randomly complex expressions. The list below provides the meaning of each operator:

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