-- 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;