实验7-触发器

大型数据库技术实验报告实验课程：大型数据库技术（Oracle）专业：班级：姓名：学号：同组人：实验日期：实验项目实验十触发器实验类型设计性实验目的要求理解什么是触发器掌握触发器的设计与使用（实验内容及步骤）说明：1.环境：sql*plus2.将练习所有的PL/SQL语句整理成文本文件保存下来，以姓名和学号命名，上传到服务器作为实验报告附件提交或者亦可附在实验报告后。【实验步骤】10.1.案例某数据库有两张表：emp表和dept表，两张表的结构如下：emp(emp_idnumber(5),emp_namevarchar2(20),emp_salarynumber(4))；dept(dept_idnumber(3),dept_namevarchar2(20),emp_idnumber(5));要求如下：1、按照上表结构建立相应的表，为每张表写入5组合法数据。2、操纵相关表，使得“技术部”的员工的薪水上涨20%。3、建立日志，追踪薪水变动情况。4、建立测试包。案例的分析与实现要求1考察点为基本SQL语句；要求2主要考察复合查询；要求3是考察触发器的应用；要求4不仅考察了包的创建，而且也考察了在PL/SQL中的测试方法。1.要求1：首先根据前面表的结构可以创建两张表：——创建员工表createtableemp(emp_idnumber(5),emp_namevarchar2(20),emp_salarynumber(4));——部门表createtabledept(dept_idnumber(3),dept_namevarchar2(20),emp_idnumber(5));建立了表之后就可以往表里面写数据了，这里把添加表记录的代码写入到相应的存储过成绩程。/*给emp表添加记录的存储过程*/createorreplaceprocedureins_table_emp(p_emp_idnumber,p_emp_namevarchar2,p_emp_salarynumber)asv_emp_idnumber:=p_emp_id;v_emp_namevarchar2(20):=p_emp_name;v_emp_salarynumber:=p_emp_salary;begininsertintoempvalues(v_emp_id,v_emp_name,v_emp_salary);endins_table_emp;/*给dept表添加记录的存储过程*/createorreplaceprocedureins_table_dept(p_dept_idnumber,p_dept_namevarchar2,p_emp_idnumber)asv_dept_idnumber:=p_dept_id;v_dept_namevarchar2(20):=p_dept_name;v_emp_idnumber:=p_emp_id;begininsertintodeptvalues(v_dept_id,v_dept_name,v_emp_id);endins_table_emp;/*调用相应的存储过程实现记录添加*/beginins_table_emp(10000,'okl',4000);ins_table_emp(10001,'hjk',2300);ins_table_emp(10002,'jkl',3500);ins_table_emp(10003,'jklkk',3500);ins_table_emp(10004,'ioi',3500);ins_table_dept(111,'rty',10000);ins_table_dept(111,'gh',10001);ins_table_dept(111,'qwe',10002);ins_table_dept(112,'rffff',10003);ins_table_dept(113,'oooooo',10004);end;2.要求2：给指定部门的员工加薪，这实际上是一个复合查询，首先需要把所有该部门的员工选出来，然后对这些员工的薪水进行相应的改动。代码如下：（需要注意的是：将要加薪的部门作为参数，这样的存储过程更有灵活性。）createorreplaceprocedureadd_salary(p_dept_idnumber)asbeginupdateempsetemp.EMP_SALARY=emp.EMP_SALARY*1.2whereemp.EMP_IDin(selectemp.EMP_IDfromemp,deptwhereemp.EMP_ID=dept.EMP_IDanddept.DEPT_ID=p_dept_id);endadd_salary;原表数据：创建的过程（我改过）修改后（选定部门111）：3.要求3：建立日志对薪水的变动情况形成一个追踪，也就是说，如果对某个职员的薪水进行变更就应该将其相应的变更记录全部记下来。如果对emp表的salary字段创建一个触发器，来监视对salary的更改，把每次更改进行记录，这样就达到了要求3的目的了。createorreplacetriggerprint_salary_changebeforedeleteorinsertorupdateonemp--触发事件foreachrow--每修改一行都需要调用此过程declare--只有触发器的声明需要declare，过程和函数都不需要salary_balancenumber;begin--:new与:old分别代表该行在修改前和修改后的记录salary_balance=:new.salary;new.salary=:old.salary;dbms_output.PUT_LINE('oldsalaryis:'||:old.salary);dbms_output.PUT_LINE('oldsalaryis:'||:new.salary);dbms_output.PUT_LINE('oldsalaryis:'||to_char(salary_balance));endprint_salary_change;4.要求4：与其他语言（c/c++等）相比，PL/SQL的测试有其不同之处，归纳下来有三种方法：(1)使用DBMS_OUTPUT包的PUT_LINE方法来显示中间变量，以此来观察程序是否存在逻辑错误。(2)插入测试表的方法。即创建一个临时的中间表，然后把所有涉及到的中间变量的结果都作为记录插入到中间表中，这样可以查询表中的结果来观察程序的执行情况。(3)使用异常处理手段，对可疑的程序段使用begin…end，然后可以在exception里进行异常捕获处理。这里使用第二种方法来建立一个测试包，PL/SQL里包的概念类似于面向对象里的类的概念，包将一组操作和属性封装在一起，不仅增强了程序的模块化，而且由于封装了更多的操作和属性而提高了执行效能。建立一个PL/SQL需要两个步骤：首先要建立包头，类似于建立一个类的头文件，里面主要对包中的过程，函数和变量的声明；第二部分主要是包体部分，实现前面声明的过程和函数，另外还需要对包进行初始化等工作。根据这一思路，建立测试包如下：/*包头部分*/createorreplacepackagedebugopasproceduredebug_w(v_descriptionvarchar2,v_valueOfvariablevarchar2)procedurereset;v_numberOfLinenumber;enddebugop;createorreplacepackagebodydebugopasproceduredebug_w(v_descriptionvarchar2,v_valueOfvariablevarchar2)isbegininsertintodebugtablevalues(v_numberOfLine,v_description,v_valueOfvariable);v_numberOfLine:=v_numberOfLine+1;enddebug_w;procedureresetisbeginv_numberOfLine:=1;deletefromdebugtable;endreset;Enddebugop;beginreset;enddebug;10.2.实验练习完成实现下列功能的程序，写出相应的代码，并给出测试结果：1、创建一个过程，打印出各个工资级别的人数。备注：显示的格式为三种情况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中。相应的代码：测试结果：10.3.根据以下表完成相应的程序，并写出相应代码和测试结果：——创建员工表createtableemp(emp_idnumber(5),emp_namevarchar2(20),emp_salarynumber(4),jobvarchar2(20),dept_idnumber(3));——部门表createtabledept(dept_idnumber(3),dept_namevarchar2(20),locvarchar2(20));1、编写一个数据库触发器，当任何时候某个部门从dept中删除时，该触发器将从emp表中删除该部门的所有雇员。相应的代码：测试结果：2、编写一个数据包，它有两个函数和两个过程以操作emp表。该数据包要执行的任务为：插入一个新雇员；删除一个现有雇员；显示指定雇员的整体薪水；显示指定雇员所在部门名称。相应的代码：测试结果：3、编写一个函数以检查所指定雇员的薪水是否有效范围内。不同职位的薪水范围为：DesignationRaiseClerk1500-2500Salesman2501-3500Analyst3501-4500Others4501andabove.如果薪水在此范围内，则显示消息SalaryisOK，否则，更新薪水为该范围内的最水值。相应的代码：测试结果：作业与思考练习题