é便åäºä¸ªï¼ä¹å¯è½å¤æäºã
å建表ï¼æå
¥æ°æ®ï¼
create table table1
(old_id varchar2(20),
new_id varchar2(20));
create table table2
(id varchar2(20));
insert into table1 values ('11111000000','40444000000');
insert into table1 values ('22202000000','55055000000');
insert into table1 values ('33000000000','66000000000');
insert into table2 values ('11111302000');
insert into table2 values ('22202882020');
insert into table2 values ('33010201000');
commit;
æ§è¡ï¼
declare
v_old_id varchar2(20);
v_new_id varchar2(20);
v_id varchar2(20);
v_str1 varchar2(20);
v_id_reverse varchar2(20);
v_s int;
cursor
c_str is
select old_id, new_id from table1;
begin
open c_str;
loop
fetch c_str
into v_old_id, v_new_id;
exit when c_str%notfound;
select reverse(v_old_id) into v_id_reverse from dual;
v_s := 1;
v_id := v_new_id;
loop
select substr(v_id_reverse, v_s, 1) into v_old_id from dual;
if v_old_id = 0 then
v_s := v_s + 1;
else
select reverse(substr(v_id_reverse,
v_s,
length(v_id_reverse) - v_s + 1))
into v_str1
from dual;
exit;
end if;
end loop;
update table2 b
set b.id =
(select substr(a.new_id, 1, length(v_str1)) ||
substr(b.id,
length(v_str1) + 1,
length(b.id) - length(v_str1))
from table1 a
where substr(a.old_id, 1, length(v_str1)) =
substr(b.id, 1, length(v_str1)))
where substr(b.id, 1, length(v_str1)) = v_str1;
commit;
end loop;
close c_str;
end;
ç»æï¼
![](https://video.ask-data.xyz/img.php?b=https://iknow-pic.cdn.bcebos.com/0dd7912397dda1444a9d2563bbb7d0a20df4864f?x-bce-process=image%2Fresize%2Cm_lfit%2Cw_600%2Ch_800%2Climit_1%2Fquality%2Cq_85%2Fformat%2Cf_auto)