the
Hope this helps. Regards Tim...
from: http://www.oracle-base.com/articles/misc/dml_returning_into_clause.php
--End--
RETURNING INTO
clause allows us to return column values for rows affected by DML statements. The following test table is used to demonstrate this clause.
When we insert data using a sequence to generate our primary key value, we can return the primary key value as follows.DROP TABLE t1; DROP SEQUENCE t1_seq; CREATE TABLE t1 ( id NUMBER(10), description VARCHAR2(50), CONSTRAINT t1_pk PRIMARY KEY (id) ); CREATE SEQUENCE t1_seq; INSERT INTO t1 VALUES (t1_seq.nextval, 'ONE'); INSERT INTO t1 VALUES (t1_seq.nextval, 'TWO'); INSERT INTO t1 VALUES (t1_seq.nextval, 'THREE'); COMMIT;
The syntax is also available for update and delete statements.SET SERVEROUTPUT ON DECLARE l_id t1.id%TYPE; BEGIN INSERT INTO t1 VALUES (t1_seq.nextval, 'FOUR') RETURNING id INTO l_id; COMMIT; DBMS_OUTPUT.put_line('ID=' || l_id); END; / ID=4 PL/SQL procedure successfully completed. SQL>
When DML affects multiple rows we can still use theSET SERVEROUTPUT ON DECLARE l_id t1.id%TYPE; BEGIN UPDATE t1 SET description = description WHERE description = 'FOUR' RETURNING id INTO l_id; DBMS_OUTPUT.put_line('UPDATE ID=' || l_id); DELETE FROM t1 WHERE description = 'FOUR' RETURNING id INTO l_id; DBMS_OUTPUT.put_line('DELETE ID=' || l_id); COMMIT; END; / UPDATE ID=4 DELETE ID=4 PL/SQL procedure successfully completed. SQL>
RETURNING INTO
, but now we must return the values into a collection using the
BULK COLLECT
clause.
We can also use theSET SERVEROUTPUT ON DECLARE TYPE t_tab IS TABLE OF t1.id%TYPE; l_tab t_tab; BEGIN UPDATE t1 SET description = description RETURNING id BULK COLLECT INTO l_tab; FOR i IN l_tab.first .. l_tab.last LOOP DBMS_OUTPUT.put_line('UPDATE ID=' || l_tab(i)); END LOOP; COMMIT; END; / UPDATE ID=1 UPDATE ID=2 UPDATE ID=3 PL/SQL procedure successfully completed. SQL>
RETURNING INTO
clause in combination with bulk binds.
This functionality is also available from dymanic SQL.SET SERVEROUTPUT ON DECLARE TYPE t_desc_tab IS TABLE OF t1.description%TYPE; TYPE t_tab IS TABLE OF t1%ROWTYPE; l_desc_tab t_desc_tab := t_desc_tab('FIVE', 'SIX', 'SEVEN'); l_tab t_tab; BEGIN FORALL i IN l_desc_tab.first .. l_desc_tab.last INSERT INTO t1 VALUES (t1_seq.nextval, l_desc_tab(i)) RETURNING id, description BULK COLLECT INTO l_tab; FOR i IN l_tab.first .. l_tab.last LOOP DBMS_OUTPUT.put_line('INSERT ID=' || l_tab(i).id || ' DESC=' || l_tab(i).description); END LOOP; COMMIT; END; / INSERT ID=5 DESC=FIVE INSERT ID=6 DESC=SIX INSERT ID=7 DESC=SEVEN PL/SQL procedure successfully completed. SQL>
For more information see:SET SERVEROUTPUT ON DECLARE TYPE t_tab IS TABLE OF t1.id%TYPE; l_tab t_tab; BEGIN EXECUTE IMMEDIATE 'UPDATE t1 SET description = description RETURNING id INTO :l_tab' RETURNING BULK COLLECT INTO l_tab; FOR i IN l_tab.first .. l_tab.last LOOP DBMS_OUTPUT.put_line('UPDATE ID=' || l_tab(i)); END LOOP; COMMIT; END; / UPDATE ID=1 UPDATE ID=2 UPDATE ID=3 PL/SQL procedure successfully completed. SQL>
Hope this helps. Regards Tim...
from: http://www.oracle-base.com/articles/misc/dml_returning_into_clause.php
--End--