Oracle PL/SQL Tutorial/PL SQL Statements/Loop
Содержание
- 1 A SQL Replacement for Regular Loops
- 2 Count up by hundreds until we get an error
- 3 Creating a REPEAT...UNTIL Loop
- 4 Exit a LOOP
- 5 EXIT a LOOP for a certain condition
- 6 Exiting from Nested Loops
- 7 Fetch Cursor data in LOOP
- 8 LOOP END LOOP
- 9 Loops
- 10 Named loop block
- 11 Nested loops: Loop inside of a Loop
- 12 Simple Loops
- 13 The EXIT WHEN statement can appear anywhere in the loop code.
- 14 Use LOOP to insert data to a table
- 15 Using Labels and EXIT Statements with Loops
A SQL Replacement for Regular Loops
<source lang="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> create or replace function f_checkDeptLimit_yn (i_deptNo VARCHAR,i_limit_nr NUMBER, i_income_nr NUMBER)
2 return VARCHAR2 3 is 4 v_counter_nr NUMBER:=0; 5 v_error_yn VARCHAR2(1):="N"; 6 begin 7 select count(*) 8 into v_counter_nr 9 from employee 10 where id = i_deptNo 11 and nvl(salary,0)<i_income_nr 12 and rownum < i_limit_nr+1 ; 13 14 if v_counter_nr=i_limit_nr then 15 v_error_yn:="Y"; 16 end if; 17 return v_error_yn; 18 end; 19 /
Function created. SQL> SQL> SQL> select f_limit_yn("01", 1000, 2000) from dual; F_LIMIT_YN("01",1000,2000)
N SQL> SQL> SQL> SQL> SQL> -- clean the table SQL> drop table Employee
2 /
Table dropped. SQL></source>
Count up by hundreds until we get an error
<source lang="sql">
SQL> SQL> SET ECHO ON SQL> SET SERVEROUTPUT ON SQL> SQL> SQL> DECLARE
2 hundreds_counter NUMBER(1,-2);
3 BEGIN
4 hundreds_counter := 100;
5 LOOP
6 DBMS_OUTPUT.PUT_LINE(hundreds_counter);
7 hundreds_counter := hundreds_counter + 100;
8 END LOOP;
9 EXCEPTION
10 WHEN OTHERS THEN
11 DBMS_OUTPUT.PUT_LINE("Done.");
12 END;
13 /
100 200 300 400 500 600 700 800 900 Done. PL/SQL procedure successfully completed. SQL> SQL></source>
Creating a REPEAT...UNTIL Loop
The Syntax for a Simulated REPEAT...UNTIL Loop
<source lang="sql">
LOOP
<statements>
IF <condition is true>
EXIT;
END IF;
END LOOP;</source>
Exit a LOOP
<source lang="sql">
SQL> SQL> SQL> BEGIN
2 LOOP 3 NULL; 4 EXIT; 5 END LOOP; 6 END; 7 /
PL/SQL procedure successfully completed.</source>
EXIT a LOOP for 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>
Exiting from Nested Loops
<source lang="sql">
SQL> SQL> declare
2 v_ind NUMBER; 3 v_current NUMBER; 4 v_max_printed NUMBER :=10; 5 v_printed NUMBER:=0; 6 begin 7 v_current:=0; -- should not be null! 8 <<Main>> 9 loop 10 v_ind:=0; -- reset each time 11 <<Inner>> 12 loop 13 v_ind:=v_ind+1; 14 DBMS_OUTPUT.put_line(v_current); 15 v_printed:=v_printed+1; 16 exit Main when v_printed = v_max_printed; 17 exit when v_ind=4; 18 end loop Inner; 19 v_current:=v_current+5; 20 exit when v_current=25; 21 end loop Main; 22 end; 23 /
0 0 0 0 5 5 5 5 10 10 PL/SQL procedure successfully completed. SQL> SQL></source>
Fetch Cursor data in LOOP
<source lang="sql">
SQL> SQL> SQL> SQL> -- create demo table SQL> create table Employee(
2 ID VARCHAR2(4 BYTE) NOT NULL primary key, 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_FirstName VARCHAR2(20); 3 v_LastName VARCHAR2(20); 4 5 CURSOR c_Students IS 6 SELECT first_name, last_name FROM employee; 7 BEGIN 8 -- Begin cursor processing. 9 OPEN c_Students; 10 LOOP 11 -- Retreive one row. 12 FETCH c_Students INTO v_FirstName, v_LastName; 13 EXIT WHEN c_Students%NOTFOUND; 14 END LOOP; 15 CLOSE c_Students; 16 END; 17 /
PL/SQL procedure successfully completed. SQL> SQL> SQL> SQL> -- clean the table SQL> drop table Employee
2 /
Table dropped. SQL></source>
LOOP END LOOP
<source lang="sql">
SQL> SQL> SET ECHO ON SQL> SET SERVEROUTPUT ON SQL> SQL> SQL> DECLARE
2 hundreds_counter NUMBER(1,-2);
3 BEGIN
4 hundreds_counter := 100;
5 LOOP
6 DBMS_OUTPUT.PUT_LINE(hundreds_counter);
7 hundreds_counter := hundreds_counter + 100;
8 END LOOP;
9 EXCEPTION
10 WHEN OTHERS THEN
11 DBMS_OUTPUT.PUT_LINE("Done.");
12 END;
13 /
100 200 300 400 500 600 700 800 900 Done. PL/SQL procedure successfully completed. SQL></source>
Loops
You may use a loop to run one or more statements multiple times.
There are three types of loops in PL/SQL:
- Simple loops Run until you explicitly end the loop
- WHILE loops Run until a specified condition occurs
- FOR loops Run a predetermined number of times
22. 3. Loop 22. 3. 1. Loops 22. 3. 2. <A href="/Tutorial/Oracle/0440__PL-SQL-Statements/SimpleLoops.htm">Simple Loops</a> 22. 3. 3. <A href="/Tutorial/Oracle/0440__PL-SQL-Statements/LOOPENDLOOP.htm">LOOP END LOOP</a> 22. 3. 4. <A href="/Tutorial/Oracle/0440__PL-SQL-Statements/Countupbyhundredsuntilwegetanerror.htm">Count up by hundreds until we get an error</a> 22. 3. 5. <A href="/Tutorial/Oracle/0440__PL-SQL-Statements/TheEXITWHENstatementcanappearanywhereintheloopcode.htm">The EXIT WHEN statement can appear anywhere in the loop code.</a> 22. 3. 6. <A href="/Tutorial/Oracle/0440__PL-SQL-Statements/ExitaLOOP.htm">Exit a LOOP</a> 22. 3. 7. <A href="/Tutorial/Oracle/0440__PL-SQL-Statements/EXITaLOOPforacertaincondition.htm">EXIT a LOOP for a certain condition</a> 22. 3. 8. <A href="/Tutorial/Oracle/0440__PL-SQL-Statements/NestedloopsLoopinsideofaLoop.htm">Nested loops: Loop inside of a Loop</a> 22. 3. 9. <A href="/Tutorial/Oracle/0440__PL-SQL-Statements/ExitingfromNestedLoops.htm">Exiting from Nested Loops</a> 22. 3. 10. <A href="/Tutorial/Oracle/0440__PL-SQL-Statements/UsingLabelsandEXITStatementswithLoops.htm">Using Labels and EXIT Statements with Loops</a> 22. 3. 11. <A href="/Tutorial/Oracle/0440__PL-SQL-Statements/CreatingaREPEATUNTILLoop.htm">Creating a REPEAT...UNTIL Loop</a> 22. 3. 12. <A href="/Tutorial/Oracle/0440__PL-SQL-Statements/UseLOOPtoinsertdatatoatable.htm">Use LOOP to insert data to a table</a> 22. 3. 13. <A href="/Tutorial/Oracle/0440__PL-SQL-Statements/FetchCursordatainLOOP.htm">Fetch Cursor data in LOOP</a> 22. 3. 14. <A href="/Tutorial/Oracle/0440__PL-SQL-Statements/ASQLReplacementforRegularLoops.htm">A SQL Replacement for Regular Loops</a> 22. 3. 15. <A href="/Tutorial/Oracle/0440__PL-SQL-Statements/Namedloopblock.htm">Named loop block</a>
Named loop block
<source lang="sql">
SQL> SQL> set serveroutput on size 500000 SQL> SQL> DECLARE
2
3 year_number PLS_INTEGER := 1992;
4
5 BEGIN
6
7 <<year_loop>>
8 WHILE year_number <= 1995
9 LOOP
10
11 dbms_output.put_line("year = "||year_number);
12
13 <<month_loop>>
14 FOR month_number IN 1 .. 12
15 LOOP
16 dbms_output.put_line("...and month = "||month_number);
17
18 END LOOP month_loop;
19
20 year_number := year_number + 2;
21
22 END LOOP year_loop;
23
24 END;
25 /
year = 1992 ...and month = 1 ...and month = 2 ...and month = 3 ...and month = 4 ...and month = 5 ...and month = 6 ...and month = 7 ...and month = 8 ...and month = 9 ...and month = 10 ...and month = 11 ...and month = 12 year = 1994 ...and month = 1 ...and month = 2 ...and month = 3 ...and month = 4 ...and month = 5 ...and month = 6 ...and month = 7 ...and month = 8 ...and month = 9 ...and month = 10 ...and month = 11 ...and month = 12 PL/SQL procedure successfully completed. SQL></source>
Nested loops: Loop inside of a Loop
<source lang="sql">
SQL> SQL> declare
2 v_ind NUMBER; 3 v_current NUMBER; 4 begin 5 v_current:=0; -- should not be null! 6 loop 7 v_ind:=0; -- reset each time 8 loop 9 v_ind:=v_ind+1; 10 DBMS_OUTPUT.put_line(v_current); 11 exit when v_ind=4; 12 end loop; 13 v_current:=v_current+5; 14 exit when v_current=25; 15 end loop; 16 end; 17 /
0 0 0 0 5 5 5 5 10 10 10 10 15 15 15 15 20 20 20 20 PL/SQL procedure successfully completed. SQL> SQL></source>
Simple Loops
A simple loop runs until you explicitly end the loop. The syntax for a simple loop is as follows:
<source lang="sql">
LOOP
statements
END LOOP;</source>
To end the loop, you use either an EXIT or EXIT WHEN statement.
The EXIT statement ends a loop immediately.
EXIT WHEN statement ends a loop when a specified condition occurs.
The EXIT WHEN statement can appear anywhere in the loop code.
<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>
Use LOOP to insert data to a table
<source lang="sql">
SQL> SQL> SQL> SQL> -- create demo table SQL> create table Employee(
2 ID VARCHAR2(4 BYTE) NOT NULL primary key, 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> SQL> -- display data in the table SQL> select * from Employee
2 /
no rows selected SQL> SQL> SQL> SQL> SQL> BEGIN
2 FOR v_LoopCounter IN 1..50 LOOP 3 INSERT INTO employee (id) 4 VALUES (v_LoopCounter); 5 END LOOP; 6 END; 7 /
PL/SQL procedure successfully completed. SQL> SQL> select * from employee; ID FIRST_NAME LAST_NAME START_DAT END_DATE SALARY CITY DESCRIPTION
-------------------- -------------------- --------- --------- ---------- ---------- ---------------
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 50 rows selected. SQL> SQL> SQL> -- clean the table SQL> drop table Employee
2 /
Table dropped. SQL> SQL></source>
Using Labels and EXIT Statements with Loops
You can use labels within loops to identify a loop.
The Syntax for Using Labels with Loops
<source lang="sql">
<<label_name1>>
LOOP (FOR, WHILE, LOOP)
<<label_name2>>
LOOP (FOR, WHILE, LOOP)
...
END LOOP <<label_name2>>
END LOOP <<label_name1>></source>
