Oracle數(shù)據(jù)庫中RETURNING子句的使用

更新時間：2024年08月05日 10:39:03 作者：zxrhhm

RETURNING子句允許您檢索插入、刪除或更新所修改的列的值,本文主要介紹了Oracle數(shù)據(jù)庫中RETURNING子句的使用,感興趣的可以了解一下

RETURNING子句允許您檢索插入、刪除或更新所修改的列（以及基于列的表達式）的值。如果不使用RETURNING，則必須在DML語句完成后運行SELECT語句，才能獲得更改列的值。因此，RETURNING有助于避免再次往返數(shù)據(jù)庫，即PL/SQL塊中的另一個上下文切換。

RETURNING子句可以返回多行數(shù)據(jù)，在這種情況下，您將使用RETURNING BULK COLLECT INTO窗體。

您還可以在RETURNING子句中調(diào)用聚合函數(shù)，以獲取DML語句更改的多行中的列的總和、計數(shù)等。

最后，還可以將RETURNING與EXECUTE IMMEDIATE一起使用（用于動態(tài)構建和執(zhí)行的SQL語句）。

1、基本用法

1.1、單行操作：

當對單行數(shù)據(jù)進行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、多行操作：

當對多行數(shù)據(jù)進行DML操作時，需要使用PL/SQL的集合類型（如TABLE OF類型或嵌套表）來接收返回的多行數(shù)據(jù)。

示例（使用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類型

對于需要同時處理多列數(shù)據(jù)的情況，可以使用PL/SQL的RECORD類型來定義一個能夠包含多列數(shù)據(jù)的復合類型，然后結合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子句中調(diào)用聚合函數(shù)

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子句中調(diào)用聚合函數(shù)，以獲取DML語句更改的多行中的列的總和、計數(shù)等。

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

-- 您可以使用組函數(shù)執(zhí)行另一個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中執(zhí)行計算。使用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子句中直接調(diào)用SUM、COUNT等，從而在將數(shù)據(jù)返回到PL/SQL塊之前執(zhí)行分析。非?？?/p>

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一起使用（用于動態(tài)構建和執(zhí)行的SQL語句）

4.1、在執(zhí)行動態(tài)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、在執(zhí)行動態(tài)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操作或遠程對象一起使用。
在通過視圖向基表中插入數(shù)據(jù)時，RETURNING子句只能與單基表視圖一起使用。
對于UPDATE和DELETE語句，RETURNING子句可以返回舊值（在Oracle 23ai/c及更高版本中增強）和新值，但對于INSERT語句，它只返回新值（因為插入前沒有舊值）。
在使用RETURNING子句時，必須確保返回的列與INTO子句中指定的變量類型兼容。
在動態(tài)SQL中使用RETURNING子句時，需要注意綁定變量的使用，并且RETURNING BULK COLLECT INTO通常需要在

6、Oracle 23ai/c及更高版本中

在Oracle 23c及更高版本中，你可以使用FLASHBACK QUERY或AS OF VERSIONS BETWEEN子句（在適當?shù)那闆r下）與RETURNING子句結合來訪問舊值，但這通常不是直接返回舊值和新值的方式。實際上，更常見的是利用Oracle的閃回技術（如Flashback Data Archive）或觸發(fā)器（Triggers）來捕獲舊值。

但是，對于UPDATE和DELETE操作，如果你想要在同一個操作中同時獲取舊值和新值，你可能需要采取以下策略之一：

使用觸發(fā)器：在UPDATE或DELETE操作之前，使用觸發(fā)器來捕獲舊值，并將它們存儲在另一個表或PL/SQL變量中。然后，你可以通過RETURNING子句獲取新值。
使用PL/SQL變量：如果你正在執(zhí)行單行操作，你可以在PL/SQL中先查詢要更新的行以獲取舊值，然后執(zhí)行UPDATE或DELETE操作，并使用RETURNING子句獲取新值。
利用Oracle的內(nèi)置功能（如果可用）：在某些Oracle版本中，可能有特定的內(nèi)置函數(shù)或特性允許你同時訪問舊值和新值，但這通常不是通過RETURNING子句直接實現(xiàn)的。
使用版本化表（如Oracle Total Recall或Flashback Data Archive）：這些特性允許你查詢表的歷史版本，從而可以間接地獲取舊值。
在SQL*Plus或SQLcl中使用SET SERVEROUTPUT ON和DBMS_OUTPUT.PUT_LINE：雖然這不會直接返回舊值和新值到客戶端，但你可以在PL/SQL塊中使用這些工具來打印出你在執(zhí)行DML操作時捕獲的舊值和新值。

請記住，RETURNING子句本身在Oracle 23c及更高版本中并沒有直接提供返回舊值和新值的功能。相反，它主要用于在DML操作后返回新值給PL/SQL程序或觸發(fā)器中的變量。如果你需要舊值，你可能需要結合使用其他Oracle特性或策略。

到此這篇關于Oracle數(shù)據(jù)庫中RETURNING子句的使用的文章就介紹到這了,更多相關Oracle RETURNING子句內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關文章希望大家以后多多支持腳本之家！

您可能感興趣的文章: