时间：2021-07-21人气：-
这篇文章主要介绍了Oracle学习记录之使用自定义函数和触发器实现主键动态生成,需要的朋友可以参考下
很早就想自己写写Oracle的函数和触发器，最近一个来自课本的小案例给了我这个机会。现在把我做的东西记录下来，作为一个备忘或者入门的朋友们的参考。
案例介绍：
招投标管理系统(数据库设计)。
数据表有以下两张：
招标书(招标书编号、项目名称、招标书内容、截止日期、状态)。
投标书(投标书编号、招标书编号、投标企业、投标书内容、投标日期、报价、状态)。
“招标书编号”为字符型，编号规则为 ZBYYYYMMDDNNN， ZB是招标的汉语拼音首字母，YYYYMMDD是当前日期，NNN是三位流水号。
“投标书编号”为字符型，编号规则为TB[11位招标书编号]NNN。
经过分析，我们可以得知两张表的关系。我们先创建数据结构，比如：
- CREATETABLETENDER(
- TENDER_IDVARCHAR2(50)PRIMARYKEY,PROJECT_NAMEVARCHAR2(50)NOTNULLUNIQUE,
- CONTENTBLOB,END_DATEDATENOTNULL,
- STATUSINTEGERNOTNULL);
- CREATETABLEBID(
- BID_IDVARCHAR2(50)PRIMARYKEY,TENDER_IDVARCHAR2(50)NOTNULL,
- COMPANYVARCHAR2(50)NOTNULL,CONTENTBLOB,
- BID_DATEDATENOTNULL,PRICEINTEGERNOTNULL,
- STATUSINTEGERNOTNULL);
- ALTERTABLEBIDADDCONSTRAINTFK_BID_TENDER_IDFOREIGNKEY(TENDER_ID)REFERENCESTENDER(TENDER_ID);
然后是生成招标的函数：
- CREATEORREPLACEFUNCTION"createZBNo"RETURNVARCHAR2
- AShasCountNUMBER(11,0);
- lastIDVARCHAR2(50);lastTimeVARCHAR2(12);
- lastNoNUMBER(3,0);curNoNUMBER(3,0);
- BEGIN--查询表中是否有记录
- SELECT"COUNT"(TENDER_ID)INTOhasCountFROMTENDER;IFhasCount>0THEN
- --查询必要信息SELECTTENDER_IDINTOlastIDFROMTENDERWHEREROWNUM=1ORDERBYto_number(to_char(scn_to_timestamp(ORA_ROWSCN),'yyyyMMddhh24mmss'),'99999999999999')DESC;
- SELECT"SUBSTR"(lastID,3,8)INTOlastTimeFROMdual;--分析上一次发布招标信息是否是今日
- IF("TO_CHAR"(SYSDATE,'YYYYMMDD')=lastTime)THENSELECT"TO_NUMBER"("SUBSTR"(lastID,11,13),'999')INTOlastNoFROMdual;
- --如果是今日且流水号允许新增招标信息IFlastNo<999THEN
- SELECTlastNo+1INTOcurNoFROMdual;RETURN'ZB'||lastTime||"LPAD"("TO_CHAR"(curNo),3,'0');
- ENDIF;--流水号超出
- RETURN'NoOutOfBounds!Checkit!';ENDIF;
- --不是今日发布的招标信息，今日是第一次RETURN'ZB'||"TO_CHAR"(SYSDATE,'YYYYMMDD')||'001';
- ENDIF;--整个表中的第一条数据
- RETURN'ZB'||"TO_CHAR"(SYSDATE,'YYYYMMDD')||'001';END;
然后是投标书的编号生成函数：
- CREATEORREPLACEFUNCTION"createTBNo"(ZBNoINVARCHAR2)
- RETURNVARCHAR2AS
- hasCountNUMBER(11,0);lastIDVARCHAR2(50);
- lastNoNUMBER(3,0);curNoNUMBER(3,0);
- BEGIN--查看是否已经有了对于该想招标的投标书
- SELECT"COUNT"(BID_ID)INTOhasCountFROMBIDWHEREBID_IDLIKE'TB'||ZBNo||'___'ANDROWNUM=1ORDERBYto_number(to_char(scn_to_timestamp(ORA_ROWSCN),'yyyyMMddhh24mmss'),'99999999999999')DESC;IFhasCount>0THEN
- --有了SELECTBID_IDINTOlastIDFROMBIDWHEREBID_IDLIKE'TB'||ZBNo||'___'ANDROWNUM=1ORDERBYto_number(to_char(scn_to_timestamp(ORA_ROWSCN),'yyyyMMddhh24mmss'),'99999999999999')DESC;
- SELECT"TO_NUMBER"("SUBSTR"(lastID,16,18),'999')INTOlastNoFROMdual;--流水号没超出
- IFlastNo<999THENSELECTlastNo+1INTOcurNoFROMdual;
- RETURN'TB'||ZBNo||"LPAD"("TO_CHAR"(curNo),3,'0');ENDIF;
- RETURN'NoOutOfBounds!Checkit!';ENDIF;
- --没有投标书对该招标书RETURN'TB'||ZBNo||'001';
- END;
然后在两个表中注册触发器，当新增数据的时候动态生成编号!
招标书触发器，用于动态生成招标书编号：
- CREATEORREPLACETRIGGERnewTender
- BEFOREINSERTONTENDER
- FOREACHROWBEGIN
- --如果生成编号失败IF(LENGTH("createZBNo")<>13)THEN
- --此处根据我的提示信息报错可以直接如下操作--:NEW.TENDER_ID:=NULL;
- RAISE_APPLICATION_ERROR(-20222,"createZBNo");ENDIF;
- --如果生成编号成功，将编号注入查询语句中:NEW.tender_id:="createZBNo";
- END;
然后是投标书的触发器：
- CREATEORREPLACETRIGGERnewBid
- BEFOREINSERTONBID
- FOREACHROWBEGIN
- IF(LENGTH("createTBNo"(:NEW.TENDER_ID))<>18)THENRAISE_APPLICATION_ERROR(-20222,"createTBNo"(:NEW.TENDER_ID));
- ENDIF;:NEW.BID_ID:="createTBNo"(:NEW.TENDER_ID);
- END;
然后插入数据测试吧：
以上只是个人的一些观点，如果您不认同或者能给予指正和帮助，请不吝赐教。
