ORACLE报错:ORA-04091 表发XXX生了变化,触发器/函数不能读它
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
BEGIN
FOR CAL IN (...) LOOP
V_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" IS
FUNCTION F_CHECK_VALUE(ARG1 VARCHAR2, ARG2 VARCHAR2) RETURN INTEGER IS
BEGIN
SELECT COUNT(*) > 0 THEN 1 ELSE 0 END
INTO 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" IS
FUNCTION F_CHECK_VALUE(ARG1 VARCHAR2, ARG2 VARCHAR2) RETURN INTEGER IS
pragma autonomous_transaction; --事务自治
BEGIN
SELECT COUNT(*) > 0 THEN 1 ELSE 0 END
INTO RESULT
FROM TABLE1
WHERE ...;
RETURN(RESULT);
END;
END;
END PKG_CHECK_VALUE;
拓展:自治事务的特点
-
第一,这段程序不依赖于原有Main程序,比如Main程序中有未提交的数据,那么在自治事务中是查找不到的。
-
第二,在自治事务中,commit或者rollback只会提交或回滚当前自治事务中的DML,不会影响到Main程序中的DML。
本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。 如若内容造成侵权/违法违规/事实不符,请联系我的编程经验分享网邮箱:veading@qq.com进行投诉反馈,一经查实,立即删除!