Binding by RETURNING

We could bind PL/SQL variables to a SQL DML statement so that it could return values extracted from the database to the PL/SQL block. To do that we use add a RETURNING clause to the SQL statement.

As an example, here is a loop that performs an UPDATE statement on each row of the EMPLOYEES table (again from the Oracle test HR schema). Two local variables are set in the RETURNING clause and then used in the proceeding of the PL/SQL code:

declare
  l_salary employees.salary%type;
  l_name employees.last_name%type;
begin
  for rec in (select * from employees)
  loop
    update employees
    set salary = salary * 1.05
    where rec.employee_id = employee_id
    returning salary, last_name into l_salary, l_name;
    
    dbms_output.put_line(l_name || ' new salary is ' || l_salary);
  end loop;
end;

More information on DML in PL/SQL in chapter 14 of Oracle PL/SQL Programming, fifth edition, by Steven Feuerstein.