Oracle PL/SQL/PL SQL/While Loop
Содержание
- 1 Call EXIT to jump out of a while loop
- 2 Change while loop counter
- 3 Corrected WHILE loop that executes
- 4 Do calculation with while loop counter
- 5 Example of a WHILE loop that never executes
- 6 Insert value in while loop
- 7 Put two statement in while loop
- 8 The WHILE loop uses a loop index value as its gate on entry criterion:
- 9 This script demonstrates variable visibility
- 10 Use EXIT WHEN to exit a while loop
- 11 WHILE Loop
- 12 While loop and number counter
- 13 WHILE..LOOP, Cursor Loop
- 14 WHILE Loop: Test the loop counter before each loop iteration to insure that it is still less than 50
- 15 WHILE Loop: This condition will evaluate to NULL, since v_Counter is initialized to NULL by default
- 16 While loop with complex conditions
- 17 While Loop with condition
Call EXIT to jump out of a while loop
<source lang="sql">
SQL> SQL> DECLARE
2 v_Radius NUMBER := 2; 3 BEGIN 4 WHILE TRUE LOOP 5 DBMS_OUTPUT.PUT_LINE("The Area is " || v_Radius * v_Radius); 6 IF v_Radius = 10 THEN 7 EXIT; 8 END IF; 9 v_Radius := v_Radius + 2 ; -- Calculates Area for Even Radius 10 END LOOP; 11 END; 12 /
The Area is 4 The Area is 16 The Area is 36 The Area is 64 The Area is 100 PL/SQL procedure successfully completed. SQL> SQL> SQL> --
</source>
Change while loop counter
<source lang="sql">
SQL> set serverout on; SQL> -- while loop SQL> SQL> declare
2 loops number := 0; 3 begin 4 dbms_output.put_line("Before my loop"); 5 6 while loops < 5 loop 7 dbms_output.put_line("Looped " || loops || " times"); 8 loops := loops + 1; 9 end loop; 10 11 dbms_output.put_line("After my loop"); 12 end; 13 /
Before my loop Looped 0 times Looped 1 times Looped 2 times Looped 3 times Looped 4 times After my loop PL/SQL procedure successfully completed. SQL>
</source>
Corrected WHILE loop that executes
<source lang="sql">
SQL> SQL> -- Corrected WHILE loop that executes. SQL> SQL> set serveroutput on; SQL> SQL> DECLARE
2 c NUMBER := 0; 3 BEGIN 4 WHILE c <= 10 LOOP 5 c := c + 1; 6 DBMS_OUTPUT.PUT_LINE("The value of c is " || c); 7 END LOOP; 8 END; 9 /
The value of c is 1 The value of c is 2 The value of c is 3 The value of c is 4 The value of c is 5 The value of c is 6 The value of c is 7 The value of c is 8 The value of c is 9 The value of c is 10 The value of c is 11 PL/SQL procedure successfully completed. SQL>
</source>
Do calculation with while loop counter
<source lang="sql">
SQL> SQL> DECLARE
2 v_Radius NUMBER := 2; 3 BEGIN 4 WHILE v_Radius <=10 LOOP 5 DBMS_OUTPUT.PUT_LINE("The Area is " || v_Radius * v_Radius); 6 v_Radius := v_Radius + 2 ; -- Calculates Area for Even Radius 7 END LOOP; 8 END; 9 /
The Area is 4 The Area is 16 The Area is 36 The Area is 64 The Area is 100 PL/SQL procedure successfully completed. SQL> SQL> SQL> --
</source>
Example of a WHILE loop that never executes
<source lang="sql">
SQL> SQL> -- Example of a WHILE loop that never executes. SQL> set serveroutput on; SQL> SQL> DECLARE
2 c NUMBER := 0; 3 BEGIN 4 DBMS_OUTPUT.PUT_LINE("The value of variable_Calc is " || c); 5 WHILE c >= 10 LOOP 6 c := c + 1; 7 DBMS_OUTPUT.PUT_LINE("The value of variable_Calc is " || c); 8 END LOOP; 9 END; 10 /
The value of variable_Calc is 0 PL/SQL procedure successfully completed. SQL>
</source>
Insert value in while loop
<source lang="sql">
SQL> SQL> create table supplier(
2 supplier_no integer primary key 3 ,supplier_name varchar2(50) 4 ,address varchar(30) 5 ,city varchar(20) 6 ,state varchar2(2) 7 ,area_code varchar2(3) 8 ,phone varchar2(8) 9 );
Table created. SQL> SQL> SQL> declare
2 v_ctr number := 2 ; 3 v_prod_ctr number := 3 ; 4 v_loop number := 1; 5 6 v_curr_supplier supplier.supplier_no%TYPE ; 7 begin 8 WHILE v_loop <= v_ctr LOOP 9 INSERT INTO supplier (SUPPLIER_NO, SUPPLIER_NAME)VALUES (v_loop+999, "#"||v_loop); 10 v_loop := v_loop + 1 ; 11 END LOOP; 12 COMMIT; 13 end; 14 /
PL/SQL procedure successfully completed. SQL> SQL> select * from supplier; SUPPLIER_NO SUPPLIER_NAME ADDRESS CITY ST ARE PHONE
-------------------------------------------------- ------------------------------ -------------------- -- --- --------
1000 #1 1001 #2
2 rows selected. SQL> SQL> drop table supplier; Table dropped. SQL> SQL> --
</source>
Put two statement in while loop
<source lang="sql">
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 /
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> SQL> SQL> SQL> SQL> --
</source>
The WHILE loop uses a loop index value as its gate on entry criterion:
<source lang="sql">
SQL> SQL> DECLARE
2 counter NUMBER := 1; 3 BEGIN 4 WHILE (counter < 5) LOOP 5 dbms_output.put_line("Index ["||counter||"]."); 6 IF counter >= 1 THEN 7 counter := counter + 1; 8 END IF; 9 END LOOP; 10 END; 11 /
Index [1]. Index [2]. Index [3]. Index [4]. PL/SQL procedure successfully completed. SQL>
</source>
This script demonstrates variable visibility
<source lang="sql">
SQL> SQL> SQL> SET SERVEROUTPUT ON SQL> SQL> DECLARE
2 visibleValue VARCHAR2(30); 3 hiddenValue VARCHAR2(30); 4 BEGIN 5 visibleValue := "visible"; 6 hiddenValue := "hidden"; 7 8 DBMS_OUTPUT.PUT_LINE("OUTER BLOCK"); 9 DBMS_OUTPUT.PUT_LINE(visibleValue); 10 DBMS_OUTPUT.PUT_LINE(hiddenValue); 11 12 DECLARE 13 hiddenValue NUMBER(10); 14 BEGIN 15 DBMS_OUTPUT.PUT_LINE("INNER BLOCK"); 16 hiddenValue := "inner hiddenValue"; 17 DBMS_OUTPUT.PUT_LINE(hiddenValue); 18 EXCEPTION 19 WHEN OTHERS 20 THEN 21 DBMS_OUTPUT.PUT_LINE("hiddenValue of type VARCHAR2 was...hidden"); 22 END; 23 END; 24 /
OUTER BLOCK visible hidden INNER BLOCK hiddenValue of type VARCHAR2 was...hidden PL/SQL procedure successfully completed.
</source>
Use EXIT WHEN to exit a while loop
<source lang="sql">
SQL> SQL> DECLARE
2 v_Radius NUMBER := 2; 3 BEGIN 4 WHILE TRUE LOOP 5 DBMS_OUTPUT.PUT_LINE("The Area is " ||v_Radius * v_Radius); 6 EXIT WHEN v_RADIUS = 10; 7 v_Radius := v_Radius + 2 ; -- Calculates Area for Even Radius 8 END LOOP; 9 END; 10 /
The Area is 4 The Area is 16 The Area is 36 The Area is 64 The Area is 100 PL/SQL procedure successfully completed. SQL> SQL> --
</source>
WHILE Loop
<source lang="sql">
SQL> SQL> DECLARE
2 v_count PLS_INTEGER := 1; 3 BEGIN 4 WHILE v_count <= 20 5 LOOP 6 DBMS_OUTPUT.PUT_LINE("While loop iteration: "||v_count); 7 v_count := v_count + 1; 8 END LOOP; 9 END; 10 /
While loop iteration: 1 While loop iteration: 2 While loop iteration: 3 While loop iteration: 4 While loop iteration: 5 While loop iteration: 6 While loop iteration: 7 While loop iteration: 8 While loop iteration: 9 While loop iteration: 10 While loop iteration: 11 While loop iteration: 12 While loop iteration: 13 While loop iteration: 14 While loop iteration: 15 While loop iteration: 16 While loop iteration: 17 While loop iteration: 18 While loop iteration: 19 While loop iteration: 20 PL/SQL procedure successfully completed. SQL>
</source>
While loop and number counter
<source lang="sql">
SQL> -- create demo table SQL> create table emp(
2 ID VARCHAR2(4 BYTE) NOT NULL, 3 fname VARCHAR2(10 BYTE), 4 lname 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> SQL> -- prepare data SQL> insert into emp(ID, fname, lname, 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 emp(ID, fname, lname, 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> SQL> SQL> declare
2 s Number(8,2); 3 totalValue Number(8,2); 4 begin 5 WHILE totalValue < 100 LOOP 6 Select salary into s from emp; 7 totalValue := totalValue + s; 8 END LOOP; 9 10 dbms_output.put_line(totalValue); 11 end; 12 /
PL/SQL procedure successfully completed. SQL> SQL> drop table emp; Table dropped. SQL>
</source>
WHILE..LOOP, Cursor Loop
<source lang="sql">
SQL> SQL> CREATE TABLE lecturer (
2 id NUMBER(5) PRIMARY KEY, 3 first_name VARCHAR2(20), 4 last_name VARCHAR2(20), 5 major VARCHAR2(30), 6 current_credits NUMBER(3) 7 );
Table created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10001, "Scott", "Lawson","Computer Science", 11);
1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major, current_credits)
2 VALUES (10002, "Mar", "Wells","History", 4);
1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10003, "Jone", "Bliss","Computer Science", 8);
1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10004, "Man", "Kyte","Economics", 8);
1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10005, "Pat", "Poll","History", 4);
1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10006, "Tim", "Viper","History", 4);
1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10007, "Barbara", "Blues","Economics", 7);
1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10008, "David", "Large","Music", 4);
1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10009, "Chris", "Elegant","Nutrition", 8);
1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10010, "Rose", "Bond","Music", 7);
1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10011, "Rita", "Johnson","Nutrition", 8);
1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10012, "Sharon", "Clear","Computer Science", 3);
1 row created. SQL> SQL> CREATE TABLE myStudent (
2 student_id NUMBER(5) NOT NULL, 3 department CHAR(3) NOT NULL, 4 course NUMBER(3) NOT NULL, 5 grade CHAR(1) 6 );
Table created. SQL> SQL> SQL> SQL> SQL> INSERT INTO myStudent (student_id, department, course, grade)
2 VALUES (10000, "CS", 102, "A");
1 row created. SQL> SQL> INSERT INTO myStudent (student_id, department, course, grade)
2 VALUES (10002, "CS", 102, "B");
1 row created. SQL> SQL> INSERT INTO myStudent (student_id, department, course, grade)
2 VALUES (10003, "CS", 102, "C");
1 row created. SQL> SQL> INSERT INTO myStudent (student_id, department, course, grade)
2 VALUES (10000, "HIS", 101, "A");
1 row created. SQL> SQL> INSERT INTO myStudent (student_id, department, course, grade)
2 VALUES (10001, "HIS", 101, "B");
1 row created. SQL> SQL> INSERT INTO myStudent (student_id, department, course, grade)
2 VALUES (10002, "HIS", 101, "B");
1 row created. SQL> SQL> INSERT INTO myStudent (student_id, department, course, grade)
2 VALUES (10003, "HIS", 101, "A");
1 row created. SQL> SQL> INSERT INTO myStudent (student_id, department, course, grade)
2 VALUES (10004, "HIS", 101, "C");
1 row created. SQL> SQL> INSERT INTO myStudent (student_id, department, course, grade)
2 VALUES (10005, "HIS", 101, "C");
1 row created. SQL> SQL> INSERT INTO myStudent (student_id, department, course, grade)
2 VALUES (10006, "HIS", 101, "E");
1 row created. SQL> SQL> INSERT INTO myStudent (student_id, department, course, grade)
2 VALUES (10007, "HIS", 101, "B");
1 row created. SQL> SQL> INSERT INTO myStudent (student_id, department, course, grade)
2 VALUES (10008, "HIS", 101, "A");
1 row created. SQL> SQL> INSERT INTO myStudent (student_id, department, course, grade)
2 VALUES (10009, "HIS", 101, "D");
1 row created. SQL> SQL> INSERT INTO myStudent (student_id, department, course, grade)
2 VALUES (10010, "HIS", 101, "A");
1 row created. SQL> SQL> INSERT INTO myStudent (student_id, department, course, grade)
2 VALUES (10008, "NUT", 307, "A");
1 row created. SQL> SQL> INSERT INTO myStudent (student_id, department, course, grade)
2 VALUES (10010, "NUT", 307, "A");
1 row created. SQL> SQL> INSERT INTO myStudent (student_id, department, course, grade)
2 VALUES (10009, "MUS", 410, "B");
1 row created. SQL> SQL> INSERT INTO myStudent (student_id, department, course, grade)
2 VALUES (10006, "MUS", 410, "E");
1 row created. SQL> SQL> INSERT INTO myStudent (student_id, department, course, grade)
2 VALUES (10011, "MUS", 410, "B");
1 row created. SQL> SQL> INSERT INTO myStudent (student_id, department, course, grade)
2 VALUES (10000, "MUS", 410, "B");
1 row created. SQL> SQL> CREATE TABLE MyTable (
2 num_col NUMBER, 3 char_col VARCHAR2(60) 4 );
Table created. SQL> SQL> SQL> DECLARE
2 CURSOR myHistoryLecturer IS 3 SELECT id, first_name, last_name 4 FROM lecturer 5 WHERE major = "History"; 6 7 myLecturerData myHistoryLecturer%ROWTYPE; 8 BEGIN 9 OPEN myHistoryLecturer; 10 FETCH myHistoryLecturer INTO myLecturerData; 11 12 WHILE myHistoryLecturer%FOUND LOOP 13 INSERT INTO myStudent (student_id, department, course) 14 VALUES (myLecturerData.ID, "HIS", 101); 15 16 INSERT INTO MyTable (num_col, char_col) 17 VALUES (myLecturerData.ID, 18 myLecturerData.first_name || " " || myLecturerData.last_name); 19 20 FETCH myHistoryLecturer INTO myLecturerData; 21 END LOOP; 22 23 CLOSE myHistoryLecturer; 24 25 COMMIT; 26 END; 27 /
PL/SQL procedure successfully completed. SQL> SQL> select * from MyTable;
NUM_COL CHAR_COL
------------------------------------------------------------
10002 Mar Wells 10005 Pat Poll 10006 Tim Viper
SQL> SQL> drop table myStudent; Table dropped. SQL> drop table lecturer; Table dropped. SQL> drop table MyTable; Table dropped. SQL> SQL> --
</source>
WHILE Loop: Test the loop counter before each loop iteration to insure that it is still less than 50
<source lang="sql">
SQL> SQL> SQL> CREATE TABLE MyTable (
2 num_col NUMBER, 3 char_col VARCHAR2(60) 4 );
Table created. SQL> SQL> SQL> DECLARE
2 v_Counter BINARY_INTEGER := 1; 3 BEGIN 4 WHILE v_Counter <= 50 LOOP 5 INSERT INTO MyTable 6 VALUES (v_Counter, "Loop index"); 7 v_Counter := v_Counter + 1; 8 END LOOP; 9 END; 10 /
PL/SQL procedure successfully completed. SQL> SQL> select * from MyTable;
NUM_COL CHAR_COL
------------------------------------------------------------
1 Loop index 2 Loop index 3 Loop index 4 Loop index 5 Loop index 6 Loop index 7 Loop index 8 Loop index 9 Loop index 10 Loop index 11 Loop index NUM_COL CHAR_COL
------------------------------------------------------------
12 Loop index 13 Loop index 14 Loop index 15 Loop index 16 Loop index 17 Loop index 18 Loop index 19 Loop index 20 Loop index 21 Loop index 22 Loop index NUM_COL CHAR_COL
------------------------------------------------------------
23 Loop index 24 Loop index 25 Loop index 26 Loop index 27 Loop index 28 Loop index 29 Loop index 30 Loop index 31 Loop index 32 Loop index 33 Loop index NUM_COL CHAR_COL
------------------------------------------------------------
34 Loop index 35 Loop index 36 Loop index 37 Loop index 38 Loop index 39 Loop index 40 Loop index 41 Loop index 42 Loop index 43 Loop index 44 Loop index NUM_COL CHAR_COL
------------------------------------------------------------
45 Loop index 46 Loop index 47 Loop index 48 Loop index 49 Loop index 50 Loop index
50 rows selected. SQL> SQL> drop table MyTable; Table dropped. SQL>
</source>
WHILE Loop: This condition will evaluate to NULL, since v_Counter is initialized to NULL by default
<source lang="sql">
SQL> SQL> CREATE TABLE MyTable (
2 num_col NUMBER, 3 char_col VARCHAR2(60) 4 );
Table created. SQL> SQL> SQL> DECLARE
2 v_Counter BINARY_INTEGER; 3 BEGIN 4 WHILE v_Counter <= 50 LOOP 5 INSERT INTO MyTable 6 VALUES (v_Counter, "Loop index"); 7 v_Counter := v_Counter + 1; 8 END LOOP; 9 END; 10 /
PL/SQL procedure successfully completed. SQL> SQL> SQL> select * from MyTable; no rows selected SQL> SQL> drop table MyTable; Table dropped.
</source>
While loop with complex conditions
<source lang="sql">
SQL> SQL> SQL> --While loop with conditions SQL> SQL> SET SERVEROUTPUT ON SQL> SQL> DECLARE
2 counter NUMBER(2):= 1; 3 counter2 NUMBER(2):= 1; 4 BEGIN 5 WHILE counter <= 12 AND counter2 <= 14 LOOP 6 counter := counter + 1; 7 counter2 := counter2 + 1; 8 END LOOP; 9 DBMS_OUTPUT.PUT_LINE("counter2:"||counter2); 10 DBMS_OUTPUT.PUT_LINE("counter:"||counter); 11 END; 12 /
counter2:13 counter:13 PL/SQL procedure successfully completed. SQL> SQL>
</source>
While Loop with condition
<source lang="sql">
SQL> SQL> SET SERVEROUTPUT ON SQL> DECLARE
2 counter INTEGER := 2; 3 BEGIN 4 5 6 counter := 0; 7 WHILE counter < 6 LOOP 8 counter := counter + 1; 9 DBMS_OUTPUT.PUT_LINE(counter); 10 END LOOP; 11 END; 12 /
1 2 3 4 5 6 PL/SQL procedure successfully completed. SQL>
</source>