13.7.5.9 SHOW CREATE PROCEDURE 语句

SHOW CREATE PROCEDURE proc_name

该语句是 MySQL 的扩展。它返回可用于重新创建命名存储过程的确切字符串。类似的语句显示创建功能显示有关存储函数的信息(请参阅第 13.7.5.8 节“ SHOW CREATE FUNCTION 语句”)。

要使用这两个语句，您必须是例程DEFINER子句中命名的用户，或者具有对mysql.proctable 的SELECT访问权限。如果您没有例程本身的特权，则Create Procedure或Create Function字段显示的值为NULL。

mysql> SHOW CREATE PROCEDURE test.citycount\G
*************************** 1. row ***************************
           Procedure: citycount
            sql_mode: ONLY_FULL_GROUP_BY,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=`me`@`localhost`
                      PROCEDURE `citycount`(IN country CHAR(3), OUT cities INT)
                      BEGIN
                        SELECT COUNT(*) INTO cities FROM world.city
                        WHERE CountryCode = country;
                      END
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: latin1_swedish_ci

mysql> SHOW CREATE FUNCTION test.hello\G
*************************** 1. row ***************************
            Function: hello
            sql_mode: ONLY_FULL_GROUP_BY,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=`me`@`localhost`
                      FUNCTION `hello`(s CHAR(20))
                      RETURNS char(50) CHARSET latin1
                      DETERMINISTIC
                      RETURN CONCAT('Hello, ',s,'!')
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: latin1_swedish_ci

character_set_client是创建例程时character_set_client系统变量的会话值。 collation_connection是创建例程时collation_connection系统变量的会话值。 Database Collation是与例程相关联的数据库的整理。