Using CONTINUE-WHEN for easy-to-read loop logic

View All Scripts Login to Run Script

Script Name Using CONTINUE-WHEN for easy-to-read loop logic
Description The CONTINUE WHEN statement exits the current iteration of a loop when the condition in its WHEN clause is true, and transfers control to the next iteration of either the current loop or an enclosing labeled loop. Each time control reaches the CONTINUE WHEN statement, the condition in its WHEN clause is evaluated. If the condition is not true, the CONTINUE WHEN statement does nothing.
Area PL/SQL General
Contributor Steven Feuerstein
Created Monday September 11, 2017

Statement 1

CREATE TABLE qz_employees
(
   employee_id   INTEGER,
   last_name     VARCHAR2 (100),
   salary        NUMBER
)

Table created.

Statement 2

BEGIN
   INSERT INTO qz_employees
        VALUES (1000, 'Frogbit', 1000);

   INSERT INTO qz_employees
        VALUES (2000, 'Kudzu', 5000);

   INSERT INTO qz_employees
        VALUES (3000, 'Loosestrife', 10000);

   COMMIT;
END;

1 row(s) inserted.

Statement 3

CREATE OR REPLACE TYPE qz_numbers_t IS TABLE OF NUMBER

Type created.

Statement 4

This approach makes extensive use of GOTOs to avoid executing loop body code based on conditional logic. This is definitely not the best way to get the job done. Use CONTINUE instead.

Using GOTOs

CREATE OR REPLACE PROCEDURE qz_proc ( 
   numbers_in   IN qz_numbers_t) 
IS 
BEGIN 
   FOR indx IN 1 .. numbers_in.COUNT 
   LOOP 
      IF numbers_in (indx) < 0 
      THEN 
         GOTO end_loop; 
      END IF; 
 
      UPDATE qz_employees 
         SET salary = numbers_in (indx) 
       WHERE employee_id = TRUNC (numbers_in (indx)); 
 
      IF SQL%ROWCOUNT > 0 
      THEN 
         GOTO end_loop; 
      END IF; 
 
      DBMS_OUTPUT.put_line ( 
         'No row updated for ' || numbers_in (indx)); 
 
     <<end_loop>> 
      NULL; 
   END LOOP; 
END;

Procedure created.

Statement 5

DECLARE
   l_ids   qz_numbers_t
              := qz_numbers_t (-2000, 1000.50, 3500);
BEGIN
   qz_proc (l_ids);
END;

No row updated for 3500

Statement 6

This approach uses the CONTINUE-WHEN statement to ensure execution of the correct portions of the loop body with the simplest structure possible.

Using CONTINUE-WHEN

CREATE OR REPLACE PROCEDURE qz_proc ( 
   numbers_in   IN qz_numbers_t) 
IS 
BEGIN 
   FOR indx IN 1 .. numbers_in.COUNT 
   LOOP 
      CONTINUE WHEN numbers_in (indx) < 0; 
 
      UPDATE qz_employees 
         SET salary = numbers_in (indx) 
       WHERE employee_id = TRUNC (numbers_in (indx)); 
 
      CONTINUE WHEN SQL%ROWCOUNT > 0; 
 
      DBMS_OUTPUT.put_line ( 
         'No row updated for ' || numbers_in (indx)); 
   END LOOP; 
END;

Procedure created.

Statement 7

DECLARE
   l_ids   qz_numbers_t
              := qz_numbers_t (-2000, 1000.50, 3500);
BEGIN
   qz_proc (l_ids);
END;

No row updated for 3500

Statement 8

This approach uses GOTOs in place of a loop. Gets the job done, but it is uuuuuugly. Use CONTINUE instead.

Using GOTOs and No Loop

CREATE OR REPLACE PROCEDURE qz_proc ( 
   numbers_in   IN qz_numbers_t) 
IS 
   l_index   PLS_INTEGER := numbers_in.FIRST - 1; 
BEGIN 
  <<check_numbers>> 
   l_index := numbers_in.NEXT (l_index); 
 
   IF l_index <= numbers_in.LAST 
   THEN 
      IF numbers_in (l_index) < 0 
      THEN 
         GOTO check_numbers; 
      END IF; 
 
      UPDATE qz_employees 
         SET salary = numbers_in (l_index) 
       WHERE employee_id = TRUNC (numbers_in (l_index)); 
 
      IF SQL%ROWCOUNT > 0 
      THEN 
         GOTO check_numbers; 
      END IF; 
 
      DBMS_OUTPUT.put_line ( 
         'No row updated for ' || numbers_in (l_index)); 
 
      GOTO check_numbers; 
   END IF; 
END;

Procedure created.

Statement 9

DECLARE
   l_ids   qz_numbers_t
              := qz_numbers_t (-2000, 1000.50, 3500);
BEGIN
   qz_proc (l_ids);
END;

No row updated for 3500

Statement 10

This approach uses an exception in order to skip over parts of the loop body that should not execute. This is definitely not the best way to get the job done. Only raise exceptions when there are actual errors. In this case, use CONTINUE instead.

Using Exceptions

CREATE OR REPLACE PROCEDURE qz_proc ( 
   numbers_in   IN qz_numbers_t) 
IS 
   l_index           PLS_INTEGER := numbers_in.FIRST; 
   e_no_more_to_do   EXCEPTION; 
BEGIN 
   FOR indx IN 1 .. numbers_in.COUNT 
   LOOP 
      BEGIN 
         IF numbers_in (indx) < 0 
         THEN 
            RAISE e_no_more_to_do; 
         END IF; 
 
         UPDATE qz_employees 
            SET salary = numbers_in (indx) 
          WHERE employee_id = TRUNC (numbers_in (indx)); 
 
         IF SQL%ROWCOUNT > 0 
         THEN 
            RAISE e_no_more_to_do; 
         END IF; 
 
         DBMS_OUTPUT.put_line ( 
            'No row updated for ' || numbers_in (indx)); 
      EXCEPTION 
         WHEN e_no_more_to_do 
         THEN 
            NULL; 
      END; 
   END LOOP; 
END;

Procedure created.

Statement 11

DECLARE
   l_ids   qz_numbers_t
              := qz_numbers_t (-2000, 1000.50, 3500);
BEGIN
   qz_proc (l_ids);
END;

No row updated for 3500

Statement 12
```
DROP TABLE qz_employees
```
```
Table dropped.
```

Statement 13

DROP PROCEDURE qz_proc

Procedure dropped.

Statement 14

DROP TYPE qz_numbers_t FORCE

Type dropped.

Additional Information

Database on OTN SQL and PL/SQL Discussion forums
Oracle Database
Download Oracle Database