mysql动态SQL和嵌套游标的使用实例分享

mysql动态执行sql，这边重点代码是：

set f_sql=concat('rename table cw_computer_resulttest to cw_computer_result',f_last,';');
set @ms=f_sql; 
prepare stmt1 from @ms;
execute stmt1;
deallocate prepare stmt1 ;
其中@ms是系统变量，不用declare，这是重点

下面是写的 全部存储过程：（本例中还给出了嵌套cursor的应用）

drop procedure if exists  cw_year1_deal;

delimiter
createprocedurecwyear1deal()begindeclarefdatevarchar(20);declarefmonthvarchar(20);declareflastvarchar(20);declaredoneint;declareiintdefault0;declarefsqlvarchar(200);declaredcursorcursorforselectdatefromtbdatewheredate>=′2016−01−11′;declarecontinuehandlerfornotfoundsetdone=1;setflast=′201601′;setfmonth=′201601′;starttransaction;opendcursor;readloop:loopfetchdcursorintofdate;ifdonethenleavereadloop;endif;setflast=fmonth;setfmonth=concat(substr(fdatefrom1for4),substr(fdatefrom6for2));ifflast<>fmonththen−−setcal1=concat(cal1,"","andopedocid=","′",doctorid,"′");setfsql=concat(′renametablecwcomputerresulttesttocwcomputerresult′,flast,′;′);set@ms=fsql;preparestmt1from@ms;executestmt1;deallocatepreparestmt1;droptableifexists‘cwcomputerresult‘;createtable‘cwcomputerresult‘(‘id‘bigint(20)notnullautoincrement,‘date‘datedefaultnull,‘bianhao‘varchar(20)defaultnull,‘licairenbh‘varchar(500)defaultnull,‘licairen‘varchar(200)defaultnull,‘scheduleid‘varchar(32)comment′主键′,‘jiekuandanhao‘varchar(50)default′0′comment′借款id′,‘dangqistarttime‘datetimedefaultnullcomment′每期开始计时时间′,‘dangqiyhtime‘datetimedefaultnullcomment′预计还款时间′,‘dangqishtime‘varchar(19)default′′,‘jiekuanhetongrfl‘decimal(18,8)defaultnull,‘jiekuanhetongts‘int(11)default′0′comment′每期天数′,‘fangkuanje‘decimal(21,2)defaultnull,‘dangqije‘decimal(20,2)default′0.00′comment′预还本金′,‘jiekuanfuwufeilx‘decimal(20,2)default′0.00′comment′预还利息′,‘jiekuangudingfy‘varchar(10)default′′,‘jiekuanzjzyqfy‘decimal(20,8)default′0.00′comment′借款−资金占用期费用′,‘userid‘varchar(32)defaultnullcomment′用户id′,‘username‘varchar(255)defaultnullcomment′借款客户姓名′,‘jiekuanhetongje‘decimal(20,2)defaultnullcomment′借款总金额′,‘fankuandate‘datetimedefaultnullcomment′借款成功时间′,‘jiekuanyhdate‘datetimedefaultnullcomment′最后还款时间′,‘licaibenjin‘int(11)defaultnull,‘zijinduanrfl‘varchar(200)defaultnull,‘zijinduanstarttime‘datedefaultnull,‘zijinduanendtime‘datedefaultnull,primarykey(‘id‘),key‘dateindex‘(‘date‘),key‘scheduleid‘(‘scheduleid‘))engine=innodbautoincrement=1defaultcharset=utf8;endif;−−selectfmonth;callcwdeal(fdate);seti=i+1;ifithenselecti;commit;starttransaction;endif;endloopreadloop;commit;closedcursor;end
createprocedurecwyear1deal()begindeclarefdatevarchar(20);declarefmonthvarchar(20);declareflastvarchar(20);declaredoneint;declareiintdefault0;declarefsqlvarchar(200);declaredcursorcursorforselectdatefromtbdatewheredate>=′2016−01−11′;declarecontinuehandlerfornotfoundsetdone=1;setflast=′201601′;setfmonth=′201601′;starttransaction;opendcursor;readloop:loopfetchdcursorintofdate;ifdonethenleavereadloop;endif;setflast=fmonth;setfmonth=concat(substr(fdatefrom1for4),substr(fdatefrom6for2));ifflast<>fmonththen−−setcal1=concat(cal1,"","andopedocid=","′",doctorid,"′");setfsql=concat(′renametablecwcomputerresulttesttocwcomputerresult′,flast,′;′);set@ms=fsql;preparestmt1from@ms;executestmt1;deallocatepreparestmt1;droptableifexists‘cwcomputerresult‘;createtable‘cwcomputerresult‘(‘id‘bigint(20)notnullautoincrement,‘date‘datedefaultnull,‘bianhao‘varchar(20)defaultnull,‘licairenbh‘varchar(500)defaultnull,‘licairen‘varchar(200)defaultnull,‘scheduleid‘varchar(32)comment′主键′,‘jiekuandanhao‘varchar(50)default′0′comment′借款id′,‘dangqistarttime‘datetimedefaultnullcomment′每期开始计时时间′,‘dangqiyhtime‘datetimedefaultnullcomment′预计还款时间′,‘dangqishtime‘varchar(19)default′,‘jiekuanhetongrfl‘decimal(18,8)defaultnull,‘jiekuanhetongts‘int(11)default′0′comment′每期天数′,‘fangkuanje‘decimal(21,2)defaultnull,‘dangqije‘decimal(20,2)default′0.00′comment′预还本金′,‘jiekuanfuwufeilx‘decimal(20,2)default′0.00′comment′预还利息′,‘jiekuangudingfy‘varchar(10)default′,‘jiekuanzjzyqfy‘decimal(20,8)default′0.00′comment′借款−资金占用期费用′,‘userid‘varchar(32)defaultnullcomment′用户id′,‘username‘varchar(255)defaultnullcomment′借款客户姓名′,‘jiekuanhetongje‘decimal(20,2)defaultnullcomment′借款总金额′,‘fankuandate‘datetimedefaultnullcomment′借款成功时间′,‘jiekuanyhdate‘datetimedefaultnullcomment′最后还款时间′,‘licaibenjin‘int(11)defaultnull,‘zijinduanrfl‘varchar(200)defaultnull,‘zijinduanstarttime‘datedefaultnull,‘zijinduanendtime‘datedefaultnull,primarykey(‘id‘),key‘dateindex‘(‘date‘),key‘scheduleid‘(‘scheduleid‘))engine=innodbautoincrement=1defaultcharset=utf8;endif;−−selectfmonth;callcwdeal(fdate);seti=i+1;ifithenselecti;commit;starttransaction;endif;endloopreadloop;commit;closedcursor;end

delimiter ;

引用的存储过程代码：

drop procedure if exists  cw_deal;

delimiter $$

create procedure cw_deal(in indate varchar(20))
begin
 
declare f_date varchar(20);
declare i int default 0;
declare f_licairen varchar(60);
declare f_schedule_id varchar(32);
declare f_jiekuandanghao varchar(50);
declare f_dangqistarttime datetime;
declare f_dangqiyhtime datetime;
declare f_dangqishtime varchar(19);
declare f_jiekuanhetongrfl decimal(18,8);
declare f_jiekuanhetongts  int(11);
declare f_fangkuanje decimal(21,2);
declare f_dangqije decimal(20,2);
declare f_jiekuanfuwulx decimal(20,2);
declare f_jiekuangudingfy varchar(10);
 
declare f_userid varchar(32);
declare f_username varchar(255);
declare f_jiekuanhetongje  decimal(20,2);
declare f_jiekuanhetongksr datetime;
declare f_jiekuanhetongyhr datetime;
 
declare f_zijinduanje int(11);
declare f_zijinduanfl varchar(200);
declare f_zijinduanstarttime date;
declare f_zijinduanendtime date;
declare f_bianhao varchar(20);
declare f_licairenbh varchar(100);
 
declare f_fkje  decimal(20,2);
declare f_fkje2  decimal(20,2);
declare f_lcbj  decimal(20,2);
declare f_ziyou  decimal(20,2);
declare flag int;
declare done int;
 
declare dcheck_cursor cursor for select bianhao,licairenbh,licairen,licaibenjin,zijinduanrfl,zijinduanstarttime,zijinduanendtime from temp_datecheck_20170122 where `date`=indate order by zijinduanrfl desc;
 
 
declare cw_cursor cursor for select schedule_id,jiekuandanhao,dangqistarttime,dangqiyhtime,dangqishtime,jiekuanhetongrfl,jiekuanhetongts,fangkuanje,dangqije,jiekuanfuwufeilx,jiekuangudingfy,userid,username,jiekuanhetongje,  fankuandate ,jiekuanyhdate from tmp_detail 
where  is_deal =0 ;
   
declare continue handler for not found set done = 1;
 
drop temporary table if exists `tmp_detail`;
create  temporary table `tmp_detail` (
 `id` bigint(20) not null auto_increment,
  `schedule_id` varchar(32) not null comment '主键',
  `jiekuandanhao` varchar(50) default '0' comment '借款id',
  `dangqistarttime` datetime default null comment '每期开始计时时间',
  `dangqiyhtime` datetime default null comment '预计还款时间',
  `dangqishtime` varchar(19) not null default '',
  `jiekuanhetongrfl` decimal(18,8) default null,
  `jiekuanhetongts` int(11) default '0' comment '每期天数',
  `fangkuanje` decimal(21,2) default null,
  `dangqije` decimal(20,2) default '0.00' comment '预还本金',
  `jiekuanfuwufeilx` decimal(20,2) default '0.00' comment '预还利息',
  `jiekuangudingfy` varchar(10) not null default '',
  `userid` varchar(32) not null comment '用户id',
  `username` varchar(255) default null comment '借款客户姓名',
  `jiekuanhetongje` decimal(20,2) default null comment '借款总金额',
  `fankuandate` datetime default null comment '借款成功时间',
  `jiekuanyhdate` datetime default null comment '最后还款时间',
   is_deal int(1) not null default '0',
   primary key (`id`),
  key `schedule_idindex` (`schedule_id`),
  key `is_dealindex` (`is_deal`)
) engine=innodb default charset=utf8;
 
insert into tmp_detail(schedule_id,jiekuandanhao,dangqistarttime,dangqiyhtime,dangqishtime,jiekuanhetongrfl,jiekuanhetongts,fangkuanje,dangqije,jiekuanfuwufeilx,jiekuangudingfy,userid,username,jiekuanhetongje,  fankuandate ,jiekuanyhdate) 
select schedule_id,jiekuandanhao,dangqistarttime,dangqiyhtime,dangqishtime,jiekuanhetongrfl,jiekuanhetongts,fangkuanje,dangqije,jiekuanfuwufeilx,jiekuangudingfy,userid,username,jiekuanhetongje,  fankuandate ,jiekuanyhdate from temp_cw_result_20170119 
where `date`=indate; 
 
set flag=1;
set f_fkje=0;
set f_fkje2=0;
start transaction;
open dcheck_cursor;
read_loop :loop
fetch dcheck_cursor into f_bianhao,f_licairenbh,f_licairen,f_zijinduanje,f_zijinduanfl,f_zijinduanstarttime,f_zijinduanendtime;
set f_lcbj=f_zijinduanje;
 
 
 
if done then
 
set f_ziyou=0;
select sum(fangkuanje) into f_ziyou from tmp_detail where  is_deal =0;
insert into cw_computer_result(bianhao,licairenbh,date,licairen,schedule_id,jiekuandanhao,dangqistarttime,dangqiyhtime,dangqishtime,jiekuanhetongrfl,jiekuanhetongts,fangkuanje,dangqije,jiekuanfuwufeilx,jiekuangudingfy,jiekuanzjzyqfy,userid,username,jiekuanhetongje,fankuandate,jiekuanyhdate,licaibenjin,zijinduanrfl,zijinduanstarttime,zijinduanendtime)
select 'bh_999','ziyou_999',indate,'自由资金',schedule_id,jiekuandanhao,dangqistarttime,dangqiyhtime,dangqishtime,jiekuanhetongrfl,jiekuanhetongts,fangkuanje,dangqije,jiekuanfuwufeilx,jiekuangudingfy,0,userid,username,jiekuanhetongje,  fankuandate ,jiekuanyhdate,f_ziyou,0.0,indate,indate from tmp_detail 
where  is_deal =0;
    
leave read_loop;
 
end if;
 
 
open cw_cursor;
inner_loop:loop
 
    fetch cw_cursor into f_schedule_id,f_jiekuandanghao,f_dangqistarttime,f_dangqiyhtime,f_dangqishtime,f_jiekuanhetongrfl,f_jiekuanhetongts,f_fangkuanje,f_dangqije,f_jiekuanfuwulx,f_jiekuangudingfy,f_userid,f_username,f_jiekuanhetongje,f_jiekuanhetongksr,f_jiekuanhetongyhr; 
 
    if done = 1 then
 
      if flag =0 
       then
       insert into cw_computer_result(bianhao,licairenbh,date,licairen,licaibenjin,zijinduanrfl,zijinduanstarttime,zijinduanendtime)
       select f_bianhao,f_licairenbh,indate,f_licairen,f_zijinduanje,f_zijinduanfl,f_zijinduanstarttime,f_zijinduanendtime;
      end if;
       leave inner_loop;
 
             
    end if;
     
 
    set flag=0;
     
    if f_fkje2>0
    then
            set f_lcbj=f_lcbj-f_fkje2;
            if f_lcbj>0
            then
                insert into cw_computer_result(bianhao,licairenbh,date,licairen,schedule_id,jiekuandanhao,dangqistarttime,dangqiyhtime,dangqishtime,jiekuanhetongrfl,jiekuanhetongts,fangkuanje,dangqije,jiekuanfuwufeilx,jiekuangudingfy,jiekuanzjzyqfy,userid,username,jiekuanhetongje,fankuandate,jiekuanyhdate,licaibenjin,zijinduanrfl,zijinduanstarttime,zijinduanendtime)
                select f_bianhao,f_licairenbh,indate,f_licairen,f_schedule_id,f_jiekuandanghao,f_dangqistarttime,f_dangqiyhtime,f_dangqishtime,f_jiekuanhetongrfl,f_jiekuanhetongts,f_fkje2,f_dangqije,f_jiekuanfuwulx,f_jiekuangudingfy,f_fkje2*f_zijinduanfl,f_userid,f_username,f_jiekuanhetongje,f_jiekuanhetongksr,f_jiekuanhetongyhr,f_zijinduanje,f_zijinduanfl,f_zijinduanstarttime,f_zijinduanendtime;
                update tmp_detail set is_deal=1 where schedule_id=f_schedule_id;
            
                set f_fkje2=0;
 
            end if;
            if f_lcbj<=0
            then
               
              set f_fkje = f_fkje2+f_lcbj;
              set f_fkje2 = -f_lcbj;
              insert into cw_computer_result(bianhao,licairenbh,date,licairen,schedule_id,jiekuandanhao,dangqistarttime,dangqiyhtime,dangqishtime,jiekuanhetongrfl,jiekuanhetongts,fangkuanje,dangqije,jiekuanfuwufeilx,jiekuangudingfy,jiekuanzjzyqfy,userid,username,jiekuanhetongje,fankuandate,jiekuanyhdate,licaibenjin,zijinduanrfl,zijinduanstarttime,zijinduanendtime)
              select f_bianhao,f_licairenbh,indate,f_licairen,f_schedule_id,f_jiekuandanghao,f_dangqistarttime,f_dangqiyhtime,f_dangqishtime,f_jiekuanhetongrfl,f_jiekuanhetongts,f_fkje,f_dangqije,f_jiekuanfuwulx,f_jiekuangudingfy,f_fkje*f_zijinduanfl,f_userid,f_username,f_jiekuanhetongje,f_jiekuanhetongksr,f_jiekuanhetongyhr,f_zijinduanje,f_zijinduanfl,f_zijinduanstarttime,f_zijinduanendtime;
              set done =1;
              set flag=1;
            end if;
 
             
    else
    set f_lcbj=f_lcbj-f_fangkuanje; 
    if f_lcbj>0
    then
            insert into cw_computer_result(bianhao,licairenbh,date,licairen,schedule_id,jiekuandanhao,dangqistarttime,dangqiyhtime,dangqishtime,jiekuanhetongrfl,jiekuanhetongts,fangkuanje,dangqije,jiekuanfuwufeilx,jiekuangudingfy,jiekuanzjzyqfy,userid,username,jiekuanhetongje,fankuandate,jiekuanyhdate,licaibenjin,zijinduanrfl,zijinduanstarttime,zijinduanendtime)
            select f_bianhao,f_licairenbh,indate,f_licairen,f_schedule_id,f_jiekuandanghao,f_dangqistarttime,f_dangqiyhtime,f_dangqishtime,f_jiekuanhetongrfl,f_jiekuanhetongts,f_fangkuanje,f_dangqije,f_jiekuanfuwulx,f_jiekuangudingfy,f_fangkuanje*f_zijinduanfl,f_userid,f_username,f_jiekuanhetongje,f_jiekuanhetongksr,f_jiekuanhetongyhr,f_zijinduanje,f_zijinduanfl,f_zijinduanstarttime,f_zijinduanendtime;
            update tmp_detail set is_deal=1 where schedule_id=f_schedule_id;
    end if;
    if f_lcbj<=0
    then
            set f_fkje = f_fangkuanje+f_lcbj;
            set f_fkje2 = -f_lcbj;
            insert into cw_computer_result(bianhao,licairenbh,date,licairen,schedule_id,jiekuandanhao,dangqistarttime,dangqiyhtime,dangqishtime,jiekuanhetongrfl,jiekuanhetongts,fangkuanje,dangqije,jiekuanfuwufeilx,jiekuangudingfy,jiekuanzjzyqfy,userid,username,jiekuanhetongje,fankuandate,jiekuanyhdate,licaibenjin,zijinduanrfl,zijinduanstarttime,zijinduanendtime)
            select f_bianhao,f_licairenbh,indate,f_licairen,f_schedule_id,f_jiekuandanghao,f_dangqistarttime,f_dangqiyhtime,f_dangqishtime,f_jiekuanhetongrfl,f_jiekuanhetongts,f_fkje,f_dangqije,f_jiekuanfuwulx,f_jiekuangudingfy,f_fkje*f_zijinduanfl,f_userid,f_username,f_jiekuanhetongje,f_jiekuanhetongksr,f_jiekuanhetongyhr,f_zijinduanje,f_zijinduanfl,f_zijinduanstarttime,f_zijinduanendtime;
            set done =1;
            set flag=1;
    end if;
 
    end if;
 
   
  end loop inner_loop;
  close cw_cursor; 
 

set done=0;
 

set i=i+1;
if i%10000=0
then
select i;
commit;
start transaction;
end if;
 
end loop read_loop;
commit;
close dcheck_cursor;
 
end $$
delimiter ;

如对本文有疑问， 点击进行留言回复！！