The INFORMATION_SCHEMA PARAMETERS dictionary view provides information about the parameters of stored routines (stored procedures and stored functions) and 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 always def. |
| SPECIFIC_SCHEMA | The name of the schema (database) to which the routine 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_POSITION The 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. The row describing the return value has the following characteristics:
|
| PARAMETER_MODE | The parameter mode. Valid values: IN, OUT, or INOUT. For the return value of a stored function, this value is NULL. |
| PARAMETER_NAME | The name of the parameter. For stored function return values, this value is NULL. |
| DATA_TYPE | The data type of the parameter. DATA_TYPE The value is only the type name and does not contain other information. DTD_IDENTIFIER A value consists of a 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 parameter of a numeric type. |
| 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_TYPE The value is only the type name and does not contain other information. DTD_IDENTIFIER The value contains the type name and other information, such as precision or length. |
| ROUTINE_TYPE | Type of the stored routine. Stored procedure is PROCEDURE, the stored function is FUNCTION. |
For example:
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