</p> 4) 上述3)在一些情形下,因为B表的记载只有A表的20-30%的记载数,5 ~! g+ B& G" H" a/ B8 Y
考虑A表使用INDEX的情形,使用cursor也许会比联系关系update带来更好的机能:9 w6 y8 ?, ?& l \1 ^* ^
set serveroutput on* O: y; v: k9 D! y$ D
declare
; l( q7 s& N A% f! |. i cursor city_cur is0 W, ?. G3 h0 ?4 \( j
select customer_id,city_name
7 D8 v' M2 ]2 x) E- C, x from tmp_cust_city- H; [$ K+ W. {8 m4 y, ^: W' p
order by customer_id;
8 G$ l, H; q' h: ~/ | begin- S: Q) a! S- b2 M# o6 I5 B- @
for my_cur in city_cur loop7 `9 G0 X5 N2 U% |2 k
update customers
/ b ^0 m/ r9 T { E4 x2 ? set city_name=my_cur.city_name
% k" o+ C" v; D7 a where customer_id=my_cur.customer_id;
1 i" e, m0 L# z9 Z7 U, b* Q) V /** 此处也可以单条/分批次提交,避免锁表情形 **/8 k: ~2 H' w) h. Z2 y3 p" o$ w# |
-- if mod(city_cur%rowcount,10000)=0 then
( f: |- V! q8 m -- dbms_output.put_line('----');
% }6 p, \9 M2 g -- commit;
$ s! |3 P! E0 _) L% G: u& {/ ? -- end if;* g. I1 r+ K6 ]8 d8 y6 x; q( b6 S2 e
end loop;4 M# [$ J5 O1 g( r1 y
end;
: a( y' t2 r, g& [ 5) 联系关系update的一个特例以及机能再切磋) V; |% r# B C% s5 b2 S7 X
在oracle的update语句语法中,除了可以update表之外,也可所以视图,所以有以下1个特例:
4 y, ?/ r2 L. n- \' } update (select a.city_name,b.city_name as new_name
8 T4 j; T% n( S: K2 c( [ from customers a,! ~ V) Y; Z4 T, A' p9 F
tmp_cust_city b' K6 [3 i# e2 d
where b.customer_id=a.customer_id, D9 A9 {1 |- {2 Z5 r( ?
)
' {2 y% e9 {) h$ L' m set city_name=new_name
, S. Q5 R* Q6 \. U 这样能避免对B表或其索引的2次扫描,但前提是 A(customer_id) b(customer_id)必需是unique index
" _5 P t: c; F6 j 或primary key。否则报错:% v0 k+ f1 M% x4 W) {! E: ]) A
01779, 00000, "cannot modify a column which maps to a non key-preserved table"0 i2 r3 e* K+ b* n7 M/ V$ M7 k8 P
// *Cause: An attempt was made to insert or update columns of a join view which
8 Z( O# ^- q' r // map to a non-key-preserved table.. g- o+ n( q: w z
// *Action: Modify the underlying base tables directly.; |" b/ ~9 ?. h( q
6)oracle另一个常见错误. K1 H4 O5 | d- c7 H
回到3)情形,因为某些原因,tmp_cust_city customer_id 不是独一index/primary key
) U7 G8 B/ N# I/ A+ U/ T update customers a -- 使用别号
2 S4 C# b7 N6 Q: R/ P set city_name=(select b.city_name from tmp_cust_city b where b.customer_id=a.customer_id)
" Z ^! q( u' N F$ ~ where exists (select 1
9 |/ n% d1 L+ D9 i' `5 q7 r4 s3 n from tmp_cust_city b
. |- @5 ?$ I9 r$ a) _ where b.customer_id=a.customer_id% C4 _6 [5 W5 y
)$ }, P \1 t* V! B
当对于一个给定的a.customer_id
5 z* \: `0 H5 K3 ]% i6 _! f' g (select b.city_name from tmp_cust_city b where b.customer_id=a.customer_id)
/ v, ]- C$ m0 x9 C: z: U1 I* E 返回多余1条的情形,则会报如下错误:$ g1 f1 E+ e& W. l/ h
01427, 00000, "single-row subquery returns more than one row"0 }2 }' y+ M! p& w* ~6 {
// *Cause:
5 y* a( n, C6 F6 x& S) N2 z // *Action:
6 h) }; I: ?$ Y; ?1 N. |2 l5 u2 o 一个斗劲简单近似于不负责任的做法是 h& K% K+ r, b! u( `1 |. E
update customers a -- 使用别号2 C( y8 P) y( V1 h& p- F( U- |
set city_name=(select b.city_name from tmp_cust_city b where b.customer_id=a.customer_id)& p+ z; S- s& v# ~. \
若何理解 01427 错误,在一个很复杂的多表毗连update的语句,经常因考虑不周,呈现这个错误,
4 J; T9 Z* ?- A 仍已上述例子来描述,一个斗劲精练的体例就是将A表代入 值表达式 中,使用group by 和
w- T" h7 ^) x: w having 字句查垂青复的记载
' F9 I3 G4 d" P& S; g1 p (select b.customer_id,b.city_name,count(*)& x9 u* r4 }- k8 F
from tmp_cust_city b,customers a/ F U, f% w7 O' i- a4 w
where b.customer_id=a.customer_id
3 q/ A- B. G+ l1 U, \4 l( H2 q group by b.customer_id,b.city_name; T) N9 G( a6 [; Y* l* p: F
having count(*)>=2
6 S( v6 H8 D- H5 k ]/ Z ) |