'CONTINUE' keyword in Oracle 10g PL/SQL

Question

I'm migrating a TSQL stored procedure to PL/SQL and have encountered a problem - the lack of a CONTINUE keyword in Oracle 10g.

I've read that Oracle 11g has this as a new feature, but upgrading is not an option unfortunately.

Is there any alternative to CONTINUE in 10g? I don't believe it's practical to restructure the logic of the SP as a work-around, because I have an outer loop, an IF, then a nested IF, then the CONTINUE at the end of a statement block within that IF.

Any help would be greatly appreciated, cheers.

Trishatriskelion

Answer 1

You can simulate a continue using goto and labels.

DECLARE
   done  BOOLEAN;
BEGIN
   FOR i IN 1..50 LOOP
      IF done THEN
         GOTO end_loop;
      END IF;
   <<end_loop>>  -- not allowed unless an executable statement follows
   NULL; -- add NULL statement to avoid error
   END LOOP;  -- raises an error without the previous NULL
END;

Berard

Sometimes, we just have to type it in and cover our noses afterwards. :) – Berard

You can also use a construct that mimics a continue perfectly. And without adding any null :) See my response below. – Jannet

As the answer below suggests (@Thorsten), I think you might be looking for EXIT. – Cromlech

Answer 2

Though it's a bit complex and just a fake, you can use exception this way :

DECLARE
  i NUMBER :=0;
  my_ex exception;
BEGIN
  FOR i IN 1..10
  LOOP
      BEGIN
         IF i = 5 THEN
            raise my_ex;
         END IF;
         DBMS_OUTPUT.PUT_LINE (i);
      EXCEPTION WHEN my_ex THEN
         NULL;
      END;
  END LOOP;

END;

Foal

Thank you so much I was looking for how to handle exceptions inside a FOR LOOP and you nailed it! Adding the logic inside the extra BEGIN ... END block solved my problem! – Tarentarentum

Answer 3

In fact, PL SQL does have something to replace CONTINUE. All you have to do is to add a label (a name) to the loop :

declare
   i integer;
begin
   i := 0;

   <<My_Small_Loop>>loop

      i := i + 1;
      if i <= 3 then goto My_Small_Loop; end if; -- => means continue

      exit;

   end loop;
end;

Jannet

Just a little less ugly than the GOTO followed by a NULL line, but it works. Thanks! – Aleppo

Answer 4

For future searches, in oracle 11g they added a continue statement, which can be used like this :

    SQL> BEGIN
  2     FOR i IN 1 .. 5 LOOP
  3        IF i IN (2,4) THEN
  4           CONTINUE;
  5        END IF;
  6        DBMS_OUTPUT.PUT_LINE('Reached on line ' || TO_CHAR(i));
  7     END LOOP;
  8  END;
  9  /
Reached on line 1
Reached on line 3
Reached on line 5

PL/SQL procedure successfully completed.

Immunoreaction

Answer 5

It's not available in 10g, however it's a new feature in 11G

Eyeful

Answer 6

Can you refactor the IFs into a function, returning at the appropriate point (early if necessary). Then the control flow will pick up in the loop at the right place.

Does that make sense?

Gall

Answer 7

Not exactly elegant, but simple:

DECLARE
   done  BOOLEAN;
BEGIN
   FOR i IN 1..50 LOOP
      IF done THEN
         NULL;
      ELSE
         <do loop stuff>;
      END IF;
   END LOOP; 
END;

Aretina

Answer 8

In Oracle there is a similar statement called EXIT that either exits a loop or a function/procedure (if there is no loop to exit from). You can add a WHEN to check for some condition.

You could rewrite the above example as follows:

DECLARE
   done  BOOLEAN;
BEGIN
    FOR i IN 1..50 LOOP
     EXIT WHEN done;
   END LOOP;
END;

This may not be enough if you want to exit from deep down some nested loops and logic, but is a lot clearer than a couple of GOTOs and NULLs.

Entomophilous

The EXIT solution wouldn't really work in this case, because it would terminate the entire loop, as opposed to just start at the next iteration as CONTINUE would do. I think I'll have to go with the GOTO solution, cheers everyone for your suggestions! – Trishatriskelion

Answer 9

This isn't exactly an answer to the question, but nevertheless worth noting:

The continue statement in PL/SQL and all other programming languages which use it the same way, can easily be misunderstood.

It would have been much wiser, clearer and more concise if the programming language developers had called the keyword skip instead.

For me, with a background of C, C++, Python, ... it has always been clear what `continue' means.

But without that historical background, you might end intepreting this code

for i in .. tab_xy.count loop
    CONTINUE WHEN some_condition(tab_xy(i));
    do_process(tab_xy(i));
end loop;

like this:

Loop through the records of the table tab_xy.

Continue if the record fulfills some_condition, otherwise ignore this record.

Do_process the record.

This interpretation is completely wrong, but if you imagine the PL/SQL code as a kind of cooking receipt and read it aloud, this can happen.

In fact it happened to a very experienced development co-worker just yesterday.

Labia

I totally agree with you. I always think that continue should be changed to skip. – Granary