CREATE PROCEDURE feeMonth(in fmark varchar(200),in fuser char(32),in ftime BIGINT,in fmonth char(6)) BEGIN #定义SQL变量 declare create_sql varchar(100); declare sel_sql varchar(100); declare del_sql varchar(100); declare fmon varchar(100); #定义表名变量 declare tableName varchar(30); #判断是否全部遍历记录的标记 declare i int default 0; #定义游标 名字为month_cursor declare month_cursor CURSOR for select table_name from data_import_config; #当游标遍历完成后将标记变成某个值 declare CONTINUE HANDLER for not found set i=1; set create_sql=‘create table ‘; set sel_sql=‘ as select * from ‘; set del_sql=‘delete from ‘; set fmon=CONCAT(‘_‘,fmonth); #打开游标 open month_cursor; REPEAT #取出每条记录付给相关变量 FETCH month_cursor into tableName; set @c_sql= CONCAT(create_sql,tableName,fmon,sel_sql,tableName); set @d_sql= CONCAT(del_sql,tableName); PREPARE pre_c_sql from @c_sql; PREPARE pre_d_sql from @d_sql; EXECUTE pre_c_sql; EXECUTE pre_d_sql; deallocate prepare pre_c_sql; deallocate prepare pre_d_sql; #关闭游标 until i end REPEAT; close month_cursor; update fee_month set mark=fmark,user_id=fuser,time=ftime,flag=‘1‘ where month=fmonth; insert into fee_month (month,flag) values (CASE WHEN (SUBSTR(fmonth,5,2)+1)>12 THEN CONCAT(SUBSTR(fmonth,1,4)+1,‘01‘) WHEN (SUBSTR(fmonth,5,1)+1)<=12 THEN (fmonth+1) END ,‘0‘); insert into fee_collect(month,ORGANIZE_ID,pre_fee,user_fee,sys_fee,tmp_fee) select f.month,f.ORGANIZE_ID,f.pre_fee,f.user_fee,f.sys_fee,f.tmp_fee from fee_collect_cur as f; delete from fee_collect_cur; insert into fee_collect_cur (month,ORGANIZE_ID,pre_fee) select (CASE WHEN (SUBSTR(fmonth,5,2)+1)>12 THEN CONCAT(SUBSTR(fmonth,1,4)+1,‘01‘) WHEN (SUBSTR(fmonth,5,1)+1)<=12 THEN (fmonth+1) END),f.ORGANIZE_ID,(f.pre_fee+f.user_fee+f.tmp_fee-f.sys_fee) from fee_collect as f WHERE (f.pre_fee+f.user_fee+f.tmp_fee-f.sys_fee)!=0; END
声明一下是有参考别人的。
在写这个SQL的时候一定要注意:
自定义的变量一定不能喝数据库中的字段重名!!
mysql存储过程动态执行SQL,布布扣,bubuko.com
原文:http://www.cnblogs.com/img-zoom/p/3912253.html