declare cursor cur_for_update is select a.serialno/*A表主键*/,a.Model_Name from A a v_model_name varchar2(100); v_serialno number(10); begin open cur_for_update;loop fetch cur_for_update into v_serialno,v_model_name ;exit when cur_for_update%notfound; v_model_name:=replace(v_model_name,'ModelA ','Model1'); v_model_name:=replace(v_model_name,'ModelB ','Model2'); ---如果没有特殊之可以 ---v_model_name:=translate(v_model_name,'ABCDEFG','1234567'); update A set Model_Name=v_model_name where serialno=v_serialno end loop; close cur_for_update; end;
update A set Model_Name = Substr(Model_Name, 1, 4)||decode(Substr(Model_Name, 5, 1),'A','1','B','2')||Substr(Model_Name, 6, length(Model_Name))
update a a1 set model_name=(select rn from ( select rowid rid,substr(model_name,1,4)||decode(substr(model_name,5,1),'a','1','b','2')||substr el_name,6) rn from a ) a2 where a1.rowid=a2.rid ) /新3行。 select * from a / ID MODEL_NAME ------ ---------- 1 mode1 2 mode2 3 mode1ble
update A set Model_Name = Substr(Model_Name, 1, 4) ||decode(Substr(Model_Name, 5, 1),'A','1','B','2') ||Substr(Model_Name, 6);
Model_Name = Substr(Model_Name, 1, 4)||decode(Substr(Model_Name, 5, 1),'A','1','B','2')
||Substr(Model_Name, 6, length(Model_Name))
cursor cur_for_update is select a.serialno/*A表主键*/,a.Model_Name from A a
v_model_name varchar2(100);
v_serialno number(10);
begin
open cur_for_update;loop
fetch cur_for_update into v_serialno,v_model_name ;exit when cur_for_update%notfound;
v_model_name:=replace(v_model_name,'ModelA ','Model1');
v_model_name:=replace(v_model_name,'ModelB ','Model2');
---如果没有特殊之可以
---v_model_name:=translate(v_model_name,'ABCDEFG','1234567');
update A set Model_Name=v_model_name where serialno=v_serialno
end loop;
close cur_for_update;
end;
set Model_Name = Substr(Model_Name, 1, 4)||decode(Substr(Model_Name, 5, 1),'A','1','B','2')||Substr(Model_Name, 6, length(Model_Name))
update a a1 set model_name=(select rn from (
select rowid rid,substr(model_name,1,4)||decode(substr(model_name,5,1),'a','1','b','2')||substr
el_name,6) rn
from a ) a2
where a1.rowid=a2.rid )
/新3行。 select * from a
/ ID MODEL_NAME
------ ----------
1 mode1
2 mode2
3 mode1ble
set Model_Name = Substr(Model_Name, 1, 4)
||decode(Substr(Model_Name, 5, 1),'A','1','B','2')
||Substr(Model_Name, 6);