导读:
You can use the BULKCOLLECTclause in the RETURNINGINTOclause of an INSERT, UPDATE, or DELETEstatement:
CREATE TABLE emp_temp AS SELECT * FROM employees;
DECLARE
TYPE NumList IS TABLE OF employees.employee_id%TYPE;
enums NumList;
TYPE NameList IS TABLE OF employees.last_name%TYPE;
names NameList;
BEGIN
DELETE FROM emp_temp WHERE department_id = 30
RETURNING employee_id, last_name BULK COLLECT INTO enums, names;
DBMS_OUTPUT.PUT_LINE('Deleted ' || SQL%ROWCOUNT || ' rows:');
FOR i IN enums.FIRST .. enums.LAST
LOOP
DBMS_OUTPUT.PUT_LINE('Employee #' || enums(i) || ': ' || names(i));
END LOOP;
END;
/
以下Ref: http://yangtingkun.itpub.net/post/468/93315
ORACLE的DML语句中可以指定RETURNING语句。RETURNING语句的使用在很多情况下可以简化PL/SQL编程。
这里不打算说明RETURNING语句的使用(其实使用起来也很简单,和SELECT INTO语句没有多大区别。),主要打算说明RETURNING语句的几个特点。
其实这篇文章源于同事问我的一个问题:
使用UPDATE语句的时候,RETURNING得到的结果是UPDATE之前的结果还是UPDATE之后的结果?
这个问题把我问住了。考虑DELETE的情况,RETURNING返回的肯定是DELETE之前的结果,而考虑INSERT的情况,RETURNING返回的一定是INSERT之后的结果。但是UPDATE到底返回那种情况,就无法推断出来了。而且,由于一般在使用UPDATE的RETURNING语句时,都会返回主键列,而主键列一般都是不会修改的,因此确实不清楚Oracle返回的是UPDATE之前的结果还是之后的结果。
当然,一个简单的例子就可以测试出来:
SQL> CREATE TABLE T (ID NUMBER, NAME VARCHAR2(30));
表已创建。
SQL> SET SERVEROUT ON
SQL> DECLARE
2 V_NAME VARCHAR2(30);
3 BEGIN
4 INSERT INTO T VALUES (1, 'YANGTK') RETURNING NAME INTO V_NAME;
5 DBMS_OUTPUT.PUT_LINE('INSERT: ' || V_NAME);
6 V_NAME := NULL;
7 UPDATE T SET NAME = 'YTK' RETURNING NAME INTO V_NAME;
8 DBMS_OUTPUT.PUT_LINE('UPDATE: ' || V_NAME);
9 V_NAME := NULL;
10 DELETE T RETURNING NAME INTO V_NAME;
11 DBMS_OUTPUT.PUT_LINE('DELETE: ' || V_NAME);
12 END;
13 /
INSERT: YANGTK
UPDATE: YTK
DELETE: YTK
PL/SQL 过程已成功完成。
显然,UPDATE操作的RETURNING语句是返回UPDATE操作之后的结果。
顺便总结几个RETURNING操作相关的问题:
1.RETURNING语句似乎和RETURN通用。
SQL> SET SERVEROUT ON
SQL> DECLARE
2 V_NAME VARCHAR2(30);
3 BEGIN
4 INSERT INTO T VALUES (1, 'YANGTK') RETURN NAME INTO V_NAME;
5 DBMS_OUTPUT.PUT_LINE('INSERT: ' || V_NAME);
6 V_NAME := NULL;
7 UPDATE T SET NAME = 'YTK' RETURN NAME INTO V_NAME;
8 DBMS_OUTPUT.PUT_LINE('UPDATE: ' || V_NAME);
9 V_NAME := NULL;
10 DELETE T RETURN NAME INTO V_NAME;
11 DBMS_OUTPUT.PUT_LINE('DELETE: ' || V_NAME);
12 END;
13 /
INSERT: YANGTK
UPDATE: YTK
DELETE: YTK
PL/SQL 过程已成功完成。
2.RETURNING语句也可以使用SQLPLUS的变量,这样,RETURNING语句不一定非要用在PL/SQL语句中。
SQL> VAR V_NAME VARCHAR2(30)
SQL> INSERT INTO T VALUES (1, 'YANGTK') RETURNING NAME INTO :V_NAME;
已创建1 行。
SQL> PRINT V_NAME
V_NAME
--------------------------------
YANGTK
SQL> UPDATE T SET NAME = 'YTK' RETURNING NAME INTO :V_NAME;
已更新1 行。
SQL> PRINT V_NAME
V_NAME
--------------------------------
YTK
SQL> DELETE T RETURNING NAME INTO :V_NAME;
已删除1 行。
SQL> PRINT V_NAME
V_NAME
--------------------------------
YTK
3.INSERT INTO VALUES语句支持RETURNING语句,而INSERT INTO SELECT语句不支持。MERGE语句不支持RETURNING语句。
SQL> MERGE INTO T USING (SELECT * FROM T) T1
2 ON (T.ID = T1.ID)
3 WHEN MATCHED THEN UPDATE SET NAME = T1.NAME
4 WHEN NOT MATCHED THEN INSERT VALUES (T1.ID, T1.NAME)
5 RETURNING NAME INTO :V_NAME;
RETURNING NAME INTO :V_NAME
*第5 行出现错误:
ORA-00933: SQL 命令未正确结束
SQL> INSERT INTO T SELECT * FROM T RETURNING NAME INTO :V_NAME;
INSERT INTO T SELECT * FROM T RETURNING NAME INTO :V_NAME
*第1 行出现错误:
ORA-00933: SQL 命令未正确结束
这两个限制确实不大方便。不知道Oracle在以后版本中是否会放开。
个人感觉RETURNING语句和BULK COLLECT INTO语句配合使用的机会更多一些。
本文转自
http://xsb.itpub.net/post/419/130574
You can use the BULKCOLLECTclause in the RETURNINGINTOclause of an INSERT, UPDATE, or DELETEstatement:
CREATE TABLE emp_temp AS SELECT * FROM employees;
DECLARE
TYPE NumList IS TABLE OF employees.employee_id%TYPE;
enums NumList;
TYPE NameList IS TABLE OF employees.last_name%TYPE;
names NameList;
BEGIN
DELETE FROM emp_temp WHERE department_id = 30
RETURNING employee_id, last_name BULK COLLECT INTO enums, names;
DBMS_OUTPUT.PUT_LINE('Deleted ' || SQL%ROWCOUNT || ' rows:');
FOR i IN enums.FIRST .. enums.LAST
LOOP
DBMS_OUTPUT.PUT_LINE('Employee #' || enums(i) || ': ' || names(i));
END LOOP;
END;
/
以下Ref: http://yangtingkun.itpub.net/post/468/93315
ORACLE的DML语句中可以指定RETURNING语句。RETURNING语句的使用在很多情况下可以简化PL/SQL编程。
这里不打算说明RETURNING语句的使用(其实使用起来也很简单,和SELECT INTO语句没有多大区别。),主要打算说明RETURNING语句的几个特点。
其实这篇文章源于同事问我的一个问题:
使用UPDATE语句的时候,RETURNING得到的结果是UPDATE之前的结果还是UPDATE之后的结果?
这个问题把我问住了。考虑DELETE的情况,RETURNING返回的肯定是DELETE之前的结果,而考虑INSERT的情况,RETURNING返回的一定是INSERT之后的结果。但是UPDATE到底返回那种情况,就无法推断出来了。而且,由于一般在使用UPDATE的RETURNING语句时,都会返回主键列,而主键列一般都是不会修改的,因此确实不清楚Oracle返回的是UPDATE之前的结果还是之后的结果。
当然,一个简单的例子就可以测试出来:
SQL> CREATE TABLE T (ID NUMBER, NAME VARCHAR2(30));
表已创建。
SQL> SET SERVEROUT ON
SQL> DECLARE
2 V_NAME VARCHAR2(30);
3 BEGIN
4 INSERT INTO T VALUES (1, 'YANGTK') RETURNING NAME INTO V_NAME;
5 DBMS_OUTPUT.PUT_LINE('INSERT: ' || V_NAME);
6 V_NAME := NULL;
7 UPDATE T SET NAME = 'YTK' RETURNING NAME INTO V_NAME;
8 DBMS_OUTPUT.PUT_LINE('UPDATE: ' || V_NAME);
9 V_NAME := NULL;
10 DELETE T RETURNING NAME INTO V_NAME;
11 DBMS_OUTPUT.PUT_LINE('DELETE: ' || V_NAME);
12 END;
13 /
INSERT: YANGTK
UPDATE: YTK
DELETE: YTK
PL/SQL 过程已成功完成。
显然,UPDATE操作的RETURNING语句是返回UPDATE操作之后的结果。
顺便总结几个RETURNING操作相关的问题:
1.RETURNING语句似乎和RETURN通用。
SQL> SET SERVEROUT ON
SQL> DECLARE
2 V_NAME VARCHAR2(30);
3 BEGIN
4 INSERT INTO T VALUES (1, 'YANGTK') RETURN NAME INTO V_NAME;
5 DBMS_OUTPUT.PUT_LINE('INSERT: ' || V_NAME);
6 V_NAME := NULL;
7 UPDATE T SET NAME = 'YTK' RETURN NAME INTO V_NAME;
8 DBMS_OUTPUT.PUT_LINE('UPDATE: ' || V_NAME);
9 V_NAME := NULL;
10 DELETE T RETURN NAME INTO V_NAME;
11 DBMS_OUTPUT.PUT_LINE('DELETE: ' || V_NAME);
12 END;
13 /
INSERT: YANGTK
UPDATE: YTK
DELETE: YTK
PL/SQL 过程已成功完成。
2.RETURNING语句也可以使用SQLPLUS的变量,这样,RETURNING语句不一定非要用在PL/SQL语句中。
SQL> VAR V_NAME VARCHAR2(30)
SQL> INSERT INTO T VALUES (1, 'YANGTK') RETURNING NAME INTO :V_NAME;
已创建1 行。
SQL> PRINT V_NAME
V_NAME
--------------------------------
YANGTK
SQL> UPDATE T SET NAME = 'YTK' RETURNING NAME INTO :V_NAME;
已更新1 行。
SQL> PRINT V_NAME
V_NAME
--------------------------------
YTK
SQL> DELETE T RETURNING NAME INTO :V_NAME;
已删除1 行。
SQL> PRINT V_NAME
V_NAME
--------------------------------
YTK
3.INSERT INTO VALUES语句支持RETURNING语句,而INSERT INTO SELECT语句不支持。MERGE语句不支持RETURNING语句。
SQL> MERGE INTO T USING (SELECT * FROM T) T1
2 ON (T.ID = T1.ID)
3 WHEN MATCHED THEN UPDATE SET NAME = T1.NAME
4 WHEN NOT MATCHED THEN INSERT VALUES (T1.ID, T1.NAME)
5 RETURNING NAME INTO :V_NAME;
RETURNING NAME INTO :V_NAME
*第5 行出现错误:
ORA-00933: SQL 命令未正确结束
SQL> INSERT INTO T SELECT * FROM T RETURNING NAME INTO :V_NAME;
INSERT INTO T SELECT * FROM T RETURNING NAME INTO :V_NAME
*第1 行出现错误:
ORA-00933: SQL 命令未正确结束
这两个限制确实不大方便。不知道Oracle在以后版本中是否会放开。
个人感觉RETURNING语句和BULK COLLECT INTO语句配合使用的机会更多一些。
本文转自
http://xsb.itpub.net/post/419/130574