Movatterモバイル変換

SHOW CREATE PROCEDUREproc_name

This statement is a MySQL extension. It returns the exact string that can be used to re-create the named stored procedure. A similar statement,SHOW CREATE FUNCTION, displays information about stored functions (seeSection 15.7.7.9, “SHOW CREATE FUNCTION Statement”).

To use either statement, you must be the user named as the routineDEFINER, have theSHOW_ROUTINE privilege, have theSELECT privilege at the global level, or have theCREATE ROUTINE,ALTER ROUTINE, orEXECUTE privilege granted at a scope that includes the routine. The value displayed for theCreate Procedure orCreate Function field isNULL if you have onlyCREATE ROUTINE,ALTER ROUTINE, orEXECUTE.

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_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;                      ENDcharacter_set_client: utf8mb4collation_connection: utf8mb4_0900_ai_ci  Database Collation: utf8mb4_0900_ai_cimysql> 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_ENGINE_SUBSTITUTION     Create Function: CREATE DEFINER=`me`@`localhost`                      FUNCTION `hello`(s CHAR(20))                      RETURNS char(50) CHARSET utf8mb4                      DETERMINISTIC                      RETURN CONCAT('Hello, ',s,'!')character_set_client: utf8mb4collation_connection: utf8mb4_0900_ai_ci  Database Collation: utf8mb4_0900_ai_ci

character_set_client is the session value of thecharacter_set_client system variable when the routine was created.collation_connection is the session value of thecollation_connection system variable when the routine was created.Database Collation is the collation of the database with which the routine is associated.