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

第八章 使用PL SQL编写存储过程访问数据库 暨南大学本科 实验报告 化学实验报告单总流体力学实验报告观察种子结构实验报告观察种子结构实验报告单观察种子的结构实验报告单 专用纸 课程名称 数据库系统 成绩评定 实验项目名称 使用PL/SQL编写存储过程访问数据库 指导教师 实验项目编号 实验项目类型 实验地点 学生姓名 学号 学院 系 专业 实验时间 2012年 月 日下午 一、 实验目的 熟悉使用存储过程来进行数据库应用程序的 设计 领导形象设计圆作业设计ao工艺污水处理厂设计附属工程施工组织设计清扫机器人结构设计 。 二、 实验 内容 财务内部控制制度的内容财务内部控制制度的内容人员招聘与配置的内容项目成本控制的内容消防安全演练内容 对学生-课程数据库，编写存储过程，完成下面功能: 1.统计离散数学的成绩分布情况，即按照各分数段统计人数; 2.统计任意一门课的平均成绩; 3.将学生选课成绩从百分制改为等级制(即A、B、C、D、E)。 要求:提交源程序并 表 关于同志近三年现实表现材料材料类招标技术评分表图表与交易pdf视力表打印pdf用图表说话 pdf 示必要的注释。保证程序能正确编译和运行，认真填写实验报告。 三、 实验步骤 实验之前，已经建立数据库，有student，course和sc三张基本表，登陆用户名为'tiger',密码为‘tiger’。接下来的实验就是对该数据库编写存储过程。 (一)统计“离散数学”课程的成绩分布情况 1.创建存储过程 (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; b60a70 INT :=0; b70a80 INT :=0; b80b90 INT :=0; more90 INT :=0; curcno CHAR(4); 暨南大学本科实验报告专用纸(附页) begin select cno INTO curcno from course where cname =name; IF NOT FOUNO THEN RAISE EXCEPTION END IF; SELECT count(*) INTO less60 FROM SC WHERE cno =curcno AND grade <60; SELECT count(*) INTO b60a70 FROM SC WHERE cno =curcno AND grade >=60 AND grade<70; SELECT count(*) INTO b70a80 FROM SC WHERE cno =curcno AND grade >=70 AND grade<80; SELECT count(*) INTO b80a90 FROM SC WHERE cno =curcno AND grade >=80 AND grade<90; SELECT count(*) INTO more90 FROM SC WHERE cno =curcno 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 显示被统计的课程名称，第二列avg显示选修了该课程的学生的平均成绩。 create table avggrade( cname char(50), avg numeric(10,6)); (3)编写存储过程 create or replace procedure collect_avg() as declare //声明变量 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_avg,然后在表avggrade中查看执行结果。 PERFORM PROCEDURE collect_avg(); SELECT * FROM avggrade; (三)在表SC中将学生选课成绩从百分制改为等级制 1.创建存储过程 根据实验要求，本实验中存储过程的执行不需要在客户端返回结果，因此不需要建立相应的表结构来存放存储过程的执行结果。直接编写存储过程。 create or replace procedure change_critical() AS DECLARE chgrade CHAR(1); currecord record; BEGIN 暨南大学本科实验报告专用纸(附页) ALTER TABLE SC ADD COLUMN(newgrade CHAR(1)); FOR currecord IN SELECT*FROM SC LOOP IF currecord.grade<60 then chgrade ='E'; ELSIF currecord.grade<70 then chgrade ='D'; ELSIF currecord.grade<80 then chgrade ='C; ELSIF currecord.grade<90 then chgrade ='B'; ELSE chgrade ='A'; END IF; UPDATE SC SET newgrade =chgrade WHERE sno =currecord.sno AND cno=currecord.cno; END LOOP; ALTER TABLE SC DROP COLUMN grade; ALTER TABLE SC RENAME newgrade TO grade; END; 2执行存储过程 PERFORM PROCEDURE change_critical(); (四)删除存储过程 存储过程一旦建立，则将被保存在数据库中，便于用户随时，反复地调用和执行。如果不再需要该存储过程，可以将其删除。 删除存储过程statistic_mark。 DROP PROCEDURE statistic_mark; (1)删除存储过程collect_avg, DROP PROCEDURE collect_avg, (2)删除存储过程 change_critical; DROP PROCEDURE change_critical; 四、 总结与体会 熟悉使用存储过程，对数据库应用程序的设计有进一步了解，存储过程不像解释执行的SQL语句那样在提出操作请求时才进行语法分析和优化工作，因而运行效率高，降低了客户机和服务器之间的通信量。