数据库系统原理MYSQL

【MySQL】实验05 存储过程

avatar

azurekiln

实验五 存储过程

一. 简答题（共17题，100分）

  1. (简答题, 2分) 本实验的实验目的是：

    掌握存储过程的概念、功能；
    学会创建和调用存储过程；
    知道如何删除存储过程。

  2. (简答题, 7分)
    请写出下列创建存储过程的MySQL语句，存储过程名命名为PROC_StuRepInfo_学号最后两位
    利用学生学号查询该名学生各学期的选修课程情况，要求显示学生的学号，姓名，选修的学年，学期，选修的课程号，课程名及其成绩，显示结果按学年和学期升序排列。

    USE Jxdb59;
DELIMITER //
# 创建存储过程
CREATE PROCEDURE PROC_StuRepInfo_59(IN p_sno CHAR(12))
BEGIN
# 要求显示学生的学号，姓名，选修的学年，学期，选修的课程号，课程名及其成绩
SELECT s.Sno, s.Sname, r.Racademicyear, r.Rterm, c.Cno, c.Cname, r.Grade
FROM Students59 s
JOIN Reports59 r ON s.Sno = r.Sno
JOIN Courses59 c ON r.Cno = c.Cno
WHERE s.Sno = p_sno
# 显示结果按学年和学期升序排列
ORDER BY r.Racademicyear, r.Rterm;
END //
DELIMITER ;
# 调用查询
CALL PROC_StuRepInfo_59('202003005103');

  3. (简答题, 2分)
    请写出下列调用存储过程PROC_StuRepInfo_学号最后两位的MySQL语句，并将执行结果截图，截图的右下角要求标上你的学号最后两位
    查询学号为202103005101的选课情况

    USE Jxdb59;
CALL PROC_StuRepInfo_59('202103005101');

  4. (简答题, 7分)
    请写出下列创建存储过程的MySQL语句，存储过程名命名为PROC_StuGrade_学号最后两位
    查询某位学生指定课程的成绩和可获得这门课程的学分
    （注：若该学生的课程成绩小于60分或是为空，则学分要显示为0分）

    USE Jxdb59;
DELIMITER //
CREATE PROCEDURE PROC_StuGrade_59(IN p_sno CHAR(12), IN p_sname VARCHAR(4),
IN p_cno CHAR(8), IN p_cname VARCHAR(20))
BEGIN
# 查询某位学生指定课程的成绩和可获得这门课程的学分
SELECT r.Grade,
       IF(r.Grade >= 60, c.Ccredit, 0) AS Credit
FROM Students59 s
JOIN Reports59 r ON s.Sno = r.Sno
JOIN Courses59 c ON r.Cno = c.Cno
WHERE (s.Sno = p_sno OR s.Sname = p_sname)
  AND (c.Cno = p_cno OR c.Cname = p_cname);
END //
DELIMITER ;

  5. (简答题, 4分)
    请写出下列调用存储过程PROC_StuGrade_学号最后两位的MySQL语句，并将执行结果截图，截图的右下角要求标上你的学号最后两位
    （1）查询'张建国'选修的'数据库系统原理'课程的成绩和学分
    （2）查询'朱玉玲'选修的'计算机科学概论'课程的成绩和学分

    USE Jxdb59;
CALL PROC_StuGrade_59(NULL, '张建国', NULL, '数据库系统原理');
CALL PROC_StuGrade_59(NULL, '朱玉玲', NULL, '计算机科学概论');


  6. (简答题, 7分)
    请写出下列创建存储过程的MySQL语句，存储过程名命名为PROC_DepNativeInfo_学号最后两位
    统计指定入学年级指定学院名中，各省的生源人数，要求显示入学年级，学院名，生源地（只显示省份）及生源人数。

    USE Jxdb59;
DELIMITER //
CREATE PROCEDURE PROC_DepNativeInfo_59(IN p_grade VARCHAR(4), IN p_dname VARCHAR(15))
BEGIN
SELECT p_grade AS Grade,
       p_dname AS Department,
       SUBSTRING_INDEX(s.Snative, '省', 1) AS Province,
       COUNT(*) AS StuCount
FROM Students59 s
JOIN Department59 d ON s.Dno = d.Dno
WHERE LEFT(s.Sno, 4) = p_grade AND d.Dname = p_dname
GROUP BY SUBSTRING_INDEX(s.Snative, '省', 1);
END //
DELIMITER ;

  7. (简答题, 2分)
    请写出下列调用存储过程PROC_DepNativeInfo_学号最后两位的MySQL语句，并将执行结果截图，截图的右下角要求标上你的学号最后两位
    查询2020级计算机与信息工程学院的生源情况

    USE Jxdb59;
CALL PROC_DepNativeInfo_59('2020', '计算机与信息工程学院');

  8. (简答题, 9分)
    （1）请写出下列函数的MySQL语句，函数名命名为FUNC_GradeSegment_学号最后两位(Score TINYINT)
    其中，参数Score为0-100的整数，即学生成绩，要求该函数返回学生成绩Score所处的分值段，
    分支段分为：90-100, 80-89, 70-79, 60-69, 0-59），若此学生的成绩为NULL，即还未考试，则返回“未考”
    （2）请写出下列创建存储过程的MySQL语句，存储过程名命名为PROC_StuGradeNum_学号最后两位
    统计指定班级指定课程的考试情况，要求显示出不同分值段以及尚未考试的学生人数，如（90-100, 2人；80-89, 7人；…未考, 2人）。

    USE Jxdb59;
# 创建函数
DELIMITER //
CREATE FUNCTION FUNC_GradeSegment_59(Score TINYINT)
RETURNS VARCHAR(10)
DETERMINISTIC
BEGIN
IF Score IS NULL THEN RETURN '未考';
ELSEIF Score >= 90 THEN RETURN '90~100';
ELSEIF Score >= 80 THEN RETURN '80~89';
ELSEIF Score >= 70 THEN RETURN '70~79';
ELSEIF Score >= 60 THEN RETURN '60~69';
ELSE RETURN '0~59';
END IF;
END //
DELIMITER ;
# 创建存储过程
DELIMITER //
CREATE PROCEDURE PROC_StuGradeNum_59(
IN p_class_no VARCHAR(20),
IN p_cname VARCHAR(50)
)
BEGIN
SELECT FUNC_GradeSegment_59(r.Grade) AS Segment,
       COUNT(*) AS Count
FROM Students59 s
JOIN Reports59 r ON s.Sno = r.Sno
JOIN Courses59 c ON r.Cno = c.Cno
WHERE s.Sclass = p_class_no AND c.Cname = p_cname
GROUP BY FUNC_GradeSegment_59(r.Grade);
END //
DELIMITER ;

  9. (简答题, 2分)
    请写出下列调用存储过程PROC_StuGradeNum_学号最后两位的MySQL语句，并将执行结果截图，截图的右下角要求标上你的学号最后两位
    查询2021160261班计算机科学概论的考试情况

    USE Jxdb59;
CALL PROC_StuGradeNum_59('2021160261', '计算机科学概论');

  10. (简答题, 9分)
    请写出下列创建存储过程的MySQL语句，存储过程名命名为PROC_StuCreditInfo_学号最后两位
    利用学生学号查询该名学生在指定学年，指定学期所获得的学分数以及还未获得的学分数，若该门课程尚无成绩或成绩小于60分，则为尚未获得的学分，结果以变量形式输出（OUTPUT）。

    USE Jxdb59;
DELIMITER //
CREATE PROCEDURE PROC_StuCreditInfo_59(
# 输入
IN p_sno CHAR(12),
IN p_year CHAR(4),
IN p_semester CHAR(1),
# 输出
OUT p_got TINYINT,
OUT p_notgot TINYINT
)
BEGIN
SELECT IFNULL(SUM(IF(r.Grade >= 60, c.Ccredit, 0)), 0),
       IFNULL(SUM(IF(r.Grade < 60 OR r.Grade IS NULL, c.Ccredit, 0)), 0)
INTO p_got, p_notgot
FROM Reports59 r
JOIN Courses59 c ON r.Cno = c.Cno
WHERE r.Sno = p_sno AND r.Racademicyear = p_year AND r.Rterm = p_semester;
END //
DELIMITER ;

  11. (简答题, 4分)
    请写出下列调用存储过程PROC_StuCreditInfo_学号最后两位的MySQL语句，并将执行结果截图，截图的右下角要求标上你的学号最后两位
    （1）查询学号为202203038101的学生2023年第2学期的学分情况
    （2）查询学号为202003005104的学生2020年第1学期的学分情况
    要求：若已获学分或未获学分返回为NULL，则结果要显示为0

    USE Jxdb59;
# (1) 查询学号为202203038101的学生2023年第2学期的学分情况
SET @got = 0, @notgot = 0;
CALL PROC_StuCreditInfo_59('202203038101', '2023', 2, @got, @notgot);
SELECT IFNULL(@got, 0) AS 已获学分, IFNULL(@notgot, 0) AS 未获学分;
# (2) 查询学号为202003005104的学生2020年第1学期的学分情况
SET @got = 0, @notgot = 0;
CALL PROC_StuCreditInfo_59('202003005104', '2020', 1, @got, @notgot);
SELECT IFNULL(@got, 0) AS 已获学分, IFNULL(@notgot, 0) AS 未获学分;


  12. (简答题, 8分)
    请写出下列创建存储过程的MySQL语句，存储过程名命名为PROC_TeaWork_学号最后两位
    统计指定教师指定学年的教学工作量(教学工作量=所有教授的课程学时数的总和)，教学工作量要求作为输出参数（OUT）

    USE Jxdb59;
DELIMITER //
CREATE PROCEDURE PROC_TeaWork_59(
IN p_tname VARCHAR(4),
IN p_year VARCHAR(4),
OUT p_workload INT
)
BEGIN
SELECT IFNULL(SUM(c.Chours), 0) INTO p_workload
FROM Teachers59 t
JOIN Tutors59 te ON t.Tno = te.Tno
JOIN Courses59 c ON te.Cno = c.Cno
WHERE t.Tname = p_tname AND te.Tacademicyear = p_year;
END //
DELIMITER ;

  13. (简答题, 4分)
    请写出下列调用存储过程PROC_TeaWork_学号最后两位的MySQL语句，并将执行结果截图，截图的右下角要求标上你的学号最后两位
    （1）查询教师刘伟2024学年的教学工作量
    （2）查询教师王一凡2022学年的教学工作量
    要求：若教学工作量返回为NULL，则结果要显示为0

    USE Jxdb59;
# (1) 查询教师刘伟2024学年的教学工作量
SET @workload = 0;
CALL PROC_TeaWork_59('刘伟', '2024', @workload);
SELECT IFNULL(@workload, 0) AS 教学工作量;
# (2) 查询教师王一凡2022学年的教学工作量
SET @workload = 0;
CALL PROC_TeaWork_59('王一凡', '2022', @workload);
SELECT IFNULL(@workload, 0) AS 教学工作量;


  14. (简答题, 10分)
    请写出下列创建存储过程的MySQL语句，存储过程名命名为PROC_TeaTax_学号最后两位
    统计指定教师每月需要缴纳的税金是多少，并将统计结果以变量形式输出（OUT）
    如果是中级以下职称（含中级），则税金=（工资+岗位津贴-1500）5%；
    如果是副高级职称，则税金=（工资+岗位津贴-2000）    5%；
    如果是正高级职称，则税金=（工资+岗位津贴-2500）*5%
    提示：IF… ELSE…
    注意：返回的税金应是实数

    USE Jxdb59;
DELIMITER //
CREATE PROCEDURE PROC_TeaTax_59(
IN p_tname VARCHAR(20),
OUT p_tax DECIMAL(10,2)
)
BEGIN
DECLARE v_prof VARCHAR(8);
DECLARE v_salary DECIMAL(10,2);
DECLARE v_allowance DECIMAL(10,2);

SELECT Tprof, Tsal, Tcomm INTO v_prof, v_salary, v_allowance FROM Teachers59 WHERE Tname = p_tname;

IF v_prof IN ('教授') THEN
    SET p_tax = (v_salary + v_allowance - 2500) * 0.05;
ELSEIF v_prof IN ('副教授') THEN
    SET p_tax = (v_salary + v_allowance - 2000) * 0.05;
ELSE
    SET p_tax = (v_salary + v_allowance - 1500) * 0.05;
END IF;
END //
DELIMITER ;

  15. (简答题, 4分)
    请写出下列调用存储过程PROC_TeaTax_学号最后两位的MySQL语句，并将执行结果截图，截图的右下角要求标上你的学号最后两位
    （1）查询教师张雪应缴纳的税金
    （2）查询教师田锋应缴纳的税金
    要求：显示的税金只保留小数点后两位

    USE Jxdb59;
# (1) 查询教师张雪应缴纳的税金
SET @tax = 0;
CALL PROC_TeaTax_59('张雪', @tax);
SELECT ROUND(@tax, 2) AS 应缴税金;
# (2) 查询教师田锋应缴纳的税金
SET @tax = 0;
CALL PROC_TeaTax_59('田锋', @tax);
SELECT ROUND(@tax, 2) AS 应缴税金;

  16. (简答题, 15分)
    请写出下列创建存储过程的MySQL语句(选课情况综合查询)，存储过程名命名为PROC_StuRepSel_学号最后两位
    此存储过程可按照用户指定的查询字段，包括学号，学生姓名，课程编号，课程名称、学年或班级编号查询学生的选课情况，查询结果显示(班级编号，学号，学生姓名，课程编号，课程名称，选修学年，选修学期，成绩)
    提示：需要两个输入参数，一个参数用来指定要查询的字段，另一个参数用来指定要查询字段的具体值，存储过程中需要根据查询字段不同，分别写出对应的查询语句

    USE Jxdb59;
DELIMITER //
CREATE PROCEDURE PROC_StuRepSel_59(
IN p_field VARCHAR(20),
IN p_value VARCHAR(50)
)
BEGIN
SET @sql = CONCAT(
    'SELECT s.Sclass, s.Sno, s.Sname, c.Cno, c.Cname, r.Racademicyear, r.Rterm, r.Grade ',
    'FROM Students59 s ',
    'JOIN Reports59 r ON s.Sno = r.Sno ',
    'JOIN Courses59 c ON r.Cno = c.Cno ',
    'WHERE ',
    CASE p_field
        WHEN '学号' THEN CONCAT('s.Sno = ''', p_value, '''')
        WHEN '学生姓名' THEN CONCAT('s.Sname = ''', p_value, '''')
        WHEN '课程编号' THEN CONCAT('c.Cno = ''', p_value, '''')
        WHEN '课程名称' THEN CONCAT('c.Cname = ''', p_value, '''')
        WHEN '学年' THEN CONCAT('r.Racademicyear = ''', p_value, '''')
        WHEN '班级编号' THEN CONCAT('s.Sclass = ''', p_value, '''')
    END
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END //
DELIMITER ;

  17. (简答题, 4分)
    请写出下列调用存储过程PROC_StuRepSel_学号最后两位的MySQL语句，并将执行结果截图，截图的右下角要求标上你的学号最后两位
    （1）查询学号为202003005106的学生的选课情况
    （2）查询20022030381班的学生的选课情况

    USE Jxdb59;
# (1) 查询学号为202003005106的学生的选课情况
CALL PROC_StuRepSel_59('学号', '202003005106');
# (2) 查询2002030381班的学生的选课情况
CALL PROC_StuRepSel_59('班级编号', '2002030381');

扫描二维码，在手机上阅读
收藏
上一篇 【MySQL】实验07 数据库的安全性保护
下一篇 第六章 CPU
powered by emlog pro
服务器供应商 新区云数据

友情链接
白衣Ink


sitemap