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:
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:
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:
- GET DIAGNOSTICS
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.