ORACLE RETURNING 语句的使用方法

1.The 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.

?

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;

?

2.When we insert data using a sequence to generate our primary key value, we can return the primary key value as follows.

?

SET SERVEROUTPUT ONDECLAREv_id t1.id%TYPE;BEGININSERT INTO t1VALUES (t1_seq.nextval, 'FOUR')RETURNING id INTO v_id;COMMIT;DBMS_OUTPUT.put_line('ID=' || v_id);END;/

?

ID=4

?

3.The syntax is also available for update and delete statements.

?

SET SERVEROUTPUT ONDECLAREv_id t1.id%TYPE;BEGINUPDATE t1SET description = descriptionWHERE description = 'FOUR'RETURNING id INTO v_id;DBMS_OUTPUT.put_line('UPDATE ID=' || v_id);DELETE FROM t1WHERE description = 'FOUR'RETURNING id INTO v_id;DBMS_OUTPUT.put_line('DELETE ID=' || v_id);COMMIT;END;/

?

UPDATESET SERVEROUTPUT ONDECLARETYPE t_tab IS TABLE OF t1.id%TYPE;v_tab t_tab;BEGINUPDATE t1SET description = descriptionRETURNING id BULK COLLECT INTO v_tab;FOR i IN v_tab.first .. l_tab.last LOOPDBMS_OUTPUT.put_line('UPDATE ID=' || l_tab(i));END LOOP;COMMIT;END;/

UPDATESET SERVEROUTPUT ONDECLARETYPE 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;BEGINFORALL i IN l_desc_tab.first .. l_desc_tab.lastINSERT INTO t1VALUES (t1_seq.nextval, l_desc_tab(i))RETURNING id, description BULK COLLECT INTO l_tab;FOR i IN l_tab.first .. l_tab.last LOOPDBMS_OUTPUT.put_line('INSERT ID=' || l_tab(i).id || ' DESC=' || l_tab(i).description);END LOOP;COMMIT;END;?/

INSERTSET SERVEROUTPUT ONDECLARETYPE t_tab IS TABLE OF t1.id%TYPE;l_tab t_tab;BEGINEXECUTE IMMEDIATE 'UPDATE t1SET description = descriptionRETURNING id INTO :l_tab'RETURNING BULK COLLECT INTO l_tab;FOR i IN l_tab.first .. l_tab.last LOOPDBMS_OUTPUT.put_line('UPDATE ID=' || l_tab(i));END LOOP;COMMIT;END;/

UPDATE ID=1

UPDATE ID=2

UPDATE ID=3

?