把那个字段设为NUMBER类型, 假定表为TAB,关键字段为IDX,所增加的字段为ADD_FIELD 然后执行下面语句 declare i number := 0; begin for c_idx in (select idx from tab) loop select max(add_field) into i from tab; if i is null then i := 1; else i := i + 1; end if; update tab set add_field = i where idx = c_idx.idx; end loop; commit; end;
为什么还要再select max(add_field) into i from tab;? 用i记住不行吗?
先建个表A(增加一个序列)把实际表B数据COPY到表A中删除表B把表A名称改为表B
先建立一个序列号 create sequence SEQ_ZSF_ID minvalue 1 maxvalue 9999 start with 21 increment by 1 cache 20 order;然后直接 update table set 字段 = SEQ_ZSF_ID.nextval
先增加表字段: alter table emp add (col number(10));再写个存储过程: procedure temp is i number(10); row emp%rowtype; cursor cur_emp is select * from emp for update; begin i:=0; open cur_emp; loop fetch cur_emp into row; exit when cur_emp%notfound; i:=i+1; update emp set col=i where current of cur_temp; end loop; end;
update table set a = rownum
假定表为TAB,关键字段为IDX,所增加的字段为ADD_FIELD
然后执行下面语句
declare
i number := 0;
begin
for c_idx in (select idx from tab) loop
select max(add_field) into i from tab;
if i is null then
i := 1;
else
i := i + 1;
end if;
update tab set add_field = i where idx = c_idx.idx;
end loop;
commit;
end;
用i记住不行吗?
create sequence SEQ_ZSF_ID
minvalue 1
maxvalue 9999
start with 21
increment by 1
cache 20
order;然后直接
update table set 字段 = SEQ_ZSF_ID.nextval
alter table emp
add (col number(10));再写个存储过程:
procedure temp is
i number(10);
row emp%rowtype;
cursor cur_emp is select * from emp for update;
begin
i:=0;
open cur_emp;
loop
fetch cur_emp into row;
exit when cur_emp%notfound;
i:=i+1;
update emp set col=i where current of cur_temp;
end loop;
end;