There are many articles over the web about the difference between an explicit and implicit cursor. Theoretically, an implicit cursor is nicer and way faster. For an implicit cursor, Oracle takes care of OPEN, FETCH and CLOSE. Which, otherwise needs to be done explicitly while using an explicit cursor.
Let’s look at a demonstration to practically see the performance difference between an Explicit and Implicit cursor. I would use DBMS_UTILITY.GET_TIME to get the delta value of the start and end times of the execution.
In SQL*Plus, I execute the following anonymous block which has two cursors involved, first an explicit cursor and next is a CURSOR FOR loop.
SQL> SET SERVEROUTPUT ON SQL> DECLARE 2 l_rec all_objects%ROWTYPE; 3 l_start NUMBER; 4 5 CURSOR cur IS 6 SELECT * 7 FROM all_objects; 8 BEGIN 9 l_start := DBMS_UTILITY.get_time; 10 11 OPEN cur; 12 LOOP 13 FETCH cur 14 INTO l_rec; 15 16 EXIT WHEN cur%NOTFOUND; 17 18 -- Do something. 19 NULL; 20 END LOOP; 21 CLOSE cur; 22 23 DBMS_OUTPUT.put_line('Explicit Cursor Loop: ' || 24 (DBMS_UTILITY.get_time - l_start) || ' hsecs'); 25 26 l_start := DBMS_UTILITY.get_time; 27 28 FOR rec IN (SELECT * FROM all_objects) LOOP 29 -- Do something. 30 NULL; 31 END LOOP; 32 33 DBMS_OUTPUT.put_line('Implicit Cursor Loop: ' || 34 (DBMS_UTILITY.get_time - l_start) || ' hsecs'); 35 END; 36 / Explicit Cursor Loop: 167 hsecs Implicit Cursor Loop: 108 hsecs PL/SQL procedure successfully completed. SQL>
So the difference is quite significant. The cursor for loop took much less time as compared to the explicit cursor. The time in hsecs is hundredths of a second.