Pick any tuning manual, and you'll often see a section about coding with explicit cursors as opposed to implicit ones. The argument seems sound, typically going along the lines of:
"With an explicit cursor, you can open, fetch and close, whereas with an implicit cursor must issue open, fetch, second fetch, close - the second fetch being needed to check for a TOO_MANY_ROWS exception.
However, since 7.3, those cunning people at Oracle have worked around this problem and the second fetch is no longer required. Because PL/SQL is interpreted (watch out for the new native compile option in 9i), implicit cursors will actually run FASTER than explicit since typically you are using less code to achieve the same task.
You can try the following test for yourself:
REM REM First a temporary table REM create table blah ( x number primary key ) ; insert into blah select rownum x from sys.source$ where rownum < 1000; REM REM Make sure its got the full set of statistics REM analyze table blah compute statistics; REM REM A procedure to do approx 50,000 implicit reads REM create or replace procedure imp_test is y number; begin for i in 1 .. 50 loop for j in 1 .. 999 loop select x into y from blah where x = j; end loop; end loop; end; / REM REM A procedure to do approx 50,000 explicit reads REM create or replace procedure exp_test is cursor c(p number) is select x from blah where x = p; y number; begin for i in 1 .. 50 loop for j in 1 .. 999 loop open c(j); fetch c into y; close c; end loop; end loop; end; / REM REM We give each one a run to prime the buffers REM exec imp_test exec exp_test REM REM And now we see who is king of the roost... REM set timing on exec imp_test exec exp_test