数据库作业14：第五章： 数据库完整性 习题 + 存储过程

关系模式
Staff（Sno，Sname，Sage，Post，Pay，Dno）
Dept（Dno，Dname，Mname，Tel）

CREATE TABLE Dept
       (Dno   CHAR(9)  PRIMARY KEY,--主码
        Dname CHAR(20),     
        Mname CHAR(20),
        Tel   CHAR(20)
       );
       
CREATE TABLE Staff
       (Sno   CHAR(9)  PRIMARY KEY,--主码
        Sname CHAR(20),     
        Ssex  CHAR(2),
        Sage  SMALLINT,
        CONSTRAINT C1 CHECK(Sage<=60),--约束不超过60
        Post  CHAR(20),
        Pay   CHAR(20),
        Dno   CHAR(9),
        FOREIGN KEY (Dno) REFERENCES Dept(Dno)--参照完整性
       );

(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)
创建存储过程并执行

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;