The INFORMATION_SCHEMA PARAMETERS dictionary view provides information about the parameters of stored routines (stored procedures and stored functions) and about the return values of stored functions, but does not include built-in (local) functions or loadable functions.
The INFORMATION_SCHEMA PARAMETERS view contains the following columns:
Field |
Description |
|---|---|
| SPECIFIC_CATALOG | The name of the directory containing the routine with parameters. This value is alwaysdef. |
| SPECIFIC_SCHEMA | The name of the schema (database) to which the procedure containing the parameter belongs. |
| SPECIFIC_NAME | The name of the routine that contains the parameter. |
| ORDINAL_POSITION | Records the parameter positions of stored procedures or functions,ORDINAL_POSITIONThe value of the parameter can be 1, 2, 3, and so on. For stored functions, an additional row is required to specify the return value. A row describing a return value has the following characteristics:
|
| PARAMETER_MODE | The parameter mode. Valid values:IN、OUTorINOUT. For the return value of a stored function, this value isNULL. |
| PARAMETER_NAME | The name of the parameter. For stored function return values, this value isNULL. |
| DATA_TYPE | The data type of the parameter.DATA_TYPEThe value is only the type name and contains no other information.DTD_IDENTIFIERThe value contains the data type name and other information, such as precision or length. |
| CHARACTER_MAXIMUM_LENGTH | The maximum length of a string parameter, in characters. |
| CHARACTER_OCTET_LENGTH | The maximum length of a string parameter, in bytes. |
| NUMERIC_PRECISION | The numerical precision of parameters of numeric types. |
| NUMERIC_SCALE | The numerical range of a numeric parameter. |
| DATETIME_PRECISION | The date precision of a date-type parameter. |
| CHARACTER_SET_NAME | The character set name for string-type parameters. |
| COLLATION_NAME | The collation for character sorting of string parameters. |
| DTD_IDENTIFIER | Records detailed information about data types.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. |
| ROUTINE_TYPE | Type of the stored routine. Stored procedure isPROCEDURE, the stored function isFUNCTION. |
An example is as follows:
obclient> SELECT * FROM INFORMATION_SCHEMA.PARAMETERS
WHERE SPECIFIC_SCHEMA='test' AND PARAMETER_NAME='c1'\G
*************************** 1. row ***************************
SPECIFIC_CATALOG: def
SPECIFIC_SCHEMA: test
SPECIFIC_NAME: my_func
ORDINAL_POSITION: 1
PARAMETER_MODE: IN
PARAMETER_NAME: c1
DATA_TYPE: char
CHARACTER_MAXIMUM_LENGTH: 20
CHARACTER_OCTET_LENGTH: 80
NUMERIC_PRECISION: NULL
NUMERIC_SCALE: NULL
DATETIME_PRECISION: NULL
CHARACTER_SET_NAME: utf8mb4
COLLATION_NAME: utf8mb4_general_ci
DTD_IDENTIFIER: char(20)
ROUTINE_TYPE: FUNCTION