Oracle序列与同义词 #

序列特点
├── 自动生成唯一数字
├── 独立于表存在
├── 可被多个表共享
├── 支持递增和递减
└── 高效并发访问

序列应用场景
├── 主键生成
│   └── 自动生成唯一ID
├── 订单编号
│   └── 生成连续订单号
├── 流水号
│   └── 生成业务流水号
└── 批次号
    └── 生成批次标识

-- 创建序列基本语法
CREATE SEQUENCE sequence_name
    [INCREMENT BY n]           -- 增量，默认1
    [START WITH n]             -- 起始值，默认1
    [MAXVALUE n | NOMAXVALUE]  -- 最大值
    [MINVALUE n | NOMINVALUE]  -- 最小值
    [CYCLE | NOCYCLE]          -- 是否循环
    [CACHE n | NOCACHE]        -- 缓存数量
    [ORDER | NOORDER];         -- 是否保证顺序

-- 创建简单序列
CREATE SEQUENCE seq_employee_id;

-- 使用默认值：
-- INCREMENT BY 1
-- START WITH 1
-- NOMAXVALUE
-- NOMINVALUE
-- NOCYCLE
-- CACHE 20
-- NOORDER

-- 创建指定参数的序列
CREATE SEQUENCE seq_order_id
    INCREMENT BY 1
    START WITH 1000
    MAXVALUE 999999999
    NOCYCLE
    CACHE 20;

-- 创建递减序列
CREATE SEQUENCE seq_decrement
    INCREMENT BY -1
    START WITH 100
    MINVALUE 1
    MAXVALUE 100
    CYCLE;

-- INCREMENT BY：增量
-- 正数：递增序列
-- 负数：递减序列

CREATE SEQUENCE seq_increment_5
    INCREMENT BY 5
    START WITH 0;  -- 0, 5, 10, 15, ...

-- START WITH：起始值
CREATE SEQUENCE seq_start_1000
    START WITH 1000;  -- 从1000开始

-- MAXVALUE/MINVALUE：最大/最小值
CREATE SEQUENCE seq_bounded
    START WITH 1
    INCREMENT BY 1
    MAXVALUE 1000
    MINVALUE 1;

-- CYCLE/NOCYCLE：循环
CREATE SEQUENCE seq_cycle
    START WITH 1
    MAXVALUE 10
    CYCLE;  -- 1,2,3,...,10,1,2,3,...

-- CACHE/NOCACHE：缓存
-- CACHE：预分配到内存，提高性能
-- NOCACHE：每次访问都更新序列

CREATE SEQUENCE seq_cache
    CACHE 100;  -- 缓存100个值

CREATE SEQUENCE seq_nocache
    NOCACHE;  -- 不缓存

-- ORDER/NOORDER：顺序保证
-- ORDER：保证按请求顺序生成
-- NOORDER：不保证顺序（默认）

CREATE SEQUENCE seq_order
    ORDER;  -- RAC环境需要

-- 主键序列
CREATE SEQUENCE seq_employee_id
    INCREMENT BY 1
    START WITH 1
    NOCACHE
    NOCYCLE;

-- 订单号序列
CREATE SEQUENCE seq_order_no
    INCREMENT BY 1
    START WITH 100000
    CACHE 100
    NOCYCLE;

-- 高性能序列（允许跳号）
CREATE SEQUENCE seq_high_perf
    INCREMENT BY 1
    START WITH 1
    CACHE 1000
    NOORDER;

-- NEXTVAL：获取下一个值
SELECT seq_employee_id.NEXTVAL FROM DUAL;

-- CURRVAL：获取当前值（必须先调用NEXTVAL）
SELECT seq_employee_id.CURRVAL FROM DUAL;

-- 使用示例
-- 第一次调用NEXTVAL
SELECT seq_employee_id.NEXTVAL FROM DUAL;  -- 返回1

-- 调用CURRVAL获取当前值
SELECT seq_employee_id.CURRVAL FROM DUAL;  -- 返回1

-- 再次调用NEXTVAL
SELECT seq_employee_id.NEXTVAL FROM DUAL;  -- 返回2

-- 插入数据时使用序列
INSERT INTO employees (employee_id, first_name, last_name)
VALUES (seq_employee_id.NEXTVAL, 'John', 'Doe');

-- 批量插入
INSERT ALL
    INTO employees (employee_id, first_name) VALUES (seq_employee_id.NEXTVAL, 'John')
    INTO employees (employee_id, first_name) VALUES (seq_employee_id.NEXTVAL, 'Jane')
    INTO employees (employee_id, first_name) VALUES (seq_employee_id.NEXTVAL, 'Bob')
SELECT * FROM DUAL;

-- 使用子查询
INSERT INTO employees (employee_id, first_name, last_name)
SELECT seq_employee_id.NEXTVAL, first_name, last_name
FROM temp_employees;

-- PL/SQL中使用序列
DECLARE
    v_employee_id NUMBER;
BEGIN
    -- 获取下一个值
    v_employee_id := seq_employee_id.NEXTVAL;
    
    -- 插入数据
    INSERT INTO employees (employee_id, first_name, last_name)
    VALUES (v_employee_id, 'John', 'Doe');
    
    -- 获取当前值
    DBMS_OUTPUT.PUT_LINE('Current ID: ' || seq_employee_id.CURRVAL);
    
    COMMIT;
END;
/

-- Oracle 12c引入IDENTITY列
-- 方式1：GENERATED ALWAYS
CREATE TABLE employees (
    employee_id NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    first_name VARCHAR2(50),
    last_name VARCHAR2(50)
);

-- 方式2：GENERATED BY DEFAULT
CREATE TABLE employees (
    employee_id NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    first_name VARCHAR2(50),
    last_name VARCHAR2(50)
);

-- 方式3：GENERATED BY DEFAULT ON NULL
CREATE TABLE employees (
    employee_id NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY PRIMARY KEY,
    first_name VARCHAR2(50),
    last_name VARCHAR2(50)
);

-- IDENTITY列选项
CREATE TABLE employees (
    employee_id NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY 
        (START WITH 1000 INCREMENT BY 1 CACHE 100) PRIMARY KEY,
    first_name VARCHAR2(50),
    last_name VARCHAR2(50)
);

-- 查看IDENTITY列信息
SELECT * FROM USER_TAB_IDENTITY_COLS WHERE TABLE_NAME = 'EMPLOYEES';

-- 修改序列
ALTER SEQUENCE seq_employee_id
    INCREMENT BY 1
    MAXVALUE 999999999
    NOCYCLE
    CACHE 20;

-- 注意：不能修改START WITH值
-- 如果需要重新开始，需要删除重建

-- 重置序列（删除重建）
DROP SEQUENCE seq_employee_id;
CREATE SEQUENCE seq_employee_id
    START WITH 1
    INCREMENT BY 1;

-- 重置序列（通过调整增量）
-- 假设当前值为100，想重置为1
ALTER SEQUENCE seq_employee_id INCREMENT BY -99;
SELECT seq_employee_id.NEXTVAL FROM DUAL;  -- 返回1
ALTER SEQUENCE seq_employee_id INCREMENT BY 1;

-- 删除序列
DROP SEQUENCE seq_employee_id;

-- 检查序列是否存在
SELECT SEQUENCE_NAME FROM USER_SEQUENCES WHERE SEQUENCE_NAME = 'SEQ_EMPLOYEE_ID';

-- 查看序列信息
SELECT 
    SEQUENCE_NAME,
    MIN_VALUE,
    MAX_VALUE,
    INCREMENT_BY,
    CYCLE_FLAG,
    CACHE_SIZE,
    LAST_NUMBER
FROM USER_SEQUENCES;

-- 查看特定序列
SELECT * FROM USER_SEQUENCES WHERE SEQUENCE_NAME = 'SEQ_EMPLOYEE_ID';

-- 查看所有序列
SELECT SEQUENCE_NAME, LAST_NUMBER FROM USER_SEQUENCES;

同义词作用
├── 简化对象访问
│   └── 缩短对象名称
├── 隐藏对象位置
│   └── 屏蔽模式名
├── 提供透明性
│   └── 对象重命名不影响应用
└── 跨模式访问
    └── 简化权限管理

同义词类型
├── 私有同义词
│   └── 只对创建者可见
└── 公共同义词
    └── 对所有用户可见

-- 创建私有同义词
CREATE SYNONYM emp FOR hr.employees;

-- 使用同义词
SELECT * FROM emp;

-- 为视图创建同义词
CREATE SYNONYM emp_view FOR hr.v_employee_details;

-- 为序列创建同义词
CREATE SYNONYM emp_seq FOR hr.seq_employee_id;

-- 为存储过程创建同义词
CREATE SYNONYM get_salary FOR hr.get_employee_salary;

-- 创建公共同义词
CREATE PUBLIC SYNONYM employees FOR hr.employees;

-- 所有用户都可以访问
SELECT * FROM employees;

-- 创建公共序列同义词
CREATE PUBLIC SYNONYM seq_emp FOR hr.seq_employee_id;

-- 使用公共序列同义词
SELECT seq_emp.NEXTVAL FROM DUAL;

-- 创建私有同义词权限
GRANT CREATE SYNONYM TO user_name;

-- 创建公共同义词权限
GRANT CREATE PUBLIC SYNONYM TO user_name;

-- 删除公共同义词权限
GRANT DROP PUBLIC SYNONYM TO user_name;

-- 使用同义词还需要基础对象的访问权限
GRANT SELECT ON hr.employees TO user_name;

-- 删除私有同义词
DROP SYNONYM emp;

-- 删除公共同义词
DROP PUBLIC SYNONYM employees;

-- 删除不存在的同义词（避免错误）
DROP SYNONYM IF EXISTS emp;

-- 查看私有同义词
SELECT 
    SYNONYM_NAME,
    TABLE_OWNER,
    TABLE_NAME
FROM USER_SYNONYMS;

-- 查看所有可访问的同义词
SELECT 
    OWNER,
    SYNONYM_NAME,
    TABLE_OWNER,
    TABLE_NAME
FROM ALL_SYNONYMS;

-- 查看公共同义词
SELECT 
    SYNONYM_NAME,
    TABLE_OWNER,
    TABLE_NAME
FROM DBA_SYNONYMS
WHERE OWNER = 'PUBLIC';

-- 查看特定同义词
SELECT * FROM USER_SYNONYMS WHERE SYNONYM_NAME = 'EMP';

-- 查看同义词依赖的对象
SELECT 
    NAME,
    TYPE,
    REFERENCED_NAME,
    REFERENCED_TYPE
FROM USER_DEPENDENCIES
WHERE NAME = 'EMP' AND TYPE = 'SYNONYM';

-- 查看依赖同义词的对象
SELECT 
    NAME,
    TYPE
FROM USER_DEPENDENCIES
WHERE REFERENCED_NAME = 'EMP' AND REFERENCED_TYPE = 'SYNONYM';

-- 原始访问方式
SELECT * FROM hr.employees;
SELECT * FROM hr.departments;
SELECT * FROM hr.jobs;

-- 创建同义词简化
CREATE SYNONYM emp FOR hr.employees;
CREATE SYNONYM dept FOR hr.departments;
CREATE SYNONYM jobs FOR hr.jobs;

-- 使用同义词
SELECT * FROM emp;
SELECT * FROM dept;
SELECT * FROM jobs;

-- 场景：应用用户需要访问HR模式下的表

-- 方式1：直接授权
GRANT SELECT ON hr.employees TO app_user;
-- 应用用户需要使用 hr.employees

-- 方式2：使用公共同义词
CREATE PUBLIC SYNONYM employees FOR hr.employees;
GRANT SELECT ON hr.employees TO PUBLIC;
-- 应用用户可以直接使用 employees

-- 场景：表从一个模式迁移到另一个模式

-- 原始状态
-- 应用使用 hr.employees

-- 迁移后
-- 表移动到 hr_new 模式

-- 方式1：修改所有应用代码（不推荐）

-- 方式2：使用同义词（推荐）
-- 删除旧同义词
DROP PUBLIC SYNONYM employees;

-- 创建新同义词
CREATE PUBLIC SYNONYM employees FOR hr_new.employees;

-- 应用无需修改

-- 场景：开发和测试环境使用不同的表

-- 开发环境
CREATE SYNONYM orders FOR dev.orders;

-- 测试环境
CREATE SYNONYM orders FOR test.orders;

-- 生产环境
CREATE SYNONYM orders FOR prod.orders;

-- 应用代码统一使用 orders

-- 1. 为序列命名
-- 推荐：seq_表名 或 seq_用途
CREATE SEQUENCE seq_employee_id;
CREATE SEQUENCE seq_order_no;

-- 2. 合理设置缓存
-- 高并发场景使用缓存
CREATE SEQUENCE seq_high_concurrency
    CACHE 1000;

-- 需要连续序列号时不使用缓存
CREATE SEQUENCE seq_invoice_no
    NOCACHE;

-- 3. 主键使用IDENTITY列（12c+）
CREATE TABLE employees (
    employee_id NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY PRIMARY KEY,
    first_name VARCHAR2(50)
);

-- 4. 监控序列使用
SELECT 
    SEQUENCE_NAME,
    LAST_NUMBER,
    CACHE_SIZE
FROM USER_SEQUENCES;

-- 1. 命名规范
-- 同义词名应简洁明了
CREATE SYNONYM emp FOR hr.employees;  -- 好
CREATE SYNONYM e FOR hr.employees;    -- 不推荐

-- 2. 公共同义词谨慎使用
-- 避免名称冲突
-- 避免过度授权

-- 3. 文档化同义词
-- 记录同义词与实际对象的映射关系
COMMENT ON SYNONYM emp IS 'HR模式员工表同义词';

-- 4. 定期审查同义词
-- 检查无效同义词
SELECT 
    SYNONYM_NAME,
    TABLE_OWNER,
    TABLE_NAME
FROM USER_SYNONYMS s
WHERE NOT EXISTS (
    SELECT 1 FROM ALL_TABLES t 
    WHERE t.OWNER = s.TABLE_OWNER 
    AND t.TABLE_NAME = s.TABLE_NAME
);

-- 问题1：序列跳号
-- 原因：CACHE导致，回滚，实例崩溃
-- 解决：接受跳号或使用NOCACHE

-- 问题2：序列耗尽
-- 原因：达到MAXVALUE
-- 解决：扩大MAXVALUE或使用CYCLE
ALTER SEQUENCE seq_employee_id MAXVALUE 999999999999;

-- 问题3：重置序列
-- 解决：调整增量或删除重建
ALTER SEQUENCE seq_employee_id INCREMENT BY -99;
SELECT seq_employee_id.NEXTVAL FROM DUAL;
ALTER SEQUENCE seq_employee_id INCREMENT BY 1;

-- 问题1：同义词无效
-- 原因：基础对象被删除或重命名
-- 解决：重建同义词
DROP SYNONYM emp;
CREATE SYNONYM emp FOR hr.employees;

-- 问题2：同义词权限不足
-- 原因：缺少基础对象的访问权限
-- 解决：授予基础对象权限
GRANT SELECT ON hr.employees TO user_name;

-- 问题3：公共同义词名称冲突
-- 原因：多个模式有同名对象
-- 解决：使用私有同义词或重命名