oracle 使用拼接字符串更新表

//数据 ta col_1 col_2 ----------- a b c d //结果： col_1 col_2 ----------- a z0001 b z0002 c z0003 d z0004 // create table ta( col_1 varchar2(2), col_2 varchar2(7)) / insert into ta select 'a','' from dual union all select 'b','' from dual union all select 'c','' from dual union all select 'd','' from dual / //解法一： declare rn number :=0; begin for cl in (select col_1 from ta order by col_1) loop rn :=rn+1; update ta set col_2='z'||lpad(rn,4,'0') where col_1=cl.col_1; commit; end loop; end; / //将表还原为原来的状态： update ta set col_2='' where col_1 in ('a','b','c','d');//col_1 is not null; //解法二： begin for c in (select rowid rid, row_number() over (order by col_1) rn from ta) loop update ta set col_2='z000'||c.rn where rowid=c.rid; end loop; commit; end; / //解法三： create table tb as select * from ta where 1=0 / insert into tb select col_1,'z000'||rn from ( select rownum rn,ta.col_1 col_1 from ta) / 

原帖：http://topic.youkuaiyun.com/u/20071113/10/e4496149-5cbe-42fc-ab90-dcc65fe8a2c6.html?80905