Oracle数据库系统应用开发实用教程文本ch8实验报告

1、广东科学技术职业学院计算机工程技术学院（软件学院）实验报告专业班级 成绩评定学号姓名（合作者号）教师签名实验题目 存储过程、函数、程序包和触发器第周 星期 _第_节实验目的与要求1掌握编写存储过程和调用过程的方法2掌握编写函数和调用函数的方法3.掌握程序包的编写规范4掌握执行程序包中过程和函数的方法。5掌握语句级触发器的原理。6语句级触发器的编写方法。7测试语句级触发器是否生效。8掌握行级触发器的原理。9行级触发器的编写方法。10. 测试行级触发器是否生效。11. 掌握替代触发器的原理。12. 创建DDL触发器。13. 替代触发器的测试方法。14. 掌握DDL触发器的原理。15. 创建DDL触

2、发器。16. DDL触发器的测试方法。17. 掌握数据库级触发器的原理。18. 创建数据库级触发器。19. 数据库级触发器的测试方法。一、实验环境及方案Windows Server 2000 sp1 或更高版本；或 Windows Server 2003的所有版本；或 Windows XP专业版；Oracle10g；In ternet环境及浏览器。二、实验步骤实训8.1存储过程目的和要求掌握编写存储过程和调用过程的方法实训内容指导：编写一个存储过程，要求根据用户输入的员工号(employee_id)查询EMPLOYEES 表，返回员工的姓名、工作职位和薪水(first_name, last_n

3、ame, job_title,salary。并编写 一个匿名块调用此过程。(使用HR用户的EMPLOYEES表、JOBS表，其中关联字段为 EMPLOYEES. JOB_ID 和 JOBS. JOB_ID。以下同)(1。编写存储过程create or replace procedure pro_emp(emp_id nu mber)asvfn ame varchar2(20);vln ame varchar2(20);vjob varchar2(20);n sal nu mber;beg inselect e.first name,e.last name,j.Job title,e.salar

4、yinto vfname,vlname,vjob,nsalfrom employees e,jobs j where employee_id=emp_id and e.job_id=j.job_id ;dbms_output.put_line （雇员的姓名是:|vfname| |vlname）;dbms_output.put_line （雇员的职位是:|vjob）;dbms_output.put_line （雇员的薪水是:|nsal）;excepti onwhen no _data_fo und the ndbms_output.put_line （雇员编号未找到!）;end pro_emp;

5、（2）调用执行存储过程exec pro_emp（104）;执行结果为雇员的姓名是:Bruce Ernst雇员的职位是:Programmer雇员的薪水是:6000练习：1. 编写一个过程，要求根据用户输入的员工号（employee_id）查询EMPLOYEES 表，返回员工的工作职位、工作年限、电话和 Email。并编写一个匿名块调用此过程。2. 创建一个过程，用来实现九九乘法表。实训8.2函数目的和要求掌握编写函数和调用函数的方法实训内容指导：编写函数以部门号为参数，并计算该部门的平均工资。（1）编写函数create or replace fun cti on fun _avgsal（dept

6、_id nu mber）retur n floatassal_avg float(10);beg inselect avg(salary) into sal_avg from employees where departme nt_id =dept_id; return sal_avg;EXCEPTIONwhe n NO_DATA_FOUND the nDBMS_OUTPUT.PUT_LINE(部门号未找至U !);end ;(2) 调用函数set serveroutput on;declaresal_avg float(10);beg insal_avg :二fun _avgsal(60);

7、DBMS_OUTPUT.PUT_LINE(该部门的平均工资为:|sal_avg); end;执行结果为该部门的平均工资为：5760练习：在上面题目的基础上，列出部门内比平均工资高的员工姓名、职位和薪水。实训8.3程序包目的和要求 掌握程序包的编写规范 掌握执行程序包中过程和函数的方法。实训内容指导：编写一个程序包，此程序包有一个过程和一个函数，过程根据职员编号显示工作职 位和薪水。函数根据职员编号返回职员的就职日期。并编写调用程序执行。(1) 编写程序包规范create or replace package pack_employeeisprocedure pro_job_sal(emp_id

8、 nu mber);fun ctio n fun _date(emp_id nu mber) return date;end pack_employee;(2) 编写程序包主体create or replace package body pack_employeeasprocedure pro_job_sal(emp_id nu mber)isvjob varchar2(20);n sal nu mber(20);begi nselect j.job_title,e.salary into vjob,n sal from employees e,jobs j where e.job_id=j.

9、job_id and e.employee_id=emp_id;DBMS_OUTPUT.PUT_LINE(该职员的工作为：|vjob|薪水为:|nsal); excepti on whe n NO_DATA_FOUND the nDBMS_OUTPUT.PUT_LINE(职员编号未找到!);end pro_job_sal;function fun _date(emp_id nu mber)return dateish_date date;begi nselect hire_date into h_date from employees where employee_id=emp_id; ret

10、urn h_date;EXCEPTIONwhe n NO DATA FOUND the nDBMS_OUTPUT.PUT_LINE(职员编号未找到!);end fun _date;end pack_employee;(3) 调用程序set serveroutput on;execute pack__job_sal (105);执行结果为该职员的工作为：Programmer薪水为:4800declareh_date date;beg inh_date:=pack_employee.fu n_date(105);DBMS_OUTPUT.PUT_LINE(该职员的就职日期是:

11、|h_date);end;执行结果为该职员的就职日期是:25-6月-97练习：编写一个程序包，此程序包有一个过程和一个函数。过程以员工的工作职位(jobs表job_title字段)为参数，显示相同职位中的员工人数。函数利用传入参数传入部门号， 返回该部门中的员工人数。并编写调用程序执行。实训8.4语句级触发器目的和要求 掌握语句级触发器的原理。 语句级触发器的编写方法。 测试语句级触发器是否生效。实训内容 指导：创建语句级触发器，需要对scott用户的dept表上进行dml操作的用户进行安全 检查，如果不是scott用户，不能够做增、删、改的动作。（1建立触发器create or replac

12、e trigger biud_deptbefore in sert or update or delete on deptbegi nif user not in （SCOTT） thenraise_applicati on _error（-20001, you do not have access to modify this table.）; end if;end;（3）测试触发器即使sys , system用户也不能修改dept表。练习：创建语句级触发器，需要对HR用户的jobs表上进行dml操作的用户进行安全检查, 如果不是HR用户，不能够做增、删、改的动作。实训8.5行级触发器目的和

13、要求 掌握行级触发器的原理。 行级触发器的编写方法。 测试行级触发器是否生效。实训内容练习：创建一行级触发器，为学生信息表的主键生成自增序列号。（学生信息表信息包括学号、姓名、籍贯等，其中学号的编码示例为01041107+两位序号）。步骤提示：（1）建学生信息表（2）建序列（3）建立触发器（4）插入数据（5）测试实训8.6替代触发器目的和要求 掌握替代触发器的原理。 创建DDL触发器。 替代触发器的测试方法。实训内容指导：仓U建一个视图 view_emp_dept,数据来源于 emp表的字段 empno, ename job， emp.deptno，条件是 emp.deptno二dept.de

14、ptno。然后对视图 view_emp_dept更新部门信 息。例如：对视图进行更新操作，出现错误，请用替代触发器解决。步骤：（1）授权给SCOTT用户创建视图的权限，创建视图conn system/ma nager1;grant create view to scott;conn scott/tiger;create or replace view view_emp_dept as select emp no,en ame,job, emp.dept no dep nofrom emp,dept where emp.dept no 二dept.deptn;o（2）对视图进行更新操作updat

15、e view_emp_dept set dep no 二 50 where dep no 二 10;执行结果为update view_emp_dept set dep no 二 55 where dep no 二 10*第1行出现错误：ORA-O2291:违反完整约束条件（SCOTT.FK_DEPTNO）-未找到父项关键字（3）在视图上创建INSTEAD O!触发器create or replace trigger dept_emp_updin stead of updateon view emp deptfor each rowdeclarerowc nt nu mber;beg insel

16、ect coun t(*) in to rowc nt from dept where dept no二:n ew.dep no; if rowc nt=O the nbegi ninsert into dept(deptno) values (:new.depnd新部门名称);update emp set dept no二:n ew.dep no where dept no =:old.dep no end;elseupdate emp set dept no二:n ew.dep no where dept no =:old.dep no ; end if;end;(4) 重新对视图进行插入

17、操作update view_emp_dept set dep no 二 50 where dep no 二 10;执行结果为已更新3行。练习：60。请用替代触发器解决对视图view_emp_dept进行插入数据，其部门号为实训8.7 DDL触发器目的和要求 掌握DDL触发器的原理。 创建DDL触发器。 DDL触发器的测试方法。实训内容练习：建立DDL触发器用于审计记录所删除的对象情况。(1) 建立一日志表conn scott/tiger;create table droped_objects(object_ name varchar2(30),object_type varchar2(30),

18、dropped_ on date);(2) 创建触发器(3 )进行测试提示：先创建表、视图等对象然后将这些对象删除。查看日志记录实训8.8数据库级触发器目的和要求 掌握数据库级触发器的原理。 创建数据库级触发器。 数据库级触发器的测试方法。实训内容指导：创建数据库级触发器，记录数据库发生错误的信息。SCOTT用户下创建)(1) 创建下面的表来实现记录数据库发生错误的信息(例如在conn scott/tiger;create table error_log(timestamp date,user name varchar2(30),in sta nee nu mber, database_ na

19、me varchar 2(50), error stack varchar 2(2000)）;（2）授予创建触发器的用户 admi nister database trigge权限（注：普通用户需具有此权限才能创建系统触发器）conn system/ma nager;grant adm ini ster database trigger to scott;（3）建立数据库级触发器create or replace trigger logerrorsafter servererror on databasebeg inin sert into error_log values（sysdate,sys .login _user, sys.i nsta nce_n