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 not about built-in (native) functions or loadable functions.
The INFORMATION_SCHEMA PARAMETERS dictionary view contains the following fields, as shown in the table below.
| Field | Description |
|---|---|
| SPECIFIC_CATALOG | The name of the catalog to which the routine that contains the parameter belongs. This value is always def. |
| SPECIFIC_SCHEMA | The name of the schema (database) to which the routine that contains the parameter belongs. |
| SPECIFIC_NAME | The name of the routine that contains the parameter. |
| ORDINAL_POSITION | The position of the parameter in the stored procedure or function. The value of ORDINAL_POSITION is 1, 2, 3, and so on. For a stored function, there is also a row for the function return value. The row that describes the return value has the following characteristics:
|
| PARAMETER_MODE | The mode of the parameter. This value can be IN, OUT, or INOUT. For a stored function return value, this value is NULL. |
| PARAMETER_NAME | The name of the parameter. For a stored function return value, this value is NULL. |
| DATA_TYPE | The data type of the parameter. The DATA_TYPE value is only the type name and does not contain other information. The DTD_IDENTIFIER value contains the data type name and other information, such as precision or length. |
| CHARACTER_MAXIMUM_LENGTH | The maximum length (in characters) of a string-type parameter. |
| CHARACTER_OCTET_LENGTH | The maximum length (in bytes) of a string-type parameter. |
| NUMERIC_PRECISION | The numeric precision of a numeric-type parameter. |
| NUMERIC_SCALE | The numeric scale of a numeric-type parameter. |
| DATETIME_PRECISION | The date precision of a date-type parameter. |
| CHARACTER_SET_NAME | The character set name of a string-type parameter. |
| COLLATION_NAME | The collation of a string-type parameter. |
| DTD_IDENTIFIER | The details of the data type. The DATA_TYPE value is only the type name and does not contain other information. The DTD_IDENTIFIER value contains the type name and other information, such as precision or length. |
| ROUTINE_TYPE | The type of the stored routine. The value is PROCEDURE for a stored procedure and FUNCTION for a stored 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