MySQL存儲(chǔ)過(guò)程

博文大綱：

• 1、什么是存儲(chǔ)過(guò)程？
• 2、存儲(chǔ)過(guò)程有哪些優(yōu)點(diǎn)？
• 3、自定義存儲(chǔ)過(guò)程舉例
• 4、while循環(huán)的存儲(chǔ)過(guò)程
• 5、帶有if判斷的存儲(chǔ)過(guò)程
• 6、帶有case的存儲(chǔ)過(guò)程
• 7、將存儲(chǔ)過(guò)程傳出到全局環(huán)境變量
• 8、其他關(guān)于存儲(chǔ)過(guò)程的操作語(yǔ)句
• 9、附加：如何復(fù)制表。

前言

存儲(chǔ)過(guò)程是數(shù)據(jù)庫(kù)存儲(chǔ)的一個(gè)重要的功能，MySQL在5.0以前的版本不支持存儲(chǔ)過(guò)程，存儲(chǔ)過(guò)程可以在大大提高數(shù)據(jù)庫(kù)處理速度的同時(shí)提高數(shù)據(jù)庫(kù)編程的靈活性。

1、什么是存儲(chǔ)過(guò)程？

存儲(chǔ)過(guò)程是一組為了完成特定功能的SQL語(yǔ)句集合。使用存儲(chǔ)過(guò)程的目的是將常用或復(fù)雜的工作預(yù)先用SQL語(yǔ)句寫好并用一個(gè)指定名稱存儲(chǔ)起來(lái)，這個(gè)過(guò)程經(jīng)編譯和優(yōu)化后存儲(chǔ)在數(shù)據(jù)庫(kù)服務(wù)器中，因此成為存儲(chǔ)過(guò)程。當(dāng)以后需要數(shù)據(jù)庫(kù)提供與定義好的存儲(chǔ)過(guò)程的功能相同的服務(wù)時(shí)，只需要調(diào)用“CALL 存儲(chǔ)過(guò)程名字”即可自動(dòng)完成。

一個(gè)存儲(chǔ)過(guò)程是一個(gè)可編程的函數(shù)，它在數(shù)據(jù)庫(kù)中創(chuàng)建并保存，一般由 SQL 語(yǔ)句和一些特殊的控制結(jié)構(gòu)組成。
當(dāng)希望在不同的應(yīng)用程序或平臺(tái)上執(zhí)行相同的特定功能時(shí)，存儲(chǔ)過(guò)程尤為合適。

2、存儲(chǔ)過(guò)程有哪些優(yōu)點(diǎn)？

• 封裝性：存儲(chǔ)過(guò)程被創(chuàng)建后，可以在程序中被多次調(diào)用，而不必重新編寫該存儲(chǔ)過(guò)程的 SQL 語(yǔ)句，并且DBA可以隨時(shí)對(duì)存儲(chǔ)過(guò)程進(jìn)行修改，而不會(huì)影響到調(diào)用它的應(yīng)用程序源代碼。
• 可增強(qiáng)：SQL 語(yǔ)句的功能和靈活性 存儲(chǔ)過(guò)程可以用流程控制語(yǔ)句編寫，有很強(qiáng)的靈活性，可以完成復(fù)雜的判斷和較復(fù)雜的運(yùn)算。
• 可減少網(wǎng)絡(luò)流量：由于存儲(chǔ)過(guò)程是在服務(wù)器端運(yùn)行的，且執(zhí)行速度快，因此當(dāng)客戶計(jì)算機(jī)上調(diào)用該存儲(chǔ)過(guò)程時(shí)，網(wǎng)絡(luò)中傳送的只是該調(diào)用語(yǔ)句，從而可降低網(wǎng)絡(luò)負(fù)載。
• 高性能：存儲(chǔ)過(guò)程執(zhí)行一次后，產(chǎn)生的二進(jìn)制代碼就駐留在緩沖區(qū)，在以后的調(diào)用中，只需要從緩沖區(qū)中執(zhí)行二進(jìn)制代碼即可，從而提高了系統(tǒng)的效率和性能。
• 提高數(shù)據(jù)庫(kù)的安全性和數(shù)據(jù)的完整性：使用存儲(chǔ)過(guò)程可以完成所有數(shù)據(jù)庫(kù)操作，并且可以通過(guò)編程的方式控制數(shù)據(jù)庫(kù)信息訪問(wèn)的權(quán)限。

3、自定義存儲(chǔ)過(guò)程舉例

mysql> select * from t1;            <!--查詢一條sql語(yǔ)句-->
+------+------+------------+---------+
| 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 //               <!--更改其默認(rèn)的分隔符為“//”，也可以是其他任意符號(hào)，只要不是默認(rèn)的“;”就行-->
mysql> create procedure test()            <!--定義存儲(chǔ)過(guò)程test-->
    -> begin
    -> select * from t1;              <!--將sql語(yǔ)句寫入存儲(chǔ)過(guò)程，可以寫多條-->
    -> end//
Query OK, 0 rows affected (0.00 sec)
mysql> delimiter ;              <!--改回默認(rèn)的分隔符-->
mysql> call test();               <!--調(diào)用存儲(chǔ)過(guò)程-->
+------+------+------------+---------+
| 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)

通過(guò)上面的例子可以看出，存儲(chǔ)過(guò)程是類似于一個(gè)shell腳本的，存放的是一些sql語(yǔ)句的集合，當(dāng)然，它同樣有一些判斷、循環(huán)等語(yǔ)句，如下。

4、while循環(huán)的存儲(chǔ)過(guò)程

下面的例子是存儲(chǔ)過(guò)程借助while循環(huán)來(lái)計(jì)算1+2+3...+100的結(jié)果是多少。

mysql> delimiter //                <!--改變默認(rèn)的截?cái)喾麨椤?/”-->
mysql> create procedure test1()        <!--創(chuàng)建存儲(chǔ)過(guò)程-->
    -> begin                     <!--存儲(chǔ)過(guò)程開(kāi)始-->
    -> declare n int;                  <!--定義一個(gè)變量名-->
    -> declare summary int;             <!--定義一個(gè)變量名-->
    -> set n=0;                   <!--設(shè)置變量的初始值為0-->
    -> set summary=0;        <!--設(shè)置變量的初始值為0-->
    -> while n<=100                  <!--當(dāng)n小于或等于100的時(shí)候-->
    -> do
    -> set summary=summary+n;           <!--summary就+n-->
    -> set n=n+1;                         <!--然后n+1-->
    -> end while;                     <!--循環(huán)結(jié)束-->
    -> select summary;           <!--查詢summary的值-->
    -> end //                         <!--存儲(chǔ)過(guò)程結(jié)束-->
mysql> delimiter ;                <!--將截?cái)喾幕啬J(rèn)的-->
mysql> call test1();                <!--調(diào)用存儲(chǔ)過(guò)程-->
+---------+
| summary |
+---------+
|    5050 |
+---------+
1 row in set (0.00 sec)

5、帶有if判斷的存儲(chǔ)過(guò)程

以下實(shí)現(xiàn)的是如果傳參的值大于或等于10，則執(zhí)行else下面的SQL語(yǔ)句，如果傳參的值小于10，則執(zhí)行then下面的SQL語(yǔ)句。

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的存儲(chǔ)過(guò)程

該存儲(chǔ)過(guò)程實(shí)現(xiàn)結(jié)果為：當(dāng)傳入的值為偶數(shù)時(shí)，輸出t1表中s_id列為偶數(shù)的行，如果傳入的值為奇數(shù)，輸出s_id列為奇數(shù)的行，否則輸出空。

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);             <!--傳入偶數(shù)-->
+------+------+--------+---------+
| 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);         <!--傳入奇數(shù)-->
+------+------+------------+---------+
| 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、將存儲(chǔ)過(guò)程傳出到全局環(huán)境變量

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;             <!--由于最大的值是浮點(diǎn)數(shù)，所以會(huì)這樣-->
+--------------------+
| @num               |
+--------------------+
| 15.699999809265137 |
+--------------------+
1 row in set (0.00 sec)

8、其他關(guān)于存儲(chǔ)過(guò)程的操作語(yǔ)句

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

9、附加：如何復(fù)制表。

方法1：like方法能一模一樣的將一個(gè)表的結(jié)果復(fù)制生成一個(gè)新表，包括復(fù)制表的備注、索引、主鍵外鍵、存儲(chǔ)引擎等。但是不包括表數(shù)據(jù)，如下：

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的方法值復(fù)制字段屬性，其它的主鍵、索引、表備注、存儲(chǔ)引擎都沒(méi)有復(fù)制。如下：

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)

———————— 本文至此結(jié)束，感謝閱讀 ————————