RETURNING子句允许您检索插入、删除或更新所修改的列（以及基于列的表达式）的值。如果不使用RETURNING，则必须在DML语句完成后运行SELECT语句，才能获得更改列的值。因此，RETURNING有助于避免再次往返数据库，即PL/SQL块中的另一个上下文切换。
RETURNING子句可以返回多行数据，在这种情况下，您将使用RETURNING BULK COLLECT INTO窗体。
您还可以在RETURNING子句中调用聚合函数，以获取DML语句更改的多行中的列的总和、计数等。
最后，还可以将RETURNING与EXECUTE IMMEDIATE一起使用（用于动态构建和执行的SQL语句）。
1、基本用法
1.1、单行操作：
当对单行数据进行DML操作时，可以使用RETURNING子句将受影响行的列值返回给变量。
1 2 3 4 5 6 7 8 9 10 11 12 | DECLARE
v_empno employees.EMPLOYEE_ID%TYPE;
v_ename employees.FIRST_NAME%TYPE;
BEGIN
UPDATE employees SET FIRST_NAME = 'superdb' WHERE EMPLOYEE_ID = 206 RETURNING EMPLOYEE_ID, FIRST_NAME INTO v_empno, v_ename;
DBMS_OUTPUT.PUT_LINE('Updated EMPLOYEE_ID: ' || v_empno || ', FIRST_NAME: ' || v_ename);
END;
/
Updated EMPLOYEE_ID: 206, FIRST_NAME: superdb
PL/SQL procedure successfully completed.
|
1.2、多行操作：
当对多行数据进行DML操作时，需要使用PL/SQL的集合类型（如TABLE OF类型或嵌套表）来接收返回的多行数据。
示例（使用BULK COLLECT INTO）：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 | HR@orcl> select EMPLOYEE_ID, FIRST_NAME,SALARY FROM employees WHERE DEPARTMENT_ID = 110 ;
EMPLOYEE_ID FIRST_NAME SALARY
205 Shelley 12008
206 William 8300
DECLARE
TYPE emp_tab IS TABLE OF employees.EMPLOYEE_ID%TYPE INDEX BY PLS_INTEGER;
v_empnos emp_tab;
TYPE name_tab IS TABLE OF employees.FIRST_NAME%TYPE INDEX BY PLS_INTEGER;
v_enames name_tab;
BEGIN
UPDATE employees SET FIRST_NAME = 'John Doe' WHERE DEPARTMENT_ID = 110
RETURNING EMPLOYEE_ID, FIRST_NAME BULK COLLECT INTO v_empnos, v_enames;
FOR i IN 1 .. v_empnos.COUNT LOOP
DBMS_OUTPUT.PUT_LINE('Empno: ' || v_empnos(i) || ', Ename: ' || v_enames(i));
END LOOP;
END;
/
Empno: 205, Ename: John Doe
Empno: 206, Ename: John Doe
PL/SQL procedure successfully completed.
|
2、使用RECORD类型
对于需要同时处理多列数据的情况，可以使用PL/SQL的RECORD类型来定义一个能够包含多列数据的复合类型，然后结合BULK COLLECT INTO来使用。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 | DECLARE
TYPE emp_rec IS RECORD (
empno employees.EMPLOYEE_ID%TYPE,
ename employees.FIRST_NAME%TYPE
);
TYPE emp_tab IS TABLE OF emp_rec INDEX BY PLS_INTEGER;
v_emps emp_tab;
BEGIN
UPDATE employees SET FIRST_NAME = 'superdb' WHERE DEPARTMENT_ID = 110
RETURNING EMPLOYEE_ID, FIRST_NAME BULK COLLECT INTO v_emps;
FOR i IN 1 .. v_emps.COUNT LOOP
DBMS_OUTPUT.PUT_LINE('Empno: ' || v_emps(i).empno || ', Ename: ' || v_emps(i).ename);
END LOOP;
END;
/
Empno: 205, Ename: superdb
Empno: 206, Ename: superdb
PL/SQL procedure successfully completed.
|
3、RETURNING子句中调用聚合函数
You can also call aggregate functions in the RETURNING clause to obtain sums, counts and so on of columns in multiple rows changed by the DML statement.
还可以在RETURNING子句中调用聚合函数，以获取DML语句更改的多行中的列的总和、计数等。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 | HR@orcl> select EMPLOYEE_ID, FIRST_NAME,SALARY FROM employees WHERE DEPARTMENT_ID = 110 ;
EMPLOYEE_ID FIRST_NAME SALARY
205 Shelley 12008
206 William 8300
DECLARE l_total INTEGER;
BEGIN
UPDATE employees
SET salary = salary * 2
WHERE DEPARTMENT_ID = 110;
SELECT SUM (salary)
INTO l_total
FROM employees
WHERE DEPARTMENT_ID = 110;
DBMS_OUTPUT.put_line (l_total);
END;
DECLARE
l_salaries DBMS_SQL.number_table;
l_total INTEGER := 0;
BEGIN
UPDATE employees
SET salary = salary * 2
WHERE DEPARTMENT_ID = 110
RETURNING salary
BULK COLLECT INTO l_salaries;
FOR indx IN 1 .. l_salaries.COUNT
LOOP
l_total := l_total + l_salaries (indx);
END LOOP;
DBMS_OUTPUT.put_line (l_total);
END;
/
|
您可以在RETURNING子句中直接调用SUM、COUNT等，从而在将数据返回到PL/SQL块之前执行分析。非常酷
Yes! You can call SUM, COUNT, etc. directly in the RETURNING clause and thereby perform analytics before you return the data back to your PL/SQL block. Very cool.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | HR@orcl> select EMPLOYEE_ID, FIRST_NAME,SALARY FROM employees WHERE DEPARTMENT_ID = 110 ;
EMPLOYEE_ID FIRST_NAME SALARY
205 Shelley 12008
206 William 8300
DECLARE l_total INTEGER;
BEGIN
UPDATE employees
SET salary = salary * 2
WHERE DEPARTMENT_ID = 110
RETURNING SUM (salary) INTO l_total;
DBMS_OUTPUT.put_line (l_total);
END;
/
|
4、RETURNING与EXECUTE IMMEDIATE一起使用
you can also use RETURNING with EXECUTE IMMEDIATE (for dynamically constructed and executed SQL statements).
还可以将RETURNING与EXECUTE IMMEDIATE一起使用（用于动态构建和执行的SQL语句）
4.1、在执行动态SQL语句时，利用RETURNING子句返回单行
1 2 3 4 5 6 7 8 9 10 11 12 13 | DECLARE
l_EMPLOYEE_ID employees.EMPLOYEE_ID%TYPE;
BEGIN
EXECUTE IMMEDIATE
q'[UPDATE employees
SET FIRST_NAME = FIRST_NAME || '-1'
WHERE EMPLOYEE_ID=206
RETURNING EMPLOYEE_ID INTO :one_para_id]'
RETURNING INTO l_EMPLOYEE_ID;
DBMS_OUTPUT.put_line (l_EMPLOYEE_ID);
END;
/
|
4.2、在执行动态SQL语句时，利用RETURNING子句返回多行
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | DECLARE
l_EMPLOYEE_ID DBMS_SQL.number_table;
BEGIN
EXECUTE IMMEDIATE
q'[UPDATE employees
SET FIRST_NAME = FIRST_NAME || 'list'
WHERE DEPARTMENT_ID = 110
RETURNING EMPLOYEE_ID INTO :para_list]'
RETURNING BULK COLLECT INTO l_EMPLOYEE_ID;
FOR indx IN 1 .. l_EMPLOYEE_ID.COUNT
LOOP
DBMS_OUTPUT.put_line (l_EMPLOYEE_ID (indx));
END LOOP;
END;
/
|
5、限制和注意事项
RETURNING子句不能与并行DML操作或远程对象一起使用。
在通过视图向基表中插入数据时，RETURNING子句只能与单基表视图一起使用。
对于UPDATE和DELETE语句，RETURNING子句可以返回旧值（在Oracle 23ai/c及更高版本中增强）和新值，但对于INSERT语句，它只返回新值（因为插入前没有旧值）。
在使用RETURNING子句时，必须确保返回的列与INTO子句中指定的变量类型兼容。
在动态SQL中使用RETURNING子句时，需要注意绑定变量的使用，并且RETURNING BULK COLLECT INTO通常需要在
6、Oracle 23ai/c及更高版本中
在Oracle 23c及更高版本中，你可以使用FLASHBACK QUERY或AS OF VERSIONS BETWEEN子句（在适当的情况下）与RETURNING子句结合来访问旧值，但这通常不是直接返回旧值和新值的方式。实际上，更常见的是利用Oracle的闪回技术（如Flashback Data Archive）或触发器（Triggers）来捕获旧值。
但是，对于UPDATE和DELETE操作，如果你想要在同一个操作中同时获取旧值和新值，你可能需要采取以下策略之一：
使用触发器：在UPDATE或DELETE操作之前，使用触发器来捕获旧值，并将它们存储在另一个表或PL/SQL变量中。然后，你可以通过RETURNING子句获取新值。
使用PL/SQL变量：如果你正在执行单行操作，你可以在PL/SQL中先查询要更新的行以获取旧值，然后执行UPDATE或DELETE操作，并使用RETURNING子句获取新值。
利用Oracle的内置功能（如果可用）：在某些Oracle版本中，可能有特定的内置函数或特性允许你同时访问旧值和新值，但这通常不是通过RETURNING子句直接实现的。
使用版本化表（如Oracle Total Recall或Flashback Data Archive）：这些特性允许你查询表的历史版本，从而可以间接地获取旧值。
在SQL*Plus或SQLcl中使用SET SERVEROUTPUT ON和DBMS_OUTPUT.PUT_LINE：虽然这不会直接返回旧值和新值到客户端，但你可以在PL/SQL块中使用这些工具来打印出你在执行DML操作时捕获的旧值和新值。
请记住，RETURNING子句本身在Oracle 23c及更高版本中并没有直接提供返回旧值和新值的功能。相反，它主要用于在DML操作后返回新值给PL/SQL程序或触发器中的变量。如果你需要旧值，你可能需要结合使用其他Oracle特性或策略。
