Oracle 序列表及获取序列详解【源码】

1 概述

1. 目的："方便使用时复制、粘贴"
   (1) Oracle 获取 '序列（sequence）' 的底层代码基本上一样
   (2) 本文在此记录，方便以后复制、粘贴

2. 思路：-- 步骤
   (1) 创建序列
   (2) 创建序列配置信息表 -- 为了获取 "序列名"
   (3) 获取序列，通过方法或过程

3. 获取序列的核心
   select [v_sequence_name].nextval from dual;

2 效果演示

3 源码

说明：
(1) 本文所用用户共计两个 
    odsdata：存放数据的用户
    odscde : 执行具体操作的用户  -- 模拟实际开发场景 

(2) 自测时，若不想这么麻烦，去掉 '属主 owner' 即可

3.1 创建序列

• 扩展：Oracle 序列详解（sequence）

CREATE SEQUENCE odsdata.sequence_test
MINVALUE 1        -- 最小值
MAXVALUE 99999999 -- 最大值. 8 位 (请根据实际需求扩大)
START WITH 1      -- 起始值
INCREMENT BY 1    -- 增量
CACHE 20          -- 缓存
CYCLE             -- 循环
NOORDER;          -- 不排序

GRANT SELECT ON odsdata.sequence_test TO odscde;

查询验证：

SELECT *
  FROM all_sequences t
 WHERE t.sequence_owner = 'ODSDATA'
   AND t.sequence_name = 'SEQUENCE_TEST';

查询结果：

3.2 序列配置表

CREATE TABLE odsdata.project_sequence_config
(
  owner         VARCHAR2(30),
  table_name    VARCHAR2(30),
  column_name   VARCHAR2(30),
  sequence_name VARCHAR2(30) NOT NULL,
  is_valid      VARCHAR2(2) NOT NULL,
  create_user   VARCHAR2(50) NOT NULL,
  create_date   DATE NOT NULL,
  update_user   VARCHAR2(50) NOT NULL,
  update_date   DATE NOT NULL,
  CONSTRAINT pk_psc_owner_table_column PRIMARY KEY(owner, table_name, column_name)
);

COMMENT ON TABLE odsdata.project_sequence_config IS '序列配置信息表';
COMMENT ON COLUMN odsdata.project_sequence_config.owner IS '属主';
COMMENT ON COLUMN odsdata.project_sequence_config.table_name IS '表名';
COMMENT ON COLUMN odsdata.project_sequence_config.column_name IS '列名';
COMMENT ON COLUMN odsdata.project_sequence_config.sequence_name IS '序列名';
COMMENT ON COLUMN odsdata.project_sequence_config.is_valid IS '是否有效（Y:有效，N:无效）';
COMMENT ON COLUMN odsdata.project_sequence_config.create_user IS '创建人';
COMMENT ON COLUMN odsdata.project_sequence_config.create_date IS '创建时间';
COMMENT ON COLUMN odsdata.project_sequence_config.update_user IS '更新人';
COMMENT ON COLUMN odsdata.project_sequence_config.update_date IS '更新时间';

GRANT SELECT, INSERT, UPDATE ON odsdata.project_sequence_config TO odscde;

演示准备：学生信息表 stu_info 的 序列 sno

CREATE TABLE odsdata.stu_info (
  sno   NUMBER(10),
  sname VARCHAR2(50)
);

GRANT SELECT, INSERT, UPDATE ON odsdata.stu_info TO odscde;

配置序列信息：

INSERT INTO odsdata.project_sequence_config
  (owner, table_name, column_name, sequence_name, is_valid, create_user, create_date, update_user, update_date)
VALUES
  ('ODSDATA', 'STU_INFO', 'SNO', 'SEQUENCE_TEST', 'Y', 'YoYo', SYSDATE, 'YoYo', SYSDATE);
COMMIT;

3.3 获取序列包

package：

CREATE OR REPLACE PACKAGE odscde.pkg_project_sequence IS

  --*************************************************************
  --功能说明: 生成 sequence 的底层过程，供业务层应用程序调用
  --参数说明: i_owner       属主
  --         i_table_name  表名
  --         i_column_name 列名
  --修改记录: create by YoYo 2020-03-06
  --*************************************************************
  PROCEDURE fetch_sequence(i_owner       IN VARCHAR2,
                           i_table_name  IN VARCHAR2,
                           i_column_name IN VARCHAR2,
                           o_sequence_no OUT VARCHAR2,
                           o_flag        OUT VARCHAR2,
                           o_message     OUT VARCHAR2);

  --*************************************************************
  --功能说明: 方便调用 过程 fetch_sequence
  --*************************************************************
  FUNCTION get_sequence(i_owner       IN VARCHAR2,
                        i_table_name  IN VARCHAR2,
                        i_column_name IN VARCHAR2) RETURN VARCHAR2;
END pkg_project_sequence;

package body：

CREATE OR REPLACE PACKAGE BODY odscde.pkg_project_sequence IS
  --*************************************************************
  --功能说明: 生成 sequence 的底层过程，供业务层应用程序调用
  --参数说明: i_owner       属主
  --         i_table_name  表名
  --         i_column_name 列名
  --修改记录: create by YoYo 2020-03-06
  --*************************************************************
  PROCEDURE fetch_sequence(i_owner       IN VARCHAR2,
                           i_table_name  IN VARCHAR2,
                           i_column_name IN VARCHAR2,
                           o_sequence_no OUT VARCHAR2,
                           o_flag        OUT VARCHAR2,
                           o_message     OUT VARCHAR2) IS
    v_sequence_count  NUMBER(1);
    v_sequence_sql    VARCHAR2(500);
    v_sequence_name   odsdata.project_sequence_config.sequence_name%TYPE;
    cur_sequence_info SYS_REFCURSOR;
  BEGIN
    -- 1.验证序列信息表是否已配置，全部转为大写，避免 "大小写引起的漏洞"
    SELECT COUNT(1)
      INTO v_sequence_count
      FROM odsdata.project_sequence_config psc -- owner, table_name, column_name
     WHERE upper(psc.owner) = upper(i_owner)
       AND upper(psc.table_name) = upper(i_table_name)
       AND upper(psc.column_name) = upper(i_column_name)
       AND psc.is_valid = 'Y';
  
    IF v_sequence_count = 0 THEN
      o_flag    := 'N';
      o_message := '请先配置 "序列信息表" ！';
      RETURN;
    END IF;
  
    -- 2.获取序列
    v_sequence_sql := 'SELECT psc.sequence_name
                         FROM odsdata.project_sequence_config psc
                        WHERE UPPER(psc.owner) = :b1
                          AND UPPER(psc.table_name) = :b2
                          AND UPPER(psc.column_name) = :b3
                          AND psc.is_valid = ''Y''';
  
    OPEN cur_sequence_info FOR v_sequence_sql
      USING upper(i_owner), upper(i_table_name), upper(i_column_name);
  
    FETCH cur_sequence_info
      INTO v_sequence_name;
  
    -- 初始序列号 8 位 （创建序列时设置的，可更改）
    EXECUTE IMMEDIATE 'SELECT ' || i_owner || '.' || v_sequence_name ||
                      '.nextval FROM DUAL'
      INTO o_sequence_no;
  
    CLOSE cur_sequence_info;
  
    -- 最终序列号 10 位 = "年份" + 初始序列号 （循环使用）
    o_sequence_no := to_char(SYSDATE, 'YY') || o_sequence_no;
  EXCEPTION
    WHEN OTHERS THEN
      o_flag    := 'N';
      o_message := SQLERRM || ' i_owner = ' || i_owner || ', i_table_name = ' ||
                   i_table_name || ', i_column_name = ' || i_column_name;
    
      IF cur_sequence_info%ISOPEN THEN
        CLOSE cur_sequence_info;
      END IF;
  END fetch_sequence;

  --*************************************************************
  --功能说明: 方便调用 过程 fetch_sequence
  --*************************************************************
  FUNCTION get_sequence(i_owner       IN VARCHAR2,
                        i_table_name  IN VARCHAR2,
                        i_column_name IN VARCHAR2) RETURN VARCHAR2 IS
    o_sequence_no odsdata.project_sequence_config.sequence_name%TYPE;
    o_flag        VARCHAR2(10);
    o_message     VARCHAR2(100);
  BEGIN
    fetch_sequence(i_owner       => i_owner,
                   i_table_name  => i_table_name,
                   i_column_name => i_column_name,
                   o_sequence_no => o_sequence_no,
                   o_flag        => o_flag,
                   o_message     => o_message);
  
    RETURN o_sequence_no;
  END get_sequence;
END pkg_project_sequence;