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>