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=================================================