Oracle PL/SQL Tutorial/PL SQL Statements/While
Содержание
- 1 A complete example of using the cursor variable using a WHILE LOOP
- 2 Calculate circle Area in WHILE loop
- 3 Example of a WHILE loop that never executes.
- 4 EXIT a WHILE LOOP with exit command
- 5 Nested WHILE Loop
- 6 The EXIT and EXIT WHEN Statements
- 7 Use WHILE Loop to insert data to table
- 8 While Counter is initialized to NULL
- 9 While loop
- 10 WHILE Loops
- 11 WHILE LOOP with counter
A complete example of using the cursor variable using a WHILE LOOP
<source lang="sql">
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 v_company_rec cursorValue%ROWTYPE; 8 BEGIN 9 OPEN cursorValue; 10 11 FETCH cursorValue INTO v_company_rec; 12 13 WHILE (cursorValue%FOUND) LOOP 14 dbms_output.put_line(rpad(v_company_rec.product_description,20," ")||" "|| 15 rpad(v_company_rec.rupany_short_name,30," ")); 16 FETCH cursorValue INTO v_company_rec; 17 END LOOP; 18 CLOSE cursorValue; 19 END; 20 /
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. SQL> SQL></source>
Calculate circle Area in 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 v_Radius <=10 LOOP
5 DBMS_OUTPUT.PUT_LINE("The Area is " ||
6 mypi * v_Radius * v_Radius);
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.</source>
Example of a WHILE loop that never executes.
<source lang="sql">
DECLARE
v_Calc NUMBER := 0;
BEGIN
WHILE v_Calc >= 10 LOOP
v_Calc := v_Calc + 1;
DBMS_OUTPUT.PUT_LINE("The value of v_Calc is " || v_Calc);
END LOOP;
END; /
Corrected WHILE loop that executes.
SQL> DECLARE
2 v_Calc NUMBER := 0;
3 BEGIN
4 WHILE v_Calc <= 10 LOOP
5 v_Calc := v_Calc + 1;
6 DBMS_OUTPUT.PUT_LINE("The value of v_Calc is " || v_Calc);
7 END LOOP;
8 END;
9 /
The value of v_Calc is 1 The value of v_Calc is 2 The value of v_Calc is 3 The value of v_Calc is 4 The value of v_Calc is 5 The value of v_Calc is 6 The value of v_Calc is 7 The value of v_Calc is 8 The value of v_Calc is 9 The value of v_Calc is 10 The value of v_Calc is 11 PL/SQL procedure successfully completed. SQL></source>
EXIT a WHILE LOOP with exit command
<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>
Nested WHILE Loop
<source lang="sql">
SQL> SQL> SQL> declare
2 v_ind NUMBER; 3 v_current NUMBER; 4 begin 5 v_current:=0; 6 while v_current<=25 7 loop 8 v_ind:=0; 9 while v_ind<4 10 loop 11 v_ind:= v_ind+1; 12 DBMS_OUTPUT.put_line(v_current); 13 end loop; 14 v_current:=v_current+5; 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 25 25 25 25 PL/SQL procedure successfully completed. SQL></source>
The EXIT and EXIT WHEN Statements
The EXIT and EXIT WHEN statements enable you to escape out of the control of a loop.
The format of the EXIT loop is
<source lang="sql">
EXIT;</source>
Use WHILE Loop to insert data to table
<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> SQL> DECLARE
2 v_Counter BINARY_INTEGER := 1; 3 BEGIN 4 WHILE v_Counter <= 50 LOOP 5 INSERT INTO employee (id) VALUES (v_Counter); 6 v_Counter := v_Counter + 1; 7 END LOOP; 8 END; 9 /
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. SQL></source>
While Counter is initialized to NULL
<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> DECLARE
2 v_Counter BINARY_INTEGER; 3 BEGIN 4 5 WHILE v_Counter <= 50 LOOP 6 INSERT INTO employee (id) VALUES (v_Counter); 7 v_Counter := v_Counter + 1; 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 8 rows selected. SQL> SQL> SQL> SQL> -- clean the table SQL> drop table Employee
2 /
Table dropped. SQL> SQL> SQL></source>
While loop
<source lang="sql">
SQL> set serveroutput on SQL> DECLARE
2 just_a_num NUMBER := 1; 3 BEGIN 4 WHILE (just_a_num <= 10) LOOP 5 dbms_output.put_line(just_a_num); 6 just_a_num := just_a_num + 1; 7 END LOOP; 8 END; 9 /
1 2 3 4 5 6 7 8 9 10 PL/SQL procedure successfully completed. SQL></source>
WHILE Loops
WHILE loops check the condition and execute a set of commands.
The loop is repeated until the loop is exited.
A WHILE loop is exactly equivalent to a regular loop with an EXIT WHEN as the first statement.
The condition is checked before the code in the loop is executed.
If the condition is false, the code in the loop will never be executed.
WHILE loop makes your code a little easier to read than with a normal loop.
<source lang="sql">
WHILE condition LOOP
statements
END LOOP;</source>
WHILE LOOP with counter
<source lang="sql">
SQL> SQL> set serveroutput on SQL> set echo on SQL> SQL> DECLARE
2 v_Calc NUMBER := 0;
3 BEGIN
4 WHILE v_Calc >= 10 LOOP
5 v_Calc := v_Calc + 1;
6 DBMS_OUTPUT.PUT_LINE("The value of v_Calc is " || v_Calc);
7 END LOOP;
8 END;
9 /
PL/SQL procedure successfully completed. SQL> SQL></source>
