Oracle学习记录之使用自定义函数和触发器实现主键动态生成_Oracle

很早就想自己写写Oracle的函数和触发器，最近一个来自课本的小案例给了我这个机会。现在把我做的东西记录下来，作为一个备忘或者入门的朋友们的参考。

　　案例介绍：

　　　　招投标管理系统（数据库设计）。
　　　　数据表有以下两张：
　　　　　　招标书（招标书编号、项目名称、招标书内容、截止日期、状态）。
　　　　　　投标书（投标书编号、招标书编号、投标企业、投标书内容、投标日期、报价、状态）。
　　　　　　“招标书编号”为字符型，编号规则为 ZBYYYYMMDDNNN， ZB是招标的汉语拼音首字母，YYYYMMDD是当前日期，NNN是三位流水号。
　　　　　　“投标书编号”为字符型，编号规则为TB[11位招标书编号]NNN。

　　经过分析，我们可以得知两张表的关系。我们先创建数据结构，比如：

				?

									CREATE TABLE TENDER

									(

									 TENDER_ID  VARCHAR2(50) PRIMARY KEY,

									 PROJECT_NAME VARCHAR2(50) NOT NULL UNIQUE,

									 CONTENT   BLOB,

									 END_DATE   DATE NOT NULL,

									 STATUS    INTEGER NOT NULL

									);

									CREATE TABLE BID

									(

									 BID_ID  VARCHAR2(50) PRIMARY KEY,

									 TENDER_ID VARCHAR2(50) NOT NULL,

									 COMPANY  VARCHAR2(50) NOT NULL,

									 CONTENT  BLOB,

									 BID_DATE DATE NOT NULL,

									 PRICE   INTEGER NOT NULL,

									 STATUS  INTEGER NOT NULL

									);

									ALTER TABLE BID ADD CONSTRAINT FK_BID_TENDER_ID FOREIGN KEY(TENDER_ID) REFERENCES TENDER(TENDER_ID);

然后是生成招标的函数：

				?

									CREATE OR REPLACE

									FUNCTION "createZBNo" RETURN VARCHAR2

									AS

									hasCount NUMBER(11,0);

									lastID VARCHAR2(50);

									lastTime VARCHAR2(12);

									lastNo NUMBER(3,0);

									curNo NUMBER(3,0);

									BEGIN

									  -- 查询表中是否有记录

									  SELECT "COUNT"(TENDER_ID) INTO hasCount FROM TENDER;

									  IF hasCount > 0 THEN

									    -- 查询必要信息

									    SELECT TENDER_ID INTO lastID FROM TENDER WHERE ROWNUM = 1 ORDER BY to_number(to_char(scn_to_timestamp(ORA_ROWSCN),'yyyyMMddhh24mmss'),'99999999999999') DESC;

									    SELECT "SUBSTR"(lastID, 3, 8) INTO lastTime FROM dual;

									    -- 分析上一次发布招标信息是否是今日

									    IF ("TO_CHAR"(SYSDATE,'YYYYMMDD') = lastTime) THEN

									      SELECT "TO_NUMBER"("SUBSTR"(lastID, 11, 13), '999') INTO lastNo FROM dual;

									      -- 如果是今日且流水号允许新增招标信息

									      IF lastNo < 999 THEN

									        SELECT lastNo + 1 INTO curNo FROM dual;

									        RETURN 'ZB'||lastTime||"LPAD"("TO_CHAR"(curNo), 3, '0');

									      END IF;

									        -- 流水号超出

									        RETURN 'NoOutOfBounds!Check it!';

									    END IF;

									      -- 不是今日发布的招标信息，今日是第一次

									      RETURN 'ZB'||"TO_CHAR"(SYSDATE,'YYYYMMDD')||'001';

									  END IF;

									      -- 整个表中的第一条数据

									    RETURN 'ZB'||"TO_CHAR"(SYSDATE,'YYYYMMDD')||'001';

									END;

然后是投标书的编号生成函数：

				?

									CREATE OR REPLACE

									FUNCTION "createTBNo" (ZBNo IN VARCHAR2)

									RETURN VARCHAR2

									AS

									hasCount NUMBER(11,0);

									lastID VARCHAR2(50);

									lastNo NUMBER(3,0);

									curNo NUMBER(3,0);

									BEGIN

									  -- 查看是否已经有了对于该想招标的投标书

									  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;

									  IF hasCount > 0 THEN

									    -- 有了

									    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;

									      SELECT "TO_NUMBER"("SUBSTR"(lastID, 16,18),'999') INTO lastNo FROM dual;

									      -- 流水号没超出

									      IF lastNo < 999 THEN

									        SELECT lastNo + 1 INTO curNo FROM dual;

									        RETURN 'TB'||ZBNo||"LPAD"("TO_CHAR"(curNo),3,'0');

									      END IF;

									        RETURN 'NoOutOfBounds!Check it!';

									  END IF;

									    -- 没有投标书对该招标书

									    RETURN 'TB'||ZBNo||'001';

									END;

然后在两个表中注册触发器，当新增数据的时候动态生成编号！

　　招标书触发器，用于动态生成招标书编号：

				?

									CREATE OR REPLACE

									 TRIGGER newTender

									 BEFORE INSERT

									 ON TENDER

									 FOR EACH ROW

									BEGIN

									  -- 如果生成编号失败

									 IF (LENGTH("createZBNo") <> 13) THEN

									    -- 此处根据我的提示信息报错可以直接如下操作

									    -- :NEW.TENDER_ID := NULL;

									  RAISE_APPLICATION_ERROR(-20222,"createZBNo");

									 END IF;

									    -- 如果生成编号成功，将编号注入查询语句中

									   :NEW.tender_id :="createZBNo";

									END;

然后是投标书的触发器：

				?

									CREATE OR REPLACE

									 TRIGGER newBid

									 BEFORE INSERT

									 ON BID

									 FOR EACH ROW

									BEGIN

									 IF (LENGTH("createTBNo"(:NEW.TENDER_ID)) <> 18) THEN

									  RAISE_APPLICATION_ERROR(-20222,"createTBNo"(:NEW.TENDER_ID));

									 END IF;

									   :NEW.BID_ID :="createTBNo"(:NEW.TENDER_ID);

									END;