实验六
实验名称：使用游标、存储过程和触发器 实验学时：2 实验目的:
（1） 了解游标的概念和工作原理。 （2） 了解存储过程的分类和使用方法。 （3） 了解触发器的概念。
（4） 学习编写和执行自定义过程。 （5） 学习创建和使用触发器。
实验内容及步骤 1、游标的使用
（一）使用显式游标 （1）说明游标。
DECLARE CURSOR <游标名> [ (<参数列表>) ] IS
OPEN <游标名> [ (<参数列表>) ]; （3）读取数据。
FETCH <游标名> INTO <变量列表>; （4）关闭游标。 CLOSE <游标名>; 例1、使用游标读取1号部门的名称。 DECLARE --开始声明部分
varName VARCHAR2(50); --声明变量,用来保存游标中的部门名称 --定义游标, varId为参数, 指定部门编号 CURSOR MyCur(varId NUMBER) IS
SELECT Dep_Name FROM dhb.Departments WHERE Dep_id = varId; BEGIN --开始程序体
OPEN MyCur(1); --打开游标,参数为1,表示读取部门编号为1的记录 FETCH MyCur INTO varName; --读取当前游标位置的数据 CLOSE MyCur; --关闭游标
dbms_output.put_line('部门名称:' || varName); --显示读取的数据 END; --结束程序体
（二）带子查询的游标for循环的语法如下： FOR <记录名> IN (SELECT 子查询) LOOP 语句1； 语句2； ?
语句n；
END LOOP;
例2、带子查询的游标for循环例子： /* 打开显示模式 */
SET ServerOutput ON; BEGIN --开始程序体
FOR var_DeptRecord IN (SELECT Dep_id, Dep_Name FROM dhb.Departments) LOOP /* 显示保存在记录变量var_DeptRecord中的数据 */
dbms_output.put_line('部门编号:' || var_DeptRecord.Dep_Id ||', 部门名称:' || var_DeptRecord.Dep_Name); END LOOP;
END; --结束程序体 /
2、过程的定义及使用
CREATE PROCEDURE语句来创建过程的语法格式： CREATE [ OR REPLACE ] PROCEDURE<过程名> [ <参数列表> ] IS | AS [ <局部变量声明> ] BEGIN
<过程体>
END [ <过程名> ]; 例3、创建过程GetDepAvgWage ，此过程的功能是根据输入的部门号获取指定部门的平均工资。
CREATE OR REPLACE PROCEDURE DHB.GetDepAvgWage ( v_DepId IN NUMBER, v_AvgWage OUT NUMBER) AS BEGIN
SELECT AVG(Wage) INTO v_AvgWage
FROM DHB.Employees WHERE Dep_Id = v_DepId; END; /
执行过程GetDepAvgWage ，获取1号部门的平均工资。 --执行在使用输出参数时，必须首先定义参数的类型。 SET ServerOutput ON; DECLARE
AvgWage NUMBER := 0; BEGIN
DHB.GetDepAvgWage(1, AvgWage); dbms_output.put_line(AvgWage); END; /
3、触发器的创建及使用。
CREATE TRIGGER语句来创建触发器：
CREATE [ OR REPLACE ] TRIGGER <触发器名> {BEFORE | AFTER }
{INSERT | DELETE | UPDATE [OF column [, column …]]}
[OR {INSERT | DELETE | UPDATE [OF column [, column …]]}...] ON <表名>|<视图名> [ FOR EACH ROW ]
[ WHEN <条件表达式> ]
例4、创建一个语句级DML触发器。
（1）首先创建表Test和表LogTable（记录对表Test的更新操作） CREATE TABLE dhb.Test ( id INTEGER,
name VARCHAR2(50)); CREATE TABLE dhb.LogTable ( log_date DATE,
action VARCHAR2(50));
（2）创建语句触发器LogUpdateTrigger
CREATE OR REPLACE TRIGGER dhb.LogUpdateTrigger AFTER INSERT OR UPDATE OR DELETE ON dhb.Test
DECLARE log_action VARCHAR2(50); BEGIN
IF INSERTING THEN log_action := 'Insert'; ELSIF UPDATING THEN log_action := 'Update'; ELSIF DELETING THEN log_action := 'Delete'; ELSE DBMS_OUTPUT.PUT_LINE(' ..'); END IF;
INSERT INTO dhb.LogTable (log_date, action) VALUES (SYSDATE, log_action); END; /
（3）在表Test上执行插入、修改、删除操作： INSERT INTO dhb.Test VALUES(1, 'Insert'); UPDATE dhb.Test SET name='Update'; DELETE FROM dhb.Test WHERE id=1; COMMIT;
（4）查看表LogTable的内容： SELECT * FROM DHB.LogTable;
例5、创建一个行级触发器MyTrigger，它的作用是当dhb.Departments中Dep_id列的值发生变化时，自动更新表Employees中的Dep_id列的值，从而保证数据完整性。 CREATE OR REPLACE TRIGGER DHB.MyTrigger AFTER UPDATE ON DHB.Departments FOR EACH ROW BEGIN
UPDATE DHB.Employees SET Dep_id = :new.Dep_id WHERE Dep_id = :old.Dep_id; END; /
验证触发器的功能：
UPDATE DHB.Departments SET Dep_id=200 WHERE Dep_id=1; Commit;
SELECT Emp_name, Dep_id FROM DHB.Employees;
思考题：
（1） 创建游标mycur，从表employees中读取1号部门的员工姓名，职务。并输出“员工
姓名：XXX，职务：XXX。”
（2） 创建存储过程GetGrade，查询指定员工的工资，使用CASE语句输出其工资等级。
工资小于等于3000，等级为―低‖；工资大于3000，小于5000，等级为‖中‖；工资大于等于5000，等级为高。并执行该存储过程。
（3） 创建存储过程UpdateWage，查询指定员工的工资，如果工资小于3000，则加200
工资，并提示信息“XX号员工工资已更新。”，如果工资大于3000，则提示信息“XX号员工工资为XXX，已达到规定标准。”
（4） 创建触发器MyTrigger，它的作用是当表departments中的记录被删除后，自动删除
表employees中的对应的员工记录，从而保证数据的完整性。
实 验 报 告
实验名称：表的管理 实验课时：4课时 实验地点：E座305
实验时间：2014年 5月 6日 星期二 第十二周 实验目的及要求：
1) 了解Oracle表和视图的概念。 2) 熟练掌握使用SQL语句创建表。 3) 学习使用SELECT语句查询数据。
实验环境：
1) 硬件设备：PC机一台 2) 操作系统：Windows XP 3) 应用工具：Oracle 10g
实验内容：（算法、程序、步骤和方法）
1) 创建用户，指定密码、表空间。用户名命名以本人姓名的缩写+_+学号后
两位命名。代码如下： create user ty_06 identified by 123
default tablespace users temporary tablespace temp;
2) 创建如下表，将表保存到自己的用户方案中。代码如下： create table student
(sno varchar2(10) Primary Key, sname varchar2(20), sage number(2), ssex varchar2(5));
create table teacher
(tno varchar2(10) Primary Key, tname varchar2(20));
create table course
(cno varchar2(10), cname varchar2(20), tno varchar2(20), Primary Key(cno,tno));