Oracle數(shù)據(jù)庫中RETURNING子句的使用
RETURNING子句允許您檢索插入、刪除或更新所修改的列（以及基于列的表達(dá)式）的值。如果不使用RETURNING，則必須在DML語句完成后運(yùn)行SELECT語句，才能獲得更改列的值。因此，RETURNING有助于避免再次往返數(shù)據(jù)庫，即PL/SQL塊中的另一個(gè)上下文切換。
RETURNING子句可以返回多行數(shù)據(jù)，在這種情況下，您將使用RETURNING BULK COLLECT INTO窗體。
您還可以在RETURNING子句中調(diào)用聚合函數(shù)，以獲取DML語句更改的多行中的列的總和、計(jì)數(shù)等。
最后，還可以將RETURNING與EXECUTE IMMEDIATE一起使用（用于動態(tài)構(gòu)建和執(zhí)行的SQL語句）。
1、基本用法
1.1、單行操作：
當(dāng)對單行數(shù)據(jù)進(jìn)行DML操作時(shí)，可以使用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、多行操作：
當(dāng)對多行數(shù)據(jù)進(jìn)行DML操作時(shí)，需要使用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í)處理多列數(shù)據(jù)的情況，可以使用PL/SQL的RECORD類型來定義一個(gè)能夠包含多列數(shù)據(jù)的復(fù)合類型，然后結(jié)合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語句更改的多行中的列的總和、計(jì)數(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í)行另一個(gè)SQL語句來檢索這些信息。
DECLARE l_total INTEGER;
BEGIN
UPDATE employees
SET salary = salary * 2
WHERE DEPARTMENT_ID = 110;
-- 要做SUM運(yùn)算，需要寫很多代碼。
SELECT SUM (salary)
INTO l_total
FROM employees
WHERE DEPARTMENT_ID = 110;
DBMS_OUTPUT.put_line (l_total);
END;
-- 可以在PL/SQL中執(zhí)行計(jì)算。使用RETURNING可以收回所有修改后的工資。然后對它們進(jìn)行迭代，一條語句完成總和。
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)構(gòu)建和執(zhí)行的SQL語句）
4.1、在執(zhí)行動態(tài)SQL語句時(shí)，利用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語句時(shí)，利用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、限制和注意事項(xiàng)
- RETURNING子句不能與并行DML操作或遠(yuǎn)程對象一起使用。
- 在通過視圖向基表中插入數(shù)據(jù)時(shí)，RETURNING子句只能與單基表視圖一起使用。
- 對于UPDATE和DELETE語句，RETURNING子句可以返回舊值（在Oracle 23ai/c及更高版本中增強(qiáng)）和新值，但對于INSERT語句，它只返回新值（因?yàn)椴迦肭皼]有舊值）。
- 在使用RETURNING子句時(shí)，必須確保返回的列與INTO子句中指定的變量類型兼容。
- 在動態(tài)SQL中使用RETURNING子句時(shí)，需要注意綁定變量的使用，并且RETURNING BULK COLLECT INTO通常需要在
6、Oracle 23ai/c及更高版本中
在Oracle 23c及更高版本中，你可以使用FLASHBACK QUERY或AS OF VERSIONS BETWEEN子句（在適當(dāng)?shù)那闆r下）與RETURNING子句結(jié)合來訪問舊值，但這通常不是直接返回舊值和新值的方式。實(shí)際上，更常見的是利用Oracle的閃回技術(shù)（如Flashback Data Archive）或觸發(fā)器（Triggers）來捕獲舊值。
但是，對于UPDATE和DELETE操作，如果你想要在同一個(gè)操作中同時(shí)獲取舊值和新值，你可能需要采取以下策略之一：
- 使用觸發(fā)器：在UPDATE或DELETE操作之前，使用觸發(fā)器來捕獲舊值，并將它們存儲在另一個(gè)表或PL/SQL變量中。然后，你可以通過RETURNING子句獲取新值。
- 使用PL/SQL變量：如果你正在執(zhí)行單行操作，你可以在PL/SQL中先查詢要更新的行以獲取舊值，然后執(zhí)行UPDATE或DELETE操作，并使用RETURNING子句獲取新值。
- 利用Oracle的內(nèi)置功能（如果可用）：在某些Oracle版本中，可能有特定的內(nèi)置函數(shù)或特性允許你同時(shí)訪問舊值和新值，但這通常不是通過RETURNING子句直接實(shí)現(xiàn)的。
- 使用版本化表（如Oracle Total Recall或Flashback Data Archive）：這些特性允許你查詢表的歷史版本，從而可以間接地獲取舊值。
- 在SQL*Plus或SQLcl中使用SET SERVEROUTPUT ON和DBMS_OUTPUT.PUT_LINE：雖然這不會直接返回舊值和新值到客戶端，但你可以在PL/SQL塊中使用這些工具來打印出你在執(zhí)行DML操作時(shí)捕獲的舊值和新值。
請記住，RETURNING子句本身在Oracle 23c及更高版本中并沒有直接提供返回舊值和新值的功能。相反，它主要用于在DML操作后返回新值給PL/SQL程序或觸發(fā)器中的變量。如果你需要舊值，你可能需要結(jié)合使用其他Oracle特性或策略。
到此這篇關(guān)于Oracle數(shù)據(jù)庫中RETURNING子句的使用的文章就介紹到這了,更多相關(guān)Oracle RETURNING子句內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！
相關(guān)文章
這篇文章主要介紹了Oracle中null值和空字符串的坑及解決,具有很好的參考價(jià)值,希望對大家有所幫助,如有錯(cuò)誤或未考慮完全的地方,望不吝賜教2024-08-08
在Oracle數(shù)據(jù)庫表中加載多個(gè)數(shù)據(jù)文件的方法詳解
這篇文章主要給大家介紹了在Oracle數(shù)據(jù)庫表中加載多個(gè)數(shù)據(jù)文件的方法,本文中,我將展示 SQL 加載器 + Unix 腳本實(shí)用程序的強(qiáng)大功能,其中 SQL 加載器可以使用自動 shell 腳本加載多個(gè)數(shù)據(jù)文件,需要的朋友可以參考下2024-01-01
在Navicat12中連接Oracle 11g的SYS用戶時(shí),如果設(shè)置了新密碼但連接失敗,可能是因?yàn)樾枰許YSDBA或SYSOPER角色連接,解決方法是確保在連接時(shí)選擇了正確的角色,SYS用戶是具有最高權(quán)限的超級用戶,擁有創(chuàng)建數(shù)據(jù)庫的權(quán)限2025-02-02
這篇文章主要介紹了linux自動運(yùn)行rman增量備份腳本，實(shí)現(xiàn)周日和周三凌晨1:00執(zhí)行0級全庫備份，周一、二、四、五、六凌晨1:30執(zhí)行增量備份,需要的朋友可以參考下2014-03-03
連接Oracle數(shù)據(jù)庫失敗(ORA-12514)故障排除全過程
Oracle連接失敗是指在使用Oracle數(shù)據(jù)庫進(jìn)行開發(fā)的過程中,服務(wù)器端無法與客戶端連接,從而導(dǎo)致Oracle連接無法成功,影響開發(fā)的效率,下面這篇文章主要給大家介紹了關(guān)于連接Oracle數(shù)據(jù)庫失敗(ORA-12514)故障排除的相關(guān)資料,需要的朋友可以參考下2023-05-05
這篇文章主要介紹了Oracle 11.2.0.4打補(bǔ)丁的方法，本文給大家介紹的非常詳細(xì)，具有一定的參考借鑒價(jià)值,需要的朋友可以參考下2019-08-08
Oracle和MySQL的數(shù)據(jù)導(dǎo)入為何差別這么大
這篇文章主要介紹了Oracle和MySQL的數(shù)據(jù)導(dǎo)入有哪些區(qū)別，幫助大家更好的理解和學(xué)習(xí)，感興趣的朋友可以了解下2020-08-08
這篇文章主要介紹了Oracle 批處理自動備份bat腳本語句的步驟詳解,本文給大家介紹的非常詳細(xì)，對大家的學(xué)習(xí)或工作具有一定的參考借鑒價(jià)值，需要的朋友可以參考下2021-01-01
這篇文章主要介紹了oracle中decode函數(shù)的使用方法,需要的朋友可以參考下2014-03-03
JDBC Oracle執(zhí)行executeUpdate卡死問題的解決方案
今天小編就為大家分享一篇關(guān)于JDBC Oracle執(zhí)行executeUpdate卡死問題的解決方案，小編覺得內(nèi)容挺不錯(cuò)的，現(xiàn)在分享給大家，具有很好的參考價(jià)值，需要的朋友一起跟隨小編來看看吧2018-12-12