oracle自增序列

article/2025/9/21 6:08:05

1.创建表TABLESPACETEST

-- Create table
create table TABLESPACETEST
(contract_no       VARCHAR2(100) primary key,contract_start_dt DATE,contract_end_dt   DATE,loan_amt          NUMBER(20,6),loan_bal          NUMBER(20,6)
)
tablespace SYSTEM;

2.创建自增序列：

create sequence SEQ_TABLESPACETEST_con minvalue 1 maxvalue 999999   increment by 1    start with 1;

摘自百度文库：

创建序列需要CREATE SEQUENCE系统权限。序列的创建语法如下：
CREATE SEQUENCE 序列名
[INCREMENT BY n]
[START WITH n]
[{MAXVALUE/ MINVALUE n| NOMAXVALUE}]
[{CYCLE|NOCYCLE}]
[{CACHE n| NOCACHE}];
其中：
1) INCREMENT BY⽤于定义序列的步长，如果省略，则默认为1，如果出现负值，则代表Oracle序列的值是按照此步长递减的。
2) START WITH 定义序列的初始值(即产⽣的第⼀个值)，默认为1。
3) MAXVALUE 定义序列⽣成器能产⽣的最⼤值。选项NOMAXVALUE是默认选项，代表没有最⼤值定义，这时对于递增Oracle序列，系统
能够产⽣的最⼤值是10的27次⽅;对于递减序列，最⼤值是-1。
4) MINVALUE定义序列⽣成器能产⽣的最⼩值。选项NOMAXVALUE是默认选项，代表没有最⼩值定义，这时对于递减序列，系统能够产
⽣的最⼩值是?10的26次⽅;对于递增序列，最⼩值是1。
5) CYCLE和NOCYCLE 表⽰当序列⽣成器的值达到限制值后是否循环。CYCLE代表循环，NOCYCLE代表不循环。如果循环，则当递增
序列达到最⼤值时，循环到最⼩值;对于递减序列达到最⼩值时，循环到最⼤值。如果不循环，达到限制值后，继续产⽣新值就会发⽣错误。
6) CACHE(缓冲)定义存放序列的内存块的⼤⼩，默认为20。NOCACHE表⽰不对序列进⾏内存缓冲。对序列进⾏内存缓冲，可以改善序列
的性能。
⼤量语句发⽣请求，申请序列时，为了避免序列在运⽤层实现序列⽽引起的性能瓶颈。Oracle序列允许将序列提前⽣成 cache x个先存⼊内
存，在发⽣⼤量申请序列语句时，可直接到运⾏最快的内存中去得到序列。但cache个数也不能设置太⼤，因为在数据库重启时，会清空内
存信息，预存在内存中的序列会丢失，当数据库再次启动后，序列从上次内存中最⼤的序列号+1 开始存⼊cache x个。这种情况也能会在数
据库关闭时也会导致序号不连续。
7) NEXTVAL 返回序列中下⼀个有效的值，任何⽤户都可以引⽤。
8) CURRVAL 中存放序列的当前值,NEXTVAL 应在 CURRVAL 之前指定，⼆者应同时有效。
--------------------------------------------------------
作者：天神太院明骞骞010
链接：https://wenku.baidu.com/view/13bc00f4270c844769eae009581b6bd97e19bc50.html
来源：百度文库

3.创建触发器：（注解来源于网络）


create  trigger TRIG_TABLESPACETEST_con      
before insert on TABLESPACETEST /*触发条件：当表TABLESPACETEST执行插入操作时触发此触发器*/ for each row   /*对每一行都检测是否触发*/  
begin       
/*触发后执行的动作，在此是取得序列SEQ_SEQ_TABLESPACETEST_con的下一个值插入到表BC_ES_IK_HOT_WORD中的id字段中*/ 
select SEQ_TABLESPACETEST_con.nextval into :new.CONTRACT_NO from dual;  
end;

4.插入2条新数据

contract_no实现自增长，for update,insert语句都可以，只需要新增非contract_no字段内容即可。

结果：

5.测试根据不同情况序列变化

（1）测试删除其中一条再新增一条

delete TABLESPACETEST where contract_no=2;
commit;
insert into TABLESPACETEST (CONTRACT_START_DT,CONTRACT_END_DT,LOAN_AMT,LOAN_BAL) 
values(TO_date('2022/01/01','YYYY/MM/DD'),TO_date('2022/12/01','YYYY/MM/DD'),'80000','80000');
insert into TABLESPACETEST (CONTRACT_START_DT,CONTRACT_END_DT,LOAN_AMT,LOAN_BAL) 
values(TO_date('2022/01/01','YYYY/MM/DD'),TO_date('2022/12/01','YYYY/MM/DD'),'80000','80000');
commit;

查询表数据：

结果：

发现序列自己增长到了21，不是想象中的3。经过分析发现序列经过查询每次也会自增

select SEQ_TABLESPACETEST_con.nextval from dual

查询出当前序列值是23，所以再次插入一条数据显示24，然后查询上述SELECT语句，查询5次后，序列值为29，再次插入一条数据，数据contract_no显示为30 。所以查询序列也会造成序列自增长。

（2）插入一条带固定值CONTRACT_NO的数据

insert into TABLESPACETEST (CONTRACT_NO,CONTRACT_START_DT,CONTRACT_END_DT,LOAN_AMT,LOAN_BAL) 
values('44',TO_date('2022/01/01','YYYY/MM/DD'),TO_date('2022/12/01','YYYY/MM/DD'),'70000','60000');
commit;

结果：

自定义字段还是按照序列的下一个值显示，固定值无效。等价于

insert into TABLESPACETEST (CONTRACT_START_DT,CONTRACT_END_DT,LOAN_AMT,LOAN_BAL) 
values(TO_date('2022/01/01','YYYY/MM/DD'),TO_date('2022/12/01','YYYY/MM/DD'),'70000','60000');insert into TABLESPACETEST (CONTRACT_NO,CONTRACT_START_DT,CONTRACT_END_DT,LOAN_AMT,LOAN_BAL) 
values(SEQ_TABLESPACETEST_con.nextval,TO_date('2022/01/01','YYYY/MM/DD'),TO_date('2022/12/01','YYYY/MM/DD'),'70000','60000');

（3）修改序列的极限值

alter sequence SEQ_TABLESPACETEST_con  maxvalue 50

查询配置用户信息确认修改成功：

 select * from USER_SEQUENCES where SEQUENCE_NAME='SEQ_TABLESPACETEST_CON';

结果：

表结构注释：

sequence_name：SEQUENCE name序列名称
min_value：序列的最小值
max_value：序列的最大值
increment_by：序列递增的值
cycle_flag：序列是否在达到极限时结束？
order_flag：序列号是按顺序生成的吗？
cache_size：要缓存的序列号数（最小设置为2）
last_number：写入磁盘的最后序列号