The INFORMATION_SCHEMA ROUTINES dictionary view provides information about stored routines (stored procedures and stored functions), but does not include built-in (local) functions or loadable functions. The INFORMATION_SCHEMA ROUTINES dictionary view contains the following columns.
Field |
Description |
|---|---|
| SPECIFIC_NAME | The name of the stored routine. |
| ROUTINE_CATALOG | The name of the directory to which the stored routine belongs. This value is alwaysdef. This field is not currently used. |
| ROUTINE_SCHEMA | The name of the schema (database) to which the stored routine belongs. |
| ROUTINE_NAME | The name of the stored routine, which corresponds to theSPECIFIC_NAMEThe same. |
| ROUTINE_TYPE | Type of the stored routine. Stored procedure isPROCEDURE, the stored function isFUNCTION. |
| DATA_TYPE | If the routine is a stored function, the return value is a data type; if the routine is a stored procedure, this value is empty.DATA_TYPEThe value is only the type name and does not contain other information.DTD_IDENTIFIERThe value contains the type name and other information, such as precision or length. This field is currently not used. |
| CHARACTER_MAXIMUM_LENGTH | The maximum length of the string return value of a stored function, in characters. For a stored procedure, this value isNULL. This field is currently not used. |
| CHARACTER_OCTET_LENGTH | The maximum length of a string return value of a stored function, in bytes. If the routine is a stored procedure, this value isNULL. This field is currently unused. |
| NUMERIC_PRECISION | The numeric range of the stored function's return value. For a stored procedure, this value isNULL. This field is currently unused. |
| NUMERIC_SCALE | The numeric precision of the stored function's return value. For a stored procedure, this value isNULL. This field is not currently used. |
| DATETIME_PRECISION | The number of seconds in the fractional part of the time returned by the stored function. For a stored procedure, this value isNULL. This field is currently not used. |
| CHARACTER_SET_NAME | The character set name of the string return value of the stored function. If the routine is a stored procedure, this value isNULL. This field is not currently used. |
| COLLATION_NAME | The name of the collation for sorting the string return value of the stored function. If the routine is a stored procedure, this value isNULL. This field is currently not used. |
| DTD_IDENTIFIER | If the routine is a stored function, the return value is a data type. If the routine is a stored procedure, this value is empty.DATA_TYPEThe value is only the type name and contains no other information.DTD_IDENTIFIERThe value contains the type name and other information, such as precision or length. |
| ROUTINE_BODY | The language used for routine definition. This value is always SQL. |
| ROUTINE_DEFINITION | The text of the SQL statement executed by the routine. |
| EXTERNAL_NAME | This value is alwaysNULL. |
| EXTERNAL_LANGUAGE | Language of the stored routine. This field is currently not used. |
| PARAMETER_STYLE | This value is always SQL. |
| IS_DETERMINISTIC | Valid values:YESorNO, depending on whether the storage routine usesDETERMINISTICThe definition of the feature. This field is currently not used. |
| SQL_DATA_ACCESS | Data access characteristics of the routine. This value can beCONTAINS SQL、NO SQL、READS SQL DATAorMODIFIES SQL DATA. |
| SQL_PATH | This value is alwaysNULL. |
| SECURITY_TYPE | Routine'sSQL SECURITYFeature. This value isDEFINERorINVOKER. This field is currently not used. |
| CREATED | Date and time when the routine was created. This column is of theTIMESTAMP. |
| LAST_ALTERED | The date and time when the routine was last modified. If the procedure has not been modified since its creation, this value is the same as the creation time.CREATEDThe values are the same. |
| SQL_MODE | You can create or modify a routine in an SQL-compatible mode that is valid at the time of creation or modification, and execute the routine in that mode. |
| ROUTINE_COMMENT | Comment text of the routine (if any). Otherwise, this value is empty. |
| DEFINER | InDEFINERThe account specified in the clause (usually the user who created the routine), in the format of'user_name'@'host_name'. |
| CHARACTER_SET_CLIENT | System variables when creating or changing a storage routineCHARACTER_SET_CLIENTThe value of the current session. |
| COLLATION_CONNECTION | System variables when creating or changing a storage routineCOLLATION_CONNECTIONThe value of the current session. |
| DATABASE_COLLATION | System variables when creating or changing a storage routineDATABASE_COLLATIONThe value of the current session. |
Here is an example:
obclient> SELECT * FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_SCHEMA='test' AND ROUTINE_NAME='p1'\G
*************************** 1. row ***************************
SPECIFIC_NAME: p1
ROUTINE_CATALOG: def
ROUTINE_SCHEMA: test
ROUTINE_NAME: p1
ROUTINE_TYPE: PROCEDURE
DATA_TYPE:
CHARACTER_MAXIMUM_LENGTH: NULL
CHARACTER_OCTET_LENGTH: NULL
NUMERIC_PRECISION: NULL
NUMERIC_SCALE: NULL
DATETIME_PRECISION: NULL
CHARACTER_SET_NAME: NULL
COLLATION_NAME: NULL
DTD_IDENTIFIER: NULL
ROUTINE_BODY: SQL
ROUTINE_DEFINITION: BEGIN
# Set value of OUT parameter
SELECT VERSION() INTO ver_param;
# Increment value of INOUT parameter
SET incr_param = incr_param + 1;
END
EXTERNAL_NAME: NULL
EXTERNAL_LANGUAGE: NULL
PARAMETER_STYLE: SQL
IS_DETERMINISTIC: NO
SQL_DATA_ACCESS: CONTAINS_SQL
SQL_PATH: NULL
SECURITY_TYPE: DEFINER
CREATED: 2022-05-18 18:07:51.994639
LAST_ALTERED: 2022-05-26 18:07:51.994639
SQL_MODE: STRICT_ALL_TABLES,NO_ZERO_IN_DATE
ROUTINE_COMMENT:
DEFINER: 'root'@'%'
CHARACTER_SET_CLIENT: utf8mb4
COLLATION_CONNECTION: utf8mb4_general_ci
DATABASE_COLLATION: utf8mb4_general_ci