RETURNING的使用:
ORACLE的DML(INSERT,DELETE,UPDATE)语句中可以指定RETURNING语句。
而在SELECT语句(DQL)中使用SELECT INTO语句返回值；
注意；一般在使用UPDATE的RETURNING语句时，都会返回主键列，当返回的列是修改的一列的情况下，将返回修改后的值。
(Oracle Database 10G - The Complete Reference - Mcgraw Hill Osborne.pdf--597页讲解)
下载地址:http://dl.dbank.com/c03yi4jjvj(如果下载存在问题，联系本人-邮箱-caroline0803@163.com)
The returning into clause is only used in insert, update, and delete commands that have a returning clause. For each value returned by the command, there must be a corresponding variable in the returning into clause.
英文解释:
The RETURNING clause was implemented as part of the Oracle 10g release and is used to return information about the effected rows after issuing Data Manipulation Language (DML) statements.
SYNTAX(语法)：
INSERT INTO <table> (c1, c2, .., cn)
VALUES (v1, v2, .., vn)
RETURNING <expression>
INTO <variables>
UPDATE <table> SET (c1) = (v1), (c2) = (v2), (cn) = (vn)
WHERE <condition>
RETURNING <expression>
INTO <variables>
DELETE FROM <table>
WHERE <condition>
RETURNING <expression>
INTO <variables>
--------------------------------------------------------------------
以下为实例
--绑定变量的实例:使用了USING和EXEXUTE IMMEDIATELY
1 DECLARE
2 SQL_STMT VARCHAR2(200);
3 SYMBOL VARCHAR2(6) := 'ADSP';
4 CLOSETODAY NUMBER(6, 2);
5 BEGIN
6 SQL_STMT := 'update STOCK set CloseToday = 32 WHERE Symbol = :1 RETURNING CloseToday INTO :2';
7 EXECUTE IMMEDIATE SQL_STMT
8 USING SYMBOL
9 RETURNING INTO CLOSETODAY;
10 END;
--普通测试：
1 DECLARE
2 V_NAME VARCHAR2(100);
3 TYPE V_NAME_TAB IS TABLE OF VARCHAR2(100) INDEX BY PLS_INTEGER;
4 NAMES V_NAME_TAB;
5 BEGIN
6 --COLLECTION返回
7 UPDATE EMP
8 SET EMP.ENAME = 'Q'
9 RETURNING EMP.ENAME BULK COLLECT INTO NAMES;
10 FOR IDX IN NAMES.FIRST .. NAMES.LAST LOOP
11 --dbms_output.put_line(idx);
12 NULL;
13 END LOOP;
14
15 --单条返回
16 UPDATE EMP
17 SET EMP.ENAME = 'X'
18 WHERE EMP.EMPNO = 7369
19 RETURNING EMP.ENAME INTO V_NAME;
20 ROLLBACK;
21 END;