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只显示最后一条

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/24627116/viewspace-754492/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/24627116/viewspace-754492/