INPUT
Listing 6.3. Illegal GOTO call inside an IF statement.

 1: DECLARE
 2:      v_Emergency_Warning VARCHAR2(50);
 3:      v_Status NUMBER = 0;
 4:      v_ReactorStatus VARCHAR2(10);
 5: BEGIN
 6:      GOTO Emergency_Check;
 7:      IF v_ReactorStatus = `Very Hot' THEN
 8:           <<Emergency_Check>>
 9:                PANIC();
10:      END IF;
11:  END;

From the GOTO call in Listing 6.3, if this block of PL/SQL code were allowed to actually execute, it would never check to see if v_ReactorStatus = `Very Hot'. There might not even be an emergency because v_ReactorStatus could have a value of `Cool'. Because the value is never evaluated, the program always goes into crisis mode. Fortunately, this improper use of GOTO is not allowed!

Jumping from One Part of an IF Statement to Another
Although you can call a label from an IF statement, it is illegal for the jump to go from the IF clause to the THEN clause. Listing 6.4 is yet another example of not being within the same scope as the GOTO.

INPUT
Listing 6.4. Illegal GOTO call from one clause of an IF statement to another clause.

 1: DECLARE
 2:      v_Emergency_Warning VARCHAR2(50);
 3:      v_Status NUMBER = 0;
 4:      v_ReactorStatus VARCHAR2(10);
 5: BEGIN
 6:      IF v_ReactorStatus = `Very Hot' THEN
 7:           GOTO Emergency_Check;
 8:      ELSE
 9:           <<Emergency_Check>>
10:                PANIC();
11:      END IF;
12:  END;

As Listing 6.4 suggests, the program is jumping from an evaluation of the IF statement as true to executing code as if the entire statement were false. This is a definite misuse of the GOTO statement, and the code in this case probably does not require a GOTO statement.

From Listing 6.5, it should be apparent that you can't raise an error and then go back to the original block of code where the error was generated from the exception handler.

INPUT
Listing 6.5. Illegal GOTO call from an exception handler.

 1: DECLARE
 2:      v_Emergency_Warning VARCHAR2(50);
 3:      v_Status NUMBER = 0;
 4:      v_ReactorStatus VARCHAR2(10);
 5: BEGIN
 6:      <<Emergency_Check>>
 7:           PANIC();
 8: EXCEPTION
 9:      WHEN e_TOOHOT THEN
10:           GOTO Emergency_Check;
11:  END;

An Example of the GOTO Statement in Action

So far, you have seen conditions that exceed the scope of the GOTO statement. Now, here's an example of a legitimate block of PL/SQL code. See Listing 6.6 for a proper GOTO.

INPUT
Listing 6.6. Example of a proper GOTO statement.

 1: DECLARE
 2:      v_Status NUMBER := 1;
 3: BEGIN
 4:      IF v_Status = 1 THEN
 5:           GOTO mybranch;
 6:      ELSE
 7:           v_Status := 1;
 8:      END IF;
 9: <<mybranch>>
10:      NULL;
11: END;

In the GOTO example from Listing 6.6, the program checks the value of v_Status. If the value is equal to 1, then the program goes immediately to the block <<mybranch>>; if the value is false, the program changes the value of v_Status to equal 1.

Why Use the GOTO Statement?

As with any procedural language, the use of GOTO statements is highly discouraged. As you saw from the listings earlier in the chapter, GOTO statements are very easy to code improperly. In almost all cases, your code can and should be written to avoid the unnecessary use of GOTO. There are several reasons not to use the GOTO statement:

• It is very easy to make logic errors when using GOTO.
• It is very easy to make coding errors even when you are trying to make the process work.
• If you use multiple GOTO statements, your code will jump all over the place out of sequence, which is known as spaghetti code. Using multiple GOTOs not only causes longer execution times, but also leads to confusion when you try to review your code and make changes.
• Almost all cases in which you would use the GOTO statement can be written with other Oracle constructs.

Perhaps the only use of GOTO statements is to immediately stop all other execution of statements and branch to a section of code to handle an emergency situation.

WHILE Loops

The WHILE loop enables you to evaluate a condition before a sequence of statements would be executed. In fact, if the condition is false, the code would never be executed. This is different from the FOR loop where you must execute the loop at least once.

The syntax of the WHILE loop is

WHILE <condition is true> LOOP
     <statements>
END LOOP;

The WHILE loop requires the keywords LOOP and END LOOP in order to designate the statements to execute.

Examples of WHILE Loops

All these WHILE loops are meant to be entered and executed so that you can get some experience coding WHILE loops.

You can enter the loops directly or use the EDIT command to save a file, which can be executed at any time. Listing 6.7 demonstrates how the conditions for a WHILE loop can cause the loop to never execute.