The GOTO statement is used to branch to a user-defined label within an SQL procedure.
This statement can only be embedded in an SQL procedure. It is not an executable statement and cannot be dynamically prepared.
- Specifies a labelled statement where processing is to continue.
The labelled statement and the GOTO statement must be in the same
- If the GOTO statement is defined in a FOR statement, label must be defined inside the same FOR statement, excluding a nested FOR statement or nested compound statement
- If the GOTO statement is defined in a compound statement, label must be defined inside the same compound statement, excluding a nested FOR statement or nested compound statement
- If the GOTO statement is defined in a handler, label must be defined in the same handler, following the other scope rules
- If the GOTO statement is defined outside of a handler, label must not be defined within a handler.
- It is recommended that the GOTO statement be used sparingly. This statement interferes with normal processing sequences, 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.
In the following compound statement, the parameters rating and v_empno are passed into the procedure, which then returns the output parameter return_parm as a date duration. If the employee's time in service with the company is less than 6 months, the GOTO statement transfers control to the end of the procedure, and new_salary is left unchanged.
CREATE PROCEDURE adjust_salary (IN v_empno CHAR(6), IN rating INTEGER, OUT return_parm DECIMAL (8,2)) MODIFIES SQL DATA LANGUAGE SQL BEGIN DECLARE new_salary DECIMAL (9,2); DECLARE service DECIMAL (8,2); SELECT SALARY, CURRENT_DATE - HIREDATE INTO new_salary, service FROM EMPLOYEE WHERE EMPNO = v_empno; IF service < 600 THEN GOTO EXIT; END IF; IF rating = 1 THEN SET new_salary = new_salary + (new_salary * .10); ELSEIF rating = 2 THEN SET new_salary = new_salary + (new_salary * .05); END IF; UPDATE EMPLOYEE SET SALARY = new_salary WHERE EMPNO = v_empno; EXIT: SET return_parm = service; END