Lab3 存储过程与触发器

lab3,存储,过程,触发器 · 浏览次数 : 0

小编点评

**实验三 存储过程与触发器实验目的:** **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');

img


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()

img

执行之前表内容

ugrade


img

ustudent
img


执行之后

img

img

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 ;


与Lab3 存储过程与触发器相似的内容: