Oracle触发器生成流水号每天从新开始编号

一、场景

每天填报录入单据，每条单据生成一个流水号，流水号格式：'210201025001'，当天累加···'210201025999'；

第二天重新编号：'210201026001'····'210201026999'

二、设计思路

触发器，插入的时候每行生成唯一流水号

三、测试样例

--新建表
CREATE TABLE TEST_TABLE ( 
	NUMBER_NO VARCHAR2(36),
	CURNAME NVARCHAR2(200),
	CURADDRESS NVARCHAR2(200),
	CREATED DATE
);
COMMENT ON TABLE TEST_TABLE IS '测试触发器表' ;
COMMENT ON COLUMN TEST_TABLE.NUMBER_NO   IS '流水号' ;
COMMENT ON COLUMN TEST_TABLE.CURNAME     IS '名称' ;
COMMENT ON COLUMN TEST_TABLE.CURADDRESS  IS '地址' ;
COMMENT ON COLUMN TEST_TABLE.CREATED     IS '创建时间' ;
COMMIT;
 
--创建触发器
CREATE OR REPLACE TRIGGER TRG_TEST_TABLE
  BEFORE INSERT
  ON TEST_TABLE 
  FOR EACH ROW
DECLARE
n NUMBER(11);
BEGIN 
--从表中查询当前插入时间最大的编号并加1作为新的编号
	SELECT NVL(TO_NUMBER(SUBSTR(MAX(NUMBER_NO), -3)), 0)+1 INTO n FROM TEST_TABLE WHERE trunc(CREATED) = trunc(SYSDATE);
  SELECT '210'||TO_CHAR(SYSDATE,'yymmdd')||LPAD(n, 3, 0) INTO :NEW.NUMBER_NO FROM DUAL;
END TRG_TEST_TABLE;
 
--查询数据测试
INSERT into TEST_TABLE(CURNAME,CURADDRESS,CREATED) VALUES('aaa','bbb',SYSDATE);
INSERT into TEST_TABLE(CURNAME,CURADDRESS,CREATED) VALUES('aaa','bbb',SYSDATE);
INSERT into TEST_TABLE(CURNAME,CURADDRESS,CREATED) VALUES('aaa','bbb',SYSDATE);
INSERT into TEST_TABLE(CURNAME,CURADDRESS,CREATED) VALUES('aaa','bbb',SYSDATE);
INSERT into TEST_TABLE(CURNAME,CURADDRESS,CREATED) VALUES('aaa','bbb',SYSDATE);
COMMIT;
SELECT * FROM TEST_TABLE ORDER BY NUMBER_NO;

测试结果：