实验6触发器与作业

1、实验6 触发器与作业姓名：学号：专业：网络工程班级：同组人：无实验日期：2013/11/28【实验目的与要求】n 理解什么是触发器n 掌握触发器的设计与使用n 掌握在Oracle中完成定时作务的方法【实验内容与步骤】相关说明：本实验中前面实验创建的触发器可能对后面的实验产生干扰，若出现这种情况，请把前面触发器删除，再完成后面实验。建议每完成一个实验题后，即将该题相关的触发器删除，以免对后面实验产生干扰。6.0 触发器创建与删除语法1触发器的创建触发器是一种在发生数据库事件时自动运行的PL/SQL语法如下：CREATE OR REPLACE TIGGER触发器名 触发时间 触发事件ON表名FOR

2、 EACH ROWBEGINpl/sql语句END2触发器的删除DROP TRIGGER trigger_name6.1 触发器基础以下程序展示的是触发器的基本使用方法，请阅读并理解以下程序代码，给出运行结果。1.创建测试表-drop table employees;create table employees( id number(5), name varchar2(30), salary number(8,2), job_id varchar2(20);2.创建触发器CREATE OR REPLACE TRIGGER secure_emp_1 -这里不能有IS BEFORE INSERT

3、ON employees - 这里没有分号BEGIN IF (TO_CHAR (SYSDATE,'DY') IN ('星期六','星期天') OR (TO_CHAR(SYSDATE,'HH24:MI') NOT BETWEEN '08:00' AND '18:00' ) THEN RAISE_APPLICATION_ERROR(-20500,'你只能在工作时间对表进行操作'); END IF; END;/3.测试触发器（1）-测试语句insert into employees val

4、ues (1,'a',2222,'aaaaa');给出测试结果截图：（2）将系统时间修改为周六，再Insert一条记录，给出并比较两次运行的结果。给出测试结果截图：6.2 使用触发器监控数据更新操作阅读以下程序，理解程序功能，给出运行测试结果。1.创建触发器CREATE OR REPLACE TRIGGER secure_emp_2 BEFORE INSERT OR UPDATE OR DELETE ON employeesBEGIN -如果当前时间是周六或周日 或者时间不在8:00-18:00之间 IF (TO_CHAR (SYSDATE,'DY

5、9;) IN ('星期五','星期天') OR (TO_CHAR(SYSDATE,'HH24:MI') NOT BETWEEN '08:00' AND '18:00' ) THEN IF DELETING THEN RAISE_APPLICATION_ERROR (-20502,'你只能在工作时间删除员工表的数据'); ELSIF INSERTING THEN RAISE_APPLICATION_ERROR (-20500,'你只能在工作时间插入员工表的数据.'); ELSIF UP

6、DATING ('SALARY') THEN RAISE_APPLICATION_ERROR (-20503,'你只能在工作时间更新员工表的数据'); ELSE RAISE_APPLICATION_ERROR (-20504,'你只能在工作事件操作员工表的数据.'); END IF; END IF;END;/2.运行测试分别运行以下测试语句，给出运行结果。并理解为什么会有这样的结果。insert into employees values (1,'a',2222,'AD_PRES');insert into emp

7、loyees values (2,'b',2222,'AD_VP');给出测试结果截图：delete from employees;给出测试结果截图：update employees set salary=3000;给出测试结果截图：6.3 在insert或update中使用:new阅读并理解以下程序，理解其功能，给出运行测试结果。1.创建触发器-在insert或update中使用:new-CREATE OR REPLACE TRIGGER restrict_salary BEFORE INSERT OR UPDATE OF salary ON employee

8、s FOR EACH ROW BEGIN - 插入和修改可以使用:new来访问新的数据,修改也可以使用:old来访问旧的数据 IF NOT (:NEW.job_id IN ('AD_PRES', 'AD_VP') AND :NEW.salary > 15000 THEN RAISE_APPLICATION_ERROR (-20202,'员工不能赚到这么多薪水'); END IF;END;2.运行测试-测试语句insert into employees values (1,'a',20000,'AD_PRES'

9、);insert into employees values (2,'b',20000,'AD_VPs');update employees set salary=30000 ,job_id='xxxx' where name='a'给出运行结果：思考：观察运行结果，比较insert和update语句执行的差别。6.4 在delete中使用:old阅读并理解以下程序，理解其功能，给出运行测试结果。1.创建触发器CREATE OR REPLACE TRIGGER restrict_salary_del BEFORE DELETE O

10、N employees FOR EACH ROW BEGIN - 在delete语句中只能使用:old来访问旧的数据 IF (:OLD.job_id IN ('AD_PRES', 'AD_VP') AND (:OLD.salary > 15000) THEN RAISE_APPLICATION_ERROR (-20202,'该员工不可以删除'); END IF;END;2.运行测试-测试语句insert into employees values (2,'张三',20000,'AD_PRES');delete

11、 from employees where name='张三'给出运行结果：6.5 INSTEAD OF类型的触发器阅读并理解以下程序，理解其功能，给出运行测试结果。1.准备工作：创建触发器及相应测试用表-INSTEAD OF类型的触发器drop table departments;create table departments( dept_id number(5) primary key, dept_name varchar2(20);drop table employees;create table employees( employee_id number(5), em

12、ployee_name varchar2(20), department_id number(5), constraint empl_dept_fk foreign key (department_id) references departments(dept_id);-创建视图create or replace view v_empasselect e.employee_id,e.employee_name,e.department_id,d.dept_namefrom employees e,departments dwhere e.department_id = d.dept_id-创建

13、触发器create or replace trigger new_view_emp_dept instead of insert on v_empl for each rowbegin if inserting then insert into departments values(:new.department_id,:new.dept_name); insert into employees values(:new.employee_id,:new.employee_name,:new.department_id); end if;end;2.运行测试（1）查询视图：select * fr

14、om v_emp;给出运行结果：（2）插入测试数据-向视图中插入数据insert into v_emp values (171,'张六',1000,'销售部');（3）结果测试select * from employees;给出运行结果：select * from departments;给出运行结果：6.6 系统级触发器阅读并理解以下程序，理解其功能，给出运行测试结果。1.创建触发器-系统触发器-创建登陆或者退出数据库日志表drop table logs;create table logs(userid varchar2(20),times date,op v

15、archar2(10) );-登陆数据库触发器create or replace trigger log_onafter logon on schemabegin insert into logs values(user,sysdate,'logon');end;/-退出数据库触发器create or replace trigger log_offbefore logoff on schemabegin insert into logs values(user,sysdate,'logoff');end;/2.运行测试(1)-以不同的用户身份登录和退出数据库数次

16、，以获得相关数据如：conn sys/testdb as sysdba; Conn soctt/tiger (2)- A设置日期的显示格式alter session set nls_date_format='YYYY-MM-DD HH_MI_SS'(3)- 查看记录select * from logs;给出运行结果：6.7 作业与定时任务6.7.1 作业概述作业是Oracle中一个程序包，可用于Oracle中实现定时任务。日常开发最常用到的用于完成定时任务的是Job中包中的submit函数，其接口如下：1. dbms_job.submit( job out binary_int

17、eger,   2. whatinarchar2,   3. next_date indate，   4. intervalinvarchar2,   5. no_parseinboolean)   其中： job：输出变量，是此任务在任务队列中的编号； what：执行的任务的名称及其输入参数； next_date：任务执行的时间； interval：任务执行的时间间隔。 其中Interval这个值是决定Job何时，被重新执行的关键；当interval设置为null时，该job执行结束后，就被从

18、队列中删除。假如我们需要该job周期性地执行，则要用sysdatem表示。以下实验中创建一个名称为tri_test_id的触发器，用于实现当在作业中定时调用存储过程，向Test表插入数据时，自动的触发序列号的产生，并添加数据到Test表中。更新员工工资之后，将更新纪录保存到表salary_change_record中。6.7.2 实验准备-(1)创建测试表:CREATE TABLE Test(id number,cur_user varchar2(20),cur_time varchar2(30); -(2)创建序列 test_sequence: CREATE SEQUENCE test_se

19、quence INCREMENT BY 1-每次加几个 START WITH 1 -从1开始计数 NOMAXVALUE -不设置最大值 NOCYCLE -一直累加，不循环 CACHE10; -建触发器tri_test_id： CREATE OR REPLACE TRIGGER tri_test_id before INSERT ON test -test 是表名 FOR each row DECLARE nextid number; BEGIN IF :new.id IS NULL or :new.id=0 THEN -id是列名 SELECT test_sequence.nextval -

20、test_sequence正是刚才创建的序列 INTO nextid FROM sys.dual; :new.id:=nextid; END IF; END tri_test_id; / -创建一个自定义过程 CREATE OR REPLACE PROCEDURE proc_test AS BEGIN INSERT INTO test(cur_user,cur_time) VALUES(user,to_char(sysdate,'YYYY-MM-DD HH24:MI:SS'); -系统时间转为格式串 END; / 6.7.3 作业的创建与使用-创建JOBDECLARE jobN

21、o number; 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-dd hh24:mi:ss'),6.7.

22、4 测试-测试： 1分钟后查看Test表中数据SELECT *FROM test请给出运行结果：6.7.5 停止与移除作业若要移除、停止或启用作业，需根据作业号，调用作业包中存储过程dbms_job.remove(jobNo)来完成。其中，作业号可通过数据字典all_jobs获取。1.获取作业号可通过查询数据字典all_jobs来获取作业号，示例如下：其中，job字段值即为作业号。2.移除作业-删除JOBbegindbms_job.remove(jobNo);-jobNo为作业号，使用时应用查询得到的结果end;/3. 停止一个JOBexec dbms_job.broken(jobNo,tru

23、e) -jobNo为作业号，使用时应用查询得到的结果4. 停止一个JOBexec dbms_job.broken(jobNo,false) -jobNo为作业号，使用时应用查询得到的结果第 15 页 共 15 页6.8 实验练习1.实验练习：利用触发器追踪薪水变动情况请创建一个名称为 change_record的触发器，实现当对emp表更新员工工资之后，将更新纪录保存到表salary_change_record中。具体要求如下：建立日志对薪水的变动情况形成一个追踪，也就是说，如果对某个职员的薪水进行变更就应该将其相应的变更记录全部记下来，将数据记录到表salary_change_record（

24、empid,old_salary,new_salary,change_date）中，其中old_salary:用来纪录员工原来的工资，new_salary：用来纪录更新后的工资，change_date：记录更新的系统时间。请给出相应的代码：create table salary_change_record( empid number(4), old_salary number(7,2), new_salary number(7,2), change_date date );create or replace trigger change_recordafter update on emp fo

25、r each rowbegin if updating then insert into salary_change_record values(:new.empno,:old.sal,:new.sal,sysdate ); end if ;end;/在emp表中添加或修改几行数据，而后查看表salary_change_record中数据。请给出测试结果：2编写一个数据库触发器，当任何时候某个部门从"dept"表中删除时，该触发器将从"emp"表中删除该部门的所有雇员记录。给出相应的代码：create or replace trigger del_emp

26、_deptnobefore delete on dept for each rowbegin delete from emp where deptno=:old.deptno;end;/给出测试结果：3. 映射emp表中每个部门的总人数和总工资（1）-创建映射表CREATE TABLE dept_sal ASSELECT deptno,COUNT(empno) AS total_emp,SUM(sal) AS total_salFROM empGROUP BY deptno;DESC dept_sal;（2）-创建触发器，映射emp表中每个部门的总人数和总工资创建一触发器，当emp表中数据改变

27、时，自动修改dept_sal表中数据，使其反映出Emp表中数据的最新状态。请给出程序源码：CREATE OR REPLACE TRIGGER emp_infoAFTER INSERT OR UPDATE OR DELETE ON emp DECLARE CURSOR cur_emp ISSELECT deptno,COUNT(empno) AS total_emp,SUM(sal) AS total_sal FROM empGROUP BY deptno;BEGIN DELETE dept_sal; FOR v_emp IN cur_emp LOOPDBMS_OUTPUT.PUT_LINE(v

28、_emp.deptno | v_emp.total_emp| v_emp.total_sal);INSERT INTO dept_sal VALUES(v_emp.deptno,v_emp.total_emp,v_emp.total_sal);END LOOP;END;/select * from dept_sal;INSERT INTO emp(empno,deptno,sal) VALUES('123','20',10000);SELECT * FROM dept_sal;DELETE EMP WHERE empno=123; SELECT * FROM d

29、ept_sal;（3）测试按下面步骤完成测试，结出测试结果，并比对测试结果，看是否满足要求。A查询dept_sal中数据：SELECT * FROM dept_sal;给出结果：B-对emp表进行DML操作INSERT INTO emp(empno,deptno,sal) VALUES('123','10',10000);SELECT * FROM dept_sal;给出测试结果：DELETE EMP WHERE empno=123; SELECT * FROM dept_sal;给出测试结果：4. 监控用户对表的更新操作。已知employees_copy表中一个非常重要的表，当用户其中的数据作修改更新等操作时，需记录操作用户、时间和所做的操作(即是INSERT/UPDATE/DELETE中的哪一个触发了触发器)于日志表中employees_log中，请按以下过程完成指定业务。(1) 建立试验表create table employees_copy as select *from hr.employees(2)