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.
Mark Hoxey 