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

一实验目的：熟悉使用存储过程来进行数据库应用程序的设计。二实验平台：在实验1中所安装的RDBMS及其PL/SQL执行引擎。编程工具自选。三实验内容和要求：对学生-课程数据库编写存储过程，完成以下一些功能：1. 统计“离散数学”课程的成绩分布情况，即按照各分数段来统计人数2. 统计任意一门课程的平均成绩3. 将学生选课成绩从百分制改为等级制（即ABCDE）要求：提交源程序并标识必要的注释。保证程序能正确地编译和运行，认真填写实验报告报告题目：使用PL/SQL编写存储过程访问数据库姓名：XXX日期：2012.5.24实验内容及完成情况： 在进行实验6之前，已经建立数据库，有Student，course和SC三张基本表，登陆用户名为SYSTEM,密码为MANAGER。接下来的实验就是对该数据库编写存储过程。 （一）统计“离散数学”课程的成绩分布情况1. 创建存储过程（1） 创建需要的表结构。因为存储过程执行后在客户端并没有返回值，因此需要建立一个表来存放执行后的结果，并返回到客户端显示。根据实验要求，要统计选修“离散数学”课程的学生的成绩分布，因此需要建立表Rank，其中第一列division显示成绩分数段划分，第二列number显示成绩在该分数段的学生人数。 Create table rank（Division char（20），Number INT）；（2） 编写存储过程。Create or replace procedure statistic_mark(name CHAR(50)ASDECLARELess60 INT :=0;B60a70 INT :=0;B70a80 INT :=0;B80b90 INT :=0;More 90 INT :=0;Curcno CHAR(4);BeginSelect cno INTO curcnoFrom courseWhere cname =name;IF NOT FOUNO THEN RAISE EXCEPTION END IF;SELECT count(*) INTO less60FROM SCWHERE cno =curcno AND grade =60 AND grade=70 AND grade=80 AND grade=90 ;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之后，在“查询分析器”中选择菜单中的“单事务执行”命令，这样系统就创建好了存储过程然后使用PERFORM调用该过程，在表rank中查看执行的结果。PERFORM PROCEDURE statistic_mark(离散数学)；Select* from rank； (二)统计任意一门课程的平均成绩1.创建存储过程（1）创建需要的表结构。根据实验要求，要统计任意一门课程的平均成绩，因此需要建立表Avggrade，其中第一列cname 显示被统计的课程名称，第二列avgg显示选修了该课程的学生的平均成绩。CREATE TABLE Aggrade（Cname CHAR(50),Avgg NUMERIC(10,6)）；（3） 编写存储过程Create or replace procedure collect_avggrade（）AsDeclare 声明变量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 AVG(grade)INTO curavgg FROM SC 求该课程的平均值送变量 WHERE cno = curcno; 向avggrade 表中插入记录，显示课程名称和平均成绩 INSERE INTO Avggrade VALUES(curname,curavgg);END LOOP; 结束循环控制END IF; 结束条件控制CLOSE mycursor;END;2. 执行存储过程首先执行编写好的存储过程collect_avggrade,然后在表avggrade中查看执行结果。 PERFORM PROCEDURE collect_avggrade();SELECT*FROM avggrade;(3) 在表SC中将学生选课成绩从百分制改为等级制1. 创建存储过程根据实验要求，本实验中存储过程的执行不需要在客户端返回结果，因此不需要建立相应的表结构来存放存储过程的执行结果。直接编写存储过程。Create or replace procedure change_critical()ASDECLARE Chgrade CHAR(1); Currecord record;BEGINALTER TABLE SC ADD COLUMN(newgrade CHAR(1);FOR currecord IN SELECT*FROM SC LOOPIF currecord.grade60 then Chgrade =E;ELSIF currecord.grade70 then Chgrade =D;ELSIF currecord.grade80 then Chgrade =C;ELSIF currecord.grade90 then Chgrade =B;ELSE Chgrade =A;END IF;UPDATE SC SET newgrade =chgradeWHERE 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();(4) 删除存储过程存储过程一旦建立，则将被保存在数据库中，便于用户随时，反复地调用和执行。如果不再需要该存储过程，可以将其删除。删除存储过程statistic_m