快捷導航

Oracle學習記錄之使用自定義函數(shù)和觸發(fā)器實現(xiàn)主鍵動態(tài)生成

更新時間：2015年11月05日 17:00:27 投稿：mdxy-dxy

這篇文章主要介紹了Oracle學習記錄之使用自定義函數(shù)和觸發(fā)器實現(xiàn)主鍵動態(tài)生成,需要的朋友可以參考下

很早就想自己寫寫Oracle的函數(shù)和觸發(fā)器，最近一個來自課本的小案例給了我這個機會。現(xiàn)在把我做的東西記錄下來，作為一個備忘或者入門的朋友們的參考。

　　案例介紹：

　　　　招投標管理系統(tǒng)（數(shù)據(jù)庫設(shè)計）。
　　　　數(shù)據(jù)表有以下兩張：
　　　　　　招標書（招標書編號、項目名稱、招標書內(nèi)容、截止日期、狀態(tài)）。
　　　　　　投標書（投標書編號、招標書編號、投標企業(yè)、投標書內(nèi)容、投標日期、報價、狀態(tài)）。
　　　　　　“招標書編號”為字符型，編號規(guī)則為 ZBYYYYMMDDNNN， ZB是招標的漢語拼音首字母，YYYYMMDD是當前日期，NNN是三位流水號。
　　　　　　“投標書編號”為字符型，編號規(guī)則為TB[11位招標書編號]NNN。

　　經(jīng)過分析，我們可以得知兩張表的關(guān)系。我們先創(chuàng)建數(shù)據(jù)結(jié)構(gòu)，比如：

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);

然后是生成招標的函數(shù)：

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;
    -- 分析上一次發(fā)布招標信息是否是今日
    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;
      -- 不是今日發(fā)布的招標信息，今日是第一次
      RETURN 'ZB'||"TO_CHAR"(SYSDATE,'YYYYMMDD')||'001';
  END IF;
      -- 整個表中的第一條數(shù)據(jù)
    RETURN 'ZB'||"TO_CHAR"(SYSDATE,'YYYYMMDD')||'001';
END;

然后是投標書的編號生成函數(shù)：

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
  -- 查看是否已經(jīng)有了對于該想招標的投標書
  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;

然后在兩個表中注冊觸發(fā)器，當新增數(shù)據(jù)的時候動態(tài)生成編號！

　　招標書觸發(fā)器，用于動態(tài)生成招標書編號：

CREATE OR REPLACE 
 TRIGGER newTender
 BEFORE INSERT 
 ON TENDER
 FOR EACH ROW
BEGIN
  -- 如果生成編號失敗
 IF (LENGTH("createZBNo") <> 13) THEN
    -- 此處根據(jù)我的提示信息報錯可以直接如下操作
    -- :NEW.TENDER_ID := NULL;
  RAISE_APPLICATION_ERROR(-20222,"createZBNo");
 END IF;
    -- 如果生成編號成功，將編號注入查詢語句中
   :NEW.tender_id :="createZBNo";
END;

然后是投標書的觸發(fā)器：

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;

然后插入數(shù)據(jù)測試吧：