next record

[swakoo]

i have a cursor.
select name from person

open cursor
loop
fetch cursor into v_name
exit when cursor%notfound

there are afew condition check here.
is it possible that if one condition fails straight away procceed to next record instead of
finish all the check?

if v_name='ABC' then
next record;
end if;
end loop
close cursor

 

[ScottMattes]

Yes, at least a couple that come right to mind.

1) if condition1
then

elsif condition2
then

elsif condition3

end if;

2) loop
fetch
exit when

loop
if condition1
then
exit; -- this leaves inner loop
end if;

exit; -- only do this inner loop once

end loop;

end loop;

 

[Makker]

Hi,

I'm used to be very strict in structured programming standards. I actually never use 'exit when'. May be only to do code a 'repeat until', since Pl/Sql has no repeat until construct (only a while-loop).
In your case, I suggest to use a for-loop:

for v_name in c_person
Loop
if condition 1
then
...
elsif condition 2
then
...
elsif condition 3
then
...
end if;
end loop;

If you use multiple exit or exit-when constructions based on different conditions then in a complex piece of code it is hard to verify if the loop is exited under the correct conditions. Programmars that have to maintain your code have a hard job to validate and change your code. So think carefully about exit-conditions of your loop and only code it on one-place. And also don't use exit-when in a for-loop: you actually meant to do a while or repeat-until-loop in that case. I see lazy-people do that: they don't have to code an open and close statement.

Regards,
Martien

 

[swakoo]

i found a better way of going..
open cursor
loop

fetch cursor into v_name
exit when cursor%notfound

there are afew condition check here.
is it possible that if one condition fails straight away procceed to next record instead of
finish all the check?

if v_name='ABC' then
goto next_record;
end if;
end loop
close cursor

anyway thanks all for ur help..

 

[William Robertson]

No, there is no next_record/continue command. You just have to construct an IF or CASE statement.

In a cursor loop, surely you would just define the SQL to return only the rows you want to process?

Also, an explicit OPEN-FETCH-CLOSE etc is normally only for ref cursors. Is that what yours is? If not, the whole OPEN-FETCH-CLOSE thing is more complicated than it needs to be.

 

[swakoo]

yes. there is. i have tried it in pl/sql.
dun believe can try for yourself. but the fact is i tried and it satisfy what i needed.

my sql only return the rows i want to process. but each row i have to check the condition and process differently.
ps. No hard feeling.

 

[William Robertson]

> yes. there is. i have tried it in pl/sql.

Yes there is what? What have you tried in PL/SQL? There is no next_record/continue command.

I hope you don't mean GOTO, which is rightly banned throughout the civilised world.

If the SQL only returns the rows you need to process, why do you need a "continue to next record without any processing" command?

Still curious.

 

[Marco van der Linden]

William's right. GOTO is a definite NO (maybe Oracle should remove it from PL/SQL alltogether). A cleaner way to do this could be the use of a private procedure.

declare
  cursor
    my_cursor is select *
                 from   your_table;
  my_cursor_rec  my_cursor%rowtype;
  --
  procedure validate_stuff(p_record in out my_cursor%rowtype)
  is
  begin
    --
    if <condition>
    then
      return
    elsif <>
    then
      etc.
    end if;
  end validate_stuff;
begin
  open my_cursor;
  loop
    fetch my_cursor into my_cursor_rec;
    if my_cursor%notfound
    then
      close my_cursor;
      exit;
    end if;
    validate_stuff(p_record => my_cursor_rec);
  end loop;
end;

Now you loop is 'logically' empty, so returning from the private procedure will automatically cause the next record to be fetched.

'There's no going back from GOTO, and you'll never know where you're going to'

 

[TenGor]

Hy,

what is the exact problem with "GOTO"?
Sometimes it's a good thing when you have a complex if-condition.
Well i didn't use the goto-mark like swakoo to go to the start of a loop.
I only use it jump over the if's to the end of a loop and do nothing in this round.

 

[William Robertson]

See "Go To Statement Considered Harmful" (Edsger W. Dijkstra, 1968). It's a bit hard to read in places but it's a classic that explains in detail why GOTO "is too much an invitation to make a mess of one's program".

Perhaps there are very simple uses of GOTO, such as navigating to the end of a loop, which strictly speaking do not violate structured programming principles, but it is still an inherently messy command and there is always a neater solution IMHO.

 

[TenGor]

Hy,

i see the problems with GOTO. So i will use it very carefully in the next time (and only in "simple" context).
Well, just learned a new thing today ;-)

 

[Makker]

Also in swakoo's example the goto is absolutely needless. See my earlier example and that of ScottMatters. Also I don't see why Marco van der Linden is using open cursor, fetch, if-not-found close cursor. This altogether can be done with a for-cursor-loop (see my example). The only reason not to use a for-cursor-loop is when you expect that you won't read all the rows in the cursor. Then I would do it Marco's way. Marco's right in extracting the processing in a separate procedure to keep the loop content clean and simple.

 

[Marco van der Linden]

The reason I used an explicit cursor fetch is to be able to access row info outside the scope of the for loop. Of course it could be done like:

declare
  ..
  my_cursor_rec my_cursor%rowtype;
  ..
begin
  for r in my_cursor
  loop
     my_cursor_rec := r;
     ..
  end loop;
end;

But in general, whenever I want to be able to use cursor data outside of the scope of the loop, I prefer to use the open/fetch/close method. But then again, that's just a matter of personal preference

 

[Ivan C.]

Another way of handling this is to include the executable statements within the loop into an "inner" block, and raise an exception when you need to "continue". It's readable, and it works the way you expect it to. The only drawback is that you'll have to declare the exception and then handle it, but this approach is a common practice.

Here's an example:
[/code]
DECLARE
CURSOR c IS
SELECT name
FROM person;
--
e_next_record EXCEPTION;
BEGIN
FOR r IN c LOOP
BEGIN
IF r.name = 'something' THEN
RAISE e_next_record;
END IF;
--
-- Process cursor's record here
EXCEPTION
WHEN e_next_record THEN
NULL;
END;
END LOOP;
END;
[/code]