5.1 查看

创建完之后，怎么知道我们创建的存储过程、存储函数是否成功了呢？

MySQL存储了存储过程和函数的状态信息，用户可以使用SHOW STATUS语句或SHOW CREATE语句来查看，也可直接从系统的information_schema数据库中查询。这里介绍3种方法。

1. 使用SHOW CREATE语句查看存储过程和函数的创建信息

基本语法结构如下：

  1. SHOW CREATE {PROCEDURE | FUNCTION} 存储过程名或函数名

举例：

  1. SHOW CREATE FUNCTION test_db.CountProc \G

2. 使用SHOW STATUS语句查看存储过程和函数的状态信息

基本语法结构如下：

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

这个语句返回子程序的特征，如数据库、名字、类型、创建者及创建和修改日期。

[LIKE ‘pattern’]：匹配存储过程或函数的名称，可以省略。当省略不写时，会列出MySQL数据库中存在的所有存储过程或函数的信息。
举例：SHOW STATUS语句示例，代码如下：

  1. mysql> SHOW PROCEDURE STATUS LIKE 'SELECT%' \G 
  2. *************************** 1. row ***************************
  3.                   Db: test_db
  4.                 Name: SelectAllData
  5.                 Type: PROCEDURE
  6.              Definer: root@localhost
  7.             Modified: 2021-10-16 15:55:07
  8.              Created: 2021-10-16 15:55:07
  9.        Security_type: DEFINER
  10.              Comment: 
  11. character_set_client: utf8mb4
  12. collation_connection: utf8mb4_general_ci
  13.   Database Collation: utf8mb4_general_ci
  14. 1 row in set (0.00 sec)

3. 从information_schema.Routines表中查看存储过程和函数的信息

MySQL中存储过程和函数的信息存储在information_schema数据库下的Routines表中。可以通过查询该表的记录来查询存储过程和函数的信息。其基本语法形式如下：

  1. SELECT * FROM information_schema.Routines
  2. WHERE ROUTINE_NAME='存储过程或函数的名' [AND ROUTINE_TYPE = {'PROCEDURE|FUNCTION'}];

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

举例：从Routines表中查询名称为CountProc的存储函数的信息，代码如下：

  1. SELECT * FROM information_schema.Routines
  2. WHERE ROUTINE_NAME='count_by_id'　AND　ROUTINE_TYPE = 'FUNCTION' \G

5.2 修改

修改存储过程或函数，不影响存储过程或函数功能，只是修改相关特性。使用ALTER语句实现。

  1. ALTER {PROCEDURE | FUNCTION} 存储过程或函数的名 [characteristic ...]

其中，characteristic指定存储过程或函数的特性，其取值信息与创建存储过程、函数时的取值信息略有不同。

  1. { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
  2. | SQL SECURITY { DEFINER | INVOKER }
  3. | COMMENT 'string'
  • CONTAINS SQL，表示子程序包含SQL语句，但不包含读或写数据的语句。
  • NO SQL，表示子程序中不包含SQL语句。
  • READS SQL DATA，表示子程序中包含读数据的语句。
  • MODIFIES SQL DATA，表示子程序中包含写数据的语句。
  • SQL SECURITY { DEFINER | INVOKER }，指明谁有权限来执行。
    • DEFINER，表示只有定义者自己才能够执行。
    • INVOKER，表示调用者可以执行。
  • COMMENT 'string'，表示注释信息。

修改存储过程使用ALTER PROCEDURE语句，修改存储函数使用ALTER FUNCTION语句。但是，这两个语句的结构是一样的，语句中的所有参数也是一样的。

举例1：

修改存储过程CountProc的定义。将读写权限改为MODIFIES SQL DATA，并指明调用者可以执行，代码如下：

  1. ALTER　PROCEDURE　CountProc
  2. MODIFIES SQL DATA
  3. SQL SECURITY INVOKER ;

查询修改后的信息：

  1. SELECT specific_name,sql_data_access,security_type
  2. FROM information_schema.`ROUTINES`
  3. WHERE routine_name = 'CountProc' AND routine_type = 'PROCEDURE';

结果显示，存储过程修改成功。从查询的结果可以看出，访问数据的权限（SQLDATA ACCESS）已经变成MODIFIES SQL DATA，安全类型（SECURITY_TYPE）已经变成INVOKER。

举例2：

修改存储函数CountProc的定义。将读写权限改为READS SQL DATA，并加上注释信息“FIND NAME”，代码如下：

  1. ALTER　FUNCTION　CountProc
  2. READS SQL DATA
  3. COMMENT 'FIND NAME' ;

存储函数修改成功。从查询的结果可以看出，访问数据的权限（SQL_DATA_ACCESS）已经变成READS SQL DATA，函数注释（ROUTINE_COMMENT）已经变成FIND NAME。

5.3 删除

删除存储过程和函数，可以使用DROP语句，其语法结构如下：

  1. DROP {PROCEDURE | FUNCTION} [IF EXISTS] 存储过程或函数的名

IF EXISTS：如果程序或函数不存储，它可以防止发生错误，产生一个用SHOW WARNINGS查看的警告。

举例：

  1. DROP PROCEDURE CountProc;
  1. DROP FUNCTION CountProc;