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

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

程序员文章站 2024-03-02 08:18:34
begin -- 声明变量 declare v_addtime_begin varchar(13); declare v_addtime_end var...
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