「每天一道面试题」MySQL存储过程

欢迎大家来到IT世界,在知识的湖畔探索吧!

MySQL存储过程

什么是存储过程

存储过程（Stored Procedure）是一组为了完成特定功能的 SQL 语句集，存储在数据库中，经过第一次编译后再次调用不需要再次编译，用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。类似于 Java 中的方法，定义好之后可以多次调用。

存储过程语法

语法

DROP PROCEDURE IF EXISTS [存储过程名]; DELIMITER [结束标记] CREATE PROCEDURE [存储过程名]([参数1], [参数2] ...) BEGIN [存储过程体(一组合法的SQL语句)] END [结束标记] DELIMITER ;

欢迎大家来到IT世界,在知识的湖畔探索吧!

参数

说明

当存储过程有且仅有一条 SQL 语句时，BEGIN 和 END 可以省略。 存储过程中的参数可分为 3 部分，分别是 [参数模式] [参数名] [参数类型] ，比如 IN name VARCHAR[20]。

调用存储过程

欢迎大家来到IT世界,在知识的湖畔探索吧!CALL [存储过程名]([参数1], [参数2] ...);

删除存储过程

DROP PROCEDURE IF EXISTS [存储过程名];

存储过程优缺点

优点

1. 通常存储过程有助于提高应用程序的性能。当创建，存储过程被编译之后，就存储在数据库中。 但是，MySQL 实现的存储过程略有不同。 MySQL 存储过程按需编译。 在编译存储过程之后，MySQL 将其放入缓存中。
2. MySQL 为每个连接维护自己的存储过程高速缓存。 如果应用程序在单个连接中多次使用存储过程，则使用编译版本，否则存储过程的工作方式类似于查询。
3. 存储过程有助于减少应用程序和数据库服务器之间的流量，因为应用程序不必发送多个冗长的 SQL 语句，而只能发送存储过程的名称和参数。
4. 存储的程序对任何应用程序都是可重用的和透明的。 存储过程将数据库接口暴露给所有应用程序，以便开发人员不必开发存储过程中已支持的功能。
5. 存储的程序是安全的。 数据库管理员可以向访问数据库中存储过程的应用程序授予适当的权限，而不向基础数据库表提供任何权限。

缺点

1. 如果使用大量存储过程，那么使用这些存储过程的每个连接的内存使用量将会大大增加。 此外，如果您在存储过程中过度使用大量逻辑操作，则 CPU 使用率也会增加，因为数据库服务器的设计不当于逻辑运算。
2. 存储过程的构造使得开发具有复杂业务逻辑的存储过程变得更加困难。
3. 很难调试存储过程。只有少数数据库管理系统允许您调试存储过程。不幸的是，MySQL 不提供调试存储过程的功能。
4. 开发和维护存储过程并不容易。开发和维护存储过程通常需要一个不是所有应用程序开发人员拥有的专业技能。这可能会导致应用程序开发和维护阶段的问题。

案例

创建测试表

欢迎大家来到IT世界,在知识的湖畔探索吧!DROP TABLE IF EXISTS `t_user`; CREATE TABLE `t_user` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(20) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

创建无参的存储过程

创建存储过程（向 t_user 数据表中插入两条数据）

DROP PROCEDURE IF EXISTS myp1; DELIMITER $ CREATE PROCEDURE myp1() BEGIN INSERT INTO t_user VALUES(NULL, 'Jas'),(NULL, 'Joy'); END $ DELIMITER ;

创建IN模式参数的存储过程

创建存储过程（根据传入的 id 获取用户名）

欢迎大家来到IT世界,在知识的湖畔探索吧!DROP PROCEDURE IF EXISTS myp2; DELIMITER $ CREATE PROCEDURE myp2(IN userId INT) BEGIN SELECT name FROM t_user WHERE id = userId; END $ DELIMITER ;

创建OUT模式参数的存储过程

创建存储过程（根据传入的 id 获取用户名赋值给输出变量）

DROP PROCEDURE IF EXISTS myp3; DELIMITER $ CREATE PROCEDURE myp3(IN userId INT, OUT username VARCHAR(20)) BEGIN SELECT name INTO username # 将查询到的用户名赋值给 username FROM t_user WHERE id = userId; END $ DELIMITER ;

创建INOUT模式参数的存储过程

创建存储过程（传入一个整数参数，值扩大 2 倍后返回）

欢迎大家来到IT世界,在知识的湖畔探索吧!DROP PROCEDURE IF EXISTS myp4; DELIMITER $ CREATE PROCEDURE myp4(INOUT a INT) BEGIN SET a = a * 2; END $ DELIMITER ;

自定义函数

语法

自定义函数的使用方法与存储过程类似，只不过自定义函数必须要有返回值，返回值有且只有一个。自定义函数语法：

DROP FUNCTION IF EXISTS [函数名]; DELIMITER [结束标记] CREATE FUNCTION [函数名]([参数1], [参数2] ...) RETURNS [返回值类型] BEGIN [方法体] RETURN [返回值]; END [结束标记] DELIMITER ; 

自定义函数中的变量只有两部分，分别是[变量名] [变量类型]，比如 username VARCHAR[20]。通过上面的语法对比，不知道大家能不能发现 MySql 中的自定义函数与 Java 中的有返回值函数很像。调用自定义函数的语法是：

欢迎大家来到IT世界,在知识的湖畔探索吧!SELECT [函数名]([参数1], [参数2] ...); 

删除自定义函数的语法是：

DROP FUNCTION IF EXISTS [函数名]; 

创建无参的自定义函数

创建自定义函数（查询 t_user 中的所有记录数，并返回）

欢迎大家来到IT世界,在知识的湖畔探索吧!DROP FUNCTION IF EXISTS myf1; DELIMITER $ CREATE FUNCTION myf1() RETURNS INT BEGIN DECLARE sum INT DEFAULT 0; # 定义局部变量 sum，默认值为 0 SELECT COUNT(*) INTO sum # 将查询的结果赋值给 sum FROM t_user; RETURN sum; END $ DELIMITER ; 

创建有参的自定义函数

创建自定义函数（根据用户 id 获取用户名，赋值后返回）

DROP FUNCTION IF EXISTS myf2; DELIMITER $ CREATE FUNCTION myf2(userId INT) RETURNS VARCHAR(20) BEGIN SET @username=''; # 定义系统会话变量 SELECT name INTO @username # 将用户名赋值给 username FROM t_user WHERE id = userId; RETURN @username; END $ DELIMITER ; 

自定义函数与存储过程对比

自定义函数与存储过程有很多相似的地方，下面是一些主要的区别对比：

1. 一般来说，存储过程实现的功能要复杂一点，而函数的实现的功能针对性比较强。
2. 存储过程可以有返回值也可以没有返回值，而自定义函数必须要返回值，且返回值有且只有一个。
3. 存储过程一般是作为一个独立的部分来执行，而函数可以作为查询语句的一个部分来调用，因此它可以在查询语句中位于 FROM 关键字的后面。 SQL 语句中不可用存储过程，而可以使用函数。