mysql 游标 demo

2022-10-05 12:15:52

一、MySQL游标的概念

1、游标介绍

MySQL的游标（cursor）是一个重要的概念，通过查找资料与自己的理解，主要得出以下几点关于自己的理解。

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

2、游标优缺点：

（1）游标的优点：

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

（2）游标缺点

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

3、游标的使用场景

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

二、游标的使用

1、游标的使用步骤

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

（1）.定义游标

DECLARE <游标名> CURSOR FOR select语句;

  -- 声明游标

  DECLARE my_cursor CURSOR FOR select style_id,id from car_type;

（2）.打开游标

open <游标名>

  -- 打开游标

  OPEN my_cursor;

（3）.使用游标

使用游标需要用关键字fetch来取出数据，然后取出的数据需要有存放的地方，我们需要用declare声明变量存放列的数据其语法格式为：

declare 变量1 数据类型(与列值的数据类型相同)

declare 变量2 数据类型(与列值的数据类型相同)

declare 变量3 数据类型(与列值的数据类型相同)

  -- 需要定义接收游标数据的变量

  DECLARE styleId bigint(19);

  DECLARE typeId bigint(19);

  -- 遍历数据结束标志

  DECLARE done INT DEFAULT FALSE;

  -- 将结束标志绑定到游标

  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

  -- 打开游标

  OPEN my_cursor;

  -- 开始循环

  read_loop: LOOP

    -- 提取游标里的数据，这里只有一个，多个的话也一样；

    FETCH cur INTO styleId,typeId;

    -- 声明结束的时候

    IF done THEN

      LEAVE read_loop;

    END IF;

    -- 这里做你想做的循环的事件

      INSERT INTO `test` VALUES (a,b) ON DUPLICATE KEY UPDATE `a`=c;

  END LOOP;

（4）.关闭游标：

  -- 关闭游标

  CLOSE my_cursor;

（5）.释放游标

demo :

-- 定义的游标变量和游标中使用的变量，必须定义在变量之后。

-- 在MySql中，造成游标溢出时会引发mysql预定义的NOT FOUND错误，
-- 所以在上面使用下面的代码指定了当引发not found错误时定义一个continue 的事件，指定这个事件发生时修改done变量的值。

use das;

-- 查看存储过程

SHOW PROCEDURE STATUS;

-- 显示pro存储过程的详细信息

SHOW CREATE PROCEDURE pro;

-- 删除pro存储过程

DROP PROCEDURE IF EXISTS pro;

select style_id,id from car_type;

select count(1) from car_type;

-- 创建简单的存储过程

DELIMITER //

DROP PROCEDURE IF EXISTS pro//

CREATE PROCEDURE pro()

BEGIN

  -- 需要定义接收游标数据的变量

  DECLARE styleId bigint(19);

  DECLARE typeId bigint(19);

  DECLARE co bigint(19) default 0;

  -- 定义的游标变量和游标中使用的变量，必须定义在变量之后。

  DECLARE done INT DEFAULT FALSE;

  -- 定义游标

  DECLARE my_cursor CURSOR FOR select style_id,id from car_type;

  -- 在MySql中，造成游标溢出时会引发mysql预定义的NOT FOUND错误，

  -- 所以在上面使用下面的代码指定了当引发not found错误时定义一个continue 的事件，指定这个事件发生时修改done变量的值。

  DECLARE CONTINUE HANDLER for not found set done = true;

  -- 打开游标

  OPEN my_cursor;

  -- 开始循环

  read_loop: LOOP

    -- 提取游标里的数据，这里只有一个，多个的话也一样；

    FETCH my_cursor INTO styleId,typeId;

    -- 声明结束的时候

    IF done THEN

      LEAVE read_loop;

    END IF;

    -- 这里做你想做的循环的事件

    set co = co + 1;

  END LOOP;

  -- 关闭游标

  CLOSE my_cursor;

  select co;

END//

DELIMITER ;

-- 调用存储过程

CALL pro();

demo :

-- getStockAmplitudeGoodsSum

use das;

-- 创建简单的存储过程

DELIMITER //

DROP PROCEDURE IF EXISTS getStockAmplitudeGoodsSum //

CREATE PROCEDURE getStockAmplitudeGoodsSum(IN storeBaseId INT, IN productGoodsId INT, OUT totalAmount INT)

BEGIN

  DECLARE type char(200);

  DECLARE amount int(11);

  declare clacAmount int(11) default 0;

  declare co int(11) default 0;

  DECLARE done INT DEFAULT FALSE;

  DECLARE my_cursor CURSOR FOR

  SELECT f.type,e.amount

  FROM stock_amplitude_goods as e left join stock_amplitude as f on e.stock_amplitude_id = f.id

  WHERE f.store_base_id = storeBaseId and e.product_goods_id = productGoodsId;

  DECLARE CONTINUE HANDLER for not found set done = true;

  -- 打开游标

  OPEN my_cursor;

  -- 开始循环

  read_loop: LOOP

    -- 提取游标里的数据，这里只有一个，多个的话也一样；

    FETCH my_cursor INTO type,amount;

    -- 声明结束的时候

    IF done THEN

      LEAVE read_loop;

    END IF;

    IF type is not null THEN

            set clacAmount = clacAmount + amount;

    END IF;

  END LOOP;

  -- 关闭游标

  CLOSE my_cursor;

  set totalAmount = clacAmount;

END//

DELIMITER ;

-- pro

use das;

DELIMITER //

DROP PROCEDURE IF EXISTS pro//

CREATE PROCEDURE pro(IN storeBaseId INT)

BEGIN

    declare brandId bigint(19);

    declare brandName char(255);

    declare seriesId bigint(19);

    declare seriesName char(255);

    declare styleId bigint(19);

    declare styleName char(255);

    declare typeId bigint(19);

    declare typeName char(255);

    declare serviceBaseId bigint(19);

    declare serviceBaseName char(255);

    declare serviceVariantId bigint(19);

    declare serviceVariantName char(255);

    declare productCategoryGoodsId bigint(19);

    declare productCategorySuitType char(200);

    declare amount int(11);

    declare deficiency boolean default false;

    declare totalAmount int(11);

    declare productGoodsId int(11);

    declare coTrue int default 0;

    declare coFalse int default 0;

    DECLARE done INT DEFAULT FALSE;

    DECLARE my_cursor CURSOR FOR

        select b.brand_id,b.brand_name,b.series_id,b.series_name,b.style_id,b.style_name,b.id as carTypeId, b.name as carTypeName,c.service_base_id,c.service_base_name,c.id as serviceVariantId,c.name as serviceVariantName,d.product_category_goods_id,d.product_category_suit_type,d.amount, true as _deficiency

        from variant_car_type as a left join car_type as b on a.car_type_id = b.id

        left join service_variant as c on a.service_variant_id = c.id

        left join variant_goods as d on c.id = d.service_variant_id;

    DECLARE CONTINUE HANDLER for not found set done = true;  

    CREATE TEMPORARY TABLE IF NOT EXISTS resultTable

    (

        brandId bigint(19),

        brandName char(255),

        seriesId bigint(19),

        seriesName char(255),

        styleId bigint(19),

        styleName char(255),

        typeId bigint(19),

        typeName char(255),

        serviceBaseId bigint(19),

        serviceBaseName char(255),

        serviceVariantId bigint(19),

        serviceVariantName char(255),

        productCategoryGoodsId bigint(19),

        productGoodsId bigint(19),

        productCategorySuitType char(200),

        amount int(11),

        totalAmount int(11),

        deficiency boolean

    );

    truncate table resultTable;

    OPEN my_cursor;

    read_loop: LOOP

        FETCH my_cursor INTO brandId,brandName,seriesId,seriesName,styleId,styleName,typeId,typeName,serviceBaseId,serviceBaseName,serviceVariantId,serviceVariantName,productCategoryGoodsId,productCategorySuitType,amount,deficiency;

        IF done THEN

            LEAVE read_loop;

        END IF;

        IF productCategorySuitType is not null THEN

            IF productCategorySuitType = 'COMMON_USE' THEN

                call getStockAmplitudeGoodsSum(storeBaseId,productCategoryGoodsId,totalAmount);

                set productGoodsId = productCategoryGoodsId;

                IF amount >  totalAmount THEN

                    set deficiency = true;

                    set coTrue = coTrue + 1;

                ELSE

                    set deficiency = false;

                    set coFalse = coFalse + 1;

                END IF;

            ELSE

                SELECT product_goods_id into productGoodsId FROM das.product_suit_car as g where g.product_category_id = productCategoryGoodsId and car_type_id = typeId;

                call getStockAmplitudeGoodsSum(storeBaseId,productGoodsId,totalAmount);

                IF amount > totalAmount THEN

                    set deficiency = true;

                    set coTrue = coTrue + 1;

                ELSE

                    set deficiency = false;

                    set coFalse = coFalse + 1;

                END IF;

            END IF;

        END IF;

        insert into resultTable(

            brandId,brandName,seriesId,seriesName,styleId,styleName,typeId,typeName,serviceBaseId,serviceBaseName,serviceVariantId,serviceVariantName,productCategoryGoodsId,productGoodsId,productCategorySuitType,amount,totalAmount,deficiency

        )

        values(

            brandId,brandName,seriesId,seriesName,styleId,styleName,typeId,typeName,serviceBaseId,serviceBaseName,serviceVariantId,serviceVariantName,productCategoryGoodsId,productGoodsId,productCategorySuitType,amount,totalAmount,deficiency

        );

    END LOOP;

    -- 关闭游标

    CLOSE my_cursor;

    select * from resultTable;

    select coTrue/coFalse;

END//

DELIMITER ;

call pro(2);

啦啦啦

码农公寓