Oracle數(shù)據(jù)庫中RETURNING子句的使用
RETURNING子句允許您檢索插入、刪除或更新所修改的列(以及基于列的表達式)的值。如果不使用RETURNING,則必須在DML語句完成后運行SELECT語句,才能獲得更改列的值。因此,RETURNING有助于避免再次往返數(shù)據(jù)庫,即PL/SQL塊中的另一個上下文切換。
RETURNING子句可以返回多行數(shù)據(jù),在這種情況下,您將使用RETURNING BULK COLLECT INTO窗體。
您還可以在RETURNING子句中調用聚合函數(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子句中調用聚合函數(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子句中調用聚合函數(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子句中直接調用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的內置功能(如果可用):在某些Oracle版本中,可能有特定的內置函數(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子句內容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關文章希望大家以后多多支持腳本之家!
相關文章
在Oracle數(shù)據(jù)庫表中加載多個數(shù)據(jù)文件的方法詳解
這篇文章主要給大家介紹了在Oracle數(shù)據(jù)庫表中加載多個數(shù)據(jù)文件的方法,本文中,我將展示 SQL 加載器 + Unix 腳本實用程序的強大功能,其中 SQL 加載器可以使用自動 shell 腳本加載多個數(shù)據(jù)文件,需要的朋友可以參考下2024-01-01連接Oracle數(shù)據(jù)庫失敗(ORA-12514)故障排除全過程
Oracle連接失敗是指在使用Oracle數(shù)據(jù)庫進行開發(fā)的過程中,服務器端無法與客戶端連接,從而導致Oracle連接無法成功,影響開發(fā)的效率,下面這篇文章主要給大家介紹了關于連接Oracle數(shù)據(jù)庫失敗(ORA-12514)故障排除的相關資料,需要的朋友可以參考下2023-05-05Oracle和MySQL的數(shù)據(jù)導入為何差別這么大
這篇文章主要介紹了Oracle和MySQL的數(shù)據(jù)導入有哪些區(qū)別,幫助大家更好的理解和學習,感興趣的朋友可以了解下2020-08-08JDBC Oracle執(zhí)行executeUpdate卡死問題的解決方案
今天小編就為大家分享一篇關于JDBC Oracle執(zhí)行executeUpdate卡死問題的解決方案,小編覺得內容挺不錯的,現(xiàn)在分享給大家,具有很好的參考價值,需要的朋友一起跟隨小編來看看吧2018-12-12