MySQL存儲過程

博文大綱：

• 1、什么是存儲過程？
• 2、存儲過程有哪些優點？
• 3、自定義存儲過程舉例
• 4、while循環的存儲過程
• 5、帶有if判斷的存儲過程
• 6、帶有case的存儲過程
• 7、將存儲過程傳出到全局環境變量
• 8、其他關于存儲過程的操作語句
• 9、附加：如何復制表。

前言

存儲過程是數據庫存儲的一個重要的功能，MySQL在5.0以前的版本不支持存儲過程，存儲過程可以在大大提高數據庫處理速度的同時提高數據庫編程的靈活性。

1、什么是存儲過程？

存儲過程是一組為了完成特定功能的SQL語句集合。使用存儲過程的目的是將常用或復雜的工作預先用SQL語句寫好并用一個指定名稱存儲起來，這個過程經編譯和優化后存儲在數據庫服務器中，因此成為存儲過程。當以后需要數據庫提供與定義好的存儲過程的功能相同的服務時，只需要調用“CALL 存儲過程名字”即可自動完成。

一個存儲過程是一個可編程的函數，它在數據庫中創建并保存，一般由 SQL 語句和一些特殊的控制結構組成。
當希望在不同的應用程序或平臺上執行相同的特定功能時，存儲過程尤為合適。

2、存儲過程有哪些優點？

• 封裝性：存儲過程被創建后，可以在程序中被多次調用，而不必重新編寫該存儲過程的 SQL 語句，并且DBA可以隨時對存儲過程進行修改，而不會影響到調用它的應用程序源代碼。
• 可增強：SQL 語句的功能和靈活性 存儲過程可以用流程控制語句編寫，有很強的靈活性，可以完成復雜的判斷和較復雜的運算。
• 可減少網絡流量：由于存儲過程是在服務器端運行的，且執行速度快，因此當客戶計算機上調用該存儲過程時，網絡中傳送的只是該調用語句，從而可降低網絡負載。
• 高性能：存儲過程執行一次后，產生的二進制代碼就駐留在緩沖區，在以后的調用中，只需要從緩沖區中執行二進制代碼即可，從而提高了系統的效率和性能。
• 提高數據庫的安全性和數據的完整性：使用存儲過程可以完成所有數據庫操作，并且可以通過編程的方式控制數據庫信息訪問的權限。

3、自定義存儲過程舉例

mysql> select * from t1;            <!--查詢一條sql語句-->
+------+------+------------+---------+
| f_id | s_id | f_name     | f_price |
+------+------+------------+---------+
| a1   |  101 | apple      |    5.20 |
| a2   |  103 | apricot    |    2.20 |
| b1   |  101 | blackberry |   10.20 |
| b2   |  104 | berry      |    7.60 |
| b5   |  107 | xxxx       |    3.60 |
| bs1  |  102 | orange     |   11.20 |
| bs2  |  105 | melon      |    8.20 |
| c0   |  101 | cherry     |    3.20 |
| l2   |  104 | lemon      |    6.40 |
| m1   |  106 | mango      |   15.70 |
| m2   |  105 | xbabay     |    2.60 |
| m3   |  105 | xxtt       |   11.60 |
| o2   |  103 | coconut    |    9.20 |
| t1   |  102 | banana     |   10.30 |
| t2   |  102 | grape      |    5.30 |
| t4   |  107 | xbababa    |    3.60 |
+------+------+------------+---------+
mysql> delimiter //               <!--更改其默認的分隔符為“//”，也可以是其他任意符號，只要不是默認的“;”就行-->
mysql> create procedure test()            <!--定義存儲過程test-->
    -> begin
    -> select * from t1;              <!--將sql語句寫入存儲過程，可以寫多條-->
    -> end//
Query OK, 0 rows affected (0.00 sec)
mysql> delimiter ;              <!--改回默認的分隔符-->
mysql> call test();               <!--調用存儲過程-->
+------+------+------------+---------+
| f_id | s_id | f_name     | f_price |
+------+------+------------+---------+
| a1   |  101 | apple      |    5.20 |
| a2   |  103 | apricot    |    2.20 |
| b1   |  101 | blackberry |   10.20 |
| b2   |  104 | berry      |    7.60 |
| b5   |  107 | xxxx       |    3.60 |
| bs1  |  102 | orange     |   11.20 |
| bs2  |  105 | melon      |    8.20 |
| c0   |  101 | cherry     |    3.20 |
| l2   |  104 | lemon      |    6.40 |
| m1   |  106 | mango      |   15.70 |
| m2   |  105 | xbabay     |    2.60 |
| m3   |  105 | xxtt       |   11.60 |
| o2   |  103 | coconut    |    9.20 |
| t1   |  102 | banana     |   10.30 |
| t2   |  102 | grape      |    5.30 |
| t4   |  107 | xbababa    |    3.60 |
+------+------+------------+---------+
16 rows in set (0.00 sec)

通過上面的例子可以看出，存儲過程是類似于一個shell腳本的，存放的是一些sql語句的集合，當然，它同樣有一些判斷、循環等語句，如下。

4、while循環的存儲過程

下面的例子是存儲過程借助while循環來計算1+2+3...+100的結果是多少。

mysql> delimiter //                <!--改變默認的截斷符為“//”-->
mysql> create procedure test1()        <!--創建存儲過程-->
    -> begin                     <!--存儲過程開始-->
    -> declare n int;                  <!--定義一個變量名-->
    -> declare summary int;             <!--定義一個變量名-->
    -> set n=0;                   <!--設置變量的初始值為0-->
    -> set summary=0;        <!--設置變量的初始值為0-->
    -> while n<=100                  <!--當n小于或等于100的時候-->
    -> do
    -> set summary=summary+n;           <!--summary就+n-->
    -> set n=n+1;                         <!--然后n+1-->
    -> end while;                     <!--循環結束-->
    -> select summary;           <!--查詢summary的值-->
    -> end //                         <!--存儲過程結束-->
mysql> delimiter ;                <!--將截斷符改回默認的-->
mysql> call test1();                <!--調用存儲過程-->
+---------+
| summary |
+---------+
|    5050 |
+---------+
1 row in set (0.00 sec)

5、帶有if判斷的存儲過程

以下實現的是如果傳參的值大于或等于10，則執行else下面的SQL語句，如果傳參的值小于10，則執行then下面的SQL語句。

mysql> delimiter //  

mysql> create procedure test3(in num int)
    -> begin
    -> if num < 10 then
    -> select * from t1 where f_price<10;
    -> else
    -> select * from t1 where f_price>=10;
    -> end if;
    -> end
    -> //
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;
mysql> call test3(9);        <!--傳入值為9-->
+------+------+---------+---------+
| f_id | s_id | f_name  | f_price |
+------+------+---------+---------+
| a1   |  101 | apple   |    5.20 |
| a2   |  103 | apricot |    2.20 |
| b2   |  104 | berry   |    7.60 |
| b5   |  107 | xxxx    |    3.60 |
| bs2  |  105 | melon   |    8.20 |
| c0   |  101 | cherry  |    3.20 |
| l2   |  104 | lemon   |    6.40 |
| m2   |  105 | xbabay  |    2.60 |
| o2   |  103 | coconut |    9.20 |
| t2   |  102 | grape   |    5.30 |
| t4   |  107 | xbababa |    3.60 |
+------+------+---------+---------+
11 rows in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)
mysql> call test3(10);                   <!--傳入值為10-->
+------+------+------------+---------+
| f_id | s_id | f_name     | f_price |
+------+------+------------+---------+
| b1   |  101 | blackberry |   10.20 |
| bs1  |  102 | orange     |   11.20 |
| m1   |  106 | mango      |   15.70 |
| m3   |  105 | xxtt       |   11.60 |
| t1   |  102 | banana     |   10.30 |
+------+------+------------+---------+
5 rows in set (0.00 sec)

6、帶有case的存儲過程

該存儲過程實現結果為：當傳入的值為偶數時，輸出t1表中s_id列為偶數的行，如果傳入的值為奇數，輸出s_id列為奇數的行，否則輸出空。

mysql> delimiter //
mysql> create procedure test4(in num int)
    -> begin
    -> case num%2
    -> when 0 then
    -> select * from t1 where s_id%2=0;
    -> when 1 then
    -> select * from t1 where s_id%2=1;
    -> else
    -> select null;
    -> end case;
    -> end
    -> //
mysql> delimiter ;
mysql> call test4(4);             <!--傳入偶數-->
+------+------+--------+---------+
| f_id | s_id | f_name | f_price |
+------+------+--------+---------+
| b2   |  104 | berry  |    7.60 |
| bs1  |  102 | orange |   11.20 |
| l2   |  104 | lemon  |    6.40 |
| m1   |  106 | mango  |   15.70 |
| t1   |  102 | banana |   10.30 |
| t2   |  102 | grape  |    5.30 |
+------+------+--------+---------+
6 rows in set (0.00 sec)
mysql> call test4(3);         <!--傳入奇數-->
+------+------+------------+---------+
| f_id | s_id | f_name     | f_price |
+------+------+------------+---------+
| a1   |  101 | apple      |    5.20 |
| a2   |  103 | apricot    |    2.20 |
| b1   |  101 | blackberry |   10.20 |
| b5   |  107 | xxxx       |    3.60 |
| bs2  |  105 | melon      |    8.20 |
| c0   |  101 | cherry     |    3.20 |
| m2   |  105 | xbabay     |    2.60 |
| m3   |  105 | xxtt       |   11.60 |
| o2   |  103 | coconut    |    9.20 |
| t4   |  107 | xbababa    |    3.60 |
+------+------+------------+---------+

7、將存儲過程傳出到全局環境變量

mysql> delimiter //
mysql> create procedure test6(out num float)
    -> begin
    -> select max(f_price) into num from t1;
    -> end
    -> //
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;
mysql> call test6(@num);
Query OK, 1 row affected (0.00 sec)

mysql> select @num;             <!--由于最大的值是浮點數，所以會這樣-->
+--------------------+
| @num               |
+--------------------+
| 15.699999809265137 |
+--------------------+
1 row in set (0.00 sec)

8、其他關于存儲過程的操作語句

mysql> help procedure;          <!--查看幫助-->        
topics:
   ALTER PROCEDURE          <!--修改procedure，一般用不到，需要修改的話，直接刪除再創建即可-->
   CREATE PROCEDURE        <!--創建procedure-->
   DROP PROCEDURE               <!--刪除procedure-->
   PROCEDURE ANALYSE         
   SELECT
   SHOW
   SHOW CREATE PROCEDURE  存儲過程名    <!--查看某個存儲過程的詳細信息-->

9、附加：如何復制表。

方法1：like方法能一模一樣的將一個表的結果復制生成一個新表，包括復制表的備注、索引、主鍵外鍵、存儲引擎等。但是不包括表數據，如下：

mysql> create table new_t1 like t1;
Query OK, 0 rows affected (0.00 sec)

mysql> desc new_t1;
+---------+--------------+------+-----+---------+-------+
| Field   | Type         | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| f_id    | char(10)     | NO   |     | NULL    |       |
| s_id    | int(11)      | NO   |     | NULL    |       |
| f_name  | char(255)    | NO   |     | NULL    |       |
| f_price | decimal(8,2) | NO   |     | NULL    |       |
+---------+--------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

方法2： select的方法值復制字段屬性，其它的主鍵、索引、表備注、存儲引擎都沒有復制。如下：

mysql> create table new_t1_2 select * from t1;
Query OK, 16 rows affected (0.01 sec)
Records: 16  Duplicates: 0  Warnings: 0

mysql> select * from new_t1_2;
+------+------+------------+---------+
| f_id | s_id | f_name     | f_price |
+------+------+------------+---------+
| a1   |  101 | apple      |    5.20 |
| a2   |  103 | apricot    |    2.20 |
| b1   |  101 | blackberry |   10.20 |
| b2   |  104 | berry      |    7.60 |
| b5   |  107 | xxxx       |    3.60 |
| bs1  |  102 | orange     |   11.20 |
| bs2  |  105 | melon      |    8.20 |
| c0   |  101 | cherry     |    3.20 |
| l2   |  104 | lemon      |    6.40 |
| m1   |  106 | mango      |   15.70 |
| m2   |  105 | xbabay     |    2.60 |
| m3   |  105 | xxtt       |   11.60 |
| o2   |  103 | coconut    |    9.20 |
| t1   |  102 | banana     |   10.30 |
| t2   |  102 | grape      |    5.30 |
| t4   |  107 | xbababa    |    3.60 |
+------+------+------------+---------+
16 rows in set (0.00 sec)

———————— 本文至此結束，感謝閱讀 ————————