--创建表
create table EMP (EMPNO number , ENAME varchar2(32) ,STR varchar2(32) );
CREATE OR REPLACE TRIGGER EMP_TRIGGER
BEFORE INSERT ON EMP
FOR EACH ROW
DECLARE
--PS+年月日+五位流水号
PS_NUMBER VARCHAR2(50); --评审单号
YEAR_MONTH_DAY VARCHAR2(32);
BEGIN
--查询最大评审单号 +1 ,得下一个评审单号
SELECT SUBSTR(MAX(E.STR), 3, LENGTH(MAX(E.STR)) - 2) + 1
INTO PS_NUMBER
FROM EMP E
WHERE E.STR LIKE '%' || 'PS' || TO_CHAR(SYSDATE, 'YYYYMMDD') || '%'
ORDER BY E.STR DESC;
--如果查询的评审单号为空,则从00001开始
IF PS_NUMBER IS NULL THEN
SELECT TO_CHAR(SYSDATE, 'YYYYMMDD') INTO YEAR_MONTH_DAY FROM DUAL;
PS_NUMBER := YEAR_MONTH_DAY || '00001';
END IF;
--如果插入的数据评审单号为空时,则取新生成的单号
IF :NEW.STR IS NULL THEN
:NEW.STR := 'PS' || PS_NUMBER;
END IF;
END;
--插入数据测试
insert into emp (empno, ename) values ('10', 'WANG莉');
insert into emp (empno, ename) values ('11', 'QQ莉2');
insert into emp (empno, ename) values ('12', 'WW莉3');
commit;
--查看结果,单号已生成插入