MySQL查看存储过程和存储函数

原创已于 2025-02-20 08:29:02 修改 · 1.7k 阅读

15 ·

本内容遵循CC 4.0 BY-SA版权协议

GEO检测

收录于

MySQL数据库技术

于 2025-02-14 14:52:02 首次发布

《MySQL 9从入门到性能优化（视频教学版）（数据库技术丛书）》(王英英)【摘要书评试读】- 京东图书 (jd.com)

MySQL9数据库技术_夏天又到了的博客-CSDN博客

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

8.3.1 使用SHOW STATUS语句查看存储过程和存储函数的状态

SHOW STATUS语句可以查看存储过程和存储函数的状态，其基本语法结构如下：

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

这个语句是一个MySQL的扩展，返回子程序的特征，如数据库、名字、类型、创建者及创建和修改日期。如果没有指定样式，那么根据使用的语句，所有存储程序或存储函数的信息都会被列出。其中，PROCEDURE和FUNCTION分别表示查看存储过程和存储函数；LIKE语句表示匹配存储过程或存储函数的名称。

【例8.24】SHOW STATUS语句示例如下：

mysql> SHOW PROCEDURE STATUS LIKE 'C%'\G
*** 1. row ***
                  Db: test_db
                Name: CountProc
Type: PROCEDURE
             Definer: root@localhost
            Modified: 2018-11-21 13:52:28
             Created: 2018-11-21 13:52:28
       Security_type: DEFINER
             Comment:
character_set_client: gbk
collation_connection: gbk_chinese_ci
  Database Collation: utf8mb4_0900_ai_ci

“SHOW PROCEDURE STATUS LIKE 'C%'\G”语句获取数据库中所有名称以字母“C”开头的存储过程的信息。通过上面的语句可以看到：这个存储过程所在的数据库为test_db、名称为CountProc等一些相关信息。

8.3.2 使用SHOW CREATE语句查看存储过程和存储函数的定义

除了SHOW STATUS之外，MySQL还可以使用SHOW CREATE语句查看存储过程和存储函数的状态。语法格式如下：

SHOW CREATE {PROCEDURE | FUNCTION} sp_name

这个语句是一个MySQL的扩展，类似于SHOW CREATE TABLE，它返回一个可用来重新创建已命名子程序的确切字符串。PROCEDURE和FUNCTION分别表示查看存储过程和存储函数；sp_name参数表示匹配存储过程或存储函数的名称。

【例8.25】SHOW CREATE语句示例如下：

mysql> SHOW CREATE FUNCTION test_db.CountProc2 \G
*************************** 1. row ***************************
            Function: CountProc2
            sql_mode: STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION
     Create Function: CREATE DEFINER=`root`@`localhost` FUNCTION `CountProc2`(sid INT) RETURNS int(11)
BEGIN
RETURN (SELECT COUNT(*) FROM fruits WHERE s_id = sid);
END
character_set_client: gbk
collation_connection: gbk_chinese_ci
  Database Collation: utf8mb4_0900_ai_ci

通过上面的语句可以看到：存储函数的名称为CountProc2、sql_mode为sql，以及数据库设置的一些信息。

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

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

SELECT * FROM information_schema.Routines 
WHERE ROUTINE_NAME=' sp_name ' ;

其中，ROUTINE_NAME字段中存储的是存储过程和存储函数的名称；sp_name参数表示存储过程或存储函数的名称。

【例8.26】从Routines表中查询名称为CountProc2的存储函数的信息，SQL语句如下：

SELECT * FROM information_schema.Routines WHERE ROUTINE_NAME='CountProc2'  AND  ROUTINE_TYPE = 'FUNCTION' \G

语句执行如下：

mysql> SELECT * FROM information_schema.Routines WHERE ROUTINE_NAME='CountProc2'  AND  ROUTINE_TYPE = 'FUNCTION' \G
*************************** 1. row ***************************
           SPECIFIC_NAME: CountProc2
         ROUTINE_CATALOG: def
          ROUTINE_SCHEMA: test_db
            ROUTINE_NAME: CountProc2
            ROUTINE_TYPE: FUNCTION
               DATA_TYPE: int
CHARACTER_MAXIMUM_LENGTH: NULL
  CHARACTER_OCTET_LENGTH: NULL
       NUMERIC_PRECISION: 10
           NUMERIC_SCALE: 0
      DATETIME_PRECISION: NULL
      CHARACTER_SET_NAME: NULL
          COLLATION_NAME: NULL
          DTD_IDENTIFIER: int(11)
            ROUTINE_BODY: SQL
      ROUTINE_DEFINITION: BEGIN
RETURN (SELECT COUNT(*) FROM fruits WHERE s_id = sid);
END
           EXTERNAL_NAME: NULL
       EXTERNAL_LANGUAGE: SQL
         PARAMETER_STYLE: SQL
        IS_DETERMINISTIC: NO
         SQL_DATA_ACCESS: CONTAINS SQL
                SQL_PATH: NULL
           SECURITY_TYPE: DEFINER
                 CREATED: 2018-11-21 16:57:09
            LAST_ALTERED: 2018-11-21 16:57:09
                SQL_MODE: STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION
         ROUTINE_COMMENT:
                 DEFINER: root@localhost
    CHARACTER_SET_CLIENT: gbk
    COLLATION_CONNECTION: gbk_chinese_ci
      DATABASE_COLLATION: utf8mb4_0900_ai_ci

在information_schema数据库下的Routines表中，存储了所有存储过程和存储函数的定义。使用SELECT语句查询Routines表中的存储过程和存储函数的定义时，一定要使用ROUTINE_NAME字段指定存储过程或存储函数的名称；否则，将查询出所有的存储过程或存储函数的定义。当存储过程和存储函数名称相同时，就需要同时指定ROUTINE_TYPE字段表明查询的是哪种类型的存储程序。