The GOTO statement is a straightforward and basic flow of control statement that causes an unconditional change in the flow of control. It is used to branch to a specific user-defined location using labels defined in the SQL procedure.
Use of the GOTO statement is generally considered to be poor programming practice and is not recommended. Extensive use of GOTO tends to lead to unreadable code especially when procedures grow long. Besides, GOTO is not necessary because there are better statements available to control the execution path. There are no specific situations that require the use of GOTO; instead it is more often used for convenience.
CREATE PROCEDURE adjust_salary ( IN p_empno CHAR(6),
IN p_rating INTEGER,
OUT p_adjusted_salary DECIMAL (8,2) )
LANGUAGE SQL
BEGIN
DECLARE new_salary DECIMAL (9,2);
DECLARE service DATE; -- start date
SELECT salary, hiredate INTO v_new_salary, v_service
FROM employee
WHERE empno = p_empno;
IF service > (CURRENT DATE - 1 year) THEN
GOTO exit;
END IF;
IF p_rating = 1 THEN
SET new_salary = new_salary + (new_salary * .10);
END IF;
UPDATE employee SET salary = new_salary WHERE empno = p_empno;
exit:
SET p_adjusted_salary = v_new_salary;
END
This example demonstrates what of the good uses of the GOTO statement: skipping almost to the end of a procedure or loop so as not to execute some logic, but to ensure that some other logic does still get executed.