mysql預存程序動態執行SQL

標籤：style   blog   color   os   strong   資料   for   ar   

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的時候一定要注意：

自訂的變數一定不能喝資料庫中的欄位重名！！