Oracle学习记录之使用自定义函数和触发器实现主键动态生成

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 ;