The INFORMATION_SCHEMA PARAMETERS dictionary view provides parameters of stored routines such as stored procedures and stored functions and return values of stored functions, but not built-in (local) functions or loadable functions.
The following table describes the fields in the INFORMATION_SCHEMA PARAMETERS dictionary view.
Field |
Description |
|---|---|
| SPECIFIC_CATALOG | The name of the catalog to which the routine of the parameter belongs. The value of this field is always def. |
| SPECIFIC_SCHEMA | The name of the schema (or database) to which the routine of the parameter belongs. |
| SPECIFIC_NAME | The name of the routine that contains the parameter |
| ORDINAL_POSITION | The position of the parameter of the stored procedure or function. Valid values include 1, 2, and 3. For a stored function, a row is required for the return value of the function. The row that describes the return value has the following characteristics:
|
| PARAMETER_MODE | The parameter mode. The value can be IN, OUT, or INOUT. For the return value of a stored function, the value is NULL. |
| PARAMETER_NAME | The name of the parameter. For the return value of a stored function, the value is NULL. |
| DATA_TYPE | The data type of the parameter. The value of DATA_TYPE contains only the type name, without other information. The value of DTD_IDENTIFIER contains the data type name and other information, such as the 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 numeric precision of a numeric parameter. |
| NUMERIC_SCALE | The numeric scale of a numeric parameter. |
| DATETIME_PRECISION | The datetime precision of a temporal parameter. |
| CHARACTER_SET_NAME | The character set name for a string parameter. |
| COLLATION_NAME | The character collation of a string parameter. |
| DTD_IDENTIFIER | The detailed information about the recorded data type. The value of DATA_TYPE contains only the type name, without other information. The value of DTD_IDENTIFIER contains the data type name and other information, such as the precision or length. |
| ROUTINE_TYPE | The type of the stored routine. For a stored procedure, the value is PROCEDURE. For a storage function, the value is FUNCTION. |
Here is an 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