MySQL存储过程和函数

MySQL存储程序可以分为存储过程和函数两种，MySQL中可以使用CREATE PROCEDURE 和CREATE FUNCTION来创建存储过程和函数。函数只能通过RETURN返回单个值或者表对象，而存储过程不允许执行RETURN，但是可以通过OUT参数返回多个值。函数限制比较多，不能用临时表，只能用表变量，还有一些函数都不可用等等，而存储过程的限制相对较少。函数可以嵌入SQL语句中使用，可以在SELECT语句中作为查询语句的一个部分调用，而存储过程一般作为独立的的部分来执行。存储过程可以有0个或多个返回值，适合做数据插入，批量更新；函数有且仅有一个返回值，适合处理数据后返回一个结果。

存储过程
创建存储过程：

CREATE PROCEDURE p_name(p_params)
BEGIN
	[characteristics...]
	p_body;
END

其中p_name代表存储过程的名字，p_params代表存储过程的参数列表,该参数列表形如：[IN][OUT][INOUT] paeam_name type,其中IN表示输入参数，OUT表示输出参数，INOUT表示既可以输入也可以输出，param_name表示参数名称，type表示参数类型，可以为MySQL中的任一种类型。characteristics指定存储过程的特性。p_body是SQL代码的内容，可以用BEGIN和END来表示SQL代码的开始和结束，当p_body中只有一条语句时，也可以省略BEGIN和NED。
下面我们创建一条存储过程：

DELIMITER $ # 声明语句结束符
CREATE PROCEDURE p1() # 不带参数
BEGIN
	SELECT * FROM userinfo;
END $

调用存储过程也非常简单，只需使用CALL p_name(p_params)。

CALL p1()$ # 修改了语句结束符以后需要使用修改后的可使用delimiter ;修改回来

上面是无参数的存储过程，创建带参数的存储过程：
带参数需要用到变量，存储过程中使用DECLARE定义变量，语法格式：

DECLARE var_name type DEFAULT value;

var_name 代表参数名称，type代表参数类型，value代表参数默认值。
定义变量之后需要为变量赋值，为变量赋值可以改变变量的默认值，MySQL中使用SET为变量赋值，语法格式：

SET var_name=value[,var_name1=value1,]...;

在存储过程中的SET语句是一般SET语句的扩展版本，SET语句作为预先存在的SET语法的一部分来实现，允许SET a=x,b=y这样的扩展语法。其中不同的变量类型（局部声明变量及全局变量）可以被混合起来，这也允许吧局部变量和一些只对系统变量有意义的选项合并起来。
创建带IN参数的存储过程：
例如我们想要根据传递的姓名返回年龄：

CREATE PROCEDURE p2(IN name VARCHAR(10))
BEGIN
	#Routine body goes here...
	DECLARE result INT DEFAULT 0;
	SELECT age INTO result
	FROM userinfo
	WHERE userinfo.name=name;
	SELECT result;
END$

创建带OUT参数的存储过程：

CREATE PROCEDURE p3(IN age VARCHAR(10),OUT count INT)
BEGIN
	#Routine body goes here...
	SELECT COUNT(*) INTO count #将获取到的值赋给count
	FROM userinfo 
	WHERE userinfo.age=age;
END$

创建带INOUT参数的存储过程：
例如我们传入一个年龄，然后将年龄+1并返回：

CREATE PROCEDURE P4(INOUT age INT)
BEGIN
	#Routine body goes here...
	SET @age = age + 1;
	SELECT @age;

END$

查看存储过程状态：

SHOW PROCEDURE STATUS LIKE "";

查看存储过程定义：

SHOW CREATE PROCEDURE 存储过程名\G; 

存储过程的删除：

DROP PROCEDURE 存储过程名;

一次只能删除一个存储过程，不支持一次删除多个。
函数
创建函数：

CREATE FUNCTION f_name(f_params) RETURNS type
BEGIN
	[characteristic...]
	f_body
END

其中f_name表示函数名，f_params表示参数列表，同存储过程的参数列表一样，RETURNS type表示函数返回数据的数据类型。f_body表示函数体，函数体中必须要有RETURN，否则会报错。
无参数函数：
例如我要创建一个返回用户表中数据的总数的函数：

CREATE FUNCTION f1() RETURNS INT
BEGIN
	DECLARE count INT DEFAULT 0; # 定义变量
	SELECT COUNT(*) INTO count # 赋值给变量
	FROM userinfo;
	RETURN count; # 返回变量
END$

带参数函数：
例如我们要根据传递的姓名返回他的年龄：

CREATE FUNCTION f2(name VARCHAR(10)) RETURNS INT
BEGIN
	SET @age = 0; # 定义变量
	SELECT age INTO @age # 赋值给变量
	FROM userinfo
	WHERE userinfo.name = name;
	RETURN @age; # 返回变量
END$

查看函数状态：

SHOW FUNCTION STATUS LIKE " ";

查看函数定义：

SHOW CREATE FUNCTION 函数名\G;

删除函数：

DROP FUNCTION 函数名;