很早就想自己寫寫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;
然后插入數據測試吧：
以上只是個人的一些觀點，如果您不認同或者能給予指正和幫助，請不吝賜教。
您可能感興趣的文章:- oracle 存儲過程詳細介紹(創建,刪除存儲過程,參數傳遞等)
- Oracle存儲過程返回游標實例詳解
- oracle 在一個存儲過程中調用另一個返回游標的存儲過程
- Oracle存儲過程入門學習基本語法
- Oracle中 關于數據庫存儲過程和存儲函數的使用
- oracle如何恢復被覆蓋的存儲過程
- oracle數據庫中查看系統存儲過程的方法
- oracle 存儲過程和函數例子
- Oracle 存儲過程總結 二、字符串處理相關函數
- Oracle存儲過程和存儲函數創建方法(詳解)
- Oracle存儲過程和自定義函數詳解
- oracle 存儲過程、函數和觸發器用法實例詳解
