小编点评

**实验三 存储过程与触发器实验目的：** **1. 设计存储过程** ```sql 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 ``` **2. 设计触发器** ```sql CREATE TRIGGER UpdateClassStudentCountAFTER INSERT ON ustudentFOR EACH ROW BEGIN DECLARE class_id VARCHAR(2); DECLARE student_count INT; -- 获取该班的学生数量 SET class_id = NEW.gid; SELECT COUNT(*) INTO student_count FROM ustudent WHERE gid = class_id; -- 更新班级表中的学生数量 UPDATE ugrade SET gyear = student_count WHERE gid = class_id; END ``` **实验过程及要求：** 1. 在数据库中创建表： ```sql CREATE TABLE ustudent ( Sid VARCHAR(9) PRIMARY KEY, Sname VARCHAR(30) NOT NULL, Ssexy VARCHAR(5), Sbdate DATE, gid VARCHAR(9), stele VARCHAR(30) ); CREATE TABLE ugrade ( gid VARCHAR(9) PRIMARY KEY, gname VARCHAR(30) NOT NULL, gyear INT DEFAULT 2020 ); CREATE TABLE usc ( sid VARCHAR(9) PRIMARY KEY, sid VARCHAR(9) NOT NULL, random_sex VARCHAR(5) DEFAULT '男' ); CREATE TABLE ucourse ( cid VARCHAR(9) PRIMARY KEY, cname VARCHAR(30) NOT NULL ); ``` 2. 插入数据： ```sql -- 插入数据学院2020级计算机科学技术1班、计算机科学技术2班记录 INSERT INTO ugrade (gid, gname, gyear) VALUES ('20200101', '计算机科学技术1班', 2020), ('20200105', '计算机科学技术2班', 2020); -- 产生学生的记录插入到学生信息表中，每班学生数30人 INSERT INTO ustudent (Sid, Sname, Ssexy, Sbdate, gid, stele) VALUES ('S1234567890', 'stu', '男', '2002-01-01', '20200101', '1234567890'), ('S9876543210', 'stu', '女', '2002-01-05', '20200105', '9876543210'); ``` 3. 编写触发器： ```sql -- 触发器更新班级表中的学生数量 CREATE TRIGGER UpdateClassStudentCountAFTER INSERT ON ustudentFOR EACH ROW BEGIN DECLARE class_id VARCHAR(2); DECLARE student_count INT; -- 获取该班的学生数量 SET class_id = NEW.gid; SELECT COUNT(*) INTO student_count FROM ustudent WHERE gid = class_id; -- 更新班级表中的学生数量 UPDATE ugrade SET gyear = student_count WHERE gid = class_id; END ``` **总结：** 完成以上实验可以学习如何设计存储过程和触发器，解决数据库中需要处理的复杂问题。

正文

          实验三 存储过程与触发器

实验目的：

学习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 ;