Oracle数据库多类型分别自增编号

一、场景

1、现在有类型：A、B、C…

2、针对不同类型分别编号：例如（项目A编号，从1-1-1开始，后续增加就是1-1-2）

3、其他类型项目也一样，插入前都需检索原表最大的项目编号，然后累计编号。

4、适用数据库类型：oracle，主要是oracle11g。

二、设计

1、触发器行级触发，插入前检查最大的项目编号。

2、存在最大编号就截取最后一位+1。

3、不存在就从1开始编号。

4、由于要更新原表，需使用自治事务：pragma autonomous_transaction;

5、批量插入数据时，要用到临时表。

三、代码

--0、创建项目信息表
CREATE TABLE FR_INFO_PROJECT(
    SEQ_NO VARCHAR2(60),
    PROJECT_NAME VARCHAR2(90),
    PK_ID VARCHAR2(60) NOT NULL,
    PRIMARY KEY (PK_ID)
);
COMMENT ON TABLE FR_INFO_PROJECT IS '项目信息表';
COMMENT ON COLUMN FR_INFO_PROJECT.SEQ_NO IS '序号';
COMMENT ON COLUMN FR_INFO_PROJECT.PROJECT_NAME IS '项目名称';
COMMENT ON COLUMN FR_INFO_PROJECT.PK_ID IS '主键';

--1、创建会话级临时表（连接数据库断开以后，该临时表自动清空；换言之，帆软平台重启后，该表自动清空。）该表作用是：触发器编号的时候，把历史编号暂存起来。
Create Global Temporary Table FR_SEQNO_TAB(
	SEQ_NO VARCHAR2(32)
) On Commit Preserve Rows;
--DELETE FROM FR_SEQNO_TAB;
--2、创建触发器，实现不同项目自动自增编号，逻辑是：插入行前检查项目编号SEQ_NO字段，找最大的编号(先从临时表中找，再从原表中找)SEQ_NO，项目编号最后一位+1。
--没找到就“组号-项目号-1，例如：1-2-1”。每执行完一次操作就往临时表中存一次SEQ_NO,目的是为了辅助下一行数据查找最大编号。
CREATE OR REPLACE TRIGGER ProjectSeqNo
  BEFORE INSERT ON FR_INFO_PROJECT FOR each ROW
DECLARE
	MaxSeqNo VARCHAR2(32);
	v_count NUMBER;
	ReSeqNo VARCHAR2(32);
	pragma autonomous_transaction;
BEGIN
  --从临时表和项目计划表中找最大的项目编号,优先取临时表中的最大编号
	SELECT COUNT(1) INTO v_count FROM FR_SEQNO_TAB WHERE SEQ_NO LIKE :new.SEQ_NO||'-%';
	IF (v_count > 0) THEN
		SELECT SEQ_NO INTO MaxSeqNo FROM (
			SELECT SEQ_NO,row_number() OVER(ORDER BY TO_NUMBER(SUBSTR(SEQ_NO, INSTR(SEQ_NO, '-',-1)+1)) DESC) AS rrow 
			FROM FR_SEQNO_TAB WHERE SEQ_NO LIKE :new.SEQ_NO||'-%'
		) WHERE rrow = 1;
	ELSIF (v_count = 0) THEN
        SELECT SEQ_NO INTO MaxSeqNo FROM (
			SELECT SEQ_NO,row_number() OVER(ORDER BY TO_NUMBER(SUBSTR(SEQ_NO, INSTR(SEQ_NO, '-',-1)+1)) DESC) AS rrow 
			FROM FR_INFO_PROJECT WHERE SEQ_NO LIKE :new.SEQ_NO||'-%'
		) WHERE rrow = 1;
	END IF;
	--如果最后一位不是空，就将该编号最后一位+1；如果最后一位是空，就直接拼接‘-1’上去
	IF (MaxSeqNo IS NOT NULL) THEN
		ReSeqNo := SUBSTR(MaxSeqNo, INSTR(MaxSeqNo, '-',-1)+1);
		ReSeqNo := TO_NUMBER(ReSeqNo) + 1;
		:new.SEQ_NO := :new.SEQ_NO||'-'||ReSeqNo;
		INSERT INTO FR_SEQNO_TAB(SEQ_NO) VALUES(:new.SEQ_NO);
		COMMIT;
	ELSIF (MaxSeqNo IS NULL) THEN
		:new.SEQ_NO := :new.SEQ_NO||'-1';
		INSERT INTO FR_SEQNO_TAB(SEQ_NO) VALUES(:new.SEQ_NO);
		COMMIT;
	END IF;
	--查询不到数据的时候会发生异常，异常时从1开始编号
	exception when no_data_found THEN
		:new.SEQ_NO := :new.SEQ_NO||'-1';
		INSERT INTO FR_SEQNO_TAB(SEQ_NO) VALUES(:new.SEQ_NO);
		COMMIT;
	--把刚才处理完的项目编号存一份到临时表中去
END ProjectSeqNo;

--3、测试
--（1）单记录插入测试
INSERT INTO FR_INFO_PROJECT(SEQ_NO,PROJECT_NAME,PK_ID) VALUES('1-2','项目2',TO_CHAR(SYSDATE+1,'yyyyMMddHHmiss'));
INSERT INTO FR_INFO_PROJECT(SEQ_NO,PROJECT_NAME,PK_ID) VALUES('1-1','项目1',TO_CHAR(SYSDATE,'yyyyMMddHHmiss'));
SELECT * FROM FR_INFO_PROJECT ORDER BY SEQ_NO;
--（2）批量插入测试
INSERT INTO FR_INFO_PROJECT(SEQ_NO,PROJECT_NAME,PK_ID) SELECT * FROM(
	SELECT '1-3' as SEQ_NO,'项目3' AS PROJECT_NAME,TO_CHAR(SYSDATE,  'yyyyMMddHHmiss') AS PK_ID FROM dual union ALL
	SELECT '1-3' as SEQ_NO,'项目3' AS PROJECT_NAME,TO_CHAR(SYSDATE+2,'yyyyMMddHHmiss') AS PK_ID FROM dual
);
SELECT * FROM FR_INFO_PROJECT ORDER BY SEQ_NO;