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 ); |
Discuss this page in DocCommentXchange. Send feedback about this page using email. |
Copyright © 2009, iAnywhere Solutions, Inc. - SQL Anywhere 11.0.1 |