mysql存储过程与存储函数的概念、区别以及如何创建、delimiter的作用、存储过程和函数的查看、修改、删除、储存过程的优缺点

1 概述

1.1 存储过程

• 它的思想很简单，就是一组经过预先编译的SQL语句的封装。
• 执行过程：存储过程预先存储在MySQL服务器上，需要执行的时候，客户端只需要向服务器端发出调用存储过程的命令，服务器端就可以把预先存储好的这一系列SQL语句全部执行。

1.2 存储函数

MySQL支持自定义函数，定义好之后，调用方式与调用MySQL预定义的系统函数一样。

2 创建

2.1 创建存储过程

2.1.1 语法

CREATE PROCEDURE 存储过程名 (IN | OUT | INOUT 参数名 参数类型,...)
[characteristics ...]
BEGIN 
存储过程体
END

2.1.2 形参列表

• 没有参数（无参数无返回）
• 仅仅带IN类型（有参数无返回）
• 仅仅带OUT类型（无参数有返回）
• 既带IN又带OUT（有参数有返回）
• 带INOUT（有参数有返回
  注意：IN、OUT、INOUT都可以在一个存储过程中带多个。

2.1.3 代码

-- 创建空参存储过程
	CREATE PROCEDURE select_all_data () 
	BEGIN
		SELECT
			* 
		FROM
		employees;
	END;
-- 存储过程的调用
CALL select_all_data();


-- 创建带OUT存储过程
CREATE PROCEDURE show_min_salary ( OUT ms DOUBLE ) BEGIN
	SELECT
		min( salary ) INTO ms 
	FROM
	employees;
END;
-- 存储过程的调用
-- 定义一个变量把结果存储起来
CALL show_min_salary(@ms);
-- 查看变量值
SELECT @ms;


-- 创建带IN存储过程
CREATE PROCEDURE show_someone_salary ( IN emp_name VARCHAR(20) ) 
BEGIN
	SELECT
		salary 
	FROM
		employees 
	WHERE
		last_name = emp_name;
END;
-- 存储过程的调用方式一
CALL show_someone_salary('King');
-- 存储过程的调用方式一
SET @emp_name  = 'King';
CALL show_someone_salary(@emp_name);


-- 创建带IN和OUT的存储过程
CREATE PROCEDURE show_someone_salary2 ( IN empname VARCHAR ( 20 ), OUT empsalary DOUBLE ) 
BEGIN
	SELECT
		salary INTO empsalary 
	FROM
		employees 
	WHERE
		last_name = empname;
END;
-- 存储过程的调用方式
CALL show_someone_salary2('Kochhar',@empsalary);
SELECT @empsalary;


-- 创建带INOUT的存储过程
CREATE PROCEDURE show_mgr_name (
	INOUT empname VARCHAR ( 20 )) 
BEGIN
	SELECT
		last_name INTO  
	FROM
		employees 
	WHERE
	manager_id = ( SELECT employee_id FROM employees WHERE last_name = empname );
END;
-- 存储过程的调用方式
SET @emp_name = 'De Haan';
CALL show_mgr_name(@emp_name);
SELECT @emp_name;

2.2 创建存储函数

2.2.1 语法

CREATE FUNCTION 函数名(参数名 参数类型,...)
RETURNS 返回值类型
[characteristics]
BEGIN
	函数体 #函数体中肯定有return语句
END;

2.2.2 代码

-- 创建空参函数方式一
CREATE FUNCTION email_by_name()
RETURNS VARCHAR(25)-- 返回的参数类型
-- 解决you might want to use the less safe log_bin_trust_function_creators variable问题
				DETERMINISTIC
				CONTAINS SQL
				READS SQL DATA
BEGIN
	RETURN (SELECT email FROM employees WHERE last_name = 'Abel');
END;


 -- 创建空参函数方式二
SET GLOBAL log_bin_trust_function_creators = 1; -- 解决you might want to use the less safe log_bin_trust_function_creators variable错误
CREATE FUNCTION email_by_name2()
RETURNS VARCHAR(25)
BEGIN
	RETURN (SELECT email FROM employees WHERE last_name = 'Abel');
END;
-- 函数调用
select email_by_name();


 -- 创建有参函数
 CREATE FUNCTION email_by_id(emp_id INT)
 RETURNS VARCHAR(25)
 BEGIN
		RETURN (SELECT email FROM employees WHERE employee_id = emp_id);
 END;
 -- 调用函数

3 delimiter的作用

-- 修改结束符为&
DELIMITER &
-- 修改结束符为;
DELIMITER ;

4 存储过程与存储函数的区别

此外，存储函数可以放在查询语句中使用，存储过程不行。反之，存储过程的功能更加强大，包括能够执行对表的操作（比如创建表，删除表等）和事务操作，这些功能是存储函数不具备的。

5 创建函数时错误解决方法

错误：

you might want to use the less safe log_bin_trust_function_creators variable

解决：

• 方式一:加上必要的函数特性“[NOT] DETERMINISTIC”和“[CONTAINS SQL|NO SQL|READS SQL DATA|MODIFIES SQL DATA}"
• 方式二： SET GLOBAL log_bin_trust_function_creators = 1;

6 存储过程和函数的查看、修改、删除

6.1 查看

-- 1.使用show create语句查看存储过程和函数的创建信息
-- 查看存储过程
SHOW CREATE PROCEDURE show_mgr_name;
-- 查看函数
SHOW CREATE FUNCTION email_by_id;

-- 2.使用show status语句查看存储过程和和函数的状态信息
-- 查看存储过程
SHOW PROCEDURE STATUS LIKE 'show_mgr_name';
-- 查看函数
SHOW FUNCTION STATUS LIKE 'email_by_id';

-- 3.从information_schema.Routines表中查看存储过程和函数的信息
SELECT * FROM information_schema.Routines WHERE ROUTINE_NAME = '存储过程名或函数名';
-- 重名的时候指定类型是函数还是存储过程
SELECT * FROM information_schema.Routines WHERE ROUTINE_NAME = 'show_mgr_name' AND ROUTINE_TYPE = 'PROCEDURE';

6.2 修改

ALTER PROCEDURE show_max_salary SQL SECURITY INVOKER COMMENT '查询最高工资';

6.3 删除

DROP PROCEDURE 存储过程名;
DROP FUNCTION 函数名;

7 储存过程的优缺点

7.1 优点

• 存储过程可以一次编译多次使用。存储过程只在创建时进行编译，之后的使用都不需要重新编译，这就提升了SQL的执行效率。
• 可以减少开发工作量。将代码封装成模块，实际上是编程的核心思想之一，这样可以把复杂的问题拆解成不同的模块，然后模块之间可以重复使用，在减少开发工作量的同时，还能保证代码的结构清晰。
• 存储过程的安全性强。我们在设定存储过程的时候可以设置对用户的使用权限，这样就和视图一样具有较强的安全性。
• 可以减少网络传输量。因为代码封装到存储过程中，每次使用只需要调用存储过程即可，这样就减少了网络传输量。
• 良好的封装性。在进行相对复杂的数据库操作时，原本需要使用一条一条的SQL语句，可能要连接多次数据库才能完成的操作，现在变成了一次存储过程，只需要连接一次即可。

7.2 缺点

• 可移植性差。存储过程不能跨数据库移植，比如在MySQL、Oracle和SQLServer里编写的存储过程，在换成其他数据库时都需要重新编写。
• 调试困难。只有少数DBMS支持存储过程的调试。对于复杂的存储过程来说，开发和维护都不容易。虽然也有一些第三方工具可以对存储过程进行调试，但要收费。
• 存储过程的版本管理很困难。比如数据表索引发生变化了，可能会导致存储过程失效。我们在开发软件的时候往往需要进行版本管理，但是存储过程本身没有版本控制，版本迭代更新的时候很麻烦。
• 它不适合高并发的场景。高并发的场景需要减少数据库的压力，有时数据库会采用分库分表的方式，而且对可扩展性要求很高，在这种情况下，存储过程会变得难以维护，增加数据库的压力，显然就不适用了。