MySQL - SHOW PROCEDURE STATUS 語句

Table of content
上一頁
下一頁

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)
列印頁面
上一頁
下一頁
廣告