'CONTINUE' keyword in Oracle 10g PL/SQL

问题:

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.

回答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;

回答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;

回答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;

回答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.

回答5:

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

回答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?

回答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;

回答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.