RETURNING Clause With EXECUTE IMMEDIATE
1. Overview
The RETURNING clause allows you to retrieve values of columns (and expressions based on columns) that were modified by an insert, delete or update. Without RETURNING you would have to run a SELECT statement after the DML statement is completed, in order to obtain the values of the changed columns. So RETURNING helps avoid another roundtrip to the database, another context switch in a PL/SQL block. We can also use RETURNING with EXECUTE IMMEDIATE (for dynamically constructed and executed SQL statements).
2. Technologies and Tools Used
The following technologies have been used to achieve this
–>Oracle PL/SQL
3. Use Case
WE can also take advantage of the RETURNING clause when executing a dynamic SQL statement.
4. Examples
DECLARE
l_part_number parts.part_number%TYPE;
BEGIN
EXECUTE IMMEDIATE
q'[UPDATE parts
SET part_name = part_name || ‘1’
WHERE part_number = 100
RETURNING part_number INTO :one_pn]’
RETURNING INTO l_part_number;
DBMS_OUTPUT.put_line (l_part_number);
END;
/
RETURNING Multiple Rows in EXECUTE IMMEDIATE
In this variation you see how to use RETURNING with a dynamic SQL statement that modifies more than one row.
DECLARE
l_part_numbers DBMS_SQL.number_table;
BEGIN
EXECUTE IMMEDIATE
q'[UPDATE parts
SET part_name = part_name || ‘1’
RETURNING part_number INTO :pn_list]’
RETURNING BULK COLLECT INTO l_part_numbers;
FOR indx IN 1 .. l_part_numbers.COUNT
LOOP
DBMS_OUTPUT.put_line (l_part_numbers (indx));
END LOOP;
END;
