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;create PROCEDURE cw_year1_deal()BEGINDECLARE f_date VARCHAR(20);DECLARE f_month VARCHAR(20);DECLARE f_last VARCHAR(20);DECLARE DONE INT;DECLARE I int DEFAULT 0;DECLARE f_sql VARCHAR(200);DECLARE d_cursor CURSOR FOR select date from tb_date where date>='2016-01-11' ;DECLARE ConTINUE HANDLER FOR NOT FOUND SET DONE = 1;set f_last='201601';set f_month='201601';START TRANSACTION;OPEN d_cursor;READ_LOOP :LOOPFETCH d_cursor INTO f_date;IF DONE THEN LEAVE READ_LOOP;END IF;set f_last=f_month;set f_month =ConCAT(SUBSTR(f_date FROM 1 FOR 4),SUBSTR(f_date FROM 6 FOR 2));if f_last<>f_month THEN-- SET cal1=ConCAT(cal1," ","AND OPEDOC_ID = ","'",DoctorId,"'");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 ;DROP TABLE IF EXISTS `cw_computer_result`;CREATE TABLE `cw_computer_result` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `date` date DEFAULT NULL, `bianhao` varchar(20) DEFAULT NULL, `licairenbh` varchar(500) DEFAULT NULL, `licairen` varchar(200) DEFAULT NULL, `SCHEDULE_ID` varchar(32) COMMENT '主键', `jiekuandanhao` varchar(50) DEFAULT '0' COMMENT '借款ID', `dangqistarttime` datetime DEFAULT NULL COMMENT '每期开始计时时间', `dangqiyhtime` datetime DEFAULT NULL COMMENT '预计还款时间', `dangqishtime` varchar(19) 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) DEFAULT '', `jiekuanzjzyqfy` decimal(20,8) DEFAULT '0.00' COMMENT '借款-资金占用期费用', `userid` varchar(32) DEFAULT 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 '最后还款时间', `licaibenjin` int(11) DEFAULT NULL, `zijinduanrfl` varchar(200) DEFAULT NULL, `zijinduanstarttime` date DEFAULT NULL, `zijinduanendtime` date DEFAULT NULL, PRIMARY KEY (`id`), KEY `date_index` (`date`), KEY `SCHEDULE_ID` (`SCHEDULE_ID`)) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;end if;-- select f_month;call cw_deal(f_date);SET I=I+1;IF I%10000=0THENSELECT I;COMMIT;START TRANSACTION;END IF;END LOOP READ_LOOP;COMMIT;CLOSE d_cursor;END引用的存储过程代码：drop PROCEDURE IF EXISTS cw_deal;create PROCEDURE cw_deal(IN indate varchar(20))BEGINDECLARE 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 :LOOPFETCH dcheck_cursor INTO f_bianhao,f_licairenbh,f_licairen,f_zijinduanje,f_zijinduanfl,f_zijinduanstarttime,f_zijinduanendtime;set f_lcbj=f_zijinduanje;IF DONE THENset 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=0THENSELECT I;COMMIT;START TRANSACTION;END IF;END LOOP READ_LOOP;COMMIT;CLOSE dcheck_cursor;END