一个存储过程:带参数动态执行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;