数据库实验

使用pl/sql编写存储过程访问数据库
（一）统计离散数学的成绩分布情况，即按照各分数段统计人数。

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;
b80a90 INT := 0;
more90 INT := 0;
curcno CHAR(4); //设字符型变量curcno 存放输入的课程名称参数BEGIN
SELECT cno INTO curcno //根据课程名称在Course 表中查询查询该课程号FROM Course
WHERE cname = name;
IF NOT FOUND THEN //如果不存在用户输入的课程名称，返回错误信息RAISE EXCEPTION '输入错误，没有该课程';
END IF;
SELECT count(*) INTO less60 //查询分数低于60 分的学生人数
FROM s
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 //查询分数高于90 的学生人数
FROM sc
WHERE cno = curcno AND grade >= 90;
INSERT INTO Rank VALUES('[0,60)',less60);//向Rank中插入五条记录显示成绩分布情况
INSERT INTO Rank VALUES('[60,70)',b60a70);
INSERT INTO Rank VALUES('[70,80)',b70a80);
INSERT INTO Rank VALUES('[80,90)',b80a90);
INSERT INTO Rank VALUES('[90,100]',more90);
END;
3）执行存储过程，首先执行编写好的存储过程statistic_mark，然后在表Rank 中查看执行结果。

PERFORM PROCEDURE statistic_mark('离散数学');
SELECT * FORM Rank;
（二）统计任意一门课的平均成绩。

1）创建需要的表结构。

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

CREATE TABLE Avggrade(
cname
avgg
CHAR(50),
NUMERIC(10, 6));
2）编写存储过程。

CREATE OR REPLACE PROCEDURE collect_avggrade()
AS
DECLARE //声明变量
Curname CHAR(50);
Curcno CHAR(4);
curavgg NUMERIC(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 AVG(grade) INTO curavgg FROM SC //求该课程的平均值送变量
WHERE cno = curcno;
INSERT INTO Avggrade VALUES(curname, curavgg); //向Avggrade中插入记录显示课程名称和平均成绩
END LOOP; //结束循环控制
END IF; //结束条件控制
CLOSE mycursor; //关闭游标
END;
3）执行存储过程
首先执行编写好的存储过程collect_avggrade，然后在表Avggrade 中查看执行结果。

PERFORM PROCEDURE collect_avggrade ();
SELECT * FORM Avggrade;
（三）在表SC 中将学生选课成绩从百分制改为等级制（即A、B、C、D、E）。

1）创建存储过程
根据实验要求，在本实验中存储过程的执行不需要在客户端返回结果，因此我们不需要建立相应的表结构来存放存储过程的执行结果。

直接编写存储过程。

CREATE OR REPLACE PROCEDURE change_critical()
AS
DECLARE
Chgrade CHAR(1); //声明标量
Currecord RECORD;
BEGIN
//由于之前的成绩值为整数型，而修改后的结果为字符型，因此在SC 中先增加一列
//newgrade，存放换算后的成绩
ALTER TABLE SC ADD COLUMN (newgrade CHAR(1));
FOR currecord IN SELECT * FROM SC LOOP //FOR 循环处理每个学生的成绩IF currecord.grade < 60 THEN //如果成绩低于60 分，将成绩置为E
chgrade = 'E';
ELSIF currecord.grade < 70 THEN //如果成绩>60&<70，将成绩置为D
chgrade = 'D';
ELSIF currecord.grade < 80 THEN //如果成绩>70&<80，将成绩置为C
chgrade = 'C';
ELSIF currecord.grade < 90 THEN //如果成绩>80&<90，将成绩置为B
chgrade = 'B';
ELSE //如果成绩高于90 分，将成绩置为 A
chgrade = 'A';
END IF; //结束条件控制
UPDATE SC SET newgrade = chgrade //修改newgrade 列值，填入换算后的成绩WHERE sno = currecord.sno AND cno = o;
END LOOP;
ALTER TABLE SC DROP COLUMN grade; //删除原有的列grade
ALTER TABLE SC RENAME newgrade TO grade; //将newgrade 列名改为grade END;
2）执行存储过程
PERFORM PROCEDURE change_critical ();
（四）删除存储过程
存储过程一旦建立，则将被保存在数据库中，便于用户随时、反复调用执行。

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

1. 删除存储过程statistic_mark。

DROP PROCEDURE statistic_mark;
2. 删除存储过程collect_avggrade。

DROP PROCEDURE collect_avggrade;
3. 删除存储过程change_critical。

DROP PROCEDURE change_critical;。