熱點推薦:
您现在的位置: 電腦知識網 >> 編程 >> Oracle >> 正文

帶參數動態執行sql語句:Execute Immediate

2013-11-13 15:53:38  來源: Oracle 

  根據客戶需要我們增加了一個統計表用來匯總統計數據統計數據的產生需要根據一個基本表動態執行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 by prior 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;
From:http://tw.wingwit.com/Article/program/Oracle/201311/17475.html
    推薦文章
    Copyright © 2005-2013 電腦知識網 Computer Knowledge   All rights reserved.