Re: PLS/SQL: OK to use EXIT in a cursor FOR LOOP ??

From: <cpereyra_at_ix.netcom.com>
Date: Fri, 18 Jun 1999 00:23:45 GMT
Message-ID: <7kc3i7$2td$1_at_nnrp1.deja.com>


Hi Steve,

re: "Uh... Where does the book say it is "poor practice?"

The reference is in page 225.

Carlos.

In article <7kb90d$oad$1_at_nntp.csufresno.edu>,   stevec_at_zimmer.csufresno.edu (Steve Cosner) wrote:
> In article <376901E0.60FDC704_at_lucent.com>,
> Kenneth C Stahl <kstahl_at_lucent.com> wrote:
> > That is a perfectly valid way to do what you describe. Steve
> > Feuerstein, the author of the O'Reilly book "Oracle PL/SQL
> > Programming" says that it is poor practice and touts the
> > open/fetch/close scenario, but he seems to have a fundamental lack
> > of understanding that the advantages of using cursor "for" loops
> > outways any "computer weenie" complaints that exiting from a cursor
> > "for" loop amounts to a "goto".
>
> Uh... Where does the book say it is "poor practice?" I found where it
> says the "FOR loop is one of my favorite PL/SQL features." But the
> bad practice is where you write some huge cursor FOR loop, with exits
> sprinkled throughout the code.
>
> I almost never use the cursor for loop because the cursor record
> values are ONLY available within the loop. I have to write procedures
> with hundreds of lines of code, and I need the data from the cursor
> throughout all those lines of code. I do NOT want to create a single
> loop that is hundreds of lines long, so I use the Open/Fetch/Close
> method instead. The loop is contained within a small "control module"
> procedure that calls many other procedures to do the work.
>
> Using a cursor FOR loop, and also erroneously defined cursor record is
> a HUGE pitfall for inexperienced programmers. The following has
> caused several of my associates hours and days of time wasted hunting
> down bugs:
>
> CURSOR my_csr IS
> SELECT a, b, C FROM my_table ORDER BY d;
> v_data my_csr%rowtype;
>
> FOR v_data IN my_cursor LOOP
> -- do some work
> END LOOP;
> If v_data.a = xxx then
> -- do some more work
>
> The above compiles just fine in PL/SQL, but it will NEVER work!
>
> > You'll never go wrong if you use cursor "for" loops. The biggest
> > reason is that if you ever raise an exception, the PL/SQL engine
> > will automatically close the cursor as soon as execution passes out
> > of scoop for the loop. The same thing goes with an exit - the engine
> > automatically performs the close and any necessary cleanup.
> >
> > IMHO - forget about the open/fetch/close sequence and always write
> > cursor "for" loop. If you need to jump out when a certain condition
> > exists, use the 'exit' statement.
>
> Raising exceptions or using many exit statements is the same as using
> Go_To's. In my opinion, that is poor programming practice.
>
> Steve Cosner
>

Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't. Received on Fri Jun 18 1999 - 02:23:45 CEST

Original text of this message