第八章 使用PL SQL编写存储过程访问数据库

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

3、（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;more90 int :=0;curcno char(4);beginselect cno into curcnofrom coursewhere cname =name;if not founo then raise exception end if;select count(*) into

4、 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. 执行存储过程编写好存储过

5、程statistic_mark之后，在“查询分析器”中选择菜单中的“单事务执行”命令，这样系统就创建好了存储过程然后使用perform调用该过程，在表rank中查看执行的结果。perform procedure statistic_mark(离散数学)；select* from rank； (二)统计任意一门课程的平均成绩1.创建存储过程（1）创建需要的表结构。根据实验要求，要统计任意一门课程的平均成绩，因此需要建立表avggrade，其中第一列cname 显示被统计的课程名称，第二列avg显示选修了该课程的学生的平均成绩。create table avggrade（cname char(50

6、),avg numeric(10,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 mycur

7、sor 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. 执行存储过程首先执行编写好的存

8、储过程collect_avg,然后在表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 ad

9、d 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 an

10、d 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_mark。 drop procedure statistic_mark;(1) 删除存储过程collect_avg,drop procedure collect_avg,