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

【图书推荐】《MySQL 9从入门到性能优化（视频教学版）》-CSDN博客

《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_dbName: CountProc
Type: PROCEDUREDefiner: root@localhostModified: 2018-11-21 13:52:28Created: 2018-11-21 13:52:28Security_type: DEFINERComment:
character_set_client: gbk
collation_connection: gbk_chinese_ciDatabase 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: CountProc2sql_mode: STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTIONCreate 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_ciDatabase 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: CountProc2ROUTINE_CATALOG: defROUTINE_SCHEMA: test_dbROUTINE_NAME: CountProc2ROUTINE_TYPE: FUNCTIONDATA_TYPE: int
CHARACTER_MAXIMUM_LENGTH: NULLCHARACTER_OCTET_LENGTH: NULLNUMERIC_PRECISION: 10NUMERIC_SCALE: 0DATETIME_PRECISION: NULLCHARACTER_SET_NAME: NULLCOLLATION_NAME: NULLDTD_IDENTIFIER: int(11)ROUTINE_BODY: SQLROUTINE_DEFINITION: BEGIN
RETURN (SELECT COUNT(*) FROM fruits WHERE s_id = sid);
ENDEXTERNAL_NAME: NULLEXTERNAL_LANGUAGE: SQLPARAMETER_STYLE: SQLIS_DETERMINISTIC: NOSQL_DATA_ACCESS: CONTAINS SQLSQL_PATH: NULLSECURITY_TYPE: DEFINERCREATED: 2018-11-21 16:57:09LAST_ALTERED: 2018-11-21 16:57:09SQL_MODE: STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTIONROUTINE_COMMENT:DEFINER: root@localhostCHARACTER_SET_CLIENT: gbkCOLLATION_CONNECTION: gbk_chinese_ciDATABASE_COLLATION: utf8mb4_0900_ai_ci

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