[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這樣的操作。我個人不主張開發使用自定義的函式的。