Shulou(Shulou.com)06/01 Report--

Http://www.itpub.net/thread-1499223-19-1.html

185th floor

I created the following table and filled in the data:

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;/

Then I compiled the following function:

CREATE OR REPLACE FUNCTION plch_foo (p_in_value IN NUMBER) RETURN VARCHAR2 RESULT_CACHEIS 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;/

Which blocks of code will display "PL/SQL Challenge" only once after execution?

(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 ChallengePL/SQL procedure successfully completedSQL >

(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 ChallengePL/SQL procedure successfully completedSQL >

(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 ChallengePL/SQL ChallengePL/SQL procedure successfully completedSQL >

(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 ChallengePL/SQL ChallengePL/SQL procedure successfully completedSQL >

(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 = 18 WHERE another_column = 2 9 10 lvdummy: = plch_foo (4); 11 END; 12 / PL/SQL ChallengePL/SQL procedure successfully completedSQL >

The answer is on the 191st floor.

2011-11-24 the result caching mechanism of the answer ABE.PL/SQL function provides a way to cache the results of the PL/SQL function in SGA, which is available to all sessions running the application. The caching mechanism is simple and effective, so you don't have to bother to design and develop your own cache and its management mechanism. To open the result cache of a function, you only need to add a RESULT_ cache clause to the function header (which must be used in both the header and the package body if used in PACKAGE). When a result cache function is called, the system checks the cache. If the result of the previous call exists in the cache and the parameters are the same, the system returns result two directly instead of executing the function body again. If the result is not found in the cache, the system executes the function body and adds the result to the cache (for this set of parameters) before returning control to the caller. The function result cache usually stores the data of one or more tables queried in the function body. ORACLE needs to make sure that the copy of this data is clean (that is, it has not been modified). ORACLE ensures data cleanliness through two mechanisms: 1. Whenever a table is modified and committed, all result caches that depend on the table are cleared. A subsequent call to the function causes the data to enter the cache again. two。 Once you modify the table during the session, all caches that depend on the table will be ignored. That is, the function body that caches the result depending on the modified table will be executed until you commit or roll back the changes. A: the parameter of both calls is NULL, and ORACLE thinks that there is no change, and directly takes the cached result instead of re-calling it. B: as above, the parameters have not changed. C: the parameters do not change, but the code modifies the plch_foo table, and the function's cache is no longer valid for the current session; after COMMIT occurs, CACHE is no longer valid for all sessions. So when the function is called again, the function is executed again D: as above, although there is no COMMIT, the cache is no longer available for the current session, so the function will be executed again. If this option is followed by more calls: lvdummy: = plch_foo (3); the function body will be reexecuted each time. E: UPDATE does not modify any data, so the cache is still valid.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.