实验六 PL-SQL触发器和包

实验六 PL/SQL触发器和包

一、实验目的

学习并熟悉过程、触发器的应用，理解包的创建，掌握PL/SQL中的测试方法。

二、实验要求

用ORACLE SQL*PLUS完成实验内容。具体要求：

1、使用save语句保存所写的所有PL/SQL程序，实验结束时，上传到FTP。

文件名为“学号+姓名_实验六.sql”;

2、使用spool语句，保存所有操作及结果，实验结束时，上传到FTP。

文件名为“学号+姓名_实验六.txt”;

三、预备知识

案例

数据库有两张表：emp表和dept表，两张表的结构如下：

emp (emp_id number(5), emp_name varchar2(20), emp_salary number(4))；

dept (dept_id number(3), dept_name varchar2(20), emp_id number(5));

要求如下：

1、按照上表结构建立相应的表，为每张表写入5组合法数据。

2、操纵相关表，使得“e”的员工的薪水上涨20%。

3、建立日志，追踪薪水变动情况。

4、建立测试包。

案例的分析与实现

要求1：首先根据前面表的结构可以创建两张表：

——创建员工表

create table emp (emp_id number(5), emp_name varchar2(20), emp_salary number(4));

——部门表

create table dept (dept_id number(3), dept_name varchar2(20), emp_id number(5));

建立了表之后就可以往表里面写数据了，这里把添加表记录的代码写入到相应的存储过程。/*给emp表添加记录的存储过程*/

create or replace procedure ins_table_emp(p_emp_id number,p_emp_name varchar2,emp_salary number) is

begin

insert into emp values(p_emp_id,p_emp_name,emp_salary);

end ins_table_emp;

/

/*给dept表添加记录的存储过程*/

create or replace procedure ins_table_dept(p_dept_id number,p_dept_name varchar2,p_emp_id number) is

begin

insert into dept values(p_dept_id,p_dept_name,p_emp_id);

end ins_table_dept;

/

/*调用相应的存储过程实现记录添加*/

begin

ins_table_emp(10000,'A',4000);

ins_table_emp(10001,'B',2300);

ins_table_emp(10002,'C',3500);

ins_table_emp(10003,'D',3500);

ins_table_emp(10004,'E',3500);

ins_table_dept(111,'a',10000);

ins_table_dept(111,'b',10001);

ins_table_dept(111,'c',10002);

ins_table_dept(112,'d',10003);

ins_table_dept(113,'e',10004);

end;

/

要求2：

给指定部门的员工加薪，这实际上是一个复合查询，首先需要把所有该部门的员工选出来，然后对这些员工的薪水进行相应的改动。代码如下：

（需要注意的是：将要加薪的部门作为参数，这样的存储过程更有灵活性。）

create or replace procedure add_salary(p_dept_id number) is

begin

update emp set emp.EMP_SALARY=emp.EMP_SALARY*1.2

where emp.EMP_ID in (select emp.EMP_ID from emp,dept where emp.EMP_ID=dept.EMP_ID and dept.DEPT_ID=p_dept_id);

end add_salary;

/

执行：

begin

add_salary(113);

end;

/

要求3：

建立日志对薪水的变动情况形成一个追踪，也就是说，如果对某个职员的薪水进行变更就应该将其相应的变更记录全部记下来。如果对emp表的salary字段创建一个触发器，来监视对salary的更改，把每次更改进行记录，这样就达到了要求3的目的了。

创建触发器：

create or replace trigger print_salary_change

before delete or insert or update on hr.emp——触发事件

for each row——每修改一行都需要调用此过程

declare——只有触发器的声明需要declare，过程和函数都不需要

salary_balance number;

begin

——（:new）与（:old）分别代表该行在修改前和修改后的记录

salary_balance:=(:new.emp_salary)-(:old.emp_salary);

dbms_output.PUT_LINE('old salary is: '|| :old.emp_salary);

dbms_output.PUT_LINE('new salary is: '|| :new.emp_salary);

dbms_output.PUT_LINE('balance salary is: '|| to_char(salary_balance));

end print_salary_change;

/

打开输出服务：

set serveroutput on

尝试写一条更新数据的语句：

update emp set emp_salary=5000 where emp_id=10003;

要求4：与其他语言（c/c++等）相比，PL/SQL的测试有其不同之处，归纳下来有三种方法：

1、使用DBMS_OUTPUT包的PUT_LINE方法来显示中间变量，以此来观察程序是否存在逻辑错误。

2、插入测试表的方法。即创建一个临时的中间表，然后把所有涉及到的中间变量的结果都作为记录插入到中间表中，这样可以查询表中的结果来观察程序的执行情况。

3、使用异常处理手段，对可疑的程序段使用begin …end ，然后可以在exception里进行异常捕获处理。

这里使用第二种方法来建立一个测试包，PL/SQL里包的概念类似于面向对象里的类的概念，包将一组操作和属性封装在一起，不仅增强了程序的模块化，而且由于封装了更多的操作和属性而提高了执行效能。建立一个PL/SQL需要两个步骤：首先要建立包头，类似于建立一个类的头文件，里面主要对包中的过程，函数和变量的声明；第二部分主要是包体部分，实现前面声明的过程和函数，另外还需要对包进行初始化等工作。

根据这一思路，建立测试包如下：

/*包头部分*/

create or replace package debug as

procedure debug(v_description varchar2,v_valueOfvariable varchar2)

procedure reset;

v_numberOfLine number;

end debug;

/*包体部分*/

create or replace package body debug as

procedure debug(v_description varchar2,v_valueOfvariable varchar2) is

begin

insert into debugtable

values(v_numberOfLine,v_description,v_valueOfvariable);

v_numberOfLine:=v_numberOfLine+1;

end debug;

procedure reset is

begin

v_numberOfLine:=1;

delete from debugtable;

end reset;

/*初始化部分*/

begin

reset;

end debug;

四、实验内容

1、创建一个过程，打印出EMP表中各个工资级别的人数。备注：显示的格式为三种情况1、工资少于2000的人数为：****；工资在2000——3000的人数为****;工资大于3000的人数为:****

2、创建一个表salary_change_record（empid,old_salary,new_salary,change_date）,old_salary:用来纪录员工原来的工资，new_salary：用来纪录更新后的工资，change_date：记录更新的系统时间。然后创建一个触发器，名称为change_record，功能：每次更新员工工资之后，将更新纪录保存到表salary_change_record中。