Oracle PL/SQL/PL SQL/GOTO

Example of a proper GOTO statement

  
SQL>
SQL> -- Example of a proper GOTO statement.
SQL> DECLARE
  2        s NUMBER := 1;
  3  BEGIN
  4       IF s = 1 THEN
  5            GOTO mybranch;
  6       ELSE
  7            s := 1;
  8       END IF;
  9       <<mybranch>>
 10       DBMS_OUTPUT.PUT_LINE("www.sqle.ru");
 11       NULL;
 12  END;
 13  /
www.sqle.ru
PL/SQL procedure successfully completed.
SQL>

GOTO a Label

   
SQL>
SQL>
SQL>    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;
 12  /
PL/SQL procedure successfully completed.
SQL> --

GOTO statement.

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

This script demonstrates GOTO

    
SQL>
SQL>
SQL> PROMPT ** GOTO
** GOTO
SQL>
SQL> SET SERVEROUTPUT ON
SQL> BEGIN
  2     DBMS_OUTPUT.PUT_LINE("BEGINNING OF BLOCK");
  3
  4     GOTO l_Last_Line;
  5
  6     DBMS_OUTPUT.PUT_LINE("GOTO didn""t work!");
  7     RETURN;
  8     <<l_Last_Line>>
  9     DBMS_OUTPUT.PUT_LINE("Last Line");
 10  END;
 11  /
BEGINNING OF BLOCK
Last Line
PL/SQL procedure successfully completed.

Use GoTO to jump out of a loop

   
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 count");
  7      v_Counter := v_Counter + 1;
  8      IF v_Counter > 50 THEN
  9        GOTO l_EndOfLoop;
 10      END IF;
 11    END LOOP;
 12
 13    <<l_EndOfLoop>>
 14    INSERT INTO MyTable (char_col)
 15      VALUES ("Done!");
 16  END;
 17  /
PL/SQL procedure successfully completed.
SQL>
SQL> select * from MyTable;
   NUM_COL CHAR_COL
---------- ------------------------------------------------------------
         1 Loop count
         2 Loop count
         3 Loop count
         4 Loop count
         5 Loop count
         6 Loop count
         7 Loop count
         8 Loop count
         9 Loop count
        10 Loop count
        11 Loop count
   NUM_COL CHAR_COL
---------- ------------------------------------------------------------
        12 Loop count
        13 Loop count
        14 Loop count
        15 Loop count
        16 Loop count
        17 Loop count
        18 Loop count
        19 Loop count
        20 Loop count
        21 Loop count
        22 Loop count
   NUM_COL CHAR_COL
---------- ------------------------------------------------------------
        23 Loop count
        24 Loop count
        25 Loop count
        26 Loop count
        27 Loop count
        28 Loop count
        29 Loop count
        30 Loop count
        31 Loop count
        32 Loop count
        33 Loop count
   NUM_COL CHAR_COL
---------- ------------------------------------------------------------
        34 Loop count
        35 Loop count
        36 Loop count
        37 Loop count
        38 Loop count
        39 Loop count
        40 Loop count
        41 Loop count
        42 Loop count
        43 Loop count
        44 Loop count
   NUM_COL CHAR_COL
---------- ------------------------------------------------------------
        45 Loop count
        46 Loop count
        47 Loop count
        48 Loop count
        49 Loop count
        50 Loop count
           Done!
51 rows selected.
SQL>
SQL> drop table MyTable;
Table dropped.

Use sequential control with the GOTO statement and a block label

    
SQL>
SQL> DECLARE
  2    counter NUMBER := 1;
  3  BEGIN
  4    WHILE (counter < 5) LOOP
  5      IF counter = 2 THEN
  6        GOTO loopindex;
  7      ELSE
  8        dbms_output.put_line("Index ["||counter||"].");
  9      END IF;
 10      << loopindex >>
 11      IF counter >= 1 THEN
 12        counter := counter + 1;
 13      END IF;
 14    END LOOP;
 15  END;
 16  /
Index [1].
Index [3].
Index [4].
PL/SQL procedure successfully completed.