45fan.com - 路饭网

搜索: 您的位置主页 > 网络频道 > 阅读资讯:如何通过Oracle使用自定义函数和触发器实现主键动态生成?

如何通过Oracle使用自定义函数和触发器实现主键动态生成?

2016-08-10 12:33:53 来源:www.45fan.com 【

如何通过Oracle使用自定义函数和触发器实现主键动态生成?

很早就想自己写写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使用自定义函数和触发器实现主键动态生成?

以上只是个人的一些观点,如果您不认同或者能给予指正和帮助,请不吝赐教。


本文地址:http://www.45fan.com/a/question/61478.html
Tags: oracle 学习 记录
编辑:路饭网
关于我们 | 联系我们 | 友情链接 | 网站地图 | Sitemap | App | 返回顶部