实验6 触发器与作业..

实验6触发器与作业

姓名：学号：

专业：网络工程班级：

同组人：无实验日期：2013/11/28

【实验目的与要求】

?理解什么是触发器

?掌握触发器的设计与使用

?掌握在Oracle中完成定时作务的方法

【实验内容与步骤】

相关说明：本实验中前面实验创建的触发器可能对后面的实验产生干扰，若出现这种情况，请把前面触发器删除，再完成后面实验。建议每完成一个实验题后，即将该题相关的触发器删除，以免对后面实验产生干扰。

6.0触发器创建与删除语法

1．触发器的创建

触发器是一种在发生数据库事件时自动运行的PL/SQL

语法如下：

CREATE [OR REPLACE] TIGGER触发器名触发时间触发事件

ON表名

[FOR EACH ROW]

BEGIN

pl/sql语句

END

2．触发器的删除

DROP TRIGGER trigger_name

6.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 ON employees -- 这里没有分号

BEGIN

IF (TO_CHAR (SYSDA TE,'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 values (1,'a',2222,'aaaaa');

给出测试结果截图：

（2）将系统时间修改为周六，再Insert一条记录，给出并比较两次运行的结果。

给出测试结果截图：

6.2使用触发器监控数据更新操作

阅读以下程序，理解程序功能，给出运行测试结果。

1.创建触发器

CREATE OR REPLACE TRIGGER secure_emp_2

BEFORE INSERT OR UPDATE OR DELETE ON employees

BEGIN

--如果当前时间是周六或周日或者时间不在8:00-18:00之间

IF (TO_CHAR (SYSDA TE,'DY') 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 UPDA TING ('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 employees 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 employees

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');

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 ON 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 from employees where name='张三';

给出运行结果：

6.5INSTEAD 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),

employee_name varchar2(20),

department_id number(5),

constraint empl_dept_fk foreign key (department_id) references departments(dept_id) );

--创建视图

create or replace view v_emp

as

select e.employee_id,e.employee_name,e.department_id,d.dept_name

from employees e,departments d

where e.department_id = d.dept_id

--创建触发器

create or replace trigger new_view_emp_dept

instead of insert on v_empl for each row

begin

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 * from 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 varchar2(10)

);

----登陆数据库触发器

create or replace trigger log_on

after logon on schema

begin

insert into logs values(user,sysdate,'logon');

end;

/

--退出数据库触发器

create or replace trigger log_off

before logoff on schema

begin

insert into logs values(user,sysdate,'logoff');

end;

/

2.运行测试

(1)--以不同的用户身份登录和退出数据库数次，以获得相关数据

如：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_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)创建测试表:

CREATE TABLE Test(id number,cur_user varchar2(20),cur_time varchar2(30));

--(2)创建序列test_sequence:

CREATE SEQUENCE test_sequence

INCREMENT BY 1 --每次加几个

START WITH 1 --从1开始计数

NOMAXV ALUE --不设置最大值

NOCYCLE --一直累加，不循环

CACHE 10;

--建触发器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 -- 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)

V ALUES(user,to_char(sysdate,'YYYY-MM-DD HH24:MI:SS')); --系统时间转为格式串END;

/

6.7.3作业的创建与使用

--创建JOB

DECLARE jobNo 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.4测试

--测试：1分钟后查看Test表中数据

SELECT *

FROM test

请给出运行结果：

6.7.5停止与移除作业

若要移除、停止或启用作业，需根据作业号，调用作业包中存储过程dbms_job.remove(jobNo)来完成。其中，作业号可通过数据字典all_jobs获取。

1.获取作业号

可通过查询数据字典all_jobs来获取作业号，示例如下：

其中，job字段值即为作业号。

2.移除作业

--删除JOB

begin

dbms_job.remove(jobNo);--jobNo为作业号，使用时应用查询得到的结果end;

/

3.停止一个JOB

exec dbms_job.broken(jobNo,true) --jobNo为作业号，使用时应用查询得到的结果

4.停止一个JOB

exec dbms_job.broken(jobNo,false) --jobNo为作业号，使用时应用查询得到的结果

6.8实验练习

1.实验练习：利用触发器追踪薪水变动情况

请创建一个名称为change_record的触发器，实现当对emp表更新员工工资之后，将更新纪录保存到表salary_change_record中。

具体要求如下：

建立日志对薪水的变动情况形成一个追踪，也就是说，如果对某个职员的薪水进行变更就应该将其相应的变更记录全部记下来，将数据记录到表salary_change_record （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_record

after update on emp for each row

begin

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_deptno

before delete on dept for each row

begin

delete from emp where deptno=:old.deptno;

end;

/

给出测试结果：

3. 映射emp表中每个部门的总人数和总工资

（1）--创建映射表

CREATE TABLE dept_sal

AS

SELECT deptno,COUNT(empno) AS total_emp,SUM(sal) AS total_sal

FROM emp

GROUP BY deptno;

DESC dept_sal;

（2）--创建触发器，映射emp表中每个部门的总人数和总工资

创建一触发器，当emp表中数据改变时，自动修改dept_sal表中数据，使其反映出Emp表中数据的最新状态。

请给出程序源码：

CREATE OR REPLACE TRIGGER emp_info

AFTER INSERT OR UPDATE OR DELETE ON emp DECLARE CURSOR cur_emp IS

SELECT deptno,COUNT(empno) AS total_emp,SUM(sal) AS total_sal FROM emp

GROUP BY deptno;

BEGIN DELETE dept_sal;

FOR v_emp IN cur_emp LOOP

DBMS_OUTPUT.PUT_LINE(v_emp.deptno || v_emp.total_emp

|| v_emp.total_sal);

INSERT INTO dept_sal V ALUES(v_emp.deptno,v_emp.total_emp,

v_emp.total_sal);

END LOOP;

END;

/

select * from dept_sal;

INSERT INTO emp(empno,deptno,sal) V ALUES('123','20',10000);

SELECT * FROM dept_sal;

DELETE EMP WHERE empno=123;

SELECT * FROM dept_sal;

（3）测试

按下面步骤完成测试，结出测试结果，并比对测试结果，看是否满足要求。

A．查询dept_sal中数据：

SELECT * FROM dept_sal;

给出结果：

B．--对emp表进行DML操作

INSERT INTO emp(empno,deptno,sal) V ALUES('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) 建立日志表

create table employees_log(

who varchar2(30),

when date,

op_type V ARCHAR2(15));

(3) 在employees_copy表上建立语句触发器，当对该表进行insert或update或delete操作时，将用户名称、操作时间和操作类型记录到表employees_log 中。

请给出源程序：

create or replace trigger emptrigger

before insert or update or delete on employees_copy for each row

declare

operation employees_log.op_type%type;

begin

if inserting then

operation := 'insert';

elsif updating then

operation := 'update';

elsif deleting then

operation := 'delete';

end if;

insert into employees_log

values(user,sysdate,operation);

end;

(4) 测试：请添加几条数据到employees_copy表中，并试着删除、修改基中的几条记录，

完成后查询employees_log表中数据。

请给出测试结果：

5．定时任务练习：

假设公司要求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 *

From Total_salary _Table;

请给出运行结果：