[20221227]a mutating table error without a trigger!.txt

[20221227]a mutating table error without a trigger!.txt --//快放假,没什么事情,花一点点时间看了harmfultriggers.blogspot.com,关于触发器的相关危害. --//参考链接:harmfultriggers.blogspot.com/2011/12/look-mom-mutating-table-error-without.html --//实际上许多开发太不了解数据库,触发器对于数据库管理就是一种灾难,也许有一点点夸大,当然下面的例子 --//并没有使用触发器,但是出现ORA-04091: table XXXX is mutating, trigger/function may not see it. 1.环境: SCOTT@test01p> @ ver1 PORT_STRING                    VERSION        BANNER                                                                               CON_ID ------------------------------ -------------- -------------------------------------------------------------------------------- ---------- IBMPC/WIN_NT64-9.1.0           12.2.0.1.0     Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production              0 rename emp to empxx; --//drop table EMP; create table EMP (EMPNO    number(3,0)  not null primary key ,ENAME    varchar2(20) not null ,SAL      number(4,0)  not null) / insert into emp(empno,ename,sal) values(100,'Toon',4000); insert into emp(empno,ename,sal) values(101,'Izaak',5000); insert into emp(empno,ename,sal) values(102,'Marcel',7000); insert into emp(empno,ename,sal) values(103,'Rene',8000); commit; --//分析表 @ tpt/gts emp SCOTT@test01p> select * from emp;      EMPNO ENAME                       SAL ---------- -------------------- ----------        100 Toon                       4000        101 Izaak                      5000        102 Marcel                     7000        103 Rene                       8000 4 rows selected. 2.测试: --//测试1: SCOTT@test01p> update EMP e1 set e1.SAL =  e1.SAL + ((select avg(e2.SAL) from EMP e2) - e1.SAL)/2 ; 4 rows updated. --//执行OK. SCOTT@test01p> select * from emp;      EMPNO ENAME                       SAL ---------- -------------------- ----------        100 Toon                       5000        101 Izaak                      5500        102 Marcel                     6500        103 Rene                       7000 4 rows selected. SCOTT@test01p> rollback; Rollback complete. --//手工测试验证执行修改后结果正确. 4000+5000+7000+8000 = 24000 24000/4 = 6000 4000+(6000-4000)/2 = 5000 5000+(6000-5000)/2 = 5500 7000+(6000-7000)/2 = 6500 8000+(6000-8000)/2 = 7000 --//测试2: --//建立f_new_sal函数,换成函数执行看看.. create or replace function f_new_sal (p_current_sal in number) return number as -- pl_avg_sal number; -- begin   --   select avg(SAL) into pl_avg_sal     from EMP;   --   return p_current_sal + (pl_avg_sal - p_current_sal)/2;   -- end; / SCOTT@test01p> update EMP e set e.SAL = f_new_sal(e.SAL); update EMP e set e.SAL = f_new_sal(e.SAL)                          * ERROR at line 1: ORA-04091: table SCOTT.EMP is mutating, trigger/function may not see it ORA-06512: at "SCOTT.F_NEW_SAL", line 8 --//报错!!因为执行时要保持数据的一致性,而调用函数再次访问时结果已经发生变化,导致报错. --//测试3: --//建立loopback dblink. CREATE PUBLIC DATABASE LINK LOOPBACK  CONNECT TO SCOTT  IDENTIFIED BY <PWD>  USING 'localhost:1521/test01p:DEDICATED'; --//尝试建立的函数使用db_link. create or replace function f_new_sal (p_current_sal in number) return number as -- pl_avg_sal number; -- begin   --   select avg(SAL) into pl_avg_sal   from EMP@loopback;                -- Here: added db-link.   --   return p_current_sal + (pl_avg_sal - p_current_sal)/2;   -- end; / SCOTT@test01p> update EMP e set e.SAL = f_new_sal(e.SAL); 4 rows updated. SCOTT@test01p> select * from emp;      EMPNO ENAME                       SAL ---------- -------------------- ----------        100 Toon                       5000        101 Izaak                      5625        102 Marcel                     6703        103 Rene                       7166 4 rows selected. --//执行是成功了,但是注意对比上面直接修改的结果,完全不对,因为这样虽然规避了查询ORA-04091错误, --//但是执行时的一致性破坏了,等于每次函数调用后返回的结果都是不同,这样除了第一条修改正确外,其它3条修改都是错误的. SCOTT@test01p> rollback; Rollback complete. --//测试4: --//如果我修改的执行顺序呢. SCOTT@test01p> update (select * from EMP order by EMPNO desc) e   set e.SAL = f_new_sal(e.SAL); 4 rows updated. SCOTT@test01p> select * from emp order by empno desc;      EMPNO ENAME                       SAL ---------- -------------------- ----------        103 Rene                       7000        102 Marcel                     6375        101 Izaak                      5297        100 Toon                       4834 4 rows selected. --//结果类似,仅仅empno=103的修改正确. SCOTT@test01p> rollback; Rollback complete. --//测试5: --//建立函数采用自治事务呢? create or replace function f_new_sal (p_current_sal in number) return number as pragma autonomous_transaction; pl_avg_sal number; -- begin   --   select avg(SAL) into pl_avg_sal     from EMP;   --   return p_current_sal + (pl_avg_sal - p_current_sal)/2;   -- end; / --//pragma autonomous_transaction后面少写一个逗号.调式浪费许多时间. --//pragma 翻译 编译指示 SCOTT@test01p> update EMP e set e.SAL = f_new_sal(e.SAL); 4 rows updated. SCOTT@test01p> select * from emp ;      EMPNO ENAME                       SAL ---------- -------------------- ----------        100 Toon                       5000        101 Izaak                      5500        102 Marcel                     6500        103 Rene                       7000 4 rows selected. --//采用自治事务后修改正确. SCOTT@test01p> rollback; Rollback complete. --//测试6: --//函数采用DETERMINISTIC呢? create or replace function f_new_sal (p_current_sal in number) return number DETERMINISTIC as pl_avg_sal number; -- begin   --   select avg(SAL) into pl_avg_sal     from EMP;   --   return p_current_sal + (pl_avg_sal - p_current_sal)/2;   -- end; / SCOTT@test01p> update EMP e set e.SAL = f_new_sal(e.SAL); update EMP e set e.SAL = f_new_sal(e.SAL)                          * ERROR at line 1: ORA-04091: table SCOTT.EMP is mutating, trigger/function may not see it ORA-06512: at "SCOTT.F_NEW_SAL", line 9 --//报错!! SCOTT@test01p> rollback; Rollback complete.