[原创]Oracle自增序列

SQL> create table tab001( id number(10) primary key not null, text varchar2(100byte) not null);                                  
																																 
Table created.

SQL>    

SQL> create sequence tab0_seq increment by 1 start with 10 maxvalue 99999999 nocycle nocache;                                    
																																 
Sequence created.

SQL> 



INCREMENT BY 用于指定序列增量（默认值：1），如果指定的是正整数，则序列号自动递增，如果指定的是负数，则自动递减。
START WITH 用于指定序列生成器生成的第一个序列号，当序列号顺序递增时默认值为序列号的最小值，当序列号顺序递减时默认值为序列号的最大值。
MAXVALUE 用于指定序列生成器可以生成的组大序列号（必须大于或等于 START WITH，并且必须大于 MINVALUE），默认为 NOMAXVALUE。
MINVALUE 用于指定序列生成器可以生成的最小序列号（必须小于或等于 START WITH，并且必须小于 MAXVALUE），默认值为 NOMINVALUE。
CYCLE 用于指定在达到序列的最大值或最小值之后是否继续生成序列号，默认为 NOCYCLE。
CACHE 用于指定在内存中可以预分配的序列号个数（默认值：20）。

注意点:

第一次 NEXTVAL 返回的是初始值；随后的 NEXTVAL 会自动增加 INCREMENT BY 对应的值，然后返回增加后的值。
CURRVAL 总是返回当前 SEQUENCE 的值，但是在第一次 NEXTVAL 初始化之后才能使用 CURRVAL ，否则会出错。
一次 NEXTVAL 会增加一次 SEQUENCE 的值，所以如果在同一个语句里面使用多个NEXTVAL，其值就是不一样的。
如果指定 CACHE 值，Oracle 就可以预先在内存里面放置一些 SEQUENCE，这样存取的快些。 CACHE 里面的取完后，Oracle 自动再取一组到 CACHE。
但使用 CACHE 或许会跳号，比如数据库突然不正常关闭（shutdown abort)， CACHE 中的 SEQUENCE 就会丢失。所以可以在 CREATE SEQUENCE 的时候用 NOCACHE 防止这种情况。



SQL> insert into tab001(id,text) values (tab0_seq.nextval,'test0000000001');

1 row created.

SQL> insert into tab001(id,text) values (tab0_seq.nextval,'test0000000002');

1 row created.

SQL>

SQL> col text for a30
SQL> select * from tab001;

		ID TEXT
---------- ------------------------------
		10 test0000000001
		11 test0000000002

SQL>

如上方式是否比较麻烦，需要每次指定tab0_seq.nextval? 是否Oracle本身可以帮我们管理? 

11g是通过触发器来实现。


SQL>CREATE OR REPLACE TRIGGER TAB001_ID_SEQ_TRG
BEFORE INSERT ON "TAB001"
FOR EACH ROW
WHEN (NEW."ID" IS NULL)
BEGIN
  SELECT TAB0_SEQ.NEXTVAL
  INTO :NEW."ID"
  FROM DUAL;
END;
 /

Trigger created.

SQL>   
	
SQL> insert into tab001(text) values('test0000000003_trg');

1 row created.

SQL> select * from tab001;

		ID TEXT
---------- ------------------------------
		10 test0000000001
		11 test0000000002
		12 test0000000003_trg

SQL> 
SQL> insert into tab001(id, text) values(null,'test0000000003_trg');

1 row created.

SQL> select * from tab001;

		ID TEXT
---------- ------------------------------
		10 test0000000001
		11 test0000000002
		12 test0000000003_trg
		13 test0000000003_trg

SQL>   

备注:

上面两种写法都可，id为null或者不用指定id进行values插入数据

但是又显示指定id，如下：

SQL> insert into tab001(id, text) values(100,'test0000000003_trg');

1 row created.

SQL> select * from tab001;

		ID TEXT
---------- ------------------------------
		10 test0000000001
		11 test0000000002
		12 test0000000003_trg
		13 test0000000003_trg
	   100 test0000000003_trg

SQL> insert into tab001(id, text) values(null,'test0000000004_trg_1');

1 row created.

SQL> select * from tab001;                                                                                                       

		ID TEXT
---------- ------------------------------
		10 test0000000001
		11 test0000000002
		12 test0000000003_trg
		13 test0000000003_trg
	   100 test0000000003_trg
		14 test0000000004_trg_1

6 rows selected.

SQL>

可以知道，插入100之后下一条触发器生成的序列式14，不是101，这个时候需要注意，如果id设置为主键不能重复的，如果达到序列到达到100之后会报错，这个时候需要规范insert的方式，通过触发器管理sequence，insert时候不能显示的去插入数据。否则如下报错：

~省略~
	98 test0000000004_trg_1
	99 test0000000004_trg_1

91 rows selected.

SQL> insert into tab001(id, text) values(null,'test0000000004_trg_1');
insert into tab001(id, text) values(null,'test0000000004_trg_1')
*
ERROR at line 1:
ORA-00001: unique constraint (LIN.SYS_C0017505) violated

SQL>  

CREATE TABLE "TEST001_12c" (
	ID NUMBER(11) GENERATED BY DEFAULT ON NULL AS IDENTITY,
	TEXT VARCHAR2(20BYTE) NOT NULL
);