OCP 1Z0 053 54

54.Evaluate the following function code: 
CREATE FUNCTION get_dept_avg(dept_id NUMBER) RETURN NUMBER RESULT_CACHE 
RELIES_ON 
(EMPLOYEES) IS avgsal NUMBER(6); 
BEGIN 
SELECT AVG(SALARY)INTO avgsal 
FROM EMPLOYEES 
WHERE DEPARTMENT_ID = dept_id; 
RETURN avgsal; 
END get_dept_avg; 
Which statement is true regarding the above function? 
A. The cached result becomes invalid when any structural change is done to the EMPLOYEES table. 
B. If the function execution results in an unhandled exception, the exception result is also stored in the 
cache. 
C. Each time the function is invoked in a different session, the current result in the result cache gets 
overwritten. 
D. If the function is invoked with a different parameter value, the existing result in the result cache gets 
overwritten by the latest value. 
Answer: A 

任何可能引起数据变动的操作都会使RESULT_CACHE 失效。这样可以保证数据的正确性。

http://docs.oracle.com/cd/E11882_01/appdev.112/e25519/subprograms.htm#LNPLS708

Result Cache Bypass

In some situations, the cache is bypassed. When the cache is bypassed:

• The function computes the result instead of retrieving it from the cache.

• The result that the function computes is not added to the cache.

Some examples of situations in which the cache is bypassed are:

• The cache is unavailable to all sessions.

  For example, the database administrator has disabled the use of the result cache during application patching (as in "Hot-Patching PL/SQL Units on Which Result-Cached Functions Depend").

• A session is performing a DML statement on a table or view on which a result-cached function depends.

  The session bypasses the result cache for that function until the DML statement is completed—either committed or rolled back. If the statement is rolled back, the session resumes using the cache for that function.

  Cache bypass ensures that:

  • The user of each session sees his or her own uncommitted changes.

  • The PL/SQL function result cache has only committed changes that are visible to all sessions, so that uncommitted changes in one session are not visible to other sessions.