使用PL/SQL编写存储过程访问数据库

1实验目的

熟悉使用存储过程来进行数据库应用程序的设计。

2实验平台和实验工具

KingbaseES数据库管理系统,KingbaseES PL/SQL执行引擎。

3实验内容及要求

对学生课程数据库,编写存储过程,完成下面功能:

  • 统计离散数学的成绩分布情况,即按照各分数段统计人数;
  • 统计任意一门课的平均成绩。
  • 将学生选课成绩从百分制改为等级制(即A、B、C、D、E);

要求:提交源程序并标识必要的注释。保证程序能正确编译和运行,认真填写实验报告。

4实验步骤

1. 创建存储过程

a. 创建需要的表结构
因为存储过程执行后在客户端并没有返回值,因此需要建立一个表来存放执行后的结果,并返回客户端显示。
根据实验要求,要统计选修“离散数学”课程的学生的成绩分布,因此急需要建立表Rank。

create table rank(division char(20), number int);

b. 编写存储过程


create or replace procedure statistic_mark(name char(40))
AS

DECLARE
less60  INT :=0;
b60a70  INT :=0;
b70a80  INT :=0;
b80a90 INT :=0;
more90 INT :=0;

curcno CHAR(4);
begin

select cno INTO curcno  /*查找课程名称对应的课程号*/
from course
where cname = name;

SELECT count(*) INTO less60     /*查询成绩小于60的学生人数,并存在变量less60中*/
FROM SC
WHERE cno =curcno AND grade <60;

SELECT count(*)  INTO b60a70    /*查询成绩在60~70的学生人数,并存在变量b60a70中*/
FROM SC
WHERE cno =curcno AND grade >=60 AND grade<70;

SELECT count(*)  INTO b70a80    /*查询成绩在70~80的学生人数,并存在变量b70a80中*/
FROM SC
WHERE cno =curcno AND grade >=70 AND grade<80;

SELECT count(*)  INTO b80a90    /*查询成绩在80~90的学生人数,并存在变量b80a90中*/
FROM SC
WHERE cno =curcno AND grade >=80 AND grade<90;

SELECT count(*)  INTO more90    /*查询成绩大于90的学生人数,并存在变量more90中*/
FROM SC
WHERE cno =curcno AND grade >=90 ;

INSERT  INTO  RANK VALUES('[0,60)',less60); /*将查询到结果插入Rank表中*/
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;

call statistic_mark('离散数学')

c. 执行存储过程

先插入一些数据:
插入课程 离散数学:
(‘8’, ‘离散数学’, ‘1’, ‘1’)

插入用来测试的学号为插入用来测试的学号为 201215125~201215135 的学生数据,举例:

(‘201215125’, ‘测试者’, ‘男’, ‘18’, ‘CS’)

插入sc表:
(‘201215125’, ‘8’, ‘59’)

insert into student values('201215125', '测试者1', '男', '18', 'CS');
insert into student values('201215126', '测试者2', '男', '18', 'CS');
insert into student values('201215127', '测试者3', '男', '18', 'CS');
insert into student values('201215128', '测试者4', '男', '18', 'CS');
insert into student values('201215129', '测试者5', '男', '18', 'CS');
insert into student values('201215130', '测试者6', '男', '18', 'CS');
insert into student values('201215131', '测试者7', '男', '18', 'CS');
insert into student values('201215132', '测试者8', '男', '18', 'CS');
insert into student values('201215133', '测试者9', '男', '18', 'CS');
insert into student values('201215134', '测试者10', '男', '18', 'CS');
insert into student values('201215135', '测试者11', '男', '18', 'CS');

insert into course values('8', '离散数学', '1', '1');

insert into sc values('201215125', '8', '51');
insert into sc values('201215126', '8', '61');
insert into sc values('201215127', '8', '62');
insert into sc values('201215128', '8', '71');
insert into sc values('201215129', '8', '72');
insert into sc values('201215130', '8', '73');
insert into sc values('201215131', '8', '81');
insert into sc values('201215132', '8', '82');
insert into sc values('201215133', '8', '83');
insert into sc values('201215134', '8', '84');
insert into sc values('201215135', '8', '90');

执行存储过程:

call statistic_mark('离散数学');
select* from rank;

2. 统计任意一门课程的平均成绩

a. 创建需要的表结构

create table avggrade(
cname char(40),
avg numeric(10,6));

b. 创建存储过程,执行并查看avggrade表中的结果

已执行成功的命令,从输入框完整复制

create or replace procedure collect_avg()
as
declare                                     /*声明变量*/
    curname   char(40);
    curcno     char(4);
    curavgg   numeric(10,6);
cursor mycursor for                         /*声明不带参数的游标mycursor查询课程号和课程名称*/
    select cno,cname from course;


begin 
open mycursor;

        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;

delete from avggrade;
call collect_avg();

select * from avggrade;

3. 将学生选课成绩从百分制改为等级制(即A、B、C、D、E)

创建存储过程并执行

create or replace procedure change_critical()
AS
DECLARE
  chgrade CHAR(1);
  currecord record;
  cursno char(9);
  curcno char(4);
  curgrade smallint;

BEGIN
/*ALTER TABLE SC ADD COLUMN(newgrade CHAR(1));*/ /*只有第一次执行的时候需要增加列*/
FOR cursno,curcno,curgrade in 
(select sno,cno,grade from sc)
loop
IF curgrade<60 then
  chgrade ='E';
ELSIF  curgrade<70 then
  chgrade ='D';
ELSIF  curgrade<80 then
  chgrade ='C';
ELSIF  curgrade<90 then
  chgrade ='B';
ELSE
  chgrade ='A';
END IF;

update sc 
set newgrade = chgrade
where sno = cursno and cno = curcno;

end loop;
/* ALTER TABLE SC DROP COLUMN grade; */ /*删了是傻逼*/
/* ALTER TABLE SC RENAME newgrade TO grade1; *//*kingbase里面有这个操作吗*/

END;

call change_critical;

select * from sc;

5实验中出现的问题

1. 执行存储过程失败:执行perform procedure statistic_mark('离散数学');

报错:
[KingbaseES Server]ERROR: 语法错误 在 “perform” 附近 Line 1 at SQL statement

修改执行语句为:call statistic_mark('离散数学');

出现如下错误:

执行语句:1
1. call statistic_mark(‘离散数学’);

执行成功: 否
执行耗时: 86 毫秒
服务器消息:
[KingbaseES Server]ERROR: plsql执行失败

修改执行语句为:call statistic_mark("离散数学");

报错:[KingbaseES Server]ERROR: 列 “离散数学” 不存在 Line 1 at SQL statement

与刚才更新数据时提示的一样。需要把双引号改成单引号。(???)

回到了之前的报错。说明存储过程内部执行语句报错。

说明是plsql语句内部的错误,没有调试功能,逐一测试代码,最后发现两个错,一个是kingbase不适用的语句,一个是变量名敲错了。

2. 删除存储过程报错

去掉过程名的括号
drop procedure STATISTIC_MARK;

6实验总结

有几个小坑,花了一点时间,有一个plsql语句排错的笨方法:

就是创建存储过程的时候,不要一开始把所有语句都加进去,一句一句地加,直到执行失败,这样就可以很快定位到错误语句。

发布了18 篇原创文章 · 获赞 23 · 访问量 6万+
展开阅读全文

没有更多推荐了,返回首页

©️2019 CSDN 皮肤主题: 大白 设计师: CSDN官方博客

分享到微信朋友圈

×

扫一扫,手机浏览