oracle多对一更新,oracle遍历表更新另一个表（一对多）

declare

cursor cur_test is

select t.txt_desig,

m.segment_id,

s.code_type_direct,

case

when s.uom_dist_ver_lower = ‘FL‘ then

s.uom_dist_ver_lower || s.val_dist_ver_lower

else

to_char(s.val_dist_ver_lower)

end as val

from nvduser11.en_route_rte t

right join nvduser11.rte_seg s

left join fmap2.segment m

on s.segment_id = m.segment_id on

t.en_route_rte_id = s.en_route_rte_id;

begin

for c in cur_test loop

update fmap2.segment

set first =

(case

when first is null or first = ‘‘ then

c.txt_desig

else

to_char(first)

end),

second =

(case

when (first is not null or first != ‘‘) and

(second is null or second = ‘‘) then

c.txt_desig

else

to_char(second)

end),

third =

(case

when (second is not null or second != ‘‘) and

(first is not null or first != ‘‘) and (third is null or third = ‘‘) then

c.txt_desig

else

to_char(third)

end),

firstdirect =

(case

when first is null or first = ‘‘ then

c.code_type_direct

else

to_char(firstdirect)

end),

firstaltitude =

(case

when first is null or first = ‘‘ then

c.val

else

to_char(firstaltitude)

end),

seconddirect =

(case

when (first is not null or first != ‘‘) and

(second is null or second = ‘‘) then

c.code_type_direct

else

to_char(seconddirect)

end),

secondaltitude =

(case

when (first is not null or first != ‘‘) and

(second is null or second = ‘‘) then

c.val

else

to_char(secondaltitude)

end),

thirddirect =

(case

when (second is not null or second != ‘‘) and

(first is not null or first != ‘‘) and (third is null or third = ‘‘) then

c.code_type_direct

else

to_char(thirddirect)

end),

thirdaltitude =

(case

when (second is not null or second != ‘‘) and

(first is not null or first != ‘‘) and (third is null or third = ‘‘) then

c.val

else

to_char(thirdaltitude)

end)

where segment_id = c.segment_id;

end loop;

commit;

end;

原文：http://www.cnblogs.com/weihongli/p/4180730.html