存储过程
目录
一、实验目的及要求
(1)能够应用某一具体DBMS支持的编程语言和编程规范,规范设计存储过程。
(2)根据数据库应用系统的设计需要,执行存储过程等工具对采集到的实验数据进行整理、分析和解释,得出有效结论。
二、实验环境
操作系统:Windows
应用软件:MySQL5.6 Navicat 12
三、实验内容
有一个学生数据库,结构如下:
请使用存储过程完成以下操作:
1. 创建存储过程proce_avg_prade统计指定课程的平均成绩;
2. 统计数据库原理与应用课程的成绩分布情况,即按照各分数段统计人数,分数段有[100,90]、(90,80]、(80,70]、(70,60]和(60,0]等五段;
3. 将学生选课成绩从百分制改为等级制(即[100,90]A、(90,80]B、(80,70]C、(70,60]D、(60,0]E)显示。
四、实验步骤
1. 创建存储过程proce_avg_prade统计指定课程的平均成绩;
运行代码:
DELIMITER $
CREATE PROCEDURE p_Avg_Grade(IN courses VARCHAR(20), OUT avgnum DECIMAL(5,2))
BEGIN
SELECT AVG(sc.Grade) INTO avgnum
FROM sc
JOIN course ON sc.Cno = course.Cno
WHERE course.CName = courses;
END $
DELIMITER ;
运行结果
2. 统计数据库原理与应用课程的成绩分布情况,即按照各分数段统计人数,分数段有[100,90]、(90,80]、(80,70]、(70,60]和(60,0]等五段;
创建rank表:
create table rank(
division char(66),
sub_sum int
);
insert into rank(division)
VALUES ('[0,60)'),('[60,70]'),('[70,80]'),('[80,90]'),('[90,100]');
运行代码:
DELIMITER $
CREATE PROCEDURE printcourse (IN pcname CHAR(20))
BEGIN
DECLARE pcno CHAR(20);
SELECT Cno INTO pcno FROM Course WHERE Cname = pcname LIMIT 1;
IF pcno IS NULL THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '您输入的课程号不存在,请重新输入!';
ELSE
-- 更新Rank表的sub_sum字段,使用JOIN来计算每个分段的数量
UPDATE Rank r JOIN (
SELECT Cno, COUNT(*) as sub_count
FROM SC
WHERE Grade < 60 AND Cno = pcno
GROUP BY Cno
) sc ON r.division = '[0,60)'
SET r.sub_sum = sc.sub_count;
UPDATE Rank r JOIN (
SELECT Cno, COUNT(*) as sub_count
FROM SC
WHERE Grade >= 60 AND Grade < 70 AND Cno = pcno
GROUP BY Cno
) sc ON r.division = '[60,70)'
SET r.sub_sum = sc.sub_count;
UPDATE Rank r JOIN (
SELECT Cno, COUNT(*) as sub_count
FROM SC
WHERE Grade >= 70 AND Grade < 80 AND Cno = pcno
GROUP BY Cno
) sc ON r.division = '[70,80)'
SET r.sub_sum = sc.sub_count;
UPDATE Rank r JOIN (
SELECT Cno, COUNT(*) as sub_count
FROM SC
WHERE Grade >= 80 AND Grade < 90 AND Cno = pcno
GROUP BY Cno
) sc ON r.division = '[80,90)'
SET r.sub_sum = sc.sub_count;
UPDATE Rank r JOIN (
SELECT Cno, COUNT(*) as sub_count
FROM SC
WHERE Grade = 90 AND Grade <= 100 AND Cno = pcno
GROUP BY Cno
) sc ON r.division = '[90,100)'
SET r.sub_sum = sc.sub_count;
END IF;
END $
DELIMITER ;
查询结果
3. 将学生选课成绩从百分制改为等级制(即[100,90]A、(90,80]B、(80,70]C、(70,60]D、(60,0]E)显示。
先把分数属性定义为字符
代码:
ALTER TABLE SC MODIFY Grade CHAR(5);
DELIMITER $
CREATE PROCEDURE ConvertGradesToLevel()
BEGIN
UPDATE SC
SET Grade = CASE
WHEN Grade >= 90 THEN 'A'
WHEN Grade > 80 THEN 'B'
WHEN Grade > 70 THEN 'C'
WHEN Grade > 60 THEN 'D'
ELSE 'E'
END;
END $
DELIMITER ;
运行结果:
该学生数据库为stu_course,sql语句如下:
/*创建表student*/
CREATE TABLE Student
(
Sno CHAR(8) PRIMARY KEY, /* 列级完整性约束条件*/
Sname VARCHAR(20) ,
Ssex CHAR(6),
Sbirthdate Date,
Smajor VARCHAR(40)
);
/*创建表course*/
CREATE TABLE Course
(
Cno CHAR(5) PRIMARY KEY,
Cname VARCHAR(40) ,
Ccredit SMALLINT,
Cpno CHAR(5),
FOREIGN KEY (Cpno) REFERENCES Course(Cno)
);
/*创建表sc*/
CREATE TABLE SC
(
Sno CHAR(8),
Cno CHAR(5),
Grade SMALLINT, /*成绩*/
Semester CHAR(5), /*开课学期*/
Teachingclass CHAR(8), /*学生选修某一门课程所在的教学班*/
PRIMARY KEY (Sno,Cno), /* 主码由两个属性构成,表级约束*/
FOREIGN KEY (Sno) REFERENCES Student(Sno),
/* 表级完整性约束条件,Sno是外码,被参照表是Student */
FOREIGN KEY (Cno) REFERENCES Course(Cno)
/* 表级完整性约束条件, Cno是外码,被参照表是Course*/
);
/*向表中插入数据*/
INSERT INTO student VALUES ('20180001', '李勇', '男', '2000-03-08', '信息安全');
INSERT INTO student VALUES ('20180002', '刘晨', '女', '1999-09-01', '计算机科学与技术');
INSERT INTO student VALUES ('20180003', '王敏', '女', '2001-08-01', '计算机科学与技术');
INSERT INTO student VALUES ('20180004', '张立', '男', '2000-01-08', '计算机科学与技术');
INSERT INTO student VALUES ('20180005', '陈新奇', '男', '2001-11-01', '信息管理与信息系统');
INSERT INTO student VALUES ('20180006', '赵明', '男', '2000-06-12', '数据科学与大数据技术');
INSERT INTO student VALUES ('20180007', '王佳佳', '女', '2001-12-07', '数据科学与大数据技术');
INSERT INTO course VALUES ('81001', '程序设计基础与C语言', 4, NULL);
INSERT INTO course VALUES ('81002', '数据结构', 4, '81001');
INSERT INTO course VALUES ('81003', '数据库系统概论', 4, '81002');
INSERT INTO course VALUES ('81004', '信息系统概论', 4, '81003');
INSERT INTO course VALUES ('81005', '操作系统', 4, '81001');
INSERT INTO course VALUES ('81006', 'Python语言', 3, '81002');
INSERT INTO course VALUES ('81007', '离散数学', 4, NULL);
INSERT INTO course VALUES ('81008', '大数据技术概论', 4, '81003');
INSERT INTO sc VALUES ('20180001', '81001', 85, '20192', '81001-01');
INSERT INTO sc VALUES ('20180001', '81002', 96, '20201', '81002-01');
INSERT INTO sc VALUES ('20180001', '81003', 87, '20202', '81003-01');
INSERT INTO sc VALUES ('20180002', '81001', 80, '20192', '81001-02');
INSERT INTO sc VALUES ('20180002', '81002', 98, '20201', '81002-01');
INSERT INTO sc VALUES ('20180002', '81003', 71, '20202', '81003-02');
INSERT INTO sc VALUES ('20180003', '81001', 81, '20192', '81001-01');
INSERT INTO sc VALUES ('20180003', '81002', 76, '20201', '81002-02');
INSERT INTO sc VALUES ('20180004', '81001', 56, '20192', '81001-02');
INSERT INTO sc VALUES ('20180004', '81002', 97, '20201', '81002-02');
INSERT INTO sc VALUES ('20180005', '81003', 68, '20202', '81003-01');
INSERT INTO sc VALUES ('20180005', '81001', null, '20192', '81001-02');