带参数动态执行sql语句:Execute Immediate

根据客户需要我们增加了一个统计表用来汇总统计数据统计数据的产生需要根据一个基本表动态执行sql语句在存储过程中动态生成sql语句然后执行需要用到Execute Immediate命令我想写一个通用的统计用存储过程似乎不大好办if语句的应用在所难免了呵呵

特此存档

create or replace procedure P_INSERT_XT_TJ_MX(sDate in varchar) is

author:李春雷

create time:

purpose:更新xt_tj_mx表

sXh xt_tjxh%type;主表序号

sDwmc xt_tjdwmc%type;

sDw xt_tjdw%type;

sDwzd xt_tjdwzd%type;

sTable xt_tjhzbmc%type;

sSjzd xt_tjsjzd%type;

sSqlStr varchar();

iCount int;

cursor curSort is

select xhdwmchzbmcsjzddwzddw from xt_tj ;

begin

open curSort;

loop

fetch curSort into sXhsDwmcsTablesSjzdsDwzdsDw;

exit when curSort%notfound;

sSqlStr := select count(*)from || sTable || where to_char(||sSjzd||||YYYYMM)=:sDate and ||sDwzd ||

in (select dwxh from xt_dw connect byprior dwxh = dwfxh start with dwxh =:sDw);

Execute Immediate sSqlStr into iCount using sDatesDw;

delete from xt_tj_mx where fxh=sXh and sjz=sDate;

insert into xt_tj_mx(xhfxhhzsmsjz)values(SEQ_XT_TJ_MXNEXTVALsXhiCountsDate);

commit;

end loop;

end P_INSERT_XT_TJ_MX;