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;
精品推荐