一、存储过程概述
存储过程(Stored Procedure)是一组为了完成特定功能的 SQL 语句集,存储在数据库中,经过第一次编译后再次调用不需要再次编译,用户通过指定存储过程的名字并给出参数(如果该存储过程带有参数)来执行它。类似于 Java 中的方法,定义好之后可以多次调用。
存储过程由以下几个优点
通常存储过程有助于提高应用程序的性能。当创建,存储过程被编译之后,就存储在数据库中
可用于提高代码的重用性,简化操作
存储的程序是安全的
二、存储过程语法结构
创建存储过程的语法结构
DROP PROCEDURE IF EXISTS [存储过程名];
DELIMITER [结束标记]
CREATE PROCEDURE [存储过程名]([参数1], [参数2] ...)
BEGIN
[存储过程体(一组合法的SQL语句)]
END [结束标记]
DELIMITER ;
当存储过程有且仅有一条 SQL 语句时,BEGIN 和 END 可以省略。 存储过程中的参数可分为 3 部分,分别是 [参数模式] [参数名] [参数类型] ,比如 IN name VARCHAR[20]。
参数模式也分为 3 种,如下
IN:输入参数
OUT:可以作为返回值的参数
INOUT:既可以作为输入参数,也可以作为返回值参数
DELIMITER [存储过程名] 本身与存储过程的语法无关,用于表示存储过程的结束。最后一个命令(DELIMITER ;)将分隔符更改回分号(;)。
调用存储过程使用CALL [存储过程名]([参数1], [参数2] ...);语句。
删除存储过程使用DROP PROCEDURE IF EXISTS [存储过程名];。
三、存储过程的简单使用
创建测试表
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;
3.1创建无参的存储过程
创建存储过程(向 t_user 数据表中插入两条数据)
DROP PROCEDURE IF EXISTS myp1;
DELIMITER $
CREATE PROCEDURE myp1()
BEGIN
INSERT INTO t_user VALUES(NULL, 'Jas'),(NULL, 'Joy');
END $
DELIMITER ;
调用存储过程并查看结果
3.2创建IN模式参数的存储过程
创建存储过程(根据传入的 id 获取用户名)
DROP PROCEDURE IF EXISTS myp2;
DELIMITER $
CREATE PROCEDURE myp2(IN userId INT)
BEGIN
SELECT name FROM t_user WHERE id = userId;
END $
DELIMITER ;
调用存储过程并查看结果
3.3创建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 ;
调用存储过程并查看结果
3.4创建INOUT模式参数的存储过程
创建存储过程(传入一个整数参数,值扩大 2 倍后返回)
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 中的有返回值函数很像。
调用自定义函数的语法是SELECT [函数名]([参数1], [参数2] ...);。
删除自定义函数的语法是 DROP FUNCTION IF EXISTS [函数名];。
五、自定义函数的简单使用
5.1创建无参的自定义函数
创建自定义函数(查询 t_user 中的所有记录数,并返回)
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 ;
调用自定义函数并查看结果
5.2创建有参的自定义函数
创建自定义函数(根据用户 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 ;
调用自定义函数并查看结果
六、总结
自定义函数与存储过程有很多相似的地方,下面是一些主要的区别对比
一般来说,存储过程实现的功能要复杂一点,而函数的实现的功能针对性比较强
存储过程可以有返回值也可以没有返回值,而自定义函数必须要返回值,且返回值有且只有一个
存储过程一般是作为一个独立的部分来执行,而函数可以作为查询语句的一个部分来调用,因此它可以在查询语句中位于 FROM 关键字的后面。 SQL 语句中不可用存储过程,而可以使用函数