根據客戶需要,我們增加了一個統(tǒng)計表,用來匯總統(tǒng)計數據,統(tǒng)計數據的產生,需要根據一個基本表,動態(tài)執(zhí)行sql語句.在存儲過程中,動態(tài)生成sql語句然后執(zhí)行,需要用到Execute Immediate命令.我想寫一個通用的統(tǒng)計用存儲過程,似乎不大好辦,if語句的應用在所難免了.呵呵.
特此存檔.
create or replace procedure P_INSERT_XT_TJ_MX(sDate in varchar2) is
--author:李春雷
--create time:2005.8.4
--purpose:更新xt_tj_mx表
sXh xt_tj.xh%type; --主表序號
sDwmc xt_tj.dwmc%type;
sDw xt_tj.dw%type;
sDwzd xt_tj.dwzd%type;
sTable xt_tj.hzbmc%type;
sSjzd xt_tj.sjzd%type;
sSqlStr varchar2(300);
iCount int;
cursor curSort is
select xh,dwmc,hzbmc,sjzd,dwzd,dw from xt_tj ;
begin
open curSort;
loop
fetch curSort into sXh,sDwmc,sTable,sSjzd,sDwzd,sDw;
exit when curSort%notfound;
sSqlStr := 'select count(*) from '|| sTable || ' where to_char('||sSjzd||','||'''YYYYMM'')=:sDate and '||sDwzd ||
' in (select dwxh from xt_dw connect by prior dwxh = dwfxh start with dwxh =:sDw)';
Execute Immediate sSqlStr into iCount using sDate,sDw;
delete from xt_tj_mx where fxh=sXh and sjz=sDate;
insert into xt_tj_mx(xh,fxh,hzsm,sjz)values(SEQ_XT_TJ_MX.NEXTVAL,sXh,iCount,sDate);
commit;
end loop;
end P_INSERT_XT_TJ_MX;