Oracle PL/SQL

Speaker Notes

Slide 1:

This presentation deals with looping in PL/SQL. We will look at: Basic Loops, While Loops and For...Loops.

Please be sure to read the notes - additional information is covered there.

Slide 2:

Please read the more complete explanation in the notes - Oracle PL/SQL loops.

Note that there can be multiple EXIT commands within the loop. From a programming point of view, I prefer the style of exiting once at the end of the loop.

LOOP and END LOOP delineate the loop - all the code between LOOP and END LOOP is code within the LOOP.

The EXIT provides a way to end the looping process and continue with the rest of the code. The EXIT will EXIT to the code directly after the END LOOP. In this example, there is no additional code, instead the END is encountered and the block will terminate.

The results of executing the code in this block are shown.

Slide 3:

This slide shows the looping action using the code from the previous slide.

The initialization is shown and then the activity within the pass is show sequentially.

Slide 4:

Instead of using the IF to determine if v_recno > 5, this code uses the EXIT WHEN to determine when to exit the loop.

Slide 5:

Note that the IF statement is expressed with a NOT here (essentially it is the reverse of the IF in the code). This is done because the logic is easier to show in the flowchart. The IF statement than says as long as it is not greater than 5 continue with the loop, when it is greater than 5 - drop through to the next instruction which is the END.

In the code, the test is v_recno > 5 EXIT. The logic is the same, it is simply a matter of representation.

NOTE in this type of loop, the loop is always entered and will be executed up until the EXIT even if v_recno had started at 6.

Slide 6:

The WHILE loop is my favorite. It conforms to a structured style and is similar to the loops you find in many other languages. The condition is stated at the beginning in the WHILE statement and as long as the condition is true the processing will loop. When the condition is no long true, processing drops to the statement after the END LOOP.

Slide 7:

This shows the code and the output being produced.

Slide 8:

Again note that the dotted lines are here only to show that when the loop is performed control is passed to the loop. When all commands in the loop have been executed, control returns to the loop which then goes back in to ask the v_recno < 6 question.

This is the type of flowchart that I prefer - I think it adds to the clarity.

Slide 9:

When LOOP is executed, the three commands in the LOOP module are processed. Control then returns to LOOP which sends control to the v_recno < 6 condition.

NOTE that with the WHILE LOOP, if the condition is not true, the LOOP is never entered. IF v_recno was initialized at 6, then the LOOP would never be entered - control would simply pass to END.

Slide 10:

Alternative flowchart. I prefer the style used in the previous slide. This is an unstructured style.

Slide 11:

Index is an implicitly declared integer which is automatically increment by 1 on each iteration until the appropriate bound is reached. Please see notes for additional definitions.

Slide 12:

The index i is automatically incremented for each pass - the loop is done when I has processed for its maximum value of 5.

The logic is the same as that shown in slide 9 for the WHILE LOOP except that the condition is based on i not passing 5. The initialization and incrementing of i are handled automatically and do not have to be shown in the logic.