实验12 触发器与作业.docx
《实验12 触发器与作业.docx》由会员分享，可在线阅读，更多相关《实验12 触发器与作业.docx（22页珍藏版）》请在冰豆网上搜索。
实验12触发器与作业
实验12 触发器与作业
姓名：
学号：
专业：
班级：
同组人：
无
实验日期：
【实验目的与要求】
⏹理解什么是触发器
⏹掌握触发器的设计与使用
⏹掌握在Oracle中完成定时作务的方法
【实验内容与步骤】
相关说明：
本实验中前面实验创建的触发器可能对后面的实验产生干扰，若出现这种情况，请把前面触发器删除，再完成后面实验。
建议每完成一个实验题后，即将该题相关的触发器删除，以免对后面实验产生干扰。
12.1触发器创建与删除语法
1．触发器的创建
触发器是一种在发生数据库事件时自动运行的PL/SQL
语法如下：
CREATE[ORREPLACE]TIGGER触发器名触发时间触发事件
ON表名
[FOREACHROW]
BEGIN
pl/sql语句
END
2．触发器的删除
DROPTRIGGERtrigger_name
12.2触发器基础
以下程序展示的是触发器的基本使用方法，请阅读并理解以下程序代码，给出运行结果。
1.创建测试表
--droptableemployees;
createtableemployees（
idnumber（5）,
namevarchar2（30）,
salarynumber（8,2）,
job_idvarchar2（20）
）;
2.创建触发器
CREATEORREPLACETRIGGERsecure_emp_1--这里不能有IS
BEFOREINSERTONemployees--这里没有分号
BEGIN
IF（TO_CHAR（SYSDATE,'DY'）IN（'星期六','星期天'）OR（TO_CHAR（SYSDATE,'HH24:
MI'）NOTBETWEEN'08:
00'AND'18:
00'））THEN
RAISE_APPLICATION_ERROR（-20500,'你只能在工作时间对表进行操作'）;
ENDIF;
END;
/
3.测试触发器
（1）--测试语句
insertintoemployeesvalues（1,'a',2222,'aaaaa'）;
给出测试结果截图：
（2）将系统时间修改为周六，再Insert一条记录，给出并比较两次运行的结果。
给出测试结果截图：
12.3使用触发器监控数据更新操作
阅读以下程序，理解程序功能，给出运行测试结果。
1.创建触发器
CREATEORREPLACETRIGGERsecure_emp_2
BEFOREINSERTORUPDATEORDELETEONemployees
BEGIN
--如果当前时间是周六或周日或者时间不在8:
00-18:
00之间
IF（TO_CHAR（SYSDATE,'DY'）IN（'星期五','星期天'）OR（TO_CHAR（SYSDATE,'HH24:
MI'）NOTBETWEEN'08:
00'AND'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;
ENDIF;
END;
/
2.运行测试
分别运行以下测试语句，给出运行结果。
并理解为什么会有这样的结果。
insertintoemployeesvalues（1,'a',2222,'AD_PRES'）;
insertintoemployeesvalues（2,'b',2222,'AD_VP'）;
给出测试结果截图：
deletefromemployees;
给出测试结果截图：
updateemployeessetsalary=3000;
给出测试结果截图：
12.4在insert或update中使用:
new
阅读并理解以下程序，理解其功能，给出运行测试结果。
1.创建触发器
--在insert或update中使用:
new--
CREATEORREPLACETRIGGERrestrict_salary
BEFOREINSERTORUPDATEOFsalaryONemployees
FOREACHROW
BEGIN
--插入和修改可以使用:
new来访问新的数据,修改也可以使用:
old来访问旧的数据
IFNOT（:
NEW.job_idIN（'AD_PRES','AD_VP'））
AND:
NEW.salary>15000
THEN
RAISE_APPLICATION_ERROR（-20202,'员工不能赚到这么多薪水'）;
ENDIF;
END;
2.运行测试
--测试语句
insertintoemployeesvalues（1,'a',20000,'AD_PRES'）;
insertintoemployeesvalues（2,'b',20000,'AD_VPs'）;
updateemployeessetsalary=30000,job_id='xxxx'wherename='a';
给出运行结果：
思考：
观察运行结果，比较insert和update语句执行的差别。
12.5在delete中使用:
old
阅读并理解以下程序，理解其功能，给出运行测试结果。
1.创建触发器
CREATEORREPLACETRIGGERrestrict_salary_del
BEFOREDELETEONemployeesFOREACHROW
BEGIN
--在delete语句中只能使用:
old来访问旧的数据
IF（:
OLD.job_idIN（'AD_PRES','AD_VP'））AND（:
OLD.salary>15000）
THEN
RAISE_APPLICATION_ERROR（-20202,'该员工不可以删除'）;
ENDIF;
END;
2.运行测试
--测试语句
insertintoemployeesvalues（2,'张三',20000,'AD_PRES'）;
deletefromemployeeswherename='张三';
给出运行结果：
12.6INSTEADOF类型的触发器
阅读并理解以下程序，理解其功能，给出运行测试结果。
1.准备工作：
创建触发器及相应测试用表
--INSTEADOF类型的触发器
droptabledepartments;
createtabledepartments（
dept_idnumber（5）primarykey,
dept_namevarchar2（20）
）;
droptableemployees;
createtableemployees（
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
values（:
new.employee_id,:
new.employee_name,:
new.department_id）;
endif;
end;
2.运行测试
（1）查询视图：
select*fromv_emp;
给出运行结果：
（2）插入测试数据
--向视图中插入数据
insertintov_empvalues（171,'张六',1000,'销售部'）;
（3）结果测试
select*fromemployees;
给出运行结果：
select*fromdepartments;
给出运行结果：
12.7系统级触发器
阅读并理解以下程序，理解其功能，给出运行测试结果。
1.创建触发器
------------系统触发器---------------------------------------
--创建登陆或者退出数据库日志表
droptablelogs;
createtablelogs（
useridvarchar2（20）,
timesdate,
opvarchar2（10）–操作类型
）;
----登陆数据库触发器
createorreplacetriggerlog_on
afterlogononschema
begin
insertintologsvalues（user,sysdate,'logon'）;
end;
/
--退出数据库触发器
createorreplacetriggerlog_off
beforelogoffonschema
begin
insertintologsvalues（user,sysdate,'logoff'）;
end;
/
2.运行测试
（1）--以不同的用户身份登录和退出数据库数次，以获得相关数据
如：
connsys/testdbassysdba;
Connsoctt/tiger
（2）--A设置日期的显示格式
altersessionsetnls_date_format='YYYY-MM-DDHH_MI_SS';
（3）--查看记录
select*fromlogs;
给出运行结果：
12.8作业与定时任务
12.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中。
12.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;
ENDIF;
ENDtri_test_id;
/
--创建一个自定义过程
CREATEORREPLACEPROCEDUREproc_test
AS
BEGIN
INSERTINTOtest（cur_user,cur_time）
VALUES（user,to_char（sysdate,'YYYY-MM-DDHH24:
MI:
SS'））;--系统时间转为格式串
END;
/
12.7.3作业的创建与使用
--创建JOB
DECLAREjobNonumber;
BEGIN
dbms_job.submit（
jobNo,--job参数
'proc_test;',--what参数是将被执行的PL/SQL代码块
sysdate,--next_date,参数指识何时将运行这个工作,本例为马上开始
'sysdate+1/（24*60*10）'--interval参数,即1/10分钟运行test过程一次
）;--no_parse参数，无
END;
--next_date,可如：
to_date（'20110517093500','yyyy-mm-ddhh24:
mi:
ss'）,
12.7.4测试
--测试：
1分钟后查看Test表中数据
SELECT*
FROMtest
请给出运行结果：
12.7.5停止与移除作业
若要移除、停止或启用作业，需根据作业号，调用作业包中存储过程dbms_job.remove（jobNo）来完成。
其中，作业号可通过数据字典all_jobs获取。
1.获取作业号
可通过查询数据字典all_jobs来获取作业号，示例如下：
其中，job字段值即为作业号。
2.移除作业
--删除JOB
begin
dbms_job.remove（jobNo）; --jobNo为作业号，使用时应用查询得到的结果
end;
/
3.停止一个JOB
execdbms_job.broken（jobNo,true）--jobNo为作业号，使用时应用查询得到的结果
4.停止一个JOB
execdbms_job.broken（jobNo,false）--jobNo为作业号，使用时应用查询得到的结果
12.9实验练习
1. 实验练习：
利用触发器追踪薪水变动情况
请创建一个名称为change_record的触发器，实现当对emp表更新员工工资之后，将更新纪录保存到表salary_change_record中。
具体要求如下：
建立日志对薪水的变动情况形成一个追踪，也就是说，如果对某个职员的薪水进行变更就应该将其相应的变更记录全部记下来，将数据记录到表salary_change_record（empid,old_salary,new_salary,change_date）中，其中old_salary:
用来纪录员工原来的工资，new_salary：
用来纪录更新后的工资，change_date：
记录更新的系统时间。
请给出相应的代码：
在emp表中添加或修改几行数据，而后查看表salary_change_record中数据。
请给出测试结果：
2．编写一个数据库触发器，当任何时候某个部门从"dept"中删除时，该触发器将从"emp"表中删除该部门的所有雇员记录。
给出相应的代码：
给出测试结果：
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表中数据的最新状态。
请给出程序源码：
createorreplacetriggerup_emp
afterupdateordeleteorinsertonempforeachrow
declare
t_depnonumber;
t_salnumber;
t_empnumber;
t_nonumber;
begin
ifupdatingthen
t_depno:
=:
old.deptno;
selectcount（empno）,sum（sal）
intot_sal,t_emp
fromemp
wheredeptno=t_depno;
updatedept_salsettotal_emp=t_emp,total_sal=t_salwheredeptno=t_depno;
elsifinsertingthen
t_depno:
=:
new.deptno;
selectcount（*）
intot_no
fromemp
wheredeptno=t_depno;
if（t_no）<2then
selectcount（empno）,sum（sal）
intot_sal,t_emp
fromemp
wheredeptno=t_depno;
insertintodept_sal
values（t_depno,t_emp,t_sal）;
else
selectcount（empno）,sum（sal）
intot_sal,t_emp
fromemp
wheredeptno=t_depno;
updatedept_salsettotal_emp=t_emp,total_sal=t_salwheredeptno=t_depno;
endif;
elsifdeletingthen
t_depno:
=:
old.deptno;
selectcount（*）
intot_no
fromemp
wheredeptno=t_depno;
if（t_no）>0then
selectcount（empno）,sum（sal）
intot_sal,t_emp
fromemp
wheredeptno=t_depno;
updatedept_salsettotal_emp=t_emp,total_sal=t_salwheredeptno=t_depno;
else
deletefromdept_salwheredeptno=t_depno;
endif;
endif;
end;
/
（3）测试
按下面步骤完成测试，结出测试结果，并比对测试结果，看是否满足要求。
A．查询dept_sal中数据：
SELECT*FROMdept_sal;
B．--对emp表进行DML操作
INSERTINTOemp（empno,deptno,sal）VALUES（'123','10',10000）;
SELECT*FROMdept_sal;
给出测试结果：
DELETEEMPWHEREempno=123;
SELECT*FROMdept_sal;
给出测试结果：
4．定时任务练习：
假设公司要求DBA每隔5分钟（实际运作时，一般为一个月统计一次，实验中为了测试，将时间变短为5分钟）根据emp表统计一次公司员工工资总和，并记录到表Total_salary_Table（count_time,total_salry）中（表中count_time表示统计时间；total_salry表示当前员工工资总和，可由sum（sal）运算得到）。
请根据以上任务需求写出完成该功能的程序代码，并给出测试实例结果。
（1）请给出源程序：
（2）测试：
执行作业任务后，对emp表的sal字段进行更新（如修改其数据，添加或删除数据行），约5分钟后，对表Total_salary_Table执行查询，并给出查询结果。
Select*
FromTotal_salary_Table;
请给出运行结果：
升级会员 