使用PLSQL编写存储过程访问数据库

使用PL/SQL编写存储过程访问数据库
一、实验目的
熟悉使用存储过程来进行数据库应用程序的设计。

二、实验内容
对学生-课程数据库，编写存储过程，完成下面功能：
1.统计离散数学的成绩分布情况，即按照各分数段统计人数；
2.统计任意一门课的平均成绩；
3.将学生选课成绩从百分制改为等级制（即A、B、C、D、E）。

要求：提交源程序并表示必要的注释。

保证程序能正确编译和运行，认真填写实验报告。

三、实验步骤
实验之前，已经建立数据库，有student，course和sc三张基本表。

（一）统计离散数学的成绩分布情况，即按照各分数段统计人数。

1.建立表Rank，其中第一列division 显示成绩分段划分，第二列number 显示的是成绩在该分数段的学生人数。

CREATE TABLE Rank(
division CHAR(20),
number INT);
2.编写存储过程。

create or replace procedure statistic_mark(name char(50))
AS
DECLARE
less60 INT :=0; //分为五个分数段，并置初始值为 0
b60a70 INT :=0;
b70a80 INT :=0;
b80b90 INT :=0;
more90 INT :=0;
curcno CHAR(4);
begin
select cno INTO curcno //根据课程名称在 Course 表中查询查询该课程号from course
where cname =name;
IF NOT FOUNO THEN //如果不存在用户输入的课程名称，返回错误信息RAISE EXCEPTION
END IF;
SELECT count(*) INTO less60 //查询分数低于 60 分的学生人数
FROM SC
WHERE cno =curcno AND grade <60;
SELECT count(*) INTO b60a70 //查询分数在 60 到 70 之间的学生人数
FROM SC
WHERE cno =curcno AND grade >=60 AND grade<70;
SELECT count(*) INTO b70a80 //查询分数在 70 到 80 之间的学生人数
FROM SC
WHERE cno =curcno AND grade >=70 AND grade<80;
SELECT count(*) INTO b80a90 //查询分数在 80 到 90 之间的学生人数
FROM SC
WHERE cno =curcno AND grade >=80 AND grade<90;
SELECT count(*) INTO more90
FROM SC
WHERE cno =curcno AND grade >=90 ;
INSERT INTO RANK V ALUES('[0,60)',less60); //向Rank中插入五条记录
INSERT INTO RANK V ALUES('[60,70)',b60a70);
INSERT INTO RANK V ALUES('[70,80)',b70a80);
INSERT INTO RANK V ALUES('[80,90)',b80a90);
INSERT INTO RANK V ALUES('[90,100)',more90);
END;
3.执行存储过程
编写好存储过程statistic_mark之后，在“查询分析器”中选择菜单中的“单事务执行”命令，这样系统就创建好了存储过程
然后使用PERFORM调用该过程，在表rank中查看执行的结果。

perform procedure statistic_mark('离散数学')；
select* from rank；
(二)统计任意一门课程的平均成绩
1.创建存储过程
（1）创建需要的表结构。

根据实验要求，要统计任意一门课程的平均成绩，因此需要建立表avggrade，其中第一列cname 显示被统计的课程名称，第二列avg显示选修了该课程的学生的平均成绩。

create table avggrade（
cname char(50),
avg numeric(10,6)）；
（1）编写存储过程
create or replace procedure collect_avg（）
as
declare //声明变量
curname char(50);
curno char(4);
curavgg char(10,6);
cursor mycursor for //声明游标mycursor查询课程号和课程名称select cno,cname from course;
begin
open mycursor; //打开游标
IF mycursor%ISOPEN THEN //条件控制，游标打开时进行以下处理
LOOP //循环控制
FETCH mycursor INTO curcno,curname; //游标推进一行取结果送变量
EXIT WHEN(mycursor%NOTFOUND); //如果没有返回值，则退出循环
SELECT A VG(grade)INTO curavgg FROM SC //求该课程的平均值送变量
WHERE cno = curcno;
//向avggrade //表中插入记录，显示课程名称和平均成绩
INSERE INTO avggrade V ALUES(curname,curavgg);
END LOOP; //结束循环控制
END IF; //结束条件控制
CLOSE mycursor;
END;
2.执行存储过程
首先执行编写好的存储过程collect_avg,然后在表avggrade中查看执行结果。

PERFORM PROCEDURE collect_avg();
SELECT * FROM avggrade;
(三)在表SC中将学生选课成绩从百分制改为等级制
1.创建存储过程
根据实验要求，本实验中存储过程的执行不需要在客户端返回结果，因此不需要建立相应的表结构来存放存储过程的执行结果。

直接编写存储过程。

create or replace procedure change_critical()
AS
DECLARE
chgrade CHAR(1);
currecord record;
BEGIN
ALTER TABLE SC ADD COLUMN(newgrade CHAR(1));
FOR currecord IN SELECT*FROM SC LOOP
IF currecord.grade<60 then
chgrade ='E';
ELSIF currecord.grade<70 then
chgrade ='D';
ELSIF currecord.grade<80 then
chgrade ='C;
ELSIF currecord.grade<90 then
chgrade ='B';
ELSE
chgrade ='A';
END IF;
UPDATE SC SET newgrade =chgrade
WHERE sno =currecord.sno AND cno=o;
END LOOP;
ALTER TABLE SC DROP COLUMN grade;
ALTER TABLE SC RENAME newgrade TO grade;
END;
2执行存储过程
PERFORM PROCEDURE change_critical();
(四)删除存储过程
存储过程一旦建立，则将被保存在数据库中，便于用户随时，反复地调用和执行。

如果不再需要该存储过程，可以将其删除。

删除存储过程statistic_mark。

DROP PROCEDURE statistic_mark;
(1)删除存储过程collect_avg,
DROP PROCEDURE collect_avg,
(2)删除存储过程change_critical;
DROP PROCEDURE change_critical;
四、实验总结。