RETURNING INTO – Enhancement Suggestion

The RETURNING INTO clause is one of my favorite features.
It returns data from the rows that have been affected by the DML statement, and as I wrote in this previous post:
For INSERT it returns the after-insert values of the new row’s columns.
For UPDATE it returns the after-update values of the affected rows’ columns.
For DELETE it returns the before-delete values of the affected rows’ columns.

For INSERT there are no before-insert values, so the “after-insert values” is the only reasonable option.
Likewise, for DELETE there are no after-delete values, so the “before-delete values” is the only reasonable option.
But for UPDATE there are both before-update and after-update values, but currently the RETURNING INTO clause supports only the after-update values.

I think it will be really useful if for UPDATE we’ll be able to get the before-update values in addition to the after-update values. This will also make the feature more symmetric and complete, in my opinion.
Currently, if we need to know the before-update values, we have to make two SQL statement calls: SELECT (FOR UPDATE) for the record(s) we’re about to update, and then the UPDATE itself.

As a simple example, consider the following procedure, that increases the salary of an employee by some amount, up to maximum of 10000, and returns the percentage in which the salary was changed:

create or replace procedure raise_salary
(
    i_employee_id  in employees.employee_id%type,
    i_raise_amount in employees.salary%type,
    o_raise_pct    out number
) as
    l_old_salary employees.salary%type;
    l_new_salary employees.salary%type;
begin
    select e.salary
    into   l_old_salary
    from   employees e
    where  e.employee_id = i_employee_id
    for    update of e.salary;

    update employees e
    set    e.salary = least(e.salary + i_raise_amount, greatest (e.salary,10000))
    where  e.employee_id = i_employee_id
    returning e.salary into l_new_salary;

    o_raise_pct := l_new_salary / l_old_salary;

end raise_salary;

With the suggested enhancement, we could achieve the same functionality with one context switch to the SQL engine rather than two (obviously the syntax I’m using is made-up, just for this example):

create or replace procedure raise_salary
(
    i_employee_id  in employees.employee_id%type,
    i_raise_amount in employees.salary%type,
    o_raise_pct    out number
) as
    l_old_salary employees.salary%type;
    l_new_salary employees.salary%type;
begin

    update employees e
    set    e.salary = least(e.salary + i_raise_amount, greatest (e.salary,10000))
    where  e.employee_id = i_employee_id
    returning old e.salary, new e.salary 
    into l_old_salary, l_new_salary;

    o_raise_pct := l_new_salary / l_old_salary;

end raise_salary;

I submitted this suggestion to the Database Ideas space of the Oracle Developer Community (formerly known as OTN) web site.
If you think it’s a good idea, please vote up :-)

Note

As a (dirty, confusing, and most probably inferior performance-wise to the “real” solution) workaround, we can achieve the same functionality for this example today, using the fact that a scalar subquery in the RETURNING INTO clause obeys the statement-level read consistency rule – it returns the data as it was before the DML happened:

create or replace procedure raise_salary
(
    i_employee_id  in employees.employee_id%type,
    i_raise_amount in employees.salary%type,
    o_raise_pct    out number
) as
    l_old_salary employees.salary%type;
    l_new_salary employees.salary%type;
begin

    update employees e
    set    e.salary = least(e.salary + i_raise_amount, greatest (e.salary,10000))
    where  e.employee_id = i_employee_id
    returning 
      (select e1.salary from employees e1 where e1.employee_id = i_employee_id),
      e.salary
    into l_old_salary, l_new_salary;

    o_raise_pct := l_new_salary / l_old_salary;

end raise_salary;

3 Comments

  1. IUDITH MENTZEL

    Hello Oren,

    Nice idea for an enhancement request :)

    In the meantime, here is an even “dirtier” variant to your work-around above:

    create or replace procedure raise_salary
    (
        i_employee_id  in employees.employee_id%type,
        i_raise_amount in employees.salary%type,
        o_raise_pct    out number
    ) as
        l_old_salary employees.salary%type;
        l_new_salary employees.salary%type;
    begin
    
        update employees e
        set    e.salary = least(e.salary + i_raise_amount, 
                                greatest(e.salary,10000))
        where  e.employee_id = i_employee_id
        returning 
          (select e.salary from DUAL),
        into l_old_salary, l_new_salary;
    
        o_raise_pct := l_new_salary / l_old_salary;
    
    end raise_salary;
    

    Maybe it is slightly more efficient performance-wise,
    and, it also works nicely for the case of updating several rows,
    using RETURNING BULK COLLECT INTO, like in the following example
    using the old scott.emp table:

    declare
       type t_number is table of number;
       l_old_sal  t_number;   
       l_new_sal  t_number;
    begin
       update emp e
       set    e.sal = 1234
       where  e.empno in (7839, 7900)
       returning 
             (select e.sal from dual),
             e.sal
       bulk collect into l_old_sal, l_new_sal;
    
       for i in 1 .. l_old_sal.count
       loop
           dbms_output.put_line ('l_old_sal['||i||']='||l_old_sal(i));
           dbms_output.put_line ('l_new_sal['||i||']='||l_new_sal(i));
       end loop;
    end;
    /
    

    One disadvantage is that if we want to return the old values for
    several columns we need a separate SELECT from DUAL for each of them …
    And, for really large updates, performance might also still be an issue.

    Cheers & Best Regards,
    Iudith

  2. Great enhancement request, Oren!
    For all the readers who like this enhancement, please make sure that you upvote and/or comment on the Database Ideas community space that is linked in this blog post!

    • Oren Nakdimon

      Thanks Gerald :-)

Leave a Reply

Your email address will not be published.

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>