ORACLE报错:ORA-04091 表XXX发生了变化，触发器/函数不能读它

问题描述

在开发校验函数FUNCTION的时候，用数据跑批测试的时候报错。经排查这个校验函数FUNCTION的被一个存储过程中的update语句调用了。所以update更新数据的同时，又select了校验函数FUNCTION，猜测这两个事务冲突了导致了这个报错。

校验存储过程例如代码所示，校验存储过程P_CHECK_VALUE中的update调用了校验函数PKG_CHECK_VALUE.F_CHECK_VALUE(ARG1, ARG2)：

--调用校验的存储过程
CREATE OR REPLACE PROCEDURE P_CHECK_VALUE(V_TABLENAME VARCHAR2, V_DECLAREDATE VARCHAR,V_BATCHNO VARCHAR2 DEFAULE NULL,V_4 VARCHAR2 DEFAULT NULL) IS
BEGINFOR CAL IN (...) LOOPV_SQL = 'UPDATE TABLE1 SET TABLE1.A1 = TABLE1.A1 || (SELECT TABLE2.B2 FROM TABLE2 WHERE TABLE2.NUM1 = PKG_CHECK_VALUE.F_CHECK_VALUE(ARG1, ARG2))';EXECUTE IMMDEIATE V_SQL;COMMIT;END LOOP;
END;

校验函数是写在校验包里的，例如：

--校验包的函数
CREATE OR REPLACE PACKAGE BODY "PKG_CHECK_VALUE" ISFUNCTION F_CHECK_VALUE(ARG1 VARCHAR2, ARG2 VARCHAR2) RETURN INTEGER ISBEGINSELECT COUNT(*) > 0 THEN 1 ELSE 0 ENDINTO RESULT FROM TABLE1 WHERE ...;RETURN(RESULT);END;END;
END PKG_CHECK_VALUE;

问题分析

在函数中查询某张表前对表做了更新和插入操作,使表数据发生了变化,而函数返回的数据已经发生变化,导致出错.
如果既对同一个表进行update，又调用其他触发器/函数对同一个表进行查询select操作，猜测是这两个操作同时处理一张表，导致两个事务冲突了。
不清楚会先update再select，还是先select再update，总之这两个操作是对同一张表进行了增删改查处理了。

解决办法

在函数BEGIN之前加入一个自治事务pragma autonomous_transaction;**，其中PRAGMA关键字通知PL/SQL编译器,将声明它的这个PL/SQL代码块分割为一个自治的或独立的事务。这意味着在使用 PRAGMA AUTONOMOUS_TRANSACTION 的函数或过程中，可以执行 SQL 操作并提交或回滚这些操作，而不会影响主事务中的数据。（例子中的主事务是update操作，子事务是函数的select操作）总的来说，PRAGMA AUTONOMOUS_TRANSACTION 的作用是在 PL/SQL 中创建一个独立的事务，用于执行与主事务完全独立的操作，通常用于记录日志。

PRAGMA AUTONOMOUS_TRANSACTION中文翻译过来叫“自治事务”（翻译的还算好理解），对于定义成自治事务的Procedure，实际上相当于一段独立运行的程序段，这段程序不依赖于主程序，也不干涉主程序
pragma autonomous_transaction;

修改后如下所示，不会报错了：

--校验包的函数
CREATE OR REPLACE PACKAGE BODY "PKG_CHECK_VALUE" ISFUNCTION F_CHECK_VALUE(ARG1 VARCHAR2, ARG2 VARCHAR2) RETURN INTEGER ISpragma autonomous_transaction;	--事务自治BEGINSELECT COUNT(*) > 0 THEN 1 ELSE 0 ENDINTO RESULT FROM TABLE1 WHERE ...;RETURN(RESULT);END;END;
END PKG_CHECK_VALUE;

拓展：自治事务的特点

• 第一,这段程序不依赖于原有Main程序，比如Main程序中有未提交的数据，那么在自治事务中是查找不到的。

• 第二,在自治事务中，commit或者rollback只会提交或回滚当前自治事务中的DML，不会影响到Main程序中的DML。