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

Oracle多表級聯更新詳解

2013-11-13 22:25:36  來源: Oracle 
我們在平時的工作中可能遇到過多表級聯更新我也在網上看到過不少的方法但是使用這些方法一般都沒成功過所以今天我給大家介紹一種稍微麻煩的方法有需要的朋友可以參考下  

  用游標實現我覺得絕對這種方法比較安全的
首先定一個游標把需要用到的一些數據存放到游標中:

復制代碼 代碼如下:
declare
  CURSOR D_CURSOR_CUS_INFO IS
    select tid_           as id_
           towe_money_    as owe_money_
           aheatingArea    as heating_area_
    from T_CUS_OWE_MONEY_ t
    left join T_CUS_OWE_MONEY_ t on tid_= tid_
    left join (select sbh  sum(
           case
                 when sstkbz= then nvl(smj)
                 when sstkbz= then nvl(smj)
           end
      ) as heatingArea from  sk s  where snd = group by sbh) a on tbh_=abh
  where  towe_money_ towe_money_  = aheatingArea* and tOWE_MONEY_ > ;

  
然後循環游標對數據進行更新

復制代碼 代碼如下:

  
begin
        FOR everyRow IN D_CURSOR_CUS_INFO
        loop
         update T_CUS_YEAR_STATUS t
             set tHEATING_AREA_ = everyRowHEATING_AREA_
                 tOWE_MONEY_    = everyRowowe_money_
             where tYEAR_ =
                   and tid_ = everyRowid_;
       end loop;
      commit;
end;


From:http://tw.wingwit.com/Article/program/Oracle/201311/19086.html
    推薦文章
    Copyright © 2005-2013 電腦知識網 Computer Knowledge   All rights reserved.