Mysql存储过程循环内嵌套使用游标示例

Mysql存储过程循环内嵌套使用游标示例如下：

BEGIN
 -- 声明变量
 DECLARE v_addtime_begin varchar(13);
 DECLARE v_addtime_end varchar(13);

 DECLARE v_borrow_id int;
 DECLARE v_count int;
 DECLARE s1 int;
 
 /** 声明游标，查询2014-05-27至2014-07-28发布的抵押标，并将查询结果存到游标中 **/
 DECLARE c_borrow CURSOR FOR
 SELECT ID from rocky_borrow WHERE BORROWTYPE = 2 AND PUBLISH_TIME >=  UNIX_TIMESTAMP('2014-05-27') AND PUBLISH_TIME <= UNIX_TIMESTAMP('2014-07-30') ORDER by ID ASC;
 /**  获取查询数量 **/
 SELECT count(ID) INTO v_count from rocky_borrow WHERE BORROWTYPE = 2 AND PUBLISH_TIME >=  UNIX_TIMESTAMP('2014-05-27') AND PUBLISH_TIME <= UNIX_TIMESTAMP('2014-07-30') ORDER by ID ASC;
 -- 创建临时资金记录日志标
 DROP TABLE `rocky_accountlog_test`;
 CREATE TABLE `rocky_accountlog_test` (
  `ID` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `ACCOUNTLOG_ID` int(11) DEFAULT NULL,
  `USER_ID` int(11) NOT NULL COMMENT '用户ID',
  `TYPE` varchar(100) NOT NULL COMMENT '日志类型',
  `TOTAL` double(20,8) NOT NULL DEFAULT '0.00000000' COMMENT '账户总额',
  `MONEY` double(20,8) NOT NULL DEFAULT '0.00000000' COMMENT '操作金额',
  `USE_MONEY` double(20,8) NOT NULL DEFAULT '0.00000000' COMMENT '可用余额',
  `NO_USE_MONEY` double(20,8) NOT NULL DEFAULT '0.00000000' COMMENT '冻结金额',
  `COLLECTION` double(20,8) NOT NULL DEFAULT '0.00000000' COMMENT '待收总额',
  `TO_USER` int(11) NOT NULL DEFAULT '0' COMMENT '交易对方ID',
  `REMARK` varchar(1000) DEFAULT NULL COMMENT '日志备注',
  `ADDTIME` varchar(13) NOT NULL COMMENT '操作时间',
  `ADDIP` varchar(64) DEFAULT NULL COMMENT '插入IP',
  `FIRST_BORROW_USE_MONEY` double(20,8) DEFAULT NULL COMMENT '优先计划可用余额',
  `BORROW_ID` int(11) DEFAULT NULL,
  PRIMARY KEY (`ID`),
  KEY `USER_ID_INDEX` (`USER_ID`) USING BTREE,
  KEY `TO_USER_INDEX` (`TO_USER`) USING BTREE,
  KEY `TYPE_INDEX` (`TYPE`) USING BTREE,
  KEY `ADDTIME_INDEX` (`ADDTIME`) USING BTREE
 ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='账户历史记录测试表';

 SET s1 = 1;
  -- 开始事务
 START TRANSACTION;
 -- 打开游标
 OPEN c_borrow;
  -- 循环游标
   WHILE s1 < v_count+1 DO
   -- 遍历游标
   FETCH c_borrow INTO v_borrow_id;
   -- 获取自动投标第一笔记录的时间
   SELECT t1.addtime INTO v_addtime_begin FROM (SELECT * FROM rocky_b_tenderrecord bt WHERE BORROW_ID = v_borrow_id AND tender_type = 1 ORDER BY ID ASC) t1 GROUP BY t1.borrow_id;
   -- 获取自动投标最后一笔记录的时间
   SELECT t1.addtime INTO v_addtime_end FROM (SELECT * FROM rocky_b_tenderrecord bt WHERE BORROW_ID = v_borrow_id AND tender_type = 1 ORDER BY ID DESC) t1 GROUP BY t1.borrow_id;
   IF (v_addtime_begin IS NOT NULL) && (v_addtime_end IS NOT NULL) THEN
     -- 嵌套使用游标
     BEGIN
       DECLARE v_id int;
       DECLARE v_user_id int;
       DECLARE v_type varchar(20);
       DECLARE v_total decimal(20,8) DEFAULT 0;
       DECLARE v_money decimal(20,8) DEFAULT 0;
       DECLARE v_use_money decimal(20,8) DEFAULT 0;
       DECLARE v_no_use_money decimal(20,8) DEFAULT 0;
       DECLARE v_collection decimal(20,8) DEFAULT 0;
       DECLARE v_to_user int(11);
       DECLARE v_remark VARCHAR(1000);
       DECLARE v_addtime varchar(13);
       DECLARE v_addip varchar(64);
       DECLARE v_first_borrow_use_money decimal(20,8) DEFAULT 0;
       DECLARE done VARCHAR(45) DEFAULT '';
       DECLARE t_error int DEFAULT 0;
         
              -- 声明游标，查询某个时间段内同一用户投标冻结或还款扣除记录数大于1条的资金记录信息，将查询结果保存到游标中
       DECLARE c_accountlog CURSOR FOR
       SELECT ID,USER_ID,TYPE,TOTAL,MONEY,USE_MONEY,NO_USE_MONEY,COLLECTION,TO_USER,REMARK,ADDTIME,ADDIP,FIRST_BORROW_USE_MONEY FROM (
        SELECT ID,USER_ID,TYPE,TOTAL,MONEY,USE_MONEY,NO_USE_MONEY,COLLECTION,TO_USER,REMARK,ADDTIME,ADDIP,FIRST_BORROW_USE_MONEY FROM rocky_accountlog
        WHERE ADDTIME >= v_addtime_begin AND ADDTIME <= v_addtime_end AND (type = 'tender_cold' or type= 'repayment_deduct')
        ) t GROUP BY t.user_id HAVING count(t.user_id) > 1;
       
       DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = NULL;
       -- 打开游标 
        OPEN c_accountlog;
       -- 遍历游标
       FETCH c_accountlog INTO v_id,v_user_id,v_type,v_total,v_money,v_use_money,v_no_use_money,v_collection,v_to_user,v_remark,v_addtime,v_addip,v_first_borrow_use_money;
       WHILE (done IS NOT NULL) DO
         -- 将遍历的记录插入到rocky_accountlog_test表中
         INSERT INTO rocky_accountlog_test (ACCOUNTLOG_ID,USER_ID,TYPE,TOTAL,MONEY,USE_MONEY,NO_USE_MONEY,COLLECTION,TO_USER,REMARK,ADDTIME,ADDIP,FIRST_BORROW_USE_MONEY,BORROW_ID)
         VALUES (v_id,v_user_id,v_type,v_total,v_money,v_use_money,v_no_use_money,v_collection,v_to_user,v_remark,v_addtime,v_addip,v_first_borrow_use_money,v_borrow_id);
         FETCH c_accountlog INTO v_id,v_user_id,v_type,v_total,v_money,v_use_money,v_no_use_money,v_collection,v_to_user,v_remark,v_addtime,v_addip,v_first_borrow_use_money;
       END WHILE;
       CLOSE c_accountlog;
     END;
   END IF;
   SET s1 = s1 + 1;
  END WHILE;
  CLOSE c_borrow;

 COMMIT; -- 事务提交 
END