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:

Suppose there is a stored procedure in our MySQL database named

, and the code is as follows:

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>p_price;
END;

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

, 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!