update更新多行資料（oracle）

標籤：

轉自：http://blog.itpub.net/25322446/viewspace-767505

 

說明：筆記總結了在工作中遇到過的幾種update方法和各種方法適用的範圍。

 

1.單表更新
方案：使用標準update文法即可，執行穩定且效率較高

update tableset (column1,column2,...)=value1,value2,...;

 

2.多表關聯更新
舉例：更新gkfq_rec表中所有slid與oa2_ftask表fi_inst相同的行，blzt欄位值=oa2_ftask表的ft_lstate。

create table gkfq_rec (slid char(12) parimary key,blzt varchar2(50),wjbt varchar2(100) not null,........);create table oa2_ftask (fi_inst char(12) parimary key,fi_state int not null,ft_lstate int not null,...);

 

 

方法描述	適用範圍	運行效率
傳統方案	一般情況適用	單表更新效率高且穩定，多表時效率較慢
inline view更新法	關聯欄位為主鍵	速度較快
merge更新法	關聯欄位非主鍵，適用於兩表關聯	非主鍵關聯表更新，速度較快
快速遊標更新法	邏輯較複雜的情況	複雜邏輯時效率很高

 

（1）傳統方案（速度可能最慢）

update gkfq_rec a set blzt=(select b.ft_lstate from oa2_ftask b where a.slid=b.fi_inst)where exists(select 1 from oa2_ftask b where a.slid=b.fi_inst);

 

//子查詢返回多行值時，通過where exists條件逐行過濾，一一匹配實現set唯一值

（2）inline view更新法（關聯主鍵欄位，速度較快）
方案：更新一個臨時建立的視圖。要求B表的主鍵欄位必須在where條件中，並且是以=號來關聯被更新表，否則可能報錯：ORA-01779:無法修改與非索引值儲存表對應的列。當B表主鍵欄位為多列組合時，也有可能出現這一報錯。

update (select a.blzt as blzt,b.ft_lstate as ft_lstatefrom gkfq_rec a,oa2_ftask b where a.slid=b.fi_inst) set blzt=ft_lstate;

 

（3）merge更新法（關聯欄位非主鍵時，速度較快）
文法：

MERGE INTO table_name alias 1USING (table|view|sub_query) alias 2ON (join condition)WHEN MATCHED THENUPDATE SET col1=col_val1,    col2=col_val2WHEN NOT MATCHED THENINSERT (column_list) VALUES (column_values);

 

方案：在alias2中select出來的資料，每一條都跟alias1進行ON (join condition)比較，若匹配，就進行更新操作，不匹配，執行插入操作。merge不會返回影響行數，且最多隻能兩表關聯，適用於串連條件不是主鍵的欄位。

merge into gkfq_rec ausing oa2_ftask bon (a.slid=b.fi_inst)when matched thenupdate set a.blzt=b.ft_lstate;

 

（4）快速遊標更新法（複雜邏輯時，效率很高）
文法：

beginfor cr in (查詢語句) loop  --迴圈update table_name set ...   --更新語句（根據查詢出來的結果集合）end loop;  --結束迴圈end;

 

方案：配合oracle專屬的內建ROWID物理欄位，使用快速遊標，不需要定義，直接把遊標寫到for迴圈中，快速定位並執行更新。它可以支援複雜邏輯的查詢語句，更新準確，無論資料多大更新效率依然很高。但執行後不返回影響行數。

beginfor aa in (select a.rowid as rowid,b.ft_lstate as ft_lstate from gkfq_rec a,oa2_ftask bwhere a.slid=b.fi_inst ) loopupdate gkfq_rec set blzt=aa.ft_lstatewhere rowid=aa.rowid;end loop;end;

 

update更新多行資料（oracle）