Obsessed with Oracle PL/SQL

The RETURNING clause allows you to retrieve values of columns (and expressions based on columns) that were modified by an insert, delete or update. Without RETURNING you would have to run a SELECT statement after the DML statement is completed, in order to obtain the values of the changed columns. So RETURNING helps avoid another roundtrip to the database, another context switch in a PL/SQL block. The RETURNING clause can return multiple rows of data, in which case you will use the RETURNING BULK COLLECT INTO form. You can also call aggregate functions in the RETURNING clause to obtain sums, counts and so on of columns in multiple rows changed by the DML statement. Finally, you can also use RETURNING with EXECUTE IMMEDIATE (for dynamically constructed and executed SQL statements). Run this LiveSQL script to see all of the statements shown below "in action." First, I will create a table to use in my scripts: CREATE TABLE parts ( part_number INTEGER , part_...

[That title is, by the way, a fairly lame play on the American Express "Don't leave home without it." tagline. Sorry, can't help myself. SF] The RETURNING clause allows you to retrieve values of columns (and expressions based on columns) that were modified by an insert, delete or update. Without RETURNING you would have to run a SELECT statement after the DML statement is completed to obtain the values of the changed columns. The RETURNING clause can return multiple rows of data, in which case you will use the RETURNING BULK COLLECT INTO form. You can also call aggregate functions in the RETURNING clause to obtain sums, counts and so on of columns in multiple rows changed by the DML statement. I explore the feature below. You can execute the same code on LiveSQL . Set Up Some Data First, let's create some data with which to play. CREATE TABLE parts ( part_number INTEGER , part_name VARCHAR2 (100) ) / BEGIN INSERT INTO parts VALUES (1, 'M...