此博客展示了在Oracle数据库中创建和使用序列的方式，用于为表的ID字段自动分配唯一值。通过包（package）定义了一个名为myseq的功能，该功能接受一个ID参数并返回下一个可用的序列值。博客还包含了插入数据到test表的示例，确保每次插入时ID值递增。
-- 准备
create or replace
package myseq
is
function nextv(in_id number) return number
;
end;
/
create or replace
package body myseq
is
type seqtab_type is table of integer index by pls_integer;
seqs seqtab_type;
function nextv(in_id number) return number
is
begin
seqs(in_id) := seqs(in_id)+1; -- 优化
-- 或者
-- select nvl(max(id),0)+1 into seqs(in_id)
-- from test
-- where docid = in_id;
return seqs(in_id);
exception
when no_data_found then
-- seqs(in_id) := 1;
-- 或者
select nvl(max(id),0)+1 into seqs(in_id)
from test
where docid = in_id;
return seqs(in_id);
when others then
raise;
end
;
end;
/
-- 执行
insert into test (id,docid,name) values (myseq.nextv(1),1,'sfsdf');
insert into test (id,docid,name) values (myseq.nextv(1),1,'sfsdf');
insert into test (id,docid,name) values (myseq.nextv(1),1,'sfsdf');
insert into test (id,docid,name) values (myseq.nextv(1),1,'sfsdf');
insert into test (id,docid,name) values (myseq.nextv(2),2,'sfsdf');
insert into test (id,docid,name) values (myseq.nextv(2),2,'sfsdf');
insert into test (id,docid,name) values (myseq.nextv(2),2,'sfsdf');
insert into test (id,docid,name) values (myseq.nextv(2),2,'sfsdf');
insert into test (id,docid,name) values (myseq.nextv(2),2,'sfsdf');
column name format a10
select * from test;
ID DOCID NAME
---------- ---------- ----------
1 1 sfsdf
2 1 sfsdf
3 1 sfsdf
4 1 sfsdf
1 2 sfsdf
2 2 sfsdf
3 2 sfsdf
4 2 sfsdf
5 2 sfsdf
扫一扫
4006
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
