Yes. Open-fetch-close of explicit cursors is outdated.
Why?
Well they're just more typing for one. But more importantly they're slower:
https://oracle-base.com/articles/misc/implicit-vs-explicit-cursors-in-oracle-plsql That said there are a couple of cases where explicit cursors are better.
Bulk collectionYou can do an implicit bulk collect like:
select ...
bulk collect into array
from ...
But this is unbounded. If your query can return a large number of rows, you could run into memory issues. Generally it's better to use explicit cursors and fetch with a limit. e.g.:
open cur;
loop
fetch cur into array limit 100;
exit when array.count = 0;
...do stuff...
end loop;
close cur;
Client fetchingSometimes you may want to open a cursor and leave it up to the client to fetch the rows. So you have a function that opens and returns the cursor:
create or replace function f
return sys_refcursor as
cur sys_refcursor;
begin
open cur for select * from ...;
return cur;
end;
/
And another process that reads the rows:
begin
cur := f;
loop
fetch cur into array;
exit when cur%notfound;
...
end loop;
close cur;
end;
/
Example of when you'd do this is to allow users to page through the results. But you need to have a stateful application to do this. In the world of the web this is less common. So arguably this approach is outdated too!
If you want to know more about different cursors, check out Steven's articles:
http://www.oracle.com/technetwork/issue-archive/2013/13-mar/o23plsql-1906474.html http://stevenfeuersteinonplsql.blogspot.com/2016/05/types-of-cursors-available-in-plsql.html