MySQL存储过程和函数

前言

了解存储过程函数，用其提高SQL性能。

一、存储过程

一、存储过程和函数
含义）封存存储过程的细节，预处理好的批SQL语句。
优点）
1）提高代码的重用性
2）简化操作
3）不用重复编译
4）减少和数据库连接次数,一个过程一个连接，即多SQL只有一个连接。
创建语法）

delimiter 结束标记
create procedure 存储过程名(参数列表)
BEGIN（如果里面SQL只有一句，则可省略）
系列SQL;
END 结束标记

1）参数
参数模式 参数名 参数类
IN stuName VARCHAR(20)
参数模式）IN（需调用方传入值） OUT（返回值） INOUT（既输入又输出）
调用语法）
CALL 存储过程名(实参列表);

2）案例
例1）一次性插入多条记录

DELIMITER $
CREATE PROCEDURE insertPR ( ) BEGIN
	INSERT INTO admin ( id, `name` )
	VALUES
		( NULL, 'a' ),
		( NULL, 'b' ),
		( NULL, 'c' ),
		( NULL, 'd' );

END $ 
CALL insertPR ( ) $

例2）根据女生名，查询对应男生信息

CREATE PROCEDURE selectPR ( IN beautyName VARCHAR ( 20 ) ) BEGIN
	SELECT
		a.* 
	FROM
		beauty a
		LEFT JOIN boys b ON a.boyfriend_id = b.id 
	WHERE
	beautyName = a.NAME;
end
END $
CALL selectPR ( '宋茜' );

例3）用户是否登录成功

CREATE PROCEDURE login ( IN username VARCHAR ( 10 ), IN PASSWORD VARCHAR ( 10 ) ) BEGIN
	DECLARE
		result INT; #声明
	SELECT
		count( 1 ) INTO result #赋值
		
	FROM
		admin a 
	WHERE
		a.username = username 
		AND a.PASSWORD = b.PASSWORD;
	SELECT
	IF
		( result = 0, 'failed', 'success' ) ； #使用
	
	END $ 
	CALL login ( 'lls', '123' ) $

例4）带out的

DELIMITER $
CREATE PROCEDURE login ( IN username VARCHAR ( 10 ), IN PASSWORD VARCHAR ( 10 ), OUT result INT ) BEGIN
	SELECT
		count( 1 ) INTO result #赋值
		
	FROM
		admin a 
	WHERE
		a.username = username 
		AND a.PASSWORD = PASSWORD;
	
END $ 
CALL login ( 'lls', '123', @result ) $;
SELECT @result $

例5）带inout 翻倍

CREATE PROCEDURE extend ( INOUT a INT, INOUT b INT ) BEGIN
	
	SET a = a * 2;# select a*2,b*2 into a,b;
	
	SET b = b * 2;
	
END $ 
SET @val1 = 2 $ 
SET @val2 = 3 $ CALL extend ( @val1, @val2 ) $
SELECT @val1 + @val2 $

例6）传入女生，返回女生 and 男生（如果男生为null，则返回 and null）

CREATE PROCEDURE test ( IN beautyName, OUT str VARCHAR ( 50 ) ) BEGIN
	SELECT
		CONCAT( beautyName, ' and ', IFNULL( boyName, 'NULL' ) ) 
	FROM
		beauty a
		LEFT JOIN boys b ON a.boyfriend_id = b.id 
	WHERE
		beautyName = a.NAME;
	
END $ 
CALL test ( '小昭', @str ) $
SELECT @str $

二、删除存储过程
语法）drop procedure 存储过程名;
注：只能一个一个的删除

三、查看存储过程
语法）show create procedure 存储过程名；

二、函数

一、函数
区别于存储过程）
1）函数有且仅有一个返回值
2）存储过程适合做批量插入和更新，函数适合处理数据并返回结果。
语法）

delimiter $
create function 函数名(参数列表) returns 返回类型
begin(函数体只有一句话时可省略begin end)
函数体
end $
select 函数名（参数列表) $

例1）无参有返回，返回公司员工个数

USE employees;

delimiter $
CREATE FUNCTION myf1 ( ) RETURNS INT BEGIN
	DECLARE
		c INT DEFAULT 0;
	SELECT
		count( 1 ) INTO c 
	FROM
		employees;
	RETURN c;
	
END $ 
SELECTmyf1 ( ) $

例2）有参有返回，根据员工名，返回它的工资

CREATE FUNCTION myf2 ( empName VARCHAR ( 20 ) ) RETURNS DOUBLE BEGIN
	
	SET @sla = 0;#声明
	SELECT
		salary INTO @sla #赋值
		
	FROM
		employees 
	WHERE
		empName = employees.NAME;
	RETURN @sal;
	
END $ 
SELECT myf2 ( 'king' ) $

二、查看函数

show create function 函数名;
show create function myf3;

三、删除函数

drop function 函数名
drop function myf3;

总结

1）如何创建储存过程
2）存储过程的删除和查看
3）函数

参考文献

[1] MySQL 尚硅谷