详解ORACLE SEQUENCE用法

在Oracle数据库中，SEQUENCE（序列）是常用的用来生成唯一数值的对象，它可以被多个会话访问，并按照指定规则生成连续的数值。本文将详细讲解SEQUENCE的用法，并附带两个示例。

SEQUENCE的创建和基本用法

在Oracle中，可以通过以下语句创建SEQUENCE：

CREATE SEQUENCE sequence_name
    [INCREMENT BY increment_value]
    [START WITH start_value]
    [MAXVALUE max_value | NOMAXVALUE]
    [MINVALUE min_value | NOMINVALUE]
    [CYCLE | NOCYCLE]
    [CACHE cache_size | NOCACHE];

其中，各参数的含义如下：

• sequence_name：序列的名称。
• INCREMENT BY：序列每次增加的步长，默认为1。
• START WITH：序列生成数值的起始值，默认为1。
• MAXVALUE：序列生成的最大值。如果没有指定，会使用一个默认最大值，如果MAXVALUE被设置成NOMAXVALUE，则为无限大。
• MINVALUE：序列生成的最小值。如果没有指定，会使用一个默认最小值，如果MINVALUE被设置成NOMINVALUE，则为无限小。
• CYCLE：当SEQENCE达到最大值时是否循环。
• CACHE：序列缓存大小，默认为20。当Oracle生成序列时，会生成一段缓存区域，如果缓存的数量大于实际使用的数量，会减少I/O开销。如果设置成NOCACHE，则不使用序列缓存。

在创建SEQUENCE之后，可以通过以下语句使用SEQUENCE：

SELECT sequence_name.NEXTVAL FROM dual;

其中，NEXTVAL是SEQUENCE对象的一个方法，用来获取下一个数值。

SEQUENCE的示例用法1

下面以商品订单表为例讲解SEQUENCE的用法。假设我们已有一个商品订单表，需要生成订单编号来唯一标识每个订单。可以通过如下语句创建订单编号的SEQUENCE：

CREATE SEQUENCE order_id_seq
    INCREMENT BY 1
    START WITH 1
    MAXVALUE 99999999999
    MINVALUE 1
    CYCLE
    CACHE 100;

每次通过如下SQL语句生成订单编号：

SELECT 'SD'||LPAD(order_id_seq.NEXTVAL,8,0) order_id FROM dual;

说明一下：

• SD是指定的前缀，可以是任意字母，LPAD函数用来将序列生成的数值左侧补0，让位数为8位。

SEQUENCE的示例用法2

有一个员工信息表，需要为每个员工信息生成唯一的员工号。假设具体要求如下：

1. 员工号为自然数，长度不超过4位。
2. 该员工号的前两位为年份，后两位为序列号。

可以通过如下语句创建员工号的SEQUENCE：

CREATE SEQUENCE emp_id_seq
    INCREMENT BY 1
    START WITH 1
    MAXVALUE 9999
    MINVALUE 1
    CYCLE
    CACHE 100;

每次通过如下SQL语句生成员工号：

SELECT TO_CHAR(sysdate,'YYYY')||LPAD(emp_id_seq.NEXTVAL,2,0)  emp_id FROM dual;

说明一下：

• TO_CHAR(sysdate,'YYYY')函数用来获取当前年份，可以根据需要使用不同的格式。
• emp_id_seq生成的原始数值只有两位，需要使用LPAD函数将其左侧补0，让其总长度为4位。