Oracle PL/SQL Best Practices

Name

SQL-13: Use RETURNING to retrieve information about modified rows (Oracle8).

Synopsis

The RETURNING clause, available in Oracle8 and above, allows you to retrieve information from rows you have just modified with an INSERT, UPDATE, or DELETE statement. This clause allows you to perform—in a single operation—what you would previously have done in two operations (INSERT, then SELECT, for example).

Example

Suppose that I am using a sequence to generate the primary key of the patient table in my universal health care system. I then need to use that new primary key for another operation. Prior to Oracle8, I would have written code like this:

INSERT INTO patient (patient_id, last_name, first_name)
   VALUES (patient_seq.NEXTVAL, 'FEUERSTEIN', 'STEVEN');

SELECT patient_id INTO l_patient_id
  FROM patient
 WHERE last_name = 'FEUERSTEIN'; 

or even like this:

SELECT patient_seq.NEXTVAL INTO l_patient_id 
  FROM dual;

INSERT INTO patient (patient_id, last_name, first_name)
   VALUES (l_patient_id, 'FEUERSTEIN', 'STEVEN');

With RETURNING, I can collapse two statements into a single INSERT statement:

INSERT INTO patient (patient_id, last_name, first_name)
   VALUES (patient_seq.NEXTVAL, 'FEUERSTEIN', 'STEVEN')
   RETURNING patient_id INTO l_patient_id;

You can also use the RETURNING clause in dynamic SQL and FORALL statements to obtain information about multiple rows affected by DML statements.

Benefits

You will see improved performance in your applications.

Code volume will be reduced.

Challenges

Your code will not ...