课程名称 Oracle数据库系统设计 实验序号 05 实验项目 过程、函数和触发器 实验地点 综B504 实验学时 2 实验类型 指导教师 侯睿 实 验 员
专 业 ____软件工程_____ 班 级 2班
学 号 姓 名
2014年 6 月 9 日
一、实验目的及要求 通过上机实践熟悉Oracle 11g的对象管理，创建过程、函数和触发器，并进行测试。 二、实验原理与内容 （1）创建一个过程avg_sal，用于输出emp表中的某个部门的平均工资，并在PL/SQL匿名块调用该过程输出部门SALES的平均工资; （2）创建一个函数find_loc, 用于返回某个员工所在的工作地点。并调用该函数，显示员工号为7788的工作地点。 （3）创建一个触发器tr_emp_sal,当进行update操作时，员工的工资只能涨不能降，不允许删除员工记录，并修改sal和删除记录，查看相关信息。 三、实验软硬件环境 1、多媒体计算机一台； 2、计算机上安装有Oracle 11g数据库软件； 四、实验过程（实验步骤、记录、数据、分析） 1、打开进程，并以数据库管理员身份登陆： (1)点击“开始”-“运行”，输入lsnrctl start以启动监听程序服务，如下： C:\\Documents and Settings\\Linux>lsnrctl start LSNRCTL for 32-bit Windows: Version 11.1.0.6.0 - Production on 09-6月 -2014 10:2 3:17 Copyright (c) 1991, 2007, Oracle. All rights reserved. 启动tnslsnr: 请稍候... TNSLSNR for 32-bit Windows: Version 11.1.0.6.0 - Production 系统参数文件为d:\\app\\Linux\\product\\11.1.0\\db_1\\network\\admin\\listener.ora 写入d:\\app\\linux\\diag\nslsnr\\Linux-49\\listener\\alert\\log.xml的日志信息 监听: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\\\.\\pipe\\EXTPROC1521ipc))) 监听: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=Linux-49)(PORT=1521))) 正在连接到 (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521))) LISTENER 的 STATUS ------------------------ 别名 LISTENER 版本 TNSLSNR for 32-bit Windows: Version 11.1.0.6.0 - Produ ction 启动日期 09-6月 -2014 10:23:19 正常运行时间 0 天 0 小时 0 分 2 秒 跟踪级别 off 安全性 ON: Local OS Authentication SNMP OFF 监听程序参数文件 d:\\app\\Linux\\product\\11.1.0\\db_1\\network\\admin\\listene r.ora 监听程序日志文件 d:\\app\\linux\\diag\nslsnr\\Linux-49\\listener\\alert\\log. xml 监听端点概要... (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\\\.\\pipe\\EXTPROC1521ipc))) (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=Linux-49)(PORT=1521))) 监听程序不支持服务 命令执行成功 (2)输入net start oracleserviceorcl以启动实例服务： C:\\Documents and Settings\\Linux>net start oracleserviceorcl OracleServiceORCL 服务正在启动 ................. OracleServiceORCL 服务已经启动成功。 (3)通过命令行连接到SQL*Plus： C:\\Documents and Settings\\Linux>sqlplus system/system as sysdba SQL*Plus: Release 11.1.0.6.0 - Production on 星期一 6月 9 10:39:03 2014 Copyright (c) 1982, 2007, Oracle. All rights reserved. 连接到: Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> 2、创建一个过程avg_sal，用于输出emp表中的某个部门的平均工资，并在PL/SQL匿名块调用该过程输出部门SALES的平均工资。 SQL> edit 已写入 file afiedt.buf 1 CREATE OR REPLACE PROCEDURE avg_sal 2 (emp_name IN VARCHAR2,value OUT NUMBER) AS 3 BEGIN 4 select avg(sal) INTO value from scott.emp 5 where deptno= 6 (select deptno from scott.dept 7 where dname=emp_name); 8* END avg_sal; SQL> / 过程已创建。 SQL> variable value NUMBER SQL> exec avg_sal('SALES',:value); PL/SQL 过程已成功完成。 SQL> PRINT value; VALUE ---------- 1566.66667 3、创建一个函数find_loc, 用于返回某个员工所在的工作地点。并调用该函数，显示员工号为7788的工作地点。 SQL> edit 已写入 file afiedt.buf 1 CREATE OR REPLACE FUNCTION find_loc (emp_num NUMBER) 2 RETURN VARCHAR2 AS 3 loc_name scott.dept.loc%TYPE; 4 BEGIN 5 select loc INTO loc_name from scott.dept 6 where deptno= 7 (select deptno from scott.emp 8 where empno=emp_num); 9 RETURN loc_name; 10* END find_loc; SQL> / 函数已创建。 SQL> select find_loc(7788) from dual; FIND_LOC(7788) -------------------------------------------------------------------------------- DALLAS SQL> 4、创建一个触发器tr_emp_sal,当进行update操作时，员工的工资只能涨不能降，不允许删除员工记录，并修改sal和删除记录，查看相关信息。 SQL> create or replace trigger tr_emp_sal before update of sal or delete on emp for each row begin case when updating('sal') then if :new.sal-:old.sal<0 then raise_application_error(-20001, '员工的工资只能涨不能降！'); end if; when deleting then raise_application_error(-20001, '不能删除员工记录！'); end case; end; 触发器已创建 SQL> delete from scott.emp where empno=7934 2 / delete from scott.emp where empno=7934 * 第 1 行出现错误: ORA-20001: 不能删除员工记录！ ORA-06512: 在 \"SYS.TR_EMP_SAL\ORA-04088: 触发器 'SYS.TR_EMP_SAL' 执行过程中出错 SQL> update scott.emp set sal=100 where empno=7934 2 / update scott.emp set sal=100 where empno=7934 * 第 1 行出现错误: ORA-20001: 员工的工资只能涨不能降！ ORA-06512: 在 \"SYS.TR_EMP_SAL\ORA-04088: 触发器 'SYS.TR_EMP_SAL' 执行过程中出错 SQL> update scott.emp set sal=10000 where empno=7934 2 / 已更新 1 行。 SQL> 五、测试/调试及实验结果分析 1、遇到的问题： （1）当输入lsnrctl start以启动进程监听时，显示以下信息： C:\\Users\\Administrator>lsnrctl start LSNRCTL for 32-bit Windows: Version 11.1.0.6.0 - Production on 20-3月 -2012 17:2 5:05 Copyright (c) 1991, 2007, Oracle. All rights reserved. 启动tnslsnr: 请稍候... TNSLSNR for 32-bit Windows: Version 11.1.0.6.0 - Production 系统参数文件为D:\\app\\Administrator\\product\\11.1.0\\db_1\\network\\admin\\listener.or a 写入d:\\app\\administrator\\diag\nslsnr\\Chenk2007\\listener\\alert\\log.xml的日志信息 监听: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\\\.\\pipe\\EXTPROC1521ipc))) 监听该对象时出错: (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=202.192.134.11)(PORT =1521))) TNS-12545: 因目标主机或对象不存在, 连接失败 TNS-12560: TNS: 协议适配器错误 TNS-00515: 因目标主机或对象不存在, 连接失败 32-bit Windows Error: 49: Unknown error 监听程序未能启动。请参阅上面的错误消息... 因此，打开“配置和移植工具”中的“Net Manager”把“主机名”改为本机的主机名“Chenk2007”，这样即可解决本问题。 六、实验结论与体会 总的来说，通过本次实验，我学会了Oracle数据库过程、函数和触发器的创建、修改和使用。 这些其实都是一些很重要的数据库操作知识，在接下来的学习中我会更加努力，学会Oracle数据PL/SQL语言的更多知识。 2014年6月9 日
因篇幅问题不能全部显示，请点此查看更多更全内容