Quick Search:
CODE
Oracle PL/SQL Code Library
JOBS
Find Or Post Oracle Jobs
FORUM
Oracle Discussion & Chat
 Oracle PL/SQL: UPDATE: with RETURNING clause Jump to:  
Category: >> Oracle PL/SQL >> UPDATE: with RETURNING clause Bookmark and Share

<< lastnext >>

Snippet Name: UPDATE: with RETURNING clause

Description: UPDATE with the RETURNING clause changes the values of the specified columns in all rows that satisfy the condition as specified in UPDATE and then returns the updated rows.

Also see:
» UPDATE: Update a partitioned table
» UPDATE: Update based on a record
» UPDATE: Update Object Table
» UPDATE: Nested Table Update example
» UPDATE: Correlated Update
» UPDATE: Update from a SELECT statement
» UPDATE: based on multiple returned val...
» UPDATE: Update based on a query
» UPDATE: Update multiple rows
» UPDATE: update a specific record
» UPDATE: Single row
» UPDATE

Comment: (none)

Language: PL/SQL
Highlight Mode: PLSQL
Last Modified: March 04th, 2009

-- update rows of a table and return the updated rows
 
UPDATE [ONLY] { TABLE OR UPDATE expression} { alias }
TABLE SET column = { 
 expression | DEFAULT } [, ...]
    [ FROM  fromlist ]
  [WHERE  condition]
[RETURNING  { expression} INTO  {data_items}];
 
 
CREATE OR REPLACE PROCEDURE SingleRowUpdateReturn
IS
    empName VARCHAR2(50);
    empSalary NUMBER(7,2);      
BEGIN
    UPDATE emp
    SET sal = sal + 1000
    WHERE empno = 7499
    RETURNING ename, sal
    INTO empName, empSalary;
 
    DBMS_OUTPUT.put_line('Name of Employee: ' || empName);
    DBMS_OUTPUT.put_line('New Salary: ' || empSalary);
 
 
-- Let's consider when an UPDATE statement affects more than 
-- one row. In order to see the affected rows, we can use arrays 
-- to hold the values. We can iterate through the array to see 
-- the values.
 
CREATE OR REPLACE PROCEDURE MultipleRowUpdateReturn
IS
TYPE emp_table IS TABLE OF emp%ROWTYPE INDEX BY PLS_INTEGER;
v_empRecs emp_table;
     BEGIN
UPDATE emp
SET sal = sal * 1.1
WHERE job = 'CLERK'
    RETURNING empno, ename, job, mgr, hiredate, sal, comm, deptno
    BULK COLLECT INTO v_empRecs;
    DBMS_OUTPUT.put_line('Rows Updated: ' || SQL%ROWCOUNT);
    FOR I IN v_empRecs.FIRST .. v_empRecs.LAST
    LOOP       
        DBMS_OUTPUT.put_line('Employee Name: ' || v_empRecs(i).ename);
     DBMS_OUTPUT.put_line('New Salary: ' || v_empRecs(i).sal);
    END LOOP;
END;
 
/*-------------------------------------------------*/
 
-- another example
-- The following example returns values from the updated row 
-- and stores the result in PL/SQL variables bnd1, bnd2, bnd3:
 
UPDATE employees
  SET job_id ='SA_MAN', salary = salary + 1000, department_id = 140
  WHERE last_name = 'Jones'
  RETURNING salary*0.25, last_name, department_id
    INTO :bnd1, :bnd2, :bnd3;
 
 
-- This example shows that you can specify a single-set 
-- aggregate function in the expression of the returning clause:
 
UPDATE employees
   SET salary = salary * 1.1
   WHERE department_id = 100
   RETURNING SUM(salary) INTO :bnd1;
 


Free
Oracle Magazine
Subscriptions
and Oracle White Papers


SQL University.net courses meet the most demanding needs of the business world for advanced education in a cost-effective manner. SQL University.net courses are available immediately for IT professionals and can be taken without disruption of your workplace schedule or processes.

Compared to traditional travel-based training, SQL University.net saves time and valuable corporate resources, allowing companies to do more with less. That's our mission, and that's what we deliver.

Click here to find out more
 
Home      :      Code Library      :      Sponsors      :      Privacy      :      Terms of Use      :      Contact Us 79 users online    © 2009 psoug.org

PSOUG LOGIN
Username: 
Password: 
Forgot your password?