二十三、MySQL基础系列笔记之储存过程与函数

存储过程

什么是存储过程

存储过程是一组事先编辑并存储在数据库中的SQL语句的集合。

如有2条可能会重复使用到的SQL语句，一条一条执行下去，比如重复10次，那么就要输入20次。更加高效的方法，将2条语句封装到存储过程种，执行10次，只组要调用10次存储过程就行了。

存储过程优点

• 简化操作；
• 提到代码重用率
• 减少数据在数据库和应用服务器之间的传输，提高效率。

创建与使用存储过程

创建存储过程语法

CREATE PROCEDURE 存储过程名([IN|OUT|INOUT] 参数名 参数类型)
BEGIN
	SQL语句
END

参数解释：

​ IN参数：输入参数

​ OUT参数：输出参数

​ INOUT参数：既可以是输入也可以是输出

存储过程：可以有0个返回值，也可以有多个返回值。

使用存储过程

语法

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

存储过程案例

#案例一 空参存储过程
DELIMITER $$
CREATE PROCEDURE demo1()
BEGIN
	SELECT * FROM user;
END$$

# 调用
CALL demo1()$$ 

# 案例二 IN参数存储过程
DELIMITER $$
CREATE PROCEDURE demo2(IN uid int)
BEGIN
	SELECT * FROM user WHERE id > uid;
END$$

#调用 查询id大于3的所有用户
CALL demo2(3)$$

# 案例三 OUT参数过程过程
DELIMITER $$
CREATE PROCEDURE demo3(IN uid int, OUT uname VARCHAR(30))
BEGIN
	SELECT name INTO uname FROM user WHERE id = uid;
END$$

#调用。使用一个全局变量@uName来接收返回的结果
CALL demo3(2, @uName)$$
SELECT @uName$$

# 案例四 INOUT参数存储过程
DELIMITER $$
CREATE PROCEDURE demo4(INOUT x int, INOUT y INT)
BEGIN
	SET x=x*2;
	SET y=y*2;
END$$

# 调用
SET @xx=10$$
SET @yy=20$$
CALL demo4(@xx,@yy)$$
SELECT @xx,@yy$$

查看存储过程

查看存储过程状态语法

SHOW PROCEDURE STATUS [like 'pattern']; 

SHOW PROCEDURE STATUS LIKE 'demo1';

查看存储过程定义的语法

SHOW CREATE PROCEDURE 存储过程名;

SHOW CREATE PROCEDURE demo1;

删除存储过程

语法

DROP PROCEDURE 存储过程名;

案例

DROP PROCEDURE demo1;

函数

函数与存储过程非常类型，不同的是函数只能有一个返回值。

创建函数的语法

CREATE FUNCTION 函数名(参数名 参数类型) RETURNS 返回类型
BEGIN
	SQL语句
END

调用函数

SELECT 函数名(参数列表);

函数案例

# 案例1 没有参数返回的函数
DELIMITER $$
CREATE FUNCTION fun1() RETURNS INT
BEGIN
	# 声明变量
	DECLARE cCount INT DEFAULT 0;
	# 查询统计为变量赋值
	SELECT COUNT(*) INTO cCount FROM user;
	# 返回
	RETURN cCount;
END$$

# 调用
SELECT fun1()$$

# 案例2 有参数返回的函数
CREATE FUNCTION fun2(uname VARCHAR(20)) RETURNS INT
BEGIN
	DECLARE uid INT;
	SELECT id INTO uid FROM user WHERE name = uname;
	RETURN uid;
END$$

# 调用
SELECT fun2('李四')$$

查看函数

查看函数状态

SHOW FUNCTION STATUS [like 'pattern']; 

SHOW FUNCTION STATUS LIKE 'fun1'$$

查看函数定义的语法

SHOW CREATE FUNCTION 函数名;

SHOW CREATE FUNCTION fun1$$

删除函数

DROP FUNCTION 函数名;

DROP FUNCTION fun1$$

2020-11-08