Note
This view is introduced since OceanBase Database V2.2.77.
Purpose
This view displays information about routines in the current tenant.
Columns
| Column | Type | Nullable? | Description |
|---|---|---|---|
| SPECIFIC_NAME | varchar(64) | NO | The name of the routine. |
| ROUTINE_CATALOG | varchar(512) | NO | The name of the catalog to which the routine belongs. At present, this column is not used, and the current value is def. |
| ROUTINE_SCHEMA | varchar(64) | NO | The name of the schema to which the routine belongs. |
| ROUTINE_NAME | varchar(64) | NO | The name of the routine. The value is the same as that of SPECIFIC_NAME. |
| ROUTINE_TYPE | varchar(9) | NO | The type of the routine. Valid values:
|
| DATA_TYPE | varchar(64) | NO | The data type of the return value of the routine if the routine is a stored function. If the routine is a stored procedure, this column is empty. At present, this column is not used. |
| CHARACTER_MAXIMUM_LENGTH | bigint(20) | NO | The maximum character length for string return values of the routine if the routine is a stored function. If the routine is a stored procedure, the value of this column is NULL. At present, this column is not used. |
| CHARACTER_OCTET_LENGTH | bigint(20) | NO | The maximum byte length for string return values of the routine if the routine is a stored function. If the routine is a stored procedure, the value of this column is NULL. At present, this column is not used. |
| NUMERIC_PRECISION | bigint(20) unsigned | NO | The numeric precision for numeric return values of the routine if the routine is a stored function. If the routine is a stored procedure, the value of this column is NULL. At present, this column is not used. |
| NUMERIC_SCALE | bigint(20) | NO | The numeric scale for numeric return values of the routine if the routine is a stored function. If the routine is a stored procedure, the value of this column is NULL. At present, this column is not used. |
| DATETIME_PRECISION | bigint(20) unsigned | NO | The fractional seconds precision for temporal return values of the routine if the routine is a stored function. If the routine is a stored procedure, the value of this column is NULL. At present, this column is not used. |
| CHARACTER_SET_NAME | varchar(64) | NO | The character set name for string return values of the routine if the routine is a stored function. If the routine is a stored procedure, the value of this column is NULL. At present, this column is not used. |
| COLLATION_NAME | varchar(64) | NO | The collation name for string return values of the routine if the routine is a stored function. If the routine is a stored procedure, the value of this column is NULL. At present, this column is not used. |
| DTD_IDENTIFIER | longtext | NO | Reserved for future use. The data type of the return value of the routine if the routine is a stored function. If the routine is a stored procedure, this column is empty. The value of DATA_TYPE contains only the type name, without other information. The value of DTD_IDENTIFIER contains the type name and possibly other information such as the precision or length. |
| ROUTINE_BODY | varchar(8) | NO | The language used for the routine definition. The value of this column is always SQL. |
| ROUTINE_DEFINITION | longtext | NO | The text of the routine definition. |
| EXTERNAL_NAME | varchar(64) | NO | The value of this column is always NULL. |
| EXTERNAL_LANGUAGE | varchar(64) | NO | The language of the routine. At present, this column is not used. |
| PARAMETER_STYLE | varchar(8) | NO | The value of this column is always SQL. |
| IS_DETERMINISTIC | varchar(3) | NO | Indicates whether the stored procedure is defined with the DETERMINISTIC characteristic. Valid value: YES or NO. At present, this column is not used. |
| SQL_DATA_ACCESS | varchar(64) | NO | Reserved for future use. The data access characteristic for the routine. |
| SQL_PATH | varchar(64) | NO | The value of this column is always NULL. |
| SECURITY_TYPE | varchar(7) | NO | The SQL security type. Valid values:
|
| CREATED | datetime | YES | The date and time when the routine was created. |
| LAST_ALTERED | datetime | YES | The date and time when the routine was last modified. If the routine has not been modified since its creation, the value of this column is the same as the value of CREATED. |
| SQL_MODE | text | NO | The SQL mode in effect when the routine was created or modified and under which the routine executes. |
| ROUTINE_COMMENT | longtext | NO | The text of the comment. |
| DEFINER | varchar(93) | NO | The account named in the DEFINER sub-clause (often the user who created the routine). |
| CHARACTER_SET_CLIENT | varchar(32) | NO | The session value of the system variable CHARACTER_SET_CLIENT when the routine was created or modified. |
| COLLATION_CONNECTION | varchar(32) | NO | The session value of the system variable COLLATION_CONNECTION when the routine was created or modified. |
| DATABASE_COLLATION | varchar(32) | NO | The session value of the system variable DATABASE_COLLATION when the routine was created or modified. |
Sample query
Query information related to stored procedures under the current tenant.
obclient[test]> SELECT * FROM information_schema.ROUTINES\G
The query result is as follows:
*************************** 1. row ***************************
SPECIFIC_NAME: citycount
ROUTINE_CATALOG: def
ROUTINE_SCHEMA: test
ROUTINE_NAME: citycount
ROUTINE_TYPE: PROCEDURE
DATA_TYPE: NULL
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
SELECT COUNT(*) INTO cities FROM city
WHERE CountryCode = country;
END
EXTERNAL_NAME: NULL
EXTERNAL_LANGUAGE: NULL
PARAMETER_STYLE: SQL
IS_DETERMINISTIC: NO
SQL_DATA_ACCESS: READS_SQL_DATA
SQL_PATH: NULL
SECURITY_TYPE: DEFINER
CREATED: 2025-05-07 14:58:18
LAST_ALTERED: 2025-05-07 14:58:18
SQL_MODE: STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_AUTO_CREATE_USER
ROUTINE_COMMENT:
DEFINER: 'root'@'%'
CHARACTER_SET_CLIENT: utf8mb4
COLLATION_CONNECTION: utf8mb4_general_ci
DATABASE_COLLATION: utf8mb4_general_ci
1 row in set