- 爱易网页
-
数据库教程
- Returnning Into 小结
日期:2014-05-16 浏览次数:20418 次
Returnning Into 总结
ORACLE的DML语句中可以指定RETURNING语句。使用起来也很简单,和SELECT INTO语句没有多大区别。RETURNING语句的使用在很多情况下可以简化PL/SQL编程。
DELETE操作:RETURNING返回的是DELETE之前的结果;
INSERT操作:RETURNING返回的是INSERT之后的结果;
UPDATE操作:的RETURNING语句是返回UPDATE操作之后的结果。
INSERT INTO SELECT语句不支持。
MERGE语句不支持RETURNING语句。
Example:
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;When we insert data using a sequence to generate our primary key value, we can return the primary key value as follows.
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>The syntax is also available for update and delete statements.
SET 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>When DML affects multiple rows we can still use the RETURNING INTO, but now we must return the values into a collection using the BULK COLLECT clause.
SET 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>We can also use the RETURNING INTO clause in combination with bulk binds.
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.