欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  数据库

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

程序员文章站 2024-01-17 19:08:46
...

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 roc

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-05-27') AND PUBLISH_TIME = v_addtime_begin AND ADDTIME  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