implicit vs explicit

[culonbu]

Hi Gurus,

Can i have your expert opinion about the following :

1 . open cursor
fecth record into var -- fecth 1 record only
close cursor


2. select bla into var


a developer - he claimed to be expert in PL/SQL - say that 1 is faster than 2.

What do you think ?

[davey23uk]

try it and find out, or better yet ask him why he thinks so because obviously that is not a complete example

[jay_l_a]

As Davey says, why not try some benchmarking and find out.

It depends on a number of factors, but generally the implicit cursor will be faster.

'Select into' used to be almost frowned upon, maybe that is where the developer is coming from.

It's much easier to interpret:

select field1 into variable1 from table1;


than

cursor cursor1 is
select field1
from table1;

begin

open cursor1;
fetch cursor1
into variable1;
close cursor1;


I know which one I'd rather read.

[PAVB]

What do you think ?

Most people thinks your developer is dead wrong... http://asktom.oracle.com/pls/asktom/...:1205168148688

[culonbu]

Thank you for all your replies.

I have checked various forums , everyone agrees that implcit is better and less prone for error, but this developer insists that explicit is better according to Joakim Treugut ( http://www.linkedin.com/pub/2/2A0/826) and does not want to change - he always use explicit cursor.

[davey23uk]

ask him why, prove it - should be fairly simple if he insists it is better

[PAVB]

Support Dave.

On top of it, using explicit cursor all the time makes code very difficult to read which will negatively impact code maintenance.

Is the guy part of your team, are you his boss?

[gandolf989]

The question should not be which is faster but which is better. The answer depends on what you are doing. Unless you only want one row or you are doing a bulk collect into a collection don't use a select into. If you want every row that a query returns then by all means use an implicit cursor. There is less stuff to type and the cursor will be opened and closed for you. However, if you might exit the for loop early, then you might want an explicit cursor.

I worked on a metadata modeling tool years ago and I replaced dozens of cases of select count(*) into...if count(*) > 1... code with functions that opened a cursor read a single line, closed the cursor of course, then returned either true or false depending on whether or not there was data. I suppose I could have also done a select into with rownum=1. Either way I would have to handle the error if no data was found, which meant that the row did not exist.

[jay_l_a]

Unless you only want one row or you are doing a bulk collect into a collection don't use a select into.

Gandolf, I'm confused by this statement.
If I read right, you are saying for:

SINGLE rows, use select into (implicit cursor)
MULTIPLE rows use select bulk collect into (again, implicit cursor)

What else is there (aside from a cursor that returns no rows)?

I suppose I could have also done a select into with rownum=1. Either way I would have to handle the error if no data was found, which meant that the row did not exist.

I'm not sure the function based approach is the best here. If I have a table with no rows and issue:

SQL> TRUNCATE TABLE j1;

Table truncated.

SQL> SELECT COUNT(*)
2 FROM j1;

COUNT(*)
----------
0

There is no error handling required. I'm completely open to new ideas, were you simply trying to improve readability, or is there another reason?
Thanks,
J

[culonbu]

Thanks guy.

he's working for a consultant company contracting to this project.

i wonder how he got the job in the first place.

implicit is better than explicit in oracle ver 7

he must be in the STONE-aged