PL/SQL Challenge 每日一题：2014-3-14 11gR2中带RELIES_ON子句的RESULT_CACHE函数

PL/SQL Challenge 每日一题：2014-3-14 11gR2中带RELIES_ON子句的RESULT_CACHE函数

最先答对且答案未经编辑的puber将获得纪念章一枚(答案不可编辑但可发新贴补充或纠正)，其他会员如果提供有价值的分析、讨论也可获得纪念章一枚。

以往旧题索引：
http://www.itpub.net/forum.php?mod=forumdisplay&fid=3&filter=typeid&typeid=1808

原始出处：
http://www.plsqlchallenge.com/

作者：        mentzel.iudith

运行环境：SQLPLUS, SERVEROUTPUT已打开
注：本题给出答案时候要求给予简要说明才能得到奖品
执行环境: 11gR2

我创建了如下的表和数据：

CREATE TABLE plch_employees
(
   employee_id  INTEGER
, last_name    VARCHAR2(100)
, salary       NUMBER
)
/

BEGIN
   INSERT INTO plch_employees
      VALUES (100, 'Steven', 10000);

   COMMIT;
END;
/

哪些选项实现了一个名为plch_salary的函数，使得下列代码块执行之后：

BEGIN
    DBMS_OUTPUT.put_line( plch_salary(100) );
    DBMS_OUTPUT.put_line( plch_salary(100) );

    UPDATE plch_employees
    SET   salary = salary * 2
    WHERE employee_id = 100 ;

    COMMIT;

    DBMS_OUTPUT.put_line( plch_salary(100) );
END;
/

我们可以看到结果中有三个完全一致的数值？


(A)
CREATE OR REPLACE FUNCTION plch_salary
           (p_employee_id_in IN plch_employees.employee_id%TYPE)
       RETURN plch_employees.salary%TYPE
       RESULT_CACHE
       RELIES_ON (plch_employees)
AS
    l_salary   plch_employees.salary%TYPE;
BEGIN
    SELECT salary
    INTO l_salary
    FROM plch_employees
    WHERE employee_id = p_employee_id_in ;

    RETURN l_salary ;
END;
/

(B)
CREATE OR REPLACE FUNCTION plch_salary
           (p_employee_id_in IN plch_employees.employee_id%TYPE)
       RETURN plch_employees.salary%TYPE
       RESULT_CACHE
       RELIES_ON (plch_employees)
AS
    l_salary   plch_employees.salary%TYPE;
BEGIN
    UPDATE plch_employees
    SET   salary = salary * 2
    WHERE employee_id = p_employee_id_in
    RETURNING salary INTO l_salary ;

    COMMIT;

    RETURN l_salary ;
END;
/

(C)
CREATE OR REPLACE FUNCTION plch_salary
            (p_employee_id_in IN plch_employees.employee_id%TYPE)
       RETURN plch_employees.salary%TYPE
       RESULT_CACHE
       RELIES_ON (plch_employees)
AS
    l_salary   plch_employees.salary%TYPE;
BEGIN
    UPDATE plch_employees
    SET   salary = salary * 2
    WHERE employee_id = p_employee_id_in ;

    COMMIT;

    SELECT salary
    INTO l_salary
    FROM plch_employees
    WHERE employee_id = p_employee_id_in ;

    RETURN l_salary ;
END;
/

(D)
CREATE OR REPLACE FUNCTION plch_salary
            (p_employee_id_in IN plch_employees.employee_id%TYPE)
       RETURN plch_employees.salary%TYPE
       RESULT_CACHE
       RELIES_ON (plch_employees)
AS
    l_salary   plch_employees.salary%TYPE;
BEGIN
  EXECUTE IMMEDIATE
     'SELECT salary FROM plch_employees
        WHERE employee_id = :employee_id'
      INTO l_salary
      USING p_employee_id_in ;

      RETURN l_salary ;
END;
/

(E)
CREATE OR REPLACE FUNCTION plch_salary
           (p_employee_id_in IN plch_employees.employee_id%TYPE)
       RETURN plch_employees.salary%TYPE
       RESULT_CACHE
       RELIES_ON (plch_employees)
AS
    l_salary   plch_employees.salary%TYPE;
BEGIN
    DELETE FROM plch_employees
      WHERE employee_id = p_employee_id_in
      RETURNING salary INTO l_salary ;

    COMMIT;

    RETURN l_salary ;
END;
/

(F)
CREATE OR REPLACE FUNCTION plch_salary
           (p_employee_id_in IN plch_employees.employee_id%TYPE)
       RETURN plch_employees.salary%TYPE
       RESULT_CACHE
       RELIES_ON (plch_employees)
AS
    l_salary   plch_employees.salary%TYPE;
BEGIN
    INSERT INTO plch_employees
       VALUES ( 500 + p_employee_id_in,
                'New Employee',
                (SELECT 2*salary FROM plch_employees
                    WHERE employee_id = p_employee_id_in)
              )
       RETURNING salary INTO l_salary ;
      
    COMMIT;

    RETURN l_salary ;
END;
/

liwei9902 发表于 2014-3-18 10:13:42

a 猜的

renfengjun 发表于 2014-3-18 10:23:42

正确答案：B、E、F
A 错误，update之后，result cache改变了。
B 结果正确，但是有点无法理解。
C 错误，第二次执行plch_salary(100) 的时候，其实使用的是result cache，没有真正执行update，在匿名块的update以后，result cache就改变了。所以其实一共执行了三次update。
D 错误，前两次的结果肯定是一样的，执行update之后，result cache改变了。
E 正确，结果正确，和B一样，无法理解。
F 正确，结果正确，和BE一样，无法理解。或者说只要加returning 子句的dml语句，都可以认定为不会影响result cache？并且也不受其他语句的影响？

liwei9902 发表于 2014-3-18 14:03:26

看了解答，发现result cache不是很实用，至少在我需要的功能中，不很实用

newkid 发表于 2014-3-19 03:23:49

答案BEF， 3楼得奖。

A: 函数结果被缓存，所以前两个调用都返回10000。但是，在UPDATE被执行后，函数再次被执行，这次返回20000。
B: 在Oracle 11gR2, RELIES_ON子句被忽略，数据库自动跟踪缓存结果所依赖的对象。令人惊奇的是，如果函数只在一张表上执行了一个UPDATE，并利用RETURNING子句返回一个列值，这张表并不会被注册为缓存结果所依赖的对象。

这个直接后果导致所有的UPDATE语句（函数内有一个，匿名块中有一个）都不会影响缓存结果，所以三次输出都是同样的值20000。
必须注意这个现象与11gR1不同，在该版本中RELIES_ON决定了缓存结果的依赖关系。

C:  一旦函数中包含了一个SELECT语句，缓存结果就会标记为依赖于PLCH_EMPLOYEES，所以前两个函数调用都返回20000, 但是在匿名块中的UPDATE语句执行之后，函数再次被执行，这次它返回80000。

在Oracle 11gR1中，这个选项的结果会有所不同，对于本题目同样是不正确的：

20000
40000
160000

D: 结果缓存依赖关系是由Oracle在执行的时候建立的，不是在编译的时候建立的。因此，虽然这个函数包含了一个动态的SELECT语句，结果缓存仍然会依 赖于PLCH_EMPLOYEES表，结果和静态的SELECT一样。前两个函数调用返回10000, 但是在UPDATE执行之后，第三个结果是20000。

E: 如果函数只在一张表上执行了一个DELETE，并利用RETURNING子句返回一个列值，这张表并不会被注册为缓存结果所依赖的对象。结果，不论是DELETE语句或者是匿名块中的UPDATE语句都不会使得缓存结果失效。
结果，我们会看到10000被显示三次。

必须注意这个现象与11gR1不同，在该版本中RELIES_ON决定了缓存结果的依赖关系。在该版本中，我们会看到10000仅仅被显示一次，随后是两个空值，因为在记录删除之后，缓存结果失效，函数再次执行并且返回了空值。

F: 此处函数确实执行了plch_employees表上的SELECT语句，但是仅仅用于INSERT语句中的VALUES子句的标量子查询。
在这种情况下，这张表并不会被注册为缓存结果所依赖的对象，因此，不论是INSERT语句或者是匿名块中的UPDATE语句都不会使得缓存结果失效。
结果，函数只会被执行一次，缓存结果20000会被显示三次。
必须注意这个现象与11gR1不同，在该版本中RELIES_ON决定了缓存结果的依赖关系。在该版本中，每次函数执行都会使得缓存结果失效，原因是 INSERT语句，所以，前两次执行会返回同样的值20000，而第三次执行发生于UPDATE语句之后，会返回40000。

dcswinner 发表于 2014-3-20 23:08:31

newkid 老师真牛逼，学习了。

newkid 发表于 2014-3-21 02:29:35

dcswinner 发表于 2014-3-20 23:08 static/image/common/back.gif
newkid 老师真牛逼，学习了。

NB个P, 这道题考的是11gR2的一个疑似bug的怪异表现，所以我做错了。

Bleach_ud 发表于 2014-3-22 10:27:52

newkid 发表于 2014-3-19 03:23 static/image/common/back.gif
答案BEF， 3楼得奖。

A: 函数结果被缓存，所以前两个调用都返回10000。但是，在UPDATE被执行后，函数再次 ...

漲姿勢了~~~

页: [1]

查看完整版本: PL/SQL Challenge 每日一题：2014-3-14 11gR2中带RELIES_ON子句的RESULT_CACHE函数