個人主頁：Guiat
歸屬專欄：Oracle

正文
TCL（Transaction Control Language）是Oracle數據庫的"交通指揮官"，專門負責管理數據庫事務的流程控制。如果說數據庫是一個繁忙的城市，那TCL就是那個指揮交通、確保秩序的紅綠燈系統。它決定了什么時候讓數據變更"通行"（提交），什么時候要"剎車"（回滾），是保證數據一致性和完整性的關鍵！

1. TCL概述

1.1 什么是TCL？

TCL就像是數據庫的"時間管理大師"，它控制著數據變更的節奏和時機。在Oracle這個數據王國里，TCL確保每個數據變更都有始有終，要么完美收官，要么干凈利落地撤銷，絕不留下半吊子的狀態。

1.2 TCL的核心功能

Oracle TCL的功能體系就像一個完整的時間管理系統：

2. 事務基礎概念

2.1 事務的ACID特性

事務就像是一份"保險合同"，必須滿足四個核心特性：

2.2 事務的生命周期

-- 事務的典型生命周期演示
BEGIN-- 事務自動開始（第一個DML語句）DBMS_OUTPUT.PUT_LINE('事務開始時間: ' || TO_CHAR(SYSTIMESTAMP, 'YYYY-MM-DD HH24:MI:SS.FF3'));-- 第一個DML操作，事務正式開始INSERT INTO transaction_log (log_id, operation_type, start_time, status)VALUES (txn_log_seq.NEXTVAL, 'DEMO_TRANSACTION', SYSTIMESTAMP, 'STARTED');-- 設置保存點SAVEPOINT operation_1_complete;DBMS_OUTPUT.PUT_LINE('保存點設置: operation_1_complete');-- 執行多個操作UPDATE account_balance SET balance = balance - 1000, last_updated = SYSTIMESTAMPWHERE account_id = 12345;UPDATE account_balance SET balance = balance + 1000, last_updated = SYSTIMESTAMPWHERE account_id = 67890;SAVEPOINT transfer_complete;DBMS_OUTPUT.PUT_LINE('保存點設置: transfer_complete');-- 記錄操作日志INSERT INTO audit_trail (audit_id, transaction_type, amount, from_account, to_account, timestamp)VALUES (audit_seq.NEXTVAL, 'TRANSFER', 1000, 12345, 67890, SYSTIMESTAMP);-- 模擬業務驗證DECLAREv_from_balance NUMBER;v_to_balance NUMBER;BEGINSELECT balance INTO v_from_balance FROM account_balance WHERE account_id = 12345;SELECT balance INTO v_to_balance FROM account_balance WHERE account_id = 67890;IF v_from_balance < 0 THENDBMS_OUTPUT.PUT_LINE('驗證失敗: 賬戶余額不足');ROLLBACK TO transfer_complete;RAISE_APPLICATION_ERROR(-20001, '賬戶余額不足');ELSEDBMS_OUTPUT.PUT_LINE('驗證通過: 轉賬成功');END IF;END;-- 提交事務COMMIT;DBMS_OUTPUT.PUT_LINE('事務提交時間: ' || TO_CHAR(SYSTIMESTAMP, 'YYYY-MM-DD HH24:MI:SS.FF3'));EXCEPTIONWHEN OTHERS THENDBMS_OUTPUT.PUT_LINE('事務異常: ' || SQLERRM);ROLLBACK;DBMS_OUTPUT.PUT_LINE('事務回滾時間: ' || TO_CHAR(SYSTIMESTAMP, 'YYYY-MM-DD HH24:MI:SS.FF3'));RAISE;
END;
/

3. COMMIT語句詳解

3.1 COMMIT基礎語法

COMMIT就像是給所有變更蓋上"官方印章"，讓它們永久生效：

-- 基本COMMIT語法
BEGININSERT INTO employees (employee_id, first_name, last_name, hire_date)VALUES (1001, '張', '三', SYSDATE);UPDATE employees SET salary = salary * 1.1 WHERE department_id = 10;DELETE FROM employees WHERE status = 'TERMINATED' AND termination_date < ADD_MONTHS(SYSDATE, -24);-- 提交所有變更COMMIT;DBMS_OUTPUT.PUT_LINE('所有變更已永久保存');
END;
/-- 帶注釋的COMMIT
BEGINUPDATE product_prices SET price = price * 1.05 WHERE category = 'ELECTRONICS';INSERT INTO price_change_log (change_id, change_date, category, change_type, change_percent)VALUES (price_log_seq.NEXTVAL, SYSDATE, 'ELECTRONICS', 'INCREASE', 5);-- 提交并添加注釋（在Oracle中，注釋主要用于日志記錄）COMMIT /* 電子產品價格調整 - 漲價5% */;DBMS_OUTPUT.PUT_LINE('價格調整已生效');
END;
/-- 條件COMMIT
DECLAREv_processed_count NUMBER := 0;v_error_count NUMBER := 0;v_batch_size NUMBER := 1000;
BEGINFOR rec IN (SELECT employee_id FROM temp_salary_adjustments) LOOPBEGINUPDATE employees SET salary = salary * 1.08, last_updated = SYSDATEWHERE employee_id = rec.employee_id;v_processed_count := v_processed_count + 1;-- 每處理1000條記錄提交一次IF MOD(v_processed_count, v_batch_size) = 0 THENCOMMIT;DBMS_OUTPUT.PUT_LINE('已處理并提交 ' || v_processed_count || ' 條記錄');END IF;EXCEPTIONWHEN OTHERS THENv_error_count := v_error_count + 1;DBMS_OUTPUT.PUT_LINE('處理員工 ' || rec.employee_id || ' 時出錯: ' || SQLERRM);END;END LOOP;-- 提交剩余的記錄IF MOD(v_processed_count, v_batch_size) != 0 THENCOMMIT;END IF;DBMS_OUTPUT.PUT_LINE('處理完成: ' || v_processed_count || ' 成功, ' || v_error_count || ' 失敗');
END;
/

3.2 自動提交與手動提交

-- 查看當前自動提交設置
SELECT value FROM v$parameter WHERE name = 'autocommit';-- 在SQL*Plus中設置自動提交
-- SET AUTOCOMMIT ON;  -- 每個DML語句后自動提交
-- SET AUTOCOMMIT OFF; -- 手動控制提交（推薦）-- 演示自動提交與手動提交的區別
CREATE OR REPLACE PROCEDURE demo_commit_modes
IS
BEGINDBMS_OUTPUT.PUT_LINE('=== 手動提交模式演示 ===');-- 開始事務INSERT INTO demo_table (id, name, created_date)VALUES (1, '測試數據1', SYSDATE);DBMS_OUTPUT.PUT_LINE('插入數據，但未提交');-- 在另一個會話中，這條數據是不可見的-- 直到執行COMMIT-- 繼續插入更多數據INSERT INTO demo_table (id, name, created_date)VALUES (2, '測試數據2', SYSDATE);INSERT INTO demo_table (id, name, created_date)VALUES (3, '測試數據3', SYSDATE);DBMS_OUTPUT.PUT_LINE('插入了3條數據，仍未提交');-- 統一提交COMMIT;DBMS_OUTPUT.PUT_LINE('所有數據已提交，現在對其他會話可見');
END;
/

3.3 提交性能優化

-- 批量提交優化
CREATE OR REPLACE PROCEDURE optimized_batch_commit(p_batch_size IN NUMBER DEFAULT 10000
)
ISCURSOR data_cursor ISSELECT * FROM large_source_table WHERE processed_flag = 'N';TYPE data_array IS TABLE OF large_source_table%ROWTYPE;l_data_batch data_array;v_total_processed NUMBER := 0;v_start_time TIMESTAMP := SYSTIMESTAMP;v_batch_start_time TIMESTAMP;BEGINDBMS_OUTPUT.PUT_LINE('開始批量處理，批次大小: ' || p_batch_size);OPEN data_cursor;LOOPv_batch_start_time := SYSTIMESTAMP;-- 批量獲取數據FETCH data_cursor BULK COLLECT INTO l_data_batch LIMIT p_batch_size;EXIT WHEN l_data_batch.COUNT = 0;-- 批量處理FORALL i IN 1..l_data_batch.COUNTINSERT INTO target_table (id, data_field1, data_field2, processed_date) VALUES (l_data_batch(i).id,l_data_batch(i).data_field1,l_data_batch(i).data_field2,SYSDATE);-- 更新源表狀態FORALL i IN 1..l_data_batch.COUNTUPDATE large_source_table SET processed_flag = 'Y', processed_date = SYSDATEWHERE id = l_data_batch(i).id;-- 批量提交COMMIT;v_total_processed := v_total_processed + l_data_batch.COUNT;DBMS_OUTPUT.PUT_LINE('批次完成: ' || l_data_batch.COUNT || ' 條記錄, ' ||'累計: ' || v_total_processed || ' 條, ' ||'批次耗時: ' || EXTRACT(SECOND FROM (SYSTIMESTAMP - v_batch_start_time)) || ' 秒');END LOOP;CLOSE data_cursor;DBMS_OUTPUT.PUT_LINE('總計處理: ' || v_total_processed || ' 條記錄, ' ||'總耗時: ' || EXTRACT(SECOND FROM (SYSTIMESTAMP - v_start_time)) || ' 秒');
END;
/-- 異步提交（Oracle 11g+）
BEGININSERT INTO audit_log (log_id, operation, timestamp)VALUES (audit_seq.NEXTVAL, 'SYSTEM_MAINTENANCE', SYSTIMESTAMP);-- 異步提交，不等待日志寫入完成COMMIT WRITE IMMEDIATE NOWAIT;DBMS_OUTPUT.PUT_LINE('異步提交完成，繼續后續操作');
END;
/-- 同步提交（確保日志寫入）
BEGININSERT INTO critical_audit_log (log_id, operation, timestamp)VALUES (critical_audit_seq.NEXTVAL, 'CRITICAL_OPERATION', SYSTIMESTAMP);-- 同步提交，等待日志寫入完成COMMIT WRITE IMMEDIATE WAIT;DBMS_OUTPUT.PUT_LINE('同步提交完成，數據已安全寫入');
END;
/

4. ROLLBACK語句詳解

4.1 ROLLBACK基礎語法

ROLLBACK就像是"時光倒流"，讓所有變更回到事務開始前的狀態：

-- 基本ROLLBACK語法
BEGININSERT INTO test_table (id, name) VALUES (1, '測試數據');UPDATE test_table SET name = '修改后的數據' WHERE id = 1;DELETE FROM test_table WHERE id = 999;DBMS_OUTPUT.PUT_LINE('執行了多個DML操作');-- 模擬檢測到錯誤IF 1 = 1 THEN  -- 某種錯誤條件ROLLBACK;DBMS_OUTPUT.PUT_LINE('檢測到錯誤，所有操作已回滾');ELSECOMMIT;DBMS_OUTPUT.PUT_LINE('操作成功，已提交');END IF;
END;
/-- 條件ROLLBACK
DECLAREv_account_balance NUMBER;v_transfer_amount NUMBER := 5000;insufficient_funds EXCEPTION;
BEGIN-- 檢查賬戶余額SELECT balance INTO v_account_balance FROM accounts WHERE account_id = 12345;IF v_account_balance < v_transfer_amount THENRAISE insufficient_funds;END IF;-- 執行轉賬操作UPDATE accounts SET balance = balance - v_transfer_amount WHERE account_id = 12345;UPDATE accounts SET balance = balance + v_transfer_amount WHERE account_id = 67890;INSERT INTO transaction_history (trans_id, from_account, to_account, amount, trans_date) VALUES (trans_seq.NEXTVAL, 12345, 67890, v_transfer_amount, SYSDATE);COMMIT;DBMS_OUTPUT.PUT_LINE('轉賬成功完成');EXCEPTIONWHEN insufficient_funds THENROLLBACK;DBMS_OUTPUT.PUT_LINE('余額不足，轉賬已取消');RAISE_APPLICATION_ERROR(-20001, '賬戶余額不足，無法完成轉賬');WHEN OTHERS THENROLLBACK;DBMS_OUTPUT.PUT_LINE('轉賬過程中發生錯誤: ' || SQLERRM);RAISE;
END;
/-- 在存儲過程中的ROLLBACK
CREATE OR REPLACE PROCEDURE process_order_with_rollback(p_customer_id IN NUMBER,p_product_id IN NUMBER,p_quantity IN NUMBER,p_order_id OUT NUMBER
)
ISv_available_qty NUMBER;v_unit_price NUMBER;v_total_amount NUMBER;inventory_insufficient EXCEPTION;invalid_customer EXCEPTION;
BEGIN-- 驗證客戶DECLAREv_customer_count NUMBER;BEGINSELECT COUNT(*) INTO v_customer_countFROM customersWHERE customer_id = p_customer_id AND status = 'ACTIVE';IF v_customer_count = 0 THENRAISE invalid_customer;END IF;END;-- 檢查庫存SELECT quantity_on_hand, unit_priceINTO v_available_qty, v_unit_priceFROM productsWHERE product_id = p_product_id;IF v_available_qty < p_quantity THENRAISE inventory_insufficient;END IF;-- 計算總金額v_total_amount := p_quantity * v_unit_price;-- 生成訂單SELECT order_seq.NEXTVAL INTO p_order_id FROM dual;INSERT INTO orders (order_id, customer_id, order_date, total_amount, status)VALUES (p_order_id, p_customer_id, SYSDATE, v_total_amount, 'CONFIRMED');INSERT INTO order_items (order_id, product_id, quantity, unit_price, line_total)VALUES (p_order_id, p_product_id, p_quantity, v_unit_price, v_total_amount);-- 更新庫存UPDATE products SET quantity_on_hand = quantity_on_hand - p_quantityWHERE product_id = p_product_id;COMMIT;DBMS_OUTPUT.PUT_LINE('訂單 ' || p_order_id || ' 創建成功');EXCEPTIONWHEN invalid_customer THENROLLBACK;RAISE_APPLICATION_ERROR(-20002, '無效的客戶ID或客戶狀態異常');WHEN inventory_insufficient THENROLLBACK;RAISE_APPLICATION_ERROR(-20003, '庫存不足，當前可用數量: ' || v_available_qty);WHEN OTHERS THENROLLBACK;RAISE_APPLICATION_ERROR(-20004, '訂單創建失敗: ' || SQLERRM);
END;
/

4.2 異常處理中的ROLLBACK

-- 復雜的異常處理和回滾機制
CREATE OR REPLACE PROCEDURE complex_business_transaction
ISv_step VARCHAR2(50);v_processed_count NUMBER := 0;-- 自定義異常step1_error EXCEPTION;step2_error EXCEPTION;step3_error EXCEPTION;BEGINDBMS_OUTPUT.PUT_LINE('開始復雜業務事務處理');-- 第一步：數據驗證和預處理BEGINv_step := 'STEP1_VALIDATION';DBMS_OUTPUT.PUT_LINE('執行步驟: ' || v_step);-- 模擬數據驗證FOR rec IN (SELECT * FROM pending_transactions WHERE status = 'PENDING') LOOPIF rec.amount <= 0 THENRAISE step1_error;END IF;v_processed_count := v_processed_count + 1;END LOOP;DBMS_OUTPUT.PUT_LINE('步驟1完成，驗證了 ' || v_processed_count || ' 條記錄');EXCEPTIONWHEN step1_error THENROLLBACK;RAISE_APPLICATION_ERROR(-20101, '步驟1失敗：數據驗證不通過');WHEN OTHERS THENROLLBACK;RAISE_APPLICATION_ERROR(-20102, '步驟1異常：' || SQLERRM);END;-- 第二步：主要業務邏輯處理BEGINv_step := 'STEP2_PROCESSING';DBMS_OUTPUT.PUT_LINE('執行步驟: ' || v_step);-- 批量處理業務邏輯UPDATE pending_transactions SET status = 'PROCESSING', processing_date = SYSDATEWHERE status = 'PENDING';-- 插入處理記錄INSERT INTO transaction_log (log_id, step_name, processed_count, log_date)VALUES (log_seq.NEXTVAL, v_step, v_processed_count, SYSDATE);DBMS_OUTPUT.PUT_LINE('步驟2完成，處理了 ' || SQL%ROWCOUNT || ' 條記錄');EXCEPTIONWHEN step2_error THENROLLBACK;RAISE_APPLICATION_ERROR(-20201, '步驟2失敗：業務處理錯誤');WHEN OTHERS THENROLLBACK;RAISE_APPLICATION_ERROR(-20202, '步驟2異常：' || SQLERRM);END;-- 第三步：結果確認和清理BEGINv_step := 'STEP3_FINALIZATION';DBMS_OUTPUT.PUT_LINE('執行步驟: ' || v_step);-- 最終確認UPDATE pending_transactions SET status = 'COMPLETED', completion_date = SYSDATEWHERE status = 'PROCESSING';-- 清理臨時數據DELETE FROM temp_processing_data WHERE created_date < SYSDATE - 1;DBMS_OUTPUT.PUT_LINE('步驟3完成，清理了 ' || SQL%ROWCOUNT || ' 條臨時數據');EXCEPTIONWHEN step3_error THENROLLBACK;RAISE_APPLICATION_ERROR(-20301, '步驟3失敗：最終確認錯誤');WHEN OTHERS THENROLLBACK;RAISE_APPLICATION_ERROR(-20302, '步驟3異常：' || SQLERRM);END;-- 所有步驟成功，提交事務COMMIT;DBMS_OUTPUT.PUT_LINE('復雜業務事務處理成功完成');EXCEPTIONWHEN OTHERS THENROLLBACK;DBMS_OUTPUT.PUT_LINE('事務在步驟 ' || v_step || ' 失敗，已全部回滾');-- 記錄錯誤日志INSERT INTO error_log (error_id, error_step, error_message, error_date)VALUES (error_seq.NEXTVAL, v_step, SQLERRM, SYSDATE);COMMIT;  -- 單獨提交錯誤日志RAISE;
END;
/

5. SAVEPOINT保存點詳解

5.1 SAVEPOINT基礎概念

SAVEPOINT就像是游戲中的"存檔點"，可以在事務中設置多個檢查點：

-- 基本SAVEPOINT使用
DECLAREv_operation_step NUMBER := 0;
BEGINDBMS_OUTPUT.PUT_LINE('=== SAVEPOINT演示開始 ===');-- 第一步操作v_operation_step := 1;INSERT INTO demo_table (id, step, description, created_date)VALUES (1, v_operation_step, '第一步操作', SYSDATE);SAVEPOINT step1_complete;DBMS_OUTPUT.PUT_LINE('步驟1完成，設置保存點: step1_complete');-- 第二步操作v_operation_step := 2;INSERT INTO demo_table (id, step, description, created_date)VALUES (2, v_operation_step, '第二步操作', SYSDATE);UPDATE demo_table SET description = description || ' - 已更新' WHERE step = 1;SAVEPOINT step2_complete;DBMS_OUTPUT.PUT_LINE('步驟2完成，設置保存點: step2_complete');-- 第三步操作（模擬出錯）v_operation_step := 3;BEGININSERT INTO demo_table (id, step, description, created_date)VALUES (3, v_operation_step, '第三步操作', SYSDATE);-- 模擬業務邏輯錯誤IF SYSDATE > DATE '1900-01-01' THENRAISE_APPLICATION_ERROR(-20001, '模擬的業務錯誤');END IF;SAVEPOINT step3_complete;DBMS_OUTPUT.PUT_LINE('步驟3完成，設置保存點: step3_complete');EXCEPTIONWHEN OTHERS THENDBMS_OUTPUT.PUT_LINE('步驟3發生錯誤: ' || SQLERRM);ROLLBACK TO step2_complete;DBMS_OUTPUT.PUT_LINE('已回滾到保存點: step2_complete');END;-- 第四步操作（替代第三步）v_operation_step := 4;INSERT INTO demo_table (id, step, description, created_date)VALUES (4, v_operation_step, '第四步操作（替代第三步）', SYSDATE);SAVEPOINT step4_complete;DBMS_OUTPUT.PUT_LINE('步驟4完成，設置保存點: step4_complete');-- 提交所有成功的操作COMMIT;DBMS_OUTPUT.PUT_LINE('=== 事務提交，所有保存點自動釋放 ===');END;
/-- 查看最終結果
SELECT id, step, description, created_date 
FROM demo_table 
ORDER BY id;

5.2 復雜的SAVEPOINT應用

-- 多層次保存點管理
CREATE OR REPLACE PROCEDURE multi_level_savepoint_demo
ISv_level1_success BOOLEAN := FALSE;v_level2_success BOOLEAN := FALSE;v_level3_success BOOLEAN := FALSE;
BEGINDBMS_OUTPUT.PUT_LINE('開始多層次保存點演示');-- 第一層操作BEGINDBMS_OUTPUT.PUT_LINE('執行第一層操作...');INSERT INTO operation_log (log_id, level_num, operation, status, timestamp)VALUES (log_seq.NEXTVAL, 1, 'LEVEL1_OPERATION', 'STARTED', SYSTIMESTAMP);-- 模擬一些復雜操作FOR i IN 1..5 LOOPINSERT INTO temp_data (id, data_value, level_num)VALUES (i, 'Level1_Data_' || i, 1);END LOOP;SAVEPOINT level1_complete;v_level1_success := TRUE;DBMS_OUTPUT.PUT_LINE('第一層操作完成，設置保存點: level1_complete');-- 第二層操作BEGINDBMS_OUTPUT.PUT_LINE('執行第二層操作...');INSERT INTO operation_log (log_id, level_num, operation, status, timestamp)VALUES (log_seq.NEXTVAL, 2, 'LEVEL2_OPERATION', 'STARTED', SYSTIMESTAMP);-- 更新第一層的數據UPDATE temp_data SET data_value = data_value || '_UPDATED_BY_LEVEL2'WHERE level_num = 1;-- 添加第二層數據FOR i IN 6..10 LOOPINSERT INTO temp_data (id, data_value, level_num)VALUES (i, 'Level2_Data_' || i, 2);END LOOP;SAVEPOINT level2_complete;v_level2_success := TRUE;DBMS_OUTPUT.PUT_LINE('第二層操作完成，設置保存點: level2_complete');-- 第三層操作BEGINDBMS_OUTPUT.PUT_LINE('執行第三層操作...');INSERT INTO operation_log (log_id, level_num, operation, status, timestamp)VALUES (log_seq.NEXTVAL, 3, 'LEVEL3_OPERATION', 'STARTED', SYSTIMESTAMP);-- 模擬第三層可能失敗的操作FOR i IN 11..15 LOOPINSERT INTO temp_data (id, data_value, level_num)VALUES (i, 'Level3_Data_' || i, 3);-- 模擬在第13個操作時失敗IF i = 13 THENRAISE_APPLICATION_ERROR(-20003, '第三層操作模擬失敗');END IF;END LOOP;SAVEPOINT level3_complete;v_level3_success := TRUE;DBMS_OUTPUT.PUT_LINE('第三層操作完成，設置保存點: level3_complete');EXCEPTIONWHEN OTHERS THENDBMS_OUTPUT.PUT_LINE('第三層操作失敗: ' || SQLERRM);ROLLBACK TO level2_complete;DBMS_OUTPUT.PUT_LINE('已回滾到保存點: level2_complete');-- 記錄第三層失敗INSERT INTO operation_log (log_id, level_num, operation, status, timestamp)VALUES (log_seq.NEXTVAL, 3, 'LEVEL3_OPERATION', 'FAILED', SYSTIMESTAMP);END;EXCEPTIONWHEN OTHERS THENDBMS_OUTPUT.PUT_LINE('第二層操作失敗: ' || SQLERRM);ROLLBACK TO level1_complete;DBMS_OUTPUT.PUT_LINE('已回滾到保存點: level1_complete');INSERT INTO operation_log (log_id, level_num, operation, status, timestamp)VALUES (log_seq.NEXTVAL, 2, 'LEVEL2_OPERATION', 'FAILED', SYSTIMESTAMP);END;EXCEPTIONWHEN OTHERS THENDBMS_OUTPUT.PUT_LINE('第一層操作失敗: ' || SQLERRM);ROLLBACK;DBMS_OUTPUT.PUT_LINE('已回滾整個事務');INSERT INTO operation_log (log_id, level_num, operation, status, timestamp)VALUES (log_seq.NEXTVAL, 1, 'LEVEL1_OPERATION', 'FAILED', SYSTIMESTAMP);COMMIT;  -- 只提交錯誤日志RETURN;END;-- 更新操作狀態IF v_level1_success THENUPDATE operation_log SET status = 'COMPLETED' WHERE level_num = 1 AND operation = 'LEVEL1_OPERATION' AND status = 'STARTED';END IF;IF v_level2_success THENUPDATE operation_log SET status = 'COMPLETED' WHERE level_num = 2 AND operation = 'LEVEL2_OPERATION' AND status = 'STARTED';END IF;IF v_level3_success THENUPDATE operation_log SET status = 'COMPLETED' WHERE level_num = 3 AND operation = 'LEVEL3_OPERATION' AND status = 'STARTED';END IF;COMMIT;DBMS_OUTPUT.PUT_LINE('多層次操作完成，最終狀態已提交');-- 輸出最終統計DBMS_OUTPUT.PUT_LINE('操作結果統計:');DBMS_OUTPUT.PUT_LINE('第一層: ' || CASE WHEN v_level1_success THEN '成功' ELSE '失敗' END);DBMS_OUTPUT.PUT_LINE('第二層: ' || CASE WHEN v_level2_success THEN '成功' ELSE '失敗' END);DBMS_OUTPUT.PUT_LINE('第三層: ' || CASE WHEN v_level3_success THEN '成功' ELSE '失敗' END);
END;
/

5.3 SAVEPOINT在批處理中的應用

-- 大批量數據處理中的保存點應用
CREATE OR REPLACE PROCEDURE batch_process_with_savepoints(p_batch_size IN NUMBER DEFAULT 1000,p_max_errors IN NUMBER DEFAULT 10
)
ISCURSOR data_cursor ISSELECT rowid as row_id, id, data_field1, data_field2FROM large_source_tableWHERE processed_flag = 'N'ORDER BY id;TYPE rowid_array IS TABLE OF ROWID;TYPE number_array IS TABLE OF NUMBER;TYPE varchar_array IS TABLE OF VARCHAR2(100);l_row_ids rowid_array;l_ids number_array;l_data1 varchar_array;l_data2 varchar_array;v_batch_count NUMBER := 0;v_total_processed NUMBER := 0;v_total_errors NUMBER := 0;v_current_batch_errors NUMBER := 0;BEGINDBMS_OUTPUT.PUT_LINE('開始批量處理，批次大小: ' || p_batch_size);DBMS_OUTPUT.PUT_LINE('最大錯誤容忍數: ' || p_max_errors);OPEN data_cursor;LOOP-- 批量獲取數據FETCH data_cursor BULK COLLECT INTO l_row_ids, l_ids, l_data1, l_data2LIMIT p_batch_size;EXIT WHEN l_row_ids.COUNT = 0;v_batch_count := v_batch_count + 1;v_current_batch_errors := 0;DBMS_OUTPUT.PUT_LINE('處理第 ' || v_batch_count || ' 批，記錄數: ' || l_row_ids.COUNT);-- 設置批次開始保存點SAVEPOINT batch_start;-- 逐條處理當前批次FOR i IN 1..l_row_ids.COUNT LOOPBEGIN-- 設置單條記錄保存點SAVEPOINT record_start;-- 復雜的業務處理邏輯INSERT INTO target_table1 (id, processed_data, created_date)VALUES (l_ids(i), UPPER(l_data1(i)), SYSDATE);INSERT INTO target_table2 (source_id, calculated_value, created_date)VALUES (l_ids(i), LENGTH(l_data2(i)) * 10, SYSDATE);-- 更新源表狀態UPDATE large_source_tableSET processed_flag = 'Y', processed_date = SYSDATEWHERE rowid = l_row_ids(i);v_total_processed := v_total_processed + 1;EXCEPTIONWHEN OTHERS THEN-- 單條記錄處理失敗，回滾到記錄開始ROLLBACK TO record_start;v_current_batch_errors := v_current_batch_errors + 1;v_total_errors := v_total_errors + 1;DBMS_OUTPUT.PUT_LINE('記錄 ' || l_ids(i) || ' 處理失敗: ' || SQLERRM);-- 記錄錯誤信息INSERT INTO error_log (error_id, source_id, error_message, error_date, batch_number) VALUES (error_seq.NEXTVAL, l_ids(i), SQLERRM, SYSDATE, v_batch_count);-- 檢查是否超過錯誤容忍限制IF v_total_errors > p_max_errors THENDBMS_OUTPUT.PUT_LINE('錯誤數超過限制，終止處理');ROLLBACK TO batch_start;CLOSE data_cursor;RAISE_APPLICATION_ERROR(-20100, '錯誤數超過限制: ' || p_max_errors);END IF;END;END LOOP;-- 檢查當前批次錯誤率IF v_current_batch_errors > l_row_ids.COUNT * 0.1 THENDBMS_OUTPUT.PUT_LINE('當前批次錯誤率過高，回滾整個批次');ROLLBACK TO batch_start;ELSE-- 提交當前批次COMMIT;DBMS_OUTPUT.PUT_LINE('第 ' || v_batch_count || ' 批處理完成，' ||'成功: ' || (l_row_ids.COUNT - v_current_batch_errors) || '，' ||'失敗: ' || v_current_batch_errors);END IF;END LOOP;CLOSE data_cursor;DBMS_OUTPUT.PUT_LINE('批量處理完成');DBMS_OUTPUT.PUT_LINE('總計處理: ' || v_total_processed || ' 條記錄');DBMS_OUTPUT.PUT_LINE('總計錯誤: ' || v_total_errors || ' 條記錄');DBMS_OUTPUT.PUT_LINE('處理批次: ' || v_batch_count || ' 個批次');END;
/

6. SET TRANSACTION語句

6.1 事務隔離級別

SET TRANSACTION就像是給事務設定"行為準則"：

-- READ COMMITTED隔離級別（默認）
BEGIN-- 顯式設置READ COMMITTED（通常不需要，因為是默認值）SET TRANSACTION ISOLATION LEVEL READ COMMITTED;DBMS_OUTPUT.PUT_LINE('使用READ COMMITTED隔離級別');-- 在這個級別下，每個查詢都能看到查詢開始時已提交的數據SELECT COUNT(*) as current_count FROM employees;-- 如果其他會話在此時插入并提交了新記錄，下一個查詢會看到它們DBMS_LOCK.SLEEP(5);  -- 等待5秒，模擬其他會話操作SELECT COUNT(*) as updated_count FROM employees;COMMIT;
END;
/-- SERIALIZABLE隔離級別
BEGINSET TRANSACTION ISOLATION LEVEL SERIALIZABLE;DBMS_OUTPUT.PUT_LINE('使用SERIALIZABLE隔離級別');-- 記錄事務開始時的SCNDECLAREv_start_scn NUMBER;v_emp_count1 NUMBER;v_emp_count2 NUMBER;BEGINSELECT CURRENT_SCN INTO v_start_scn FROM v$database;DBMS_OUTPUT.PUT_LINE('事務開始SCN: ' || v_start_scn);SELECT COUNT(*) INTO v_emp_count1 FROM employees;DBMS_OUTPUT.PUT_LINE('第一次查詢員工數: ' || v_emp_count1);-- 即使其他會話提交了新數據，在SERIALIZABLE模式下-- 本事務仍然只能看到事務開始時的數據快照DBMS_LOCK.SLEEP(10);SELECT COUNT(*) INTO v_emp_count2 FROM employees;DBMS_OUTPUT.PUT_LINE('第二次查詢員工數: ' || v_emp_count2);IF v_emp_count1 = v_emp_count2 THENDBMS_OUTPUT.PUT_LINE('SERIALIZABLE確保了讀一致性');END IF;END;COMMIT;
END;
/-- READ ONLY事務
BEGINSET TRANSACTION READ ONLY;DBMS_OUTPUT.PUT_LINE('只讀事務開始');-- 生成一致性報表DECLAREv_report_time TIMESTAMP := SYSTIMESTAMP;BEGINDBMS_OUTPUT.PUT_LINE('報表生成時間: ' || TO_CHAR(v_report_time, 'YYYY-MM-DD HH24:MI:SS'));-- 部門統計FOR dept_rec IN (SELECT d.department_name, COUNT(e.employee_id) as emp_count, AVG(e.salary) as avg_salaryFROM departments dLEFT JOIN employees e ON d.department_id = e.department_idGROUP BY d.department_nameORDER BY d.department_name) LOOPDBMS_OUTPUT.PUT_LINE('部門: ' || dept_rec.department_name || ', 員工數: ' || dept_rec.emp_count || ', 平均薪資: ' || ROUND(dept_rec.avg_salary, 2));END LOOP;-- 在只讀事務中，所有查詢都看到同一個時間點的數據快照-- 確保報表數據的一致性-- 嘗試執行DML會失敗BEGININSERT INTO test_table VALUES (1, '測試');EXCEPTIONWHEN OTHERS THENDBMS_OUTPUT.PUT_LINE('只讀事務中不能執行DML: ' || SQLERRM);END;END;COMMIT;  -- 或者 ROLLBACK，對只讀事務效果相同
END;
/

6.2 事務名稱和屬性設置

-- 設置事務名稱（便于監控和調試）
BEGINSET TRANSACTION NAME '月度財務結算';DBMS_OUTPUT.PUT_LINE('開始執行月度財務結算事務');-- 復雜的財務處理邏輯INSERT INTO monthly_summary (month_year, total_revenue, total_expenses, net_profit)SELECT TO_CHAR(SYSDATE, 'YYYY-MM'),SUM(CASE WHEN transaction_type = 'REVENUE' THEN amount ELSE 0 END),SUM(CASE WHEN transaction_type = 'EXPENSE' THEN amount ELSE 0 END),SUM(CASE WHEN transaction_type = 'REVENUE' THEN amount ELSE -amount END)FROM financial_transactionsWHERE transaction_date >= TRUNC(SYSDATE, 'MM')AND transaction_date < ADD_MONTHS(TRUNC(SYSDATE, 'MM'), 1);-- 更新賬戶余額UPDATE account_balances abSET current_balance = (SELECT ab.current_balance + COALESCE(SUM(CASE WHEN ft.transaction_type = 'CREDIT' THEN ft.amount ELSE -ft.amount END), 0)FROM financial_transactions ftWHERE ft.account_id = ab.account_idAND ft.transaction_date >= TRUNC(SYSDATE, 'MM')AND ft.transaction_date < ADD_MONTHS(TRUNC(SYSDATE, 'MM'), 1)AND ft.processed_flag = 'N');-- 標記已處理的交易UPDATE financial_transactions SET processed_flag = 'Y', processed_date = SYSDATEWHERE transaction_date >= TRUNC(SYSDATE, 'MM')AND transaction_date < ADD_MONTHS(TRUNC(SYSDATE, 'MM'), 1)AND processed_flag = 'N';COMMIT;DBMS_OUTPUT.PUT_LINE('月度財務結算完成');
END;
/-- 設置事務使用特定回滾段
BEGIN-- 在舊版本Oracle中可以指定回滾段（現在通常由Oracle自動管理）-- SET TRANSACTION USE ROLLBACK SEGMENT rbs_large;DBMS_OUTPUT.PUT_LINE('開始大型數據操作事務');-- 大批量數據操作INSERT INTO archive_tableSELECT * FROM active_tableWHERE created_date < ADD_MONTHS(SYSDATE, -24);DELETE FROM active_tableWHERE created_date < ADD_MONTHS(SYSDATE, -24);COMMIT;DBMS_OUTPUT.PUT_LINE('數據歸檔操作完成');
END;
/-- 組合使用多個事務屬性
BEGINSET TRANSACTION ISOLATION LEVEL SERIALIZABLENAME '關鍵業務一致性檢查';DBMS_OUTPUT.PUT_LINE('開始關鍵業務一致性檢查');-- 在SERIALIZABLE級別下執行一致性檢查DECLAREv_accounts_total NUMBER;v_transactions_total NUMBER;v_difference NUMBER;BEGIN-- 計算所有賬戶余額總和SELECT SUM(balance) INTO v_accounts_total FROM account_balances;-- 計算所有交易凈額總和SELECT SUM(CASE WHEN transaction_type = 'CREDIT' THEN amount ELSE -amount END)INTO v_transactions_total FROM all_transactions;v_difference := ABS(v_accounts_total - v_transactions_total);DBMS_OUTPUT.PUT_LINE('賬戶余額總和: ' || v_accounts_total);DBMS_OUTPUT.PUT_LINE('交易凈額總和: ' || v_transactions_total);DBMS_OUTPUT.PUT_LINE('差額: ' || v_difference);IF v_difference > 0.01 THEN  -- 允許1分錢的舍入誤差RAISE_APPLICATION_ERROR(-20200, '數據不一致，差額: ' || v_difference);ELSEDBMS_OUTPUT.PUT_LINE('一致性檢查通過');END IF;END;COMMIT;
END;
/

結語
感謝您的閱讀！期待您的一鍵三連！歡迎指正！