Mysql中游标的使用

2022-07-16 12:19:54

最近在使用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 FOR SQLEXCEPTION
  BEGIN
    ROLLBACK;
    SET ExtReturnValue = 0; -- Failed
  END;

  START TRANSACTION;

  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:LOOP
      FETCH curSales INTO depId, salesVal;
      IF isLeave = 1 THEN -- 循环结束
        LEAVE curSales_loop;
      ELSE
        UPDATE departmentinfo SET Sales = Sales + salesVal WHERE DepartmentID = depId; -- 增加部门总销售额
      END IF;
    END LOOP curSales_loop;

    CLOSE curSales; -- 关闭游标

  END;

  UPDATE employeeinfo SET Sales = 0; -- 员工个人销售额清0

  SET ExtReturnValue = 1; -- Success

  COMMIT;
END