The GOTO statement is used to branch to a user-defined label within an SQL procedure.
- Specifies a labeled statement at which processing is to continue.
The labeled statement and the GOTO statement must be in the same scope. The following rules apply to the scope:
- If the GOTO statement is defined in a compound statement, label must be defined inside the same compound statement.
- If the GOTO statement is defined in a handler, label must be defined in the same handler and follow the other scope rules.
- If the GOTO statement is defined outside of a handler, label must not be defined within a handler.
If label is not defined within a scope that the GOTO statement can reach, an error is returned.
A label name cannot be the same as the name of the SQL procedure in which the label is used.
Use the GOTO statement sparingly. Because the GOTO statement interferes with the normal sequence of processing, it makes an SQL procedure more difficult to read and maintain. Before using a GOTO statement, determine whether some other statement, such as an IF statement or LEAVE statement, can be used instead.
Use a GOTO statement to transfer control to the end of a compound statement if the value of an SQL variable is less than 600.
BEGIN DECLARE new_salary DECIMAL(9,2); DECLARE service DECIMAL(8,2); SELECT SALARY, CURRENT_DATE - HIREDATE INTO new_salary, service FROM EMP 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 EMP SET SALARY = new_salary WHERE EMPNO = v_empno; EXIT: SET return_parm = service; END