mysql存储过程动态执行SQL

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 detail varchar(100);
DECLARE done INT DEFAULT FALSE;
#定义表名变量
declare tableName varchar(30);
#定义游标 名字为month_cursor
declare month_cursor CURSOR for select table_name from data_import_config GROUP BY table_name;
#当游标遍历完成后将标记变成某个值
declare CONTINUE HANDLER for not found set done=true;
set create_sql='create table ';
set sel_sql=' as select * from ';
set del_sql='delete from ';
set detail='fee_detail';
set fmon=CONCAT('_',fmonth);
set @detail_c_sql=CONCAT(create_sql,detail,fmon,sel_sql,detail);
set @detail_d_sql= CONCAT(del_sql,detail);
PREPARE pre_detail_c_sql from @detail_c_sql;
PREPARE pre_detail_d_sql from @detail_d_sql;
EXECUTE pre_detail_c_sql;
EXECUTE pre_detail_d_sql;
deallocate prepare pre_detail_c_sql;
deallocate prepare pre_detail_d_sql;
#打开游标
open month_cursor;
my_loop:loop
#取出每条记录付给相关变量
FETCH month_cursor into tableName;
if done then
leave my_loop;
end if;
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;
#关闭游标
end loop;
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的时候一定要注意：

自定义的变量一定不能和数据库中的字段重名！！