Let me share with you guys new test which I did:
I again created following two functions, this time without exception:
CREATE OR REPLACE FUNCTION example1 RETURN VARCHAR2
IS
i VARCHAR2(10);
BEGIN
BEGIN
SELECT ‘x’ INTO i FROM DUAL;
RETURN i;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RETURN null;
END;
END example1;CREATE OR REPLACE FUNCTION example2 RETURN VARCHAR2
IS
BEGIN
FOR c1 IN (SELECT ‘x’ col1 FROM DUAL) LOOP
RETURN c1.col1;
END LOOP;
RETURN NULL;
END example2;
Then I ran following PL/SQL block to collect statictics:
DECLARE
x VARCHAR2(10);
l_result PLS_INTEGER;
BEGIN
l_result := DBMS_PROFILER.START_PROFILER(RUN_COMMENT => ’selectinto_ok’);
FOR i IN 1 .. 1000000 LOOP
x := example1;
END LOOP;
l_result := DBMS_PROFILER.STOP_PROFILER;
END;DECLARE
x VARCHAR2(10);
l_result PLS_INTEGER;
BEGIN
l_result := DBMS_PROFILER.START_PROFILER(RUN_COMMENT => ‘forcusor_ok’);
FOR i IN 1 .. 1000000 LOOP
x := example2;
END LOOP;
l_result := DBMS_PROFILER.STOP_PROFILER;
l_result := DBMS_PROFILER.FLUSH_DATA;
END;
My results:
RUNID RUN_DATE RUN_COMMENT RUN_TOTAL_TIME
—— ——— ————————————————– ———————–
13 14-AUG-07 selectinto_ok 18,734,314,785,817.00
14 14-AUG-07 forcusor_ok 23,177,037,928,563.00
Once there was no exception, the result totally reversed.
wow!
0 responses so far ↓
There are no comments yet...Kick things off by filling out the form below.
Leave a Comment