MySQL - SHOW PROCEDURE STATUS 语句
SHOW PROCEDURE STATUS 语句
存储过程是存储在 SQL 目录中的子例程，是 SQL 语句的片段。所有可以访问关系数据库(Java、Python、PHP 等)的应用程序都可以访问存储过程。
存储过程包含 IN 和 OUT 参数，或者两者兼有。如果您使用 SELECT 语句，它们可能会返回结果集。存储过程可以返回多个结果集。
MySQL SHOW PROCEDURE STATUS 语句显示存储过程的特性。它提供以下信息:-
- 过程的名称。
- 创建过程的数据库。
- 过程的类型。
- 过程的创建者。
- 修改日期等
语法
以下是 PROCEDURE STATUS 语句的语法:-
SHOW PROCEDURE STATUS [LIKE 'pattern' | WHERE expr]
示例
以下语句展示了存储过程的特征 -
SHOW PROCEDURE STATUS\G;
输出
以下是上述查询的输出 -
************ 1. row ************
Db: test
Name: areaOfCircle
Type: PROCEDURE
Definer: root@localhost
Modified: ------------------
Created: ------------------
Security_type: DEFINER
Comment:
character_set_client: cp850
collation_connection: cp850_general_ci
Database Collation: utf8mb4_0900_ai_ci
************ 2. row ************
Db: test
Name: case_example
Type: PROCEDURE
Definer: root@localhost
Modified: ------------------
Created: ------------------
Security_type: DEFINER
Comment:
character_set_client: cp850
collation_connection: cp850_general_ci
Database Collation: utf8mb4_0900_ai_ci
************ 3. row ************
Db: test
Name: coursedetails_CASE
Type: PROCEDURE
Definer: root@localhost
Modified: ------------------
Created: ------------------
Security_type: DEFINER
Comment:
character_set_client: cp850
collation_connection: cp850_general_ci
Database Collation: utf8mb4_0900_ai_ci
************ 4. row ************
Db: test
Name: curdemo
Type: PROCEDURE
Definer: root@localhost
Modified: ------------------
Created: ------------------
Security_type: DEFINER
Comment:
character_set_client: cp850
collation_connection: cp850_general_ci
Database Collation: utf8mb4_0900_ai_ci
************ 5. row ************
Db: ooo
Name: cursorExample
Type: PROCEDURE
Definer: root@localhost
Modified: ------------------
Created: ------------------
Security_type: DEFINER
Comment:
character_set_client: cp850
collation_connection: cp850_general_ci
Database Collation: utf8mb4_0900_ai_ci
************ 6. row ************
Db: test
Name: demo
Type: PROCEDURE
Definer: root@localhost
Modified: ------------------
Created: ------------------
Security_type: DEFINER
Comment:
character_set_client: cp850
collation_connection: cp850_general_ci
Database Collation: utf8mb4_0900_ai_ci
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
. . . . . . . . . . . . . . . . .
LIKE 子句
使用 LIKE 子句，您可以指定一个模式来检索有关过程的信息。
示例
假设我们创建了一个新数据库，并使用 CREATE 语句在其中创建了 3 个过程，如下所示 -
SHOW CREATE demo;
use dem;
database changed
DELIMITER //
CREATE PROCEDURE sample1 ()
BEGIN
SELECT 'This is a sample procedure';
END//
Query OK, 0 rows affected (0.29 sec)
CREATE PROCEDURE sample2 ()
BEGIN
SELECT 'This is a sample procedure';
END//
Query OK, 0 rows affected (0.29 sec)
CREATE PROCEDURE sample3 ()
BEGIN
SELECT 'This is a sample procedure';
END//
Query OK, 0 rows affected (0.29 sec)
CREATE PROCEDURE sample4 ()
BEGIN
SELECT 'This is a sample procedure';
END//
DELIMITER ;
以下查询检索名称以字母 "e" 开头的过程的信息。
SHOW PROCEDURE STATUS LIKE 'sample%'\G;
输出
上述查询生成如下所示的输出 -
************ 1. row ************
Db: demo
Name: sample1
Type: PROCEDURE
Definer: root@localhost
Modified: 2021-05-13 21:54:02
Created: 2021-05-13 21:54:02
Security_type: DEFINER
Comment:
character_set_client: cp850
collation_connection: cp850_general_ci
Database Collation: utf8mb4_0900_ai_ci
************ 2. row ************
Db: xo
Name: sample2
Type: PROCEDURE
Definer: root@localhost
Modified: 2021-05-13 21:54:07
Created: 2021-05-13 21:54:07
Security_type: DEFINER
Comment:
character_set_client: cp850
collation_connection: cp850_general_ci
Database Collation: utf8mb4_0900_ai_ci
************ 3. row ************
Db: xo
Name: sample3
Type: PROCEDURE
Definer: root@localhost
Modified: 2021-05-13 21:54:13
Created: 2021-05-13 21:54:13
Security_type: DEFINER
Comment:
character_set_client: cp850
collation_connection: cp850_general_ci
Database Collation: utf8mb4_0900_ai_ci
************ 4. row ************
Db: xo
Name: sample4
Type: PROCEDURE
Definer: root@localhost
Modified: 2021-05-13 21:54:19
Created: 2021-05-13 21:54:19
Security_type: DEFINER
Comment:
character_set_client: cp850
collation_connection: cp850_general_ci
Database Collation: utf8mb4_0900_ai_ci
WHERE 子句
您可以使用 SHOW PROCEDURE STATUS 语句的 WHERE 子句来检索符合指定条件的过程信息。
示例
假设我们在数据库中创建了一个名为 Employee 的表，如下所示 -
CREATE TABLE Employee( Name VARCHAR(255), Salary INT NOT NULL, Location VARCHAR(255) );
我们创建一个存储过程 myProcedure，它接受姓名、薪资和地点值，并将它们作为记录插入到上面创建的表中。
DELIMITER //
Create procedure myProcedure (
IN name VARCHAR(30),
IN sal INT,
IN loc VARCHAR(45))
BEGIN
INSERT INTO Employee(Name, Salary, Location) VALUES (name, sal, loc);
END //
DELIMITER ;
以同样的方式，下面的过程检索上面创建的表中的所有记录 -
Create procedure retrieveRecords ()
BEGIN
SELECT * FROM Dispatches;
END //
您可以使用 SHOW PROCEDURE STATUS 语句验证数据库中的过程列表，如下所示 -
SHOW PROCEDURE STATUS WHERE db = 'test'\G;
输出
查询执行后，将生成以下输出 -
************ 1. row ************
Db: test
Name: myProcedure
Type: PROCEDURE
Definer: root@localhost
Modified: 2021-03-22 14:02:01
Created: 2021-03-22 14:01:42
Security_type: DEFINER
Comment: This is a sample comment
character_set_client: cp850
collation_connection: cp850_general_ci
Database Collation: utf8mb4_0900_ai_ci
************ 2. row ************
Db: test
Name: retrieveRecords
Type: PROCEDURE
Definer: root@localhost
Modified: 2021-03-22 15:15:09
Created: 2021-03-22 15:15:09
Security_type: DEFINER
Comment:
character_set_client: cp850
collation_connection: cp850_general_ci
Database Collation: utf8mb4_0900_ai_ci
以下查询将删除/丢弃上面创建的过程 -
DROP PROCEDURE myProcedure; DROP PROCEDURE retrieveRecords;
验证
由于我们已经删除了这两个过程。如果您再次验证过程列表，您将得到一个空集 -
SHOW PROCEDURE STATUS WHERE db = 'test'; Empty set (0.00 sec)
