Oracle PL/SQL Tutorial/PL SQL Statements/Loop — различия между версиями
Admin (обсуждение | вклад) м (1 версия) |
|
(нет различий)
| |
Версия 13:45, 26 мая 2010
Содержание
- 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
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>
Count up by hundreds until we get an error
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>
Creating a REPEAT...UNTIL Loop
The Syntax for a Simulated REPEAT...UNTIL Loop
LOOP
<statements>
IF <condition is true>
EXIT;
END IF;
END LOOP;
Exit a LOOP
SQL>
SQL>
SQL> BEGIN
2 LOOP
3 NULL;
4 EXIT;
5 END LOOP;
6 END;
7 /
PL/SQL procedure successfully completed.
EXIT a LOOP for a certain condition
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.
Exiting from Nested Loops
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>
Fetch Cursor data in LOOP
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>
LOOP END LOOP
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>
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
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>
Nested loops: Loop inside of a Loop
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>
Simple Loops
A simple loop runs until you explicitly end the loop. The syntax for a simple loop is as follows:
LOOP
statements
END LOOP;
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.
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>
Use LOOP to insert data to a table
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>
Using Labels and EXIT Statements with Loops
You can use labels within loops to identify a loop.
The Syntax for Using Labels with Loops
<<label_name1>>
LOOP (FOR, WHILE, LOOP)
<<label_name2>>
LOOP (FOR, WHILE, LOOP)
...
END LOOP <<label_name2>>
END LOOP <<label_name1>>