Should I always use explicit cursors where coding PL/SQL

The Oracle (tm) Users' Co-Operative FAQ

Should I always use explicit cursors when coding PL/SQL

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 
复制

Let me know how you go - implicit wins on every server I've tried

Further reading: N/A