MySQL数据库中触发器和存储过程实例分析：提高程序效率的重要方法

MySQL数据库的触发器和存储过程实例分析

一、实验目的

• 1、掌握某主流DBMS支持的SQL编程语言和编程规范，规范设计存储过程；

• 2、能够理解不同类型触发器的作用和执行原理，验证触发器的有效性;
  

• 3、培养学生的系统思维，提升解决复杂工程问题所需的编程能力。

二、实验要求

掌握某主流DBMS的SQL编程语言，在前面创建的数据库基础上，定义BEFORE（for）触发器和AFTER触发器；掌握数据库存储过程定义、存储过程运行，存储过程更名，存储过程删除，存储过程的参数传递。

温馨提示：以下内容均已经过测试，不过难免会有疏漏，但是代码实现大多数思路及实现经测验都是正确的。

三、实现内容及步骤1、创建一个不带参数的简单存储过程

• (1)创建存储过程sp_avggrade，实现查询每门课程学生的平均成绩的功能；

delimiter//
CREATE PROCEDURE sp_avggrade()
COMMENT '
查询每门课程学生的平均成绩的功能'

BEGIN
SELECT cno as 课程号,avg(grade)as 平均成绩
FROM sc
GROUP BY cno;

end//
delimiter;

(2)调用该存储过程，实现查询。

call sp_avggrade();
2、创建一个带输入参数的存储过程

(1)创建存储过程sp_course_avggrade, 实现通过输入课程编号参数查询指定课程编号的课程平均成绩的功能；

delimiter//
CREATE PROCEDURE sp_course_avggrade(IN c_no CHAR(2))
COMMENT '
通过输入课程编号参数查询指定课程编号的课程平均成绩的功能'

BEGIN
SELECT cno,AVG(grade)
FROM sc
WHERE cno=c_no;

END//
delimiter;

(2)调用该存储过程，获取指定课程的平均成绩。

3、创建一个带输入输出参数的存储过程

(1)创建存储过程sp_sdept _student，实现根据用户输入的院系编号参数，查找该学院的学生人数，并以变量形式输出的功能；

delimiter//
CREATE PROCEDURE sp_sdept_student2(IN _sdept CHAR(10),OUT num int)
BEGIN
SELECT COUNT(sno) INTO num
FROM student
WHERE _sdept = sdept;

END//
delimiter;

(2)调用该存储过程，以返回变量的形式获取相应院系的学生人数。

SET @num=10;

CALL sp_sdept_student2('
计科'
,@num);

SELECT @num AS '
人数'
;
4、触发器的创建与使用

(1)在学生表上创建触发器，实现学生表中删除学生记录时，成绩表中该学生成绩记录的级联删除；

delimiter//
CREATE TRIGGER delete_stu
BEFORE DELETE
ON student
FOR EACH ROW
BEGIN
DELETE
FROM sc
WHERE sc.sno=old.sno;

END//
delimiter;

(2)在选课表上创建触发器，若录入的成绩大于100分，小于0分，则拒绝插入记录并显示；

delimiter//
CREATE TRIGGER scgrade
BEFORE INSERT ON sc
FOR EACH ROW
BEGIN
IF new.grade>
100 or new.grade <
0 THEN
SIGNAL SQLSTATE '
45000'

SET message_text='
录入成绩不符合规定，拒绝插入'
;

END IF;

END//
delimiter;

(3)验证(1)、(2)中触发器

验证(1)

DELETE
FROM student
WHERE sno='
201513'
;

验证(2)

INSERT INTO sc(sno,cno,grade)
VALUES('
2002720'
,'
1'
,200);


引言
MySQL数据库是最受欢迎的开源关系型数据库管理系统之一。MySQL数据库支持触发器和存储过程，它们都是提高程序效率的重要方法。本文将分析MySQL中的触发器和存储过程的基本概念、语法结构和实例，帮助读者更好地理解和应用触发器和存储过程。
触发器的基本概念和语法结构
MySQL数据库触发器是一种与MySQL数据库表相关联的特殊类型的存储过程，可以在INSERT、UPDATE、DELETE语句执行之前或之后自动运行。触发器是由事件触发的特殊程序，可以在数据库表中的数据发生修改时自动执行一些指定的操作。
创建触发器：CREATE TRIGGER trigger_name trigger_time trigger_event ON table_name FOR EACH ROW trigger_code
触发器的实例分析
以创建一个记录网站用户访问记录的触发器为例，该触发器在INSERT语句执行后自动向access_log数据表中插入一条记录。
DELIMITER //\\
CREATE TRIGGER insert_access_log AFTER INSERT ON user_access\\
FOR EACH ROW\\
BEGIN\\
INSERT INTO access_log VALUES (NEW.user_id, NEW.access_time);\\
END;//
存储过程的基本概念和语法结构
MySQL数据库存储过程是一种预定义的MySQL代码块，它可以接受参数和返回值，并将它们存储在数据库中。存储过程的主要优点是可以减少数据库的通信次数，从而提高程序执行效率。
创建存储过程：CREATE PROCEDURE procedure_name ([IN|OUT|INOUT] parameter_name parameter_type[, …]) BEGIN procedure_body END;
存储过程的实例分析
以创建一个存储过程计算网站用户的访问次数为例，该存储过程接受用户id作为输入参数，并返回该用户访问次数。
DELIMITER //\\
CREATE PROCEDURE calc_access_count(IN uid INT, OUT a_count INT)\\
BEGIN\\
SELECT COUNT(*) INTO a_count FROM user_access WHERE user_id = uid;\\
END;//
触发器和存储过程的应用场景
在MySQL数据库应用中，触发器和存储过程可用于以下场景:
1. 数据库表数据变化记录
2. 大型数据处理
3. 数据库性能优化
4. 网站数据分析
常见问题及解决方案
1. 触发器会对MySQL数据库性能产生影响，如何优化触发器的性能？
触发器优化方法：尽量减少不必要的查询操作，避免在触发器中更新触发器所操作的数据表，避免在触发器中使用复杂且耗时的操作。
2. 如何调用存储过程？
调用存储过程：CALL procedure_name(parameter_name);（parameter_name为存储过程的参数）。
总结
MySQL的触发器和存储过程可以更高效地管理数据、提高程序效率。在使用触发器和存储过程时，需要根据具体的业务需求进行实际分析，合理使用触发器和存储过程，从而提高程序的性能。