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

[Quoted] 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.

[Quoted] 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.

[Quoted] 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 Received on Thu Jun 17 1999 - 18:50:21 CEST