oracle 使用拼接字符串更新表

koinbugjlr

　　//数据tacol_1 col_2-----------abcd//结果：col_1 col_2-----------a     z0001b     z0002c     z0003d     z0004//create table ta(col_1 varchar2(2),col_2 varchar2(7))/insert into taselect 'a','' from dual union allselect 'b','' from dual union allselect 'c','' from dual union allselect 'd','' from dual///解法一：declarern number :=0;beginfor cl in (select col_1 from ta order by col_1) looprn :=rn+1;update taset col_2='z'||lpad(rn,4,'0')where col_1=cl.col_1;commit;end loop;end;///将表还原为原来的状态：update taset col_2=''where col_1 in ('a','b','c','d');//col_1 is not null;//解法二：beginfor c in (select rowid rid,row_number() over (order by col_1) rnfrom ta)loopupdate taset col_2='z000'||c.rnwhere rowid=c.rid;end loop;commit;end;///解法三：create table tb asselect * from ta where 1=0/insert into tbselect col_1,'z000'||rnfrom (select rownum rn,ta.col_1 col_1from ta)/
　　原帖：http://topic.csdn.net/u/20071113/10/e4496149-5cbe-42fc-ab90-dcc65fe8a2c6.html?80905