把N2分成两个字段好了 n21 n22 n1 n21 n22 ---------------------- a z 0001 b z 0002 e z 0003 r z 0004
BEGIN FOR c IN (SELECT ROWID AS rid, Row_Number() over (ORDER BY n1) AS rn FROM table1) LOOP UPDATE table1 SET n21='z', n22=SubStr('0000'||To_Char(c.rn),-4) WHERE ROWID=c.rid; END LOOP; COMMIT; END; /
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 completedSQL> select * from table1;
N1 N2 -- ----- A z001 B z002 E z003 R z004
-- Create sequence create sequence seq_test minvalue 1 maxvalue 99999999999999999 start with 1 increment by 1;create table t_test as select n1,to_char(seq_test.nextval,'fm0000') n2 from table1;drop table1; rename t_test table1;ok
--无敌小脚本 create table temp as select * from talbe1; delete table temp; insert into temp select ta.n1,SubStr('0000'||To_Char(ta.a),-4) from (select row_number() over(order by n1) a,t.* from table1 t) ta; drop tabel table1; create table table1 as select * from temp; drop table temp;
n1 n21 n22
----------------------
a z 0001
b z 0002
e z 0003
r z 0004
FOR c IN
(SELECT ROWID AS rid,
Row_Number() over (ORDER BY n1) AS rn
FROM table1)
LOOP
UPDATE table1
SET n21='z',
n22=SubStr('0000'||To_Char(c.rn),-4)
WHERE ROWID=c.rid;
END LOOP;
COMMIT;
END;
/
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 completedSQL> select * from table1;
N1 N2
-- -----
A z001
B z002
E z003
R z004
create sequence seq_test
minvalue 1
maxvalue 99999999999999999
start with 1
increment by 1;create table t_test as
select n1,to_char(seq_test.nextval,'fm0000') n2
from table1;drop table1;
rename t_test table1;ok
create table temp as select * from talbe1;
delete table temp;
insert into temp select ta.n1,SubStr('0000'||To_Char(ta.a),-4)
from (select row_number() over(order by n1) a,t.* from table1 t) ta;
drop tabel table1;
create table table1 as select * from temp;
drop table temp;