Oracle PL/SQL Loops

1. Oracle PL/SQL Loops Please use speaker notes for additional information!

2. SYNTAX: LOOP Processing code within the loop EXIT (which can include a WHEN condition) END LOOP; Basic loop SQL> edit basicloop1 DECLARE v_recno testloop.rec_no%TYPE :=1; v_ctr testloop.ctr%TYPE :=100; BEGIN LOOP INSERT INTO testloop(rec_no, ctr) VALUES(v_recno, v_ctr); v_recno :=v_recno + 1; v_ctr := v_ctr + 10; IF v_recno > 5 THEN EXIT; END IF; END LOOP; END; / PL/SQL code for the basicloop1 block (as seen in the editor). SQL> SELECT * FROM testloop; REC_NO CTR DATA_IN --------- --------- ---------- 1 100 2 110 3 120 4 130 5 140 SQL> @ basicloop1 PL/SQL procedure successfully completed.

3. DECLARE v_recno testloop.rec_no%TYPE :=1; v_ctr testloop.ctr%TYPE :=100; BEGIN LOOP INSERT INTO testloop(rec_no, ctr) VALUES(v_recno, v_ctr); v_recno :=v_recno + 1; v_ctr := v_ctr + 10; IF v_recno > 5 THEN EXIT; END IF; END LOOP; END; / Basic loop Pass Rows in testloop v_recno v_ctr test v_recno > 5 Initialize 1 100 Pass 1 1 100 2 110 2 > 5 = NO -LOOP Pass 2 2 110 3 120 3 > 5 = NO -LOOP Pass 3 3 120 4 130 4 > 5 = NO - LOOP Pass 44 130 5140 5 > 5 = NO -LOOP Pass 5 5 140 6 150 6 > 5 = YES -EXIT

4. DECLARE v_recno testloop.rec_no%TYPE :=1; v_ctr testloop.ctr%TYPE :=100; BEGIN LOOP INSERT INTO testloop(rec_no, ctr) VALUES(v_recno, v_ctr); v_recno :=v_recno + 1; v_ctr := v_ctr + 10; EXIT WHENv_recno > 5 END LOOP; END; / Basic loop Pass Rows in testloop v_recno v_ctr test v_recno > 5 Initialize 1 100 Pass 1 1 100 2 110 2 > 5 = NO -LOOP Pass 2 2 110 3 120 3 > 5 = NO -LOOP Pass 3 3 120 4 130 4 > 5 = NO -LOOP Pass 4 4 130 5 140 5 > 5 = NO -LOOP Pass 5 5 140 6 150 6 > 5 = YES -EXIT

5. Basic loop Pass Rows in testloop v_recno v_ctr test v_recno NOT > 5 Basicloop1 Initialize 1 100 Pass 1 1 100 2 110 2 NOT> 5 = NO -LOOP v_recno = 1 v_ctr = 100 Pass 2 2 110 3 120 3 NOT > 5 = NO -LOOP Loop Pass 3 3 120 4 130 4 NOT > 5 = NO -LOOP Pass 4 4 130 5 140 5 NOT > 5 = NO -LOOP Insert v_recno, v_ctr into testloop Pass 5 5 140 6 150 6 > 5 = YES -EXIT Exit drops to END END has been reached - processing complete Add 1 to v_recno Add 1 to v_ctr Directions: Click to initialize and then click to see each of the passes. The last message will tell you processing is complete. v_recno NOT > 5 Y Loop N END END Basicloop1

6. While loops DECLARE v_recno testloop.rec_no%TYPE :=1; v_ctr testloop.ctr%TYPE :=100; BEGIN WHILE v_recno < 6 LOOP INSERT INTO testloop(rec_no, ctr) VALUES(v_recno, v_ctr); v_recno :=v_recno + 1; v_ctr := v_ctr + 10; END LOOP; END; / SQL> edit whileloop1 SQL> @ whileloop1 PL/SQL procedure successfully completed. SQL> SELECT * FROM testloop; REC_NO CTR DATA_IN --------- --------- ---------- 1 100 2 110 3 120 4 130 5 140 SYNTAX: WHILE condition LOOP processing code END LOOP;

7. While loops DECLARE v_recno testloop.rec_no%TYPE :=1; v_ctr testloop.ctr%TYPE :=100; BEGIN WHILEv_recno < 6LOOP INSERT INTO testloop(rec_no, ctr) VALUES(v_recno, v_ctr); v_recno :=v_recno + 1; v_ctr := v_ctr + 10; END LOOP; END; / Pass test v>recno < 6 rows in testloop v_recno v_ctr Initialize 1 100 Pass 1 1 < 6 is YES -LOOP 1 100 2 110 Pass 2 2 < 6 is YES - LOOP 2 110 3 120 Pass 3 3 < 6 is YES - LOOP 3 120 4 130 Pass 4 4 < 6 is YES - LOOP 4 130 5 140 Pass 5 5 < 6 is YES - LOOP 5 140 6 150 Pass 6 6 < 6 is NO - END LOOP (processing is not executed)

8. While loop This flowchart shows the loop code in a separate module. If v_recno <6 the loop is executed. Then the control returns to ask the question again. If the answer to v_recno <6 is No, then the loop is not performed and the END is executed since there is no code except END after the loop. The dotted lines would not be included in the flowchart - I put them in to show the flow. LOOP Whileloop1 Insert v_recno, v_ctr into testloop v_recno = 1 v_ctr = 100 Add 1 to v_recno v_recno < 6 LOOP Y Add 1 to v_ctr N END END LOOP END Whileloop1

9. While loop Whileloop1 LOOP v_recno = 1 v_ctr = 100 Insert v_recno, v_ctr into testloop Directions: Click to initialize and then click to see each of the passes. The last message will tell you processing is complete. Add 1 to v_recno v_recno < 6 LOOP Y N Add 1 to v_ctr END END LOOP END Whileloop1 Pass test v>recno < 6 rows in testloop v_recno v_ctr Initialize 1 100 Pass 1 1 < 6 is YES -LOOP 1 100 2 110 Pass 2 2 < 6 is YES - LOOP 2 110 3 120 Pass 3 3 < 6 is YES - LOOP 3 120 4 130 Pass 4 4 < 6 is YES - LOOP 4 130 5 140 Pass 5 5 < 6 is YES - LOOP 5 140 6 150 Pass 6 6 < 6 is NO - END LOOP (processing is not executed) END has been reached - processing complete

10. While loop Whileloop1 v_recno = 1 v_ctr = 100 Loop v_recno NOT < 6 Y End Loop N Insert v_recno, v_ctr into testloop Pass test v>recno < 6 rows in testloop v_recno v_ctr Initialize 1 100 Pass 1 1 < 6 is YES -LOOP 1 100 2 110 Pass 2 2 < 6 is YES - LOOP 2 110 3 120 Pass 3 3 < 6 is YES - LOOP 3 120 4 130 Pass 4 4 < 6 is YES - LOOP 4 130 5 140 Pass 5 5 < 6 is YES - LOOP 5 140 6 150 Pass 6 6 < 6 is NO - END LOOP (processing is not done) Add 1 to v_recno Add 1 to v_ctr End Loop END END Whileloop1

11. SYNTAX: FOR index in [REVERSE] lower_bound..upper_bound LOOP processing code END LOOP; For loop SQL> edit forloop1 DECLARE v_recno testloop.rec_no%TYPE :=1; v_ctr testloop.ctr%TYPE :=100; BEGIN FOR i IN 1..5 LOOP INSERT INTO testloop(rec_no, ctr) VALUES(v_recno, v_ctr); v_recno :=v_recno + 1; v_ctr := v_ctr + 10; END LOOP; END; / SQL> SELECT * FROM testloop; REC_NO CTR DATA_IN --------- --------- ---------- 1 100 2 110 3 120 4 130 5 140 SQL> @forloop1 PL/SQL procedure successfully completed.

12. For loop DECLARE v_recno testloop.rec_no%TYPE :=1; v_ctr testloop.ctr%TYPE :=100; BEGIN FOR i IN 1..5 LOOP INSERT INTO testloop(rec_no, ctr) VALUES(v_recno, v_ctr); v_recno :=v_recno + 1; v_ctr := v_ctr + 10; END LOOP; END; / Pass i range 1 to 5 rows in testloop v_recno v_ctr Initialize 1 100 Pass 1 i = 1 -LOOP 1 100 2 110 Pass 2 i = 2 -LOOP 2 110 3 120 Pass 3 i = 3 -LOOP 3 120 4 130 Pass 4 i = 4 -LOOP 4 130 5 140 Pass 5 i = 5 -LOOP 5 140 6 150 Maximum value i has been reached - END LOOP (processing is not done)