实验六：存储过程.doc

题目：实验六 PL/SQL姓名Vivian日期 2006-1-1实验内容及完成情况：实验环境介绍：硬件：CPU：P3 800硬盘：30G内存：512M系统：Windows 2000Advanced Server在实验六之前，在安装过程中自动建立的“学生课程数据库”中共有三张关系表 Student、Course 和 SC，登陆的用户名为 SYSTEM，密码为 MANAGER。接下来的实验就是对学生课程数据库，编写存储过程。（一） 统计离散数学的成绩分布情况，即按照各分数段统计人数。1. 创建存储过程1） 创建需要的表结构。因为存储过程在执行后在客户端并没有返回值，因此需要建立一个表存放执行后的结果，并返回到客户端显示。根据实验要求，我们要统计选修了离散数学的学生的成绩分布，因此我们建立表 Rank，其中第一列 division 显示成绩分段划分，第二列 number 显示的是成绩在该分数段的学生人数。CREATE TABLE Rank(division CHAR(20),numberINT);2） 编写存储过程。CREATE OR REPLACE PROCEDURE statistic_mark(name CHAR(50)/存储过程带有一个字符型参数值，便于统计不同科目的分数分布情况ASDECLAREless60INT := 0;/分为五个分数段，并置初始值为 0b60a70INT := 0;b70a80INT := 0;b80a90INT := 0;more90 INT := 0;curcnoCHAR(4);/设字符型变量 curcno 存放输入的课程名称参数BEGINSELECT cno INTO curcno/根据课程名称在 Course 表中查询查询该课程号FROM CourseWHERE cname = name;IF NOT FOUND THEN/如果不存在用户输入的课程名称，返回错误信息RAISE EXCEPTION 输入错误，没有该课程;END IF;SELECT count(*) INTO less60/查询分数低于 60 分的学生人数FROM scWHERE cno = curcno AND grade = 60 AND grade = 70 AND grade = 80 AND grade = 90;/向表 Rank 中插入五条记录，显示成绩分布情况INSERT INTO Rank VALUES(0,60),less60);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;2. 执行存储过程首先执行编写好的存储过程 statistic_mark，然后在表 Rank 中查看执行结果。PERFORM PROCEDURE statistic_mark(离散数学);SELECT * FORM Rank;（二） 统计任意一门课的平均成绩。1. 创建存储过程1） 创建需要的表结构。根据实验要求，我们要统计任意一门课的平均成绩，因此我们建立表 Avggrade，其中第一列 cname 显示统计的课程名称，第二列 avgg 显示选修了该课程的学生平均成绩。CREATE TABLE Avggrade(cnameCHAR(50),avggNUMERIC(10, 6);2） 编写存储过程。CREATE OR REPLACE PROCEDURE collect_avggrade()ASDECLARE/声明变量curnameCHAR(50);curcnoCHAR(4);curavggNUMERIC(10, 6);CURSOR mycursor FOR/声明游标 mycursor 查询课程号和名称SELECT cno, cname FROM course;BEGINOPEN 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;/向 Avggrade 表中插入记录，显示课程名称和平均成绩INSERT INTO Avggrade VALUES(curname, curavgg);END LOOP;/结束循环控制END IF;/结束条件控制CLOSE mycursor;/关闭游标END;2. 执行存储过程首先执行编写好的存储过程 collect_avggrade，然后在表 Avggrade 中查看执行结果。PERFORM PROCEDURE collect_avggrade ();SELECT * FORM Avggrade;（三） 在表 SC 中将学生选课成绩从百分制改为等级制（即 A、B、C、D、E）。1. 创建存储过程根据实验要求，在本实验中存储过程的执行不需要在客户端返回结果，因此我们不需要建立相应的表结构来存放存储过程的执行结果。直接编写存储过程。CREATE OR REPLACE PROCEDURE change_critical()ASDECLAREchgradeCHAR(1);/声明标量currecordRECORD;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 分，将成绩置为 Echgrade = E;ELSIF currecord.grade 60&70，将成绩置为 Dchgrade = D;ELSIF currecord.grade 70&80，将成绩置为 Cchgrade = C;ELSIF currecord.grade 80&90，将成绩置为 Bchgrade = B;ELSE/如果成绩高于 90 分，将成绩置为 Achgrade = A;END IF;/结束条件控制UPDATE SC SET newgrade = chgrade /修改 newgrade 列值，填入换算后的成绩WHERE sno = currecord.sno AND cno = o;END LOOP;ALTER TABLE SC DROP COLUMN grade; /删除原有的列 gradeALTER TABLE SC RENAME newgrade TO grade; /将 newgrade 列名改为 gradeEND;2. 执行存储过程PERFORM PROCEDURE change_critical ();（四） 删除存储过程存储过程一旦建立，则将被保存在数据库中，便于用户随时、反复调用执行。如果不再需要该存储过程的话，可以将其删除。1. 删除存