Oracle PL/SQL/PL SQL/Loop

An anonymous block demonstrates a guard on exit simple loop

   
SQL>
SQL> DECLARE
  2    counter NUMBER;
  3    first   BOOLEAN;
  4  BEGIN
  5    LOOP
  6      IF NVL(counter,1) >= 1 THEN
  7        IF NOT NVL(first,TRUE) THEN
  8          counter := counter + 1;
  9        ELSE
 10          counter := 1;
 11          first := FALSE;
 12        END IF;
 13      END IF;
 14      dbms_output.put_line("Iteration ["||counter||"]");
 15
 16      EXIT WHEN NOT counter < 3;
 17    END LOOP;
 18  END;
 19  /
Iteration [1]
Iteration [2]
Iteration [3]
PL/SQL procedure successfully completed.
SQL>

Empty Loop statement

  
SQL>
SQL>   BEGIN
  2         LOOP
  3              NULL;
  4              EXIT;
  5         END LOOP;
  6    END;
  7  /
PL/SQL procedure successfully completed.
SQL>
SQL> --

Insert a row into MyTable with the current value of the loop counter.

  
SQL>
SQL>
SQL> CREATE TABLE MyTable (
  2    num_col    NUMBER,
  3    char_col   VARCHAR2(60)
  4    );
Table created.
SQL>
SQL> DECLARE
  2    v_Counter BINARY_INTEGER := 1;
  3  BEGIN
  4    LOOP
  5      INSERT INTO MyTable
  6        VALUES (v_Counter, "Loop index");
  7      v_Counter := v_Counter + 1;
  8      IF v_Counter > 50 THEN
  9        EXIT;
 10      END IF;
 11    END LOOP;
 12  END;
 13  /
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL> drop table MyTable;
Table dropped.
SQL>
SQL>

LOOP..END LOOP, Cursor Loop

  
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>
SQL> CREATE TABLE MyTable (
  2    num_col    NUMBER,
  3    char_col   VARCHAR2(60)
  4    );
Table created.
SQL>
SQL> DECLARE
  2    myLecturerID   lecturer.id%TYPE;
  3    myFirstName   lecturer.first_name%TYPE;
  4    v_LastName    lecturer.last_name%TYPE;
  5
  6    CURSOR myHistoryLecturer IS
  7      SELECT id, first_name, last_name
  8        FROM lecturer
  9        WHERE major = "History";
 10  BEGIN
 11    OPEN myHistoryLecturer;
 12    LOOP
 13      FETCH myHistoryLecturer INTO myLecturerID, myFirstName, v_LastName;
 14
 15      EXIT WHEN myHistoryLecturer%NOTFOUND;
 16
 17      INSERT INTO myStudent (student_id, department, course)
 18        VALUES (myLecturerID, "HIS", 301);
 19
 20      INSERT INTO MyTable (num_col, char_col)
 21        VALUES (myLecturerID, myFirstName || " " || v_LastName);
 22
 23    END LOOP;
 24
 25    CLOSE myHistoryLecturer;
 26
 27    COMMIT;
 28  END;
 29  /
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 MyTable;
Table dropped.
SQL>
SQL> drop table lecturer;
Table dropped.
SQL> drop table myStudent;
Table dropped.
SQL>
SQL>

Loop with label

  
SQL>
SQL>
SQL> DECLARE
  2     lv_counter_num PLS_INTEGER := 0;
  3  BEGIN
  4     <<LOOP1>>
  5     LOOP
  6        <<LOOP2>>
  7        LOOP
  8           lv_counter_num := lv_counter_num + 1;
  9           DBMS_OUTPUT.PUT_LINE("Counter: " ||lv_counter_num);
 10           EXIT LOOP1 WHEN lv_counter_num = 3;
 11        END LOOP LOOP2;
 12           DBMS_OUTPUT.PUT_LINE("Exited LOOP2");
 13     END LOOP LOOP1;
 14           DBMS_OUTPUT.PUT_LINE("Exited LOOP1");
 15  END;
 16  /
Counter: 1
Counter: 2
Counter: 3
Exited LOOP1
PL/SQL procedure successfully completed.
SQL>

unconstrained loop

  
SQL>
SQL>  declare
  2      l_loops number := 0;
  3    begin
  4      dbms_output.put_line("Before my loop");
  5
  6      loop
  7        if l_loops > 4 then
  8          exit;
  9        end if;
 10        dbms_output.put_line("Looped " || l_loops || " times");
 11        l_loops := l_loops + 1;
 12      end loop;
 13
 14      dbms_output.put_line("After my loop");
 15    end;
 16    /
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>

Use EXIT WHEN to exit a loop

  
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);
  7               v_Radius := v_Radius + 2;
  8               EXIT WHEN v_Radius > 10;
  9          END LOOP;
 10    END;
 11  /
The AREA of the circle is 4
The AREA of the circle is 16
The AREA of the circle is 36
The AREA of the circle is 64
The AREA of the circle is 100
PL/SQL procedure successfully completed.
SQL> --