MySQL中的存储过程与存储函数

刚开始接触存储过程和存储函数的时候可能会产生误解，这里的“存储”并不是指这种语句是数据库用来存储数据的，“存储”的真正含义是存储预编译的语句，可以简单地把它理解成类似其他编程语言里的函数。

• 存储过程:存储过程就是提前编译好的一段sql语言，放置在数据库端，可以直接被调用，这一段sql一般都是固定步骤的业务。
• 存储函数：在存储过程的基础上加上返回值。

MySQL中的存储过程和存储函数跟Oracle中的原理相同，语法也几乎一样，只有细微的差别，但他们的参数列表还是有较大差异的。

1. 存储过程

创建语法

CREATE PROCEDURE 存储过程名(参数列表)
BEGIN
    存储过程体(组合的SQL语句);
END;

可以看出其创建语法跟Oracle略有区别，其所有的操作必须在begin..end之间，包括定义变量，而且没有create or replace这种写法，必须先删除再创建；还有一点，declare声明变量必须在存储过程的最开始，否则就会出错。

这里插播一个小知识，我们知道 ; 默认是语句的结束，当我们在dos界面输入一个很长的存储过程或者存储函数时，其内部必然含有大量 ; ，当我们enter后会自动编译，我们不希望这样，我们想要的是同时编译一段语句，这个怎么做到哪？我们可以将本来默认的;改为别的符号，语法为：

DELIMITER $$   -- 自定义的符号，不要加分号

所以上面我们可以改成，delimiter的作用范围为当前会话，可以理解成当前的.sql文件：

DELIMITER $$ 
CREATE PROCEDURE 存储过程名(参数列表)
BEGIN
    存储过程体(组合的SQL语句);
END $$

调用语法

CALL 存储过程名(参数列表);

这个跟oracle也不一样，需要加CALL关键字。

删除语法

DROP PROCEDURE 存储过程名;

查看创建信息

SHOW CREATE PROCEDURE 存储过程名;

2. in、out、in out参数

用法跟oracle中有很大差异，这里的参数跟普通编程语言里比较相似，不像Oracle那么严格。

• in：用于接收参数，将参数传入存储过程或存储函数内部后也可以进行修改，但是不建议，因为这样没有任何意义，这类似于c语言中的形参，在存储过程或者函数内部修改形参并不会改变外部传入参数的值，默认的参数模式为in；例如下面允许修改input（在Oracle中这种行为是非法的）但这样做没有任何意义：

-- in参数
DELIMITER $$
CREATE PROCEDURE p1(IN input INT)
BEGIN
	SET input=5; -- 可以改变input
	SELECT input;
END $$
-- 测试
SET @n=1;
SELECT @n;  -- 输出1
CALL p1(@n);
SELECT @n;  -- 输出还是1，并没有改变外部的变量

• out：用于修改参数，必须先在外部定义变量（一般不赋值），然后传入存储过程或存储函数内部进行修改，存储过程或存储函数并不care在外部有没有赋值，即使有赋值也会忽略掉（清零赋值为null），可以理解成你传入output但不对它进行赋值，这样做没有意义，那么MySQL会自动赋值为null，例如：

-- 带out参数的存储过程，赋值
DELIMITER $$
CREATE PROCEDURE p2(OUT output INT)
BEGIN
	SET output=10;
	SELECT output;
END $$
-- 测试
SET @num=1;
SELECT @num;  -- 输出1
CALL p2(@num);
SELECT @num;  -- 输出10，改变了外部的变量

-- 带out参数的存储过程，不赋值，忽略（清空）传入的值
DELIMITER $$
CREATE PROCEDURE p3(OUT output INT)
BEGIN
	SELECT output;  -- 清空，输出null
END $$
-- 测试
SET @num=1;
SELECT @num;   -- 输出1
CALL p3(@num); -- 输出null
SELECT @num;   -- 输出null，改变了外部变量！！！赋值为null

• inout：既要用于接收参数，又要用于修改参数，既能干in的事又能干out的事，这里的in out才是真正类似于C语言中的函数参数传入地址，总结起来就是既能像in读入参数的值（out读不了，会清零为null），又能对外部变量赋值（in干不了，只能读值），例如：

-- 带in out参数的存储过程
DELIMITER $$
CREATE PROCEDURE p4(INOUT in_output_a INT, INOUT in_output_b INT)
BEGIN
	SELECT in_output_a, in_output_b;  -- 输出1, 2
	SET in_output_a=in_output_a*2;
	SET in_output_b=in_output_b*2;
END $$
-- 测试
SET @a=1;
SET @b=2;
SELECT @a, @b;    -- 输出1, 2
CALL p4(@a, @b);  -- 输出1, 2; 说明读入了
SELECT @a, @b;    -- 输出2, 4; 说明改变了外部的变量

3. 存储函数

创建语法

DELIMITER $$ 
CREATE FUNCTION 函数名(参数列表) RETURNS 返回类型
BEGIN
    函数体(一般步骤为：
                先定义返回值，
                再为返回值赋值，
                最后返回);
END $$

注意这里的returns跟oracle中的不同，要加个s哦。

调用语法

SELECT 函数名(参数列表);

这里的意思是执行函数，并返回值，可以定义变量接收？

删除语法

DROP FUNCTION 函数名;

查看创建信息

SHOW CREATE FUNCTION 函数名;

我之所以在前面讲参数，是因为Oracle和MySQL中的存储函数有很大不同，MySQL中的存储函数是没有in、out、inout参数区分的，可以把它的参数看做默认是且必须是in类型的。

而且还有一点需要注意的是存储函数只能有一个返回值，而且在存储函数内部是不允许使用select 变量名;这种格式打印返回值的。

-- 存储函数
DELIMITER $$
CREATE FUNCTION f3(a INT, b INT) RETURNS INT
BEGIN
	DECLARE SUM INT DEFAULT 0;
	SET SUM=a+b;
	-- select sum; -- 这样写是非法的
	RETURN SUM;
END $$
-- 测试
SET @a=5;
SET @b=5;
SELECT f3(@a, @b); -- 输出10