Oracle自学之触发器1

库存管理
4张表，我们称为数据字典

入库表deptin
标识       字段      类型       长度   是否主键   是否外键    是否为空    其他约束   备注
入库编号   inid      number      10      是
物资编号   proid     number      10                 是
入库数量   innum     number      10


出库表doptout
标识       字段      类型       长度   是否主键   是否外键    是否为空    其他约束   备注
出库编号    outid     number     10      是
物资编号    proid     number     10                 是
出库数量    outnum    number     10 
 

库存表dopt
标识       字段      类型       长度   是否主键   是否外键    是否为空    其他约束   备注
库存编号   doptid    number     10       是                    否
物资编号   proid     number     10                  是       
库存数量   doptnum   number     10
 
物资定义表pro  
标识       字段      类型       长度   是否主键   是否外键    是否为空    其他约束   备注
物资编号    proid    number     10       是         否
物资名称    proname  varchar2   20       
物资描述    prodesc  varchar2   50

物资编号    物资名称    物资描述 
 1           复印纸
 2           签字笔
 3           办公桌
 4           记事本
 5           椅子


powerdesign数据库设计软件


数据字典怎么描述？


powerDesign数据库建表连接


创建一个Physical Data Model,修改Model name，DBMS选择数据库类型，如ORACLE Version 11g


主键记得勾选，外键可以用连接符连接


导出Data base——Generate Data base


--------------------------------------------------------------------------------------


PL/SQL Developer 创建新用户
System系统管理员登录，创建一个user，填写name，Password
写入Role privileges，选择connect及Resource，即可。

--------------------------------------------------------------------------------------
打开Conmmand Window，SQL>中输入  @ F:\2017\dopt.sql;换行即可。

--------------------------------------------------------------------------------------
设置sequences，SP_DOPT，SP_DOPTIN，SP_DOPTOUT


触发器：在我们往入库表添加记录的时候
new Trigger
name:tri_in
files:before
event:insert
Table or view:dopin


--触发器创建
create or replace trigger tri_in
  before insert on doptin  
  for each row
declare
  -- local variables here
  v_count number;
begin
  
  --业务规则
  select count(*) into v_count from dopt where proid = :new.proid;
  --1、检测是否第一次入库此货物，需要在dopt表中建立新纪录
  if v_count =0 then
        insert into dopt values(sp_dopt.nextval,:new.proid,:new.innum);
  else
  --2、否则修改dopt表中库存数量
        update dopt set doptnum=doptnum+:new.innum where proid = :new.proid;
  
  end if;
  
  exception when others then
            dbms_output.put_line(sqlerrm);
            
  
end tri_in;


--出库前触发器
create or replace trigger tri_out
  before insert on doptout  
  for each row
declare
  v_num number;
begin
  --统计货物够不够出库数量
  select doptnum into v_num from dopt where proid= :new.proid;
  
  if :new.outnum>v_num then 
        raise_application_error(-20001,'库存不足');
  else
        update dopt set doptnum=doptnum - :new.outnum where proid= :new.proid;
  end if; 
  
end tri_out;


--检验
SQL> insert into doptin values(sp_doptin.nextval,1,20);


1 row inserted


SQL> select * from doptin;


       INID                 PROID       INNUM
----------- --------------------- -----------
        101                     1          20


SQL> insert into doptout values(sp_doptout.nextval,1,20);


1 row inserted


SQL> select * from dopt;


     DOPTID                 PROID     DOPTNUM
----------- --------------------- -----------
         11                     1          20


SQL> select * from doptin;


       INID                 PROID       INNUM
----------- --------------------- -----------
        101                     1          20
        102                     1          20
        103                     1          50


SQL> insert into doptout values(sp_doptout.nextval,2,50);


insert into doptout values(sp_doptout.nextval,2,50)


ORA-20001: 库存不足
ORA-06512: 在 "HYD.TRI_OUT", line 8
ORA-04088: 触发器 'HYD.TRI_OUT' 执行过程中出错


触发器就是对维护业务逻辑的一种很好的工具。

触发器是指放在数据库中，并隐含执行的存储过程，程序员一般做DML操作的触发器。
在Oracle8i之前，只允许基于表或者视图的DML操作（INSERT,UPDATE和DELETE）建立
触发器，而从Oracle8i开始，不仅支持DML触发器，也允许基于系统事件（启动数据库，
关闭数据库，登录）和DDL操作建立触发器。