Re: Implicit vs. Explicit cursors

From: Thomas J. Kyte <tkyte_at_us.oracle.com>
Date: 2000/05/30
Message-ID: <8h0r4g$gpo$1@nnrp1.deja.com>#1/1

arc wrote:
>>
>> In our current project we discovered some bugs which we would never
 have
>> discovered with explicit cursors (too_many_rows exceptions where
 they were
>> not supposed to occur and which could not be prevented by means of
 unique
>> constraints...).
>>
>> Marc
>>
>> Paul Martin wrote in message <8gjlia$i9s$1_at_bw107zhb.bluewin.ch>...
>> >Hello
>> >
>> >Can anybody tell me how penalizing implicit cursor are on an Oracel
 7.3.4.
>> >Is it worth changing all the implicit cursors to explicit cursors
 in our
>> >plsql packages ?
>> >Should implicit cursors even be used ?
>

I believe you've confused some terms here. You tell us to use explicit cursors
but you describe IMPLICIT cursors. Perhaps you meant "bind variables????"

>That would only occur if you use the explicit cursor in a manner that
>does not check for TOO_MANY_ROWS.
>
>SELECTs should always be performed with explicit cursor. I have yet to
>find a single instance when an implicit cursor will work better then an
>implicit one and as a dba I have actually had to tell people to rewrite
>their code to use explicit cursors because the way they wrote their
 code
>the implicit cursor was being re-parsed with every use and I had to
>teach them how to write a cursor that could be reused.
>

declare

   l_cnt number;
begin

   select count(*) into l_cnt from T;
end;
/

that is an implicit cursor.

declare

   cursor c is select count(*) from t;
   l_cnt number;
   l_dummy number;
begin

   open c;
   fetch c into l_cnt;
   if ( c%notfound )
   then

      raise no_data_found;
   end if;
   fetch c into l_dummy;
   if ( NOT c%notfound )
   then

      raise too_many_rows;
   end if;
   close c;
end;
/

That is the equivalent code written using an explicit cursor. Please tell me
you are not telling us to write the second way are you? The first way, in
addition to executing faster, is less error prone when coding...

>On the other hand, using implicit cursors for INSERT, UPDATE and DELETE
>is the norm - although it is important to rigorously check for error
>conditions and bind variables should be used whenever possible.
>

well in plsql -- unless you use DBMS_SQL -- they are the only way. In Pro*C,
unless you use dynamic sql -- they are the only way as well....

>Also, completely forget about OPEN/FETCH/CLOSE for explicit cursors.
>Always use a cursor FOR loop. Once you get used to writing them and

the cursor in a cursor for loop IS an implicit cursor by definition. if you
code:

begin

   for x in ( select * from T ) loop
     ...
   end loop;
end;
/

that *is* implicit. The OPEN, the FETCH, the CLOSE are done for you -- i
believe you've confused some terminology. The explicit version would be:

declare

    cursor c is select * from t;
    l_rec t%rowtype;
begin

    open c;
    loop

       fetch c into l_rec;
       exit when (c%notfound);
       ....
     end loop;
     close c;

end;
/

I agree with you -- always use the cursor for loop. As fast (if not faster) and
less error prone from a coding perspective.

>handling all of the possible exceptions/errors you'll never want to go
>back to the OPEN/FETCH/CLOSE syntax. Steve Feuerstein got this wrong in
>his book and too many people follow his advice without thinking through
>all of the consequences (he claims that there are no adverse
>consequences and tries to lead the reader to believe that there are
>inherent problems with the cursor FOR loop and trusts that the reader
>won't know better).

--
Thomas Kyte (tkyte_at_us.oracle.com) Oracle Service Industries
Howtos and such: http://osi.oracle.com/~tkyte/index.html
Oracle Magazine: http://www.oracle.com/oramag
Opinions are mine and do not necessarily reflect those of Oracle Corp


Sent via Deja.com http://www.deja.com/
Before you buy.

Received on Tue May 30 2000 - 00:00:00 CDT