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;

然后插入数据测试吧:

  

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

(0)

相关推荐

  • oracle中添加删除主键的方法

    1.创建表的同时创建主键约束 (1)无命名 create table student ( studentid int primary key not null, studentname varchar(8), age int); (2)有命名 create table students ( studentid int , studentname varchar(8), age int, constraint yy primary key(studentid)); 2.删除表中已有的主键约束 (1

  • 深入Mysql,SqlServer,Oracle主键自动增长的设置详解

    1.把主键定义为自动增长标识符类型MySql在mysql中,如果把表的主键设为auto_increment类型,数据库就会自动为主键赋值.例如: 复制代码 代码如下: create table customers(id int auto_increment primary key not null, name varchar(15));insert into customers(name) values("name1"),("name2");select id fr

  • Oracle 数据库针对表主键列并发导致行级锁简单演示

    本文内容 •软件环境 •简单演示 Oracle 数据库并发导致行级锁 本文简单演示针对表主键并发导致的行级锁.并发是两个以上的用户对同样的数据进行修改(包括插入.删除和修改).锁的产生是因为并发.没有并发,就没有锁.并发的产生是因为系统需要,系统需要是因为用户需要. 软件环境 -------------------------------------------------------------------------------- •Windows 2003 Server •Oracle 1

  • Oracle数据库中创建自增主键的实例教程

    在设计数据库表的时候发现Oracle没有自增主键的设置,Google了解到Oracle本身并不支持自增主键,需要通过序列(Sequence)和触发器(Trigger)实现. 创建表Student Create Table Student( id number(12) primary key, --通过序列和触发器实现id的自增 name varchar2(20) , age number(3) , sex number(1) ) 创建序列Sequence Create Sequence SEQ_

  • Oracle创建主键自增表(sql语句实现)及触发器应用

    1.创建表 复制代码 代码如下: createtableTest_Increase( useridnumber(10)NOTNULLprimarykey,/*主键,自动增加*/ usernamevarchar2(20) ); 2.创建自动增长序列 复制代码 代码如下: CREATESEQUENCETestIncrease_Sequence INCREMENTBY1--每次加几个 STARTWITH1--从1开始计数 NOMAXVALUE--不设置最大值,设置最大值:maxvalue9999 NO

  • oracle删除主键查看主键约束及创建联合主键

    1,主键的删除 ALTER TABLE TABLENAME DROP PRIMARY_KEY 执行上面的SQL可以删除主键:如果不成功可以用 ALTER TABLE TABLENAME DROP CONSTRAINTS COLUMN CASCADE; --删除约束 ALTER TABLE TABLENAME DISABLE PRIMARY_COLUMN ; --设置被设置为主键的列为无效 DROP INDEX INDEX_NAME; --删除主键索引 2,查看主键约束 SELECT * FROM

  • Oracle与Mysql主键、索引及分页的区别小结

    区别: 1.主键,Oracle不可以实现自增,mysql可以实现自增. oracle新建序列,SEQ_USER_Id.nextval 2.索引: mysql索引从0开始,Oracle从1开始. 3.分页, mysql: select * from user order by desc limit n ,m. 表示,从第n条数据开始查找,一共查找m条数据. Oracle:select * from user select rownum a * from ((select * from user)a

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

    很早就想自己写写Oracle的函数和触发器,最近一个来自课本的小案例给了我这个机会.现在把我做的东西记录下来,作为一个备忘或者入门的朋友们的参考. 案例介绍: 招投标管理系统(数据库设计). 数据表有以下两张: 招标书(招标书编号.项目名称.招标书内容.截止日期.状态). 投标书(投标书编号.招标书编号.投标企业.投标书内容.投标日期.报价.状态). "招标书编号"为字符型,编号规则为 ZBYYYYMMDDNNN, ZB是招标的汉语拼音首字母,YYYYMMDD是当前日期,NNN是三位流

  • MySQL自定义函数及触发器

    目录 1. 存储函数(自定义函数) 1.1 定义存储函数 1.2 调用存储函数 1.3 删除存储函数 2. 触发器 2.1 触发器介绍 2.2 创建触发器 2.3 删除触发器 2.4 查看触发器 1. 存储函数(自定义函数) 自定义函数是一种对MySQL扩展的途径,其用法与内置的函数相同. 定义函数的两个必要条件:参数.返回值.函数可以返回任意类型的值,同样可以接收这些类型的参数. 关于函数体: 函数体是由合法的SQL语句构成. 函数体可以是简单的SELECT或INSERT语句. 函数体如果为符

  • Oracle 触发器实现主键自增效果

    触发器的作用 触发器的作用类似拦截器.把一些针对数据库的DML操作(insert/update/delete/select)进行拦截,符合业务要求的进行操作,不符合要求的操作可以通过抛出异常来阻止 说白了就是数据确认(after)与安全性检查(before),此外触发器不针对select操作 1.首先创建表testUser         Oracle设置为不区分大小的,所以table名,言简意赅即可. CREATE TABLE testUser ( id NUMBER ( 11 ) prima

  • ORACLE学习笔记-添加更新数据函数篇

    一.添加数据 /*添加数据*/ insert into STU values('stu0004','赵一',18,1,"kc0004"); insert into STU(STU_ID,STU_NAME,STU_AGE,STU_SET ) values('stu0013','储十一',19,1); 说明:如果不指定将数据添加到那个字段,那么此时必须列出全部数据,如果列出字段,则一一对应,必须列出约束不为空的所有字段,否则报错. 二.更新数据 /*更新数据*/ update STU SE

  • django自定义非主键自增字段类型详解(auto increment field)

    1.django自定义字段类型,实现非主键字段的自增 # -*- encoding: utf-8 -*- from django.db.models.fields import Field, IntegerField from django.core import checks, exceptions from django.utils.translation import ugettext_lazy as _ class AutoIncreField(Field): description =

  • Mybatis insert方法主键回填和自定义操作

    在数据库插入的时候,有很多属性需要我们自己处理,如主键自增字段. MYSQL中主键根据一定规则生成后,需要我们在插入后去主动获取,以便后面的操作,Mybatis为我们提供了处理的方法. 主键回填 keyProperty:指定哪个字段是主键 useGeneratedKeys:这个主键是否使用数据库内置生成策略 我们可以在XML文件中进行如下配置: <insert id="insertUser" parameterType="user" useGeneratedK

  • PHP学习记录之数组函数

    数组 php中的数组是一个有序映射,映射是一种把values关联到keys的类型,数组可以接受任意数量用逗号分隔符的键值对. 数组的介绍 /* * php中数组可以分为: * 索引数组:下标是数字 * 关联数组:下标是字符串 * 注:php中数组其实不区分索引还是关联数组,都是根据键名找到对应的值 * * php可以同时包含integer和string类型的键名 * key:可以是一个整数integer或者字符串string * value:可以是任意类型的值 * * php中的key只能是in

  • php基于自定义函数记录log日志方法

    本文实例讲述了php基于自定义函数记录log日志方法.分享给大家供大家参考,具体如下: /** * 记录错误日志 * @param 日志内容 $res */ function save_log($res) { $err_date = date("Ym", time()); //$address = '/var/log/error'; $address = './error'; if (!is_dir($address)) { mkdir($address, 0700, true); }

  • Python学习笔记之自定义函数用法详解

    本文实例讲述了Python学习笔记之自定义函数用法.分享给大家供大家参考,具体如下: 函数能提高应用的模块性,和代码的重复利用率.Python提供了许多内建函数,比如print()等.也可以创建用户自定义函数. 函数定义 函数定义的简单规则: 函数代码块以def关键词开头,后接函数标识符名称和圆括号(),任何传入参数和自变量必须放在圆括号中间 函数内容以冒号起始,并且缩进 若有返回值,Return[expression] 结束函数:不带return 表达式相当于返回None 函数通常使用三个单引

  • OpenCV学习记录python实现连通域处理函数

    目录 1.两个函数介绍 1.1什么是连通域 1.2 cv2.connectedComponents() 1.3 cv2.connectedComponentsWithStats() 2.代码实践 3.总结 1.两个函数介绍 总得来说,connectedComponents()仅仅创建了一个标记图(图中不同连通域使用不同的标记,和原图宽高一致),connectedComponentsWithStats()可以完成上面任务,除此之外,还可以返回每个连通区域的重要信息–bounding box, ar

随机推荐