GOTO Statement v11

The GOTO statement causes the point of execution to jump to the statement with the specified label. The syntax of a GOTO statement is:

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 syntax:

<<label>> <statement>

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

You can label assignment statements, any SQL statement (like INSERT, UPDATE, CREATE, etc.) and selected procedural language statements. The procedural language statements that can be labeled are:

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

Please note that exit is considered 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, job description, and 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.