MySQL十六：存储过程与存储函数

存储过程与存储函数

一、概述

1. 相当于Java中的方法

2. 定义

   程序化的SQL，预先存储在MySQL服务器上，直接操作底层数据表，需要执行的时候，客户端只需向服务端发出调用命令即可

3. 优点

   清晰、安全、减少网络传输量

4. 分类

   ① 无参无返回

   ② 有参无返回：带IN

   ③ 无参有返回：带OUT

   ④ 有参有返回：既带IN，又带OUT

二、创建过程

1. 语法格式

   DELIMITER //
   CREATE PROCEDURE 过程名(IN/OUT/INOUT 参数名 参数类型, ...)
   [characteristics ...]
   BEGIN
   	SQL语句1;
   	SQL语句2;
   	...
   END //
   DELIMITER ;
   

2. 参数说明

   IN：输入参数

   OUT：输出参数

   INOUT：既是输入参数、也是输出参数

3. characteristics

   ① LANGUAGE SQL

   存储过程执行体是由SQL语句组成

   ② [NOT] DETERMINISTIC

   存储过程执行的结果是否确定。

   不加NOT，相同的输入得到相同的输出；加NOT，相同的输入可能得到不同的输出

   ③ 子程序使用SQL语句的限制

   CONTAINS SQL：默认，子程序包含SQL语句，不包含读写数据的SQL语句

   NO SQL：子程序不包含SQL语句

   READS SQL DATA：子程序中包含读数据的SQL

   MODIFIES SQL DATA：子程序中包含写数据的SQL

   ④SQL SECURITY DEFINER / INVOKER

   DEFINER：创建者或定义这才能执行当前存储过程（默认）

   INVOKER：拥有当前存储过程的访问权限的用户能够执行当前存储过程

   ⑤ COMMENT ‘string’

   注释信息

4. DELIMITER

   设置新的结束标记，一般设置为 ‘ // ’ 或 ’ $ '，避免使用反斜杠

   注：在Navicat中，软件自动设置了DELIMITER，不需要手动操作

三、创建和调用

1. IN

   # 创建
   DELIMITER //
   CREATE PROCEDURE show_someone_salary(IN empname VARCHAR(20))
   BEGIN
   	SELECT salary 
   	FROM emps
   	WHERE ename = empname;
   END //
   DELIMITER ;
   # 调用
   SET @empname = '值';
   CALL show_someone_salary(@empname);
   

2. OUT

   # 创建
   DELIMITER //
   CREATE PROCEDURE show_someone_salary(OUT ms DOUBLE)
   BEGIN
   	SELECT MIN(salary) INTO ms 
   	FROM emps;
   END //
   DELIMITER ;
   # 调用
   SET @ms;
   CALL show_someone_salary(@ms);
   SELECT @ms;
   

3. INOUT

   # 创建
   DELIMITER //
   CREATE PROCEDURE show_mgr_name(INOUT empname VARCHAR(20))
   BEGIN
   	# 此处的empname参数是OUT
   	SELECT ename into empname
       FROM emps
       # 此处的empname参数是IN
   	WHERE eid = (ename = empname);
   END //
   DELIMITER ;
   # 调用
   SET @empname = '值';
   CALL show_mgr_name(@empname);
   SELECT @empname;
   

四、存储函数

1. 语法格式

   DELIMITER //
   CREATE FUNCTION 函数名(参数名1 参数类型, 参数名2 参数类型 ...)
   RETURNS 返回值类型
   [characteristics ...]
   BEGIN
   	函数体;
   	RETURN  value;
   END //
   DELIMITER ;
   

2. 调用函数

   SELECT 函数名(参数列表);
   

3. 注意

   若创建存储函数报错：you might want to use the less safe log_bin_trust_function_creators variable，则需进行如下设置

   SET GLOBAL  log_bin_trust_function_creators = 1;
   

五、对比存储过程和存储函数

1. 对比表

   关键字	调用语法	返回值	应用场景
   存储过程	PROCEDURE	CALL 存储过程名	0个或多个
   存储函数	FUNCTION	SELECT 函数名	只能由一个

2. 各自优势

   存储函数可以放在查询语句中使用，存储过程不行

   存储过程功能更强大，可以执行对表和事务操作，存储函数不行

六、查改删

1. 查看

   # 查看创建信息
   SHOW CREATE PROCEDURE/FUNCTION 过程名/函数名;
   
   # 查看过程/函数的状态信息（数据库、名称、类型、创建者...）,如果不写模糊查询，就列出所有过程/函数的信息
   SHOW PROCEDURE/FUNCTION STATUS [LIKE '']
   
   # 从information_schema.Routinies表中查看存储过程/函数的信息
   SELECT * FROM information_schema.Routinies
   WHERE ROUTIME_NAME = '过程/函数名'
   AND TOUTIME_TYPE = 'PROCEDURE/FUNCTION';
   

2. 修改

   # 只修改相关特性，不修改功能体
   ALTER PROCEDURE/FUNCTION 过程名/函数名
   [characteristic]
   

3. 删除

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

七、存储过程的优缺点

1. 优点

   ① 一次编译多次使用

   ② 减少开发工作量

   ③ 安全性强

   ④ 减少网络传输量

   ⑤ 良好的封装性

2. 缺点

   ① 可移植性差

   ② 调试困难

   ③ 版本管理困难

   ④ 不适合高并发场景

3. 阿里开发规范

   进制使用存储过程，存储过程难以调试和扩展，更没有移植性