Printable View
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.
Most people thinks your developer is dead wrong... http://asktom.oracle.com/pls/asktom/...:1205168148688Quote:
Originally Posted by 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.
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?
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.
Gandolf, I'm confused by this statement.Quote:
Originally Posted by gandolf989
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'm not sure the function based approach is the best here. If I have a table with no rows and issue:Quote:
Originally Posted by gandolf989
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
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
-- This is an implicit cursor.
-- If you want to process every row returned by a query use this.
-- This is function that uses an explicit cursor to see if there is data.Code:DECLARE
CURSOR cur_implicit IS
SELECT department
FROM mytable
WHERE department = 'Sales';
BEGIN
FOR v_row IN cur_implicit
LOOP
-- do stuff here
END LOOP;
-- It doesn't tell you how many rows, or anything about any other column
-- in the table. But if you want to know whether or not a column has a
-- given value this is a good way to find out.
-- You can also do this, but the potential errors are NO_DATA_FOUNDCode:CREATE OR REPLACE FUNCTION departmentExists
( p_department mytable.department%TYPE)
RETURN BOOLEAN
CURSOR cur_explicit IS
SELECT department
FROM mytable
WHERE department = 'Sales';
v_department mytable.department%TYPE;
BEGIN
OPEN cur_explicit;
FETCH cur_explicit INTO v_department;
CLOSE cur_explicit;
RETURN TRUE;
EXCEPTION
WHEN NO_DATA_FOUND THEN
CLOSE cur_explicit;
RETURN FALSE;
END departmentExists;
/
-- and TOO_MANY_ROWS_RETURNED.
Code:DECLARE
v_mytable_row mytable%ROWTYPE;
BEGIN
SELECT col1, col2, col3
INTO v_mytable_row
WHERE department = 'Sales';
END;
/
gandolf989 wrote
Quote:
CREATE OR REPLACE FUNCTION departmentExists
( p_department mytable.department%TYPE)
RETURN BOOLEAN
CURSOR cur_explicit IS
SELECT department
FROM mytable
WHERE department = 'Sales';
v_department mytable.department%TYPE;
BEGIN
OPEN cur_explicit;
FETCH cur_explicit INTO v_department;
CLOSE cur_explicit;
RETURN TRUE;
EXCEPTION
WHEN NO_DATA_FOUND THEN
CLOSE cur_explicit;
RETURN FALSE;
END departmentExists;
/
This has potential retrieving incorrect informattion IF MORE THAN 1 RECORD satisfy specified condition.
gandolf989 wrote
this function is correct to check if department exists in table.Quote:
CREATE OR REPLACE FUNCTION departmentExists
( p_department mytable.department%TYPE)
RETURN BOOLEAN
CURSOR cur_explicit IS
SELECT department
FROM mytable
WHERE department = 'Sales';
v_department mytable.department%TYPE;
BEGIN
OPEN cur_explicit;
FETCH cur_explicit INTO v_department;
CLOSE cur_explicit;
RETURN TRUE;
EXCEPTION
WHEN NO_DATA_FOUND THEN
CLOSE cur_explicit;
RETURN FALSE;
END departmentExists;
/
but if you want to retrieve some column values, you should use
and check for NO_DATA_FOUND and TOO_MANY_ROWSQuote:
DECLARE
v_mytable_row mytable%ROWTYPE;
BEGIN
SELECT col1, col2, col3
INTO v_mytable_row
WHERE department = 'Sales';
END;