实验三 存储过程与触发器

实验目的：

学习SQL语言进行编程的基本方法与技术，能够编写存储过程、触发器解决数据库需要处理的复杂问题。

实验内容：

1、 设计一个存储过程或者自定义函数，练习存储过程的设计方法。
2、 设计触发器，理解触发器的工作原理与设计方法。

实验过程及要求：

1、 编写存储过程，传入学号，查询该同学所有选修记录，结果显示信息项包括学号、姓名、班级名称、课程名、学分、成绩.
2、撰写存储过程，完成以下操作代码写在空白处。
（1）插入数据学院2020级计算机科学技术1班、计算机科学技术2班记录。
（2）针对2个班级，产生学生的记录插入到学生信息表中，每班学生数30人。
3、编写触发器：当在学生表中更新（增、删、改）学生记录时，计算学生班级总人数，并更新班级表中对应的记录中

实验具体操作

1、 编写存储过程，传入学号，查询该同学所有选修记录，结果显示信息项包括学号、姓名、班级名称、课程名、学分、成绩.

DROP PROCEDURE if exists GetStudentCourseRecords;
DELIMITER //

CREATE PROCEDURE GetStudentCourseRecords(IN student_id VARCHAR(9))
BEGIN
    SELECT s.Sid, s.Sname, g.gname, c.Cname, c.credit, sc.score1,sc.score2
    FROM ustudent s
    INNER JOIN ugrade g ON s.gid = g.gid
    INNER JOIN usc sc ON s.Sid = sc.sid
    INNER JOIN ucourse c ON sc.cid = c.Cid
    WHERE s.Sid = student_id;
END //

DELIMITER ;

call GetStudentCourseRecords('012005004');

2、撰写存储过程，完成以下操作代码写在空白处。
1）插入数据学院2020级计算机科学技术1班、计算机科学技术2班记录。
2）针对2个班级，产生学生的记录插入到学生信息表中，每班学生数30人。

DELIMITER //
DROP PROCEDURE if exists InsertClassData;
CREATE PROCEDURE InsertClassData()
BEGIN
    DECLARE i INT DEFAULT 1;
    DECLARE j INT DEFAULT 1;
    DECLARE random_sex varchar(5);


    -- 1) 插入数据学院2020级计算机科学技术1班、计算机科学技术2班记录
    WHILE i <= 2
        DO
            INSERT INTO ugrade (gid, gname, gyear)
            VALUES (CONCAT('20', LPAD(i, 2, '0')), CONCAT('计算机科学技术', i, '班'), 2020);

            SET i = i + 1;
        END WHILE;

    -- 2) 产生学生的记录插入到学生信息表中 每班学生数30人
    SET i = 1;
    WHILE i <= 2
        DO
            SELECT IF(RAND() < 0.5, '男', '女') INTO random_sex;

            WHILE j <= 30
                DO
                    INSERT INTO ustudent (Sid, Sname, Ssexy, Sbdate, gid, stele)
                    VALUES (CONCAT('S', LPAD(j, 7, '0')), CONCAT('stu', LPAD(j, 3, '0')), random_sex, '2002-01-01',
                            CONCAT('20', LPAD(i, 2, '0')), '1234567890');

                    SET j = j + 1;
                END WHILE;

            SET i = i + 1;
        END WHILE;
END //

DELIMITER ;


call InsertClassData()

执行之前表内容

ugrade

ustudent

执行之后

3、编写触发器：当在学生表中更新（增、删、改）学生记录时，计算学生班级总人数，并更新班级表中对应的记录中

DELIMITER //

CREATE TRIGGER UpdateClassStudentCount
AFTER INSERT ON ustudent
FOR EACH ROW
BEGIN
    DECLARE class_id VARCHAR(2);
    DECLARE student_count INT;

    -- class id
    SET class_id = NEW.gid;

    -- count of students in the class
    SELECT COUNT(*) INTO student_count FROM ustudent WHERE gid = class_id;

    -- ugrade table
    UPDATE ugrade SET gyear = student_count WHERE gid = class_id;
END //

DELIMITER ;