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 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

mysql存储过程动态执行SQL

上一篇:mvc使用mongodb时objectId序列化与反序列化


下一篇:Oracle性能分析1:开启SQL跟踪和获取trace文件