很早就想自己写写Oracle的函数和触发器，最近一个来自课本的小案例给了我这个机会。现在把我做的东西记录下来，作为一个备忘或者入门的朋友们的参考。
案例介绍：
招投标管理系统（数据库设计）。
数据表有以下两张：
招标书（招标书编号、项目名称、招标书内容、截止日期、状态）。
投标书（投标书编号、招标书编号、投标企业、投标书内容、投标日期、报价、状态）。
“招标书编号”为字符型，编号规则为 ZBYYYYMMDDNNN， ZB是招标的汉语拼音首字母，YYYYMMDD是当前日期，NNN是三位流水号。
“投标书编号”为字符型，编号规则为TB[11位招标书编号]NNN。
经过分析，我们可以得知两张表的关系。我们先创建数据结构，比如：
1 CREATE TABLE TENDER 2 ( 3 TENDER_ID VARCHAR2(50) PRIMARY KEY, 4 PROJECT_NAME VARCHAR2(50) NOT NULL UNIQUE, 5 CONTENT BLOB, 6 END_DATE DATE NOT NULL, 7 STATUS INTEGER NOT NULL 8 ); 9 CREATE TABLE BID 10 ( 11 BID_ID VARCHAR2(50) PRIMARY KEY, 12 TENDER_ID VARCHAR2(50) NOT NULL, 13 COMPANY VARCHAR2(50) NOT NULL, 14 CONTENT BLOB, 15 BID_DATE DATE NOT NULL, 16 PRICE INTEGER NOT NULL, 17 STATUS INTEGER NOT NULL 18 ); 19 ALTER TABLE BID ADD CONSTRAINT FK_BID_TENDER_ID FOREIGN KEY(TENDER_ID) REFERENCES TENDER(TENDER_ID);
然后是生成招标的函数：
1 CREATE OR REPLACE 2 FUNCTION "createZBNo" RETURN VARCHAR2 3 AS 4 hasCount NUMBER(11,0); 5 lastID VARCHAR2(50); 6 lastTime VARCHAR2(12); 7 lastNo NUMBER(3,0); 8 curNo NUMBER(3,0); 9 BEGIN 10 -- 查询表中是否有记录 11 SELECT "COUNT"(TENDER_ID) INTO hasCount FROM TENDER; 12 IF hasCount > 0 THEN 13 -- 查询必要信息 14 SELECT TENDER_ID INTO lastID FROM TENDER WHERE ROWNUM = 1 ORDER BY to_number(to_char(scn_to_timestamp(ORA_ROWSCN),'yyyyMMddhh24mmss'),'99999999999999') DESC; 15 SELECT "SUBSTR"(lastID, 3, 8) INTO lastTime FROM dual; 16 -- 分析上一次发布招标信息是否是今日 17 IF ("TO_CHAR"(SYSDATE,'YYYYMMDD') = lastTime) THEN 18 SELECT "TO_NUMBER"("SUBSTR"(lastID, 11, 13), '999') INTO lastNo FROM dual; 19 -- 如果是今日且流水号允许新增招标信息 20 IF lastNo < 999 THEN 21 SELECT lastNo + 1 INTO curNo FROM dual; 22 RETURN 'ZB'||lastTime||"LPAD"("TO_CHAR"(curNo), 3, '0'); 23 END IF; 24 -- 流水号超出 25 RETURN 'NoOutOfBounds!Check it!'; 26 END IF; 27 -- 不是今日发布的招标信息，今日是第一次 28 RETURN 'ZB'||"TO_CHAR"(SYSDATE,'YYYYMMDD')||'001'; 29 END IF; 30 -- 整个表中的第一条数据 31 RETURN 'ZB'||"TO_CHAR"(SYSDATE,'YYYYMMDD')||'001'; 32 END;
然后是投标书的编号生成函数：
1 CREATE OR REPLACE 2 FUNCTION "createTBNo" (ZBNo IN VARCHAR2) 3 RETURN VARCHAR2 4 AS 5 hasCount NUMBER(11,0); 6 lastID VARCHAR2(50); 7 lastNo NUMBER(3,0); 8 curNo NUMBER(3,0); 9 BEGIN 10 -- 查看是否已经有了对于该想招标的投标书 11 SELECT "COUNT"(BID_ID) INTO hasCount FROM BID WHERE BID_ID LIKE 'TB'||ZBNo||'___' AND ROWNUM = 1 ORDER BY to_number(to_char(scn_to_timestamp(ORA_ROWSCN),'yyyyMMddhh24mmss'),'99999999999999') DESC; 12 IF hasCount > 0 THEN 13 -- 有了 14 SELECT BID_ID INTO lastID FROM BID WHERE BID_ID LIKE 'TB'||ZBNo||'___' AND ROWNUM = 1 ORDER BY to_number(to_char(scn_to_timestamp(ORA_ROWSCN),'yyyyMMddhh24mmss'),'99999999999999') DESC; 15 SELECT "TO_NUMBER"("SUBSTR"(lastID, 16,18),'999') INTO lastNo FROM dual; 16 -- 流水号没超出 17 IF lastNo < 999 THEN 18 SELECT lastNo + 1 INTO curNo FROM dual; 19 RETURN 'TB'||ZBNo||"LPAD"("TO_CHAR"(curNo),3,'0'); 20 END IF; 21 RETURN 'NoOutOfBounds!Check it!'; 22 END IF; 23 -- 没有投标书对该招标书 24 RETURN 'TB'||ZBNo||'001'; 25 END;
然后在两个表中注册触发器，当新增数据的时候动态生成编号！
招标书触发器，用于动态生成招标书编号：
1 CREATE OR REPLACE 2 TRIGGER newTender 3 BEFORE INSERT 4 ON TENDER 5 FOR EACH ROW 6 BEGIN 7 -- 如果生成编号失败 8 IF (LENGTH("createZBNo") <> 13) THEN 9 -- 此处根据我的提示信息报错可以直接如下操作 10 -- :NEW.TENDER_ID := NULL; 11 RAISE_APPLICATION_ERROR(-20222,"createZBNo"); 12 END IF; 13 -- 如果生成编号成功，将编号注入查询语句中 14 :NEW.tender_id :="createZBNo"; 15 END;
然后是投标书的触发器：
1 CREATE OR REPLACE 2 TRIGGER newBid 3 BEFORE INSERT 4 ON BID 5 FOR EACH ROW 6 BEGIN 7 IF (LENGTH("createTBNo"(:NEW.TENDER_ID)) <> 18) THEN 8 RAISE_APPLICATION_ERROR(-20222,"createTBNo"(:NEW.TENDER_ID)); 9 END IF; 10 :NEW.BID_ID :="createTBNo"(:NEW.TENDER_ID); 11 END;
然后插入数据测试吧：
以上只是个人的一些观点，如果您不认同或者能给予指正和帮助，请不吝赐教。