Loop one time takes more time than loop 200 times ...
I all !
I've encountered a strange behavior with a loop in PL/SQL block.
Here is the PL/SQL code :
set serveroutput on size 1000000
DECLARE
TYPE fdv_type is REF CURSOR return view_test%rowtype;
PIO_CURSOR fdv_type;
PI_DATE DATE;
PI_PDT_NIV NUMBER;
FETCH_ROW PIO_CURSOR%ROWTYPE;
BEGIN
DBMS_OUTPUT.ENABLE(32768) ;
PI_DATE := '20/06/03';
PI_PDT_NIV := 1;
P_TEST( PIO_CURSOR, PI_DATE, PI_PDT_NIV);
dbms_output.put_line('before loop : '||to_char(sysdate,'DD/MM/YYYY HH24:MI:SS'));
LOOP
FETCH PIO_CURSOR INTO FETCH_ROW ;
EXIT WHEN PIO_CURSOR%NOTFOUND ;
END LOOP;
dbms_output.put_line('after loop : '||to_char(sysdate,'DD/MM/YYYY HH24:MI:SS'));
DBMS_OUTPUT.PUT_LINE(PIO_CURSOR%ROWCOUNT);
END;
/
When the PI_PDT_NIV=1, the p_test procedure retrieves one row, when the PI_PDT_NIV=5, the p_test procedure retrieves 200 rows.
The p_test() procedure always takes less than one second to execute(for PI_PDT_NIV=1 or PI_PDT_NIV=5).
But the loop takes 5 seconds when the cursor contains one row, and less than one second when the cursor contains 200 rows ...
How could this happen ?
Thanks a lot for your help ...
