1. 存儲過程總結

存儲過程是一種存儲在數據庫中的程序。它可以包含多個 SQL 語句，并提供許多過程語言的功能，例如變量定義、條件控制語句、循環語句、游標以及異常處理等。

1.1 存儲過程優點

• 實現代碼的重用和管理性。存儲過程創建后可以在被重復調用，不同的應用可以共享相同的存儲過程；
• 實現業務的封裝和隔離。應用程序通過接口訪問存儲過程，而不關系具體實現；當業務發生變化時，只需要修改存儲過程的邏輯，但對應用程序源代碼卻毫無影響
• 提高應用的執行效率。存儲過程經過編譯之后存儲在數據庫中，執行時可以進行緩存，可以提高執行的速度；
• 減少了應用與數據庫之間的網絡流量。調用存儲過程時，只需要傳遞參數，在一定程度上可以減輕網絡負擔；
• 存儲過程可以提高安全性。應用程序通過存儲過程進行數據訪問，而不需要直接訪問數據表，保證數據的安全。

1.2 存儲過程缺點

• 不同數據庫的實現不同。Oracle 中稱為 PL/SQL，MySQL 中稱為 PSM，其他數據庫也都有各自的語法實現；
• 存儲過程需要占用數據庫服務器的資源，包括 CPU、內存等，而數據庫的擴展性不如應用程序；
• 存儲過程的開發和維護需要專業的技能，存儲過程的調試不如其他編程語言方便。

2. 創建存儲過程

2.1 默認分隔符

默認的 MySQL 語句分隔符為 ;
mysql 命令行實用程序也使用 ; 作為語句分隔符。如果命令行實用程序要解釋存儲過程自身內的 ; 字符，則它們最終不會成為存儲過程的成分，這會使存儲過程中的SQL 出現句法錯誤。

解決辦法是臨時更改命令行實用程序的語句分隔符，如下所示：

DELIMITER  //
....
DELIMITER  ;

其中，DELIMITER // 告訴命令行實用程序使用 // 作為新的語句結束分隔符，可以看到標志存儲過程結束的 END 定義為 END // 而不是 END;。這樣，存儲過程體內的 ; 仍然保持不動，并且正確地傳遞給數據庫引擎。最后，為恢復為原來的語句分隔符，可使用 DELIMITER ; 。

除 \ 符號外，任何字符都可以用作語句分隔符。

2.2 創建存儲過程

mysql> CREATE PROCEDURE productpricing()-> BEGIN-> 	SELECT AVG(prod_price) AS price_average->  FROM products;-> END;
Query OK, 0 rows affected

此存儲過程名為 productpricing，用 CREATE PROCEDURE productpricing() 語句定義。如果存儲過程接受參數，它們將在 () 中列舉出來。此存儲過程沒有參數，但后跟的 () 仍然需要。BEGIN 和 END 語句用來限定存儲過程體。

3. 調用過程

SQL 使用 CALL 命令調用存儲過程，大部分數據庫遵循該標準。

mysql> CALL productpricing();
+---------------+
| price_average |
+---------------+
| 16.133571     |
+---------------+
1 row in set

4. 刪除存儲過程

使用 DROP PROCEDURE 語句可以從數據庫中刪除存儲過程。存儲過程在創建之后，被保存在服務器上以供使用，直至被刪除。

mysql> DROP PROCEDURE IF EXISTS productpricing;
Query OK, 0 rows affectedmysql> CALL productpricing();
1305 - PROCEDURE aaa.productpricing does not exist

5. 帶參數的存儲過程創建和調用

5.1 創建存儲過程

productpricing 只是一個簡單的存儲過程，它簡單地顯示 SELECT 語句的結果。一般，存儲過程并不顯示結果，而是把結果返回給你指定的變量。

變量（variable）內存中一個特定的位置，用來臨時存儲數據。

mysql> CREATE PROCEDURE productpricing(-> 		OUT min_price DECIMAL(8,2),-> 		OUT max_price DECIMAL(8,2),-> 		OUT avg_price DECIMAL(8,2)-> )-> BEGIN-> 		SELECT min(prod_price) INTO min_price FROM products;-> 		SELECT max(prod_price) INTO max_price FROM products;-> 		SELECT avg(prod_price) INTO avg_price FROM products;-> END;
Query OK, 0 rows affected

此存儲過程接受 3 個參數：min_price 存儲產品最低價格，max_price 存儲產品最高價格，avg_price 存儲產品平均價格。每個參數必須具有指定的類型，這里使用十進制值。

關鍵字 OUT 指出相應的參數用來從存儲過程傳出一個值（返回給調用者）。

MySQL 支持 IN（傳遞給存儲過程）、OUT（從存儲過程傳出，如這里所用）和INOUT（對存儲過程傳入和傳出）類型的參數。存儲過程的代碼位于 BEGIN 和END 語句內，如前所見，它們是一系列 SELECT 語句，用來檢索值，然后保存到相應的變量（通過指 INTO 關鍵字）。

5.2 調用存儲過程

mysql> CALL productpricing(@price_min, @price_max, @price_avg);
Query OK, 1 row affected

由于此存儲過程要求 3 個參數，因此必須正好傳遞 3 個參數，不多也不少。所以，這條 CALL 語句給出 3 個參數。它們是存儲過程將保存結果的 3 個變量的名字。

變量名 所有 MySQL 變量都必須以 @開始。

在調用時，這條語句并不顯示任何數據。它返回以后可以顯示（或在其他處理中使用）的變量。

5.3 顯示存儲過程結果

mysql> SELECT @price_min, @price_max, @price_avg;
+------------+------------+------------+
| @price_min | @price_max | @price_avg |
+------------+------------+------------+
| 2.50       | 55.00      | 16.13      |
+------------+------------+------------+
1 row in setmysql> 

6. 同時攜帶 IN 和 OUT 參數的存儲過程

6.1 創建存儲過程

mysql> CREATE PROCEDURE ordertotal(-> 		IN  order_number INT,-> 		OUT total_price DECIMAL(8,2)-> )-> BEGIN-> 		SELECT sum(item_price * quantity) FROM orderitems WHERE order_num = order_number INTO total_price;-> END;
Query OK, 0 rows affectedmysql> 

order_number 定義為 IN，因為訂單號被傳入存儲過程。
total_price 定義為 OUT，因為要從存儲過程返回合計。
SELECT 語句使用這兩個參數，WHERE 子句使用 order_number 選擇正確的行，INTO 使用 total_price 存儲計算出來的合計。

6.2 調用存儲過程

必須給 ordertotal 傳遞兩個參數；第一個參數為訂單號，第二個參數為包含計算出來的合計的變量名。

mysql> CALL ordertotal(20005, @total_price);
Query OK, 1 row affectedmysql> SELECT @total_price;
+--------------+
| @total_price |
+--------------+
| 149.87       |
+--------------+
1 row in setmysql> 

查詢另外一個訂單

mysql> CALL ordertotal(20006, @total_price);SELECT @total_price;
Query OK, 1 row affected+--------------+
| @total_price |
+--------------+
| 55.00        |
+--------------+
1 row in setmysql> 

7. 檢查存儲過程

為顯示用來創建一個存儲過程的 CREATE 語句，使用 SHOW CREATE PROCEDURE 語句：

mysql> SHOW CREATE PROCEDURE ORDERTOTAL;
+------------+----------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
| Procedure  | sql_mode                                                       | Create Procedure                                                                                                                                                                                                           | character_set_client | collation_connection | Database Collation |
+------------+----------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
| ordertotal | STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION | CREATE DEFINER=`root`@`localhost` PROCEDURE `ordertotal`(IN  order_number INT,OUT total_price DECIMAL(8,2))
BEGINSELECT sum(item_price * quantity) FROM orderitems WHERE order_num = order_number INTO total_price;END | utf8                 | utf8_general_ci      | utf8_general_ci    |
+------------+----------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
1 row in setmysql> 

總結

以上是生活随笔為你收集整理的MySQL 学习笔记（6）— 存储过程创建、调用、删除以及带参数的存储过程的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。