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

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

案例介紹：

招投標(biāo)管理系統(tǒng)(數(shù)據(jù)庫(kù)設(shè)計(jì))。

數(shù)據(jù)表有以下兩張：

招標(biāo)書(shū)(招標(biāo)書(shū)編號(hào)、項(xiàng)目名稱(chēng)、招標(biāo)書(shū)內(nèi)容、截止日期、狀態(tài))。

投標(biāo)書(shū)(投標(biāo)書(shū)編號(hào)、招標(biāo)書(shū)編號(hào)、投標(biāo)企業(yè)、投標(biāo)書(shū)內(nèi)容、投標(biāo)日期、報(bào)價(jià)、狀態(tài))。

“招標(biāo)書(shū)編號(hào)”為字符型，編號(hào)規(guī)則為 ZBYYYYMMDDNNN， ZB是招標(biāo)的漢語(yǔ)拼音首字母，YYYYMMDD是當(dāng)前日期，NNN是三位流水號(hào)。

“投標(biāo)書(shū)編號(hào)”為字符型，編號(hào)規(guī)則為T(mén)B[11位招標(biāo)書(shū)編號(hào)]NNN。

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

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

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

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. -- 查詢(xún)表中是否有記錄 
11. SELECT "COUNT"(TENDER_ID) INTO hasCount FROM TENDER; 
12. IF hasCount > 0 THEN 
13. -- 查詢(xún)必要信息 
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. -- 分析上一次發(fā)布招標(biāo)信息是否是今日 
17. IF ("TO_CHAR"(SYSDATE,'YYYYMMDD') = lastTime) THEN 
18. SELECT "TO_NUMBER"("SUBSTR"(lastID, 11, 13), '999') INTO lastNo FROM dual; 
19. -- 如果是今日且流水號(hào)允許新增招標(biāo)信息 
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. -- 流水號(hào)超出 
25. RETURN 'NoOutOfBounds!Check it!'; 
26. END IF; 
27. -- 不是今日發(fā)布的招標(biāo)信息，今日是第一次 
28. RETURN 'ZB'||"TO_CHAR"(SYSDATE,'YYYYMMDD')||'001'; 
29. END IF; 
30. -- 整個(gè)表中的第一條數(shù)據(jù) 
31. RETURN 'ZB'||"TO_CHAR"(SYSDATE,'YYYYMMDD')||'001'; 
32. END; 

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

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. -- 查看是否已經(jīng)有了對(duì)于該想招標(biāo)的投標(biāo)書(shū) 
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. -- 流水號(hào)沒(méi)超出 
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. -- 沒(méi)有投標(biāo)書(shū)對(duì)該招標(biāo)書(shū) 
24. RETURN 'TB'||ZBNo||'001'; 
25. END; 

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

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

1. CREATE OR REPLACE 
2. TRIGGER newTender 
3. BEFORE INSERT 
4. ON TENDER 
5. FOR EACH ROW 
6. BEGIN 
7. -- 如果生成編號(hào)失敗 
8. IF (LENGTH("createZBNo") <> 13) THEN 
9. -- 此處根據(jù)我的提示信息報(bào)錯(cuò)可以直接如下操作 
10. -- :NEW.TENDER_ID := NULL; 
11. RAISE_APPLICATION_ERROR(-20222,"createZBNo"); 
12. END IF; 
13. -- 如果生成編號(hào)成功，將編號(hào)注入查詢(xún)語(yǔ)句中 
14. :NEW.tender_id :="createZBNo"; 
15. END; 

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

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; 

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

以上只是個(gè)人的一些觀點(diǎn)，如果您不認(rèn)同或者能給予指正和幫助，請(qǐng)不吝賜教。