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;

然后插入数据测试吧：

 

　　

 

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