-
implicit vs explicit
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 ?
-
try it and find out, or better yet ask him why he thinks so because obviously that is not a complete example
-
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.
-
 Originally Posted by culonbu
What do you think ?
Most people thinks your developer is dead wrong... http://asktom.oracle.com/pls/asktom/...:1205168148688
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
-
proven
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.
-
ask him why, prove it - should be fairly simple if he insists it is better
-
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?
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
-
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.
-
 Originally Posted by gandolf989
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)?
 Originally Posted by gandolf989
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
-
consultant
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
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|