We help IT Professionals succeed at work.

How to use "continue" in a pl/sql for-loop

MichaelHuth
MichaelHuth asked
on
Medium Priority
30,088 Views
Last Modified: 2011-08-18
I use a for-loop in a pl/sql script like:

for rec in (select a,b,c from x) loop begin <script> end; end loop;

If have catched an exeption and want to do "continue" just to skip the current record, what is the appropriate way in pl/sql to do this? I can't find a "continue" statement...

Thank you.
Comment
Watch Question

Guy Hengel [angelIII / a3]Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009

Commented:
for rec in (select a,b,c from x)
loop
  begin
    <script>
 
  exception
    when <exception> then
      -- handle the exception

  end;
end loop;

Author

Commented:
I know how to handle an exception, but not the pl/sql equivalent to a "continue" statement as known in other languages.

Commented:
or u can use goto statement:

for ... loop
  --SQL command
  if <some condition> then
    GOTO continue; -- skip additional work inside a loop
  end if;
  -- additional work inside a loop
  <<continue>>
  null; -- label must be followed by executable code ("end loop" is not executable, "null" is)
end loop;
Commented:
If you can put the exception at the very end of the loop as angelIII is showing, it is essentially the same thing, you jump to the exception, handle it, then continue on.  If you need exceptions in the middle too, then you can always create a new exception type and raise that when you want to go to the end of the loop.

Believe it or not, you can also use labels and goto to do what you are requesting... May seem unconventional (like people say goto is bad), but if they are used in a controlled understood manner, then they are ok and do what you need.

this is a slightly modified exacmple from the Oracle Manual...

The label end_loop in the following example is not allowed because it does not precede an executable statement:
DECLARE
   done  BOOLEAN;
BEGIN
   ...
   FOR i IN 1..50 LOOP
      IF need_to_continue THEN
         GOTO end_loop;
      END IF;
      ...
   <<end_loop>>  -- not allowed
   END LOOP;  -- not an executable statement
END;

To debug the last example, just add the NULL statement, as follows:
FOR i IN 1..50 LOOP
   IF need_to_continue THEN
      GOTO end_loop;
   END IF;
   ...
<<end_loop>>
NULL;  -- an executable statement
END LOOP;

The above will jump to the end of the loop and check the condition to continue. I believe this is what you are requesting...


Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts

Commented:
I was writing and researching as konektor  was posting....

Commented:
If you want the alternative using a self-declared exception to do this without goto's, I can provide it, but it doesn't make sense to put that together unless you are ademant about avoiding goto in code (many people are).
Ora_TechieDatabase Administrator
CERTIFIED EXPERT

Commented:
for rec in (select a,b,c from x)
loop
  begin
    <script>
  exception
    when others then null;
  end;
end loop;
Mark GeerlingsDatabase Administrator
CERTIFIED EXPERT

Commented:
There is no "continue" statement in PL\SQL.  You just need a local exception handler inside the loop at the level where the exception is raised (like in the example from riazpk above, or in the link that Kollu posted).  You may also have an exception-handler outside of the loop if you want to handle errors that happen outside of the loop, if any.

I use that technique in many of our interface jobs so a record with a problem is not processed, but at least all others do get processed, and the procedures finish without raising an exception.
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.