实验6触发器与作业Word格式文档下载.docx
《实验6触发器与作业Word格式文档下载.docx》由会员分享，可在线阅读，更多相关《实验6触发器与作业Word格式文档下载.docx（18页珍藏版）》请在冰豆网上搜索。
CREATEORREPLACETRIGGERsecure_emp_1--这里不能有IS
BEFOREINSERTONemployees--这里没有分号
IF（TO_CHAR（SYSDATE,'
DY'
）IN（'
星期六'
'
星期天'
）OR（TO_CHAR（SYSDATE,'
HH24:
MI'
）NOTBETWEEN'
08:
00'
AND'
18:
））THEN
RAISE_APPLICATION_ERROR（-20500,'
你只能在工作时间对表进行操作'
ENDIF;
END;
/
3.测试触发器
（1）--测试语句
insertintoemployeesvalues（1,'
a'
2222,'
aaaaa'
给出测试结果截图：
（2）将系统时间修改为周六，再Insert一条记录，给出并比较两次运行的结果。
6.3使用触发器监控数据更新操作
阅读以下程序，理解程序功能，给出运行测试结果。
1.创建触发器
CREATEORREPLACETRIGGERsecure_emp_2
BEFOREINSERTORUPDATEORDELETEONemployees
--如果当前时间是周六或周日或者时间不在8:
00-18:
00之间
星期五'
））
THEN
IFDELETINGTHEN
RAISE_APPLICATION_ERROR（-20502,'
你只能在工作时间删除员工表的数据'
ELSIFINSERTINGTHEN
RAISE_APPLICATION_ERROR（-20500,'
你只能在工作时间插入员工表的数据.'
ELSIFUPDATING（'
SALARY'
）THEN
RAISE_APPLICATION_ERROR（-20503,'
你只能在工作时间更新员工表的数据'
ELSE
RAISE_APPLICATION_ERROR（-20504,'
你只能在工作事件操作员工表的数据.'
ENDIF;
2.运行测试
分别运行以下测试语句，给出运行结果。
并理解为什么会有这样的结果。
AD_PRES'
insertintoemployeesvalues（2,'
b'
AD_VP'
deletefromemployees;
updateemployeessetsalary=3000;
6.4在insert或update中使用:
new
阅读并理解以下程序，理解其功能，给出运行测试结果。
--在insert或update中使用:
new--
CREATEORREPLACETRIGGERrestrict_salary
BEFOREINSERTORUPDATEOFsalaryONemployees
FOREACHROW
BEGIN
--插入和修改可以使用:
new来访问新的数据,修改也可以使用:
old来访问旧的数据
IFNOT（:
NEW.job_idIN（'
'
））
AND:
NEW.salary>
15000
THEN
RAISE_APPLICATION_ERROR（-20202,'
员工不能赚到这么多薪水'
--测试语句
20000,'
AD_VPs'
updateemployeessetsalary=30000,job_id='
xxxx'
wherename='
;
给出运行结果：
思考：
观察运行结果，比较insert和update语句执行的差别。
6.5在delete中使用:
old
CREATEORREPLACETRIGGERrestrict_salary_del
BEFOREDELETEONemployeesFOREACHROW
--在delete语句中只能使用:
IF（:
OLD.job_idIN（'
））AND（:
OLD.salary>
15000）
该员工不可以删除'
张三'
deletefromemployeeswherename='
6.6INSTEADOF类型的触发器
1.准备工作：
创建触发器及相应测试用表
--INSTEADOF类型的触发器
droptabledepartments;
createtabledepartments（
dept_idnumber（5）primarykey,
dept_namevarchar2（20）
droptableemployees;
employee_idnumber（5）,
employee_namevarchar2（20）,
department_idnumber（5）,
constraintempl_dept_fkforeignkey（department_id）referencesdepartments（dept_id）
--创建视图
createorreplaceviewv_emp
as
selecte.employee_id,e.employee_name,e.department_id,d.dept_name
fromemployeese,departmentsd
wheree.department_id=d.dept_id
--创建触发器
createorreplacetriggernew_view_emp_dept
insteadofinsertonv_emplforeachrow
begin
ifinsertingthen
insertintodepartments
values（:
new.department_id,:
new.dept_name）;
insertintoemployees
new.employee_id,:
new.employee_name,:
new.department_id）;
endif;
end;
（1）查询视图：
select*fromv_emp;
（2）插入测试数据
--向视图中插入数据
insertintov_empvalues（171,'
张六'
1000,'
销售部'
（3）结果测试
select*fromemployees;
select*fromdepartments;
6.7系统级触发器
------------系统触发器---------------------------------------
--创建登陆或者退出数据库日志表
droptablelogs;
createtablelogs（
useridvarchar2（20）,
timesdate,
opvarchar2（10）
----登陆数据库触发器
createorreplacetriggerlog_on
afterlogononschema
insertintologsvalues（user,sysdate,'
logon'
--退出数据库触发器
createorreplacetriggerlog_off
beforelogoffonschema
logoff'
（1）--以不同的用户身份登录和退出数据库数次，以获得相关数据
如：
connsys/testdbassysdba;
Connsoctt/tiger
（2）--A设置日期的显示格式
altersessionsetnls_date_format='
YYYY-MM-DDHH_MI_SS'
（3）--查看记录
select*fromlogs;
6.8作业与定时任务
6.7.1作业概述
作业是Oracle中一个程序包，可用于Oracle中实现定时任务。
日常开发最常用到的用于完成定时任务的是Job中包中的submit函数，其接口如下：
1.dbms_job.submit（joboutbinary_integer,
2.what in archar2,
3.next_date in date，
4.interval in varchar2,
5.no_parse in boolean）
其中：
●job：
输出变量，是此任务在任务队列中的编号；
●what：
执行的任务的名称及其输入参数；
●next_date：
任务执行的时间；
●interval：
任务执行的时间间隔。
其中Interval这个值是决定Job何时，被重新执行的关键；
当interval设置为null时，该job执行结束后，就被从队列中删除。
假如我们需要该job周期性地执行，则要用‘sysdate＋m’表示。
以下实验中创建一个名称为tri_test_id的触发器，用于实现当在作业中定时调用存储过程，向Test表插入数据时，自动的触发序列号的产生，并添加数据到Test表中。
更新员工工资之后，将更新纪录保存到表salary_change_record中。
6.7.2实验准备
--
（1）创建测试表:
CREATETABLETest（idnumber,cur_uservarchar2（20）,cur_timevarchar2（30））;
--
（2）创建序列test_sequence:
CREATESEQUENCEtest_sequence
INCREMENTBY1--每次加几个
STARTWITH1--从1开始计数
NOMAXVALUE--不设置最大值
NOCYCLE--一直累加，不循环
CACHE10;
--建触发器tri_test_id：
CREATEORREPLACETRIGGERtri_test_id
beforeINSERT
ONtest--test是表名
FOReachrow
DECLARE
nextidnumber;
BEGIN
IF:
new.idISNULLor:
new.id=0THEN--id是列名
SELECTtest_sequence.nextval--test_sequence正是刚才创建的序列
INTOnextid
FROMsys.dual;
:
new.id:
=nextid;
ENDtri_test_id;
/
--创建一个自定义过程
CREATEORREPLACEPROCEDUREproc_test
AS
BEGIN
INSERTINTOtest（cur_user,cur_time）
VALUES（user,to_char（sysdate,'
YYYY-MM-DDHH24:
MI:
SS'
））;
--系统时间转为格式串
END;
/
6.7.3作业的创建与使用
--创建JOB
DECLAREjobNonumber;
dbms_job.submit（
jobNo,--job参数
'
proc_test;
'
--what参数是将被执行的PL/SQL代码块
sysdate,--next_date,参数指识何时将运行这个工作,本例为马上开始
sysdate+1/（24*60*10）'
--interval参数,即1/10分钟运行test过程一次
）;
--no_parse参数，无
--next_date,可如：
to_date（'
20110517093500'
yyyy-mm-ddhh24:
mi:
ss'
）,
6.7.4测试
--测试：
1分钟后查看Test表中数据
SELECT*
FROMtest
请给出运行结果：
6.7.5停止与移除作业
若要移除、停止或启用作业，需根据作业号，调用作业包中存储过程dbms_job.remove（jobNo）来完成。
其中，作业号可通过数据字典all_jobs获取。
1.获取作业号
可通过查询数据字典all_jobs来获取作业号，示例如下：
其中，job字段值即为作业号。
2.移除作业
--删除JOB
dbms_job.remove（jobNo）;
--jobNo为作业号，使用时应用查询得到的结果
3.停止一个JOB
execdbms_job.broken（jobNo,true）--jobNo为作业号，使用时应用查询得到的结果
4.停止一个JOB
execdbms_job.broken（jobNo,false）--jobNo为作业号，使用时应用查询得到的结果
6.9实验练习
1. 实验练习：
利用触发器追踪薪水变动情况
请创建一个名称为change_record的触发器，实现当对emp表更新员工工资之后，将更新纪录保存到表salary_change_record中。
具体要求如下：
建立日志对薪水的变动情况形成一个追踪，也就是说，如果对某个职员的薪水进行变更就应该将其相应的变更记录全部记下来，将数据记录到表salary_change_record（empid,old_salary,new_salary,change_date）中，其中old_salary:
用来纪录员工原来的工资，new_salary：
用来纪录更新后的工资，change_date：
记录更新的系统时间。
请给出相应的代码：
createtablesalary_change_record
（
empidnumber（4）,
old_salarynumber（7,2）,
new_salarynumber（7,2）,
change_datedate
createorreplacetriggerchange_record
afterupdateonempforeachrow
ifupdatingthen
insertintosalary_change_record
values（:
new.empno,:
old.sal,:
new.sal,sysdate
endif;
在emp表中添加或修改几行数据，而后查看表salary_change_record中数据。
请给出测试结果：
2．编写一个数据库触发器，当任何时候某个部门从"
dept"
表中删除时，该触发器将从"
emp"
表中删除该部门的所有雇员记录。
给出相应的代码：
createorreplacetriggerdel_emp_deptno
beforedeleteondeptforeachrow
deletefromempwheredeptno=:
old.deptno;
给出测试结果：
3.映射emp表中每个部门的总人数和总工资
（1）--创建映射表
CREATETABLEdept_sal
AS
SELECTdeptno,COUNT（empno）AStotal_emp,SUM（sal）AStotal_sal
FROMemp
GROUPBYdeptno;
DESCdept_sal;
（2）--创建触发器，映射emp表中每个部门的总人数和总工资
创建一触发器，当emp表中数据改变时，自动修改dept_sal表中数据，使其反映出Emp表中数据的最新状态。
请给出程序源码：
CREATEORREPLACETRIGGERemp_info
AFTERINSERTORUPDATEORDELETEONempDECLARECURSORcur_empIS
SELECTdeptno,COUNT（empno）AStotal_emp,SUM（sal）AStotal_salFROMemp
BEGINDELETEdept_sal;
FORv_empINcur_empLOOP
DBMS_OUTPUT.PUT_LINE（v_emp.deptno||v_emp.total_emp
||v_emp.total_sal）;
INSERTINTOdept_salVALUES（v_emp.deptno,v_emp.total_emp,
v_emp.total_sal）;
ENDLOOP;
select*fromdept_sal;
INSERTINTOemp（empno,deptno,sal）VALUES（'
123'
20'
10000）;
SELECT*FROMdept_sal;
DELETEEMPWHEREempno=123;
（3）测试
按下面步骤完成测试，结出测试结果，并比对测试结果，看是否满足要求。
A．查询dept_sal中数据：
给出结果：
B．--对emp表进行DML操作
10'
4.监控用户对表的更新操作。
已知employees_copy表中一个非常重要的表，当用户其中的数据作修改更新等操作时，需记录操作用户、时间和所做的操作（即是INSERT/UPDATE/DELETE中的哪一个触发了触发器）于日志表中employees_log中，请按以下过程完成指定业务。
（1）建立试验表
createtableemployees_copy
as
select*fromhr.employees
（2）建立日志表
createtableemployees_log（
whovarchar2（30）,
whendate,
op_typeVARCHAR2（15））;
（3）在employees_copy表上建立语句触发器，当对该表进行insert或update或delete操作时，将用户名称、操作时间和操作类型记录到表employees_log中。
请给出源程序：
createorreplacetriggeremptrigger
beforeinsertorupdateordeleteonemployees_copyforeachrow
declare
operationemployees_log.op_type%type;
operation:
='
insert'
elsifupdatingthen
update'
elsifdeletingthen
delete'
insertintoemployees_log
values（user,sysdate,operation）;
升级会员 