[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这样的操作。我个人不主张开发使用自定义的函数的。