Oracle PL/SQL/PL SQL/While Loop

Материал из SQL эксперт
Перейти к: навигация, поиск

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>