Oracle 使用拼接字符串更新表
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)
- /
评论暂时关闭