一個預存程序:帶參數動態執行sql語句:Execute Immediate

根據客戶需要,我們增加了一個統計表,用來匯總統計資料,統計資料的產生,需要根據一個基本表,動態執行sql語句.在預存程序中,動態產生sql語句然後執行,需要用到Execute Immediate命令.我想寫一個通用的統計用預存程序,似乎不大好辦,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;

今天增加了一個統計項目,用到了動態資料指標,存檔.

create or replace procedure P_INSERT_XT_TJ_MX(sDate in varchar2) is
--author:李春雷
--create time:2005.8.8
--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;
       iTemp int;
       sYear varchar2(4);      
       sQxflbh qx_qxjl.qxflbh%type;  
        
       cursor curSort is
              select xh,dwmc,hzbmc,sjzd,dwzd,dw from xt_tj ;
             
      type t_Cursor is REF CURSOR;
      qxCursor t_Cursor;
     
begin    
     dbms_output.put_line(sDate);
     sYear:=substr(sDate,1,4);
     open curSort;
     loop
         fetch curSort into sXh,sDwmc,sTable,sSjzd,sDwzd,sDw;        
         exit when curSort%notfound;
         --統計各公司工作任務單數目:
         if sTable = 'rw_gzrwd' then
            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_rw_gzrwd where fxh=sXh and sjz=sDate;
            insert into xt_tj_rw_gzrwd(xh,fxh,hzsm,sjz)values(SEQ_XT_TJ_MX.NEXTVAL,sXh,iCount,sDate);          
         end if;
         --統計各公司各缺陷數目
         if sTable = 'qx_qxjl' then
            sSqlStr := 'select count(*),t.qxflbh from ' || sTable ||
                       ' t where qxdjrdw in (select dwxh from xt_dw connect by  prior dwxh = dwfxh start with dwxh =:sDw) and to_char(t.fxrq,''YYYY'')=:sYear group by t.qxflbh';
            open qxCursor for sSqlStr using sDw,sYear;
            loop
               fetch qxCursor into iCount,sQxflbh;
               exit when qxCursor%notfound;
               dbms_output.put_line('dwbh='||sDw||'flbh='||sQxflbh||',iCount='||iCount);              
               select count(*) into iTemp from xt_tj_qx_qxjl t where t.dwxh=sDw and t.qxflbh=sQxflbh and t.sjz=sYear;
               if iTemp=0 then
                  insert into xt_tj_qx_qxjl(XH,DWXH,QXFLBH,SJZ,HZSM)values(seq_xt_tj_qx_qxjl.nextval,sDw,sQxflbh,sYear,iCount);
               else
                  update xt_tj_qx_qxjl set hzsm=iCount where dwxh=sDw and qxflbh=sQxflbh and sjz=sYear;
               end if;
             end loop;
            close qxCursor;
         end if;        
     end loop;
     close curSort;
     commit;
end P_INSERT_XT_TJ_MX;