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]
04-17 15:05