Oracle PL/SQL/PL SQL/EXIT

Exit loop since last index value is read.

    
SQL> DECLARE
  2    current VARCHAR2(9 CHAR);
  3    element INTEGER;
  4
  5    TYPE months_varray IS VARRAY(12) OF STRING(9 CHAR);
  6    TYPE calendar_table IS TABLE OF VARCHAR2(9 CHAR) INDEX BY VARCHAR2(9 CHAR);
  7
  8    month MONTHS_VARRAY := months_varray("January","February","March","April","May","June","July","August","September","October","November","December");
  9
 10    calendar CALENDAR_TABLE;
 11  BEGIN
 12    IF calendar.COUNT = 0 THEN
 13      FOR i IN month.FIRST..month.LAST LOOP
 14        calendar(month(i)) := TO_CHAR(i);
 15        DBMS_OUTPUT.PUT_LINE("Index ["||month(i)||"] is ["||i||"]");
 16      END LOOP;
 17
 18      FOR i IN 1..calendar.COUNT LOOP
 19        IF i = 1 THEN
 20          current := calendar.FIRST;
 21          element := calendar(current);
 22        ELSE
 23          IF calendar.NEXT(current) IS NOT NULL THEN
 24            current := calendar.NEXT(current);
 25            element := calendar(current);
 26          ELSE
 27            EXIT;
 28          END IF;
 29        END IF;
 30
 31        DBMS_OUTPUT.PUT_LINE("Index ["||current||"] is ["||element||"]");
 32      END LOOP;
 33    END IF;
 34  END;
 35  /
Index [January] is [1]
Index [February] is [2]
Index [March] is [3]
Index [April] is [4]
Index [May] is [5]
Index [June] is [6]
Index [July] is [7]
Index [August] is [8]
Index [September] is [9]
Index [October] is [10]
Index [November] is [11]
Index [December] is [12]
Index [April] is [4]
Index [August] is [8]
Index [December] is [12]
Index [February] is [2]
Index [January] is [1]
Index [July] is [7]
Index [June] is [6]
Index [March] is [3]
Index [May] is [5]
Index [November] is [11]
Index [October] is [10]
Index [September] is [9]
PL/SQL procedure successfully completed.

Exit outer loop with "EXIT LabelName When" statement

  
SQL>
SQL>   BEGIN
  2          <<outerloop>>
  3          FOR v_outerloopcounter IN 1..2 LOOP
  4               <<innerloop>>
  5               FOR v_innerloopcounter IN 1..4 LOOP
  6                    DBMS_OUTPUT.PUT_LINE("Outer Loop counter is "
  7                         || v_outerloopcounter ||
  8                         " Inner Loop counter is " || v_innerloopcounter);
  9                         EXIT outerloop WHEN v_innerloopcounter = 3;
 10              END LOOP innerloop;
 11         END LOOP outerloop;
 12    END;
 13    /
Outer Loop counter is 1 Inner Loop counter is 1
Outer Loop counter is 1 Inner Loop counter is 2
Outer Loop counter is 1 Inner Loop counter is 3
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL> --

Exit to a label

  
SQL>
SQL> DECLARE
  2          just_a_num NUMBER := 1;
  3  BEGIN
  4          <<just_a_loop>>
  5          LOOP
  6                  dbms_output.put_line(just_a_num);
  7          EXIT just_a_loop
  8          WHEN (just_a_num >= 10);
  9                  just_a_num := just_a_num + 1;
 10          END LOOP;
 11  END;
 12  /
1
2
3
4
5
6
7
8
9
10
PL/SQL procedure successfully completed.
SQL>

EXIT WHEN clause.

  
SQL>
SQL> CREATE TABLE MyTable (
  2    num_col    NUMBER,
  3    char_col   VARCHAR2(60)
  4    );
Table created.
SQL>
SQL>
SQL> DECLARE
  2    v_Counter BINARY_INTEGER := 1;
  3  BEGIN
  4    LOOP
  5      INSERT INTO MyTable
  6        VALUES (v_Counter, "Loop index");
  7      v_Counter := v_Counter + 1;
  8      EXIT WHEN v_Counter > 50;
  9    END LOOP;
 10  END;
 11  /
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL> drop table MyTable;
Table dropped.
SQL>

Impact of EXIT in a function

 
SQL>
SQL> -- Impact of EXIT in a function.
SQL>
SQL> CREATE OR REPLACE FUNCTION exitfunc(p_pass_string VARCHAR2)
  2        RETURN NUMBER IS
  3             len Number := 1;
  4  BEGIN
  5        WHILE len <= LENGTH(p_PASS_STRING) LOOP
  6             len := len + 1;
  7             EXIT WHEN SUBSTR(p_PASS_STRING,len,1) = " ";
  8        END LOOP;
  9        RETURN len ;
 10  END exitfunc;
 11  /
Function created.
SQL>
SQL>
SQL> --  Executing EXIT within a function.
SQL> DECLARE
  2       v_MYTEXT VARCHAR2(20) := "THIS IS A TEST";
  3  BEGIN
  4       DBMS_OUTPUT.PUT_LINE("Total count is " || exitfunc(v_MYTEXT));
  5  END;
  6  /
Total count is 5
PL/SQL procedure successfully completed.
SQL>

Unconstrained loop: exit

 
SQL>
SQL> -- unconstrained loop: exit
SQL>
SQL>  declare
  2      l_loops number := 0;
  3    begin
  4      dbms_output.put_line("Before my loop");
  5
  6      loop
  7        if l_loops > 4 then
  8          exit;
  9        end if;
 10        dbms_output.put_line("Looped " || l_loops || " times");
 11        l_loops := l_loops + 1;
 12      end loop;
 13
 14      dbms_output.put_line("After my loop");
 15    end;
 16    /
Before my loop
Looped 0 times
Looped 1 times
Looped 2 times
Looped 3 times
Looped 4 times
After my loop
PL/SQL procedure successfully completed.
SQL>

Using EXIT with a FOR loop

 
SQL>
SQL> -- Using EXIT with a FOR loop.
SQL> BEGIN
  2        FOR i IN 1..20 LOOP
  3             IF MOD(i,2) = 0 THEN
  4                  DBMS_OUTPUT.PUT_LINE("The AREA of the circle is " || i*i * mypi);
  5             END IF;
  6             IF i = 10 THEN
  7                  EXIT;
  8             END IF;
  9        END LOOP;
 10   END;
 11  /
The AREA of the circle is 12.56
The AREA of the circle is 50.24
The AREA of the circle is 113.04
The AREA of the circle is 200.96
The AREA of the circle is 314
PL/SQL procedure successfully completed.
SQL>
SQL>

Using EXIT with a simple LOOP

 
SQL>
SQL> -- Using EXIT with a simple LOOP.
SQL> BEGIN
  2       LOOP
  3            NULL;
  4            EXIT;
  5       END LOOP;
  6  END;
  7  /
PL/SQL procedure successfully completed.
SQL>

Using EXIT with a WHILE loop

 
SQL>
SQL> -- Using EXIT with a WHILE loop.
SQL> DECLARE
  2       r NUMBER := 2;
  3  BEGIN
  4       WHILE TRUE LOOP
  5            DBMS_OUTPUT.PUT_LINE("The Area is " || mypi * r * r);
  6            IF r = 10 THEN
  7                EXIT;
  8            END IF;
  9            r := r + 2 ;
 10       END LOOP;
 11  END;
 12  /
The Area is 12.56
The Area is 50.24
The Area is 113.04
The Area is 200.96
The Area is 314
PL/SQL procedure successfully completed.
SQL>