首页 > 数据库 > MySQL >

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

2018-07-28

mysql动态SQL和嵌套游标的使用实例分享。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>=&prime;2016&minus;01&minus;11&prime;;DECLARECONTINUEHANDLERFORNOTFOUNDSETDONE=1;setflast=&prime;201601&prime;;setfmonth=&prime;201601&prime;;STARTTRANSACTION;OPENdcursor;READLOOP:LOOPFETCHdcursorINTOfdate;IFDONETHENLEAVEREADLOOP;ENDIF;setflast=fmonth;setfmonth=CONCAT(SUBSTR(fdateFROM1FOR4),SUBSTR(fdateFROM6FOR2));ifflast<>fmonthTHEN&minus;&minus;SETcal1=CONCAT(cal1,"","ANDOPEDOCID=","&prime;",DoctorId,"&prime;");setfsql=CONCAT(&prime;renametablecwcomputerresulttesttocwcomputerresult&prime;,flast,&prime;;&prime;);set@ms=fsql;PREPAREstmt1FROM@ms;EXECUTEstmt1;DEALLOCATEPREPAREstmt1;DROPTABLEIFEXISTS&lsquo;cwcomputerresult&lsquo;;CREATETABLE&lsquo;cwcomputerresult&lsquo;(&lsquo;id&lsquo;bigint(20)NOTNULLAUTOINCREMENT,&lsquo;date&lsquo;dateDEFAULTNULL,&lsquo;bianhao&lsquo;varchar(20)DEFAULTNULL,&lsquo;licairenbh&lsquo;varchar(500)DEFAULTNULL,&lsquo;licairen&lsquo;varchar(200)DEFAULTNULL,&lsquo;SCHEDULEID&lsquo;varchar(32)COMMENT&prime;主键&prime;,&lsquo;jiekuandanhao&lsquo;varchar(50)DEFAULT&prime;0&prime;COMMENT&prime;借款ID&prime;,&lsquo;dangqistarttime&lsquo;datetimeDEFAULTNULLCOMMENT&prime;每期开始计时时间&prime;,&lsquo;dangqiyhtime&lsquo;datetimeDEFAULTNULLCOMMENT&prime;预计还款时间&prime;,&lsquo;dangqishtime&lsquo;varchar(19)DEFAULT&prime;&prime;,&lsquo;jiekuanhetongrfl&lsquo;decimal(18,8)DEFAULTNULL,&lsquo;jiekuanhetongts&lsquo;int(11)DEFAULT&prime;0&prime;COMMENT&prime;每期天数&prime;,&lsquo;fangkuanje&lsquo;decimal(21,2)DEFAULTNULL,&lsquo;dangqije&lsquo;decimal(20,2)DEFAULT&prime;0.00&prime;COMMENT&prime;预还本金&prime;,&lsquo;jiekuanfuwufeilx&lsquo;decimal(20,2)DEFAULT&prime;0.00&prime;COMMENT&prime;预还利息&prime;,&lsquo;jiekuangudingfy&lsquo;varchar(10)DEFAULT&prime;&prime;,&lsquo;jiekuanzjzyqfy&lsquo;decimal(20,8)DEFAULT&prime;0.00&prime;COMMENT&prime;借款&minus;资金占用期费用&prime;,&lsquo;userid&lsquo;varchar(32)DEFAULTNULLCOMMENT&prime;用户ID&prime;,&lsquo;username&lsquo;varchar(255)DEFAULTNULLCOMMENT&prime;借款客户姓名&prime;,&lsquo;jiekuanhetongje&lsquo;decimal(20,2)DEFAULTNULLCOMMENT&prime;借款总金额&prime;,&lsquo;fankuandate&lsquo;datetimeDEFAULTNULLCOMMENT&prime;借款成功时间&prime;,&lsquo;jiekuanyhdate&lsquo;datetimeDEFAULTNULLCOMMENT&prime;最后还款时间&prime;,&lsquo;licaibenjin&lsquo;int(11)DEFAULTNULL,&lsquo;zijinduanrfl&lsquo;varchar(200)DEFAULTNULL,&lsquo;zijinduanstarttime&lsquo;dateDEFAULTNULL,&lsquo;zijinduanendtime&lsquo;dateDEFAULTNULL,PRIMARYKEY(&lsquo;id&lsquo;),KEY&lsquo;dateindex&lsquo;(&lsquo;date&lsquo;),KEY&lsquo;SCHEDULEID&lsquo;(&lsquo;SCHEDULEID&lsquo;))ENGINE=InnoDBAUTOINCREMENT=1DEFAULTCHARSET=utf8;endif;&minus;&minus;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>=&prime;2016&minus;01&minus;11&prime;;DECLARECONTINUEHANDLERFORNOTFOUNDSETDONE=1;setflast=&prime;201601&prime;;setfmonth=&prime;201601&prime;;STARTTRANSACTION;OPENdcursor;READLOOP:LOOPFETCHdcursorINTOfdate;IFDONETHENLEAVEREADLOOP;ENDIF;setflast=fmonth;setfmonth=CONCAT(SUBSTR(fdateFROM1FOR4),SUBSTR(fdateFROM6FOR2));ifflast<>fmonthTHEN&minus;&minus;SETcal1=CONCAT(cal1,"","ANDOPEDOCID=","&prime;",DoctorId,"&prime;");setfsql=CONCAT(&prime;renametablecwcomputerresulttesttocwcomputerresult&prime;,flast,&prime;;&prime;);set@ms=fsql;PREPAREstmt1FROM@ms;EXECUTEstmt1;DEALLOCATEPREPAREstmt1;DROPTABLEIFEXISTS&lsquo;cwcomputerresult&lsquo;;CREATETABLE&lsquo;cwcomputerresult&lsquo;(&lsquo;id&lsquo;bigint(20)NOTNULLAUTOINCREMENT,&lsquo;date&lsquo;dateDEFAULTNULL,&lsquo;bianhao&lsquo;varchar(20)DEFAULTNULL,&lsquo;licairenbh&lsquo;varchar(500)DEFAULTNULL,&lsquo;licairen&lsquo;varchar(200)DEFAULTNULL,&lsquo;SCHEDULEID&lsquo;varchar(32)COMMENT&prime;主键&prime;,&lsquo;jiekuandanhao&lsquo;varchar(50)DEFAULT&prime;0&prime;COMMENT&prime;借款ID&prime;,&lsquo;dangqistarttime&lsquo;datetimeDEFAULTNULLCOMMENT&prime;每期开始计时时间&prime;,&lsquo;dangqiyhtime&lsquo;datetimeDEFAULTNULLCOMMENT&prime;预计还款时间&prime;,&lsquo;dangqishtime&lsquo;varchar(19)DEFAULT&Prime;,&lsquo;jiekuanhetongrfl&lsquo;decimal(18,8)DEFAULTNULL,&lsquo;jiekuanhetongts&lsquo;int(11)DEFAULT&prime;0&prime;COMMENT&prime;每期天数&prime;,&lsquo;fangkuanje&lsquo;decimal(21,2)DEFAULTNULL,&lsquo;dangqije&lsquo;decimal(20,2)DEFAULT&prime;0.00&prime;COMMENT&prime;预还本金&prime;,&lsquo;jiekuanfuwufeilx&lsquo;decimal(20,2)DEFAULT&prime;0.00&prime;COMMENT&prime;预还利息&prime;,&lsquo;jiekuangudingfy&lsquo;varchar(10)DEFAULT&Prime;,&lsquo;jiekuanzjzyqfy&lsquo;decimal(20,8)DEFAULT&prime;0.00&prime;COMMENT&prime;借款&minus;资金占用期费用&prime;,&lsquo;userid&lsquo;varchar(32)DEFAULTNULLCOMMENT&prime;用户ID&prime;,&lsquo;username&lsquo;varchar(255)DEFAULTNULLCOMMENT&prime;借款客户姓名&prime;,&lsquo;jiekuanhetongje&lsquo;decimal(20,2)DEFAULTNULLCOMMENT&prime;借款总金额&prime;,&lsquo;fankuandate&lsquo;datetimeDEFAULTNULLCOMMENT&prime;借款成功时间&prime;,&lsquo;jiekuanyhdate&lsquo;datetimeDEFAULTNULLCOMMENT&prime;最后还款时间&prime;,&lsquo;licaibenjin&lsquo;int(11)DEFAULTNULL,&lsquo;zijinduanrfl&lsquo;varchar(200)DEFAULTNULL,&lsquo;zijinduanstarttime&lsquo;dateDEFAULTNULL,&lsquo;zijinduanendtime&lsquo;dateDEFAULTNULL,PRIMARYKEY(&lsquo;id&lsquo;),KEY&lsquo;dateindex&lsquo;(&lsquo;date&lsquo;),KEY&lsquo;SCHEDULEID&lsquo;(&lsquo;SCHEDULEID&lsquo;))ENGINE=InnoDBAUTOINCREMENT=1DEFAULTCHARSET=utf8;endif;&minus;&minus;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 &#39;主键&#39;,
  `jiekuandanhao` varchar(50) DEFAULT &#39;0&#39; COMMENT &#39;借款ID&#39;,
  `dangqistarttime` datetime DEFAULT NULL COMMENT &#39;每期开始计时时间&#39;,
  `dangqiyhtime` datetime DEFAULT NULL COMMENT &#39;预计还款时间&#39;,
  `dangqishtime` varchar(19) NOT NULL DEFAULT &#39;&#39;,
  `jiekuanhetongrfl` decimal(18,8) DEFAULT NULL,
  `jiekuanhetongts` int(11) DEFAULT &#39;0&#39; COMMENT &#39;每期天数&#39;,
  `fangkuanje` decimal(21,2) DEFAULT NULL,
  `dangqije` decimal(20,2) DEFAULT &#39;0.00&#39; COMMENT &#39;预还本金&#39;,
  `jiekuanfuwufeilx` decimal(20,2) DEFAULT &#39;0.00&#39; COMMENT &#39;预还利息&#39;,
  `jiekuangudingfy` varchar(10) NOT NULL DEFAULT &#39;&#39;,
  `userid` varchar(32) NOT NULL COMMENT &#39;用户ID&#39;,
  `username` varchar(255) DEFAULT NULL COMMENT &#39;借款客户姓名&#39;,
  `jiekuanhetongje` decimal(20,2) DEFAULT NULL COMMENT &#39;借款总金额&#39;,
  `fankuandate` datetime DEFAULT NULL COMMENT &#39;借款成功时间&#39;,
  `jiekuanyhdate` datetime DEFAULT NULL COMMENT &#39;最后还款时间&#39;,
   is_deal int(1) NOT NULL DEFAULT &#39;0&#39;,
   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 &#39;bh_999&#39;,&#39;ziyou_999&#39;,indate,&#39;自由资金&#39;,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 ;
相关文章
最新文章
热点推荐