Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

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

Posted on 2006-03-22
10
Medium Priority
?
29,892 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.
0
Comment
Question by:MichaelHuth
9 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 16266483
for rec in (select a,b,c from x)
loop
  begin
    <script>
 
  exception
    when <exception> then
      -- handle the exception

  end;
end loop;
0
 

Author Comment

by:MichaelHuth
ID: 16266616
I know how to handle an exception, but not the pl/sql equivalent to a "continue" statement as known in other languages.
0
 
LVL 9

Expert Comment

by:konektor
ID: 16266629
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;
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 16

Accepted Solution

by:
RCorfman earned 500 total points
ID: 16266679
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...


0
 
LVL 16

Expert Comment

by:RCorfman
ID: 16266686
I was writing and researching as konektor  was posting....
0
 
LVL 16

Expert Comment

by:RCorfman
ID: 16266698
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).
0
 
LVL 13

Expert Comment

by:riazpk
ID: 16268610
for rec in (select a,b,c from x)
loop
  begin
    <script>
  exception
    when others then null;
  end;
end loop;
0
 
LVL 35

Expert Comment

by:Mark Geerlings
ID: 16274278
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.
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

572 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question