Oracle PL/SQL Tutorial/PL SQL Statements/EXIT
Содержание
EXIT a FOR LOOP
<source lang="sql">
SQL> SQL> set serveroutput on SQL> set echo on SQL> SQL> BEGIN
2 FOR v_loopcounter IN 1..20 LOOP
3 IF MOD(v_loopcounter,2) = 0 THEN
4 DBMS_OUTPUT.PUT_LINE("The AREA of the circle is " ||v_loopcounter*v_loopcounter * mypi);
5 END IF;
6 IF v_loopcounter = 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></source>
Exit a function
<source lang="sql">
SQL> set serveroutput on SQL> set echo on SQL> SQL> CREATE OR REPLACE function exitfunc(myString VARCHAR2)
2 RETURN NUMBER IS 3 v_current_position INTEGER := 1; 4 v_counter NUMBER := 0; 5 BEGIN 6 WHILE v_current_position <= LENGTH(myString) LOOP 7 IF SUBSTR(myString,v_current_position,1) != " " THEN 8 v_counter := v_counter + 1; 9 ELSE 10 NULL; 11 END IF; 12 v_current_position := v_current_position + 1; 13 EXIT WHEN SUBSTR(myString,v_current_position,1) = " "; 14 END LOOP; 15 RETURN v_counter ; 16 END exitfunc; 17 /
Function created. SQL> SQL> 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 4 PL/SQL procedure successfully completed. SQL></source>
EXIT a LOOP WHEN a certain condition
<source lang="sql">
SQL> set serveroutput on SQL> set echo on SQL> SQL> DECLARE
2 v_Radius NUMBER := 2;
3 BEGIN
4 LOOP
5 DBMS_OUTPUT.PUT_LINE("The AREA of the circle is " ||
6 v_RADIUS*v_RADIUS * mypi);
7 v_Radius := v_Radius + 2;
8 EXIT WHEN v_Radius > 10;
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.</source>
EXIT a WHILE LOOP
<source lang="sql">
SQL> SQL> set serveroutput on SQL> set echo on SQL> SQL> DECLARE
2 v_Radius NUMBER := 2;
3 BEGIN
4 WHILE TRUE LOOP
5 DBMS_OUTPUT.PUT_LINE("The Area is "||mypi * v_Radius * v_Radius);
6 IF v_Radius = 10 THEN
7 EXIT;
8 END IF;
9 v_Radius := v_Radius + 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> SQL></source>
EXIT statement with Label
<source lang="sql">
SQL> SQL> set serveroutput on SQL> set echo on 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 " ||
9 v_innerloopcounter);
10 EXIT outerloop WHEN v_innerloopcounter = 3;
11 END LOOP innerloop;
12 END LOOP outerloop;
13 END;
14 /
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></source>
Exit to a Label
<source lang="sql">
SQL> set serveroutput on 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 10 just_a_num := just_a_num + 1; 11 END LOOP; 12 END; 13 /
1 2 3 4 5 6 7 8 9 10 PL/SQL procedure successfully completed. SQL> SQL></source>
EXIT WHEN
<source lang="sql">
SQL> SQL> set serveroutput on SQL> set echo on SQL> SQL> DECLARE
2 v_Radius NUMBER := 2;
3 BEGIN
4 WHILE TRUE LOOP
5 DBMS_OUTPUT.PUT_LINE("The Area is " ||mypi * v_Radius * v_Radius);
6 EXIT WHEN v_RADIUS = 10;
7 v_Radius := v_Radius + 2 ;
8 END LOOP;
9 END;
10 /
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></source>
Using "EXIT WHEN" in a loop
<source lang="sql">
SQL> SQL> SQL> -- create demo table SQL> create table Employee(
2 ID VARCHAR2(4 BYTE) NOT NULL, 3 First_Name VARCHAR2(10 BYTE), 4 Last_Name VARCHAR2(10 BYTE), 5 Start_Date DATE, 6 End_Date DATE, 7 Salary Number(8,2), 8 City VARCHAR2(10 BYTE), 9 Description VARCHAR2(15 BYTE) 10 ) 11 /
Table created. SQL> SQL> -- prepare data SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values ("01","Jason", "Martin", to_date("19960725","YYYYMMDD"), to_date("20060725","YYYYMMDD"), 1234.56, "Toronto", "Programmer")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("02","Alison", "Mathews", to_date("19760321","YYYYMMDD"), to_date("19860221","YYYYMMDD"), 6661.78, "Vancouver","Tester")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("03","James", "Smith", to_date("19781212","YYYYMMDD"), to_date("19900315","YYYYMMDD"), 6544.78, "Vancouver","Tester")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("04","Celia", "Rice", to_date("19821024","YYYYMMDD"), to_date("19990421","YYYYMMDD"), 2344.78, "Vancouver","Manager")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("05","Robert", "Black", to_date("19840115","YYYYMMDD"), to_date("19980808","YYYYMMDD"), 2334.78, "Vancouver","Tester")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("06","Linda", "Green", to_date("19870730","YYYYMMDD"), to_date("19960104","YYYYMMDD"), 4322.78,"New York", "Tester")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("07","David", "Larry", to_date("19901231","YYYYMMDD"), to_date("19980212","YYYYMMDD"), 7897.78,"New York", "Manager")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("08","James", "Cat", to_date("19960917","YYYYMMDD"), to_date("20020415","YYYYMMDD"), 1232.78,"Vancouver", "Tester")
3 /
1 row created. SQL> SQL> SQL> SQL> -- display data in the table SQL> select * from Employee
2 /
ID FIRST_NAME LAST_NAME START_DAT END_DATE SALARY CITY DESCRIPTION
-------------------- -------------------- --------- --------- ---------- ---------- ---------------
01 Jason Martin 25-JUL-96 25-JUL-06 1234.56 Toronto Programmer 02 Alison Mathews 21-MAR-76 21-FEB-86 6661.78 Vancouver Tester 03 James Smith 12-DEC-78 15-MAR-90 6544.78 Vancouver Tester 04 Celia Rice 24-OCT-82 21-APR-99 2344.78 Vancouver Manager 05 Robert Black 15-JAN-84 08-AUG-98 2334.78 Vancouver Tester 06 Linda Green 30-JUL-87 04-JAN-96 4322.78 New York Tester 07 David Larry 31-DEC-90 12-FEB-98 7897.78 New York Manager 08 James Cat 17-SEP-96 15-APR-02 1232.78 Vancouver Tester 8 rows selected. SQL> SQL> SQL> SQL> SQL> DECLARE
2 v_Counter BINARY_INTEGER := 1; 3 BEGIN 4 LOOP 5 INSERT INTO employee (id) VALUES (v_Counter); 6 v_Counter := v_Counter + 1; 7 EXIT WHEN v_Counter > 50; 8 END LOOP; 9 END; 10 /
PL/SQL procedure successfully completed. SQL> SQL> select * from employee; ID FIRST_NAME LAST_NAME START_DAT END_DATE SALARY CITY DESCRIPTION
-------------------- -------------------- --------- --------- ---------- ---------- ---------------
01 Jason Martin 25-JUL-96 25-JUL-06 1234.56 Toronto Programmer 02 Alison Mathews 21-MAR-76 21-FEB-86 6661.78 Vancouver Tester 03 James Smith 12-DEC-78 15-MAR-90 6544.78 Vancouver Tester 04 Celia Rice 24-OCT-82 21-APR-99 2344.78 Vancouver Manager 05 Robert Black 15-JAN-84 08-AUG-98 2334.78 Vancouver Tester 06 Linda Green 30-JUL-87 04-JAN-96 4322.78 New York Tester 07 David Larry 31-DEC-90 12-FEB-98 7897.78 New York Manager 08 James Cat 17-SEP-96 15-APR-02 1232.78 Vancouver Tester 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 58 rows selected. SQL> SQL> SQL> SQL> -- clean the table SQL> drop table Employee
2 /
Table dropped.</source>
