RDBMS关系数据库系统之​ 存储过程

本章学习目标

​ 理解存储过程

​ 熟练掌握存储过程的相关操作

在实际开发中，经常会遇到为了完成某一功能，需要编写一组SQL语句，为了提高SQL语句的重用性，MySQL提供了存储过程，本章将对MySQL存储过程详细讲解。

9.1​ 存储过程概述

9.1.1​ 什么是存储过程

存储过程是将SQL语句放入一个集合里，然后直接调用存储过程来执行已经定义好的SQL语句集合，这样做可以避免开发人员重复编写相同的SQL语句。另外，存储过程还可以减少数据在数据库和应用服务器之间的传输，可以提高数据处理的效率。

9.1.2​ 存储过程的优缺点

存储过程的优点如下所示。

​ 允许标准组件式编程，提高了SQL语句的重用性、共享性和可移植性。

​ 能够实现较快的执行速度，节省网络流量。

​ 可以作为一种安全机制来使用。

存储过程的缺点如下所示。

​ 编写存储过程比单句SQL语句复杂，需要用户具有丰富的经验。

​ 在编写存储过程时，需要创建这些数据库对象的权限。

9.2​ 存储过程的相关操作

上节内容详细阐述了存储过程的基本概念，接下来将讲解存储过程的相关操作，包含创建、修改、删除和查看存储过程。

9.2.1​ 数据准备

在讲解存储过程前，首先创建三张数据表并插入数据，用于后面的例题演示，分别为用户表users、学生表stu和学生分数表stu_score，首先来了解一下表结构，用户表users的表结构如表9.1所示。

表9.1​ users表

字段	字段类型	说明
id	int	用户编号
name	varchar(50)	用户姓名
age	int	用户年龄
email	varchar(50)	用户邮箱

表9.1中列出了users表的字段、字段类型和说明，接着创建users表，SQL语句如下所示。

mysql> CREATE TABLE users(
-> ID INT PRIMARY KEY,
-> NAME VARCHAR(50),
-> AGE INT,
-> EMAIL VARCHAR(50)
-> );

Query OK, 0 rows affected (0.21 sec)

users表创建完成后，向表中插入数据，SQL语句如下所示。

mysql> INSERT INTO users(ID,NAME,AGE,EMAIL)
-> VALUES
-> (1,'zs',22,'zs@qq.com'),
-> (2,'ls',25,'ls@qq.com'),
-> (3,'ww',28,'ww@qq.com');

Query OK, 3 rows affected (0.05 sec)

Records: 3 Duplicates: 0 Warnings: 0

以上执行结果证明数据插入完成，可以查看表中数据，SQL语句如下所示。

mysql> SELECT * FROM users;
+----+------+------+-----------+
| ID | NAME | AGE | EMAIL |
+----+------+------+-----------+
| 1 | zs | 22 | zs@qq.com |
| 2 | ls | 25 | ls@qq.com |
| 3 | ww | 28 | ww@qq.com |
+----+------+------+-----------+
3 rows in set (0.00 sec)

接着创建学生表stu，表结构如表9.2所示。

表9.2​ stu表

字段	字段类型	说明
stu_id	int	学生编号
stu_name	char(10)	学生姓名
stu_class	int	学生班级
stu_sex	char(2)	学生性别
stu_age	int	学生年龄

表9.2中列出了stu表的字段、字段类型和说明，接着创建stu表，SQL语句如下所示。

mysql> CREATE TABLE stu(
-> STU_ID INT NOT NULL,
-> STU_NAME CHAR(10) NOT NULL,
-> STU_CLASS INT NOT NULL,
-> STU_SEX CHAR(2) NOT NULL,
-> STU_AGE INT NOT NULL,
-> PRIMARY KEY (STU_ID)
-> );

Query OK, 0 rows affected (0.09 sec)

stu表创建完成后，向表中插入数据，SQL语句如下所示。

mysql> INSERT INTO stu VALUES
-> (1,'aa',3,'女',23),
-> (2,'bb',1,'男',12),
-> (3,'cc',30,'女',11),
-> (4,'dd',2,'男',22),
-> (5,'ee',1,'女',23),
-> (6,'ff',2,'女',13),
-> (7,'gg',3,'男',10),
-> (8,'hh',2,'女',11),
-> (9,'ii',1,'男',13),
-> (10,'jj',3,'女',27);
Query OK, 10 rows affected (0.06 sec)

Records: 10 Duplicates: 0 Warnings: 0

最后创建学生成绩表stu_score，表结构如表9.3所示。

表9.3​ stu_score表

字段	字段类型	说明
stu_id	int	学生编号
stu_score	int	学生分数

表9.3中列出了stu_score表的字段、字段类型和说明，接着创建stu_score表，SQL语句如下所示。

mysql> CREATE TABLE stu_score(
-> stu_id INT NOT NULL,
-> stu_score INT NOT NULL,
-> FOREIGN KEY(stu_id) REFERENCES stu(stu_id)
-> );

Query OK, 0 rows affected (0.16 sec)

stu_score表创建完成后，向表中插入数据，SQL语句如下所示。

mysql> INSERT INTO stu_score VALUES
-> (1,91),(2,62),(3,18),
-> (4,95),(5,71),(6,82),
-> (7,60),(8,52),(9,99),
-> (10,46);
Query OK, 10 rows affected (0.06 sec)

Records: 10 Duplicates: 0 Warnings: 0

至此，三张数据表创建完成，本章后面的演示例题会用到这三张表。

9.2.2​ 创建存储过程

在创建存储过程时，当前用户必须具有创建存储过程的权限。此时登录的是root用户，查询该用户是否具有创建存储过程的权限，SQL语句如下所示。

mysql> select Create_routine_priv from mysql.user WHERE User='root';
+---------------------+
| Create_routine_priv |
+---------------------+
| Y |
| Y |
+---------------------+

2 rows in set (0.00 sec)

以上执行结果可看出，当前用户具有创建存储过程的权限，可以进行创建存储过程的操作，MySQL中创建存储过程的语法格式如下所示。

CREATE PROCEDURE sp_name([proc_parameter[...]])

[characteristic ...] routine_body

以上语法格式中，创建存储过程的语句由多条子句构成，为了帮助大家更好地理解，接下来对语法格式中的每个部分进行详细解析，具体如下所示。

​ CREATE PROCEDURE：表示创建存储过程的关键字。

​ sp_name：表示存储过程的名称。

​ proc_parameter：表示存储过程的参数列表。

​ characteristic：用于指定存储过程的特性。

​ routine_body：表示存储过程的主体部分，包含了在过程调用的时候必须执行的sql语句。以BEGIN开始，以END结束。如果存储过程体中只有一条sql语句,可以省略BEGIN-END标志。

以上是创建存储过程的语法格式，proc_parameter为指定存储过程的参数列表，该参数列表的形式如下所示。

[IN|OUT|INOUT] param_name type

以上参数列表中，IN表示输入参数，OUT表示输出参数，INOUT表示既可以输入也可以输出；param_name表示参数名称；type表示参数的类型，可以是MySQL中的任意类型。

另外，创建存储过程的格式中，characteristic有五种可选值，具体如下所示。

​ COMMENT 'string'：用于对存储过程的描述，其中string为描述内容，comment为关键字。

​ LANGUAGE SQL：用于指明编写存储过程的语言为SQL语言。

​ DETERMINISTIC：表示存储过程对同样的输入参数产生相同的结果;NOT DETERMINISTIC则表示会产生不确定的结果（默认）。

​ contains sql | no sql | reads sql data | modifies sql data：表示存储过程包含读或写数据的语句，no sql表示不包含sql语句，reads sql data表示存储过程只包含读数据的语句，modifies sql data 表示存储过程只包含写数据的语句。

​ sql security definer | invoker：指定有权限执行存储过程的用户，其中definer代表定义者，invoker代表调用者，默认是definer。

接下来通过具体案例演示如何创建存储过程。

例9-1​ 创建一个带IN的存储过程，用于通过传入用户名，查询表users中的用户信息，SQL语句如下所示。

mysql> delimiter //

mysql> CREATE PROCEDURE SP_SEARCH(IN p_name CHAR(20))
-> BEGIN
-> IF p_name is null or p_name='' THEN
-> SELECT * FROM users;
-> ELSE
-> SELECT * FROM users WHERE name LIKE p_name;
-> END IF;
-> END//

Query OK, 0 rows affected (0.06 sec)

以上SQL语句中，“delimiter //”语句的作用是将MySQL的结束符设置为“//”，因为MySQL默认的语句结束符号为分号，与存储过程中的语句结束符冲突，所以需要“delimiter //”语句改变默认的结束符，最后以“END//”语句结束存储过程。存储过程创建完成后，可以使用“delimiter”语句恢复默认结束符，具体如下所示。

mysql> delimiter ;

以上SQL恢复了默认结束符，需要注意的是，“delimiter”与设定的结束符之间一定要有一个空格，否则设定无效。存储过程创建完成后，可以使用CALL关键字调用存储过程，SQL语句如下所示。

mysql> CALL SP_SEARCH('zs');

+----+------+------+-----------+

| ID | NAME | AGE | EMAIL |

+----+------+------+-----------+

| 1 | zs | 22 | zs@qq.com |

+----+------+------+-----------+

1 row in set (0.00 sec)

Query OK, 0 rows affected (0.02 sec)

从以上执行结果可看出，通过CALL关键字调用了存储过程SP_SEARCH，传入了参数“zs”，执行存储过程后，成功查询到了用户“zs”的信息。

例9-2​ 创建一个带OUT的存储过程，用于通过传入用户年龄，查询表users中大于该年龄的用户信息，并且输出查询到的用户个数，SQL语句如下所示。

mysql> delimiter //

mysql> CREATE PROCEDURE SP_SEARCH2(IN p_age INT,OUT p_int INT)

-> BEGIN

-> IF p_age is null or p_age='' THEN

-> SELECT * FROM users;

-> ELSE

-> SELECT * FROM users WHERE age>p_age;

-> END IF;

-> SELECT FOUND_ROWS() INTO p_int;

-> END//

Query OK, 0 rows affected (0.03 sec)

以上执行结果证明存储过程创建成功，接着调用存储过程，SQL语句如下所示。

mysql> CALL SP_SEARCH2(22,@p_num);

+----+------+------+-----------+

| ID | NAME | AGE | EMAIL |

+----+------+------+-----------+

| 2 | ls | 25 | ls@qq.com |

| 3 | ww | 28 | ww@qq.com |

+----+------+------+-----------+

2 rows in set (0.00 sec)

Query OK, 1 row affected (0.01 sec)

从以上执行结果可以看出，通过CALL关键字调用了存储过程SP_SEARCH2，传入了参数“22”，执行存储过程后，成功查询到了users表中年龄大于22的用户信息，@p_num可以查询到存储过程执行后的输出内容，即用户的个数，SQL语句如下所示。

mysql> SELECT @p_num;
+--------+
| @p_num |
+--------+
| 2 |

+--------+

1 row in set (0.00 sec)

从以上执行结果可看出，通过查询@p_num，查看到了存储过程执行后的输出内容，年龄大于22的用户有两个。

例9-3​ 创建一个带INOUT的存储过程，用于将输入的参数乘以10并输出，SQL语句如下所示。

mysql> delimiter //

mysql> CREATE PROCEDURE SP_INOUT(INOUT p_num INT)

-> BEGIN

-> SET p_num=p_num*10;

-> END//

Query OK, 0 rows affected (0.00 sec)

以上执行结果证明存储过程创建成功，接着调用存储过程，SQL语句如下所示。

mysql> delimiter ;

mysql> SET @p_num2=5;

Query OK, 0 rows affected (0.00 sec)

mysql> CALL SP_INOUT(@p_num2);

Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @p_num2;

+---------+

| @p_num2 |

+---------+

| 50 |

+---------+

1 row in set (0.00 sec)

以上SQL语句中，首先通过SET关键字定义@p_num2等于5，然后通过CALL关键字调用存储过程SP_INOUT，将参数@p_num2传入，最后通过SELECT关键字查询@p_num2的值，输出结果为50，存储过程的功能成功实现。

9.2.3​ 查看存储过程

查看存储过程有三种方式，接下来针对三种方式分别讲解。

1.使用SHOW STATUS语句查看存储过程

查询存储过程的状态，可以使用SHOW STATUS语句，具体语法格式如下。

SHOW {PROCEDURE | FUNCTION} STATUS [LIKE ‘pattern’]

接下来通过具体案例演示查看存储过程的状态。

例9-4​ 使用SHOW STATUS语句查看所有名称以“S”开头存储过程的状态，SQL语句如下所示。

mysql> SHOW PROCEDURE STATUS LIKE 'S%'\G

*************************** 1. row ***************************

Db: qianfeng6

Name: SP_INOUT

Type: PROCEDURE

Definer: root@localhost

Modified: 2017-11-30 15:34:12

Created: 2017-11-30 15:34:12

Security_type: DEFINER

Comment:

character_set_client: gbk

collation_connection: gbk_chinese_ci

Database Collation: utf8_general_ci

*************************** 2. row ***************************

Db: qianfeng6

Name: SP_SEARCH

Type: PROCEDURE

Definer: root@localhost

Modified: 2017-11-30 14:39:42

Created: 2017-11-30 14:39:42

Security_type: DEFINER

Comment:

character_set_client: gbk

collation_connection: gbk_chinese_ci

Database Collation: utf8_general_ci

*************************** 3. row ***************************

Db: qianfeng6

Name: SP_SEARCH2

Type: PROCEDURE

Definer: root@localhost

Modified: 2017-11-30 15:16:54

Created: 2017-11-30 15:16:54

Security_type: DEFINER

Comment:

character_set_client: gbk

collation_connection: gbk_chinese_ci

Database Collation: utf8_general_ci

3 rows in set (0.01 sec)

以上执行结果可看到数据库中一共有三个名称以“S”开头的存储过程，通过SHOW STATUS语句可以查看到三个存储过程的Db、Name、Type等。

2.使用SHOW CREATE语句查看存储过程

查询存储过程的创建信息，可以使用SHOW CREATE语句，具体语法格式如下。

SHOW CREATE{PROCEDURE | FUNCTION} sp_name

接下来通过具体案例演示查看存储过程的创建信息。

例9-5​ 使用SHOW CREATE语句查看存储过程SP_SEARCH2的创建信息，SQL语句如下所示。

mysql> SHOW CREATE PROCEDURE SP_SEARCH2\G

*************************** 1. row ***************************

Procedure: SP_SEARCH2

sql_mode:
Create Procedure: CREATE DEFINER=`root`@`localhost` PROCEDURE

`SP_SEARCH2`(IN p_age INT,OUT p_int INT)

BEGIN

          IF p_age is null or p_age='' THEN

         SELECT * FROM users;

ELSE

      SELECT * FROM users WHERE age>p_age;

     END IF;

SELECT FOUND_ROWS() INTO p_int;

END

character_set_client: gbk

collation_connection: gbk_chinese_ci

Database Collation: utf8_general_ci

1 row in set (0.00 sec)

以上执行结果可看到，通过SHOW CREATE语句可以查看存储过程SP_SEARCH2的Procedure、Create Procedure等。

3.从information_schema.Routines表中查看存储过程

在MySQL中，存储过程的信息存储在information_schema库下的Routines表中，可以通过查询该表的数据来查询存储过程的信息，接下来通过具体案例演示查看Routines表中存储过程的信息。

例9-6​ 通过查询information_schema.Routines表，查看存储过程SP_SEARCH的信息，SQL语句如下所示。

mysql> SELECT * FROM information_schema.ROUTINES

-> WHERE ROUTINE_NAME='SP_SEARCH' AND ROUTINE_TYPE='PROCEDURE'\G

*************************** 1. row ***************************

SPECIFIC_NAME: SP_SEARCH

ROUTINE_CATALOG: def

ROUTINE_SCHEMA: qianfeng6

ROUTINE_NAME: SP_SEARCH

ROUTINE_TYPE: PROCEDURE

DATA_TYPE:

CHARACTER_MAXIMUM_LENGTH: NULL

CHARACTER_OCTET_LENGTH: NULL

NUMERIC_PRECISION: NULL

NUMERIC_SCALE: NULL

CHARACTER_SET_NAME: NULL

COLLATION_NAME: NULL

DTD_IDENTIFIER: NULL

ROUTINE_BODY: SQL

ROUTINE_DEFINITION: BEGIN

IF p_name is null or p_name='' THEN

SELECT * FROM users;

ELSE

SELECT * FROM users WHERE name LIKE p_name;

END IF;

END

EXTERNAL_NAME: NULL

EXTERNAL_LANGUAGE: NULL

PARAMETER_STYLE: SQL

IS_DETERMINISTIC: NO

SQL_DATA_ACCESS: CONTAINS SQL

SQL_PATH: NULL

SECURITY_TYPE: DEFINER

CREATED: 2017-11-30 14:39:42

LAST_ALTERED: 2017-11-30 14:39:42

SQL_MODE:

ROUTINE_COMMENT:

DEFINER: root@localhost

CHARACTER_SET_CLIENT: gbk

COLLATION_CONNECTION: gbk_chinese_ci

DATABASE_COLLATION: utf8_general_ci

1 row in set (0.01 sec)

以上执行结果可看到，通过查询information_schema.Routines表，不仅可以查看到存储过程SP_SEARCHE的基本信息，还可以查看存储过程SP_SEARCHE的创建语句。

9.2.4​ 修改存储过程

在修改存储过程时，当前用户必须具有修改存储过程的权限。此时登录的是root用户，查询该用户是否具有修改存储过程的权限，SQL语句如下所示。

mysql> select Alter_routine_priv from mysql.user WHERE User='root';

+--------------------+

| Alter_routine_priv |

+--------------------+

| Y |

| Y |

+--------------------+

2 rows in set (0.00 sec)

以上执行结果可看出，当前用户具有修改存储过程的权限，可以进行修改存储过程的操作，MySQL中修改存储过程的语法格式如下所示。

ALTER {PROCEDURE | FUNCTION} sp_name [characteristic ...]

以上语法格式中，sp_name表示存储过程的名称，characteristic表示修改存储过程的具体部分，有六个可选值，具体如下所示。

​ CONTAINS SQL：表示子程序包含SQL语句，但不包含读或写数据的语句。

​ NO SQL：表示子程序中不包含SQL语句。

​ READS SQL DATA：表示程序中包含读数据的语句。

​ MODIFIES SQL DATA：表示子程序中包含写数据的语句。

​ SQL SECURITY{DEFINER | INVOKER}：指明有权限执行的用户，其中DEFINER表示只有定义者才能执行，INVOKER表示只有调用者才可以执行。

​ COMMENT ‘string’：表示注释信息。

接下来通过具体案例演示如何修改存储过程。

例9-7​ 修改存储过程SP_SEARCH的定义，将读写权限修改为MODIFIES SQL DATA，并指明只有调用者可执行，SQL语句如下所示。

mysql> ALTER PROCEDURE SP_SEARCH

-> MODIFIES SQL DATA

-> SQL SECURITY INVOKER;

Query OK, 0 rows affected (0.01 sec)

以上执行结果证明存储过程修改完成，可以通过查询information_schema.Routines表，查看存储过程SP_SEARCH是否修改，SQL语句如下所示。

mysql> SELECT SPECIFIC_NAME,SQL_DATA_ACCESS,SECURITY_TYPE
-> FROM information_schema.Routines
-> WHERE ROUTINE_NAME='SP_SEARCH' AND ROUTINE_TYPE='PROCEDURE';
+---------------+-------------------+---------------+
| SPECIFIC_NAME | SQL_DATA_ACCESS | SECURITY_TYPE |
+---------------+-------------------+---------------+
| SP_SEARCH | MODIFIES SQL DATA | INVOKER |
+---------------+-------------------+---------------+
1 row in set (0.01 sec)

从以上执行结果可看出，存储过程SP_SEARCH的定义成功修改，目前MySQL还不支持对已经存在的存储过程代码进行修改，如果要修改存储过程的代码，只能重新创建一个存储过程。

9.2.5​ 删除存储过程

在删除存储过程时，当前用户必须具有修改存储过程的权限。此时登录的是root用户，具体修改存储过程的权限，这里就不再验证，删除存储过程的语法格式如下所示。

DROP {PROCEDURE | FUNCTION} [IF EXISTS] sp_name

以上语法格式中，sp_name是要删除的存储过程名称，IF EXISTS是可选的，表示如果存储过程不存在，不发生错误，而是产生一个警告，接下来通过具体案例演示删除存储过程。

例9-8​ 将存储过程SP_SEARCH删除，SQL语句如下所示。

mysql> DROP PROCEDURE SP_SEARCH;

Query OK, 0 rows affected (0.02 sec)

以上执行结果证明存储过程删除成功。

9.2.6​ 变量的使用

存储过程中可以使用变量，在MySQL5.1以后，变量是不区分大小写的，变量可以在子程序中声明并使用，这些变量的作用范围是在BEGIN-END程序中。在存储过程中使用变量，首先要定义变量，MySQL提供了DECLARE语句定义变量，具体语法格式如下所示。

DECLARE var_name[,varname]…date_typey[DEFAULT value];

以上语法格式中，var_name为局部变量的名称，可以定义多个变量，用逗号分隔开，DEFAULT value子句可以为变量提供默认值，如果没有该语句，变量的初始值为NULL，接下来定义一个名称为tmp的变量，类型为varchar(10)，默认值为abc，具体代码如下所示。

DECLARE tmp varchar(10) DEFAULT ‘abc’;

以上代码中定义了变量tmp，MySQL提供了SET语句为变量赋值，具体语法格式如下所示。

SET var_name=expr[,var_name=expr]…;

如上语法格式中，var_name代表变量名，expr代表为变量赋的值，可以为多个变量赋值，用逗号分隔开即可，接下来声明变量tmp，然后使用SET语句为变量赋值为10，具体代码如下所示。

DECLARE tmp INT;

SET tmp=10;

以上代码中，使用DECLARE语句声明INT类型变量tmp，然后使用SET语句将变量tmp赋值为10，以上为变量声明和赋值的实现。需要注意的是，局部变量只能在存储过程体的BEGIN-END语句块中声明。

9.2.7​ 定义条件和处理程序

在开发中，可能会对特定的条件进行处理，定义条件是事先定义程序执行过程中遇到的问题，处理程序是定义了在遇到问题时，应采取什么样的处理方式，接下来对定义条件和处理进行详细讲解。

1.定义条件

MySQL中使用DECLARE语句可以定义条件，具体语法格式如下所示。

DECLARE condition_name CONDITION FOR[condition_type];

[condition_type]:

SQLSTATE[VALUE] sqlstate_value | mysql_error_code

以上语法格式中，condition_name代表定义的条件名称，condition_type代表条件的类型，sqlstate_value和mysql_error_code都可以表示MySQL的错误，其中sqlstate_value是长度为5的字符串类型错误代码，mysql_error_code是数值类型的错误代码，接下来通过具体案例演示如何定义条件。

例9-9​ 定义“ERROR1148(42000)”错误，名称为command_not_allowed，具体代码如下所示。

DECLARE command_not_allowed CONDITION FOR SQLSTATE ‘42000’;

或者

DECLARE command_not_allowed CONDITION FOR 1148;

以上代码两种写法都可以实现定义“ERROR1148(42000)”错误，因为“ERROR1148(42000)”中，sqlstate_value的值是长度为5的字符串’42000’，mysql_error_code的值是数值类型1148。

2.定义处理程序

定义了条件后，还需要定义对应的处理程序，MySQL中使用DECLARE语句定义处理程序，具体语法格式如下所示。

DECLARE handler_type HANDLER FOR condition_value[,…] sp_statement

handler_type:

CONTINUE | EXIT | UNDO

condition_value:

|condition_name

|SQLWARNING

|NOT FOUND

|SQLEXCEPTION

|mysql_error_code

以上语法格式中，handler_type为错误处理方式，参数取值有三个，其中CONTINUE表示遇到错误不处理，继续执行；EXIT表示遇到错误马上退出；UNDO表示遇到错误后，撤销之前的操作，MySQL中暂时不支持这样的操作。Sp_statement为程序语句段，表示在遇到定义的错误时，需要执行的存储过程。condition_value表示错误类型，有六个可选值，具体如下所示。

​ SQLSTATE[VALUE] sqlstate_value：包含5个字符的字符串错误值。

​ condition_name：表示DECLARE CONDITION定义的错误条件名称。

​ SQLWARNING：匹配所有以01开头的SQLSTATE错误代码。

​ NOT FOUND：匹配所有以02开头的SQLSTATE错误代码。

​ SQLEXCEPTION：匹配所有没有被SQLWARNING或NOT FOUND捕获的SQLSTATE错误代码。

​ mysql_error_code：匹配数值类型错误代码。

另外，定义处理程序有六种方式，具体如下所示。

// 方法一，捕获sqlstate_value

DECLARE CONTINUE HANDLER FOR SQLSTATE ‘42S02’

SET @info=’NO_SUCH_TABLE’;

// 方法二，捕获mysql_error_code

DECLARE CONTINUE HANDLER FOR 1146 SET @info=’NO_SUCH_TABLE’;

// 方法三，先定义条件，然后调用

DECLARE no_such_table CONDITION FOR 1146;

DECLARE CONTINUE HANDLER FOR NO_SUCH_TABLE SET @info=’ERROR’;

// 方法四，使用SQLWARNING

DECLARE EXIT HANDLER FOR SQLWARNING SET @info=’ERROR’;

// 方法五，使用NOT FOUND

DECLARE EXIT HANDLER FOR NOT FOUND SET @info=’NO_SUCH_TABLE’;

// 方法六，使用SQLEXCEPTION

DECLARE EXIT HANDLER FOR SQLEXCEPTION SET @info=’ERROR’;

以上为六种处理程序的方式，接下来分别讲解这些处理方式，具体如下所示。

​ 第一种方法是捕获sqlstate_value值，如果遇到sqlstate_value值为’42S02’，则执行CONTINUE操作，并且输出”NO_SUCH_TABLE”信息。

​ 第二种方法是捕获mysql_error_code值，如果遇到mysql_error_code值为1146，则执行CONTINUE操作，并且输出”NO_SUCH_TABLE”信息。

​ 第三种方法是先定义条件，然后再调用条件，这里先定义no_such_table条件，遇到1146错误，就执行CONTINUE操作。

​ 第四种方法是使用SQLWARNING，SQLWARNING捕获所有以01开头的sqlstate_value值，然后执行EXIT操作，并且输出ERROR信息。

​ 第五种方法是使用NOT FOUND，NOT FOUND捕获所有以02开头的sqlstate_value值，然后执行EXIT操作，并且输出”NO_SUCH_TABLE”信息。

​ 第六种方法是使用SQLEXCEPTION，SQLEXCEPTION捕获所有没有被SQLWARNING或NOT FOUND捕获的sqlstate_value值，然后执行EXIT操作，并且输出”ERROR”信息。

9.2.8​ 光标的使用

查询语句可能返回多条记录，在存储过程中可以使用光标，用于逐条读取查询结果集中的记录。光标的使用包括声明光标、打开光标、使用光标和关闭光标。接下来详细讲解光标的相关内容。

1.声明光标

在使用光标前，需要先声明光标，并且必须声明在处理程序之前，变量和条件之后，MySQL中使用DECLARE关键字声明光标，具体语法格式如下所示。

DECLARE cursor_name cursor FOR select_statement;

以上语法格式中，cursor_name表示光标的名称，select_statement表示SELECT语句的内容，返回一个用于创建光标的结果集。接下来声明一个名为cur_employee的光标，具体代码如下所示。

Declare cur_employee cursor FOR select name,age from employee;

通过以上代码，成功声明了一个名为cur_employee的光标。

2.打开光标

声明光标后，若想使用光标，需要先打开光标，具体语法格式如下所示。

Open cursor_name

以上语法格式中，cursor_name表示光标的名称，Open是打开光标的关键字，接下来将光标cur_employee打开，具体代码如下所示。

Open cur_employee;

通过以上代码，成功打开了名为cur_employee的光标。

3.使用光标

在MySQL中，使用Fetch关键字来使用光标，具体语法格式如下所示。

Fetch cur_name INTO var_name[,var_name…];

以上语法格式中，cursor_name表示光标的名称，var_name表示将光标中的select语句查询出来的信息存入该参数，var_name必须在声明光标前定义。接下来使用名称为cur_employee的光标，将查询出来的信息存入emp_name和emp_age，具体代码如下所示。

Fetch cur_employee INTO emp_name,emp_age;

通过以上代码，成功使用了名为cur_employee的光标，并且将查询出来的信息存入emp_name和emp_age。

4.关闭光标

使用完光标后，需要关闭光标，具体语法格式如下所示。

Close cursor_name;

以上语法格式中，cursor_name表示光标的名称，Close是关闭光标的关键字，接下来将光标cur_employee关闭，具体代码如下所示。

Close cur_employee;

通过以上代码，成功关闭了名为cur_employee的光标。

9.2.9​ 流程控制

在编写存储过程时，还可以自定义流程控制，实现多个SQL语句划分或组合成符合业务逻辑的代码块，MySQL中流程控制语句包含IF语句、CASE语句、WHILE语句等，接下来详细讲解存储过程中的流程控制语句。

1.IF语句

IF语句是指若满足某种条件，就根据判断的结果为TRUE或FALSE执行相应的语句，具体语法格式如下所示。

IF search_condition THEN statement_list

[ELSEIF search_condition THEN statement_list]

[ELSE statement_list]

END IF

以上语法格式中，search_condition表示条件判断语句，statement_list表示不同条件的执行语句，接下来通过具体案例演示IF语句的用法。

例9-10​ 使用IF语句编写存储过程，通过传入的参数等级，可以返回各个分数等级的学生编号和学生分数，SQL语句如下所示。

mysql> delimiter //

mysql> CREATE PROCEDURE SP_SCHOLARSHIP_LEVEL(IN p_level char(1))

-> BEGIN

-> IF p_level ='A' THEN

-> SELECT * FROM stu_score WHERE STU_SCORE >=90;

-> ELSEIF p_level ='B' THEN

-> SELECT * FROM stu_score WHERE STU_SCORE <90 AND STU_SCORE>=80;

-> ELSEIF p_level ='C' THEN

-> SELECT * FROM stu_score WHERE STU_SCORE <80 AND STU_SCORE>=70;

-> ELSEIF p_level ='D' THEN

-> SELECT * FROM stu_score WHERE STU_SCORE <60;

-> ELSE

-> SELECT * FROM stu_score;

-> END IF;

-> END//

Query OK, 0 rows affected (0.00 sec)

以上执行结果证明存储过程创建完成，接下来调用存储过程，返回等级A的学生编号和学生分数，SQL语句如下所示。

mysql> delimiter ;

mysql> CALL SP_SCHOLARSHIP_LEVEL('A');

+--------+-----------+

| stu_id | stu_score |

+--------+-----------+

| 1 | 91 |

| 4 | 95 |

| 9 | 99 |

+--------+-----------+

3 rows in set (0.02 sec)

Query OK, 0 rows affected (0.03 sec)

以上执行执行结果可以看出，通过调用存储过程SP_SCHOLARSHIP_LEVEL，传入参数等级，成功查询到分数等级A的学生编号和学生分数。

2.CASE语句

CASE语句是另一种进行条件判断的语句，具体语法格式如下所示。

Case case_value

         When when_value then statement_list

         [When when_value then statement_list]…

        [else statement_list]

End case

以上语法格式中，case_value表示条件判断的表达式，决定了哪个WHEN子句会被执行，when_value表示表达式可能的值，如果某个when_value表达式与case_value表达式结果相同，则执行对应THEN关键字后的statement_list中的语句，statement_list表示不同when_value值的执行语句，接下来通过具体案例演示CASE语句的用法。

例9-11​ 使用CASE语句编写存储过程，通过传入的参数等级，可以返回各个分数等级的学生信息，SQL语句如下所示。

mysql> delimiter //

mysql> CREATE PROCEDURE SP_SCHOLARSHIP_LEVEL3(IN p_level char(1))

-> BEGIN

-> DECLARE p_num int DEFAULT 0;

-> CASE p_level

-> WHEN 'A' THEN

-> SET p_num=90;

-> WHEN 'B' THEN

-> SET p_num=80;

-> WHEN 'C' THEN

-> SET p_num=70;

-> WHEN 'D' THEN

-> SET p_num=60;

-> ELSE

-> SET p_num=0;

-> END CASE;

-> SELECT * FROM stu_score sc,stu s

-> WHERE sc.STU_ID=s.STU_ID AND sc.STU_SCORE >= p_num;

-> END//

Query OK, 0 rows affected (0.01 sec)

以上执行结果证明存储过程创建完成，接下来调用存储过程，返回等级D的学生信息，SQL语句如下所示。

mysql> delimiter ;

mysql> CALL SP_SCHOLARSHIP_LEVEL3('D');

+--------+-----------+--------+----------+-----------+---------+---------+

| stu_id | stu_score | STU_ID | STU_NAME | STU_CLASS | STU_SEX | STU_AGE |

+--------+-----------+--------+----------+-----------+---------+---------+

| 1 | 91 | 1 | aa | 3 | 女 | 23 |

| 2 | 62 | 2 | bb | 1 | 男 | 12 |

| 4 | 95 | 4 | dd | 2 | 男 | 22 |

| 5 | 71 | 5 | ee | 1 | 女 | 23 |

| 6 | 82 | 6 | ff | 2 | 女 | 13 |

| 7 | 60 | 7 | gg | 3 | 男 | 10 |

| 9 | 99 | 9 | ii | 1 | 男 | 13 |

+--------+-----------+--------+----------+-----------+---------+---------+

7 rows in set (0.08 sec)

Query OK, 0 rows affected (0.11 sec)

以上执行结果可以看出，通过调用存储过程SP_SCHOLARSHIP_LEVEL3，传入参数等级，成功查询到分数等级D的学生信息。

3.WHILE语句

WHILE语句可以创建一个带条件判断的循环过程，在语句执行时，先对指定的表达式进行判断，如果为真，则执行循环内的语句，否则退出循环，具体语法格式如下所示。

[begin_label:]

while search_condition do

      Statement_list

End while

[end_label]

以上语法格式中，search_condition为进行判断的表达式，如果表达式结果为真，WHILE语句内的语句或语句块被执行，直到search_condition为假，退出循环，接下来通过具体案例演示WHILE语句的用法。

例9-12​ 使用WHILE语句编写存储过程，通过传入的参数，当参数大于result时，循环计算参数乘以result并累加值，SQL语句如下所示。

mysql> delimiter //

mysql> CREATE PROCEDURE sp_cal(IN p_num INT,OUT p_result INT)

-> BEGIN

-> SET p_result=1;

-> WHILE p_num > 1 DO

-> SET p_result = p_num * p_result;

-> SET p_num = p_num-1;

-> END WHILE;

-> END//

Query OK, 0 rows affected (0.03 sec)

以上执行结果证明存储过程创建完成，接下来调用存储过程，传入参数5，计算最后循环相乘累加的值，SQL语句如下所示。

mysql> delimiter ;

mysql> CALL sp_cal(5,@result);

Query OK, 0 rows affected (0.01 sec)

mysql> SELECT @result;

+---------+

| @result |

+---------+

| 120 |

+---------+

1 row in set (0.00 sec)

以上执行执行结果可以看出，通过调用存储过程sp_cal，传入参数5，成功计算出循环结束后的值为120。

9.2.10​ 事件调度器

事件调度器是MySQL5.1后新增的功能，可以将数据库按自定义的时间周期触发某种操作，可以理解为时间触发器。MySQL中事件调度器是默认关闭的，可以先查看是否已开启事件调度器，具体语法格式如下所示。

SELECT @@event_scheduler;

通过以上语法，查看当前MySQL中是否开启了事件调度器，SQL语句如下所示。

mysql> SELECT @@event_scheduler;

+-------------------+

| @@event_scheduler |

+-------------------+

| OFF |

+-------------------+

1 row in set (0.00 sec)

从以上执行结果可看出，当前MySQL中没有开启事件调度器，开启事件调度器的语法格式如下所示。

SET GLOBAL event_scheduler=ON;

通过以上语法开启事件调度器，SQL语句如下所示。

mysql> SET GLOBAL event_scheduler=ON;

Query OK, 0 rows affected (0.04 sec)

从以上执行结果可看出，事件调度器成功开启，再次查看当前MySQL中事件调度器是否开启，SQL语句如下所示。

mysql> SELECT @@event_scheduler;

+-------------------+
| @@event_scheduler |
+-------------------+
| ON |
+-------------------+

1 row in set (0.00 sec)

以上执行结果证明，当前MySQL中事件调度器已经开启，创建事件调度器的语法格式如下所示。

CREATE EVENT [IF NOT EXISTS] event_name

ON SCHEDULE schedule

[ON COMPLETION [NOT] PRESERVE]

[ENABLE | DISABLE]

[COMMENT 'comment']

DO sql_statement;

schedule:

AT TIMESTAMP [+ INTERVAL INTERVAL]

| EVERY INTERVAL [STARTS TIMESTAMP] [ENDS TIMESTAMP]

INTERVAL:

quantity {YEAR | QUARTER | MONTH | DAY | HOUR | MINUTE |

WEEK | SECOND | YEAR_MONTH

以上语法格式中，event_name代表创建的事件名称，schedule代表是执行计划，它有两个选项，第一是在某一时刻执行，第二是从某时到某时每隔一段时间执行，INTERVAL代表时间间隔，可以精确到秒，接下来通过具体案例演示事件调度器的使用，首先创建一张测试表test_event，SQL语句如下所示。

mysql> CREATE TABLE test_event(

-> id INT,

-> create_time DATETIME

-> );

Query OK, 0 rows affected (0.18 sec)

以上执行结果证明表test_event创建完成，接着创建事件调度器test_event_1，实现每隔5秒向test_event表插入一条记录，SQL语句如下所示。

mysql> CREATE EVENT test_event_1

-> ON SCHEDULE

-> EVERY 5 SECOND

-> DO

-> INSERT INTO test_event(id,create_time)

-> VALUES('test_event',now());

Query OK, 0 rows affected (0.09 sec)

以上执行结果证明事件调度器创建完成，此时可以查看表test_event中的数据，SQL语句如下所示。

mysql> SELECT * FROM test_event;

+------+---------------------+

| id | create_time |

+------+---------------------+

| 0 | 2017-12-04 17:31:15 |

| 0 | 2017-12-04 17:31:20 |

| 0 | 2017-12-04 17:31:25 |

+------+---------------------+

3 rows in set (0.05 sec)

从以上执行结果可看出，表test_event中已经插入了三条数据，三条数据的插入时间间隔为5秒，对于事件调度器，还有很多选项，例如指定事件开始时间和结束时间，或者指定某个时间执行一次，而不是循环执行，更详细的使用方法可以参考官方文档，这里就不再赘述。

9.3​ 本章小结

本章首先介绍了存储过程的概念，然后详细讲解了存储过程的相关操作，包括存储过程的创建、查看、修改、删除等，需要大家通过实践去熟练掌握。

9.4​ 习题

1.思考题

(1) 请简述什么是存储过程。

(2) 请简述存储过程的优缺点。

(3) 请简述创建存储过程的语法格式。

(4) 请简述查看存储过程的三种方式。

(5) 请简述删除存储过程的语法格式。