RETURNING INTO

Recently I’ve helped out a developer with some ad-hoc PL/SQL scripts. While the developer was highly proficient in their chosen languages, their understanding of PL/SQL less so. To me, the interesting part of the interaction was in the small features of PL/SQL that simplified the scripts that the developer was unaware of. One such feature was the RETUNING INTO clause.

Like many applications this one used sequences to populate primary key ID columns. A rather common scenario in the scripts was to create a parent table entry and then create a set of child table entries. Normally the creation of the primary key values would be done in the insert operation itself. Unfortunately the application used triggers on the tables to obtain a value from the sequence and assign it to the primary key column. The use of triggers was problematic as the sequence value used could not be determined beforehand. This then caused problems with trying to create child entries related to the newly inserted row. Enter the RETURNING INTO clause to solve all our problems as shown in the following scenario…

SQL>CREATE TABLE parent
  2     (parent_id    NUMBER(10)   NOT NULL
  3     ,parent_name  VARCHAR2(50) NOT NULL
  4     ,CONSTRAINT parent_pk PRIMARY KEY (parent_id))
  5  /

Table created.

SQL>CREATE TABLE child
  2     (child_id    NUMBER(10)   NOT NULL
  3     ,parent_id   NUMBER(10)   NOT NULL
  4     ,child_name  VARCHAR2(50) NOT NULL
  5     ,CONSTRAINT child_pk PRIMARY KEY (child_id)
  6     ,CONSTRAINT child_fk1 FOREIGN KEY (parent_id)
  7      REFERENCES parent (parent_id))
  8  /

Table created.

SQL>CREATE SEQUENCE parent_id_seq
  2  /

Sequence created.

SQL>CREATE SEQUENCE child_id_seq
  2  /

Sequence created.

SQL>CREATE OR REPLACE TRIGGER trg_parent_id
  2  BEFORE INSERT ON parent
  3  FOR EACH ROW
  4  DECLARE
  5  BEGIN
  6     :new.parent_id := parent_id_seq.NEXTVAL;
  7  END;
  8  /

Trigger created.

SQL>DECLARE
  2     l_parent_id parent.parent_id%TYPE;
  3  BEGIN
  4     INSERT INTO parent
  5        (parent_name)
  6     VALUES
  7        ('PARENT_1')
  8     RETURNING parent_id INTO l_parent_id;
  9
 10     INSERT INTO child
 11        (child_id, parent_id, child_name)
 12     VALUES
 13        (child_id_seq.NEXTVAL, l_parent_id, 'CHILD_1');
 14  END;
 15  /

PL/SQL procedure successfully completed.

SQL>SELECT *
  2  FROM   parent
  3  /

 PARENT_ID PARENT_NAME
---------- ------------------------------------------------
         1 PARENT_1

SQL>SELECT *
  2  FROM   child
  3  /

  CHILD_ID  PARENT_ID CHILD_NAME
---------- ---------- -------------------------------------
         1          1 CHILD_1

Quite simply we have two tables with a foreign key relationship between them. The PARENT table has a trigger on it that assigns a new sequence generated value to the PARENT_ID primary key column. The anonymous PL/SQL block performs an insert into the PARENT table and then creates a row in the CHILD table. The INSERT into the PARENT table contains the RETURNING INTO clause, which populates a local variable with the value that has been assigned to the PARENT_ID column by the trigger.

Even without the trigger we would still want to use the RETURNING INTO clause. Far too often I have seen code resembling:

DECLARE
   l_parent_id parent.parent_id%TYPE;
BEGIN

   SELECT parent_id_seq.NEXTVAL
   INTO   l_parent_id
   FROM   dual;
   
   INSERT INTO parent
      (parent_id, parent_name)
   VALUES
      (l_parent_id, 'PARENT_1');

   INSERT INTO child
      (child_id, parent_id, child_name)
   VALUES
      (child_id_seq.NEXTVAL, l_parent_id, 'CHILD_1');
END;

which can be rewritten as:

DECLARE
   l_parent_id parent.parent_id%TYPE;
BEGIN
   
   INSERT INTO parent
      (parent_id, parent_name)
   VALUES
      (parent_id_seq.NEXTVAL, 'PARENT_1')
   RETURNING parent_id INTO l_parent_id;


   INSERT INTO child
      (child_id, parent_id, child_name)
   VALUES
      (child_id_seq.NEXTVAL, l_parent_id, 'CHILD_1');
END;

RETURNING INTO isn’t just for INSERT statements either. It can be used with UPDATE and DELETE too:

SQL> DECLARE
  2     l_child_name child.child_name%TYPE;
  3  BEGIN
  4
  5     DELETE
  6     FROM   child
  7     WHERE  child_id = (SELECT MIN(child_id) FROM child)
  8     RETURNING child_name INTO l_child_name;
  9
 10     dbms_output.put_line ('Deleted child ' || l_child_name);
 11  END;
 12  /
Deleted child CHILD_1

PL/SQL procedure successfully completed.

Furthermore, you can obtain the values from multiple columns:

SQL>DECLARE
  2     l_child_id   child.child_id%TYPE;
  3     l_parent_id  parent.parent_id%TYPE;
  4     l_child_name child.child_name%TYPE;
  5  BEGIN
  6
  7     INSERT INTO child
  8        (child_id, parent_id, child_name)
  9     VALUES
 10        (child_id_seq.NEXTVAL
 11        ,(SELECT MIN(parent_id) FROM parent)
 12        ,'CHILD_' || child_id_seq.NEXTVAL)
 13     RETURNING child_id, parent_id, child_name
 14     INTO l_child_id, l_parent_id, l_child_name;
 15
 16     dbms_output.put_line ('CHILD_ID ' || TO_CHAR(l_child_id));
 17     dbms_output.put_line ('PARENT_ID ' || TO_CHAR(l_parent_id));
 18     dbms_output.put_line ('CHILD_NAME ' || l_child_name);
 19  END;
 20  /
CHILD_ID 3
PARENT_ID 1
CHILD_NAME CHILD_3

PL/SQL procedure successfully completed.

For retrieving multiple columns it might be easier to retrieve them into a record type:

SQL>DECLARE
  2     l_child_rec  child%ROWTYPE;
  3  BEGIN
  4
  5     INSERT INTO child
  6        (child_id, parent_id, child_name)
  7     VALUES
  8        (child_id_seq.NEXTVAL
  9        ,(SELECT MIN(parent_id) FROM parent)
 10        ,'CHILD_' || child_id_seq.NEXTVAL)
 11     RETURNING child_id, parent_id, child_name
 12     INTO l_child_rec;
 13
 14     dbms_output.put_line ('CHILD_ID ' || TO_CHAR(l_child_rec.child_id));
 15     dbms_output.put_line ('PARENT_ID ' || TO_CHAR(l_child_rec.parent_id));
 16     dbms_output.put_line ('CHILD_NAME ' || l_child_rec.child_name);
 17  END;
 18  /
CHILD_ID 4
PARENT_ID 1
CHILD_NAME CHILD_4

PL/SQL procedure successfully completed.

The values returned by the RETURNING INTO clause do not need to be simple column values either, expressions are permitted too:

SQL>DECLARE
  2     l_child_name_len  NUMBER;
  3  BEGIN
  4
  5     INSERT INTO child
  6        (child_id, parent_id, child_name)
  7     VALUES
  8        (child_id_seq.NEXTVAL
  9        ,(SELECT MIN(parent_id) FROM parent)
 10        ,'CHILD_' || child_id_seq.NEXTVAL)
 11     RETURNING LENGTH(child_name)
 12     INTO l_child_name_len;
 13
 14     dbms_output.put_line ('CHILD_NAME length is ' || TO_CHAR(l_child_name_len));
 15  END;
 16  /
CHILD_NAME length is 7

PL/SQL procedure successfully completed.

In all the above we’ve been handling single row operations. If an operation modifies multiple rows then the simple RETURNING INTO clause will fail:

SQL>DECLARE
  2     l_child_name child.child_name%TYPE;
  3  BEGIN
  4
  5     DELETE
  6     FROM   child
  7     RETURNING child_name INTO l_child_name;
  8
  9     dbms_output.put_line ('Deleted child ' || l_child_name);
 10  END;
 11  /
DECLARE
*
ERROR at line 1:
ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at line 5

There’s a BULK COLLECT option for obtaining multiple rows back from the DML operation, which will be the subject of a later post.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s