SQL语句如何实现按递增赋值

table1

n1 n2
--------
a
b
e
r

实现

n1 n2
--------
a z0001
b z0002
e z0003
r z0004

SQL> select * from table1;

N1 N2
-- -----
A
B
E
R
create or replace procedure ppppp is
2 v_rn int:=0;
3 begin
4 for c1 in (select n1 from table1 order by n1 ) loop
5 v_rn:=v_rn+1;
6 update table1
7 set n2= 'z ' ¦ ¦lpad(v_rn,3, '0 ')
8 where n1=c1.n1;
9 end loop;
10 commit;
11 end;
12
13 /

Procedure created
execute ppppp;

PL/SQL procedure successfully completed

SQL> select * from table1;

N1 N2
-- -----
A z001
B z002
E z003
R z004

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/10768286/viewspace-83472/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/10768286/viewspace-83472/