CONTINUE Statement

The CONTINUE statement jumps out of the current loop interation and starts the next one. It can be used on its own, or as part of a CONTINUE WHEN statement, as shown below.

SET SERVEROUTPUT ON
DECLARE
  l_number    NUMBER := 0;
BEGIN
  FOR i IN 1 .. 100 LOOP
    CONTINUE WHEN MOD(i,2) = 0;

    -- Do something here!
    l_number := l_number + 1;
  END LOOP;

  DBMS_OUTPUT.put_line('CONTINUE WHEN : ' || l_number);

  l_number := 0;

  FOR i IN 1 .. 100 LOOP
    IF MOD(i,2) = 0 THEN
      CONTINUE;
    END IF;

    -- Do something here!
    l_number := l_number + 1;
  END LOOP;

  DBMS_OUTPUT.put_line('IF .. CONTINUE: ' || l_number);
END;
/
CONTINUE WHEN : 50
IF .. CONTINUE: 50

PL/SQL procedure successfully completed.

SQL>
     

This type of processing has always been possible using IF statements either on their own or with exceptions or GOTO statements, but the CONTINUE statement is neater and brings PL/SQL in line with other langauges. The following examples show the type of code necessary to perform the same task before the CONTINUE statement was added to PL/SQL.

SET SERVEROUTPUT ON
DECLARE
  ex_continue EXCEPTION;
  l_number    NUMBER := 0;
BEGIN
  FOR i IN 1 .. 100 LOOP
    BEGIN
      IF MOD(i,2) != 0 THEN
        RAISE ex_continue;
      END IF;

      -- Do something here!
      l_number := l_number + 1;
    EXCEPTION
      WHEN ex_continue THEN
        NULL;
    END;
  END LOOP;

  DBMS_OUTPUT.put_line('EXCEPTION: ' || l_number);

  l_number := 0;

  FOR i IN 1 .. 100 LOOP
    IF MOD(i,2) != 0 THEN

      -- Do something here!
      l_number := l_number + 1;

    END IF;
  END LOOP;

  DBMS_OUTPUT.put_line('IF       : ' || l_number);

  l_number := 0;

  FOR i IN 1 .. 100 LOOP
    IF MOD(i,2) = 0 THEN
      GOTO label_continue;
    END IF;

    -- Do something here!
    l_number := l_number + 1;

    << label_continue >>
    NULL;
  END LOOP;

  DBMS_OUTPUT.put_line('GOTO     : ' || l_number);
END;
/
EXCEPTION: 50
IF       : 50
GOTO     : 50

PL/SQL procedure successfully completed.

SQL>

Sequences in PL/SQL Expressions

The NEXTVAL and CURRVAL sequence pseudocolumns can now be accessed in PL/SQL expressions as well as queries. This makes the code look simpler, and the documentation suggests it improves performance. The following example compares the speed of the original and new methods of accessing these sequence values.

CREATE SEQUENCE test1_seq START WITH 1000000;

SET SERVEROUTPUT ON
DECLARE
  l_start NUMBER;
  l_loops NUMBER := 100000;
  l_value NUMBER;
BEGIN

  l_start := DBMS_UTILITY.get_time;

  FOR i IN 1 .. l_loops LOOP
    SELECT test1_seq.NEXTVAL
    INTO   l_value
    FROM dual;
  END LOOP;

  DBMS_OUTPUT.put_line('NEXTVAL SELECT=' || (DBMS_UTILITY.get_time - l_start) || ' hsecs');

  l_start := DBMS_UTILITY.get_time;

  FOR i IN 1 .. l_loops LOOP
    l_value := test1_seq.NEXTVAL;
  END LOOP;

  DBMS_OUTPUT.put_line('NEXTVAL Expression=' || (DBMS_UTILITY.get_time - l_start) || ' hsecs');

  l_start := DBMS_UTILITY.get_time;

  FOR i IN 1 .. l_loops LOOP
    SELECT test1_seq.CURRVAL
    INTO   l_value
    FROM dual;
  END LOOP;

  DBMS_OUTPUT.put_line('CURRVAL SELECT=' || (DBMS_UTILITY.get_time - l_start) || ' hsecs');

  l_start := DBMS_UTILITY.get_time;

  FOR i IN 1 .. l_loops LOOP
    l_value := test1_seq.CURRVAL;
  END LOOP;

  DBMS_OUTPUT.put_line('CURRVAL Expression=' || (DBMS_UTILITY.get_time - l_start) || ' hsecs');

END;
/
NEXTVAL SELECT=2196 hsecs
NEXTVAL Expression=2203 hsecs
CURRVAL SELECT=1007 hsecs
CURRVAL Expression=1003 hsecs

PL/SQL procedure successfully completed.

SQL>

You can see that as far as elapsed time is concerned, there is little difference between the two methods.

Dynamic SQL Enhancements

Native dynamic SQL and the DBMS_SQL package now support dynamic SQL statements larger than 32 KB. The EXECUTE IMMEDIATE statement, OPEN-FOR statement and DBMS_SQL.PARSE procedure all accept SQL statements in the form of CLOBs.

The DBMS_SQL.TO_REFCURSOR function converts a DBMS_SQL cursor ID into a REF CURSOR.

SET SERVEROUTPUT ON
DECLARE
  l_cursor       NUMBER;
  l_return       NUMBER;

  l_ref_cursor   SYS_REFCURSOR;
  TYPE t_emp_tab IS TABLE OF emp%ROWTYPE;
  l_emp_tab      t_emp_tab;
BEGIN
  l_cursor := DBMS_SQL.open_cursor;

  DBMS_SQL.parse(l_cursor, 'SELECT * FROM emp', DBMS_SQL.NATIVE);

  l_return := DBMS_SQL.EXECUTE(l_cursor);

  -- Connvert from DBMS_SQL to a REF CURSOR.
  l_ref_cursor := DBMS_SQL.to_refcursor(l_cursor);
  FETCH l_ref_cursor BULK COLLECT INTO l_emp_tab;

  DBMS_OUTPUT.put_line('Employee Count: ' || l_emp_tab.count);

  CLOSE l_ref_cursor;
END;
/
Employee Count: 14

PL/SQL procedure successfully completed.

SQL>

The DBMS_SQL.TO_CURSOR_NUMBER function converts a REF CURSOR into a DBMS_SQL cursor ID.

SET SERVEROUTPUT ON
DECLARE
  l_ref_cursor   SYS_REFCURSOR;

  l_cursor       NUMBER;
  l_count        NUMBER := 0;
BEGIN
  OPEN l_ref_cursor FOR 'SELECT * FROM emp';

  l_cursor := DBMS_SQL.to_cursor_number(l_ref_cursor);

  WHILE DBMS_SQL.fetch_rows(l_cursor) > 0 LOOP
    l_count := l_count + 1;
  END LOOP;

  DBMS_OUTPUT.put_line('Employee Count: ' || l_count);

  DBMS_SQL.close_cursor(l_cursor);
END;
/
Employee Count: 14

PL/SQL procedure successfully completed.

SQL>

In addition, the DBMS_SQL package now supports all datatypes supported by native dynamic SQL.