mysql存储过程

2022-06-23

存储过程是一组预先编译好的SQL语句的集合，类似于编程语言中的函数，打包了一堆语句，提高复用性。同时因为是已经编译过的，下次再执行不会再编译了，所以也减少了编译次数。

语法

CREATE PROCEDURE 存储过程名(参数列表)
BEGIN
	存储过程体
END

通常参数会有类型和参数名两部分，这里的参数列表包含三部分：参数模式，参数名，参数类型。比如：

IN stuname VARCHAR(20)

主要说下参数模式

IN：表示输入，可以理解成函数的参数
OUT：表输出，可以理解成函数的返回值
INOUT：既当参数又当返回值

如果存储过程体中仅仅只有一句话，那么BEGIN END可以省略，类似java中if语句或者for语句里面只有一条语句时可以省略大括号一样。

存储过程体中的每条语句的结尾必须加上分号，这个时候为了防止分号被认为是整个存储过程的结束，存储过程的结尾可以使用DELIMITER重新设置，语法为：DELIMITER 结束标记，比如DELIMITER $

调用语法：CALL 存储过程名(实参列表)

使用

空参列表

向user表添加两行数据

DELIMITER $
CREATE PROCEDURE proc()
BEGIN
	INSERT INTO `user`(`name`, email) VALUES('allen', 'allen@gmail.com'),('curry', 'curry@gmail.com');
END $

直接将上面的代码复制到mysql到命令行里

mysql> CREATE PROCEDURE proc()
    -> BEGIN
    -> INSERT INTO `user`(`name`, email) VALUES('allen', 'allen@gmail.com'),('curry', 'curry@gmail.com');
    -> END $

然后再输入如下代码执行存储过程。注意因为已经设置了DELIMITER，所以这里也要用$来结尾。

CALL proc()$

IN模式

因为上面设置过DELIMITER了，这里就不设置了。

CREATE PROCEDURE proc2(IN n VARCHAR(20))
BEGIN
	SELECT * FROM `user` WHERE `name`=n;
END $

CALL proc2('curry')$

这里要注意的是上面参数名如果写成name就不行，会打印出表的所有内容，因为他会认为name为user里的字段名，那就是恒成立了。否则就要加上表名，比如user.name=name

看一个带变量赋值的例子

CREATE PROCEDURE proc3(IN name VARCHAR(20), IN email VARCHAR(20))
BEGIN
	DECLARE result INT DEFAULT 0; # 声明变量并初始化
	SELECT COUNT(*) INTO result # INTO赋值给result
	FROM `user` WHERE `user`.`name`=name
	AND `user`.email=email;

	SELECT IF(result>0, 'found', 'not found');
END $

CALL proc3('curry', 'curry@gmail.com')$  # found
CALL proc3('curry', 'curry1@gmail.com')$ # not found

OUT模式

返回一个值的情况：

CREATE PROCEDURE proc4(IN name VARCHAR(20), OUT email VARCHAR(20))
BEGIN
	SELECT `user`.email INTO email
	FROM `user` WHERE `user`.`name`=name;
END $

# 调用
CALL proc4('curry', @email)$
SELECT @email$

要注意的是，如果上面的select语句包含不只一行的时候会报错：Result consisted of more than one row。

返回多个值的情况，主要是注意一下INTO那里的写法：

CREATE PROCEDURE proc5(IN name VARCHAR(20), OUT email VARCHAR(20), OUT id INT)
BEGIN
	SELECT `user`.email, `user`.id INTO email, id
	FROM `user` WHERE `user`.`name`=name;
END $

# 调用
CALL proc5('curry', @email, @id)$
SELECT @email, @id$

INOUT模式

CREATE PROCEDURE proc6(INOUT a INT, INOUT b INT)
BEGIN
	set a=a*2;
	set b=b*2;
END $

# 调用
set @m=2$
set @n=3$
CALL proc6(@m, @n)$
SELECT @m, @n$

要注意的是，因为这里要传变量而不能传常量给proc6，这样他里面才能完成赋值。因此这里专门先设置了两个变量。

删除

DROP PROCEDURE proc1;

要注意的是不支持一次删除多个，只能一个个删除。

查看

SHOW CREATE PROCEDURE proc3;