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 创建序列
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;