不想使用序列,我建了一张表,表中仅两个字段,表名,最大ID值
存储过程getnextid带参数tablename in,nextid out,increment in
调用getnextid取得某个表的可用ID,getnextid怎么写?要用merge into吗:
存储过程getnextid带参数tablename in,nextid out,increment in
调用getnextid取得某个表的可用ID,getnextid怎么写?要用merge into吗:
insert into t_seqno values ('T1', 0);
insert into t_seqno values ('T2', 0);
commit;create or replace procedure getnextid
( tablename varchar2,
nextid out number,
incr number)
is
pragma autonomous_transaction;
begin
update t_seqno set tno = tno + incr where tname = tablename;
select tno into nextid from t_seqno where tname = tablename;
commit;
exception
when others then nextid := 0; rollback;
end;