mysql儲存過程梳理
0.前言-為什麼要使用儲存過程
在做一些複雜業務邏輯時，如果程式與資料庫進行多次互動，會增加連線資源的消耗，增加整個業務邏輯處理的時間，儲存過程能夠減少程式與資料庫的互動次數，節省連線資源，加快處理速度。
以下示例均在8.0.21版本下測試成功。
1.優缺點
1.1 優點
-
儲存過程能夠減少程式與資料庫的互動次數，節省連線資源，加快處理速度
-
建立時會先編譯，後續的呼叫都不需要再次編譯
-
生產環境中，可以通過直接修改儲存過程的方式修改業務邏輯，而不用重啟伺服器
1.2 缺點
- 過程化編譯，維護成本高，尤其是在一些複雜的業務邏輯中
- 測試不方便，無法debug
- 不同資料庫之間可移植性差，語法不一樣
2.基本語法
2.1 基本關鍵字
2.1.1 delimiter
宣告結束符為:
delimiter
告訴mysql直譯器，該段命令是否已經結束了，mysql是否可以執行了。
預設情況下，delimiter是分號;。在命令列客戶端中，如果有一行命令以分號結束，
那麼回車後，mysql將會執行該命令。如輸入下面的語句
mysql> select * from test_table;
然後回車，那麼MySQL將立即執行該語句。
但有時候，不希望MySQL這麼做。在為可能輸入較多的語句，且語句中包含有分號
例如下面的意思為，以$$作為結束符號,表示這個儲存過程的結尾
delimiter $$
2.1.2 create
建立一個儲存過程
CREATE PROCEDURE
2.1.3 drop
刪除一個儲存過程
drop procedure pro_name
2.1.4 begin|end
邏輯程式碼塊
-- 開始
BEGIN
...
-- 結束
END
2.2 語法結構
2.2.1 示例
DELIMITER $$
CREATE
PROCEDURE demo_procedure()
BEGIN
select 'hello procedure';
END $$
DELIMITER ;
call demo_procedure();
2.2.2 總體結構
[]內的引數為可選引數
CREATE
[DEFINER = user]
PROCEDURE sp_name ([proc_parameter[,...]])
[characteristic ...] routine_body
-- proc_parameter 引數部分，可以如下書寫:
[ IN | OUT | INOUT ] param_name type
-- type型別可以是MYSQL支援的所有型別
-- toutine_body(程式體)部分，可以書寫合法的SQL語句 ---- BEGIN ... END
2.2.3 routine_body(程式碼體)結構
BEGIN
...
END $$
2.3 變數
2.3.1 宣告與賦值
2.3.1.1 declare 宣告
宣告變數 declare var_name type[default var_value]
舉例: declare test_name varchar(32);
2.3.1.2 set 賦值
set test_name = '張三';
2.3.1.3 into賦值
select '張三' into test_name
完整示例
DELIMITER $$
CREATE PROCEDURE test_var_procedure()
BEGIN
declare test_name varchar(32) default '李四';
set test_name = '張三';
select test_name;
END $$
DELIMITER ;
call test_var_procedure();
2.3.2 區域性變數與使用者變數
2.3.2.1 區域性變數
使用者自定義，在begin-end塊中使用
語法:
宣告變數 declare var_name type[default var_value]
舉例: declare test_name varchar(32);
-- set賦值
DELIMITER $$
CREATE PROCEDURE test_procedure()
BEGIN
declare test_name varchar(32);
set test_name = '張三';
select test_name;
END $$
DELIMITER ;
2.3.2.2 使用者變數
使用者自定義，當前會話(連線)有效
語法:
@test_name
不需要提前宣告，使用即宣告
DELIMITER $$
CREATE PROCEDURE test_procedure()
BEGIN
set @test_name = '張三';
select @test_name;
END $$
DELIMITER ;
call test_procedure();
-- 同一會話中直接查詢也能查詢出來
select @test_name
2.4 入參、出參
2.4.1 入參
IN表示入參，需要傳入到儲存過程中的引數
DELIMITER $$
CREATE PROCEDURE test_procedure(IN age int,IN name varchar(32))
BEGIN
set @age = age;
set @name = name;
select @age,@name;
END $$
DELIMITER ;
call test_procedure(18,'張三');
2.4.2 出參
OUT表示出參
DELIMITER $$
CREATE PROCEDURE test_procedure(IN p_name varchar(32),OUT age int)
BEGIN
SELECT 18 INTO age;
-- set age = 19;
END $$
DELIMITER ;
call test_procedure('張三',@age);
SELECT @age;
2.4.3 出入參
INOUT
DELIMITER $$
CREATE PROCEDURE test_procedure(INOUT age int)
BEGIN
SELECT 18 INTO age;
-- set age = 19;
END $$
DELIMITER ;
-- 通過變數傳參進，再通過同一個變數出
set @age = 20;
call test_procedure(@age);
SELECT @age;
2.5 流程控制
寫流行控制的時候，優先把結構寫好，再補齊內部邏輯，比如寫IF後緊跟著應該先把END IF寫好，邏輯再補在中間，防止遺漏END。
2.5.1 判斷IF
-- 語法
IF condition THEN statement_list
[ELSEIF condition THEN statement_list] ...
[ELSE statement_list]
END IF
示例:
DELIMITER $$
CREATE PROCEDURE test_procedure(IN p_name VARCHAR(32),OUT o_age int)
BEGIN
IF p_name = '張三'
THEN set o_age = 18;
ELSE
set o_age = 19;
END IF;
END $$
DELIMITER ;
call test_procedure('李四',@age);
SELECT @age;
2.5.2 CASE
語義類似於java中的switch..case，CASE也在sql語句中常用到
語法：
-- 語法一
CASE case_value
WHEN when_value THEN statement_list
[WHEN when_value THEN statement_list]
...
[ELSE statement_list]
END CASE
-- 語法二
CASE
WHEN search_condition THEN statement_list
[WHEN search_condition THEN statement_list]
...
[ELSE statement_list]
END CASE
示例:
DELIMITER $$
CREATE PROCEDURE demo_procedure(IN name varchar(32),OUT age INT)
BEGIN
CASE name
WHEN '張三'
THEN set age = 12;
ELSE set age = 18;
END CASE
CASE
WHEN name = '李四'
THEN set age = 20;
ELSE set age = 18;
END CASE;
END $$
DELIMITER ;
call demo_procedure('張三',@age);
select @age;
2.5.3 迴圈LOOP
-- 語法
[begin_label:] LOOP
statement_list
END LOOP [end_label]
begin_label:給迴圈宣告一個變數，這個變數指向這個迴圈體
舉例
需要說明，loop是死迴圈，需要手動退出迴圈，我們可以使用leave來退出。
可以把leave看成我們java中的break；與之對應的，就有iterate--類比java的continue
-- 迴圈列印1-10
DELIMITER $$
CREATE PROCEDURE demo_procedure()
BEGIN
-- 宣告一個下標變數，初始值為1
DECLARE demo_index INT DEFAULT 1;
demo_loop:LOOP
-- 若大於10，則跳出迴圈
IF demo_index >10
THEN LEAVE demo_loop;
END IF;
select demo_index;
set demo_index = demo_index + 1;
END LOOP demo_loop;
END $$
DELIMITER ;
call demo_procedure();
2.5.4 跳出LEAVE
Leave語句表明退出指定標籤的流程控制語句塊，通常會用在begin…end，以及loop, repeat, while的迴圈語句
-- 迴圈列印1-10
DELIMITER $$
CREATE PROCEDURE demo_procedure()
BEGIN
-- 宣告一個下標變數，初始值為1
DECLARE demo_index INT DEFAULT 1;
demo_loop:LOOP
-- 若大於10，則跳出迴圈
IF demo_index >10
THEN LEAVE demo_loop;
END IF;
select demo_index;
set demo_index = demo_index + 1;
END LOOP demo_loop;
END $$
DELIMITER ;
call demo_procedure();
2.5.5 ITERATE
類比於java中的continue
DELIMITER $$
CREATE PROCEDURE demo_procedure()
BEGIN
-- 列印到10的時候才跳出，攔截列印到2的時候跳出
DECLARE num INT DEFAULT 1;
demo_loop: LOOP
SELECT num;
set num = num +1;
IF num <10
THEN ITERATE demo_loop;
END IF;
IF num =3 or num = 11
THEN LEAVE demo_loop;
END IF;
END LOOP demo_loop;
END $$
DELIMITER ;
call demo_procedure();
2.5.6 REPEAT
類似於java的do..while
-- 語法
[demo_label:] REPEAT
statement_list
UNTIL search_condition -- 直到...為止
END REPEAT [demo_label]
start=>start: start
statement=>operation: statement
expressions=>condition: expressions
stop=>end: stop
start->statement->expressions
expressions(false)->statement
expressions(true)->stop
示例:
DELIMITER $$
CREATE PROCEDURE demo_procedure()
BEGIN
-- 迴圈列印1-10
DECLARE num INT DEFAULT 1;
demo_repeat: REPEAT
SELECT num;
set num = num + 1;
UNTIL num>10
END REPEAT demo_repeat;
END $$
DELIMITER ;
call demo_procedure();
2.5.7 WHILE
類似於java中的while
-- 語法
[demo_label:] WHILE demo_condition DO
statement_list
END WHILE [demo_lable]
示例:
-- 迴圈列印1-10
DELIMITER $$
CREATE PROCEDURE demo_procedure()
BEGIN
-- 迴圈列印1-10
DECLARE num INT DEFAULT 1;
demo_while: WHILE num <=10 DO
select num;
set num = num+1;
END WHILE demo_while;
END $$
DELIMITER ;
call demo_procedure();
2.6 遊標
遊標實際上是一種能從包括多條資料記錄的結果集中每次提取一條記錄的機制。遊標充當指標的作用。儘管遊標能遍歷結果中的所有行，但他一次只指向一行。遊標的作用就是用於對查詢資料庫所返回的記錄進行遍歷，以便進行相應的操作。
2.6.1 用法
-- 1.宣告一個遊標，這裡的demo_table可以是任意集合
DECLARE cur_name CURSOR FOR demo_table
-- 2.開啟定義的遊標
OPEN cur_name
-- 3.獲得下一行資料
FETCH cur_name INTO field_one,field_two,...
-- 4.釋放遊標
CLOSE cur_name
2.6.2 示例
-- 建表，造資料
-- name,age
-- 張三,18
-- 李四,19
-- 需求：將表中所有人的姓名和年齡拼接成一個字串：張三,18;李四,19;
CREATE TABLE `user_information` (
`name` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
`age` int DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
INSERT INTO user_information(name,age) VALUES ('張三',18);
INSERT INTO user_information(name,age) VALUES ('李四',19);
-- -------------------- 建立儲存過程----------------------
DELIMITER $$
-- 將使用者資訊拼接成一個字串 張三,18;李四,19；
CREATE PROCEDURE demo_procedure()
BEGIN
-- 臨時儲存姓名
DECLARE c_name VARCHAR(255);
-- 臨時儲存年齡
DECLARE c_age INT;
-- 存放拼接字串
DECLARE res VARCHAR(255) DEFAULT '';
DECLARE done INT DEFAULT 0;
-- 1.定義遊標
DECLARE demo_cursor CURSOR FOR select name,age from user_information;
-- 2.捕獲系統丟擲的 not found 錯誤，如果捕獲到，將 done 設定為 1 相當於try異常
DECLARE CONTINUE HANDLER FOR NOT found SET done=1;
-- 3.開啟遊標
OPEN demo_cursor;
demo_loop: LOOP
-- 注意FETCH的位置，FETCH是觸發not found的觸發點，但是done的判斷和java的catch異常有區別，
-- FETCH觸發異常的時候done已經為1了，但是done的IF判斷還沒有執行，要在下一次loop迴圈執行，所以一定要注意FETCH
-- 和done判斷的位置。
FETCH demo_cursor INTO c_name,c_age;
-- 若done=1，即發生not found異常，則結束迴圈
IF done=1
THEN LEAVE demo_loop;
END IF;
SET res = CONCAT(res,c_name,',',c_age,';');
END LOOP demo_loop;
SELECT res;
-- 釋放遊標(ps:網上很多文章說遊標未釋放會產生死鎖，但是我沒有復現-_-)
CLOSE demo_cursor;
END $$
DELIMITER ;
call demo_procedure();
2.7 儲存過程中的HANDLER
HANDLER的宣告必須要放在變數宣告、遊標宣告的後面
宣告順序依次為變數、遊標、HANDLER
異常抓取
-- 語法
-- 執行順序為，發生異常->抓取到condition_value的異常->執行statement語句->執行handler_action相應的動作
DECLARE handler_action HANDLER
FOR condition_value[,condition_value]...
statement
-- 常見handler_aciton
handler_action:{
CONTINUE|
EXIT|
UNDO
}
-- 常見的condition_value,condition_value也可以是指定的錯誤碼
{
mysql_error_code|
SQLSTATE [VALUE] sqlstate_value|
condition_name|
SQLWARNING|
NOT FOUND|
SQLEXCEPTION
}
-- CONTINUE 繼續往下執行程式
CONTINUE: Execution of the current program continues.
-- EXIT 退出，後面語句不執行
EXIT:Execution terminates for the BEGIN ... END compound statement in which
the handler is declared. This is true even if the condition occurs in an inner block.
-- UNDO 後面語句不執行，前面執行過的語句撤銷(據說mysql還不支援，暫未驗證)