MySQL游标和if怎么一起使用 mysql游标

转载

imking 2023-05-28 15:41:38

文章标签 mysql 存储过程 Sales 数据 文章分类 MySQL 数据库

1、游标（cursor）介绍

　　有数据缓冲的思想：游标的设计是一种数据缓冲区的思想，用来存放SQL语句执行的结果。先有数据基础：游标是在先从数据表中检索出数据之后才能继续灵活操作的技术。类似于指针：游标类似于指向数据结构堆栈中的指针，用来pop出所指向的数据，并且只能每次取一个。

2、游标优缺点

1）游标的优点：

　　因为游标是针对行操作的，所以对从数据库中select查询得到的每一行可以进行分开的独立的相同或不同的操作，是一种分离的思想。可以满足对某个结果行进行特殊的操作。游标与基于游标位置的增删改查能力。 MySQL数据库中没有专门描述一行的表达形式，但这是需要的，所以，个人理解的话，我觉得游标是在关系数据库这种面向集合的系统中抽离出来，单独针对行进行表达（也可以理解成网上资料说的：游标是面向集合与面向行的设计思想之间的一种桥梁）

2）游标缺点

　　游标的缺点是针对有点而言的，也就是只能一行一行操作，在数据量大的情况下，是不适用的，速度过慢。这里有个比喻就是：当你去ATM存钱是希望一次性存完呢，还是100一张一张的存，这里的100一张一张存就是游标针对行的操作。数据库大部分是面对集合的，业务会比较复杂，而游标使用会有死锁，影响其他的业务操作，不可取。当数据量大时，使用游标会造成内存不足现象。

3、游标的使用场景

　　针对游标的优缺点，我总结游标的使用场景，主要用在循环处理、存储过程、函数中使用，用来查询结果集，就比如：我们需要从表中循环判断并得到想要的结果集，这时候使用游标操作很方便速度也很快。

4、游标的使用步骤

　　游标的使用一般分为5个步骤，主要是：定义游标->打开游标->使用游标->关闭游标->释放游标。

5、下面看个例子

张表departmentinfo(部门信息表)和employeeinfo(员工信息表)。

MySQL游标和if怎么一起使用 mysql游标_数据

表结构如下：

departmentinfo存储部门名及部门总销售额

MySQL游标和if怎么一起使用 mysql游标_mysql_02

employeeinfo存储员工名、员工所在部门ID及员工个人销售额

MySQL游标和if怎么一起使用 mysql游标_数据_03

表中初始数据如下：

各部门总销售额均为0

MySQL游标和if怎么一起使用 mysql游标_Sales_04

员工分别在3个部门下，且都有自己的销售额

MySQL游标和if怎么一起使用 mysql游标_存储过程_05

然后创建存储过程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

执行上面的存储过程

MySQL游标和if怎么一起使用 mysql游标_mysql_06