Lab3 存储过程与触发器
实验三 存储过程与触发器
实验目的:
学习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 ;