Click here to view and discuss this page in DocCommentXchange. In the future, you will be sent there automatically.
The following procedure uses an updatable cursor on a SELECT statement. It illustrates how to perform an UPDATE on a row using the stored procedure language.
CREATE PROCEDURE UpdateSalary( IN employeeIdent INT, IN salaryIncrease NUMERIC(10,3) ) BEGIN -- Procedure to increase (or decrease) an employee's salary DECLARE err_notfound EXCEPTION FOR SQLSTATE '02000'; DECLARE oldSalary NUMERIC(20,3); DECLARE employeeCursor CURSOR FOR SELECT Salary from Employees WHERE EmployeeID = employeeIdent FOR UPDATE; OPEN employeeCursor; FETCH employeeCursor INTO oldSalary FOR UPDATE; IF SQLSTATE = err_notfound THEN MESSAGE 'No such employee' TO CLIENT; ELSE UPDATE Employees SET Salary = oldSalary + salaryIncrease WHERE CURRENT OF employeeCursor; END IF; CLOSE employeeCursor; END
The following statement calls the above stored procedure:
CALL UpdateSalary( 105, 220.00 );