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