mysql存储过程和自定义函数_MySql存储过程与自定义函数

一、存储过程概述

存储过程(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 语句中不可用存储过程，而可以使用函数