刚开始接触存储过程和存储函数的时候可能会产生误解,这里的“存储”并不是指这种语句是数据库用来存储数据的,“存储”的真正含义是存储预编译的语句,可以简单地把它理解成类似其他编程语言里的函数。
- 存储过程:存储过程就是提前编译好的一段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