GOTO statement

Syntax

Description

label

Specifies the label for the GOTO statement. The label name cannot be the same as the routine name or advanced trigger name in which the label is used or another label in the same scope. End of change

target-label

Specifies a label of the statement where processing is to continue. target-label must be defined as a label for an SQL procedure statement. The target label must be accessible to the GOTO statement as defined in References to SQL labels in SQL PL, subject to the following restrictions:

If the GOTO statement is in a condition handler, target-label must be defined in that condition handler.
If the GOTO statement is not defined in a condition handler, target-label must not be defined in a condition handler.

Notes

Using a GOTO statement: It is recommended that the GOTO statement be used sparingly. This statement interferes with the normal sequence of processing SQL statements, thus making a routine more difficult to read and maintain. Before using a GOTO statement, determine whether another statement, such as IF or LEAVE, can be used in place, to eliminate the need for a GOTO statement.

Effect on open cursors: When a GOTO statement transfers control out of a compound statement, all open cursors that are declared in the compound statement that contains the GOTO statement are closed, except cursors that are used to return result sets.

Examples

Example 1: In the following procedure, the GOTO statement branches outside of the current compound statement to a higher level:

CREATE PROCEDURE TESTGOTO5 ( )
P1: BEGIN
   DECLARE I ,A INTEGER;
     SET I = 1;
  LAB1: SET A = 1;
    BEGIN
      LAB2: SET A = 2;
         BEGIN
           SET I = I+1;
           IF I<3 THEN GOTO LAB1;
           END IF;
         END;
    END;
END P1

Example 2: In the following example, cursors are declared at multiple levels. The GOTO statement that specified TargLabel as the target label, results in the closing of cursors C1, C2, and C3®. This is because cursors C1, C2, and C3 are all declared directly or indirectly in the compound statement with the label L1. The GOTO statement causes control to transfer out of the compound statement with label L1, so the cursors that are defined within that compound statement (at any level) are closed.

L0: BEGIN
      DECLARE CURSOR C0 ...
  ...
  TARGLABEL: ...
  ...
  L1: BEGIN
        DECLARE CURSOR C1 ...
        ...
      L2: BEGIN
            DECLARE CURSOR C2 ...
            ...
            GOTO TARGLABEL;
            ...
          L3: BEGIN
               DECALUE CURSOR C3 ...
            ...
          END L3;
      END L2;
   END L1;
END L0