《MySQL》数据定义语言DDL管理存储过程和存储函数

概念

简单的说，存储过程就是一个或多个 SQL 语句的集合，可以视作批处理文件，又不仅限于批处理。

创建存储过程

CREATE PROCEDURE <存储过程的名称> ([<参数列表>])
[<特性说明>] <存储过程的子程序>

参数列表

[IN | OUT | INOUT] <参数名> <参数的数据类型>

IN 表示输入参数， OUT 表示输出参数， INOUT 既可以输入也可以输出。

参数的数据类型可以是 MySQL 数据库中的任意类型。

可以逗号分割多个参数。

特性说明

• LANGUAGE SQL

  说明存储过程主体的代码为 SQL，目前支持的唯一值

• [NOT] DETERMINISTIC

  指明存储过程执行的结果是否具有确定性（相同的输入得到相同的输出）。默认为 NOT DETERMINISTIC

• CANTIANS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA

  指明子程序是否包含SQL，及包含哪种SQL，默认为 CANTIANS SQL

  • CANTIANS SQL： 包含，但不包含读写数据的，比如 SET @x = 1 或 DO RELEASE_LOCK('abc')

  • NO SQL： 不包含

  • READS SQL DATA：包含读数据的

  • MODIFIES SQL DATA：包含写数据的

• SQL SECURITY {DEFINER | INVOKER}

  指定执行权限。DEFINER 只有创建者有权执行，INVOKER 有权限的调用者可以执行

• COMMENT '<注释>'

  注释信息

子程序

以 BEGIN ... END; 来表示子程序的开始和结束。

子程序可以嵌套

创建存储函数

存储函数可以简单的理解成特殊的存储过程。

CREATE FUNCTION <存储函数的名称> ([<参数列表>])
RETURNS <返回值类型>
[<特性说明>] <存储函数的子程序>

参数列表

<参数名> <参数的数据类型>

参数的数据类型可以是 MySQL 数据库中的任意类型。

可以逗号分割多个参数。

返回值类型

返回值的数据类型可以是 MySQL 数据库中的任意类型。

特性说明

与存储过程用法相同，只不过存储函数只支持 [NOT] DETERMINISTIC, NO SQL 和 READS SQL DATA

存储过程与存储函数的差异

变量的使用

在存储过程和存储函数的子程序中可以声明局部变量来使用。

声明变量

DECLARE <变量名>[,<变量名>]... <变量类型> [DEFAULT <默认值>]

为变量赋值

• SET 为一个或多个变量赋值

  SET <变量名> = <表达式> [,<变量名> = <表达式>]...

• SELECT … INTO … 为一个或多个变量赋值

  SELECT <字段列表> INTO <变量列表> FROM <表名>

定义条件和处理程序

在存储过程和存储函数的子程序中可以设定遇到问题时的处理方式。

定义条件

DECLARE <条件名称> CONDITION FOR <错误类型>

错误类型可以是以下两种写法：

• SQLSTATE <5位字符串类型错误代码>

• <数字类型MySQL错误代码>

定义处理程序

特定条件触发特定处理程序

DECLARE <错误处理方式> HANDLER FOR <错误类型> <程序语句段>

错误处理方式：

• EXIT 遇到错误马上退出

• CONTINUE 遇到错误不处理，继续执行

• UNDO 遇到错误撤回之前的操作（MySQL暂时不支持）

错误类型：

• SQLSTATE <5位字符串类型错误代码>

• <条件名称>

  DECLARE CONDITION 定义的错误条件名称

• SQLWARNING

  匹配 01 开头的 SQLSTATE 错误代码

• NOT FOUND

  匹配 02 开头的 SQLSTATE 错误代码

• SQLEXCEPTION

  匹配所有没有被 SQLWARNING NOT FOUND 捕获的错误代码

• <数字类型MySQL错误代码>

光标的使用

查询语句可能返回多条记录，如果数据量非常大，需要在存储过程和存储函数中使用光标来逐条读取查询结果集中的记录。

声明光标

DECLARE <光标名称> CURSOR FOR <查询语句>

打开光标

OPEN <光标名称>

关闭光标

CLOSE <光标名称>

使用光标

FETCH <光标名称> INTO <变量列表>

子程序中各个声明的顺序

1. 变量和条件

2. 光标

3. 处理程序

流程控制语句

流程控制的每一个流程中可以使用单独的语句，也可以使用 BEGIN ... END; 构造的复合语句。

IF

IF <条件> THEN <条件为 true 时执行的语句>
    [ELSEIF <条件> THEN <条件为 true 时执行的语句>] ...
    [ELSE <其他情况下执行的语句>]
END IF;

CASE

CASE <表达式>
    WHEN <表达式的值> THEN <执行的语句>
    [WHEN <表达式的值> THEN <执行的语句>] ...
    [ELSE <其他情况下执行的语句>]
END CASE;

LOOP

单纯的循环，依靠 LEAVE 语句退出循环

[<标注名称>:]LOOP
    <执行的语句>
END LOOP [<标注名称>];

REPEAT

创建一个带条件判断的循环过程，每次循环结束都会判断条件表达式，如果为真，则结束循环，否则继续循环

[<标注名称>:] REPEAT
    <执行的语句>
UNTIL <条件判断>
END REPEAT [<标注名称>]

WHILE

创建一个带条件判断的循环过程，每次循环开始前都会判断条件表达式，如果为真，则继续循环，否则退出循环。

[<标注名称>:] WHILE <条件判断> DO
<执行的语句>
END EHILE [<标注名称>]

LEAVE

退出任何被标注的流程控制。

类似 PHP 的 break

LEAVE <标注名称>

ITERATE

用于循环语句段中，跳过余下未执行的语句，再次回到循环语句段的开头。只能用于 LOOP REPEAT WHILE。

类似 PHP 的 continue

ITERATE <循环的标注名称>

调用存储过程和存储函数

调用存储过程

CALL <存储过程名称>(<参数列表>)

参数列表：

• 对于 IN 参数可以使用变量或者常值。

• 对于 OUT 和 INOUT 参数必须是变量才能接收输出的值

用户变量使用 select @<变量名> 查看

调用存储函数

可以像调用 MySQL 内置函数一样调用存储函数

查看存储过程和存储函数

SHOW PROCEDURE STATUS [LIKE '<关键字>']

SHOW FUNCTION STATUS [LIKE '<关键字>']

SHOW CREATE PROCEDURE <存储过程名称>

SHOW CREATE FUNCTION <存储函数名称>

修改存储过程和存储函数

只能修改存储过程和存储函数的特性，子程序一旦定义不能修改

ALTER {PROCEDURE | FUNCTION} <名称> <新特性>

删除存储过程和存储函数

DROP {PROCEDURE | FUNCTION} [IF EXISTS] <名称>

存储过程的优点

1. 重复使用

   存储的程序对任何应用程序都是可重用的和透明的。 存储过程将数据库接口暴露给所有应用程序，以便开发人员不必开发存储过程中已支持的功能。

2. 减少网络流量

   存储过程有助于减少应用程序和数据库服务器之间的流量，因为应用程序不必发送多个冗长的SQL语句，而只用发送存储过程的名称和参数。

3. 安全性

   参数化的存储过程可以防止SQL注入式攻击。可以只授予访问存储过程的权限，而不必授予访问数据库表的权限

4. 执行速度

   通常存储过程有助于提高应用程序的性能。当创建，存储过程被编译之后，就存储在数据库中。 但是，MySQL实现的存储过程略有不同。 MySQL存储过程按需编译。 在编译存储过程之后，MySQL将其放入缓存中。 MySQL为每个连接维护自己的存储过程高速缓存。 如果应用程序在单个连接中多次使用存储过程，则使用编译版本，否则存储过程的工作方式类似于查询。

存储过程的缺点

1. MySQL对SQL编程和复杂查询 性能优化的支持很差

   如果使用大量存储过程，那么使用这些存储过程的每个连接的内存使用量将会大大增加。 此外，如果您在存储过程中过度使用大量逻辑操作，则CPU使用率也会增加。

2. MySQL 很难调试存储过程。

3. 最大的缺点是会增加数据库服务器的压力，尤其是高并发的业务，如互联网应用

争议

为什么网上那么多吐槽存储过程的，甚至阿里的开发手册明确禁止使用存储过程？

一方面是因为 MySQL 的存储过程起步较晚，各方面支持很差，另一方面是因为互联网高并发的场景，单次请求涉及数据少，数据关系简单，但是更新频率高；工程的迭代速率高，数据关系随时可能扩展修改，很多数据都是分库分表的，性能瓶颈往往在数据库，所以原则是对db的保护做到最大化，能减少db压力的就减少db压力，尽量把运算逻辑拉到代码里面，数据库只做数据容器。

而企业级应用中，例如金融、企业、政府应用，开发面对的情况是经常要大批量的处理数据，表都很大，表关系也复杂，十几个表关联不是什么大不了的情况。数据处理流程长，不用存储过程只会让事情更加复杂。这些应用中对数据库请求量相比互联网企业来说是非常低的，相对不用太关心数据库压力问题，这种时候把一些操作放到数据存储过程里可以兼顾效率和开发成本。