【Oracle】TCL语言

个人主页：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;
/

结语
感谢您的阅读！期待您的一键三连！欢迎指正！