Advantage of explicite over implicite cursor

[parbhani]

Hi all,

Just a question about the implicit and explicit cursors.
Its recommended in many books and documents that in PL/SQL one should use explicit cursors, ALWAYS, even if you know that the cursor will be returning( or will be used to return ) only one row.
What is the exact difference that will make ?
Please guide.

Regards

 

[sem]

In fact select into may perform 2 fetches: 1 to get a row and another one to verify it's the only row satisfying conditions. Even if you know that second fetch is redundant So explicit cursor provides more control over execution and may improve performance.

Regards, Dima

 

[jocave]

Actually, if anything, implicit cursors will tend to be faster, though this difference is likely to be miniscule. Tom Kyte has a couple of discussions on this

http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:1205168148688

http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:4696422878211

It's far more important is that you use the convention that will tend to make the most readable code. To me, that's implicit cursors, though there are other people that disagree.

Justin