存储过程
MySQL 存储过程（Stored procedure）是一种存储在数据库中的程序。它可以包含多个 SQL 语句，提供许多过程语言的功能，例如变量定义、条件语句、循环语句、游标以及异常处理等。存储过程没有返回值，但是它可以通过输出参数实现数据的返回，同时还可以产生一个查询结果返回到客户端。
在数据库中创建存储过程之后，应用程序或其他存储过程可以通过名称对其进行重复调用。
在介绍存储过程的管理之前，我们还需要了解一下它的优缺点，存储过程的主要优点包括：
- 实现代码的重用和集中管理。存储过程存储在数据库中，可以在被重复调用，不同的应用可以共享存储过程；
- 实现业务的封装和隔离。应用程序通过接口访问存储过程；当业务发生变化时，只需要修改存储过程的逻辑，对应用程序毫无影响；
- 减少了应用与数据库之间的网络流量。调用存储过程时，只需要传递参数，在一定程度上可以减轻网络负担；
- 存储过程可以提高安全性。应用程序通过存储过程进行数据访问，而不需要直接访问数据表，保证数据的安全。
不过，存储过程也存在一些缺点：
- MySQL 存储过程的语法和其他数据库之间不兼容，无法直接移植；
- 存储过程需要占用数据库服务器的资源，包括 CPU、内存等。MySQL 对于大量逻辑处理的支持不够完善；
- 存储过程的开发和维护需要专业的技能。MySQL 存储过程不支持调试功能，增加了应用程序的开发和维护难度。
一般来说，对于业务快速变化的互联网应用，倾向于将业务逻辑放在应用层，便于扩展；对于传统行业，或者复杂的报表分析，合理使用存储过程可以提高效率。
创建存储过程
MySQL 使用CREATE PROCEDURE语句创建存储过程：
CREATE PROCEDURE sp_name (
[ IN | OUT | INOUT ] param_name data_type,
...
)
routine_body;其中，sp_name 是存储过程名称；param_name 是参数名称，IN 表示输入参数（默认模式），OUT 表示输出参数，INOUT 表示输入输出参数；data_type 是参数的数据类型；routine_body 是存储过程的具体实现。
下面是一个创建存储过程的示例：
DELIMITER $$
CREATE PROCEDURE GetDevelopers()
BEGIN
SELECT emp_name, sex, hire_date, salary, bonus, email
FROM employee
WHERE dept_id = 4;
END$$
DELIMITER ;其中，DELIMITER不属于存储过程的内容。由于很多 MySQL 客户端将分号（;）作为 SQL 语句的终止符，而存储过程中包含多个语句；为了将存储过程的定义整体发送到服务器，需要将终止符临时修改为其他符号（例如 \$\$），最后再将其改回分号。BEGIN 和 END 表示程序主体的开始和结束。
存储过程 GetDevelopers 没有参数，程序体中包含了一个查询语句，用于返回开发部门的员工信息。MySQL 使用 CALL 语句调用存储过程，例如：
CALL GetDevelopers();
emp_name|sex|hire_date |salary |bonus|email |
--------|---|----------|--------|-----|-------------------|
赵云 |男 |2005-12-19|15000.00| 0.00|zhaoyun@shuguo.com |
廖化 |男 |2009-02-17| 6500.00| 0.00|liaohua@shuguo.com |
关平 |男 |2011-07-24| 6800.00| 0.00|guanping@shuguo.com|
赵氏 |女 |2011-11-10| 6600.00| 0.00|zhaoshi@shuguo.net |
关兴 |男 |2011-07-30| 7000.00| 0.00|guanxing@shuguo.com|
张苞 |男 |2012-05-31| 6500.00| 0.00|zhangbao@shuguo.com|
赵统 |男 |2012-05-03| 6000.00| 0.00|zhaotong@shuguo.com|
周仓 |男 |2010-02-20| 8000.00| 0.00|zhoucang@shuguo.com|
马岱 |男 |2014-09-16| 5800.00| 0.00|madai@shuguo.com |MySQL 在第一次调用存储过程时查找并编译它的源码，并且将编译结果放入缓存，然后执行该存储过程。如果在同一个会话中再次调用该存储过程，MySQL 直接从缓存中执行，而不需要重新编译。
我们再创建一个存储过程 GetEmpNumBySex，并且为其指定两个参数，按照性别查询员工的数量：
DELIMITER $$
CREATE PROCEDURE GetEmpNumBySex (
IN psex VARCHAR(10),
OUT pnum INT
)
BEGIN
SELECT COUNT(1)
INTO pnum
FROM employee
WHERE sex = psex;
END$$
DELIMITER ;其中，psex 是输入参数，类型为 VARCHAR(10)；pnum 是输出参数，类型为 INTEGER；在存储过程内部，通过一个查询返回指定性别的员工数量并赋值给 pnum。调用该存储过程的示例如下：
CALL GetEmpNumBySex('女', @num);
SELECT @num;
@num|
----|
6|会话变量 @num 用于接收存储过程返回的参数值。
MySQL 存储过程的输入参数使用 IN 模式表示，在存储过程内部使用输入参数的副本进行操作，不会改变外部传入的参数值；输出参数使用 OUT 模式表达，在存储过程内部对其进行赋值并返回给调用程序，存储过程无法使用输出参数的初始值；输入输出参数使用 INOUT 模式组成，它是 IN 和 OUT 参数的组合。
另外，在创建存储过程时还可以指定一些可选的属性：
CREATE PROCEDURE sp_name (
[ IN | OUT | INOUT ] param_name data_type,
...
)
[
COMMENT 'string'
| LANGUAGE SQL
| [NOT] DETERMINISTIC
| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }
]
routine_body;其中，COMMENT 可以为存储过程添加注释；LANGUAGE表示编写存储过程的语言，目前 SQL 是唯一可用值；DETERMINISTIC 属性表示这是一个确定性的存储函数，对于相同的输入参数一定会返回相同的结果；MySQL 默认创建的是非确定性函数（NOT DETERMINISTIC）。
CONTAINS SQL 表示程序中不包含读取或者写入数据表的语句，这是默认设置。举例来说，SET @x = 1 或者DO RELEASE_LOCK('abc')不会读写任何数据。NO SQL 表示程序不包含任何 SQL 语句。READS SQL DATA 表示程序包含读取操作（例如 SELECT），但不会修改数据表。MODIFIES SQL DATA 表示程序包含写入操作（例如 INSERT 或者DELETE）。这些属性仅供 MySQL 服务器参考使用，不会用于限制程序中实际使用的语句。
SQL SECURITY 属性表示存储过程以定义者权限（DEFINER ）还是调用者权限（INVOKER ）执行。
查看存储过程
使用 SHOW PROCEDURE STATUS 语句查看存储过程的列表和属性：
SHOW PROCEDURE STATUS
[LIKE 'pattern' | WHERE expr]LIKE 用于匹配存储过程的名称，WHERE 可以指定更多的过滤条件。例如，以下语句返回了存储过程 GetDevelopers 的相关信息：
SHOW PROCEDURE STATUS WHERE name = 'GetDevelopers';
Name |Value |
--------------------|-------------------|
Db |hrdb |
Name |GetDevelopers |
Type |PROCEDURE |
Definer |root@% |
Modified |2020-09-27 05:32:44|
Created |2020-09-27 05:32:44|
Security_type |DEFINER |
Comment | |
character_set_client|utf8mb4 |
collation_connection|utf8mb4_0900_ai_ci |
Database Collation |utf8mb4_0900_ai_ci |返回的信息包括数据库、存储过程名称、类型（PROCEDURE）、创建者、创建时间和修改时间、调用权限以及字符集信息。
使用 SHOW CREATE PROCEDURE 语句查看存储过程的定义。例如：
SHOW CREATE PROCEDURE GetDevelopers;
Name |Value |
--------------------|----------------------------------------------------------------------------------------------------------------------------------------------------------------|
Procedure |GetDevelopers |
sql_mode |ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION |
Create Procedure |CREATE DEFINER=`root`@`%` PROCEDURE `GetDevelopers`()
BEGIN
SELECT emp_name, sex, hire_date, salary, bonus, email
FROM employee
WHERE dept_id = 4;
END|
character_set_client|utf8mb4 |
collation_connection|utf8mb4_0900_ai_ci |
Database Collation |utf8mb4_0900_ai_ci 另外，我们也可以通过系统表 information_schema.routines 查看存储过程和函数的信息。
修改存储过程
如果想要为存储过程增加或删除某个参数，或者修改存储过程的定义，只能通过下文中的DROP PROCEDURE语句删除该存储过程，然后再次使用CREATE PROCEDURE语句创建存储过程。
MySQL 中的ALTER PROCEDURE语句只能用于修改存储过程的某些属性：
ALTER PROCEDURE proc_name
COMMENT 'string'
| LANGUAGE SQL
| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }这些属性和 CREATE PROCEDURE 语句中的属性一样。
删除存储过程
MySQL 使用 DROP PROCEDURE 语句删除存储过程：
DROP PROCEDURE [IF EXISTS] sp_name;如果删除的存储过程不存在，将会返回一个错误信息；使用 IF EXISTS 可以避免该错误。例如：
DROP PROCEDURE IF EXISTS GetDevelopers;管理存储函数
MySQL 存储函数（Stored function）和存储过程类似，也是存储在数据库中的程序，但是它会返回一个计算结果。存储函数可以和内置函数或者表达式一样用于 SQL 语句，可以提高代码的可读性以及可维护性。
MySQL 存储过程和存储函数统称为存储例程（Stored routine）。MySQL 还支持一种创建函数的方式，就是通过编写 C++ 程序实现扩展的自定义函数（User-Defined Function）
存储函数的管理和存储过程类似，创建存储函数使用CREATE FUNCTION语句：
CREATE FUNCTION sp_name (
param_name data_type,
...
)
RETURNS data_type
routine_body;其中，sp_name 是存储函数名称；param_name 是参数名称，所有的参数都是输入参数；data_type 是参数或者返回值的数据类型；RETURNS 定义了返回值的类型；routine_body 是存储过程的具体实现。
例如，以下语句创建了一个函数 add2：
DELIMITER $$
CREATE FUNCTION add2(
p1 int,
p2 int
)
RETURNS int
DETERMINISTIC
BEGIN
RETURN p1 + p2;
END $$
DELIMITER ;函数 add2 用于计算两个整数的和；DETERMINISTIC 属性表示这是一个确定性函数，对于相同的输入参数一定会返回相同的结果；MySQL 默认创建的是非确定性函数（NOT DETERMINISTIC）。
使用以下语句测试一下 add2 函数：
SELECT add2(2, 3);
add2(2, 3)|
----------|
5|查看存储函数的方式和存储过程类似，只需要将 PROCEDURE 替换成 FUNCTION 即可：
SHOW FUNCTION STATUS WHERE name = 'add2';
Name |Value |
--------------------|-------------------|
Db |hrdb |
Name |add2 |
Type |FUNCTION |
Definer |root@% |
Modified |2020-09-27 07:01:38|
Created |2020-09-27 07:01:38|
Security_type |DEFINER |
Comment | |
character_set_client|utf8mb4 |
collation_connection|utf8mb4_0900_ai_ci |
Database Collation |utf8mb4_0900_ai_ci |修改存储函数的定义也只能通过DROP FUNCTION语句先删除，然后再次使用CREATE FUNCTION语句创建存储函数。MySQL 中的ALTER FUNCTION语句只能用于修改存储函数的某些属性：
ALTER FUNCTION proc_name
COMMENT 'string'
| LANGUAGE SQL
| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }这些属性也可以在 CREATE FUNCTION 语句中指定。
MySQL 使用DROP FUNCTION语句删除存储函数：
DROP FUNCTION [IF EXISTS] sp_name;如果删除的存储函数不存在，将会返回一个错误信息；使用 IF EXISTS 可以避免该错误。例如：
DROP FUNCTION IF EXISTS add2;存储过程变量
变量（Variable）是存储过程和函数内部用于存储中间结果的对象。这些变量属于局部变量，作用域限定在存储过程中。在使用存储过程变量之前需要使用DECLARE语句声明变量：
DECLARE variable_name datatype(size) [DEFAULT default_value];其中，DECLARE 表示定义变量，必须位于 BEGIN 和 END 之间并且是第一个语句；variable_name 是变量名；datatype(size) 是变量的类型和长度；DEFAULT default_value 用于为变量指定一个默认值，否则默认值为 NULL。例如，以下是一些变量的定义：
DECLARE salary DECIMAL(8, 2) DEFAULT 0.0;
DECLARE x, y INTEGER DEFAULT 0;salary 是 DECIMAL(8, 2) 类型的变量，默认值为 0.0；x 和 y 是 INTEGER 类型变量，默认值为 0。
声明了变量之后，可以使用SET语句进行赋值：
SET variable_name = value;或者使用SELECT INTO语句将查询的结果赋值给变量：
SELECT expression1, ...
INTO variable1, ...
FROM ...;
SELECT expression1, ...
FROM ...
INTO variable1, ...;SELECT 返回的表达式个数和变量的个数相同，查询语句最多只能返回一行数据，可以使用 LIMIT 1 进行限定。
以下语句创建了一个存储过程 TestProc：
DELIMITER $$
CREATE PROCEDURE TestProc()
BEGIN
DECLARE sal decimal(8, 2) DEFAULT 0.0;
DECLARE x, y integer DEFAULT 0;
SELECT salary
FROM employee
WHERE emp_name = '张飞'
INTO sal;
SET x = 6, y = 9;
SELECT sal, x, y;
END$$
DELIMITER ;调用该存储过程查看变量的赋值结果：
CALL TestProc();
sal |x|y|
--------|-|-|
24000.00|6|9|对于存储过程变量，它的作用域（生命周期）位于存储过程定义中的 BEGIN 和 END 语句之间。如果在嵌套的 BEGIN 和 END 语句之间定义的变量，作用域只在这个模块内部。如果不同作用域中存在同名的变量，在作用域内有效范围更小的变量优先级更高。例如：
DROP PROCEDURE TestProc;
DELIMITER $$
CREATE PROCEDURE TestProc()
BEGIN
DECLARE x integer DEFAULT 0;
SET x = 6;
SELECT 'outer', x;
BEGIN
DECLARE x integer DEFAULT 0;
SET x = 7;
SELECT 'inner', x;
END;
END$$
DELIMITER ;存储过程中先定义了一个个变量 x，然后在嵌套模块中又定义了同名的变量。调用该存储过程的输出结果如下：
CALL TestProc();
outer|x|
-----|-|
outer|6|
inner|x|
-----|-|
inner|7|在嵌套模块中查询返回的是该模块中的变量 x。虽然 MySQL 支持这种同名变量的使用，但是建议尽量不要这样使用，避免引起混淆。
条件控制语句
MySQL 提供了两种条件控制语句：IF 语句和 CASE 语句。
IF 语句
IF 语句可以实现基本的条件判断结构，语法如下：
IF search_condition THEN statement_list
[ELSEIF other_condition THEN other_statement_list]
...
[ELSE else_statement_list]
END IF;如果 search_condition 的结果为 true，指定对应 THEN 之后的 statement_list 语句列表；否则，如果存在可选的 ELSEIF 并且 other_condition 结果为 true，执行对应的 other_statement_list 语句列表；依次类推；否则，如果存在可选的 ELSE，执行对应的 else_statement_list 语句列表。
例如：
DROP PROCEDURE TestProc;
DELIMITER $$
CREATE PROCEDURE TestProc()
BEGIN
DECLARE x, y integer DEFAULT 0;
SET x = 5, y = 10;
IF x = 5 THEN
SELECT 'x = 5';
END IF;
IF y < 10 THEN
SELECT 'y < 10';
ELSEIF y > 10 THEN
SELECT 'y > 10';
ELSE
SELECT 'y = 10';
END IF;
END$$
DELIMITER ;调用该存储过程的输出结果如下：
CALL TestProc();
x = 5|
-----|
x = 5|
y = 10|
------|
y = 10|CASE语句
CASE 语句可以用于构造复杂的条件判断结构，支持两种形式的语法。第一种简单形式的 CASE 语句如下：
CASE case_value
WHEN when_value1 THEN statement_list1
[WHEN when_value2 THEN statement_list2]
...
[ELSE else_statement_list]
END CASE;其中，case_value 是一个表达式；首先使用该表达式的值和 when_value1 进行比较，如果相等则执行 statement_list1 语句列表并结束 CASE 语句；否则，如果表达式等于可选的 when_value2，则执行 statement_list2 并结束 CASE 语句；依次类推；最后，如果存在可选的 ELSE，执行 else_statement_list 语句列表；如果此时没有定义 ELSE 子句，将会返回“Case not found for CASE statement”错误。
第二种形式的搜索 CASE 语句如下：
CASE
WHEN search_condition1 THEN statement_list1
[WHEN search_condition2 THEN statement_list2]
...
[ELSE else_statement_list]
END CASE;首先，判断 search_condition1 是否为 true；如果是则执行 statement_list1 语句列表；否则，判断 search_condition2 是否为 true，如果是则执行 statement_list2 语句列表；依此类推；最后，如果存在可选的 ELSE，执行 else_statement_list 语句列表；如果此时没有定义 ELSE 子句，将会返回“Case not found for CASE statement”错误。
例如：
DROP PROCEDURE TestProc;
DELIMITER $$
CREATE PROCEDURE TestProc()
BEGIN
DECLARE x integer DEFAULT 0;
SET x = 5;
CASE
WHEN x < 0 THEN
SELECT 'x < 0';
WHEN x = 0 THEN
SELECT 'x = 0';
ELSE
SELECT 'x > 0';
END CASE;
END$$
DELIMITER ;调用该存储过程的输出结果如下：
CALL TestProc();
x > 0|
-----|
x > 0|循环控制语句
MySQL 支持三种循环控制语句：LOOP、REPEAT 以及 WHILE 语句。
LOOP 语句
LOOP语句用于实现简单的循环结构，语法如下：
[begin_label:] LOOP
statement_list
END LOOP [end_label]LOOP 语句重复执行 statement_list 直到循环被终止，通常使用LEAVE label语句退出循环，或者使用函数的 RETURN 语句退出整个函数。begin_label 和 end_label 是该循环语句的标签。例如：
DELIMITER $$
CREATE PROCEDURE TestLoop()
BEGIN
DECLARE x,sumx integer DEFAULT 0;
label1: LOOP
SET x = x + 1;
IF x > 10 THEN
LEAVE label1;
END IF;
IF x mod 2 THEN
ITERATE label1;
ELSE
SET sumx = sumx + x;
END IF;
END LOOP label1;
SELECT sumx;
END$$
DELIMITER ;其中，LEAVE 表示 x 大于 10 时退出循环 label1；ITERATE 类似于 C++ 或者 Java 中的 CONTINUE，表示 x 为奇数则进入下一次循环，否则将其增加到变量 sumx 。存储过程的运行结果如下：
CALL TestLoop();
sumx|
----|
30|⚠️如果 LOOP 循环语句中没有指定退出条件，将会进入死循环。
REPAET 语句
REPAET语句用于重复执行指定语句列表直到某个条件为真，语法如下：
[begin_label:] REPEAT
statement_list
UNTIL search_condition
END REPEAT [end_label];REPEAT 语句首先执行 statement_list，然后判断 search_condition 是否为 true；如果是则终止循环，否则再次循环。REPAET 语句至少会执行一次。例如，上面的 LOOP 语句示例使用 REPAET 实现如下：
DELIMITER $$
CREATE PROCEDURE TestRepeat()
BEGIN
DECLARE x,sumx integer DEFAULT 0;
label1: REPEAT
SET x = x + 1;
IF x mod 2 THEN
ITERATE label1;
ELSE
SET sumx = sumx + x;
END IF;
UNTIL x = 10 END REPEAT label1;
SELECT sumx;
END$$
DELIMITER ;UNTIL 指定了 x = 10 时退出循环。
WHILE 语句
WHILE语句基于某个条件为真时重复执行指定的语句列表，语法如下：
[begin_label:] WHILE search_condition DO
statement_list
END WHILE [end_label];首先，判断 search_condition 是否为 true；如果是则执行 statement_list 语句列表，否则退出循环语句。WHILE 语句可能一次也不执行。例如，上面的 LOOP 语句示例使用 WHILE 实现如下：
DELIMITER $$
CREATE PROCEDURE TestWhile()
BEGIN
DECLARE x,sumx integer DEFAULT 0;
label1: WHILE x < 10 DO
SET x = x + 1;
IF x mod 2 THEN
ITERATE label1;
ELSE
SET sumx = sumx + x;
END IF;
END WHILE label1;
SELECT sumx;
END$$
DELIMITER ;错误处理
当存储过程或者函数在执行过程中出现某种错误条件（例如警告或者异常）时，需要进行特殊处理，例如退出当前程序模块或者继续执行，同时返回一个有用的错误信息。
定义错误条件
MySQL 提供了定义错误条件的DECLARE ... CONDITION语句：
DECLARE condition_name CONDITION
FOR { mysql_error_code | SQLSTATE [VALUE] sqlstate_value }其中，condition_name 是错误条件的名称，可以用于随后的错误处理器声明；mysql_error_code 表示 MySQL 错误码，例如 1062 代表了唯一键重复错误；SQLSTATE 表示使用 5 位字符串代表的 SQLSTATE 值，例如“42000” 和错误码 1062 一样代表了唯一键重复错误。
定义错误处理器
定义了错误条件之后，我们可以使用DECLARE ... HANDLER语句定义处理一个或多个错误条件的错误处理器：
DECLARE { CONTINUE | EXIT } HANDLER
FOR condition_value [, condition_value] ...
statement;
condition_value: {
mysql_error_code
| SQLSTATE [VALUE] sqlstate_value
| condition_name
| SQLWARNING
| NOT FOUND
| SQLEXCEPTION
}该语句定义了一个错误处理器；当某个错误条件 condition_value 发生时，执行指定的 statement 语句，可以是单个语句或者 BEGIN/END 代码块；然后执行相应的操作，CONTINUE 表示继续执行当前程序，EXIT 表示终止执行错误处理器所在的 BEGIN/END 模块。
指定错误条件时，mysql_error_code 表示 MySQL 错误码，SQLSTATE 表示使用 5 位字符串代表的 SQLSTATE 值，condition_name 是使用 DECLARE ... CONDITION 语句定义的错误条件名称，SQLWARNING 代表了以“01”开头的一类 SQLSTATE 值，NOT FOUND 代表了以“02”开头的一类 SQLSTATE 值，SQLEXCEPTION 代表了以“00”、“01”或者“02”开头的一类 SQLSTATE 值。
例如：
DELIMITER $$
CREATE PROCEDURE TestError()
BEGIN
DECLARE empid int DEFAULT 1;
DECLARE duplicate_employee CONDITION FOR 1062;
DECLARE EXIT HANDLER
FOR duplicate_employee
BEGIN
ROLLBACK;
SELECT CONCAT('重复的员工信息！', 'emp_id:', empid) Message;
END;
INSERT INTO employee
VALUES (empid, '刘备', '男', 1, NULL, DATE('2000-01-01'), 1, 30000, 10000, 'liubei@shuguo.com');
END$$
DELIMITER ;首先，我们定义了一个错误条件 duplicate_employee，表示员工信息重复错误；然后定义了一个错误处理器，发生该错误时回滚数据修改并且输出一个消息，使用命名的错误条件可以提高代码的可读性。执行该存储过程的结果如下：
CALL TestError();
Message |
---------------------|
重复的员工信息！emp_id:1|如果将错误处理器执行的语句稍加修改，就可以实现之前讲过的合并数据操作：
DECLARE EXIT HANDLER
FOR duplicate_employee
BEGIN
UPDATE employee
SET emp_name = '刘备'
...
WHERE emp_id = empid;
END;如果定义了多个错误处理器，指定错误码的处理器优先级最高，然后是 SQLSTATE 处理器，然后是 SQLEXCEPTION 处理器，最后是 SQLWARNING 处理器以及 NOT FOUND 处理器。多个优先级相同的错误处理器的执行顺序不确定。
获取诊断信息
MySQL 在发生错误时会生成诊断信息，应用程序可以通过GET DIAGNOSTICS语句获取这些信息。例如：
mysql> DROP TABLE no_such_table;
ERROR 1051 (42S02): Unknown table 'hrdb.no_such_table'
mysql> GET DIAGNOSTICS CONDITION 1
-> @p1 = RETURNED_SQLSTATE, @p2 = MESSAGE_TEXT;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT @p1, @p2;
+-------+------------------------------------+
| @p1 | @p2 |
+-------+------------------------------------+
| 42S02 | Unknown table 'hrdb.no_such_table' |
+-------+------------------------------------+
1 row in set (0.00 sec)另外，我们也可以使用 SHOW WARNINGS 或者 SHOW ERRORS 查看相关的错误信息。
GET DIAGNOSTICS 语句的完整语法如下：
GET [CURRENT | STACKED] DIAGNOSTICS {
target = NUMBER | target = ROW_COUNT,
...
}
GET [CURRENT | STACKED] DIAGNOSTICS
CONDITION condition_number {
target = condition_information_item_name,
...
}
condition_information_item_name: {
CLASS_ORIGIN
| SUBCLASS_ORIGIN
| RETURNED_SQLSTATE
| MESSAGE_TEXT
| MYSQL_ERRNO
| CONSTRAINT_CATALOG
| CONSTRAINT_SCHEMA
| CONSTRAINT_NAME
| CATALOG_NAME
| SCHEMA_NAME
| TABLE_NAME
| COLUMN_NAME
| CURSOR_NAME
}其中，CURRENT 表示返回当前诊断区域中的信息，STACKED 表示返回第二诊断区域的信息（只能用于 DECLARE HANDLER 语句之中）。默认返回当前诊断区域中的信息。
简单来说，诊断区域中存储了两类信息：
- 语句信息，包括错误条件的编号（NUMBER）和影响的行数（ROW_COUNT），对应上面的第一种语法。
- 条件信息，例如错误码（MYSQL_ERRNO）和错误信息（MESSAGE_TEXT）等，对应上面的第二种语法。如果一个语句触发了多个错误条件，每个错误条件都提供了一个条件区域，编号从 1 到 NUMBER。
以下是一个在存储过程中获取诊断信息的示例：
CREATE TABLE t(id int NOT NULL);
DELIMITER $$
CREATE PROCEDURE do_insert(value INT)
BEGIN
-- 声明保存诊断信息的变量
DECLARE cno INT;
DECLARE code CHAR(5) DEFAULT '00000';
DECLARE msg TEXT;
DECLARE nrows INT;
DECLARE result TEXT;
-- 声明异常错误处理器
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
BEGIN
GET DIAGNOSTICS cno = NUMBER;
GET DIAGNOSTICS CONDITION cno
code = RETURNED_SQLSTATE, msg = MESSAGE_TEXT;
END;
-- 执行插入语句
INSERT INTO t(id) VALUES(value);
-- 检查插入是否成功
IF code = '00000' THEN
GET DIAGNOSTICS nrows = ROW_COUNT;
SET result = CONCAT('insert succeeded, row count = ',nrows);
ELSE
SET result = CONCAT('insert failed, error = ',code,', message = ',msg);
END IF;
-- 返回错误信息
SELECT result;
END$$
DELIMITER ;其中，cno 是返回的错误条件编号，code 和 msg 是错误编码和信息，nrows 是成功插入的行数。运行以下测试语句：
CALL do_insert(1);
result |
-------------------------------|
insert succeeded, row count = 1|
CALL do_insert(NULL);
result |
------------------------------------------------------------------|
insert failed, error = 23000, message = Column 'id' cannot be null|抛出错误条件
除了捕获和处理错误之外，MySQL 还提供了抛出错误的SIGNAL和RESIGNAL语句。
SIGNAL { SQLSTATE [VALUE] sqlstate_value | condition_name }
[SET condition_information_item_name = simple_value, ...]
condition_information_item_name: {
CLASS_ORIGIN
| SUBCLASS_ORIGIN
| MESSAGE_TEXT
| MYSQL_ERRNO
| CONSTRAINT_CATALOG
| CONSTRAINT_SCHEMA
| CONSTRAINT_NAME
| CATALOG_NAME
| SCHEMA_NAME
| TABLE_NAME
| COLUMN_NAME
| CURSOR_NAME
}SIGNAL 可以抛出一个 SQLSTATE 值或者命名的错误条件，SET 子句可以设置不同的信息，它们可以在调用程序中使用 GET DIAGNOSTICS 语句捕获。例如：
DELIMITER $$
CREATE PROCEDURE p(divisor INT)
BEGIN
IF divisor = 0 THEN
SIGNAL SQLSTATE '22012'
SET MESSAGE_TEXT = 'devided by zero!';
END IF;
END;
DELIMITER ;存储过程 p 抛出了一个自定义的错误条件，指定了 SQLSTATE 值和错误消息。传入参数 0，测试抛出错误的结果：
CALL p(0);
ERROR 1644 (22012): devided by zero!另外，RESIGNAL语句也可以抛出错误，语法和 SIGNAL 相同。不过，RESIGNAL 和 SIGNAL 存在一些差异：
- RESIGNAL 语句只能用于错误处理器的处理语句中，可以修改某些错误信息后再次抛出；
- RESIGNAL 语句的属性可以忽略，意味着将接收到的错误不经修改再次抛出。
游标的使用
游标（Cursor）可以支持对查询结果的遍历，从而实现数据集的逐行处理。MySQL 存储过程和函数支持游标，它们具有以下特性：
- Asensitive 灵敏性，服务器可能直接遍历基础表的数据，也可能复制一份额外的临时数据；
- 只读性（Read only），不能通过游标修改基础表中的数据；
- 不可滚动性（Nonscrollable），只能按照查询结果的顺序访问数据，不能反向遍历，也不能跳跃访问数据行。
使用 MySQL 游标的过程如下：
- 通过DECLARE语句声明游标；
- 使用OPEN语句打开游标；
- 循环使用FETCH语句获取游标中的数据行；
- 使用CLOSE语句关闭游标并释放资源。
以下是一个使用游标的示例：
DELIMITER $$
CREATE PROCEDURE TestCurosr()
BEGIN
DECLARE done INT DEFAULT FALSE;
DECLARE id int;
DECLARE name varchar(50);
DECLARE namelist varchar(500) default '';
DECLARE cur1 CURSOR FOR
SELECT emp_id, emp_name FROM employee WHERE sex = '女';
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
OPEN cur1;
getEmp: LOOP
FETCH cur1 INTO id, name;
IF done THEN
LEAVE getEmp;
ELSE
SET namelist = concat( name, ';', namelist);
END IF;
END LOOP;
CLOSE cur1;
SELECT namelist;
END$$
DELIMITER ;该存储过程的执行结果如下：
CALL TestCurosr();
namelist |
------------------|
赵氏;孙丫鬟;孙尚香;|声明游标
MySQL 使用 DECLARE 语句声明游标：
DECLARE cursor_name CURSOR FOR select_statement;其中，cursor_name 是游标名称，select_statement 定义了与游标关联的查询语句。游标声明必须在变量和错误条件（CONDITION）声明之后，以及错误处理器（HANDLER）声明之前。在上面的示例中声明了一个游标 cur1：
DECLARE cur1 CURSOR FOR
SELECT emp_id, emp_name FROM employee WHERE sex = '女';打开游标
声明之后，使用 OPEN 语句打开游标，也就是执行查询初始化结果集：
OPEN cur1;遍历游标
接下来就是循环使用 FETCH 语句获取下一行数据，并且移动游标指针：
FETCH cur1 INTO id, name;同时，在循环中检查是否还有更多数据行；如果没有则退出循环。变量 done 在预定义的错误处理器中进行设置，没有找到数据（NOT FOUND）时设置为 true。
关闭游标
最后，使用 CLOSE 语句关闭游标：
CLOSE cur1;关闭游标可以释放相关的资源。
访问权限控制
在存储过程和函数的定义中，可以使用 SQL SECURITY 属性指定定义者权限（DEFINER ）或者调用者权限（INVOKER ）。
对于 SQL SECURITY DEFINER，存储过程使用定义者的权限执行，定义者可以通过 DEFINER 属性进行指定，默认为当前用户。
对于 SQL SECURITY INVOKER，存储过程将会使用调用者的权限执行，此时 DEFINER 属性不会对执行产生任何作用。如果调用者的权限较低，定义者的权限较高，调用者仍然能够获得存储过程内部操作所需的权限。
|
参考： |
