2011-11-24 RESULT_CACHE函數

http://www.itpub.net/thread-1499223-19-1.html

185樓

我創建了如下的表并填入數據：

CREATE TABLE plch_tab
(
   this_is_it       VARCHAR2 (20)
, another_column   NUMBER (2)
)
/
BEGIN
   INSERT INTO plch_tab (this_is_it)
        VALUES ('PL/SQL Challenge');
   COMMIT;
END;
/

然后我編譯了如下的函數：

CREATE OR REPLACE FUNCTION plch_foo (p_in_value IN NUMBER)
   RETURN VARCHAR2
   RESULT_CACHE
IS
   lvretval   plch_tab.this_is_it%TYPE;
BEGIN
   SELECT this_is_it INTO lvretval FROM plch_tab;
   DBMS_OUTPUT.put_line (lvretval);
   RETURN lvretval;
END;
/

哪些代碼塊執行之后會顯示 "PL/SQL Challenge" 僅僅一次?

(A)

DECLARE
   lvdummy   plch_tab.this_is_it%TYPE;
BEGIN
   lvdummy := plch_foo (NULL);
   lvdummy := plch_foo (NULL);
END;
/

SQL> DECLARE
  2     lvdummy   plch_tab.this_is_it%TYPE;
  3  BEGIN
  4     lvdummy := plch_foo (NULL);
  5     lvdummy := plch_foo (NULL);
  6  END;
  7  /
PL/SQL Challenge
PL/SQL procedure successfully completed
SQL>

(B)

DECLARE
  lvDummy  plch_tab.this_is_it%TYPE;
BEGIN
  lvDummy := plch_foo(1);
  lvDummy := plch_foo(1);
END;
/

SQL> DECLARE
  2    lvDummy  plch_tab.this_is_it%TYPE;
  3  BEGIN
  4    lvDummy := plch_foo(1);
  5    lvDummy := plch_foo(1);
  6  END;
  7  /
PL/SQL Challenge
PL/SQL procedure successfully completed
SQL>

(C)

DECLARE
   lvdummy   plch_tab.this_is_it%TYPE;
BEGIN
   lvdummy := plch_foo (2);
   UPDATE plch_tab SET another_column = 1;
   COMMIT;
   lvdummy := plch_foo (2);
END;
/

SQL> DECLARE
  2     lvdummy   plch_tab.this_is_it%TYPE;
  3  BEGIN
  4     lvdummy := plch_foo (2);
  5  
  6     UPDATE plch_tab SET another_column = 1;
  7     COMMIT;
  8  
  9     lvdummy := plch_foo (2);
 10  END;
 11  /
PL/SQL Challenge
PL/SQL Challenge
PL/SQL procedure successfully completed
SQL>

(D)

DECLARE
   lvdummy   plch_tab.this_is_it%TYPE;
BEGIN
   lvdummy := plch_foo (3);
   UPDATE plch_tab SET another_column = 1;
   lvdummy := plch_foo (3);
END;
/

SQL> DECLARE
  2     lvdummy   plch_tab.this_is_it%TYPE;
  3  BEGIN
  4     lvdummy := plch_foo (3);
  5  
  6     UPDATE plch_tab SET another_column = 1;
  7  
  8     lvdummy := plch_foo (3);
  9  END;
 10  /
PL/SQL Challenge
PL/SQL Challenge
PL/SQL procedure successfully completed
SQL>

(E)

DECLARE
   lvdummy   plch_tab.this_is_it%TYPE;
BEGIN
   lvdummy := plch_foo (4);
   UPDATE plch_tab
      SET another_column = 1
    WHERE another_column = 2;
   lvdummy := plch_foo (4);
END;
/

SQL> DECLARE
  2     lvdummy   plch_tab.this_is_it%TYPE;
  3  BEGIN
  4     lvdummy := plch_foo (4);
  5  
  6     UPDATE plch_tab
  7        SET another_column = 1
  8      WHERE another_column = 2;
  9  
 10     lvdummy := plch_foo (4);
 11  END;
 12  /
PL/SQL Challenge
PL/SQL procedure successfully completed
SQL>

答案說明在191樓

2011-11-24 答案ABE.
PL/SQL 函數的結果緩存機制提供了一種把PL/SQL 函數的結果緩存在SGA里面的辦法，這個結果對運行應用程序的所有會話都是可用的。緩存機制簡單而且有效，你不必再費心設計開發自己的緩存及其管理機制。
為了打開函數的結果緩存，你只需在函數頭加上RESULT_CACHE子句（如果在PACKAGE里面使用，則包頭和包體中的函數聲明都必須有這個子句）
當一個結果緩存函數被調用時，系統檢查緩存。如果以前調用的結果存在于緩存中，參數也相同，系統直接返回結果二不是再次執行函數體。如果緩存中找不到該結果，系統執行函數體，并在把控制權交還給調用者之前，把結果加入到緩存（針對于這組參數）。
函數結果緩存通常保存在函數體中查詢到的一個或多個表的數據。ORACLE需要確保這個數據的拷貝是干凈的（即未被修改過）。
ORACLE通過兩個機制來確保數據干凈：
1. 每當表發生了修改并被提交，所有依賴于這個表的結果緩存將被清空。隨后對函數的調用會導致數據再次進入緩存。
2. 一旦你在會話中修改了表，那么所有依賴于這個表的緩存將被忽略。即：緩存結果依賴于被改過的表的函數體將會被執行，一直到你提交或回滾了這些修改。
A: 兩次調用參數都為NULL, ORACLE認為沒有變化，直接取緩存結果而不是重新調用。
B: 同上，參數沒有發生變化。
C: 參數沒有發生變化，但是代碼修改了 plch_foo 表，函數的緩存對當前會話不再有效；COMMIT發生后，CACHE對所有會話都不再有效。因此再次調用函數時，函數被再次執行了
D: 同上，雖然沒有COMMIT, 但是該緩存對當前會話已經不可用了，所以函數會被再次執行。
   假如這個選項后面還有更多的調用：lvdummy := plch_foo (3);
   則函數體每次都會被重新執行。
E: UPDATE沒有修改任何數據，因此緩存仍然有效。