Note
This view is introduced since OceanBase Database V2.2.77.
Purpose
This view displays information about stored procedures.
Columns
Column |
Type |
Nullable? |
Description |
|---|---|---|---|
| db | varchar(128) | NO | The name of the database. |
| name | varchar(128) | NO | The name of the stored procedure. |
| type | varchar(10) | NO | The type of the stored procedure. Valid values: PROCEDURE: a stored procedure.FUNCTION: a stored function. |
| specific_name | varchar(128) | NO | The value is the same as that of the name column. |
| language | varchar(4) | NO | At present, this column is not used and is always SQL. |
| sql_data_access | varchar(32) | NO | At present, this column is not used is fixed to CONTAINS_SQL. |
| is_deterministic | varchar(4) | NO | Indicates whether the stored procedure is defined with the DETERMINISTIC characteristic. This column is not used. |
| security_type | varchar(10) | NO | The SQL access attribute. This column is not used. |
| param_list | longblob | NO | The parameter list of the stored procedure. |
| returns | longblob | NO | The return value of the stored procedure. |
| body | longblob | NO | The text information of the stored procedure definition. |
| definer | varchar(77) | NO | The user who created the stored procedure. |
| created | timestamp(6) | NO | The date and time when the stored procedure was created. |
| modified | timestamp(6) | NO | The date and time when the stored procedure was last modified. |
| sql_mode | text | NO | The SQL mode in effect when the stored procedure was created or modified and under which the stored procedure executes. |
| comment | varchar(4096) | NO | The text of the comment. |
| character_set_client | varchar(128) | NO | The session value of the system variable CHARACTER_SET_CLIENT when the stored procedure was created or modified. |
| collation_connection | varchar(128) | NO | The session value of the system variable COLLATION_CONNECTION when the stored procedure was created or modified. |
| collation_database | varchar(128) | NO | The session value of the system variable collation_database when the stored procedure was created or modified. |
| body_utf8 | longblob | NO | The definition of the stored procedure, which is the same as value of the body column. |
Sample query
Query detailed information about the stored procedure my_proc.
obclient [infotest]> SELECT * FROM mysql.proc WHERE NAME='my_proc'\G
The query result is as follows:
*************************** 1. row ***************************
DB: infotest
NAME: my_proc
TYPE: PROCEDURE
SPECIFIC_NAME: my_proc
LANGUAGE: SQL
SQL_DATA_ACCESS: READS_SQL_DATA
IS_DETERMINISTIC: NO
SECURITY_TYPE: DEFINER
PARAM_LIST: IN emp_no INT,OUT emp_count INT
RETURNS:
BODY: BEGIN
SELECT COUNT(*) INTO emp_count FROM emp WHERE empno=emp_no;
END
DEFINER: 'root'@'%'
CREATED: 2025-01-24 14:00:18.496744
MODIFIED: 2025-01-24 14:00:18.496744
SQL_MODE: STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_AUTO_CREATE_USER
COMMENT:
CHARACTER_SET_CLIENT: utf8mb4
COLLATION_CONNECTION: utf8mb4_general_ci
DB_COLLATION: utf8mb4_general_ci
BODY_UTF8: BEGIN
SELECT COUNT(*) INTO emp_count FROM emp WHERE empno=emp_no;
END
1 row in set