Feuertip #21: Returning isn’t just for functions

The first thing you probably think about when the word “returning” comes up is: function!

A function returns a value (could be a scalar, could be a complex type like a collection).

But there is another use of “returning” in PL/SQL, one that highlights the tight integration between PL/SQL and SQL: the returning into clause of a non-query DML statement.

I’ve covered this feature in a previous blog post (on my personal blog). I’ll cover the basics here as a “backup” for this week’s episode of Feuertips. I’ll also offer a list of resources for further study.

The basic idea with returning is to avoid executing two (or more) SQL statements and instead combine everything you need into one. This reduces the number of context switches, always a good thing when it comes to application performance.

Here’s a simple example: the primary key on my parts table is generated automatically as an identity column. Here’s the problem with that: after I insert a new row, I need the primary key to perform the next operation.

procedure create_new_part (p_name in varchar2, p_type in varchar2)
is 
   l_part_id   parts.id%type; 
begin
   insert into parts (name, type) values (p_name, p_type); 
 
   SELECT id 
     INTO l_part_id 
     FROM parts 
    WHERE name = p_name; 
 
   initialize_part (l_part_id); 
end;

There are a couple of issues to consider here:

• Performance overhead (and context switch) of the second SQL statement
• Need to identify precisely the row that was just inserted

I can avoid those issues by using the returning into clause. With returning into, I can get back the information I need directly from the SQL engine after the insert is completed.

procedure create_new_part (p_name in varchar2, p_type in varchar2)
is 
   l_part_id   parts.id%type; 
begin
   insert into parts (name, type) values (p_name, p_type)
      returning id into l_part_id;
 
   initialize_part (l_part_id); 
end;

Is that sweet, or what?

Wait, but what if you are changing more than one row? Add bulk collect!

declare 
   l_part_numbers   dbms_sql.number_table; 
begin 
      update parts 
         set part_name = part_name || '1' 
   returning part_number 
        bulk collect into l_part_numbers; 
 
   for indx in 1 .. l_part_numbers.count 
   loop 
      dbms_output.put_line (l_part_numbers (indx)); 
   end loop; 
end;

You can bulk collect into separate collections for each column in the returning list, or you can use a collection of records.

Beyond the Obvious

Also, within the returning into clause, you can…

• Execute aggregate functions like sum
• Execute regular SQL functions like substr
• Run a query
• Call a PL/SQL function – built-in or user-defined.

This LiveSQL script offers examples of all of that.

Resources

Oracle LiveSQL script demonstrating the returning into clause

Another LiveSQL script exploring the less common usages of returning into (“Beyond the Obvious”)

Tim Hall (ORACLE-BASE)’s article on returning into

Oracle documentation: the RETURNING INTO Clause

Watch the full Feuertips episode here