mysql儲存過程整理

lensener發表於2021-08-13

MySql儲存過程

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還不支援，暫未驗證)

mysql 儲存過程
2024-05-17
MySql儲存過程
MySQL之儲存過程
2018-04-17
MySql儲存過程
MySQL---------儲存過程
2024-07-23
MySql儲存過程
Mysql 儲存過程的使用
2021-08-05
MySql儲存過程
mysql如何呼叫儲存過程
2021-09-11
MySql儲存過程
MySQL儲存過程 (即函式)
2019-01-24
MySql儲存過程函式
mySql 儲存過程與函式
2024-05-24
MySql儲存過程函式
Laravel 中使用 MySQL 儲存過程
2019-04-22
LaravelMySql儲存過程
MySql資料庫——儲存過程
2021-09-09
MySql資料庫儲存過程
mysql儲存過程的引數
2021-09-11
MySql儲存過程
MySQL 儲存過程和函式
2022-04-23
MySql儲存過程函式
MySQL儲存過程和函式
2020-12-12
MySql儲存過程函式
mysql和orcale的儲存過程和儲存函式
2018-06-04
MySql儲存過程儲存函式
MySQL儲存過程語句及呼叫
2018-11-30
MySql儲存過程
MySQL儲存過程中如何使用ROLLBACK
2024-05-15
MySql儲存過程
Mysql 5.7儲存過程的學習
2019-07-31
MySql儲存過程
MySQL--儲存過程與檢視
2019-05-13
MySql儲存過程
mysql 儲存過程 procedure 批次建表
2023-01-04
MySql儲存過程
MySQL儲存過程的建立和使用
2020-12-31
MySql儲存過程
MySQL入門--儲存過程(PROCEDURE)和儲存函式(FUNCTION)
2019-05-30
MySql儲存過程儲存函式Function
MySQL入門系列：儲存程式（三）之儲存過程簡介
2019-03-21
MySql儲存過程
MySQL自定義函式與儲存過程
2019-04-13
MySql函式儲存過程
Mysql儲存過程基礎（案例+程式碼）
2018-09-07
MySql儲存過程
Mysql儲存過程中使用多遊標
2018-08-27
MySql儲存過程
mysql檢視儲存過程show procedure status;
2020-04-04
MySql儲存過程
MySQL 儲存過程進行切換表
2020-04-21
MySql儲存過程
[MySQL光速入門]015 聊聊儲存過程
2019-04-18
MySql儲存過程
23. 使用MySQL之使用儲存過程
2024-11-10
MySql儲存過程
MySQL的寫入資料儲存過程
2022-06-10
MySql儲存過程
mysql多次呼叫儲存過程的問題
2021-09-09
MySql儲存過程
MySQL全面瓦解16：儲存過程相關
2021-01-18
MySql儲存過程
MySQL-09-mysql 儲存過程入門介紹
2024-04-13
MySql儲存過程
Oracle儲存過程乾貨（一）：儲存過程基礎
2024-03-05
Oracle儲存過程
SQL 儲存過程裡呼叫另一個儲存過程
2021-04-03
SQL儲存過程
儲存過程與儲存函式
2020-11-16
儲存過程儲存函式
SQLSERVER儲存過程
2024-05-20
SQLServer儲存過程
呼叫儲存過程
2024-03-16
儲存過程
unidac儲存過程
2024-12-07
儲存過程