Jun 21, 2010

CONTINUE Statement in PL/SQL (Oracle 11g):

We -as developers- know that most programming languages have CONTINUE statement to skip to next iteration of the loop, but unfortunately we didn't have in Oracle, so we used to do workaround code to imitate CONTINUE functionality. Say we have the following loop code:
SET serveroutput ON
DECLARE
BEGIN
  FOR i IN 1 .. 10
  LOOP
    DBMS_OUTPUT.put_line ('The current number is ' || i);
  END LOOP;
END;

And if we don't want to print for 4,6 and 8 numbers we would do some of the following workarounds:
Use label:

SET serveroutput ON
DECLARE
BEGIN
  FOR i IN 1 .. 10
  LOOP
    IF (I IN (4,6,8)) THEN
      GOTO FOO ;
    END IF;
    DBMS_OUTPUT.PUT_LINE ('The current number is ' || I);
    <<FOO>> NULL;
  END LOOP;
END;

Or use IF ELSE:
SET serveroutput ON
DECLARE
BEGIN
  FOR i IN 1 .. 10
  LOOP
    IF (I NOT IN (4,6,8)) THEN
      DBMS_OUTPUT.PUT_LINE ('The current number is ' || I);
    ELSE
      NULL;
    END IF;
  END LOOP;
END;

etc...

The good news is we don't have to do previous code. In Oracle 11g a new feature is introduced to enable user to enable the developer to explicitly use CONTINUE statement:
SET serveroutput ON
DECLARE
BEGIN
  FOR i IN 1 .. 10
  LOOP
    CONTINUE WHEN i IN (4,6,8);
    DBMS_OUTPUT.put_line ('The current number is ' || i);
  END LOOP;
END;

Or write:
SET serveroutput ON
DECLARE
BEGIN
  FOR i IN 1 .. 10
  LOOP
    IF I IN (4,6,8) THEN
      CONTINUE;
    END IF;
    DBMS_OUTPUT.put_line ('The current number is ' || i);
  END LOOP;
END;


And viola....

Saad Nayef,

No comments:

Post a Comment