Mysql存儲過程循環內嵌套使用游標示例代碼，mysql示例代碼

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;
 
 /** 聲明游標，並將查詢結果存到游標中 **/
 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

mysql 存儲過程過程 循環游標

游標關閉條件
 

SQLServer2000存儲過程是否允許游標嵌套，可以的話怎寫？多謝

可以，但最好用事務，一旦某個語句執行出錯了就要釋放資源
其實存儲過程中的游標和其他程序設計語言裡的for 循環是一樣的