The GOTO statement causes the point of execution to jump to the statement with the specified label. The following example shows the syntax of the GOTO statement:

GOTO label

label is a name assigned to an executable statement. label must be unique within the scope of the function, procedure, or anonymous block.

To label a statement, use the following syntax:

<<label>> statement

statement is the point of execution that the program jumps to.

You can label assignment statements, any SQL statement (such as INSERT, UPDATE, and CREATE), and selected procedural language statements. The following procedural language statements can be labeled:

  • IF
  • EXIT
  • RETURN
  • RAISE
  • EXECUTE
  • PERFORM
  • GET DIAGNOSTICS
  • OPEN
  • FETCH
  • MOVE
  • CLOSE
  • NULL
  • COMMIT
  • ROLLBACK
  • GOTO
  • CASE
  • LOOP
  • WHILE
  • FOR

Note that exit is considered as a keyword, and cannot be used as the name of a label.

GOTO statements cannot transfer control into a conditional block or sub-block, but can transfer control from a conditional block or sub-block.

The following example verifies that an employee record contains a name, a job description, and an employee hire date. If any piece of information is missing, a GOTO statement transfers the point of execution to a statement that prints a message that the employee is not valid.

CREATE OR REPLACE PROCEDURE verify_emp (
    p_empno         NUMBER
)
IS
    v_ename         emp.ename%TYPE;
    v_job           emp.job%TYPE;
    v_hiredate      emp.hiredate%TYPE;
BEGIN
    SELECT ename, job, hiredate
        INTO v_ename, v_job, v_hiredate FROM emp
        WHERE empno = p_empno;
    IF v_ename IS NULL THEN
        GOTO invalid_emp;
    END IF;
    IF v_job IS NULL THEN
        GOTO invalid_emp;
    END IF;
    IF v_hiredate IS NULL THEN
        GOTO invalid_emp;
    END IF;
    DBMS_OUTPUT.PUT_LINE('Employee ' || p_empno ||
        ' validated without errors.') ;
    RETURN;
    <<invalid_emp>> DBMS_OUTPUT.PUT_LINE('Employee ' || p_empno ||
        ' is not a valid employee.') ;
END;

GOTO statements have the following restrictions:

  • A GOTO statement cannot jump to a declaration.
  • A GOTO statement cannot transfer control to another function or procedure.

A label should not be placed at the end of a block, function, or procedure.