Fork me on GitHub

Oracle SEQUENCE使用

序列(SEQUENCE)是序列号生成器，可以为表中的行自动生成序列号，产生一组等间隔的数值(类型为数字)。序列主要用于生成主键。

建立序列

CREATE SEQUENCE sequence_name 
[INCREMENT BY n] 
[START WITH n] 
[{MAXVALUE/MINVALUE n|NOMAXVALUE/NOMINVALUE}] 
[{CYCLE|NOCYCLE}] 
[{CACHE n|NOCACHE}];
  • INCREMENT BY：用于定义序列的步长，如果省略，则默认为1，如果出现负值，则代表序列的值是按照此步长递减的。
  • START WITH ：定义序列的初始值(即产生的第一个值)，默认为1。
  • MAXVALUE ：定义序列生成器能产生的最大值。选项NOMAXVALUE是默认选项，代表没有最大值定义，这时对于递增序列，系统能够产生的最大值是10的27次方；对于递减序列，最大值是-1。
  • MINVALUE：定义序列生成器能产生的最小值。选项NOMINVALUE是默认选项，代表没有最小值定义，这时对于递减序列，系统能够产生的最小值是？10的26次方；对于递增序列，最小值是1。
  • CYCLE和NOCYCLE：表示当序列生成器的值达到限制值后是否循环。CYCLE代表循环，NOCYCLE代表不循环。如果循环，则当递增序列达到最大值时，循环到最小值；对于递减序列达到最小值时，循环到最大值。如果不循环，达到限制值后，继续产生新值就会发生错误。
  • CACHE：(缓冲)定义存放序列的内存块的大小，默认为20。NOCACHE表示不对序列进行内存缓冲。对序列进行内存缓冲，可以改善序列的性能。（高级操作，一般人用不到）

使用序列

  • 取序列的下一值（首次取初值）

    SELECT sequence_name.nextval FROM dual;

  • 取序列当前值

    SELECT sequence_name.currval FROM dual;

  • 查看所有序列

    SELECT * FROM user_sequences;

更新序列

ALTER SEQUENCE sequence_name
[INCREMENT BY n] 
[{MAXVALUE/MINVALUE n|NOMAXVALUE/NOMINVALUE}] 
[{CYCLE|NOCYCLE}] 
[{CACHE n|NOCACHE}];

注意：ALTER不能修改START WITH，如果要修改，只能删除后重建。

删除序列

DROP SEQUENCE sequence_name;

举例

CREATE SEQUENCE SEQ_CCI554 START WITH 1 INCREMENT BY 1 MAXVALUE 9999;
DECLARE
  I INT:=1;
BEGIN
  LOOP
    INSERT INTO CI54 (CCI554, AAC001, AAB001, CCD001) VALUES(
     '80'||'030005'||LPAD(TO_CHAR(SEQ_CCI554.nextval),4,'0'), '622925199012056023', '0205000027', '170010602898');
    COMMIT;
    EXIT WHEN I>=12;
    I:=I+1;
  END LOOP;
END;

其中||用于字符串拼接，LPAD()用于固定位数字符串补齐。

需要注意的是，首句CREATE SEQUENCE只能执行一次，序列创建后除非DROP，否则不会自动消失。我在使用JDBC连接的时候就犯了这个错误。

小技巧：重置序列

重置序列的方法一般有两个。

  • 第一种方法是DROP之后重新CREATE，这种方式有很多弊端，DROP之后CREATE之前依赖它的函数和存储过程将失效，需要重新编译。

  • 第二种方法比较巧妙，利用递增参数：先查出SEQUENCE的currval，记为value，然后将递增参数改为-value，取它的nextval，再将递增参数改回来。奉上大佬写的存储过程。

    CREATE OR REPLACE PROCEDURE SEQ_RESET(sequence_name VARCHAR2) AS n NUMBER(10);
TSQL VARCHAR2(100);
 BEGIN
 EXECUTE IMMEDIATE 'SELECT '||sequence_name||'.currval FROM dual' INTO n;
  n:=-n;
  TSQL:='ALTER SEQUENCE '||sequence_name||' INCREMENT BY '|| n;
  EXECUTE IMMEDIATE TSQL;
 EXECUTE IMMEDIATE 'SELECT '||sequence_name||'.nextval FROM dual' INTO n;
  TSQL:='ALTER SEQUENCE '||sequence_name||' INCREMENT BY 1';
 EXECUTE IMMEDIATE TSQL;
 END SEQ_RESET;

参考文章

Comments