Query mysql stored procedure

1. Overview

MySQL stored procedure is a very important feature in the MySQL database. It can encapsulate some SQL statements that need to be executed frequently into a unit for easy management and use. This article will introduce how to query MySQL stored procedures.

2. Basic syntax for querying stored procedures

The basic syntax for querying MySQL stored procedures is as follows:

SHOW PROCEDURE STATUS [LIKE 'pattern'];

pattern is the matching pattern of the procedure name , you can use the wildcard characters % and _.

3. Detailed explanation of parameters for querying stored procedures

When using the above syntax to query stored procedures, you can obtain results including the following columns:

##CommentComment of stored procedure## 4. Example

Column name	Description
Db	The name of the database where the stored procedure is located
Name	The name of the stored procedure
Type	If the stored procedure has input parameters or output parameters, it is PROCEDURE, otherwise it is FUNCTION
Definer	The definer of the stored procedure
Modified	The last modified stored procedure Time
Created	The time when the stored procedure was created
Security_type	The security type of the stored procedure : DEFINER or INVOKER

Suppose there is a stored procedure in our MySQL database named

get_total

, and the code is as follows: <pre class='brush:php;toolbar:false;'>CREATE PROCEDURE `get_total`(IN `p_price` INT, OUT `p_total` DECIMAL(10,2)) BEGIN SELECT SUM(price) INTO p_total FROM orders WHERE price&gt;p_price; END;</pre> We can use the following statement to query the information of the stored procedure:

SHOW PROCEDURE STATUS WHERE Name='get_total';

The query results are as follows:

+-----------+----------+-------+---------+---------------------+---------------------+----------------+---------+
| Db        | Name     | Type  | Definer | Modified            | Created             | Security_type  | Comment |
+-----------+----------+-------+---------+---------------------+---------------------+----------------+---------+
| test_db   | get_total| PROCEDURE | root@%   | 2019-01-01 00:00:00 | 2018-01-01 00:00:00 | DEFINER        | a test  |
+-----------+----------+-------+---------+---------------------+---------------------+----------------+---------+

Through this result, we can obtain the relevant information of the stored procedure

get_total

, including the database test_db, and the process type is PROCEDURE. The definer is root@%, the last modification time is 2019-01-01 00:00:00, and the creation time is 2018-01-01 00:00:00 , security type is DEFINER and annotation is a test. 5. Summary

Through the above examples, we can see that querying MySQL stored procedures is very simple. You only need to use the SHOW PROCEDURE STATUS statement. Through query, we can obtain the relevant information of the stored procedure, which facilitates further management and use of the stored procedure. In practical applications, stored procedures are one of the indispensable and important features of the MySQL database. It can greatly improve the efficiency and security of database operations, allowing developers to focus more on the implementation of business logic.

The above is the detailed content of Query mysql stored procedure. For more information, please follow other related articles on the PHP Chinese website!