实验十六游标、存储过程和函数
一、目的与要求
1．了解游标的概念和工作原理；
2．了解存储过程的分类和使用方法；
3．了解触发器的概念；
4．学习编写和执行自定义过程；
5．学习编写和执行自定义函数；
6．学习创建和使用触发器。
二、实验准备
1．首先要了解游标是映射在结果集中一行数据上的位置实体，有了游标，用户就可以访问结果集中的任意一行数据了。
将游标放置到某行后，即可对该行数据进行操作，最常见的操作是提取当前行数据。
2．使用显式游标的步骤：
（1）说明游标。
（2）打开游标。
（3）读取数据。
（4）关闭游标。
3．了解PL/SQL包括3种存储过程，即过程、函数和程序包。
4．了解触发器是一种特殊的存储过程，当指定表中的数据发生变化时自动运行。
三、实验内容
1．练习书上的例子10.1—10.24。
2．以上机实验经常用到的数据库LIB为例，编写过程P_ResetPrice，此过程的功能是将表图书中指定书号的单价更改为10.0，调用该过程将书号为’TP311.13/CM3’的单价更改为10.0，将程序写在实验报告中。
create or replace procedure P_ResetPrice
(vBno in varchar2)
as
begin
update 图书 set 单价=10.0 where 图书号=vBno;
end;
execute P_ResetPrice(‘TP311.13/CM3’);
3．编写一函数F_GetBName，该函数的功能是在图书中根据指定的书号，返回该书的书名，并在匿名块中调用函数F_GetBName找出编号为“TP311.132/ZG1”的书名，将程序写在实验报告中。
create function F_GetName
(vtno IN 图书.图书号%Type)
return 图书.书名%Type
as
outname 图书.书名%Type;
begin
select 书名 into outname
from 图书
where 图书号=vtno;
return outname;
end;
set ServerOutPut on;
declare
varName 图书.书名%Type;
begin
varName:=F_GetName('TP311.132/ZG1');
dbms_output.put_line(varName);
end;
4．创建一程序包MyPack，包含以上的过程P_ResetPrice和函数F_GetBName，并在匿名块中调用Mypack包中的过程和函数进行测试，将程序写在实验报告中。
CREATE PACKAGE MyPack
IS
PROCEDURE P_ResetPrice
(vBno in varchar2);
FUNCTION F_GetName
(vtno IN 图书.图书号%Type)
return 图书.图书号%Type;
END MyPack;
CREATE PACKAGE BODY MyPack
IS
PROCEDURE P_ResetPrice
(vBno in varchar2)
AS
begin
update 图书 set 单价=10.0 where 图书号=’’||vBno||’’;
end;
function F_GetName
(vtno IN 图书.图书号%Type)
return 图书.图书号%Type
as
outname 图书.图书号%Type;
begin
select 书名 into outname
from 图书
where 图书号=’’||vtno||’’;
return outname;
end;
END MyPack;
SET ServerOutput ON;
declare
varName 图书.图书号%Type;
begin
/* 测试包中的过程*/
MyPack.P_ResetPrice(‘TP311.13/CM3’);
/* 测试包中的函数*/
varName:=MyPack.F_GetName('TP311.132/ZG1');
dbms_output.put_line(varName);
end;
5．编写一数据库触发器，当任何时候读者书表中的某个读者号更新时，该触发器将借阅表中相应的读者号进行级联更新，将程序写在实验报告中。
CREATE OR REPLACE TRIGGER MyTrigger
AFTER UPDATE ON 读者
FOR EACH ROW
BEGIN
UPDATE 借阅 SET 读者号 = :new.读者号
WHERE 读者号 = :old.读者号;
END;
6.编写程序根据常用的Enrollment表中所有记录的分数，给出相应的等级，要求用游标实现，程序运行的结果如下：
学号:20010101，课程号: C1，成绩:90，等级:: 优
学号:20010102，课程号: C1，成绩:88，等级:: 良
学号:20010102，课程号: C2，成绩:94，等级:: 优
学号:20010102，课程号: C3，成绩:54，等级:: 不及格
将程序写在实验报告中。
set serveroutput on;
declare
v_grade number;
v_dj varchar2(20);
v_sno enrollment.sno%TYPE;
v_cno o%TYPE;
cursor mycur is select sno,cno,grade from enrollment;
begin
if mycur%ISOPEN=FALSE then
open mycur;
end if;
LOOP
FETCH mycur into v_sno,v_cno,v_grade;
exit when mycur%NOTFOUND;
case
when v_grade>=90 then
dbms_output.put_line('学号:' || v_sno ||'，课程号: '|| v_cno ||'，成绩:' || v_grade|| '，等级:' || ': 优');
when v_grade>=80 AND v_grade<90 then
dbms_output.put_line('学号:' || v_sno ||'，课程号: '|| v_cno ||'，成绩:' || v_grade ||'，等级:' || ': 良');
when v_grade>=70 AND v_grade<80 then
dbms_output.put_line('学号:' || v_sno ||'，课程号: '|| v_cno ||'，成绩:' || v_grade ||'，等级:' || ': 中');
when v_grade>=60 AND v_grade<70 then
dbms_output.put_line('学号:' || v_sno ||'，课程号: '|| v_cno ||'，成绩:' || v_grade ||'，等级:' || ': 及格');
else
dbms_output.put_line('学号:' || v_sno ||'，课程号: '|| v_cno ||'，成绩:' || v_grade|| '，等级:' || ': 不及格');
end case;
end loop;
close mycur;
end;
结果如下所示:
学号:20010101，课程号: C1，成绩:90，等级:: 优
学号:20010102，课程号: C1，成绩:88，等级:: 良
学号:20010102，课程号: C2，成绩:94，等级:: 优
学号:20010102，课程号: C3，成绩:54，等级:: 不及格。