Category: PL/SQL

Here’s my contribution to the ODC Appreciation Day.

Overview

Last week I had the privilege to participate in the EOUC Database ACES Share Their Favorite Database Things session at Oracle OpenWorld, so I think that the best topic to write about, as part of the ODC Appreciation Day, is the one I talked about in this session.
My 5-minute presentation was about Collections in SQL.

Collections are very useful in PL/SQL development. This is a well-known fact, and collections are indeed used a lot by PL/SQL developers.
But it is less known that collections can be very useful also in SQL statements, not only in PL/SQL. The Oracle database supports user-defined collection types (Nested Tables and Varrays) and built-in functions that operate on collections, and they add yet more power to the already-powerful SQL language.

Examples

Here are some links to blog posts that I’ve written in the past, in which I used collections in SQL.

This post includes an example for a PL/SQL procedure that gets a collection parameter as input and needs to perform some manipulation on it. The implementation of this procedure becomes easy using Collection Unnesting – simply writing a SQL statement (MERGE in this case), that treats the collection parameter as if it were a simple table.

In this post we take advantage of the PowerMultiSet built-in function for creating a Kakuro cheat-sheet.

In this post we observe subtle differences between the two types of collections – Nested Tables and Varrays – with regard to the SQL functions that can be applied on them.

This post shows how sometimes collection functions such as SET and CARDINALITY enable solving a problem in a more concise and simple way than the alternatives.

And here is a simple solution for some problem that was asked on Ask TOM, using the SQL function with probably the longest name – POWERMULTISET_BY_CARDINALITY.

Documentation

• Collection Unnesting
• Built-in SQL Collection Functions
• Multiset Conditions
• Multiset Operators

Update: this enhancement suggestion got accepted, and it is now part of Oracle Database 23c

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;

The RETURNING INTO clause is one of my favorite PL/SQL features. It allows to write less code, improves readability and reduces context switches between PL/SQL and SQL.
In this post I’d like to highlight some less-known characteristics of the RETURNING INTO clause and emphasize differences that exist when it is used in different DML statements.

Supported Statements

The RETURNING INTO clause is supported by the UPDATE, DELETE, and single-table single-row (“values-based”) INSERT statements.
It is not supported by subquery-based INSERT, multi-table INSERT, and MERGE. Some additional restrictions are documented.

What is returned?

The RETURNING INTO clause returns data from the rows that have been affected by the DML statement.
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.

> create table t (
    id number generated as identity,
    data number
  );

Table created.

> declare
    ret1 number;
    ret2 number;
  begin
    insert into t (data) values (10) returning id,data into ret1,ret2;
    dbms_output.put_line('id='||ret1||' ; data='||ret2);
    insert into t (data) values (20) returning id,data into ret1,ret2;
    dbms_output.put_line('id='||ret1||' ; data='||ret2);
    insert into t (data) values (30) returning id,data into ret1,ret2;
    dbms_output.put_line('id='||ret1||' ; data='||ret2);
    commit;
  end;
  /

id=1 ; data=10
id=2 ; data=20
id=3 ; data=30

PL/SQL procedure successfully completed.

> declare
    ret number;
  begin
    update t set data=111 where id=1 returning data into ret;
    dbms_output.put_line('post-update value = '||ret);
    delete t where id=2 returning data into ret;
    dbms_output.put_line('pre-delete value = '||ret);
    rollback;
  end;
  /

post-update value = 111
pre-delete value = 20

PL/SQL procedure successfully completed.

Expressions

The returned values are not limited just to simple column names. It can virtually be any expression you can place in the select-list of a SELECT statement from the affected table.

> declare
    ret1 number;
    ret2 varchar2(10);
  begin
    update t
    set data = round(dbms_random.value(1,100))
    where id = 1
    returning data, decode(mod(data,2),0,'even','odd')
    into ret1, ret2;
 
    dbms_output.put_line('the new value is '||ret1||', an '||ret2||' number');
    rollback;
  end;
  /

the new value is 19, an odd number

PL/SQL procedure successfully completed.

Warning: you can even include in the returned expression a scalar subquery against the affected table. This is probably not a very good idea, especially with INSERT and UPDATE, which return the post-DML values. The scalar subquery, on the other hand, obeys the statement-level read consistency rule – it returns the data as it was before the DML happened. So it may lead to confusing inconsistent results:

> declare
    ret1 number;
    ret2 number;
  begin
    update t
    set data = 29081969
    where id = 1
    returning data, (select max(data) from t)
    into ret1, ret2;

    dbms_output.put_line('just set DATA of a row to ' || ret1 ||
                         ' and the maximum value is (no! *was*) ' || ret2);
    rollback;
  end;
  /

just set DATA of a row to 29081969 and the maximum value is (no! *was*) 30

PL/SQL procedure successfully completed.

BULK COLLECT

The returned values can be placed into collection variables, using RETURNING with the BULK COLLECT INTO clause. This is very useful for the UPDATE and DELETE statements, that may affect multiple rows in a single statement (but this syntax is supported also for the INSERT statement – perhaps as a preparation for supporting RETURNING in multi-row INSERT in the future?).

> declare
    type ntt is table of number;
    ret1 ntt;
    ret2 ntt;
  begin
    update t
    set data = data * 2
    returning id, data
    bulk collect into ret1, ret2;
 
    for i in 1..ret1.count loop
      dbms_output.put_line('id='||ret1(i)||' ; data='||ret2(i));
    end loop;
 
    rollback;
  end;
  /

id=1 ; data=20
id=2 ; data=40
id=3 ; data=60

PL/SQL procedure successfully completed.

Aggregation

For UPDATE and DELETE the returned expression can be an aggregate function. The aggregation is performed on the affected rows, and (since the aggregated value is scalar) BULK COLLECT is not needed when using this option. This is not supported for INSERT.

> declare
    ret1 number;
    ret2 number;
    ret3 varchar2(100);
  begin
    update t
    set data = data * 2
    where id < 3
    returning sum(data),
              avg(data),
              listagg(id||':'||data,',') within group (order by id)
    into ret1, ret2, ret3;
 
    dbms_output.put_line('sum(data) of updated rows = ' || ret1);
    dbms_output.put_line('avg(data) of updated rows = ' || ret2);
    dbms_output.put_line('new values = ' || ret3);
 
    rollback;
  end;
  /
sum(data) of updated rows = 60
avg(data) of updated rows = 30
new values = 1:20,2:40

PL/SQL procedure successfully completed.

RETURNING or RETURN?

One last anecdote: the RETURN keyword can be used instead of RETURNING. I prefer RETURNING.

What about MERGE?

As many others, I wish the MERGE statement would support the RETURNING INTO clause as well.
However, since MERGE is a combination of INSERT, UPDATE and DELETE, and given the differences in the way RETURNING works with these 3 DML statements (as listed above), supporting RETURNING INTO in MERGE is clearly not a simple task.
It may also lead to confusing situations – think for example about a MERGE statement that includes the UPDATE SET and DELETE WHERE clauses – what will RETURNING return for rows affected by the DELETE WHERE clause? The pre-MERGE value, the post-UPDATE-pre-DELETE value, or perhaps simply 42?

In Part 1 we saw that the SQL function COLLECT with the DISTINCT option is not natively supported in PL/SQL.
One suggested workaround was to apply the SET function on the result of the “simple” COLLECT function (without the DISTINCT option).
This works fine, in both SQL and PL/SQL, as long as the collection type that we use is Nested Table.

create type integer_ntt as table of integer
/

select person_id,set(cast(collect(project_id) as integer_ntt)) project_id_list
from project_assignments
group by person_id
order by person_id;

PERSON_ID PROJECT_ID_LIST
---------- -----------------------------------------
       101 INTEGER_NTT(1, 3, 2)
       102 INTEGER_NTT(2)
       103 INTEGER_NTT(3)

But what if the collection type we use is Varray and not Nested Table?

create type integer_vt as varray(100) of integer
/

select person_id,set(cast(collect(project_id) as integer_vt)) project_id_list 
from project_assignments 
group by person_id
order by person_id;

select person_id,set(cast(collect(project_id) as integer_vt)) project_id_list
                     *
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected UDT got DEMO.INTEGER_VT

So not every operation we can do with one collection type (Nested Table) is supported with other collection type (Varray).
SET is one example for that, and actually it makes sense. The elements within a set are unordered by definition, while a Varray is an ordered list of elements.
Another example for a function that works for nested tables but not for varrays is the CARDINALITY collection function:

select cardinality(integer_ntt(7,8,9)) from dual;

CARDINALITY(INTEGER_NTT(7,8,9))
-------------------------------
                              3

select cardinality(integer_vt(7,8,9)) from dual;

select cardinality(integer_vt(7,8,9)) from dual
                   *
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected UDT got DEMO.INTEGER_VT