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没有修改任何数据，因此缓存仍然有效。