《第八章 使用PL SQL编写存储过程访问数据库.doc》由会员分享,可在线阅读,更多相关《第八章 使用PL SQL编写存储过程访问数据库.doc(4页珍藏版)》请在三一文库上搜索。
1、暨南大学本科实验报告专用纸课程名称 数据库系统 成绩评定 实验项目名称 使用PL/SQL编写存储过程访问数据库 指导教师 实验项目编号 实验项目类型 实验地点 学生姓名 学号 学院 系 专业 实验时间 2012年 月 日下午一、 实验目的熟悉使用存储过程来进行数据库应用程序的设计。二、 实验内容对学生-课程数据库,编写存储过程,完成下面功能:1. 统计离散数学的成绩分布情况,即按照各分数段统计人数;2. 统计任意一门课的平均成绩;3. 将学生选课成绩从百分制改为等级制(即A、B、C、D、E)。要求:提交源程序并表示必要的注释。保证程序能正确编译和运行,认真填写实验报告。三、 实验步骤实验之前,
2、已经建立数据库,有student,course和sc三张基本表,登陆用户名为tiger,密码为tiger。接下来的实验就是对该数据库编写存储过程。(一)统计“离散数学”课程的成绩分布情况1. 创建存储过程(1) 创建需要的表结构。因为存储过程执行后在客户端并没有返回值,因此需要建立一个表来存放执行后的结果,并返回到客户端显示。根据实验要求,要统计选修“离散数学”课程的学生的成绩分布,因此需要建立表Rank,其中第一列division显示成绩分数段划分,第二列number显示成绩在该分数段的学生人数。create table rank(division char(20),number int);
3、(2) 编写存储过程。create or replace procedure statistic_mark(name char(50)ASDECLAREless60 INT :=0;b60a70 INT :=0;b70a80 INT :=0;b80b90 INT :=0;more90 INT :=0;curcno CHAR(4);beginselect cno INTO curcnofrom coursewhere cname =name;IF NOT FOUNO THEN RAISE EXCEPTION END IF;SELECT count(*) INTO less60FROM SCWHE
4、RE 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之后
5、,在“查询分析器”中选择菜单中的“单事务执行”命令,这样系统就创建好了存储过程然后使用PERFORM调用该过程,在表rank中查看执行的结果。perform procedure statistic_mark(离散数学);select* from rank; (二)统计任意一门课程的平均成绩1.创建存储过程(1)创建需要的表结构。根据实验要求,要统计任意一门课程的平均成绩,因此需要建立表avggrade,其中第一列cname 显示被统计的课程名称,第二列avg显示选修了该课程的学生的平均成绩。create table avggrade(cname char(50),avg numeric(10,
6、6));(3) 编写存储过程create or replace procedure collect_avg()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,c
7、urname; /游标推进一行取结果送变量 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_avg,然后
8、在表avggrade中查看执行结果。PERFORM PROCEDURE collect_avg();SELECT * FROM avggrade;(3) 在表SC中将学生选课成绩从百分制改为等级制1. 创建存储过程根据实验要求,本实验中存储过程的执行不需要在客户端返回结果,因此不需要建立相应的表结构来存放存储过程的执行结果。直接编写存储过程。create or replace procedure change_critical()ASDECLARE chgrade CHAR(1); currecord record;BEGINALTER TABLE SC ADD COLUMN(newgrade
9、 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;
10、ALTER TABLE SC DROP COLUMN grade;ALTER TABLE SC RENAME newgrade TO grade;END;2执行存储过程PERFORM PROCEDURE change_critical();(4) 删除存储过程存储过程一旦建立,则将被保存在数据库中,便于用户随时,反复地调用和执行。如果不再需要该存储过程,可以将其删除。删除存储过程statistic_mark。 DROP PROCEDURE statistic_mark;(1) 删除存储过程collect_avg,DROP PROCEDURE collect_avg,(2) 删除存储过程 change_critical;DROP PROCEDURE change_critical; 四、 总结与体会熟悉使用存储过程,对数据库应用程序的设计有进一步了解,存储过程不像解释执行的SQL语句那样在提出操作请求时才进行语法分析和优化工作,因而运行效率高,降低了客户机和服务器之间的通信量。