[20201111]PL SQL function 和一致性.txt

[20201111]PL SQL function 和一致性.txt --//每当从SQL查询中调用PL/SQL函数时，函数中的每个查询都与其开始的SCN一致，而不是与父查询的SCN一致。 --//通过一个例子说明： 1.环境： SCOTT@book> @ ver1 PORT_STRING                    VERSION        BANNER ------------------------------ -------------- -------------------------------------------------------------------------------- x86_64/Linux 2.4.xx            11.2.0.4.0     Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production create table test as select level a, level b from dual connect by level<=10; create or replace function f1(a int) return int as   res int; begin   select b into res   from test t   where t.a=f1.a;   dbms_lock.sleep(1);   return res; end; / /* Formatted on 2020/11/11 9:21:28 (QP5 v5.269.14213.34769) */ CREATE OR REPLACE FUNCTION SCOTT.f1x (x INT)    RETURN INT    DETERMINISTIC AS    res   INT; BEGIN    SELECT b      INTO res      FROM test t     WHERE t.a = x;    DBMS_LOCK.sleep (1);    RETURN res; END; / SCOTT@book> set timing on SCOTT@book> select t.*, f1(a) func from test t;          A          B       FUNC ---------- ---------- ----------          1          1          1          2          2          2          3          3          3          4          4          4          5          5          5 Elapsed: 00:00:05.01 --//查询时函数f1的查询结果与B字段对应。 2.测试： $ cat abc.sql begin     for i in 1..10 loop       update test set b=b+1;       commit;       dbms_lock.sleep(1);     end loop; end; / --// session 2:加入在修改test的同时，执行上面的查询呢？ SCOTT@book> @ abc.sql PL/SQL procedure successfully completed. --//马上切换到session 1,执行如下： SCOTT@book> select t.*, f1(a) func from test t;          A          B       FUNC ---------- ---------- ----------          1          4          4          2          5          6          3          6          8          4          7         10          5          8         12 Elapsed: 00:00:05.00 --//你可以发现字段B与调用函数f1(a)的值不一致。越往后相差越大。 --//也就是当执行select t.*, f1(a) func from test t;时 oracle是访问那个时刻的SCN的信息，而函数调用时的scn信息不一致。 --//使用f1x函数也是一样。 --//这样就导致这样的情况出现，这种情况实际上也可以在正常的业务系统出现。 --//如何保证一致呢？执行如下： CREATE OPERATOR f1_op    BINDING (INT)    RETURN INT    USING F1; --//这样可以保证调用函数f1_op时与查询时scn一致。 SCOTT@book> update test set b=a; 5 rows updated. Elapsed: 00:00:00.01 SCOTT@book> commit ; Commit complete. Elapsed: 00:00:00.00 --//session 2: SCOTT@book> @ abc.sql PL/SQL procedure successfully completed. --//session 1: SCOTT@book> select t.*, f1(a) func,f1_op(a) op from test t;          A          B       FUNC         OP ---------- ---------- ---------- ----------          1          3          3          3          2          4          6          4          3          5          9          5          4          6         12          6          5          7         15          7 Elapsed: 00:00:10.02 --//问一下，有多少人知道CREATE OPERATOR这样的操作。我个人不主张开发使用自定义的函数的。