触发器和内置程序包文档格式.docx

上传人:b****5 文档编号:16201774 上传时间:2022-11-21 格式:DOCX 页数:29 大小:27.13KB
下载 相关 举报
触发器和内置程序包文档格式.docx_第1页
第1页 / 共29页
触发器和内置程序包文档格式.docx_第2页
第2页 / 共29页
触发器和内置程序包文档格式.docx_第3页
第3页 / 共29页
触发器和内置程序包文档格式.docx_第4页
第4页 / 共29页
触发器和内置程序包文档格式.docx_第5页
第5页 / 共29页
点击查看更多>>
下载资源
资源描述

触发器和内置程序包文档格式.docx

《触发器和内置程序包文档格式.docx》由会员分享,可在线阅读,更多相关《触发器和内置程序包文档格式.docx(29页珍藏版)》请在冰豆网上搜索。

触发器和内置程序包文档格式.docx

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

展开阅读全文
相关资源
猜你喜欢
相关搜索

当前位置:首页 > 小学教育 > 数学

copyright@ 2008-2022 冰豆网网站版权所有

经营许可证编号:鄂ICP备2022015515号-1