In the last post, we saw a neat way to implement auto-increment functionality in an Oracle table. The auto-incremented column gets its value populated in the background, without the issuer of the insert statement even getting to know about it.
BUT – what if the issuer of the insert statement does want to know about it?
You might want to use the current inserted row’s id, maybe for a further transaction in related tables, or maybe for tracing/logging purposes. The implementation is hidden, which means that you don’t know directly the value of the generated ID.
It can be done pretty easily, using the RETURNING clause.
The RETURNING clause
Using the example of the same tables etc. used to generate the auto-increment column, here is how RETURNING works.
SQL> -- Current status of t_autoinc SQL> select * from t_autoinc; A B C D ---------- ---------- ---------- ---------- 1 100 130 130 2 150 200 240 SQL> -- Defining a variable aval to take the value returned SQL> var aval number; SQL> SQL> -- Insert statement with returning clause SQL> insert into t_autoinc (b,c,d) 2 values (80, 130, 150) returning a into :aval; 1 row created. SQL> SQL> print :aval; AVAL ---------- 3 SQL> -- Verifying that the value of :aval is the same as value of 'a' in new row SQL> select * from t_autoinc; A B C D ---------- ---------- ---------- ---------- 1 100 130 130 2 150 200 240 3 80 130 150
Conclusion
With the use of RETURNING clause in the insert statement, you can get the value currently generated for the auto-increment column and capture it in a session variable. You can use this session variable for subsequent SQLs or PL/SQL.
