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

ORACLE多表關聯UPDATE語句

2013-11-13 22:22:22  來源: Oracle 

  為了方便起見建立了以下簡單模型和構造了部分測試數據:

  在某個業務受理子系統BSS中

  客戶資料表

  create table customers

  (

  customer_id   number()    not null  客戶標示

  city_name     varchar() not null  所在城市

  customer_type char()      not null  客戶類型

  

  )

  create unique index PK_customers on customers (customer_id)

  由於某些原因客戶所在城市這個信息並不什麼准確但是在

  客戶服務部的CRM子系統中通過主動服務獲取了部分客戶%的所在

  城市等准確信息於是你將該部分信息提取至一張臨時表中

  create table tmp_cust_city

  (

  customer_id     number() not null

  citye_name     varchar() not null

  customer_type  char()   not null

  )

  ) 最簡單的形式

  經確認customers表中所有customer_id小於均為北京

  以內的均是公司走向全國之前的本城市的老客戶:)

  update customers

  set    city_name=北京

  where  customer_id<

  ) 兩表(多表)關聯update 僅在where字句中的連接

  這次提取的數據都是VIP且包括新增的所以順便更新客戶類別

  update customers   a       使用別名

  set    customer_type= 為vip為普通

  where  exists (select

  from   tmp_cust_city b

  where  bcustomer_id=acustomer_id

  )

  ) 兩表(多表)關聯update 被修改值由另一個表運算而來

  update customers a   使用別名

  set    city_name=(select bcity_name from tmp_cust_city b where bcustomer_id=acustomer_id)

  where  exists (select

  from   tmp_cust_city b

  where  bcustomer_id=acustomer_id

  )

   update 超過個值

  update customers a   使用別名

  set    (city_namecustomer_type)=(select bcity_namebcustomer_type

  from   tmp_cust_city b

  where  bcustomer_id=acustomer_id)

  where  exists (select

  from   tmp_cust_city b

  where  bcustomer_id=acustomer_id

  )

  注意在這個語句中

  =(select bcity_namebcustomer_type

  from   tmp_cust_city b

  where  bcustomer_id=acustomer_id

  )

  與

  (select

  from   tmp_cust_city b

  where  bcustomer_id=acustomer_id

  )

  是兩個獨立的子查詢查看執行計劃可知對b表/索引掃描了

  如果捨棄where條件則默認對A表進行全表

  更新但由於(select bcity_name from tmp_cust_city b where where  bcustomer_id=acustomer_id)

  有可能不能提供足夠多因為tmp_cust_city只是一部分客戶的信息

  所以報錯(如果指定的列city_name可以為NULL則另當別論)

   cannot update (%s) to NULL

  // *Cause:

  // *Action:

  一個替代的方法可以采用

  update customers a   使用別名

  set    city_name=nvl((select bcity_name from tmp_cust_city b where bcustomer_id=acustomer_id)acity_name)

  或者

  set    city_name=nvl((select bcity_name from tmp_cust_city b where bcustomer_id=acustomer_id)未知)

   當然這不符合業務邏輯了

  ) 上述)在一些情況下因為B表的紀錄只有A表的%的紀錄數

  考慮A表使用INDEX的情況使用cursor也許會比關聯update帶來更好的性能

  set serveroutput on

  declare

  cursor city_cur is

  select customer_idcity_name

  from   tmp_cust_city

  order by customer_id;

  begin

  for my_cur in city_cur loop

  update customers

  set    city_name=my_curcity_name

  where  customer_id=my_curcustomer_id;

  /** 此處也可以單條/分批次提交避免鎖表情況 **/

       if mod(city_cur%rowcount)= then

           dbms_outputput_line();

           commit;

       end if;

  end loop;

  end;

  ) 關聯update的一個特例以及性能再探討

  在oracle的update語句語法中除了可以update表之外也可以是視圖所以有以下個特例

  update (select acity_namebcity_name as new_name

  from   customers a

  tmp_cust_city b

  where  bcustomer_id=acustomer_id

  )

  set    city_name=new_name

  這樣能避免對B表或其索引的次掃描但前提是 A(customer_id) b(customer_id)必需是unique index

  或primary key否則報錯

   cannot modify a column which maps to a non keypreserved table

  // *Cause: An attempt was made to insert or update columns of a join view which

  //         map to a nonkeypreserved table

  // *Action: Modify the underlying base tables directly

  )oracle另一個常見錯誤

  回到)情況由於某些原因tmp_cust_city customer_id 不是唯一index/primary key

  update customers a   使用別名

  set    city_name=(select bcity_name from tmp_cust_city b where bcustomer_id=acustomer_id)

  where  exists (select

  from   tmp_cust_city b

  where  bcustomer_id=acustomer_id

  )

  當對於一個給定的acustomer_id

  (select bcity_name from tmp_cust_city b where bcustomer_id=acustomer_id)

  返回多余條的情況則會報如下錯誤

   singlerow subquery returns more than one row

  // *Cause:

  // *Action:

  一個比較簡單近似於不負責任的做法是

  update customers a   使用別名

  set    city_name=(select bcity_name from tmp_cust_city b where bcustomer_id=acustomer_id and rownum=)

  如何理解 錯誤在一個很復雜的多表連接update的語句經常因考慮不周出現這個錯誤

  仍已上述例子來描述一個比較簡便的方法就是將A表代入 值表達式 中使用group by 和

  having 字句查看重復的紀錄

  (select bcustomer_idbcity_namecount(*)

  from tmp_cust_city bcustomers a

  where bcustomer_id=acustomer_id

  group by bcustomer_idbcity_name

  having count(*)>=

  )


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