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

开发者在线 Builder.com.cn 更新时间:2007-10-04作者:中国IT实验室 来源:中国IT实验室

本文关键词: 备份 数据库


  根据客户需要,我们增加了一个统计表,用来汇总统计数据,统计数据的产生,需要根据一个基本表,动态执行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;

查看本文来源