Mysql存储过程循环内嵌套使用游标示例代码
网络编程 2021-07-05 15:27www.168986.cn编程入门
本节主要介绍了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_aountlog 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_aountlog 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_aountlog; FETCH c_aountlog 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_aountlog_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_aountlog 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_aountlog; END; END IF; SET s1 = s1 + 1; END WHILE; CLOSE c_borrow; COMMIT; -- 事务提交 END
编程语言
- 甘肃哪有关键词排名优化购买方式有哪些
- 甘肃SEO如何做网站优化
- 河南seo关键词优化怎么做电话营销
- 北京SEO优化如何做QQ群营销
- 来宾百度关键词排名:提升您网站曝光率的关键
- 卢龙关键词优化:提升您网站排名的策略与技巧
- 山东网站优化的注意事项有哪些
- 四川整站优化怎样提升在搜索引擎中的排名
- 疏附整站优化:提升网站性能与用户体验的全新
- 海南seo主要做什么工作售后服务要做到哪些
- 荣昌百度网站优化:提升您网站的搜索引擎排名
- 河北seo网站排名关键词优化如何做SEO
- 江西优化关键词排名推广售后保障一般有哪些
- 古浪SEO优化:提升你的网站可见性
- 西藏网站排名优化怎么把网站排名在百度首页
- 如何提升阳东百度快照排名:详尽指南