Oracle Class9. 数据库触发器和内置程序包

------------------------2013-5-20------------------------
数据库触发器
内置程序包

是pl/sql块或存储过程，dml操作时触发，隐式执行，可能具有声明部分和异常处理部分。

组成：语句，主体，限制。
类型：应用程序触发器，数据库触发器。

instead of触发器。
视图上而不是在表上定义的。   !!
提供修改视图的透明方法。
可以针对视图编写insert,update及delete语句。
以不可见方式在后台运行。
执行在触发器主体中编写的操作。

启用或禁用触发器
alter trigger <Trigger_name> enable;
alter trigger <Trigger_name> disable;
删除触发器
drop trigger <Trigger_name>;
查看有关触发器，使用下列数据字典视图：
user_triggers
all_triggers
dba_triggers

内置程序包
扩展数据库的功能
为pl/sql提供对sql功能的访问
用户sys拥有所有程序包
是公共同义词可以由任何用户访问
用户必须具有execute权限，才能访问过程和函数。

程序包名称  描述
dbms_standard  提供语言工具
dbms_alert  支持数据库事件的异步通知
dbms_application_info 允许为跟踪目的而注册应用程序
dbms_aq & dbms_aqadm 管理oracle advanced queuing选件
dbms_defer,
dbms_defer_sys,
dbms_defer_query 允许构建和管理延迟的远程过程调用
dbms_ddl  某些ddl命令的pl/sql等效项
calendar  提供日历维护功能
dbms_describe  描述存储子程序
dbms_lob  操纵oracle lob
dbms_job  允许对pl/sql过程进行调度
dbms_lock  用户定义的锁
dbms_output  在sql*plus或服务器管理器中提供屏幕输出

dbms_output中的一些过程
-Enable
-Disable
-Put
-Put_line
-New_line
-Get_line
-Get_lines

dbms_lob
-append
-compare
-copy
-erase
-fileclose
-fileexists
-filegetname
-getlength

强制约束 类型
- 非空（not null）
- 主键（primary key）
- 外键（foreign key）
- 唯一（unique）
- 检查（check）
- 缺省（default）

--创建表，并使用约束。
create table student2(
  no char(4) not null primary key,
  name varchar2(8) not null unique,
  sex char(2) default('女') check(sex='女' or sex='男'),
  age int default(18) check(age > 0 and age < 100),
  phone char(11) null
);

--添加数据
insert into t values(1);
insert into t (select a from t);
--查询表
select * from t;

--语句级触发器
set serveroutput on;

create or replace trigger t_ais
after insert on t
begin
  dbms_output.put_line('Insert Statement...');
end;

insert into t values(2);
insert into t values(3);

--行级
create or replace trigger t_air
after insert on t
for each row
begin
  dbms_output.put_line('Insert Row...');
end;

-- 多功能触发器
create or replace trigger t_ardur
after insert or update or delete on t
for each row
begin
  case
  when inserting then
    dbms_output.put_line('Insert is Coming@...');
  when deleting then
    dbms_output.put_line('Delete is Coming@...');
  when updating then
    dbms_output.put_line('Update is Coming@...');
  end case;
end;

--insert into t values(7);
--update t set a = 8 where a=7;
delete from t where a = 8;
select * from t;                 分别在执行添删改查时触发。

--创建表，添加数据。
create table users
(
id int,
name varchar2(12)
);

insert into users values(1,'ANiu');
insert into users values(2,'AZhu');

--判断当前用户
create or replace trigger users_budr
before update or delete on users
for each row
begin
  if user = 'NEWLIFEYHJ' then
    dbms_output.put_line('WelCome NewLifeYhj');
  else
    dbms_output.put_line('No no no');
  end if;
end;

update users set name='AGou' where id=2;

加上判断条件
when (to_char(sysdate,'Day') = '星期日')
insert into log values (user, sysdate,:old.name || ' 被修改为 ' || :new.name);  触发器主体对另外一个表进行操作。

--创建日志表
create table log
(
username varchar2(24),
operdate date,
operations varchar2(500)
);

create or replace trigger users_budr
before delete or update of name on users
for each row
when (to_char(sysdate,'Day') = '星期一')
begin
  if user = 'NEWLIFEYHJ' then
    dbms_output.put_line('WelCome NewLifeYhj');
  else
    dbms_output.put_line('No no no');
  end if;
  insert into log values(user,sysdate,:old.name||'被修改为'||:new.name);
end;

update users set name='AZhu' where id=3;

--逻辑记录
:New
:Old

ORA-04092: COMMIT 不能在触发器中

set serveroutput on;    --重新连接之后，打印信息出不来，是这个需要重新设置。-- ##这就是奇怪为什么打印出不来的原因了##

update users set name='AZhu' where id=1;

select * from user_triggers;  --数据字典--

--触发器实例应用--进销存
--进货表
create table jh
(
hh char,       --货号
sl number(3),  --数量
sj date    --时间
);
--销售表
create table xs
(
hh char,       --货号
sl number(3),  --数量
sj date    --时间
);

--库存表
create table cc
(
hh char,       --货号
sl number(3),  --数量
sj date    --时间
);

--进货触发器--
create or replace trigger triInsertJH
after insert
on jh
for each row
declare
lv_hc_num int;
begin
  select count(*) into lv_hc_num from cc where hh = :new.hh;
  if lv_hc_num > 0 then
    update cc set sl = sl + :new.sl where  hh = :new.hh;
  else
    insert into cc values (:new.hh, :new.sl,sysdate);
  end if;
end;

insert into jh values ('A', 120, sysdate);

--销售触发器--
create or replace trigger triInsertXS
after insert
on xs
for each row
declare
lv_hc_num int;
begin
  select sl into lv_hc_num from cc where hh = :new.hh;
  if lv_hc_num >=  :new.sl then
    update cc set sl = sl - :new.sl where  hh = :new.hh;
  else
    RAISE_APPLICATION_ERROR (-20001, '销售量远大于现有库存量！');
  end if;
end;

内置程序包
取随机数：select dbms_random.random from dual;
          select dbms_random.string('q',4) from dual;   -- 四位字符随机数。

#dbms_lob#
#file xml 操作#  dbms_xmlquery  dbms_xmlgen

dbms_sql 动态sql

--建立文件夹
create or replace directory wrox_dir as 'c:\temp2';

dbms_utility

##sql server##
sp_helptrigger
sp_helptext