触发器和内置程序包文档格式.docx
《触发器和内置程序包文档格式.docx》由会员分享，可在线阅读，更多相关《触发器和内置程序包文档格式.docx（29页珍藏版）》请在冰豆网上搜索。
selectempno,comm,deptnofromempwhereempno=8888;
2、触发器组件
（1）触发器时机
before、after、insteadof
（2）触发器事件
导致触发器执行的特定事件：
表或视图上的DML之增删改、模式对象上的DDL、实例范围的数据库事件
（3）表名
如“beforeinsertorupdateofdeptnoonemp”
（4）触发器类型，语句级、行级、insteadof、模式、数据库级
（5）触发器限制，when子句
[when（布尔表达式）]，如“when（new.deptno<
40）”
（6）触发器主体，触发器操作，PL/SQL块
PL/SQL块declare-begin-exception，如“begin:
end;
”
只有行级触发器的PL/SQL块中的语句能访问行的列值，如“foreachrow...:
--在emp表中更新sal列之后，
--如果新行的sal大于旧行的sal，就输出它们的差值，
afterupdateofsalonemp
when（new.sal-old.sal>
0）
declare
diffnumber;
diff:
=:
new.sal-:
old.sal;
dbms_output.put_line（diff）;
updateempsetsal=sal+100whereempno=7788;
3、触发器类型
（1）行级触发器
对DML语句影响的每个行都执行一次的触发器，有foreachrow
--创建序列
createsequenceseq;
--创建触发器
--如果一个触发器由多种DML语句触发
beforeinsertorupdateofempnoonemp
--在触发器主体中用条件谓词inserting、deleting、updating判断
ifinsertingthen
--用行级触发器给主键插入序列
selectseq.nexvalinto:
new.empnofromdual;
else
raise_application_error（-20001,'
不许更新empno'
）;
endif;
insertintoemp（empno）values（0）;
selectempnofromemp;
--emp删一条，向同结构的表emp_log写一条
--创建和emp同结构的空表emp_log
createtableemp_logasselect*fromempwhere1=2;
createorreplacetriggerdelete_trg
beforedeleteonemp
foreachrow
insertintoemp_log（empno,ename,sal,deptno）
values（:
old.empno,:
old.ename,:
old.sal,:
old.deptno）;
end;
deleteempwhereempno=7788;
select*fromemp_log;
rollback;
（2）语句级触发器
无论DML语句影响了多少行，每个DML语句只执行一次的触发器，无foreachrow
--DML影响很多行后，只给出一句提示
afterinsertordeleteorupdateonemp
dbms_output.put_line（'
已插入'
elsifdeletingthen
已删除'
elsifupdatingthen
已更新'
updateempsetsal=3000;
--更新14行，但只显示一条“已更新”
（3）insteadof触发器
on后面必须是视图
必须有foreachrow
用PL/SQL块中的语句替换DML语句的触发器，用于修改不能直接用DML语句修改的视图
DML语句不执行，触发器主体执行，只用于视图
--通过视图同时向两个表插入数据、如果数据已存在则按主键更新
--创建视图
createorreplaceviewemp_viewas
selecte.empno,e.ename,d.deptno,d.dnamefromempe,deptd
wheree.deptno=d.deptno;
createorreplacetriggeremp_dept_trg
insteadofinsertonemp_view
cursorecisselect*fromemp
whereempno=:
new.empno;
cursordcisselect*fromdept
wheredeptno=:
new.deptno;
v_empec%rowtype;
v_deptdc%rowtype;
openec;
opendc;
fetchecintov_emp;
fetchdcintov_dept;
ifdc%notfoundthen
insertintodept（deptno,dname）values（:
new.deptno,:
new.dname）;
updatedeptsetdname=:
new.dnamewheredeptno=:
ifec%notfoundthen
insertintoemp（empno,ename）values（:
new.empno,:
new.ename）;
updateempsetename=:
new.enamewhereempno=:
closeec;
closedc;
endemp_dept_trg;
insertintoemp_viewvalues（9999,'
MACMIC'
10,'
GAME'
--通过视图同时向两个表插入数据、如果数据已存在则提示“主键重复”
selecte.empno,e.ename,d.deptno,d.dname
fromempeinnerjoindeptd
one.deptno=d.deptno;
insteadofinsertonemp_view
v_tempint;
selectcount（*）intov_tempfromdept
wheredeptno=:
ifv_temp=0then
insertintodept（deptno,dname）
selectcount（*）intov_tempfromemp
whereempno=:
insertintoemp（empno,ename,deptno）
new.ename,:
new.deptno）;
raise_application_error（-20001,'
主键重复'
createorreplaceviewemp_view
（total_employeer,total_salary,deptno）
asselectcount（*）,sum（sal）,deptno
fromempgroupbydeptno
--在视图上删除部门号为30的记录
deleteemp_viewwheredeptno=30;
--error，此视图的数据操纵操作非法
--创建insteadof触发器
insteadofdeleteonemp_view
deleteempwheredeptno=:
old.deptno;
--ok
（4）模式触发器
在模式级的操作上建立的触发器，在模式对象上进行模式级操作时触发触发器
模式级的操作：
DDL：
create、alter、drop、truncate
DCL：
grant、revoke
模式对象：
表、视图、索引、序列、同义词、过程、函数、程序包
{before|after}{create|alter|drop|truncate|ddl|grant|revoke|dcl}
on[某模式.]schema
when（某条件）
--创建表，记录mac模式的所有ddl操作
createtableevent_ddl
（
eventvarchar2（20）,
usernamevarchar2（10）,
ownervarchar2（10）,
objnamevarchar2（20）,
objtypevarchar2（10）,
timedate
--创建mac的模式触发器
createorreplacetriggerddl_trg
afterddlonmac.schema
insertintoevent_ddl
values（ora_sysevent,ora_login_user,
ora_dict_obj_owner,ora_dict_obj_name,
ora_dict_obj_type,sysdate）;
endddl_trg;
--创建、删除一个表
connmac/mac@macmic
createtabletest_table（xchar）;
droptabletest_table;
--查看mac的模式事件表
select*fromevent_ddl;
（5）数据库级触发器
在数据库事件上创建的触发器
数据库事件是跨模式的、实例范围的，
包括：
startup、shutdown、服务器错误、
logon（当connect时触发）、logoff（当disconnect时触发）
--必须sys才有startup、shutdown权限
connsys/sysassysdba
setserveroutputon
createorreplacetriggerdatabase_trg
beforeshutdownondatabase
dbms_output.put_line（'
数据库即将关闭'
enddatabase_trg;
--测试，为什么没触发？
shutdown
--创建记录登录、登出信息的表
createtablelog_table
logon_timedate,
logoff_timedate,
addressvarchar2（20）
--创建登录触发器
createorreplacetriggerlogon_trg
afterlogonondatabase
insertintolog_table（username,logon_time,address）
values（ora_login_user,sysdate,ora_client_ip_address）;
--创建登出触发器
createorreplacetriggerlogoff_trg
beforelogoffondatabase
insertintolog_table（username,logoff_time,address）
--分别用disc、conn命令测试登出、登录触发器
disc
conn
--查看触发器插入log_table中的登录、登出信息，为什么ora_client_ip_address列没数据？
select*fromlog_table;
4、触发器限制
①select语句必须是selectinto、或内部游标声明
②不许DDL、TCL
触发器中不能调用包括TCL的存储过程
事务有原子性，整个触发器是一个完整事务，所以触发器中不能再嵌TCL
--测试用的表
createtablet（t1numberprimarykey,t2varchar（10））;
--带TCL的触发器
createorreplacetriggertr_sec_emp
beforeinsertorupdateordeleteonemp
insertintotvalues（1,'
a'
insertintotvalues（2,'
commit;
endtr_sec_emp;
deleteemp;
--error，COMMIT不能在触发器中
③:
old、:
new都是rowtype，但是行中不能有long、longrow类型的列
④在PL/SQL块的begin~end之间，只要不是declare部分声明的变量，都要用冒号
但是when条件中的new、old不用带冒号
⑤语句级触发器的执行次数=1
行级触发器的执行次数=n
⑥列级触发器updateof某列,...
5、启用和禁用触发器
altertrigger触发器{enable|disable};
altertable表名{enable|disable}alltriggers;
--触发器属于特定的表
6、删除触发器
droptrigger触发器;
7、查看触发器信息
descuser_triggers;
selecttrigger_namefromuser_triggerswheretable_name='
表'
;
selecttrigger_type,triggering_event,when_clausefromuser_triggerswheretrigger_name='
触发器'
--综合例子
--创建表
createtabledept_summary
deptnonumber
（2）,
sal_sumnumber（9,2）,
emp_countnumber
--插入数据测试表
insertintodept_summary
selectdeptno,sum（sal）,count（*）
fromempgroupbydeptno;
--创建存储过程，显示dept_summary表
createorreplaceproceduredept_proc
as
cursorcurisselect*fromdept_summary;
forv_recincurloop
dbms_output.put_line（v_rec.deptno||'
:
'
||v_rec.sal_sum||'
||v_rec.emp_count）;
endloop;
afterinsertorupdateordeleteonemp
deletefromdept_summary;
insertintodept_summary
selectdeptno,sum（sal）,count（*）
fromempgroupbydeptno;
--创建PL/SQL块，
--用dbms_utility.exec_ddl_statement（'
DDL语句'
）在PL/SQL块中创建触发器，
--在块中向emp插入数据，调用存储过程dept_proc显示dept_summay表中信息
--用dbms_utility.exec_ddl_statement（'
）在PL/SQL块中创建触发器
dbms_utility.exec_ddl_statement
（'
createorreplacetriggeremp_trg
begin
--删除dept_summary表的旧记录
insertintodept_summary--向dept_summary表插入新记录
endemp_trg;
'
insertintoemp（empno,ename,sal,deptno）--向emp插入数据
values（8899,'
MIC'
8899,20）;
dept_proc;
--调用存储过程dept_proc显示dept_summay表中信息
二、内置程序包
sys拥有Oracle所有私有对象，它们被定义成公有同义词，并把执行权限授予public组，
所以所有用户都可以不带模式名地访问这些对象
--源代码：
c:
\oracle\ora92\rdbms\admin\*.sql
rem--注释
createorreplacepackage某内置包as
procedure某过程（参数列表）;
pragmarestrict_references（某过程,WNDS,RNDS）;
createorreplacepublicsynonymdbms_outputfordbms_output--公有同义词
grantexecuteondbms_outputtopublic--执行权限授予public组
程序包名称
说明
standard和dbms_standard
此包内的过程不加“包.”前缀就能调用，如raise_application_error（）
dbms_lob
操作blob、clob、bfile等类型数据
dbms_lock
dbms_output
从PL/SQL块输出信息
dbms_session
dbms_rowid
dbms_random
8位随机正负整数
dbms_sql
动态SQL，DML、DDL，可以返回多行、空行
dbms_job
dbms_xmldom
升级会员 