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;

/** 声明游标，并将查询结果存到游标中 **/
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;

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;

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
    INSERT INTO rocky_accountlog_test2 (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