如何在Oracle PL/SQL中禁止非法提交并保留有效事务控制？

方案1：用自定义事务控制包封装逻辑 #

自己写个PL/SQL包，把COMMIT、SAVEPOINT、局部回滚都包起来，测试环境下只拦截COMMIT，其他操作正常放行。举个例子：

CREATE OR REPLACE PACKAGE txn_control AS
  PROCEDURE safe_commit;
  PROCEDURE set_savepoint(p_sp_name VARCHAR2);
  PROCEDURE rollback_to_sp(p_sp_name VARCHAR2);
END txn_control;
/

CREATE OR REPLACE PACKAGE BODY txn_control AS
  -- 测试环境设为FALSE，正式环境改成TRUE
  g_allow_commit BOOLEAN := FALSE;

  PROCEDURE safe_commit IS
  BEGIN
    IF NOT g_allow_commit THEN
      RAISE_APPLICATION_ERROR(-20001, '非法COMMIT已被拦截');
    END IF;
    COMMIT;
  END safe_commit;

  PROCEDURE set_savepoint(p_sp_name VARCHAR2) IS
  BEGIN
    SAVEPOINT p_sp_name;
  END set_savepoint;

  PROCEDURE rollback_to_sp(p_sp_name VARCHAR2) IS
  BEGIN
    ROLLBACK TO SAVEPOINT p_sp_name;
  END rollback_to_sp;
END txn_control;
/

把代码里的COMMIT换成txn_control.safe_commit，SAVEPOINT和局部回滚换成包内的对应方法就行。测试时只要把g_allow_commit设为FALSE，非法COMMIT会直接抛错，而SAVEPOINT和局部回滚完全不受影响。

方案2：用数据库触发器拦截未授权的COMMIT #

Oracle没有直接的BEFORE COMMIT触发器，但可以通过AFTER COMMIT触发器结合标记来拦截非法提交。核心思路是：只有带有特定标记的COMMIT才被允许，其他的直接拦截并记录日志。

CREATE OR REPLACE TRIGGER block_unauthorized_commit
AFTER COMMIT ON DATABASE
DECLARE
  v_client_info VARCHAR2(64);
BEGIN
  DBMS_APPLICATION_INFO.read_client_info(v_client_info);
  -- 只有客户端信息标记为ALLOW_COMMIT的提交才合法
  IF v_client_info != 'ALLOW_COMMIT' THEN
    RAISE_APPLICATION_ERROR(-20002, '未授权的COMMIT已被拦截');
    -- 把拦截记录写到审计表，方便排查
    INSERT INTO commit_audit (txn_id, event_time, status)
    VALUES (DBMS_TRANSACTION.local_transaction_id, SYSTIMESTAMP, 'BLOCKED');
    -- 这里的COMMIT是专门写审计日志的，要单独处理
    COMMIT;
  END IF;
END;
/

合法需要提交的代码里，先调用DBMS_APPLICATION_INFO.set_client_info('ALLOW_COMMIT')，提交完再重置回去。测试环境开这个触发器，非法COMMIT会被拦截，而SAVEPOINT和局部回滚不会触发COMMIT事件，所以完全不受影响。

方案3：静态代码扫描提前排查 #

用Oracle自带的工具或者SQL Developer的代码检查功能，直接扫描所有PL/SQL代码，找出所有直接写COMMIT的语句。比如SQL Developer的Code Inspector可以加自定义规则，把直接使用COMMIT的代码标记出来，提前整改，不用等到运行时拦截。

要注意的点 #

• 测试环境和正式环境的配置要严格分开，别把测试用的拦截逻辑弄到生产环境。
• 要是碰到第三方库或者没法修改的代码，方案2的触发器更管用，但要注意审计日志的处理，别搞出循环提交的问题。
• SAVEPOINT和ROLLBACK TO SAVEPOINT属于局部事务操作，不会触发COMMIT事件，所以上面的方案都不会影响它们正常运行。

内容的提问来源于stack exchange，提问作者Wolfgang