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

2020-07-26 14:14:34

字體：大中小

供稿：網(wǎng)友

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

　　案例介紹：

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

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

CREATE OR REPLACE FUNCTION "createZBNo" RETURN VARCHAR2AShasCount 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ā)布招標(biāo)信息是否是今日    IF ("TO_CHAR"(SYSDATE,'YYYYMMDD') = lastTime) THEN      SELECT "TO_NUMBER"("SUBSTR"(lastID, 11, 13), '999') INTO lastNo FROM dual;      -- 如果是今日且流水號(hào)允許新增招標(biāo)信息      IF lastNo < 999 THEN        SELECT lastNo + 1 INTO curNo FROM dual;        RETURN 'ZB'||lastTime||"LPAD"("TO_CHAR"(curNo), 3, '0');      END IF;        -- 流水號(hào)超出        RETURN 'NoOutOfBounds!Check it!';    END IF;      -- 不是今日發(fā)布的招標(biāo)信息，今日是第一次      RETURN 'ZB'||"TO_CHAR"(SYSDATE,'YYYYMMDD')||'001';  END IF;      -- 整個(gè)表中的第一條數(shù)據(jù)    RETURN 'ZB'||"TO_CHAR"(SYSDATE,'YYYYMMDD')||'001';END;

然后是投標(biāo)書的編號(hào)生成函數(shù)：

CREATE OR REPLACE FUNCTION "createTBNo" (ZBNo IN VARCHAR2)RETURN VARCHAR2AShasCount NUMBER(11,0);lastID VARCHAR2(50);lastNo NUMBER(3,0);curNo NUMBER(3,0);BEGIN  -- 查看是否已經(jīng)有了對(duì)于該想招標(biāo)的投標(biāo)書  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;      -- 流水號(hào)沒超出      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;    -- 沒有投標(biāo)書對(duì)該招標(biāo)書    RETURN 'TB'||ZBNo||'001';END;

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

　　招標(biāo)書觸發(fā)器，用于動(dòng)態(tài)生成招標(biāo)書編號(hào)：

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

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

CREATE OR REPLACE  TRIGGER newBid BEFORE INSERT  ON BID FOR EACH ROWBEGIN 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ù)測(cè)試吧：