概述
大多數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
小結
儲存過程的優點開篇已經說過了，這邊就不贅述了，個人使用的最大感觸是，儘量不要在應用程式碼中寫大量的指令碼邏輯，做成儲存過程或者函式會更高效簡潔且易於維護。