Re: Implicit vs. Explicit cursors

thomas, the performance of explict and implicit cursor are about the same, so reusability of sql is not a issue as described by kenneth. ?
ted
In article <8h0r4g$gpo$1_at_nnrp1.deja.com>,   Thomas J. Kyte <tkyte_at_us.oracle.com> wrote:
> 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.
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Tue May 30 2000 - 00:00:00 CDT