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

18-07-27 来源：[db:作者]
收藏我要投稿
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 ;
点击复制链接与好友分享!回本站首页