一、实验目的
1. 了解触发器的概念和使用方法；
2. 把握创建和使用触发器实现主动统计和更新数据的功能；
3. 学习编写存储过程，实现选修课程的添加和修改功能。
二、实验环境
数据库管理系统：MySQL
数据库名称：hbmustu
表布局：Student（学号，姓名），Course（课程号，课程名，学分），SC（学号，课程号，分数）
三、实验步骤
1. 创建student_avg, course_avg表，用于存储学生平均分和课程平均分。
CREATE TABLE student_avg (
student_id INT PRIMARY KEY,
avg_score FLOAT
);
CREATE TABLE course_avg (
course_id INT PRIMARY KEY,
avg_score FLOAT
);
2. 创建mylog表，用于纪录表变化日志。
CREATE TABLE mylog (
log_id INT PRIMARY KEY,
table_name VARCHAR(50),
time_stamp TIMESTAMP,
operation VARCHAR(50),
old_value VARCHAR(100),
new_value VARCHAR(100)
);
3. 创建触发器来监控SC表的变化
CREATE TRIGGER update_avg_score
AFTER INSERT OR UPDATE ON SC
FOR EACH ROW
BEGIN
DECLARE total_score INT;
DECLARE avg_score FLOAT;
DECLARE course_id INT;
SET total_score = (SELECT SUM(score) FROM SC WHERE student_id = NEW.student_id AND course_id = NEW.course_id);
SET avg_score = total_score / (SELECT COUNT(*) FROM SC WHERE student_id = NEW.student_id AND course_id = NEW.course_id);
UPDATE student_avg SET avg_score = avg_score WHERE student_id = NEW.student_id;
UPDATE course_avg SET avg_score = avg_score WHERE course_id = NEW.course_id;
INSERT INTO mylog (table_name, time_stamp, operation, old_value, new_value) VALUES ('SC', CURRENT_TIMESTAMP, 'INSERT OR UPDATE', '', '');
END;
4. 创建当Course表变化时主动统计总学分的触发器
CREATE TRIGGER update_total_credits
AFTER INSERT OR UPDATE ON Course
FOR EACH ROW
BEGIN
DECLARE total_credits INT;
SET total_credits = (SELECT SUM(credit) FROM Course);
INSERT INTO mylog (table_name, time_stamp, operation, old_value, new_value) VALUES ('Course', CURRENT_TIMESTAMP, 'INSERT OR UPDATE', '', total_credits);
END;
5. 创建当Course表变化时主动统计总学分的触发器。
CREATE TRIGGER update_total_credits
AFTER INSERT OR UPDATE ON Course
FOR EACH ROW
BEGIN
DECLARE total_credits INT;
SET total_credits = (SELECT SUM(credit) FROM Course);
INSERT INTO mylog (table_name, time_stamp, operation, old_value, new_value) VALUES ('Course', CURRENT_TIMESTAMP, 'INSERT OR UPDATE', '', total_credits);
END;
6. 创建当Student表、Course表、SC表发生变化时，写入到日志表mylog的触发器。
CREATE TRIGGER log_table_changes
AFTER INSERT OR UPDATE OR DELETE ON Student
FOR EACH ROW
BEGIN
DECLARE table_name VARCHAR(50);
SET table_name = 'Student';
INSERT INTO mylog (table_name, time_stamp, operation, old_value, new_value) VALUES (table_name, CURRENT_TIMESTAMP, 'INSERT OR UPDATE OR DELETE', '', '');
END;
CREATE TRIGGER log_table_changes_2
AFTER INSERT OR UPDATE OR DELETE ON Course
FOR EACH ROW
BEGIN
DECLARE table_name VARCHAR(50);
SET table_name = 'Course';
INSERT INTO mylog (table_name, time_stamp, operation, old_value, new_value) VALUES (table_name, CURRENT_TIMESTAMP, 'INSERT OR UPDATE OR DELETE', '', '');
END;
CREATE TRIGGER log_table_changes_3
AFTER INSERT OR UPDATE OR DELETE ON SC
FOR EACH ROW
BEGIN
DECLARE table_name VARCHAR(50);
SET table_name = 'SC';
INSERT INTO mylog (table_name, time_stamp, operation, old_value, new_value) VALUES (table_name, CURRENT_TIMESTAMP, 'INSERT OR UPDATE OR DELETE', '', '');
END;
7.创建选修课程存储过程。这个存储过程担当学号和课程号作为输入，并担当分数作为输入或输出。首先，我们需要创建一个表来存储选课信息。
CREATE TABLE course_selection (
student_id INT,
course_id INT,
score INT,
PRIMARY KEY (student_id, course_id)
);
创建存储过程
DELIMITER //
CREATE PROCEDURE register_course(IN student_id INT, IN course_id INT, IN score INT)
BEGIN
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
-- Error handling code goes here. For example:
-- PRINT 'An error occurred in the trigger. Exiting...';
ROLLBACK; -- Rollback any active transactions.
LEAVE; -- Exit the procedure.
END;
-- Normal code goes here. For example:
START TRANSACTION; -- Start a new transaction.
SELECT COUNT(*) INTO @existing_record FROM course_selection WHERE student_id = student_id AND course_id = course_id;
IF @existing_record = 0 THEN
INSERT INTO course_selection(student_id, course_id, score) VALUES (student_id, course_id, score);
SELECT 'Course registered successfully!' AS result;
ELSE
UPDATE course_selection SET score = score WHERE student_id = student_id AND course_id = course_id;
SELECT 'Score updated successfully!' AS result;
END IF;
END// DELIMITER ; -- Return to default delimiter. (Must be the last statement in the procedure.)
8.创建一个调用该存储过程的示例。我们可以创建一个名为"register_course"的函数，该函数担当学号和课程号作为参数，并返回选课状态消息。
DELIMITER //
CREATE FUNCTION register_course_example(IN student_id INT, IN course_id INT, IN score INT)
RETURNS VARCHAR(50)
BEGIN
DECLARE result VARCHAR(50);
CALL register_course(student_id, course_id, score);
SELECT result INTO result FROM course_selection WHERE student_id = student_id AND course_id = course_id;
RETURN result;
END// DELIMITER ; -- Return to default delimiter. (Must be the last statement in the procedure.)
四、实验结果与分析
1. 触发器实现主动统计和更新数据的功能：
- SC表插入触发器：当向SC表中插入一条纪录时，触发器会主动计算每个学生的平均分和每门课程的平均分，并更新到student_avg, course_avg表中。
- SC表更新触发器：当更新SC表中的分数时，触发器会主动更新学生平均分和课程平均分到student_avg, course_avg表中。
- SC表删除触发器：当从SC表中删除一条纪录时，触发器会主动更新学生平均分和课程平均分到student_avg, course_avg表中。
- Course表插入触发器：当向Course表中插入一条纪录时，触发器会主动统计总学分，并表现出来。
2. 存储过程实现选修课程的添加和修改功能：
首先，我们需要创建一个存储过程，接收三个参数：学号、课程号和分数。然后按照要求举行判断和操作。
1. 使用IF语句判断学号是否存在，如果不存在，打印出"学号不存在"；
2. 使用IF语句判断课程号是否存在，如果不存在，打印出"课程号不存在"；
3. 使用IF语句判断分数是否为数值，如果不是则打印出"分数应该为数值"；判断分数是否低于40分，如果低于40分，主动改为40分；
4. 使用IF语句判断是否已经有纪录，如果有直接更改分数，并打印出"将某某姓名，某某课程的分数改为了多少？"；
5. 使用IF语句判断如果没有纪录，则直接插入纪录，并打印出"某某姓名，某某课程的分数为多少？"。
以下是具体的sql语句：
DELIMITER //
CREATE PROCEDURE update_grade(IN student_id INT, IN course_id INT, IN grade INT)
BEGIN
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
-- Error handling code goes here. For example:
-- PRINT 'An error occurred in the trigger. Exiting...';
ROLLBACK; -- Rollback any active transactions.
LEAVE; -- Exit the procedure.
END;
-- Normal code goes here. For example:
START TRANSACTION; -- Start a new transaction.
-- 判断学号是否存在
IF student_id NOT IN (SELECT student_id FROM Student) THEN
SELECT '学号不存在' AS error_message;
LEAVE; -- Exit the procedure.
END IF;
-- 判断课程号是否存在
IF course_id NOT IN (SELECT course_id FROM Course) THEN
SELECT '课程号不存在' AS error_message;
LEAVE; -- Exit the procedure.
END IF;
-- 判断分数是否为数值
IF grade IS NOT NULL AND grade NOT IN (SELECT grade FROM SC WHERE student_id = student_id AND course_id = course_id) THEN
SELECT '分数应该为数值' AS error_message;
LEAVE; -- Exit the procedure.
END IF;
-- 判断分数是否低于40分，如果低于40分，主动改为40分
IF grade IS NULL OR grade < 40 THEN
SET grade = 40;
END IF;
-- 判断是否已经有纪录，如果有直接更改分数，并打印出将某某姓名，某某课程的分数改为了多少？
IF EXISTS (SELECT * FROM SC WHERE student_id = student_id AND course_id = course_id) THEN
UPDATE SC SET grade = grade WHERE student_id = student_id AND course_id = course_id;
SELECT CONCAT(course_id, ' ', '分数改为了', grade) AS updated_grade;
ELSE
-- 如果没有纪录，则直接插入纪录，并打印出某某姓名，某某课程的分数为多少？
INSERT INTO SC (student_id, course_id, grade) VALUES (student_id, course_id, grade);
SELECT CONCAT(course_id, ' ', '分数为', grade) AS new_grade;
END IF;
END// DELIMITER ; -- Return to default delimiter. (Must be the last statement in the procedure.)
五、实验总结
通过本次实验，我们学习了触发器的概念和使用方法，把握了创建和使用触发器实现主动统计和更新数据的功能。同时，我们还学习了编写存储过程的方法，实现了选修课程的添加和修改功能。通过这些实践操作，我们加深了对数据库的理解和应用能力。
六、实验中碰到的问题及办理方法
在实验过程中，可能会碰到以下问题：
1. 创建触发器时出现语法错误或执行失败的情况。办理方法是细致查抄触发器的语法是否精确，确保表名、字段名等都精确无误。
2. 在存储过程中举行条件判断时出现逻辑错误。办理方法是细致分析需求，理清逻辑关系，确保条件判断语句的精确性。
3. 在存储过程中举行数据更新或插入时出现主键冲突或唯一束缚的问题。办理方法是先查询是否存在相同的纪录，如果存在则举行更新操作，否则举行插入操作。
七、实验心得与了解
通过本次实验，我深刻了解到了数据库中触发器和存储过程的紧张性和实用性。触发器可以在特定事件触发时主动执行相应的操作，提高了数据处理的服从和灵活性。而存储过程可以将复杂的业务逻辑封装起来，简化了操作流程和代码维护。在现实开发中，我们应该根据具体的需求合理使用触发器和存储过程，以提高数据库应用的性能和可维护性。
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。 |
