软件开发培训班 >> 编程开发 >> Oracle


Oracle数据库中RETURNING子句的使用

RETURNING子句允许您检索插入、删除或更新所修改的列（以及基于列的表达式）的值 。如果不使用RETURNING，则必须在DML语句完成后运行SELECT语句，才能获得更改列的值 。因此，RETURNING有助于避免再次往返数据库，即PL/SQL块中的另一个上下文切换 。

RETURNING子句可以返回多行数据，在这种情况下，您将使用RETURNING BULK COLLECT INTO窗体 。

您还可以在RETURNING子句中调用聚合函数，以获取DML语句更改的多行中的列的总和、计数等 。

最后，还可以将RETURNING与EXECUTE IMMEDIATE一起使用（用于动态构建和执行的SQL语句） 。

1、基本用法

1.1、单行操作：

当对单行数据进行DML操作时，可以使用RETURNING子句将受影响行的列值返回给变量 。

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）：

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来使用 。

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语句更改的多行中的列的总和、计数等 。

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

-- 您可以使用组函数执行另一个SQL语句来检索这些信息 。

DECLARE l_total INTEGER; 
BEGIN 
   UPDATE employees 
      SET salary = salary * 2 
    WHERE DEPARTMENT_ID = 110;
   -- 要做SUM运算，需要写很多代码 。
   SELECT SUM (salary) 
     INTO l_total 
     FROM employees 
    WHERE DEPARTMENT_ID = 110;
 
   DBMS_OUTPUT.put_line (l_total); 
END;

-- 可以在PL/SQL中执行计算 。使用RETURNING可以收回所有修改后的工资 。然后对它们进行迭代，一条语句完成总和 。

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.

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子句返回单行

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子句返回多行

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特性或策略 。

到此这篇关于Oracle数据库中RETURNING子句的使用的文章就介绍到这了,更多相关Oracle RETURNING子句内容请搜索以前的文章或继续浏览下面的相关文章希望大家以后多多支持！