MySQL全面瓦解16:儲存過程相關

翁智華 發表於 2021-01-18

概述

大多數SQL語句都是針對一個或多個表的單條語句。但並非所有業務都這麼簡單,經常會有複雜的操作需要多條語句才能完成。

比如使用者購買一個商品,要刪減庫存表,要生成訂單資料,要儲存支付資訊等等,他是一個批量的語句執行行為。

儲存過程簡單來說,就是為以後的使用而儲存的一條或多條MySQL語句的集合。可將其視為批檔案,雖然它們的作用不僅限於批處理。 

優點:
提高程式碼的複用性:把一些通用操作內容封裝到一個儲存過程中,可以不斷的給業務功能複用。

簡化操作:避免在業務中寫大量的程式碼

提高效率:減少執行次數和資料庫伺服器連線次數。

提高安全性:通過儲存過可以減少對基礎資料的誤操作,引數化的儲存過程一定程度上可以防止SQL隱碼攻擊式攻擊,而且可以將Grant、Deny以及Revoke許可權應用於儲存過程。 

說儲存過程之前,先來了解兩個重要的知識點:自定義變數 和 delimiter關鍵字。

自定義變數

概念

變數由使用者自定義的,而非系統已經存在的。

使用步驟

第一步宣告;第二步賦值;第三步使用(呼叫、比較和運算)

分類

包含使用者變數和區域性變數,我們一個個來看:

使用者變數

作用域

針對當前會話有效,作用域同會話變數。

使用者變數可以在任何地方使用,既可以是包含的begin和end,也可以是在這之外。

使用

宣告並初始化
1 set @variable=value;
2 or
3 set @variable:=value;
4 or
5 select @variable:=value;

這邊需要注意:使用了@符號來定義 變數,set中=號前面冒號是可選的,select方式=前面必須有冒號。

賦值方式

一種方式就是跟宣告並初始化一致,直接set、select進行賦值,

另外一種就是直接從其他表、檢視或變數中查詢並賦值,如下:

1 select columnname into @variable from tname; 

這邊需要注意兩種select的使用方式

實踐一下

 1 mysql> set @var1='num1';
 2 set @var2:='num2';
 3 select @var3:='num3';
 4 select @var1,@var2,@var3;
 5 Query OK, 0 rows affected
 6 
 7 Query OK, 0 rows affected
 8 
 9 +---------------+
10 | @var3:='num3' |
11 +---------------+
12 | num3          |
13 +---------------+
14 1 row in set
15 
16 +-------+-------+-------+
17 | @var1 | @var2 | @var3 |
18 +-------+-------+-------+
19 | num1  | num2  | num3  |
20 +-------+-------+-------+
21 1 row in set

區域性變數

作用域

declare用於定義區域性變數,在儲存過程和函式中通過declare定義變數在begin…end中,且在語句之前。並且可以通過重複定義多個變數

declare變數的作用範圍同程式設計裡面類似,在這裡一般是在對應的begin和end之間。在end之後這個變數就沒有作用了,不能使用了。這個同程式設計一樣。

使用

宣告語法
1 declare variable type [default default_value]

declare 變數名 變數型別,後面的 [ 預設值] 為可選;

賦值方式
1 set variable=value;
2 set variable:=value;
3 select variable:=value;
4 
5 或者
6 
7 select cname into variable from tname; 

注意自定義變數和區域性變數的區別,一個前面有@符號,一個沒有。

檢視變數的值

1 select variable

實踐一下

 1 mysql> 
 2 /*這邊宣告指令碼的結束符為// */
 3 DELIMITER //
 4 DROP PROCEDURE IF EXISTS sp_avg;
 5 CREATE PROCEDURE sp_avg()
 6 BEGIN
 7   /*宣告瞭一個區域性變數 avg_score*/
 8   DECLARE avg_score int;
 9   select AVG(score) into avg_score from students;
10   select avg_score;
11   -- Todo
12 END //
13 /*重置指令碼的結束符為; */
14 DELIMITER ;
15 Query OK, 0 rows affected
16 
17 mysql>
18 /*呼叫儲存過程*/
19 call sp_avg();
20 +-----------+
21 | avg_score |
22 +-----------+
23 |        87 |
24 +-----------+
25 1 row in set
26 
27 Query OK, 0 rows affected

 


 變數型別作用域定義位置語法格式
使用者變數 當前會話都有效 會話的任一地方 @符號,無需指定型別
區域性變數 所屬定義的begin end之間 begin...end中的第一個位置,緊跟在begin後面 不加@符號,需指定型別

 

delimiter 關鍵字的使用 

簡介

delimiter是mysql分隔符,在mysql客戶端中分隔符預設是分號;。如果一次輸入的語句較多,並且語句中間有分號,這時需要新指定一個特殊的分隔符。

其實就是告訴mysql直譯器,該段命令是否已經結束了,mysql是否可以執行了。預設情況下,delimiter是分號;。在命令列客戶端中,如果有一行命令以分號結束,那麼回車後,mysql將會執行該命令。

詳細解釋: 

其實就是告訴mysql直譯器,該段命令是否已經結束了,mysql是否可以執行了。 

預設情況下,delimiter是分號;。在命令列客戶端中,如果有一行命令以分號結束, 那麼回車後,mysql將會執行該命令。如輸入下面的語句 :

1 mysql> select * from tname;  

然後回車,那麼MySQL將立即執行該語句。

使用

但有時候,不希望MySQL這麼做。在為可能輸入較多的語句,且語句中包含有分號。 這種情況下,就需要事先把delimiter換成其它符號,如//、$$或者;;。

更改結束標誌的定義如下:

1 mysql>delimiter // 

舉個例子:建立一個儲存過程,在建立該儲存過程之前,將delimiter分隔符轉換成符號“//”,最後在轉換回符號“;”。

1 /*將結束標誌符更改為// */
2 delimiter //
3 /*建立函式或儲存過程*/
4 -- Todo,這邊寫下你的sql語句
5 end //
6 /*重置指令碼的結束符為; */
7 delimiter ; 

上面就是,先將分隔符設定為 //, 直到遇到下一個 //,才整體執行語句。

執行完後,最後一行, delimiter ; 將mysql的分隔符重新設定為分號;

如果不修改的話,本次會話中的所有分隔符都以// 為準。 

儲存過程操作

儲存過程的操作包含建立

建立儲存過程

1 create procedure 儲存過程名([引數模式] 引數名 引數型別)
2 begin
3     儲存過程體
4 end

引數模式有3種:

in:該引數可以作為輸入,也就是該引數需要呼叫方傳入值。

out:該引數可以作為輸出,也就是說該引數可以作為返回值。

inout:該引數既可以作為輸入也可以作為輸出,也就是說該引數需要在呼叫的時候傳入值,又可以作為返回值。

引數模式預設為IN,一個儲存過程可以有多個輸入、多個輸出、多個輸入輸出引數。

所以建立儲存過程的時候引數可能存在一下幾種情況:

無參情況

編寫儲存過程

 1 /*設定結束符設定為// */
 2 DELIMITER //
 3 /*儲存過程如果存在先刪除*/
 4 DROP PROCEDURE IF EXISTS sp_test1;
 5 /*建立無引數儲存過程sp_test1*/
 6 CREATE PROCEDURE sp_test1()
 7 BEGIN
 8 update students set score = (score+1) where studentname='lala';
 9 END //
10 /*將結束符重新設定為;*/
11 DELIMITER; 

呼叫實現:對比資料可確定呼叫成功

 1 mysql> select *  from students;
 2 +-----------+-------------+-------+---------+
 3 | studentid | studentname | score | classid |
 4 +-----------+-------------+-------+---------+
 5 |         1 | brand       | 97.5  |       1 |
 6 |         2 | helen       | 96.5  |       1 |
 7 |         3 | lyn         | 96    |       1 |
 8 |         4 | sol         | 97    |       1 |
 9 |         7 | b1          | 81    |       2 |
10 |         8 | b2          | 82    |       2 |
11 |        13 | c1          | 71    |       3 |
12 |        14 | c2          | 72.5  |       3 |
13 |        19 | lala        | 53    |       0 |
14 +-----------+-------------+-------+---------+
15 9 rows in set
16 
17 mysql> call sp_test1();
18 Query OK, 1 row affected
19 
20 mysql> select *  from students;
21 +-----------+-------------+-------+---------+
22 | studentid | studentname | score | classid |
23 +-----------+-------------+-------+---------+
24 |         1 | brand       | 97.5  |       1 |
25 |         2 | helen       | 96.5  |       1 |
26 |         3 | lyn         | 96    |       1 |
27 |         4 | sol         | 97    |       1 |
28 |         7 | b1          | 81    |       2 |
29 |         8 | b2          | 82    |       2 |
30 |        13 | c1          | 71    |       3 |
31 |        14 | c2          | 72.5  |       3 |
32 |        19 | lala        | 54    |       0 |
33 +-----------+-------------+-------+---------+
34 9 rows in set
帶in引數

編寫儲存過程:

 1 /*設定結束符為// */
 2 DELIMITER //
 3 /*儲存過程如果存在先刪除*/
 4 DROP PROCEDURE IF EXISTS sp_test2;
 5 /*建立儲存過程sp_test2*/
 6 CREATE PROCEDURE sp_test2(sname varchar(20),score DECIMAL(10,2),classid int)
 7 BEGIN
 8 INSERT INTO students(studentname,score,classid) VALUES (sname,score,classid);
 9 END //
10 /*將結束符重新置為;*/
11 DELIMITER ; 

呼叫實現:

 1 mysql> set @uname='wzh1',@score=100,@classid=8;
 2 call sp_test2(@uname,@score,@classid);
 3 Query OK, 0 rows affected
 4 
 5 Query OK, 1 row affected
 6 
 7 mysql> select * from students;
 8 +-----------+-------------+-------+---------+
 9 | studentid | studentname | score | classid |
10 +-----------+-------------+-------+---------+
11 |         1 | brand       | 97.5  |       1 |
12 |         2 | helen       | 96.5  |       1 |
13 |         3 | lyn         | 96    |       1 |
14 |         4 | sol         | 97    |       1 |
15 |         7 | b1          | 81    |       2 |
16 |         8 | b2          | 82    |       2 |
17 |        13 | c1          | 71    |       3 |
18 |        14 | c2          | 72.5  |       3 |
19 |        19 | lala        | 54    |       0 |
20 |        20 | wzh1        | 100   |       8 |
21 +-----------+-------------+-------+---------+
22 10 rows in set 
帶out引數

編寫儲存過程

 1 /*設定結束符為// */
 2 DELIMITER //
 3 /*如果儲存過程存在則刪除*/
 4 DROP PROCEDURE IF EXISTS sp_test3;
 5 /*建立儲存過程sp_test2*/
 6 CREATE PROCEDURE sp_test3(sname varchar(20),score DECIMAL(10,2),classid int,out lastid int)
 7 BEGIN
 8 INSERT INTO students(studentname,score,classid) VALUES (sname,score,classid);
 9 select lastid = @@identity;
10 END //
11 /*將結束符重新置為;*/
12 DELIMITER ;

呼叫實現

 1 mysql> set @uname='wzh3',@score=104,@classid=10;
 2 call sp_test3(@uname,@score,@classid,@lastid);
 3 select @lastid;
 4 Query OK, 0 rows affected
 5 
 6 Query OK, 1 row affected
 7 
 8 +---------+
 9 | @lastid |
10 +---------+
11 |      22 |
12 +---------+
13 1 row in set
14 
15 mysql> select * from students;
16 +-----------+-------------+-------+---------+
17 | studentid | studentname | score | classid |
18 +-----------+-------------+-------+---------+
19 |         1 | brand       | 97.5  |       1 |
20 |         2 | helen       | 96.5  |       1 |
21 |         3 | lyn         | 96    |       1 |
22 |         4 | sol         | 97    |       1 |
23 |         7 | b1          | 81    |       2 |
24 |         8 | b2          | 82    |       2 |
25 |        13 | c1          | 71    |       3 |
26 |        14 | c2          | 72.5  |       3 |
27 |        19 | lala        | 54    |       0 |
28 |        20 | wzh1        | 100   |       8 |
29 |        21 | wzh2        | 101   |       9 |
30 |        22 | wzh3        | 104   |      10 |
31 +-----------+-------------+-------+---------+
32 12 rows in set 
帶inout引數

自己試試吧,小夥子們

呼叫儲存過程

1 call 儲存過程名稱(引數列表);

注意:呼叫儲存過程關鍵字是call

如上所示 ,所有的call都是這樣的額

刪除儲存過程

1 drop procedure [if exists] 儲存過程名稱;

 儲存過程只能一個個刪除,不能批量刪除。

if exists:表示儲存過程存在的情況下刪除,我們上面演示的儲存過程都是判斷如果存在就先刪除。

修改儲存過程

儲存過程不能修改,若涉及到修改的,可以先刪除,然後重建。

檢視儲存過程

1 show create procedure 儲存過程名稱;

可以檢視儲存過程詳細建立語句。

 1 mysql> show create procedure sp_test3;
 2 +-----------+------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
 3 | Procedure | sql_mode                                                                                                               | Create Procedure                                                                                                                                                                                                                                  | character_set_client | collation_connection | Database Collation |
 4 +-----------+------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
 5 | sp_test3  | STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION | CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_test3`(sname varchar(20),score DECIMAL(10,2),classid int,out lastid int)
 6 BEGIN
 7 INSERT INTO students(studentname,score,classid) VALUES (sname,score,classid);
 8 select LAST_INSERT_ID() into lastid;
 9 END | utf8                 | utf8_general_ci      | utf8_general_ci    |
10 +-----------+------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
11 1 row in set

小結

儲存過程的優點開篇已經說過了,這邊就不贅述了,個人使用的最大感觸是,儘量不要在應用程式碼中寫大量的指令碼邏輯,做成儲存過程或者函式會更高效簡潔且易於維護。