Oracle序列介绍

Oracle序列介绍

1. Oracle序列演进

Oracle序列（Sequence）是数据库生成唯一数值序列的对象，主要用于解决主键自增、唯一标识生成等场景。其核心原理基于内存预分配机制，通过CACHE参数提升性能，同时保障事务独立性。

• Oracle 11g及之前：
  依赖手动创建序列+触发器实现自增主键。
• Oracle 12c：
  引入身份列（Identity Column），简化自增列管理（详见第3节）。
• Oracle 19c：
  支持SCALABLE序列，优化分布式数据库的序列分配效率。

2. Oracle序列使用

2.1 创建序列

-- 实例1
CREATE SEQUENCE seq_emp  
   START WITH 1000       -- 起始值  
   INCREMENT BY 1        -- 步长  
   MAXVALUE 9999         -- 最大值  
   MINVALUE 1000         -- 最小值  
   CACHE 20              -- 缓存数  
   CYCLE;                -- 循环生成
 
-- 实例2
CREATE SEQUENCE SEQ1
    START WITH 1
    INCREMENT BY 1
    NOMAXVALUE
    NOCYCLE
    CACHE 20;

参数说明：

• start with 1 指定序列初始值为1
• increment by 1 指定序列值每次递增1，这个值可以为负数，代表递减序列，increment by 1也是序列创建的默认设置，可以省略。
• nomaxvalue 指定序列值没有上限（实际最大值为10^28-1，），默认设置。你也可以用maxvalue N来指定序列的最大值。
• nocycle 指定序列到达最大值时不循环，默认设置。如果指定cycle，那么序列到达最大值后，会从头开始循环。
• cache 20 为了提高访问速度，预先分配好并缓存在内存中的序列值个数，默认设置。如果是较繁忙的系统，可以适当的提高该值。

2.2 调用序列

• 获取下一个值：

  INSERT INTO emp (id, name) VALUES (seq_emp.NEXTVAL, 'Alice');  
  

• 查看当前值：

  SELECT seq_emp.CURRVAL FROM DUAL;  -- 必须先调用NEXTVAL  
  

2.3 修改与删除

• 修改序列：

  ALTER SEQUENCE seq_emp INCREMENT BY 2;  -- 调整步长  
  

  限制：无法修改起始值（需重建序列）。
• 删除序列：

  DROP SEQUENCE seq_emp;  
  

3. Oracle身份列（自增列）

自Oracle 12c起，身份列简化了自增字段的管理，底层仍依赖序列实现。

3.1 创建身份列

身份列定义的三种方式（其实就2种）：

• generated always：总是由系统自动生成身份列的值，如果显式给身份列赋值，则会引发异常。
• generated by default：用户可以给身份列赋值，如果未赋值，则由系统生成身份列的值。
• generated by default on null：为第二项定义的扩展，如果指定了on null选项，则为身份列列赋值为null时会由系统生成值。

CREATE TABLE employees (  
   id NUMBER GENERATED ALWAYS AS IDENTITY, 
   name VARCHAR2(50)  
);

CREATE TABLE employees (  
   id NUMBER GENERATED BY DEFAULT AS IDENTITY, 
   name VARCHAR2(50)  
); 

CREATE TABLE employees (  
   id NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY, 
   name VARCHAR2(50)  
); 

当我们创建身份列的时候，Oracle会隐式的创建一个序列生成器（Sequence Generator SG）。既然底层也是序列，那么这个序列生成器的属性在创建时也是可以显式指定的。

create table tab3 (
id number generated always as identity (
start with 10
increment by 2
maxvalue 200
nocycle),
name varchar2(32)
);

3.2 身份列 vs 传统序列

4. Oracle序列常见使用与问题

1. 序列开始值修改

-- 获取表中序列最大值
select max(id) from your_table
-- 查看当前值（需先调用NEXTVAL）
SELECT your_seq.CURRVAL FROM dual;
 
-- 调整到目标值（如改为1000）
ALTER SEQUENCE your_seq INCREMENT BY 999; -- 1000 - 当前值 
SELECT your_seq.NEXTVAL FROM dual;        -- 执行后变为1000 
ALTER SEQUENCE your_seq INCREMENT BY 1;   -- 恢复步长 

2. 修改列为自增列

-- 确认ID字段当前属性 
SELECT column_name, data_type, identity_column
FROM all_tab_columns 
WHERE table_name = 'SSO_HOSPITAL_DUTY_INFORMATION' 
AND owner = 'SSO2';

-- 方案A：严格自增（禁止手动插入值）
ALTER TABLE SSO2.SSO_HOSPITAL_DUTY_INFORMATION 
ADD (
    NEW_ID NUMBER GENERATED ALWAYS AS IDENTITY (START WITH 1 INCREMENT BY 1)
);
 
-- 方案B：允许手动覆盖（业务灵活性更高）
ALTER TABLE SSO2.SSO_HOSPITAL_DUTY_INFORMATION 
ADD (
    NEW_ID NUMBER GENERATED BY DEFAULT AS IDENTITY (START WITH 1)
);


-- 迁移原ID数据（如需保留历史值）
UPDATE SSO2.SSO_HOSPITAL_DUTY_INFORMATION SET NEW_ID = ID;
 
-- 删除原列并重命名（需停业务）
ALTER TABLE SSO2.SSO_HOSPITAL_DUTY_INFORMATION DROP COLUMN ID;
ALTER TABLE SSO2.SSO_HOSPITAL_DUTY_INFORMATION RENAME COLUMN NEW_ID TO ID;

-- 重新添加主键（假设原约束名为PK_HOSP_DUTY）
ALTER TABLE SSO2.SSO_HOSPITAL_DUTY_INFORMATION 
ADD CONSTRAINT PK_HOSP_DUTY PRIMARY KEY (ID);

3. 现有身份列修改方式

-- 修改为强制模式
ALTER TABLE SSO2.SSO_HOSPITAL_DUTY_INFORMATION MODIFY (ID GENERATED ALWAYS AS IDENTITY);
-- 修改为手动模式
ALTER TABLE SSO2.SSO_HOSPITAL_DUTY_INFORMATION MODIFY (ID GENERATED BY DEFAULT AS IDENTITY);