mysql 存储过程 游标 示例

前言：为了实现导入遗漏的数据库数据，需要先查出遗漏的数据，用存储过程来循环批量导入，但是由于mysql中没有数组这个类型，做了一个很笨的办法，每次查询都LIMIT 1 ，调补数据再查询，直到查LIMIT 1出来为null。做完之后才知道还有游标这一概念，于是就将代码改进了下，发现游标的写法和我之前用笨方法有点相似之处。请看代码

=======================原先Mysql的存储过程代码==================================

DROP PROCEDURE IF EXISTS contract_Insert;
delimiter //
CREATE PROCEDURE contract_Insert()
  BEGIN
    DECLARE orderGiftId VARCHAR(64);
    DECLARE startDate DATETIME;
    DECLARE endDate DATE;
    DECLARE cno VARCHAR(15);

    SET orderGiftId =
    (SELECT order_gift.id FROM order_gift
      LEFT JOIN `order` ON order_gift.order_id = `order`.id
      LEFT JOIN order_contract ON order_gift.id = order_contract.order_gift_id
    WHERE pay_status=1 AND order_contract.id IS NULL LIMIT 1);

    WHILE (orderGiftId is not null)DO
      SET startDate = (SELECT create_date FROM order_gift WHERE id = orderGiftId);
      SET endDate = date_add(startDate,interval 1 YEAR );
      SET cno = DATE_FORMAT(startDate,'%Y%m%h%d%s');

      INSERT INTO order_contract(
        id, no, type, status, end_date,parent_id, order_gift_id, create_date,del_flag, start_date, remarks,file_path
      )VALUES (REPLACE(UUID(), '-', ''),concat('ZD',cno),'0','0',endDate,'0',orderGiftId, now(),'0',startDate,'SQL补充导入',concat(concat('/userfiles/contract/ZD/',concat('ZD',cno)),'.doc')),
        (REPLACE(UUID(), '-', ''),concat('XS',cno),'1','0',endDate,'0',orderGiftId, now(),'0',startDate,'SQL补充导入',concat(concat('/userfiles/contract/XS/',concat('XS',cno)),'.doc'));
      #获取下一行数据
      SET orderGiftId =
      (SELECT order_gift.id FROM order_gift
        LEFT JOIN `order` ON order_gift.order_id = `order`.id
        LEFT JOIN order_contract ON order_gift.id = order_contract.order_gift_id
      WHERE pay_status=1 AND order_contract.id IS NULL LIMIT 1);

    END WHILE;
  END;
//
CALL contract_Insert();

===============================end===============================================

==========================使用游标的存储过程========================================

DROP PROCEDURE IF EXISTS contract_Insert;
delimiter //
CREATE PROCEDURE contract_Insert()
  BEGIN
    DECLARE stop int default 0;#终止标记
    DECLARE orderGift_id VARCHAR(64);
    DECLARE orderGift_date DATETIME;
    DECLARE endDate DATE;
    DECLARE cno VARCHAR(15);

  #声明游标
    declare orderGift cursor for (SELECT order_gift.id , order_gift.create_date FROM order_gift
      LEFT JOIN `order` ON order_gift.order_id = `order`.id
      LEFT JOIN order_contract ON order_gift.id = order_contract.order_gift_id
    WHERE pay_status=1 AND order_contract.id IS NULL);
  # 声明游标的异常处理，设置一个终止标记
    declare CONTINUE HANDLER FOR SQLSTATE '02000' SET stop=1;

    OPEN orderGift;

    #获取数据到变量
    FETCH orderGift INTO orderGift_id, orderGift_date;
    #判断是否终止
    WHILE stop <> 1 DO

      SET endDate = date_add(orderGift_date,interval 1 YEAR );
      SET cno = DATE_FORMAT(orderGift_date,'%Y%m%h%d%s');

      INSERT INTO order_contract(
        id, no, type, status, end_date,parent_id, order_gift_id, create_date,del_flag, start_date, remarks,file_path
      )VALUES (REPLACE(UUID(), '-', ''),concat('ZD',cno),'0','0',endDate,'0',orderGift_id, now(),'0',orderGift_date,'SQL补充导入',concat(concat('/userfiles/contract/ZD/',concat('ZD',cno)),'.doc')),
        (REPLACE(UUID(), '-', ''),concat('XS',cno),'1','0',endDate,'0',orderGift_id, now(),'0',orderGift_date,'SQL补充导入',concat(concat('/userfiles/contract/XS/',concat('XS',cno)),'.doc'));
      #获取下一行数据
      FETCH  orderGift INTO orderGift_id, orderGift_date;
    END WHILE;

    #关闭游标
    CLOSE orderGift;
  END;
//
CALL contract_Insert();

================================end=================================================