oracle return from,Oracle return into 用法

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');

returning into语句的主要作用是：

delete操作：returning返回的是delete之前的结果

insert操作:returning返回的是insert之后的结果

update操作:returning语句是返回update之后的结果

注意：returning into语句不支持insert into select 语句和merge语句

下面演示该语句的具体用法

--(1)获取添加的值

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

-- (2)更新和删除

DECLARE

L_ID T1.ID%TYPE;

BEGIN

UPDATE T1 SET DESCRIPTION = 'two2' WHERE ID = 2 RETURNING ID INTO L_ID;

DBMS_OUTPUT.PUT_LINE('UPDATE ID=' || L_ID);

DELETE FROM T1 WHERE DESCRIPTION = 'THREE' RETURNING ID INTO L_ID;

DBMS_OUTPUT.PUT_LINE('DELETE ID=' || L_ID);

COMMIT;

END;

SQL> /

UPDATE ID=2

DELETE ID=3

--(3)如果更新dml操作影响多条记录可以通过bulk collect into 来提取

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;

END;

SQL> /

update id=21

update id=22

update id=23

--(4)如果插入操作影响多行也可以获取

DECLARE

TYPE DESCRIPTION_TABLE_TYPE IS TABLE OF T1.DESCRIPTION%TYPE;

TYPE T1_TABLE_TYPE IS TABLE OF T1%ROWTYPE;

DESCRIPTION_TABLE DESCRIPTION_TABLE_TYPE := DESCRIPTION_TABLE_TYPE('FIVE',

'SIX',

'SEVEN');

T1_TABLE T1_TABLE_TYPE;

BEGIN

FORALL I IN DESCRIPTION_TABLE.FIRST .. DESCRIPTION_TABLE.LAST

INSERT INTO T1

VALUES

(T1_SEQ.NEXTVAL, DESCRIPTION_TABLE(I))

RETURNING ID, DESCRIPTION BULK COLLECT INTO T1_TABLE;

FOR I IN T1_TABLE.FIRST .. T1_TABLE.LAST LOOP

DBMS_OUTPUT.PUT_LINE('INSERT ID=' || T1_TABLE(I)

.ID || 'DESC=' || T1_TABLE(I).DESCRIPTION);

END LOOP;

END;

/

执行结果

INSERT ID=27DESC=FIVE

INSERT ID=28DESC=SIX

INSERT ID=29DESC=SEVEN

--forall指的是同时插入，如果使用for循环也可以插入三条记录，但默认returing只显示最后一条