一、场景
每天填报录入单据,每条单据生成一个流水号,流水号格式:'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;
测试结果: