Overview
Displays information about stored procedures.
Columns
Field |
Type |
Nullable |
Description |
|---|---|---|---|
| db | varchar(128) | NO | Database Name |
| name | varchar(128) | NO | The name of the stored procedure. |
| type | varchar(10) | NO | Types of stored procedures:
|
| specific_name | varchar(128) | NO | Same as the name field. |
| language | varchar(4) | NO | Not used. The current value is a fixed SQL statement. |
| sql_data_access | varchar(32) | NO | Not used. The current value is CONTAINS_SQL. |
| is_deterministic | varchar(4) | NO | Not used. Indicates whether the stored procedure enables DETERMINISTIC Feature definition:
|
| security_type | varchar(10) | NO | Not used. Identifies the access attributes of the SQL statement:
|
| param_list | longblob | NO | Parameter list information of the stored procedure |
| returns | longblob | NO | Return value information of stored procedures |
| body | longblob | NO | Text information defining the stored procedure |
| definer | varchar(77) | NO | User who created the stored procedure |
| created | timestamp(6) | NO | Date and Time of Stored Procedure Creation |
| modified | timestamp(6) | NO | Last modified date and time of the stored procedure |
| sql_mode | text | NO | The SQL mode that is valid when creating or altering the stored procedure, and in which the stored procedure is executed. |
| comment | varchar(4096) | NO | Comment text |
| character_set_client | varchar(128) | NO | Session value of the character_set_client system variable when creating or altering a stored procedure |
| collation_connection | varchar(128) | NO | Session value of the collation_connection system variable when creating or altering a stored procedure |
| collation_database | varchar(128) | NO | Session value of collation_database system variable when creating or altering a stored procedure |
| body_utf8 | longblob | NO | Procedure definition, which is the same as the body field. |
Sample query
View the details of 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