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;
/
a 猜的
正确答案：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？并且也不受其他语句的影响？
看了解答，发现result cache不是很实用，至少在我需要的功能中，不很实用
答案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。
newkid 老师真牛逼，学习了。
dcswinner 发表于 2014-3-20 23:08 static/image/common/back.gif
newkid 老师真牛逼，学习了。
NB个P, 这道题考的是11gR2的一个疑似bug的怪异表现，所以我做错了。
newkid 发表于 2014-3-19 03:23 static/image/common/back.gif
答案BEF， 3楼得奖。
A: 函数结果被缓存，所以前两个调用都返回10000。但是，在UPDATE被执行后，函数再次 ...
漲姿勢了~~~