RETURNING INTO v16
You can append the INSERT
, UPDATE
, and DELETE
commands with the optional RETURNING INTO
clause. This clause allows the SPL program to capture the newly added, modified, or deleted values from the results of an INSERT
, UPDATE
, or DELETE
command, respectively.
Syntax
insert
is a validINSERT
command.update
is a validUPDATE
command.delete
is a validDELETE
command.- If you specify
*
, then the values from the row affected by theINSERT
,UPDATE
, orDELETE
command are made available for assignment to the record or fields to the right of theINTO
keyword. (The use of*
is an EDB Postgres Advanced Server extension and isn't compatible with Oracle databases.) expr_1, expr_2...
are expressions evaluated upon the row affected by theINSERT
,UPDATE
, orDELETE
command. The evaluated results are assigned to the record or fields to the right of theINTO
keyword.record
is the identifier of a record that must contain fields that match in number and order and are data-type compatible with the values in theRETURNING
clause.field_1, field_2,...
are variables that must match in number and order and are data-type compatible with the set of values in theRETURNING
clause.
If the INSERT
, UPDATE
, or DELETE
command returns a result set with more than one row, then an exception is thrown with SQLCODE 01422, query returned more than one row
. If no rows are in the result set, then the variables following the INTO
keyword are set to null.
Note
A variation of RETURNING INTO
using the BULK COLLECT
clause allows a result set of more than one row that's returned into a collection. See Using the BULK COLLECT clause for more information.
Adding the RETURNING INTO clause
This example modifies the emp_comp_update
procedure introduced in UPDATE. It adds the RETURNING INTO
clause:
The following is the output from this procedure, assuming employee 9503
created by the emp_insert
procedure still exists in the table:
Adding the RETURNING INTO clause using record types
This example modifies the emp_delete
procedure, adding the RETURNING INTO
clause using record types:
The following is the output from this procedure: