【MySQL】存储过程与存储函数

【MySQL】存储过程与存储函数

1. 存储过程与函数概述

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

2. 创建存储过程

2.1 语法分析

创建存储过程的语法格式如下：

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

说明：

1. 参数修饰符代表的含义：

   • IN：代表当前参数为输入参数，存储过程可以读取这个参数，当没有指定参数修饰符时默认为IN
   • OUT：代表当前参数为输出参数，存储过程执行时会将结果存储到该输出参数中
   • INOUT：代表当前参数既可以是输入参数也可以是输出参数

2. [characteristics]表示创建存储过程时对于存储过程的约束条件

   其取值信息如下：

   LANGUAGE SQL
   | [NOT] DETERMINISTIC
   | {CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA}
   | SQL SECURITY {DEFINER | INVOKER}
   | COMMENT 'string'
   

   • LANGUAGE SQL：代表当前存储过程使用的是SQL语句，当前系统支持SQL
   • [NOT] DETERMINISTIC：指明当前存储过程执行结果是否是确定的，即相同的输入是否会得到相同的输出，如果相同则表明是却确定的为DETERMINISTIC反之则为NOT DETERMINISTIC
   • {CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA}：用来指定子程序使用SQL的限制
     • CONTAINS SQL：代表子程序包含SQL语句，但是不包含读写数据的SQL语句，为系统的默认方式
     • NO SQL：代表子程序不包含SQL语句
     • READS SQL DATA：代表子程序包含读取数据的SQL语句
     • MODIFIES SQL DATA：代表子程序包含写数据的SQL语句
   • SQL SECURITY：用来指定当前存储过程的权限
     • DEFINER：表明只有当前存储过程的创建者或者定义者才能够执行当前存储过程
     • INVOKER：表明只有当前具有存储过程的访问权限才能够执行当前存储过程

3. 通常定义存储过程我们需要设置新的结束标记，通过使用DELIMITER 符号的方式，防止在SQL语句中因为分号而误以为语句提前结束，例如：

   DELIMITER $
   
   CREATE PROCEDURE 存储过程名(IN|OUT|INOUT 参数名 参数类型,...)
   [characteristics...]
   BEGIN
   	sql 语句1;
   	sql 语句2;
   END $
   
   DELIMITER ;
   

2.2 代码举例

举例1：创建存储过程select_all_data()，查看 emps 表的所有数据

DELIMITER $

CREATE PROCEDURE select_all_data()
BEGIN
	SELECT * FROM emps;
END $

DELIMITER ;
CALL select_all_data(); # 调用存储过程

举例2：创建存储过程avg_employee_salary()，返回所有员工的平均工资

DELIMITER $

CREATE PROCEDURE avg_employee_salary()
BEGIN
	SELECT AVG(salary) FROM emps;
END $

DELIMITER ;

CALL avg_employee_salary(); # 调用存储过程

举例3：创建存储过程show_max_salary()，用来查看“emps”表的最高薪资值。

DELIMITER $

CREATE PROCEDURE show_max_salary()
BEGIN 
	SELECT MAX(salary) FROM emps;
END $

DELIMITER ;
CALL show_max_salary(); # 调用存储过程

举例4：创建存储过程show_min_salary()，查看“emps”表的最低薪资值。并将最低薪资通过OUT参数“ms”
输出

DELIMITER $
CREATE PROCEDURE show_min_salary(OUT ms DOUBLE(8,2))
BEGIN
	SELECT MIN(salary) INTO ms
	FROM emps;
END $
DELIMITER ;

CALL show_min_salary(@ms);
SELECT @ms;

注：这里的ms输出参数在定义时类型需要符合原表内的类型约束

举例5：创建存储过程show_someone_salary()，查看“emps”表的某个员工的薪资，并用IN参数empname
输入员工姓名

DELIMITER $

CREATE PROCEDURE show_someone_salary(IN empname VARCHAR(25))
BEGIN
	SELECT salary FROM emps
	WHERE last_name = empname;
END $

DELIMITER ;

SET @empname = 'Abel'; # 变量赋值
CALL show_someone_salary(@empname); # 调用存储过程

注：这里使用了SET @empname = 'Abel'的语法对变量进行赋值

举例6：创建存储过程show_someone_salary2()，查看“emps”表的某个员工的薪资，并用IN参数empname
输入员工姓名，用OUT参数empsalary输出员工薪资。

DELIMITER $

CREATE PROCEDURE show_someone_salary2(IN empname VARCHAR(25),OUT empsalary DOUBLE(8,2))
BEGIN
	SELECT salary INTO empsalary
	FROM emps WHERE last_name = empname;
END $

DELIMITER ;

SET @empname := 'Abel';
CALL show_someone_salary2(@empname,@empsalary);
SELECT @empsalary;

举例7：创建存储过程show_mgr_name()，查询某个员工领导的姓名，并用INOUT参数“empname”输入员
工姓名，输出领导的姓名

DELIMITER $

CREATE PROCEDURE show_mgr_name(INOUT empname VARCHAR(25))
BEGIN
	SELECT last_name INTO empname FROM emps
	WHERE employee_id = (
		SELECT manager_id FROM emps
		WHERE last_name = empname);	
END $

DELIMITER ;

SET @empname = 'Kochhar';
CALL show_mgr_name(@empname);
SELECT @empname;

3. 调用存储过程

存储过程的调用有多种格式，但是都必须通过关键字CALL调用

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

格式：

1. 调用IN模式的参数

   CALL sp1('值')
   

2. 调用OUT模式的参数

   SET @name;
   CALL sp1(@name);
   SELECT @name;
   

3. 调用INOUT模式的参数

   SET @name = '值';
   CALL sp1(@name);
   select @name;
   

4. 存储函数的使用

4.1 语法分析

语法格式：

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

说明：

1. 参数列表，其中IN、OUT、INOUT只对于存储过程有效，对于存储函数来说参数修饰符默认为IN
2. RETURNS语句表示函数返回数据的类型，返回值对于函数FUNCTION而言时强制的，并且在函数体中必须要有RETURN子句的出现
3. characteristics表示存储函数的约束，与存储过程中描述的一致，这里不再重复赘述
4. 函数体也可以用begin和end表示SQL语句的开始与结束，若只有一行也可以省略

4.2 调用格式

在MySQL中，存储函数与系统函数的使用是一样的，本质上没有什么区别。只是系统函数是由MySQL开发者定义的，而存储函数则是由用户自定义的

调用语法格式：

select 函数名(实参列表);

4.3 代码举例

举例1：创建存储函数，名称为email_by_name()，参数定义为空，该函数查询Abel的email，并返回，数据类型为
字符串型

DELIMITER $

CREATE FUNCTION email_by_name()
RETURNS VARCHAR(25)
DETERMINISTIC

BEGIN
  RETURN (SELECT email FROM emps
  WHERE last_name = 'Abel'); 
END $

DELIMITER ;

SELECT email_by_name();

注：如果不添加约束[characteristics]，也许运行会报错，此时只要加上约束即可

举例2：创建存储函数，名称为email_by_id()，参数传入emp_id，该函数查询emp_id的email，并返回，数据类型
为字符串型。

DROP FUNCTION email_by_id;
DELIMITER $

CREATE FUNCTION email_by_id(emp_id INT)
RETURNS VARCHAR(25)
DETERMINISTIC
BEGIN
	RETURN (SELECT email FROM emps
	WHERE employee_id = emp_id);
END $

DELIMITER ;

SELECT email_by_id(100);

举例3：创建存储函数count_by_id()，参数传入dept_id，该函数查询dept_id部门的员工人数，并返回，数据类型
为整型

DELIMITER $

CREATE FUNCTION count_by_id(dept_id INT)
RETURNS INT
DETERMINISTIC
BEGIN
	RETURN (SELECT COUNT(*) FROM emps
	WHERE department_id = dept_id);
END $

DELIMITER ;

SELECT count_by_id(80);

5. 存储过程与函数的查看、修改、删除

5.1 查看

我们创建了存储过程、存储函数，那么是否可以观察到呢？这里介绍三种方法

1. SHOW CREATE语句查看存储过程与存储函数的创建信息

   基本语法结构如下：

   SHOW CREATE {PROCEDURE | FUNCTION} 存储过程名或函数名

   举例：

   SHOW CREATE FUNCTION count_by_id;

   

2. SHOW STATUS语句查看存储过程与函数的状态信息

   基本语法结构如下：

   SHOW {PROCEDURE | FUNCTION} STATUS [LIKE pattern]

   举例：

   SHOW PROCEDURE STATUC LIKE 'select_all_data'

   

3. 从表information_schema.Routines表中查看存储过程与存储函数的信息

   MySQL中存储过程与存储函数的信息会保存在系统数据库表中，我们可以通过information_schema.Routines进行查看

   基本语法格式如下：

   SELECT * FROM information_schema.Routines WHERE ROUTINE_NAME = '存储过程或函数名' AND ROUTINE_TYPE = {'PROCEDURE' | 'FUNCTION'}

   举例：

   SELECT * FROM information_schema.Routines WHERE ROUTINE_NAME = 'avg_employee_salary' AND ROUTINE_TYPE = 'PROCEDURE';

   

5.2 修改存储过程与存储函数

这里的修改作用范围很小，并不能修改内部的存储过程或存储函数体内容，只能修改建立存储过程以及存储函数时的约束，即之前所提到的[characteristics]

语法格式：

ALTER FUNCTION count_by_id CONTAINS SQL COMMENT '查询部门员工人数';

再次查询：

5.3 删除存储过程与存储函数

语法格式：

DROP {PROCEDURE | FUNCTION} IF EXISTS 存储过程或存储函数名

举例：

DROP PROCEDURE IF EXISTS select_all_data;
DROP FUNCTION IF EXISTS count_by_id;

6. 关于存储过程的争议

存储过程的优点：

1. 体现了较好的封装性
2. 存储过程实现了一次编译多次运行，提高代码复用率
3. 存储过程封装了代码，使用时只需要调用。减少了网络传输量
4. 存储过程可以设置用户权限，提高了安全性

存储过程的缺点：

1. 可移植性差，不同DBMS语法差异比较大
2. 调试比较困难
3. 不适合高并发中分库分表的情景

(img-75V3miCN-1698831122611)]

5.3 删除存储过程与存储函数

语法格式：

DROP {PROCEDURE | FUNCTION} IF EXISTS 存储过程或存储函数名

举例：

DROP PROCEDURE IF EXISTS select_all_data;
DROP FUNCTION IF EXISTS count_by_id;

6. 关于存储过程的争议

存储过程的优点：

1. 体现了较好的封装性
2. 存储过程实现了一次编译多次运行，提高代码复用率
3. 存储过程封装了代码，使用时只需要调用。减少了网络传输量
4. 存储过程可以设置用户权限，提高了安全性

存储过程的缺点：

1. 可移植性差，不同DBMS语法差异比较大
2. 调试比较困难
3. 不适合高并发中分库分表的情景