Oracle PL/SQL Tutorial/PL SQL Statements/For LOOP
Содержание
- 1 A complete example if uisng the cursor variable using a cursor FOR LOOP
- 2 Drop any objects to make script re-runnable: for in
- 3 EXIT a FOR LOOP with exit command
- 4 EXPLAIN PLAN FOR select statement
- 5 FOR Loop Ranges
- 6 FOR Loops
- 7 FOR Loop Scoping Rules
- 8 For loop variable in a range of numbers
- 9 FOR LOOP with loop counter
- 10 FOR year_number IN 1800..1995: define variable for a range type
- 11 LOOP with Labels
- 12 Nested FOR loop counter
- 13 Nested FOR LOOP with Labels
- 14 Nested IF statement in For loop
- 15 Nested three level LOOP
- 16 Reversed FOR LOOP
- 17 Reversed loop
- 18 The upper or lower bounds of the FOR loop can be defined as variables or functions.
- 19 Use FOR LOOP to loop through dates
- 20 variable-delimitted loop
A complete example if uisng the cursor variable using a cursor FOR LOOP
SQL>
SQL>
SQL> create table product(
2 product_id number(4) not null,
3 product_description varchar2(20) not null
4 );
Table created.
SQL>
SQL> insert into product values (1,"Java");
1 row created.
SQL> insert into product values (2,"Oracle");
1 row created.
SQL> insert into product values (3,"C#");
1 row created.
SQL> insert into product values (4,"Javascript");
1 row created.
SQL> insert into product values (5,"Python");
1 row created.
SQL>
SQL>
SQL> create table company(
2 product_id number(4) not null,
3 company_id NUMBER(8) not null,
4 company_short_name varchar2(30) not null,
5 company_long_name varchar2(60)
6 );
Table created.
SQL> insert into company values(1,1001,"A Inc.","Long Name A Inc.");
1 row created.
SQL> insert into company values(1,1002,"B Inc.","Long Name B Inc.");
1 row created.
SQL> insert into company values(1,1003,"C Inc.","Long Name C Inc.");
1 row created.
SQL> insert into company values(2,1004,"D Inc.","Long Name D Inc.");
1 row created.
SQL> insert into company values(2,1005,"E Inc.","Long Name E Inc.");
1 row created.
SQL> insert into company values(2,1006,"F Inc.","Long Name F Inc.");
1 row created.
SQL>
SQL>
SQL>
SQL> DECLARE
2 CURSOR cursorValue IS
3 SELECT h.product_description,o.rupany_short_name
4 FROM company o,product h
5 WHERE o.product_id =h.product_id
6 ORDER by 2;
7 BEGIN
8
9 FOR idx IN cursorValue LOOP
10 dbms_output.put_line(rpad(idx.product_description,20," ")||" "||rpad(idx.rupany_short_name,30," "));
11 END LOOP;
12 END;
13 /
Java A Inc.
Java B Inc.
Java C Inc.
Oracle D Inc.
Oracle E Inc.
Oracle F Inc.
PL/SQL procedure successfully completed.
SQL>
SQL> drop table product;
Table dropped.
SQL>
SQL> drop table company;
Table dropped.
Drop any objects to make script re-runnable: for in
SQL> SET SERVEROUTPUT ON SIZE 1000000
SQL>
SQL> BEGIN
2
3 FOR i IN (SELECT table_name
4 FROM user_tables
5 WHERE table_name = "DUMMY" ) LOOP
6 EXECUTE IMMEDIATE "DROP TABLE dummy";
7
8 END LOOP;
9
10 END;
11 /
PL/SQL procedure successfully completed.
SQL>
EXIT a FOR LOOP with exit command
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>
EXPLAIN PLAN FOR select statement
SQL>
SQL> CREATE TABLE employee
2 (employee_id NUMBER(7),
3 last_name VARCHAR2(25),
4 first_name VARCHAR2(25),
5 userid VARCHAR2(8),
6 start_date DATE,
7 comments VARCHAR2(255),
8 manager_id NUMBER(7),
9 title VARCHAR2(25),
10 department_id NUMBER(7),
11 salary NUMBER(11, 2),
12 commission_pct NUMBER(4, 2)
13 );
Table created.
SQL>
SQL> INSERT INTO employee VALUES (1, "V", "Ben", "cv",to_date("03-MAR-90 8:30", "dd-mon-yy hh24:mi"),NULL, NULL, "PRESIDENT", 50, 2500, NULL);
1 row created.
SQL> INSERT INTO employee VALUES (2, "N", "Haidy", "ln", "08-MAR-90", NULL,1, "VP, OPERATIONS", 41, 1450, NULL);
1 row created.
SQL> INSERT INTO employee VALUES (3, "N", "Molly", "mn", "17-JUN-91",NULL, 1, "VP, SALES", 31, 1400, NULL);
1 row created.
SQL> INSERT INTO employee VALUES (4, "S", "Mark", "mq", "07-APR-90",NULL, 1, "VP, FINANCE", 10, 1450, NULL);
1 row created.
SQL> INSERT INTO employee VALUES (5, "R", "AUDRY", "ar", "04-MAR-90",NULL, 1, "VP, ADMINISTRATION", 50, 1550, NULL);
1 row created.
SQL> INSERT INTO employee VALUES (6, "U", "MOLLY", "mu", "18-JAN-91",NULL, 2, "WAREHOUSE MANAGER", 41, 1200, NULL);
1 row created.
SQL> INSERT INTO employee VALUES (7, "M", "ROBERTA", "rm", "14-MAY-90",NULL, 2, "WAREHOUSE MANAGER", 41, 1250, NULL);
1 row created.
SQL> INSERT INTO employee VALUES (8, "B", "BEN", "ry", "07-APR-90", NULL, 2,"WAREHOUSE MANAGER", 41, 1100, NULL);
1 row created.
SQL> INSERT INTO employee VALUES (9, "C", "Jane", "ac", "09-FEB-92",NULL, 2, "WAREHOUSE MANAGER", 41, 1300, NULL);
1 row created.
SQL> INSERT INTO employee VALUES (10, "H", "Mart", "mh", "27-FEB-91", NULL, 2,"WAREHOUSE MANAGER", 41, 1307, NULL);
1 row created.
SQL>
SQL>
SQL>
SQL> EXPLAIN PLAN FOR
2 SELECT last_name, first_name
3 FROM employee;
Explained.
SQL>
SQL>
SQL> drop table employee;
Table dropped.
SQL>
FOR Loop Ranges
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> DECLARE
2 v_LowValue NUMBER := 10;
3 v_HighValue NUMBER := 40;
4 BEGIN
5 FOR v_Counter IN REVERSE v_LowValue .. v_HighValue LOOP
6 INSERT INTO employee (id)
7 VALUES (v_Counter);
8 END LOOP;
9 END;
10 /
PL/SQL procedure successfully completed.
SQL>
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
40
39
38
37
36
35
34
33
32
31
30
29
28
27
26
25
24
23
22
21
20
19
18
17
16
15
14
13
12
11
10
39 rows selected.
SQL>
SQL>
SQL>
SQL> -- clean the table
SQL> drop table Employee
2 /
Table dropped.
FOR Loops
A FOR loop runs a predetermined number of times.
The syntax for a FOR loop is as follows:
FOR loop_variable IN [REVERSE] lower_bound..upper_bound LOOP
statements
END LOOP;
FOR Loop Scoping Rules
This loop redeclares v_Counter as a BINARY_INTEGER, which hides the NUMBER declaration of v_Counter.
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> DECLARE
2 v_Counter NUMBER := 7;
3 BEGIN
4 -- Inserts the value 7 into employee.
5 INSERT INTO employee (id) VALUES (v_Counter);
6 -- This loop redeclares v_Counter as a BINARY_INTEGER, which hides
7 -- the NUMBER declaration of v_Counter.
8 FOR v_Counter IN 20..30 LOOP
9 -- Inside the loop, v_Counter ranges from 20 to 30.
10 INSERT INTO employee (id) VALUES (v_Counter);
11 END LOOP;
12 -- Inserts another 7 into employee.
13 INSERT INTO employee (id)
14 VALUES (v_Counter);
15 END;
16 /
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
7
20
21
22
23
24
25
26
27
28
29
30
7
21 rows selected.
SQL>
SQL>
SQL>
SQL> -- clean the table
SQL> drop table Employee
2 /
Table dropped.
SQL>
SQL>
SQL>
SQL>
For loop variable in a range of numbers
SQL>
SQL> set serveroutput on
SQL>
SQL> DECLARE
2
3 start_period_number pls_integer := 1;
4 end_period_number pls_integer := 10;
5 current_period pls_integer := 5;
6
7 BEGIN
8
9 dbms_output.put_line("forward loop...");
10 FOR loop_counter IN 1 .. 10
11 LOOP
12 dbms_output.put_line(loop_counter);
13 -- ... executable statements ...
14 END LOOP;
15 END;
16 /
forward loop...
1
2
3
4
5
6
7
8
9
10
PL/SQL procedure successfully completed.
FOR LOOP with loop counter
SQL>
SQL>
SQL> set serveroutput on
SQL> set echo on
SQL> BEGIN
2 FOR v_loopcounter IN 1..5 LOOP
3 DBMS_OUTPUT.PUT_LINE("Loop counter is " || v_loopcounter);
4 END LOOP;
5 END;
6 /
Loop counter is 1
Loop counter is 2
Loop counter is 3
Loop counter is 4
Loop counter is 5
PL/SQL procedure successfully completed.
SQL>
SQL>
FOR year_number IN 1800..1995: define variable for a range type
SQL>
SQL>
SQL> set serveroutput on size 500000
SQL>
SQL> BEGIN
2
3 <<year_loop>>
4 FOR year_number IN 1800..1995
5 LOOP
6
7 <<month_loop>>
8 FOR month_number IN 1 .. 12
9 LOOP
10 IF year_loop.year_number = 1900
11 THEN
12 dbms_output.put_line("The "||month_number||"th month of "||year_number);
13 END IF;
14
15 END LOOP month_loop;
16
17 END LOOP year_loop;
18
19 END;
20 /
The 1th month of 1900
The 2th month of 1900
The 3th month of 1900
The 4th month of 1900
The 5th month of 1900
The 6th month of 1900
The 7th month of 1900
The 8th month of 1900
The 9th month of 1900
The 10th month of 1900
The 11th month of 1900
The 12th month of 1900
PL/SQL procedure successfully completed.
SQL>
SQL>
LOOP with Labels
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> DECLARE
2 v_Counter BINARY_INTEGER := 1;
3 BEGIN
4 LOOP
5 INSERT INTO employee (id)
6 VALUES (v_Counter);
7 v_Counter := v_Counter + 1;
8 IF v_Counter > 50 THEN
9 GOTO l_EndOfLoop;
10 END IF;
11 END LOOP;
12
13 <<l_EndOfLoop>>
14 INSERT INTO employee (id)
15 VALUES (99);
16 END;
17 /
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
99
59 rows selected.
SQL>
SQL>
SQL>
SQL> -- clean the table
SQL> drop table Employee
2 /
Table dropped.
Nested FOR loop counter
SQL>
SQL> set serveroutput on
SQL> set echo on
SQL>
SQL> BEGIN
2 FOR v_outerloopcounter IN 1..2 LOOP
3 FOR v_innerloopcounter IN 1..4 LOOP
4 DBMS_OUTPUT.PUT_LINE("Outer Loop counter is " ||
5 v_outerloopcounter ||
6 " Inner Loop counter is " || v_innerloopcounter);
7 END LOOP;
8 END LOOP;
9 END;
10 /
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
Outer Loop counter is 1 Inner Loop counter is 4
Outer Loop counter is 2 Inner Loop counter is 1
Outer Loop counter is 2 Inner Loop counter is 2
Outer Loop counter is 2 Inner Loop counter is 3
Outer Loop counter is 2 Inner Loop counter is 4
PL/SQL procedure successfully completed.
SQL>
Nested FOR LOOP with Labels
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 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
Outer Loop counter is 1 Inner Loop counter is 4
Outer Loop counter is 2 Inner Loop counter is 1
Outer Loop counter is 2 Inner Loop counter is 2
Outer Loop counter is 2 Inner Loop counter is 3
Outer Loop counter is 2 Inner Loop counter is 4
PL/SQL procedure successfully completed.
SQL>
SQL>
Nested IF statement in For loop
SQL>
SQL> set serveroutput on
SQL> set echo on
SQL>
SQL>
SQL> BEGIN
2 FOR v_loopcounter IN 1..6 LOOP
3 IF MOD(v_loopcounter,2) = 0 THEN
4 DBMS_OUTPUT.PUT_LINE("Loop counter is " || v_loopcounter);
5 END IF; -- End execution of statements for even counter
6 END LOOP;
7 END;
8 /
Loop counter is 2
Loop counter is 4
Loop counter is 6
PL/SQL procedure successfully completed.
SQL>
SQL>
Nested three level LOOP
SQL>
SQL> set serveroutput on
SQL> set echo on
SQL>
SQL> BEGIN
2 FOR I = 5 to 7 LOOP
3 FOR J = 1 to 31 LOOP
4 FOR K = 1 to 12 LOOP
5 NULL;
6 END LOOP;
7 END LOOP;
8 END LOOP;
9 END;
10 /
FOR I = 5 to 7 LOOP
*
ERROR at line 2:
ORA-06550: line 2, column 15:
PLS-00103: Encountered the symbol "=" when expecting one of the following:
in
SQL>
Reversed FOR LOOP
SQL>
SQL> set serveroutput on
SQL> set echo on
SQL>
SQL> DECLARE
2 v_Start Integer := 1;
3 BEGIN
4 FOR v_loopcounter IN REVERSE v_Start..5 LOOP
5 DBMS_OUTPUT.PUT_LINE("Loop counter is " || v_loopcounter);
6 END LOOP;
7 END;
8 /
Loop counter is 5
Loop counter is 4
Loop counter is 3
Loop counter is 2
Loop counter is 1
PL/SQL procedure successfully completed.
SQL>
Reversed loop
SQL>
SQL>
SQL> set serveroutput on
SQL>
SQL> DECLARE
2
3 start_period_number pls_integer := 1;
4 end_period_number pls_integer := 10;
5 current_period pls_integer := 5;
6
7 BEGIN
8
9 dbms_output.put_line("reverse loop...");
10 FOR loop_counter IN REVERSE 1 .. 10
11 LOOP
12 dbms_output.put_line(loop_counter);
13 -- ... executable statements ...
14 END LOOP;
15
16
17 END;
18 /
reverse loop...
10
9
8
7
6
5
4
3
2
1
PL/SQL procedure successfully completed.
SQL>
SQL>
The upper or lower bounds of the FOR loop can be defined as variables or functions.
SQL>
SQL> declare
2 V_lower NUMBER:=2/3;
3 begin
4 for main_c in reverse v_lower..10/3
5 loop
6 DBMS_OUTPUT.put_line(main_c);
7 end loop;
8 end;
9 /
3
2
1
PL/SQL procedure successfully completed.
SQL>
Use FOR LOOP to loop through dates
SQL>
SQL> SET SERVEROUTPUT ON
SQL> DECLARE
2 test_date DATE;
3 day_of_week VARCHAR2(3);
4 years_ahead INTEGER;
5 BEGIN
6 test_date := TO_DATE("1-Jan-1997","dd-mon-yyyy");
7
8 FOR years_ahead IN 1..10 LOOP
9 day_of_week := TO_CHAR(test_date,"Dy");
10
11 IF day_of_week IN ("Sat","Sun") THEN
12 DBMS_OUTPUT.PUT_LINE(TO_CHAR(test_date,"dd-Mon-yyyy")|| " A long weekend!");
13 ELSE
14 DBMS_OUTPUT.PUT_LINE(TO_CHAR(test_date,"dd-Mon-yyyy")|| " Not a long weekend.");
15 END IF;
16 test_date := ADD_MONTHS(test_date,12);
17 END LOOP;
18 END;
19 /
01-Jan-1997 Not a long weekend.
01-Jan-1998 Not a long weekend.
01-Jan-1999 Not a long weekend.
01-Jan-2000 A long weekend!
01-Jan-2001 Not a long weekend.
01-Jan-2002 Not a long weekend.
01-Jan-2003 Not a long weekend.
01-Jan-2004 Not a long weekend.
01-Jan-2005 A long weekend!
01-Jan-2006 A long weekend!
PL/SQL procedure successfully completed.
SQL>
SQL>
variable-delimitted loop
SQL>
SQL> set serveroutput on 500000
SP2-0158: unknown SET option "500000"
SQL>
SQL> DECLARE
2
3 start_period_number pls_integer := 1;
4 end_period_number pls_integer := 10;
5 current_period pls_integer := 5;
6
7 BEGIN
8
9 FOR calc_index IN start_period_number ..
10 LEAST (end_period_number, current_period)
11 LOOP
12 dbms_output.put_line(calc_index);
13 -- ... executable statements ...
14 END LOOP;
15
16
17 END;
18 /
1
2
3
4
5
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL>
SQL>