最近在使用Mysql时,经常需要用到游标,所以将使用方法做下记录以备日后查看。(这里只是为了说明游标的用法,不会涉及复杂的业务逻辑,所以举的例子可能并不恰当)
首先,创建两张表departmentinfo(部门信息表)和employeeinfo(员工信息表)
表结构如下:
表departmentinfo存储部门名及部门总销售额
表employeeinfo存储员工名、员工所在部门ID及员工个人销售额
表中初始数据如下:
各部门总销售额均为0
员工分别在3个部门下,且都有自己的销售额
然后创建存储过程P_ChangeSales,代码如下
CREATE DEFINER = 'root'@'localhost'
PROCEDURE test.P_ChangeSales(OUT ExtReturnValue INT)TOP:BEGIN
DECLARE EXIT HANDLER FORSQLEXCEPTIONBEGIN
ROLLBACK;SET ExtReturnValue = 0; --Failed
END;
STARTTRANSACTION;BEGIN
DECLARE isLeave INT; --是否要跳出循环,0:继续 1:跳转
DECLARE depId INT; --部门ID变量
DECLARE salesVal INT; --销售额变量
DECLARE curSales CURSOR FOR SELECT DepartmentID,Sales FROM employeeinfo; --声明游标
DECLARE CONTINUE HANDLER FOR NOT FOUND SET isLeave = 1; --出现溢出则赋值为1,作为跳出循环的判断
OPEN curSales; --开启游标
SET isLeave = 0;
curSales_loop:LOOPFETCH curSales INTOdepId, salesVal;IF isLeave = 1 THEN --循环结束
LEAVE curSales_loop;ELSE
UPDATE departmentinfo SET Sales = Sales + salesVal WHERE DepartmentID = depId; --增加部门总销售额
END IF;ENDLOOP curSales_loop;CLOSE curSales; --关闭游标
END;UPDATE employeeinfo SET Sales = 0; --员工个人销售额清0
SET ExtReturnValue = 1; --Success
COMMIT;END
现在我们执行上面的存储过程
存储过程执行成功,得到返回值1。查看表中数据
表departmentinfo中计算出的部门总销售额
表employeeinfo中员工的个人销售额变为0
至此,我们完成了Mysql中游标的简单应用。
还有一点需要注意,存储过程中变量的定义不要和我们表中的字段名相同,不然会有问题。例如,我们将存储过程中的所有”salesVal”全部改为”sales”与字段名”Sales”相同(不区分大小写)。此时保存修改,并不会报错,存储过程也可以执行,但是会得到如下返回值
我们的存储过程执行时出现问题,进行了回滚,才会得到这个返回值。
下面我们对存储过程进行调试发现,执行到”FETCH … INTO …”后,”depId”中得到正确的值,但是”sales”的值为NULL。
所以存储过程在执行到下面的sql语句时,出错回滚,这点大家一定要注意。