数据库组成存储过程和函数

举报
xcc-2022 发表于 2022/07/26 10:44:39 2022/07/26
【摘要】 16.1　存储过程和函数简介存储过程和函数不仅能够简化开发人员开发应用程序的工作量，而且对于存储过程和函数中SQL语句的变动，无须修改上层应用程序的代码，这也大大简化了后期对于应用程序维护的复杂度。16.1.1　什么是存储过程和函数在MySQL数据库中，存储程序可以分为存储过程和存储函数。存储过程和存储函数都是一系列SQL语句的集合，这些SQL语句被封装到一起组成一个存储过程或者存储函数保存...

16.1　存储过程和函数简介

存储过程和函数不仅能够简化开发人员开发应用程序的工作量，而且对于存储过程和函数中SQL语句的变动，无须修改上层应用程序的代码，这也大大简化了后期对于应用程序维护的复杂度。


16.1.1　什么是存储过程和函数

在MySQL数据库中，存储程序可以分为存储过程和存储函数。存储过程和存储函数都是一系列SQL语句的集合，这些SQL语句被封装到一起组成一个存储过程或者存储函数保存到数据库中。应用程序调用存储过程只需要通过CALL关键字并指定存储过程的名称和参数即可；同样，应用程序调用存储函数只需要通过SELECT关键字并指定存储函数的名称和参数即可。

存储过程和存储函数是有一定区别的，存储函数必须有返回值，而存储过程没有。另外，存储过程的参数类型可以是IN、OUT和INOUT，而存储函数的参数类型只能是IN。


16.1.2　存储过程和函数的使用场景

在实际的企业项目开发过程中，往往不会只编写针对一个或多个表的单条SQL语句，而经常会编写一些复杂的业务逻辑，这些业务逻辑往往需要多条SQL语句的配合才能完成。

在实际工作中，可以单独编写每条SQL语句，根据SQL语句执行的先后顺序和结果条件，依次执行其他SQL语句。不过，在每个需要处理这些逻辑的地方，都需要编写这些复杂的业务逻辑来保证业务流程的正确性。

例如，在笔者主导开发的电商系统中，一个典型的场景就是用户提交订单的流程。在用户提交订单时，系统中会包含如下几种行为：

·当用户直接下单时，必须校验商品的库存信息；

·当用户从购物车下单时，必须校验商品的有效状态（是否被下架）和库存信息；

·如果商品有效并存在库存，则锁定相关商品，并减少对应商品的库存信息，以保证正确的库存量；

·如果商品无效（商品已下架），则需要通知用户该商品已经被下架，无法生成订单；

·如果商品库存不足，需要通知库存进货，并需要与供应商进行交互；

·还需要通知用户哪些商品可以直接发货，哪些商品需要取消订单。

注意：这里只列举出了笔者主导开发的电商系统下单逻辑的部分流程。

如果将用户下单时系统包含的这些行为单独编写每条SQL语句，之后根据SQL语句执行的先后顺序和结果条件，依次执行其他SQL语句，不仅增加了开发应用程序的业务逻辑复杂性，而且在每个需要处理订单逻辑的地方都需要编写这些SQL语句，SQL语句的变动也会导致应用程序中业务逻辑的变动，这无疑增加了系统后期维护与升级的复杂度。

此时，可以编写存储过程和函数，按照特定的执行顺序和结果条件，将相应的SQL语句封装成特定的业务逻辑，应用程序只需要调用编写的存储过程和函数进行相应的处理，而无须关注SQL语句实现的细节。同时，在后期应用程序的维护过程中修改了存储过程和函数内部的SQL语句，无须修改上层应用程序的业务逻辑。


16.1.3　存储过程和函数的优点

在实际项目开发过程中，使用存储过程和函数能够为项目开发和维护带来诸多好处，现就存储过程和函数的典型优点总结如下：

1．具有良好的封装性

存储过程和函数将一系列的SQL语句进行封装，经过编译后保存到MySQL数据库中，可以供应用程序反复调用，而无须关注SQL逻辑的实现细节。

2．应用程序与SQL逻辑分离

存储过程和函数中的SQL语句发生变动时，在一定程度上无须修改上层应用程序的业务逻辑，大大简化了应用程序开发和维护的复杂度。

3．让SQL具备处理能力

存储过程和函数支持流程控制处理，能够增强SQL语句的灵活性，而且使用流程控制能够完成复杂的逻辑判断和相关的运算处理。

4．减少网络交互

单独编写SQL语句在应用程序中处理业务逻辑时，需要通过SQL语句反复从数据库中查询数据并进行逻辑处理。每次查询数据时，都会在应用程序和数据库之间产生数据交互，增加了不必要的网络流量。使用存储过程和函数时，将SQL逻辑封装在一起并保存到数据库中，应用程序调用存储过程和函数，在应用程序和函数之间只需要产生一次数据交互即可，大大减少了不必要的网络带宽流量。

5．能够提高系统性能

由于存储过程和函数是经过编译后保存到MySQL数据库中的，首次执行存储过程和函数后，存储过程和函数会被保存到相关的内存区域中。反复调用存储过程和函数时，只需要从对应的内存区域中执行存储过程和函数即可，大大提高了系统处理业务的效率和性能。

6．降低数据出错的概率

在实际的系统开发过程中，业务逻辑处理的步骤越多，出错的概率往往越大。存储过程和函数统一封装SQL逻辑，对外提供统一的调用入口，能够大大降低数据出错的概率。

7．保证数据的一致性和完整性

通过降低数据出错的概率，能够保证数据的一致性和完整性。

8．保证数据的安全性

在实际的系统开发过程中，需要对数据库划分严格的权限。部分人员不能直接访问数据表，但是可以为其赋予存储过程和函数的访问权限，使其通过存储过程和函数来操作数据表中的数据，从而提升数据库中数据的安全性。

接下来介绍存储过程和函数中各项技术的使用方式。

注意：在本章中，存储过程和函数的处理逻辑依托于第8章中建立的商品信息表t_goods。关于商品信息表t_goods的详细信息，这里不再赘述。

16.2　创建存储过程和函数

介绍完存储过程和函数的基础知识后，接下来介绍如何在MySQL数据库中创建存储过程和函数。


16.2.1　创建存储过程

1．创建存储过程的语法说明

创建存储过程需要使用CREATE PROCEDURE语句，语法格式如下：

CREATE PROCEDURE sp_name ([proc_parameter[,...]])
      [characteristic ...] routine_body

语法格式说明：

·CREATE PROCEDURE：创建存储过程必须使用的关键字；

·sp_name：创建存储过程时指定的存储过程名称；

·proc_parameter：创建存储过程时指定的参数列表，参数列表可以省略；

·characteristic：创建存储过程时指定的对存储过程的约束；

·routine_body：存储过程的SQL执行体，使用BEGIN…END来封装存储过程需要执行的SQL语句。

2．参数详细说明

在创建存储过程的语法中，有两个参数需要特别说明，一个参数是proc_parameter，另一个参数是characteristic。

（1）proc_parameter：表示在创建存储过程时指定的参数列表。其列表形式如下：

[ IN | OUT | INOUT ] param_name type

各项说明如下：

·IN：当前参数为输入参数，也就是表示入参；

·OUT：当前参数为输出参数，也就是表示出参；

·INOUT：当前参数即可以为输入参数，也可以为输出参数，也就是即可以表示入参，也可以表示出参；

·param_name：当前存储过程中参数的名称；

·type：当前存储过程中参数的类型，此类型可以是MySQL数据库中支持的任意数据类型。

（2）characteristic：表示创建存储过程时指定的对存储过程的约束条件，其取值信息如下：

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

各项说明如下：

·LANGUAGE SQL：存储过程的SQL执行体部分（存储过程语法格式中的routine_body部分）是由SQL语句组成的。

·[NOT] DETERMINISTIC：执行当前存储过程后，得出的结果数据是否确定。其中，DETERMINISTIC表示执行当前存储过程后得出的结果数据是确定的，即对于当前存储过程来说，每次输入相同的数据时，都会得到相同的输出结果。NOT DETERMINISTIC表示执行当前存储过程后，得出的结果数据是不确定的，即对于当前存储过程来说，每次输入相同的数据时，得出的输出结果可能不同。如果没有设置执行值，则MySQL默认为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语句。如果没有设置相关的值，则MySQL默认指定值为CONTAINS SQL。

·SQL SECURITY {DEFINER | INVOKER}：执行当前存储过程的权限，即指明哪些用户能够执行当前存储过程。DEFINER表示只有当前存储过程的创建者或者定义者才能执行当前存储过程；INVOKER表示拥有当前存储过程的访问权限的用户能够执行当前存储过程。如果没有设置相关的值，则MySQL默认指定值为DEFINER。

·COMMENT 'string'：表示当前存储过程的注释信息，解释说明当前存储过程的含义。

注意：在MySQL的存储过程中允许包含DDL的SQL语句，允许执行Commit（提交）操作，也允许执行Rollback（回滚）操作，但是不允许执行LOAD DATA INFILE语句。在当前存储过程中，可以调用其他存储过程或者函数。

3．创建存储过程的简单示例

下面的SQL代码创建了一个名为SelectAllData的存储过程，这个存储过程比较简单，就是返回t_goods表中的所有数据，在MySQL命令行创建名为SelectAllData的存储过程。

mysql> DELIMITER $$
  mysql> CREATE PROCEDURE SelectAllData()
      -> BEGIN
      -> SELECT * FROM t_goods
      -> END $$
  Query OK, 0 rows affected (0.00 sec)
  mysql> DELIMITER ;

此时，名为SelectAllData的存储过程创建成功。

当用MySQL的命令行创建存储过程时，首先需要使用“DELIMITER $$”语句将MySQL数据库的语句结束符设置为“$$”。因为MySQL数据库默认的语句结束符为分号（;），如果不设置MySQL数据库的语句结束符，则存储过程中的SQL语句的结束符会与MySQL数据库默认的语句结束符相冲突。在创建存储过程的结尾使用“END $$”来结束存储过程。当整个存储过程创建完毕后，再使用“DELIMITER ;”语句将MySQL数据库的语句结束符恢复成默认的分号（;）。

用MySQL命令行创建存储过程时，也可以使用DELIMITER语句指定其他符号为语句结束符，而不一定是“$$”符号。


16.2.2　创建存储函数

1．创建存储函数的语法说明

在MySQL数据库中创建存储函数时需要使用CREATE FUNCTION语句。创建存储函数的语法格式如下：

CREATE FUNCTION func_name ([func_parameter[,...]])
      RETURNS type
      [characteristic ...] routine_body

语法格式说明：

·CREATE FUNCTION：创建函数必须使用的关键字；

·func_name：创建函数时指定的函数名称；

·func_parameter：创建函数时指定的参数列表，参数列表可以省略；

·RETURNS type：创建函数时指定的返回数据类型；

·characteristic：创建函数时指定的对函数的约束；

·routine_body：函数的SQL执行体。

2．参数详细说明

（1）对于参数列表而言，存储过程的参数类型可以是IN、OUT和INOUT类型，而存储函数的参数类型只能是IN类型。

（2）创建函数时对characteristic参数的说明与创建存储过程时对characteristic参数的说明相同，笔者不再赘述。

3．创建函数的简单示例

下面的SQL代码创建了一个名为SelectNameById的函数。这个函数比较简单，就是返回t_goods数据表中id为1000001的名称信息。在MySQL命令行中创建名为SelectNameById的函数。

mysql> DELIMITER $$
  mysql> CREATE FUNCTION SelectNameById()
      -> RETURNS varchar(30)
      -> RETURN (SELECT t_name FROM t_goods WHERE id = 1000001);
      -> $$
  Query OK, 0 rows affected (0.00 sec)
  
  mysql> DELIMITER ;

此时，名为SelectNameById的函数创建成功。

16.3　查看存储过程和函数

当在MySQL数据库中创建了存储过程和函数时，这些存储过程和函数就会被存储在MySQL数据库中，应用程序可以重复调用这些存储过程和函数。同时，MySQL数据库提供了3种方式来查看存储过程和函数，分别为使用SHOW CREATE语句查看存储过程和函数的创建信息；使用SHOW STATUS语句查看存储过程和函数的状态信息；从information_schema数据库中查看存储过程和函数的信息。


16.3.1　查看存储过程和函数的创建或定义信息

1．语法说明

使用SHOW CREATE语句查看存储过程和函数的创建信息，语法格式如下：

SHOW CREATE {PROCEDURE | FUNCTION} sp_name

语法格式说明：

·SHOW CREATE：查看存储过程和函数信息的关键字。

·{PROCEDURE | FUNCTION}：指定当前语句查看的是存储过程还是函数。PROCEDURE表示当前语句查看的是存储过程；FUNCTION表示当前语句查看的是函数。

·sp_name：存储过程或者函数的名称。

2．简单示例

（1）查看名为SelectAllData的存储过程的信息，在MySQL命令行中执行命令。

mysql> SHOW CREATE PROCEDURE SelectAllData \G
  *************************** 1. row ***************************
             Procedure: SelectAllData
              sql_mode: STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,
  NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
      Create Procedure: CREATE DEFINER=`root`@`localhost` PROCEDURE `SelectAllData`()
  BEGIN
  SELECT * FROM t_goods
  END
  character_set_client: utf8mb4
  collation_connection: utf8mb4_general_ci
    Database Collation: utf8mb4_general_ci
  1 row in set (0.00 sec)

可以看到，结果数据中展示了名为SelectAllData的存储过程信息，以及MySQL数据库的一些设置信息。

（2）查看名为SelectNameById的函数信息，在MySQL命令行中执行命令。

mysql> SHOW CREATE FUNCTION SelectNameById \G
  *************************** 1. row ***************************
              Function: SelectNameById
              sql_mode: STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,
  NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
       Create Function: CREATE DEFINER=`root`@`localhost` FUNCTION `SelectNameById`() RETURNS 
  varchar(30) CHARSET utf8mb4
  RETURN (SELECT t_name FROM t_goods WHERE id = 1000001)
  character_set_client: utf8mb4
  collation_connection: utf8mb4_general_ci
    Database Collation: utf8mb4_general_ci
  1 row in set (0.00 sec)

可以看到，结果数据中展示了名为SelectNameById的函数的信息，以及MySQL数据库的一些设置信息。


16.3.2　查看存储过程和函数的状态信息

1．语法说明

使用SHOW STATUS语句查看存储过程和函数的状态信息，语法格式如下：

SHOW {PROCEDURE | FUNCTION} STATUS [LIKE 'pattern']

语法格式说明：

·{PROCEDURE | FUNCTION}：指定当前语句查看的是存储过程还是函数。PROCEDURE表示当前语句查看的是存储过程；FUNCTION表示当前语句查看的是函数。

·[LIKE 'pattern']：匹配存储过程或函数的名称，可以省略。当省略不写时，会列出MySQL数据库中存在的所有存储过程或函数的信息。

2．简单示例

（1）查看名为SelectAllData的存储过程信息，在MySQL命令行中执行命令。

mysql> SHOW PROCEDURE STATUS LIKE 'SELECT%' \G 
  *************************** 1. row ***************************
                    Db: db_goods
                  Name: SelectAllData
                  Type: PROCEDURE
               Definer: root@localhost
              Modified: 2019-10-16 15:55:07
               Created: 2019-10-16 15:55:07
         Security_type: DEFINER
               Comment: 
  character_set_client: utf8mb4
  collation_connection: utf8mb4_general_ci
    Database Collation: utf8mb4_general_ci
  1 row in set (0.00 sec)

可以看到，输出结果中展示了当前存储过程所在的数据库为db_goods，存储过程的名称为SelectAllData，类型为存储过程，当前存储过程的所有者为root账户，并展示了当前存储过程的创建和修改时间，以及只有创建存储过程或者定义存储过程的用户才能执行该存储过程。另外，结果数据中还展示了MySQL数据库的一些设置信息。

（2）查看名为SelectNameById的函数信息，在MySQL命令行中执行命令。

mysql> SHOW FUNCTION STATUS LIKE 'SELECT%' \G
  *************************** 1. row ***************************
                    Db: db_goods
                  Name: SelectNameById
                  Type: FUNCTION
               Definer: root@localhost
              Modified: 2019-10-16 16:44:36
               Created: 2019-10-16 16:44:36
         Security_type: DEFINER
               Comment: 
  character_set_client: utf8mb4
  collation_connection: utf8mb4_general_ci
    Database Collation: utf8mb4_general_ci
  1 row in set (0.00 sec)

可以看到，结果数据展示了当前函数所在的数据库为db_goods，函数的名称为Select-NameById，类型为函数，当前函数的所有者为root账户，并展示了当前函数的创建和修改时间，以及只有创建或者定义函数的用户才能执行该函数。另外，结果数据中还展示了MySQL数据库的一些设置信息。


16.3.3　从数据库中查看存储过程和函数的信息

MySQL数据库会将创建的存储过程和函数的信息保存在information_schema数据库的ROUTINES数据表中。也就是说，可以通过查询information_schema数据库的ROUTINES数据表中的记录数据来查看存储过程和函数的信息。

1．语法说明

从information_schema数据库中查看存储过程和函数的信息，语法格式如下：

SELECT * FROM information_schema.ROUTINES where ROUTINE_NAME = 'sp_name' [and ROUTINE_TYPE = 
  {'PROCEDURE|FUNCTION'}];

语法格式也可以表示成如下：

SELECT * FROM information_schema.ROUTINES where ROUTINE_NAME LIKE 'sp_name' [and ROUTINE_TYPE = 
  {'PROCEDURE|FUNCTION'}];

语法格式说明：

·sp_name：要查询的存储过程或函数的名称；

·[and ROUTINE_TYPE = 'PROCEDURE|FUNCTION']：指定ROUTINE_TYPE的查询条件，ROUTINE_TYPE的取值可以为PROCEDURE或者FUNCTION。当ROUTINE_TYPE的取值为PROCEDURE时，sp_name表示要查询存储过程的名称；当ROUTINE_TYPE的取值为FUNCTION时，sp_name表示要查询函数的名称。此查询条件可以省略，当省略此查询条件时，MySQL会根据查询名称自动匹配查询的存储过程或者函数。

注意：如果在MySQL数据库中存在存储过程和函数名称相同的情况，最好指定ROUTINE_TYPE查询条件来指明查询的是存储过程还是函数。

2．简单示例

（1）查询名为SelectAllData存储过程的信息，在MySQL命令行中执行命令。

mysql> SELECT * FROM information_schema.ROUTINES where ROUTINE_NAME = 'SelectAllData' and ROUTINE_TYPE 
  = 'PROCEDURE' \G
  *************************** 1. row ***************************
             SPECIFIC_NAME: SelectAllData
           ROUTINE_CATALOG: def
            ROUTINE_SCHEMA: db_goods
              ROUTINE_NAME: SelectAllData
              ROUTINE_TYPE: PROCEDURE
                 DATA_TYPE: 
  CHARACTER_MAXIMUM_LENGTH: NULL
    CHARACTER_OCTET_LENGTH: NULL
         NUMERIC_PRECISION: NULL
             NUMERIC_SCALE: NULL
        DATETIME_PRECISION: NULL
        CHARACTER_SET_NAME: NULL
            COLLATION_NAME: NULL
            DTD_IDENTIFIER: NULL
              ROUTINE_BODY: SQL
        ROUTINE_DEFINITION: BEGIN
  SELECT * FROM t_goods
  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: 2019-10-16 15:55:07
              LAST_ALTERED: 2019-10-16 15:55:07
                  SQL_MODE: STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,
  NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
           ROUTINE_COMMENT: 
                   DEFINER: root@localhost
      CHARACTER_SET_CLIENT: utf8mb4
      COLLATION_CONNECTION: utf8mb4_general_ci
        DATABASE_COLLATION: utf8mb4_general_ci
  1 row in set (0.00 sec)

也可以表示如下：

SELECT * FROM information_schema.ROUTINES where ROUTINE_NAME LIKE 'Select%' and ROUTINE_TYPE =
   'PROCEDURE' \G

可以看到，在information_schema数据库的ROUTINES表中保存了存储过程的信息，通过查询information_schema数据库的ROUTINES表中的记录数据，可以获取存储过程的信息。

（2）查询名为SelectNameById的函数信息，在MySQL命令行中执行命令。

mysql> SELECT * FROM information_schema.ROUTINES where ROUTINE_NAME = 'SelectNameById' and ROUTINE_TYPE 
  = 'FUNCTION' \G
  *************************** 1. row ***************************
             SPECIFIC_NAME: SelectNameById
           ROUTINE_CATALOG: def
            ROUTINE_SCHEMA: db_goods
              ROUTINE_NAME: SelectNameById
              ROUTINE_TYPE: FUNCTION
                 DATA_TYPE: varchar
  CHARACTER_MAXIMUM_LENGTH: 30
    CHARACTER_OCTET_LENGTH: 120
         NUMERIC_PRECISION: NULL
             NUMERIC_SCALE: NULL
        DATETIME_PRECISION: NULL
        CHARACTER_SET_NAME: utf8mb4
            COLLATION_NAME: utf8mb4_general_ci
            DTD_IDENTIFIER: varchar(30)
              ROUTINE_BODY: SQL
        ROUTINE_DEFINITION: RETURN (SELECT t_name FROM t_goods WHERE id = 1000001)
             EXTERNAL_NAME: NULL
         EXTERNAL_LANGUAGE: NULL
           PARAMETER_STYLE: SQL
          IS_DETERMINISTIC: NO
           SQL_DATA_ACCESS: CONTAINS SQL
                  SQL_PATH: NULL
             SECURITY_TYPE: DEFINER
                   CREATED: 2019-10-16 16:44:36
              LAST_ALTERED: 2019-10-16 16:44:36
                  SQL_MODE: STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,
  NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
           ROUTINE_COMMENT: 
                   DEFINER: root@localhost
      CHARACTER_SET_CLIENT: utf8mb4
      COLLATION_CONNECTION: utf8mb4_general_ci
        DATABASE_COLLATION: utf8mb4_general_ci
  1 row in set (0.00 sec)

也可以表示如下：

SELECT * FROM information_schema.ROUTINES where ROUTINE_NAME LIKE 'Select%' and ROUTINE_TYPE = 
  'FUNCTION' \G

在information_schema数据库的ROUTINES表中保存了函数信息，通过查询information_schema数据库的ROUTINES表中的记录数据，可以获取函数信息。

16.4　修改存储过程和函数

创建存储过程和函数后，可以通过ALTER语句修改存储过程和函数的某些特性。本节介绍如何修改存储过程和函数。


16.4.1　修改存储过程

1．语法说明

修改存储过程的语法格式如下：

ALTER PROCEDURE sp_name [characteristic ...]

语法格式说明：

·ALTER PROCEDURE：修改存储过程必须使用的关键字；

·sp_name：需要修改的存储过程的名称；

·characteristic：存储过程修改后的特性。

2．参数说明

characteristic参数在修改存储过程时表示存储过程修改后的特性，其取值信息与创建存储过程时的取值信息略有不同。

characteristic参数在修改存储过程时的取值信息如下：

    { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
    | SQL SECURITY { DEFINER | INVOKER }
    | COMMENT 'string'

其中，每个参数的含义与16.2.1节中的characteristic参数含义相同，不再赘述。

3．简单示例

（1）修改存储过程SelectAllData的定义，将读写权限修改为READS SQL DATA，同时加上注释信息Select All Data。在MySQL命令行中执行命令。

mysql> ALTER PROCEDURE SelectAllData
      -> READS SQL DATA
      -> COMMENT 'Select All Data';
  Query OK, 0 rows affected (0.00 sec)

可以看到，成功执行SQL语句。

（2）通过查询information_schema数据库的ROUTINES数据表中的记录数据来查看存储过程SelectAllData的信息。

mysql> SELECT * FROM information_schema.ROUTINES where ROUTINE_NAME = 'SelectAllData' and ROUTINE_TYPE 
  = 'PROCEDURE' \G
  *************************** 1. row ***************************
  SPECIFIC_NAME: SelectAllData
           ……….此处省略………
           SQL_DATA_ACCESS: READS SQL DATA
           ……….此处省略………
           ROUTINE_COMMENT: Select All Data
           ……….此处省略………
  1 row in set (0.01 sec)

在查询的结果信息中可以看到：名称为SelectAllData的存储过程的SQL_DATA_ACCESS字段信息被修改为READS SQL DATA，同时加上了注释信息ROUTINE_COMMENT:Select All Data。


16.4.2　修改存储函数

1．语法说明

修改函数的语法格式如下：

ALTER FUNCTION func_name [characteristic ...]

语法格式说明：

·ALTER FUNCTION：修改函数必须使用的关键字；

·func_name：需要修改的函数名称；

·characteristic：函数修改后的特性。

characteristic参数的详细说明与16.4.1节中characteristic参数的详细说明相同，不再赘述。

2．简单示例

（1）修改函数SelectNameById的定义，将读写权限修改为MODIFIES SQL DATA，同时将调用权限修改为具有访问权限的用户可以调用并执行。在MySQL命令行中执行命令。

mysql> ALTER FUNCTION SelectNameById
      -> MODIFIES SQL DATA
      -> SQL SECURITY INVOKER;
  Query OK, 0 rows affected (0.00 sec)

（2）通过查询information_schema数据库的ROUTINES数据表中的记录数据来查看函数SelectNameById的信息。

mysql> SELECT * FROM information_schema.ROUTINES where ROUTINE_NAME = 'SelectNameById' and ROUTINE_TYPE 
  = 'FUNCTION' \G
  *************************** 1. row ***************************
             SPECIFIC_NAME: SelectNameById
           ……….此处省略………
           SQL_DATA_ACCESS: MODIFIES SQL DATA
           ……….此处省略………
             SECURITY_TYPE: INVOKER
           ……….此处省略………
  1 row in set (0.01 sec)

在查询的结果信息中可以看到：名称为SelectNameById的函数的SQL_DATA_ACCESS字段被修改为MODIFIES SQL DATA，同时，SECURITY_TYPE字段信息被修改为INVOKER，说明此时名称为SelectNameById的函数的读写权限为MODIFIES SQL DATA，调用权限为具有访问权限的用户才可以调用并执行。

16.5　调用存储过程和函数

调用存储过程和调用函数的方式稍有区别，调用存储过程使用的是CALL语句，而调用函数使用的是SELECT语句。


16.5.1　调用存储过程

1．语句说明

调用存储过程使用的是CALL语句，语法格式如下：

CALL proc_name ([parameter[,…]])

语法格式说明：

·CALL：调用存储过程的关键字；

·proc_name：调用存储过程的名称；

·parameter：存储过程定义的参数列表，当创建存储过程时没有定义参数列表，则参数列表为空。

2．简单示例

调用名称为SelectAllData的存储过程，在MySQL命令行中执行命令。

mysql> CALL SelectAllData();
  +---------+-----------+------------------+---------+---------+---------------------+
  | id      | t_name    | t_category       | t_price | t_stock |     t_upper_time    |
  +---------+-----------+------------------+---------+---------+---------------------+
  | 1000001 | 连衣裙     | 女装/女士精品    |   49.90 |     500 | 2019-12-14 00:00:00 |
  | 1000002 | 破洞女仔裤 | 女装/女士精品    |   79.90 |     550 | 2019-12-14 00:00:00 |
  | 1000003 | T恤       | 男装             |   59.90 |     680 | 2019-12-15 00:00:00 |
  | 1000004 | 卫衣       | 男装             |   79.90 |    1000 | 2019-12-15 00:00:00 |
  | 1000005 | 床单       | 居家用品         |   69.90 |     500 | 2019-12-16 00:00:00 |
  | 1000006 | 枕头       | 居家用品         |   29.90 |     700 | 2019-12-16 00:00:00 |
  | 1000007 | 项链       | 饰品             | 5999.90 |    2500 | 2019-12-17 00:00:00 |
  | 1000008 | 戒指       | 饰品             | 4999.90 |    2000 | 2019-12-17 00:00:00 |
  | 1000009 | 头灯       | 登山设备         |  129.90 |     200 | 2019-12-18 00:00:00 |
  | 1000010 | 登山杖     | 登山设备         |  159.90 |     500 | 2019-12-18 00:00:00 |
  +---------+-----------+------------------+---------+---------+---------------------+
  10 rows in set (0.00 sec)
  
  Query OK, 0 rows affected (0.00 sec)

可以看到，调用名称为SelectAllData的存储过程，能够查询出t_goods数据表中的所有数据信息。


16.5.2　调用存储函数

1．语法说明

调用函数使用的是SELECT语句，语法格式如下：

SELECT func_name ([parameter[,…]])

语法格式说明：

·SELECT：调用函数的关键字，也是查询数据的关键字；

·func_name：调用的函数名称；

·parameter：调用函数的参数列表，当创建函数时没有定义参数列表，则参数列表为空。

2．简单示例

调用名称为SelectNameById的函数，在MySQL命令行中执行命令。

mysql> SELECT SelectNameById();
  +------------------+
  | SelectNameById() |
  +------------------+
  | 连衣裙           |
  +------------------+
  1 row in set (0.00 sec)

可以看到，调用名称为SelectNameById的函数，能够查询出t_goods数据表中主键编号为“1000001”的商品名称，并且商品名称为“连衣裙”。

16.6　删除存储过程和函数

删除存储过程和函数可以使用DROP语句。本节简单介绍一下在MySQL数据库中如何删除存储过程和函数。


16.6.1　删除存储过程

1．语法说明

删除存储过程的语法格式如下：

DROP PROCEDURE [IF EXISTS] proc_name

语法格式说明：

·DROP PROCEDURE：删除存储过程必须使用的关键字；

·[IF EXISTS]：当需要删除的存储过程不存在时不会报错；

·proc_name：需要删除的存储过程的名称。

2．简单示例

（1）删除名称为SelectAllData的存储过程，在MySQL命令行中执行命令。

mysql> DROP PROCEDURE IF EXISTS SelectAllData;
  Query OK, 0 rows affected (0.00 sec)

可以看到，SQL语句执行成功。

（2）通过查询information_schema数据库的ROUTINES数据表中的记录数据来查看存储过程SelectAllData的信息。

mysql> SELECT * FROM information_schema.ROUTINES where ROUTINE_NAME = 'SelectAllData' and ROUTINE_TYPE 
  = 'PROCEDURE' \G
  Empty set (0.00 sec)

可以看到，查询出的结果数据为空，说明名称为SelectAllData的存储过程已经被成功删除。


16.6.2　删除存储函数

1．语法说明

删除函数的语法格式如下：

DROP FUNCTION [IF EXISTS] func_name

语法格式说明：

·DROP FUNCTION：删除函数必须使用的关键字；

·[IF EXISTS]：当要删除的函数不存在时不会报错；

·func_name：需要删除的函数的名称。

2．简单示例

（1）删除名称为SelectNameById的函数，在MySQL命令行中执行命令。

mysql> DROP FUNCTION IF EXISTS SelectNameById;
  Query OK, 0 rows affected (0.00 sec)

可以看到，SQL语句执行成功。

（2）通过查询information_schema数据库的ROUTINES数据表中的记录数据，查看函数SelectNameById的信息。

mysql> SELECT * FROM information_schema.ROUTINES where ROUTINE_NAME = 'SelectNameById' and ROUTINE_TYPE 
  = 'FUNCTION' \G
  Empty set (0.00 sec)

查询出的结果数据为空，说明名称为SelectNameById的函数已经被成功删除。

16.7　MySQL中使用变量

在MySQL数据库的存储过程和函数中，可以使用变量来存储查询或计算的中间结果数据，或者输出最终的结果数据。本节简单介绍在MySQL数据库中如何使用变量。


16.7.1　定义变量

在MySQL数据库中，可以使用DECLARE语句定义一个局部变量，变量的作用域为BEGIN…END语句块，变量也可以被用在嵌套的语句块中。变量的定义需要写在复合语句的开始位置，并且需要在任何其他语句的前面。定义变量时，可以一次声明多个相同类型的变量，也可以使用DEFAULT为变量赋予默认值。

1．语法说明

定义变量的语法格式如下：

DECLARE var_name[,...] type [DEFAULT value]

语法格式说明：

·DECLARE：定义变量使用的关键字；

·var_name[,...]：定义的变量名称，可以一次声明多个相同类型的变量；

·type：定义变量的数据类型，此类型可以是MySQL数据库中支持的任意数据类型；

·[DEFAULT value]：定义变量的默认值，可以省略，如果没有为变量指定默认值，默认值为NULL。

2．简单示例

定义一个名称为totalprice的变量，类型为DECIMAL(10,2)，默认值为0.00。

DECLARE totalprice DECIMAL(10,2) DEFAULT 0.00;


16.7.2　变量赋值

定义变量后，可以为变量进行赋值操作。变量可以直接赋值，也可以通过查询语句赋值。

1．直接赋值

可以使用SET语句为变量直接赋值，语法格式如下：

SET var_name = expr [, var_name = expr] ...

语法格式说明：

·SET：为变量赋值的关键字；

·var_name：变量名称；

·expr：变量的值，可以是一个常量，也可以是一个表达式。

使用SET语句为16.7.1节中定义的totalprice变量直接赋值。

SET totalprice = 2999.99;

或者可以使用类似于下面的SQL语句为totalprice变量赋值：

SET totalprice = (1999.99 * 12);

2．通过查询语句赋值

MySQL数据库支持通过查询语句为变量赋值。当通过查询语句为变量赋值时，要求查询语句返回的结果数据必须只有一行，语法格式如下：

SELECT col_name[,...] INTO var_name[,...] table_expr

·SELECT：查询语句的关键字；

·col_name：数据表中字段的名称，可以同时查询数据表中多个字段的数据；

·var_name：定义的变量名称，可以为多个变量赋值；

·table_expr：查询表数据时使用的查询条件，查询条件中包含表名称和WHERE语句。

使用查询语句为16.7.1节中定义的totalprice变量赋值：

SELECT SUM(t_price) INTO totalprice FROM t_goods

16.8　MySQL中使用变量案例

在MySQL数据库中，往往是在存储过程和函数中使用自定义变量。在了解了如何定义变量和为变量赋值后，本节介绍如何在存储过程和函数中使用变量。


16.8.1　在存储过程中使用变量

可以在存储过程和函数中使用变量，本节简单介绍一个在存储过程中使用变量的完整示例。

1．需求描述

在db_goods数据库中创建名为SelectCountAndPrice的存储过程，在存储过程中定义3个变量，分别为totalcount、totalprice和avgprice。其中，totalcount为INT类型，默认值为0；totalprice和avgprice为DECIMAL(10,2)类型，默认值为0.00。查询t_goods表中的记录条数，并将其赋值给变量totalcount，统计t_goods表中所有商品的总价格，将其赋值给变量totalprice。接下来将totalprice除以totalcount得出的平均价格赋值给变量avgprice。最后输出totalprice、totalcount和avgprice的值。

2．代码实现

创建存储过程，在MySQL命令行中执行代码。

mysql> DELIMITER $$
  mysql> CREATE PROCEDURE SelectCountAndPrice()
      -> BEGIN
      -> DECLARE totalcount INT DEFAULT 0;
      -> DECLARE totalprice, avgprice DECIMAL(10,2) DEFAULT 0.00;
      -> SELECT COUNT(*) INTO totalcount FROM t_goods
      -> SELECT SUM(t_price) INTO totalprice FROM t_goods
      -> SET avgprice = totalprice / totalcount;
      -> SELECT totalprice, totalcount, avgprice;
      -> END $$
  Query OK, 0 rows affected (0.00 sec)
  
  mysql> DELIMITER ;

可以看到，名为SelectCountAndPrice的存储过程创建成功。

3．调用存储过程

在MySQL命令行中调用SelectCountAndPrice存储过程，并输出结果数据。

mysql> CALL SelectCountAndPrice();
  +------------+------------+----------+
  | totalprice | totalcount | avgprice |
  +------------+------------+----------+
  |   11659.00 |         10 |  1165.90 |
  +------------+------------+----------+
  1 row in set (0.00 sec)
  Query OK, 0 rows affected (0.00 sec)

调用名为SelectCountAndPrice的存储过程，正确输出了变量totalprice、totalcount与avgprice的值。


16.8.2　在函数中使用变量

变量不仅可以在存储过程中使用，也可以在函数中使用。本节简单介绍如何在函数中使用变量。

1．需求描述

在db_goods数据库中创建名为SelectCountAndStock的函数，在函数中定义3个变量，分别为totalcount、totalstock和avgstock，数据类型为INT，默认值为0。首先，查询t_goods数据表中所有的数据记录条数，并赋值给变量totalcount；统计t_goods数据表中所有的库存总量，并赋值给变量totalstock。然后，将totalstock除以totalcount得出的平均库存数量赋值给avgstock变量，最后，输出avgstock变量的值。

2．代码实现

创建函数，在MySQL命令行中执行代码。

mysql> DELIMITER $$
  mysql> CREATE FUNCTION SelectCountAndStock()
      -> RETURNS INT
      -> BEGIN
      -> DECLARE totalcount, totalstock, avgstock INT DEFAULT 0;
      -> SELECT COUNT(*) INTO totalcount FROM t_goods
      -> SELECT SUM(t_stock) INTO totalstock FROM t_goods
      -> SET avgstock = totalstock / totalcount;
      -> RETURN avgstock;
      -> END $$
  Query OK, 0 rows affected (0.00 sec)
  mysql> DELIMITER ;

可以看到，名为SelectCountAndStock的函数创建成功。

3．调用函数

在MySQL命令行中调用SelectCountAndStock()函数，输出结果数据。

mysql> SELECT SelectCountAndStock();
  +-----------------------+
  | SelectCountAndStock() |
  +-----------------------+
  |                   913 |
  +-----------------------+
  1 row in set (0.00 sec)

可以看到，调用名称为SelectCountAndStock的函数，成功输出了结果数据。

16.9　定义条件和处理程序

MySQL数据库支持定义条件和处理程序。定义条件就是提前将程序执行过程中遇到的问题及对应的状态等信息定义出来，在程序执行过程中遇到问题时，可以返回提前定义好的条件信息。处理程序能够定义在程序执行过程中遇到问题时应该采取何种处理方式来保证程序能够继续执行。


16.9.1　定义条件

1．语法说明

定义条件可以使用DECLARE语句，语法格式如下：

DECLARE condition_name CONDITION FOR condition_value

语法格式说明：

·condition_name：定义的条件名称；

·condition_value：定义的条件类型。

2．参数详细说明

condition_value的取值如下：

SQLSTATE [VALUE] sqlstate_value | mysql_error_code

参数说明：

·sqlstate_value：长度为5的字符串类型的错误信息；

·mysql_error_code：数值类型的错误代码。

3．简单示例

定义“ERROR 2199(48000)”错误条件，名称为exec_refused。此时，可以使用两种方式进行定义。

（1）使用sqlstate_value进行定义。

DECLARE exec_ refused CONDITION FOR SQLSTATE ‘48000’;

（2）使用mysql_error_code进行定义。

DECLARE exec_refused CONDITION FOR 2199;


16.9.2　定义处理程序

1．语法说明

定义处理程序也可以使用DECLARE语句，语法格式如下：

DECLARE handler_type HANDLER FOR condition_value[,...] sp_statement

语法格式说明：

·handler_type：定义的错误处理方式；

·condition_value：定义的错误类型；

·sp_statement：当遇到定义的错误时，需要执行的存储过程或函数。

2．参数详细说明

在定义处理程序的语法中，需要对handler_type参数和condition_value参数进行详细说明。

（1）handler_type参数的取值如下：

CONTINUE | EXIT | UNDO

参数说明：

·CONTINUE：遇到错误时，不进行处理，继续向后执行；

·EXIT：遇到错误时，立刻退出程序；

·UNDO：遇到错误时，撤回之前的操作。

注意：目前MySQL数据库还不支持UNDO操作。

（2）condition_value参数的取值如下：

SQLSTATE [VALUE] sqlstate_value
    | condition_name
    | SQLWARNING
    | NOT FOUND
    | SQLEXCEPTION
    | mysql_error_code

参数说明：

·SQLSTATE [VALUE] sqlstate_value：长度为5的字符串类型的错误信息；

·condition_name：定义的条件名称；

·SQLWARNING：所有以01开头的SQLSTATE错误代码；

·NOT FOUND：所有以02开头的SQLSTATE错误代码；

·SQLEXCEPTION：所有没有被SQLWARNING或NOT FOUND捕获的SQLSTATE错误代码；

·mysql_error_code：数值类型的错误代码。

3．简单示例

（1）定义处理程序捕获sqlstate_value值，当遇到sqlstate_value值为29011时，执行CONTINUE操作，并且输出DATABASE NOT FOUND信息。

DECLARE CONTINUE HANDLER FOR SQLSTATE '29011' SET @log=' DATABASE NOT FOUND';

（2）定义处理程序捕获mysql_error_code的值，当遇到mysql_error_code的值为1162时，执行CONTINUE操作，并且输出SEARCH FAILED信息。

DECLARE CONTINUE HANDLER FOR 1162 SET @log=' SEARCH FAILED';

（3）先定义search_failed条件，捕获mysql_error_code的值，当遇到mysql_error_code的值为1162时，执行CONTINUE操作。接下来定义处理程序，调用search_failed条件，并输出SEARCH FAILED信息。

DECLARE search_failed CONDITION FOR 1162;
  DECLARE CONTINUE HANDLER FOR search_failed SET @log=' SEARCH FAILED';

（4）使用SQLWARNING捕获所有以01开头的sqlstate_value错误代码，执行CONTINUE操作，并输出SQLWARNING信息。

DECLARE CONTINUE HANDLER FOR SQLWARNING SET @log=' SQLWARNING';

（5）使用NOT FOUND捕获所有以02开头的sqlstate_value错误代码，执行EXIT操作，并输出SQL EXIT信息。

DECLARE EXIT HANDLER FOR NOT FOUND SET @log=' SQL EXIT';

（6）使用SQLEXCEPTION捕获所有没有被SQLWARNING或NOT FOUND捕获的sqlstate_value错误代码，执行EXIT操作，并输出SQLEXCEPTION信息。

DECLARE EXIT HANDLER FOR SQLEXCEPTION SET @log=' SQLEXCEPTION';

注意：带有@符号的变量（比如@log）是用户变量，可以使用SET语句进行赋值，用户变量与MySQL的连接有关。在一个客户端的连接会话中定义的用户变量，只能在此连接会话中可见并使用，当此连接会话关闭时，该连接会话中创建的所有变量都会被自动释放。

16.10　定义条件和处理程序案例

在了解了如何定义条件和处理程序后，本节将分别介绍在存储过程或函数中未定义或已定义条件和处理程序的案例，通过未定义和已定义条件和处理程序的对比，使读者能够更加深入地理解定义条件和处理程序对存储过程和函数所起到的作用。


16.10.1　在存储过程中未定义条件和处理程序

1．创建存储过程

创建一个名称为InsertDataNoCondition的存储过程，此存储过程的功能比较简单，首先为@x变量赋值1；然后向t_goods表中插入一条主键编号为1000011的数据，并将@x变量的值修改为2；最后再次向t_goods表中插入一条主键编号为1000010的数据，并将@x变量的值修改为3。

创建名称为InsertDataNoCondition的存储过程，在MySQL命令行中执行代码。

mysql> DELIMITER $$
  mysql> CREATE PROCEDURE InsertDataNoCondition()
      -> BEGIN
      -> SET @x = 1;
      -> INSERT INTO db_goods.t_goods (id, t_name, t_category, t_price, t_stock, t_upper_time) VALUES 
  ('1000011', '耐克运动鞋', '男鞋', '1399.90', '500', '2019-12-18 00:00:00');
      -> SET @x = 2;
      -> INSERT INTO db_goods.t_goods (id, t_name, t_category, t_price, t_stock, t_upper_time) VALUES 
  ('1000010', '登山杖', '登山设备', '159.90', '500', '2019-12-18 00:00:00');
      -> SET @x = 3;
      -> END $$
  Query OK, 0 rows affected (0.00 sec)
  
  mysql> DELIMITER ;

可以看到，名为InsertDataNoCondition的存储过程创建成功。

2．调用存储过程

在MySQL命令行中调用存储过程。

mysql> CALL InsertDataNoCondition();
  ERROR 1062 (23000): Duplicate entry '1000010' for key 'PRIMARY'

可以看到，MySQL数据库报错，主键为1000010的记录已经存在。

此时，查看@x命令的值。

mysql> SELECT @x;
  +------+
  | @x   |
  +------+
  |    2 |
  +------+
  1 row in set (0.00 sec)

可以看到，此时@x变量的值为2。

结合创建存储过程的SQL语句代码可以得出：在存储过程中未定义条件和处理程序，且当存储过程中执行的SQL语句报错时，MySQL数据库会抛出错误，并退出当前SQL逻辑，不再向下继续执行。


16.10.2　在存储过程中定义条件和处理程序

1．创建存储过程

创建一个名称为InsertDataWithCondition的存储过程，在存储过程中，定义处理程序，捕获sqlstate_value值，当遇到sqlstate_value值为23000时，执行CONTINUE操作，并且将@proc_value的值设置为1。

接下来，将@x变量的值设置为1，向t_goods表中插入一条主键编号为1000011的数据，并将@x变量的值修改为2；随后，再次向t_goods表中插入一条主键编号为1000010的数据，并将@x变量的值修改为3。

创建名称为InsertDataWithCondition的存储过程，在MySQL命令行中执行代码。

mysql> DELIMITER $$
  mysql> CREATE PROCEDURE InsertDataWithCondition()
      -> BEGIN
      -> DECLARE CONTINUE HANDLER FOR SQLSTATE '23000' SET @proc_value=1;
      -> SET @x = 1;
      -> INSERT INTO db_goods.t_goods (id, t_name, t_category, t_price, t_stock, t_upper_time) VALUES 
  ('1000011', '耐克运动鞋', '男鞋', '1399.90', '500', '2019-12-18 00:00:00');
      -> SET @x = 2;
      -> INSERT INTO db_goods.t_goods (id, t_name, t_category, t_price, t_stock, t_upper_time) VALUES 
  ('1000010', '登山杖', '登山设备', '159.90', '500', '2019-12-18 00:00:00');
      -> SET @x = 3;
      -> END $$
  Query OK, 0 rows affected (0.13 sec)
  
  mysql> DELIMITER ;

可以看到，名为InsertDataWithCondition的存储过程创建成功。

另外，此时创建的名称为InsertDataWithCondition的存储过程与16.10.1节中创建的名称为InsertDataNoCondition的存储过程基本相同，只是多了一行定义处理程序的代码。

DECLARE CONTINUE HANDLER FOR SQLSTATE '23000' SET @proc_value=1;

2．调用存储过程

在MySQL命令行中调用存储过程。

mysql> CALL InsertDataWithCondition();
  Query OK, 0 rows affected (0.00 sec)

可以看到，正确执行了存储过程。

接下来，查询@proc_value变量的值和@x变量的值。

mysql> SELECT @proc_value, @x;
  +-------------+------+
  | @proc_value | @x   |
  +-------------+------+
  |           1 |    3 |
  +-------------+------+
  1 row in set (0.00 sec)

名称为InsertDataWithCondition的存储过程执行了定义处理程序的代码，将@proc_value的值设置为1。同时，定义的处理程序捕获到SQL语句抛出的异常，并继续向下执行，最后将@x的值设置为3。


16.10.3　在函数中未定义条件和处理程序

1．创建函数

创建名称为InsertDataNoCondition的函数，函数的返回类型为INT。在函数中，先将@x变量的值设置为1，向t_goods表中插入一条主键编号为1000011的数据，并将@x变量的值修改为2；随后再次向t_goods表中插入一条主键编号为1000010的数据，并将@x变量的值修改为3。

创建函数，在MySQL命令行中执行代码。

mysql> DELIMITER $$
  mysql> CREATE FUNCTION InsertDataNoCondition()
      -> RETURNS INT
      -> BEGIN
      -> SET @x = 1;
      -> INSERT INTO db_goods.t_goods (id, t_name, t_category, t_price, t_stock, t_upper_time) VALUES 
  ('1000011', '耐克运动鞋', '男鞋', '1399.90', '500', '2019-12-18 00:00:00');
      -> SET @x = 2;
      -> INSERT INTO db_goods.t_goods (id, t_name, t_category, t_price, t_stock, t_upper_time) VALUES 
  ('1000010', '登山杖', '登山设备', '159.90', '500', '2019-12-18 00:00:00');
      -> SET @x = 3;
      -> RETURN @x;
      -> END $$
  Query OK, 0 rows affected (0.00 sec)
  
  mysql> DELIMITER ;

可以看到，名称为InsertDataNoCondition的函数创建成功。

2．调用函数

在MySQL命令行中调用InsertDataNoCondition()函数。

mysql> SELECT InsertDataNoCondition();
  ERROR 1062 (23000): Duplicate entry '1000010' for key 'PRIMARY'

MySQL抛出错误信息，主键编号为1000010的数据已经存在。

此时，查询@x的变量信息。

mysql> SELECT @x;
  +------+
  | @x   |
  +------+
  |    2 |
  +------+
  1 row in set (0.00 sec)

@x变量的值为2。同样说明，在函数中未定义条件和处理程序，且当SQL语句抛出错误时，MySQL会退出当前SQL逻辑，不会向下继续执行。


16.10.4　在函数中定义条件和处理程序

1．创建函数

创建名称为InsertDataWithCondition的函数，函数的返回类型为INT。在函数中定义处理程序，捕获sqlstate_value值，当遇到sqlstate_value值为23000时，执行CONTINUE操作，并将@func_value的值设置为1。

首先将@x变量的值设置为1，向t_goods表中插入一条主键编号为1000011的数据，并将@x变量的值修改为2；然后再次向t_goods表中插入一条主键编号为1000010的数据，并将@x变量的值修改为3。

创建函数，在MySQL命令行中执行代码。

mysql> DELIMITER $$
  mysql> CREATE FUNCTION InsertDataWithCondition()
      -> RETURNS INT
      -> BEGIN
      -> DECLARE CONTINUE HANDLER FOR SQLSTATE '23000' SET @func_value=1;
      -> SET @x = 1;
      -> INSERT INTO db_goods.t_goods (id, t_name, t_category, t_price, t_stock, t_upper_time) VALUES 
  ('1000011', '耐克运动鞋', '男鞋', '1399.90', '500', '2019-12-18 00:00:00');
      -> SET @x = 2;
      -> INSERT INTO db_goods.t_goods (id, t_name, t_category, t_price, t_stock, t_upper_time) VALUES 
  ('1000010', '登山杖', '登山设备', '159.90', '500', '2019-12-18 00:00:00');
      -> SET @x = 3;
      -> RETURN @x;
      -> END $$
  Query OK, 0 rows affected (0.00 sec)
  mysql> DELIMITER ;

可以看到，SQL语句执行成功，名称为InsertDataWithCondition的函数创建成功。

2．调用函数

在MySQL命令行调用名称为InsertDataWithCondition的函数。

mysql> SELECT InsertDataWithCondition();
  +---------------------------+
  | InsertDataWithCondition() |
  +---------------------------+
  |                         3 |
  +---------------------------+
  1 row in set (0.00 sec)

函数的返回值为3。说明在名称为InsertDataWithCondition的函数中成功执行了定义条件和处理程序的代码，捕获到SQL语句抛出的错误，并向下继续执行代码，最终将@x变量的值设置为3，并返回@x变量的值。

16.11　MySQL中游标的使用

如果在存储过程和函数中查询的数据量非常大，可以使用游标对结果集进行循环处理。MySQL中游标的使用包括声明游标、打开游标、使用游标和关闭游标。


16.11.1　声明游标

1．语法说明

可以使用DECLARE语句声明游标，语法格式如下：

DECLARE cursor_name CURSOR FOR select_statement

语法格式说明：

·cursor_name：声明的游标名称；

·select_statement：SELECT查询语句的内容，返回一个创建游标结果数据的集合。

2．简单示例

创建一个名称为cursor_proc_func的游标，从表t_goods中查询出商品名称、商品价格和商品库存信息，代码如下：

DECLARE cursor_proc_func CURSOR FOR SELECT t_name, t_price, t_stock FROM t_goods


16.11.2　打开游标

1．语法说明

可以使用OPEN语句打开之前声明的游标，语法格式如下：

OPEN cursor_name;

语法格式说明：

·cursor_name：声明的游标名称。

2．简单示例

打开之前创建名称为cursor_proc_func的游标。

OPEN cursor_proc_func;


16.11.3　使用游标

1．语法说明

可以使用FETCH语句使用之前打开的游标，语法格式如下：

FETCH cursor_name INTO var_name [, var_name] ...

语法格式说明：

·cursor_name：之前声明的游标名称；

·var_name：接收创建游标时定义的查询语句的结果数据，可以定义多个var_name。

注意：var_name必须在声明游标之前定义好。

2．简单示例

使用名称为cursor_proc_func的游标，将查询出来的结果数据分别存入name、price和stock这3个变量中。

FETCH cursor_proc_func INTO name, price, stock;

注意：name、price和stock这3个变量必须提前定义好。


16.11.4　关闭游标

1．语法说明

可以使用CLOSE语句关闭之前打开的游标，语法格式如下：

CLOSE cursor_name

语法格式说明：

·cursor_name：之前打开的游标名称。

2．简单示例

关闭名称为cursor_proc_func的游标：

CLOSE cursor_proc_func;

注意：游标必须在声明处理程序之前被声明，并且变量和条件必须在声明游标或处理程序之前被声明。游标只能用在存储过程和函数中。

16.12　MySQL中游标的使用案例

了解了游标在MySQL数据库中的基本使用方法后，本节简单介绍如何在MySQL数据库的存储过程和函数中使用游标。


16.12.1　在存储过程中使用游标

1．创建带有游标的存储过程

创建一个名称为StatisticsPrice的存储过程，该存储过程接收一个DECIMAL(10,2)类型的输出参数totalprice，使用游标对t_goods表中查询出的价格数据进行循环处理，累加商品价格信息到totalprice变量。判断循环结束的条件是捕获NOT FOUND状态，当游标找不到下一条记录时，程序会关闭游标并退出存储过程。

创建存储过程，在MySQL命令行中执行代码。

mysql> DELIMITER $$
  mysql> CREATE PROCEDURE StatisticsPrice(OUT totalprice DECIMAL(10,2))
      -> BEGIN
      -> DECLARE price DECIMAL(10,2) DEFAULT 0.00;
      -> DECLARE cursor_price CURSOR FOR SELECT t_price FROM t_goods
      -> DECLARE EXIT HANDLER FOR NOT FOUND CLOSE cursor_price;
      -> SET totalprice = 0.00;
      -> OPEN cursor_price;
      -> REPEAT
      -> FETCH cursor_price INTO price;
      -> SET totalprice = totalprice + price;
      -> UNTIL 0 END REPEAT;
      -> CLOSE cursor_price;
      -> END $$
  Query OK, 0 rows affected (0.00 sec)
  mysql> DELIMITER ;

名为StatisticsPrice的存储过程创建成功。

2．调用存储过程

在MySQL命令行中调用名称为StatisticsPrice的存储过程。

mysql> CALL StatisticsPrice(@x);
  Query OK, 0 rows affected (0.00 sec)

此时，查询@x变量的值。

mysql> SELECT @x;
  +----------+
  | @x       |
  +----------+
  | 11659.00 |
  +----------+
  1 row in set (0.00 sec)

名称为StatisticsPrice的存储过程使用游标正确地统计出了t_goods数据表中的商品价格信息，并将统计出的商品总价格赋值给了输出参数。


16.12.2　在函数中使用游标

1．创建带有游标的函数

创建一个名称为StatisticsStock的函数，在函数中定义两个INT类型的变量stock和totalstock，使用游标对t_goods表中查询出的商品库存数据进行循环处理，累加商品库存信息到totalstock变量。判断循环结束的条件是捕获NOT FOUND状态，当游标找不到下一条记录时，程序会继续向下执行并返回totalstock变量的值。

创建函数，在MySQL命令行中执行代码。

mysql> DELIMITER $$
  mysql> CREATE FUNCTION StatisticsStock()
      -> RETURNS INT
      -> BEGIN
      -> DECLARE stock, totalstock INT DEFAULT 0;
      -> DECLARE cursor_stock CURSOR FOR SELECT t_stock FROM t_goods
      -> DECLARE CONTINUE HANDLER FOR NOT FOUND RETURN totalstock ;
      -> OPEN cursor_stock;
      -> REPEAT
      -> FETCH cursor_stock INTO stock;
      -> SET totalstock = totalstock + stock;
      -> UNTIL 0 END REPEAT;
      -> CLOSE cursor_stock;
      -> RETURN totalstock;
      -> END $$
  Query OK, 0 rows affected (0.00 sec)
  
  mysql> DELIMITER ;

名称为StatisticsStock的函数创建成功。

2．调用函数

在MySQL命令行中调用名称为StatisticsStock的函数。

mysql> SELECT StatisticsStock();
  +-------------------+
  | StatisticsStock() |
  +-------------------+
  |              9130 |
  +-------------------+
  1 row in set (0.00 sec)

可以看到，名称为StatisticsStock的函数使用游标正确地统计出了t_goods数据表中的商品库存信息，并返回商品库存总量。

16.13　MySQL中控制流程的使用

MySQL数据库支持使用IF语句、CASE语句、LOOP语句、LEAVE语句、ITERATE语句、REPEAT语句和WHILE语句进行流程的控制。本节简单介绍如何使用这些语句进行流程控制。


16.13.1　使用IF语句控制流程

1．语法说明

IF语句能够根据条件判断的结果为TRUE或者FALSE来执行相应的逻辑。IF语句的语法格式如下：

IF search_condition THEN statement_list
      [ELSEIF search_condition THEN statement_list] ...
      [ELSE statement_list]
  END IF

如果相应的search_condition条件为TRUE，则对应的statement_list语句将被执行；否则执行ELSE语句对应的statement_list语句。

2．简单示例

（1）创建一个名称为CompareNumber的存储过程，在存储过程中定义一个INT类型的变量x，并为变量x赋值100。接下来，使用IF语句对x的值进行判断，如果x的值小于100，则输出“x < 100”；如果x的值等于100，则输出“x = 100”；如果x的值大于100，则输出“x > 100”。

创建存储过程，在MySQL命令行中执行代码。

mysql> DELIMITER $$ 
  mysql> CREATE PROCEDURE CompareNumber()
      -> BEGIN
      -> DECLARE x INT DEFAULT 0;
      -> SET x = 100;
      -> IF x < 100 THEN
      -> SELECT 'x < 100';
      -> ELSEIF x = 100 THEN
      -> SELECT 'x = 100';
      -> ELSE 
      -> SELECT 'x > 100';
      -> END IF;
      -> END $$
  Query OK, 0 rows affected (0.00 sec)
  mysql> DELIMITER ;

存储过程创建成功。

（2）在MySQL命令行中调用名称为CompareNumber的存储过程。

mysql> CALL CompareNumber();
  +---------+
  | x = 100 |
  +---------+
  | x = 100 |
  +---------+
  1 row in set (0.00 sec)
  Query OK, 0 rows affected (0.00 sec)

在名称为CompareNumber的存储过程中，为变量x赋值100，根据IF语句的判断逻辑，输出了“x = 100”的信息。


16.13.2　使用CASE语句控制流程

1．语法说明

CASE语句有两种语法格式。

（1）语法格式1。

CASE case_value
      WHEN when_value THEN statement_list
      [WHEN when_value THEN statement_list] ...
      [ELSE statement_list]
  END CASE

其中，case_value表示条件表达式，根据case_value的值，执行相应的WHEN语句。when_value为case_value可能的值，如果某个when_value的值与case_value的值相同，则会执行当前when_value对应的THEN后面的statement_list语句；如果没有when_value的值与case_value的值相同，则执行ELSE语句对应的statement_list语句。

（2）语法格式2。

CASE
      WHEN search_condition THEN statement_list
      [WHEN search_condition THEN statement_list] ...
      [ELSE statement_list]
  END CASE

其中，search_condition为条件判断语句，当某个search_condition语句为TRUE时，执行对应的THEN后面的statement_list语句；如果search_condition语句都为FALSE，则执行ELSE对应的statement_list语句。

2．简单示例

（1）创建名称为CompareNumberWithCaseValue的存储过程，使用CASE语句语法格式1，对变量x的值进行判断，并输出相应的信息。创建存储过程，在MySQL命令行中执行代码。

mysql> DELIMITER $$
  mysql> CREATE PROCEDURE CompareNumberWithCaseValue()
      -> BEGIN
      -> DECLARE x INT DEFAULT 0;
      -> SET x = 100;
      -> CASE x
      -> WHEN 0 THEN SELECT 'x = 0';
      -> WHEN 100 THEN SELECT 'x = 100';
      -> ELSE SELECT 'x <> 0 and x <> 100';
      -> END CASE;
      -> END $$
  Query OK, 0 rows affected (0.00 sec)
  mysql> DELIMITER ;

名称为CompareNumberWithCaseValue的存储过程创建成功。

接下来，在MySQL命令行中调用名称为CompareNumberWithCaseValue的存储过程。

mysql> CALL CompareNumberWithCaseValue();
  +---------+
  | x = 100 |
  +---------+
  | x = 100 |
  +---------+
  1 row in set (0.00 sec)
  Query OK, 0 rows affected (0.00 sec)

在存储过程CompareNumberWithCaseValue中，将变量x设置为100，根据CASE语句条件的判断，输出了“x = 100”的信息。

（2）创建名称为CompareNumberWithCase的存储过程，使用CASE语句语法格式2，对变量x的值进行判断并输出相应的信息。创建存储过程，在MySQL命令行中执行代码。

mysql> DELIMITER $$
  mysql> CREATE PROCEDURE CompareNumberWithCase()
      -> BEGIN
      -> DECLARE x INT DEFAULT 0;
      -> SET x = 100;
      -> CASE
      -> WHEN x < 100 THEN SELECT 'x < 100';
      -> WHEN x = 100 THEN SELECT 'x = 100';
      -> WHEN x > 100 THEN SELECT 'x > 100';
      -> ELSE SELECT 'x NOT FOUND';
      -> END CASE;
      -> END $$
  Query OK, 0 rows affected (0.00 sec)
  mysql> DELIMITER ;

名称为CompareNumberWithCase的存储过程创建成功。

在MySQL命令行中调用名称为CompareNumberWithCase的存储过程。

mysql> CALL CompareNumberWithCase();
  +---------+
  | x = 100 |
  +---------+
  | x = 100 |
  +---------+
  1 row in set (0.00 sec)
  Query OK, 0 rows affected (0.00 sec)

名称为CompareNumberWithCase的存储过程同样输出了“x = 100”的信息。


16.13.3　使用LOOP语句控制流程

LOOP语句能够循环执行某些语句，而不进行条件判断，可以使用LEAVE语句退出LOOP循环。

1．语法说明

LOOP语句的语法格式如下：

 [begin_label:] LOOP
      statement_list
  END LOOP [end_label]

其中，begin_label和end_label都是LOOP语句的标注名称，该参数可以省略。如果begin_label和end_label两者都出现，则它们必须是相同的。

2．简单示例

（1）创建名称为HandlerDataWithLoop的存储过程，在存储过程中定义INT类型的变量x，默认值为0，使用LOOP语句为x变量循环加1，当x变量的值大于等于100时，退出LOOP循环，最后输出x的值。创建存储过程，在MySQL命令行中执行代码。

mysql> DELIMITER $$
  mysql> CREATE PROCEDURE HandlerDataWithLoop()
      -> BEGIN
      -> DECLARE x INT DEFAULT 0;
      -> x_loop: LOOP
      -> SET x = x + 1;
      -> IF x >= 100 THEN 
      -> LEAVE x_loop;
      -> END IF;
      -> END LOOP x_loop;
      -> SELECT x;
      -> END $$
  Query OK, 0 rows affected (0.01 sec)
  mysql> DELIMITER ;

名称为HandlerDataWithLoop的存储过程创建成功。

（2）在MySQL命令行中调用名称为HandlerDataWithLoop的存储过程。

mysql> CALL HandlerDataWithLoop();
  +------+
  | x    |
  +------+
  |  100 |
  +------+
  1 row in set (0.00 sec)
  
  Query OK, 0 rows affected (0.00 sec)

存储过程正确地输出了x变量的值。


16.13.4　使用LEAVE语句控制流程

1．语法说明

LEAVE语句能够从被标注的流程结果中退出，语法结构如下：

LEAVE label

其中，label表示被标注的流程标志。

2．简单示例

参见16.13.3节中的简单示例，这里不再赘述。


16.13.5　使用ITERATE语句控制流程

1．语法说明

ITERATE语句表示跳过本次循环，而执行下次循环操作。语法格式如下：

ITERATE label

其中，label表示被标注的流程标志。

注意：ITERATE只可以出现在LOOP、REPEAT和WHILE语句内。

2．简单示例

（1）创建名称为HandlerDataWithIterate的存储过程，在存储过程中定义INT类型的变量x，默认值为0。接下来，在LOOP循环中为变量x加1，当x的值小于5时，执行ITERATE操作；当x的值大于等于10时，退出LOOP循环；其他情况，打印x变量的值。创建存储过程，在MySQL命令行中执行代码。

mysql> DELIMITER $$
  mysql> CREATE PROCEDURE HandlerDataWithIterate()
      -> BEGIN
      -> DECLARE x INT DEFAULT 0;
      -> x_loop: LOOP
      -> SET x = x + 1;
      -> IF x < 5 THEN ITERATE x_loop;
      -> ELSEIF x >= 10 THEN LEAVE x_loop;
      -> END IF;
      -> SELECT x;
      -> END LOOP x_loop;
      -> END $$
  Query OK, 0 rows affected (0.00 sec)
  mysql> DELIMITER ;

（2）在MySQL命令行中调用名称为HandlerDataWithIterate的存储过程。

mysql> CALL HandlerDataWithIterate();
  +------+
  | x    |
  +------+
  |    5 |
  +------+
  1 row in set (0.00 sec)
  
  +------+
  | x    |
  +------+
  |    6 |
  +------+
  1 row in set (0.00 sec)
  
  +------+
  | x    |
  +------+
  |    7 |
  +------+
  1 row in set (0.00 sec)
  
  +------+
  | x    |
  +------+
  |    8 |
  +------+
  1 row in set (0.00 sec)
  
  +------+
  | x    |
  +------+
  |    9 |
  +------+
  1 row in set (0.00 sec)
  Query OK, 0 rows affected (0.00 sec)

当x变量的值大于等于5且小于10时，输出了x变量的值。


16.13.6　使用REPEAT语句控制流程

1．语法说明

REPEAT语句会创建一个带有条件判断的循环语句，每次执行循环体时，都会对条件进行判断，如果条件判断为TRUE，则退出循环，否则继续执行循环体，语法格式如下：

[begin_label:] REPEAT
      statement_list
  UNTIL search_condition
  END REPEAT [end_label]

其中，begin_label和end_label为循环的标志，二者可以省略，如果二者同时出现，则必须相同。当search_condition条件判断为TRUE时，退出循环。

2．简单示例

（1）创建名称为HandlerDataWithRepeat的存储过程，在存储过程中定义INT类型的变量x，默认值为0。在REPEAT循环中为x变量加1，如果x变量的值大于等于10，则退出REPEAT循环，最后打印x变量的值。创建存储过程，在MySQL命令行中执行代码。

mysql> DELIMITER $$
  mysql> CREATE PROCEDURE HandlerDataWithRepeat()
      -> BEGIN
      -> DECLARE x INT DEFAULT 0;
      -> x_repeat: REPEAT
      -> SET x = x + 1;
      -> UNTIL x >= 10
      -> END REPEAT x_repeat;
      -> SELECT x;
      -> END $$
  Query OK, 0 rows affected (0.00 sec)
  mysql> DELIMITER ;

（2）在MySQL命令行中调用名称为HandlerDataWithRepeat的存储过程。

mysql> CALL HandlerDataWithRepeat();
  +------+
  | x    |
  +------+
  |   10 |
  +------+
  1 row in set (0.00 sec)
  Query OK, 0 rows affected (0.00 sec)

存储过程通过REPEAT循环处理，正确地输出了x变量的值。


16.13.7　使用WHILE语句控制流程

1．语法说明

WHILE语句同样可以创建一个带有条件判断的循环语句。与REPEAT语句不同，WHILE语句的条件判断为TRUE时，继续执行循环体。语法格式如下：

[begin_label:] WHILE search_condition DO
      statement_list
  END WHILE [end_label]

其中，begin_label和end_label为循环的标志，二者可以省略，如果二者同时出现，则必须相同。当search_condition条件判断为TRUE时，继续执行循环体。

2．简单示例

（1）创建一个名称为HandlerDataWithWhile的存储过程，在存储过程中定义一个INT类型的变量x，默认值为0。当x的值小于10时，使用WHILE循环对x变量的值加1，最后打印x变量的值。创建存储过程，在MySQL命令行中执行代码。

mysql> DELIMITER $$
  mysql> CREATE PROCEDURE HandlerDataWithWhile()
      -> BEGIN
      -> DECLARE x INT DEFAULT 0;
      -> x_while: WHILE x < 10 DO
      -> SET x = x + 1;
      -> END WHILE x_while;
      -> SELECT x;
      -> END $$
  Query OK, 0 rows affected (0.00 sec)
  mysql> DELIMITER ;

名称为HandlerDataWithWhile的存储过程创建成功。

（2）在MySQL命令行中调用名称为HandlerDataWithWhile的存储过程。

mysql> CALL HandlerDataWithWhile();
  +------+
  | x    |
  +------+
  |   10 |
  +------+
  1 row in set (0.00 sec)
  Query OK, 0 rows affected (0.00 sec)

在存储过程中通过WHILE循环对x变量的值进行处理，并正确输出了x变量的值。

【版权声明】本文为华为云社区用户原创内容，转载时必须标注文章的来源（华为云社区）、文章链接、文章作者等基本信息， 否则作者和本社区有权追究责任。如果您发现本社区中有涉嫌抄袭的内容，欢迎发送邮件进行举报，并提供相关证据，一经查实，本社区将立刻删除涉嫌侵权内容，举报邮箱： cloudbbs@huaweicloud.com
SQL 数据库
  • 点赞
  • 收藏
  • 关注作者

评论（0）

0/1000
抱歉，系统识别当前为高风险访问，暂不支持该操作

全部回复

上滑加载中

设置昵称

在此一键设置昵称，即可参与社区互动！

*长度不超过10个汉字或20个英文字符，设置后3个月内不可修改。

*长度不超过10个汉字或20个英文字符，设置后3个月内不可修改。

确认 取消

加入云驻计划，成为创作者

  • 华为云周边好礼
  • 免费体验产品
  • 特殊身份标识
  • 线下官方门票
  • 内部专家零距离
  • 与10000+优质创作者共同成长
立即加入