?

存儲過程概述

存儲過程是事先經過編譯并存儲在數據庫中的一段 SQL 語句的集合，調用存儲過程可以簡化應用開發人員的很多工作，減少數據在數據庫和應用服務器之間的傳輸，提高數據處理的效率。

存儲過程思想上很簡單，就是數據庫 SQL 語言層面的代碼封裝與重用。

優點：

1. 減少網絡傳輸：在數據庫端執行，降低應用服務器與數據庫間的數據交互。

2. 性能提升：預編譯減少了重復解析和優化，提高執行效率

3. 簡化開發：封裝復雜邏輯，減少應用層代碼量，便于調用

1、基本語法

• 創建：

CREATE PROCEDURE 存儲過程名稱 ([參數列表]) BEGIN -- SQL語句 END;

舉例：

-- 創建
CREATE PROCEDURE p1()
BEGINSELECT COUNT(*) FROM student;END

運行結果：
可以看到在functions下多了一個p1函數

• 調用：

CALL 存儲名稱 ([參數]);

?舉例：

CALL p1();

運行結果：

• 查看

SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA = 'xxx'; -- 查詢指定數據庫的存儲過程及狀態信息
SHOW CREATE PROCEDURE 存儲過程名稱; -- 查詢某個存儲過程的定義

舉例：

-- 查詢指定數據庫的存儲過程及狀態信息
SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA = 'school_db';-- 查詢某個存儲過程的定義
SHOW CREATE PROCEDURE p1;

運行結果：

分別是兩條語句的運行結果：

• 刪除

DROP PROCEDURE [IF EXISTS] 存儲過程名稱;

舉例：
這樣就刪除成功了：

DROP PROCEDURE p1;

2、變量

①、系統變量

系統變量由Mysql服務器提供，不由用戶定義，屬于服務器層面，分為全局變量與會話變量。

全局變量：無論開多少個會話，變量值都是一樣的，
會話變量：只在當前會話生效的變量。

像在navicat中，我們點擊創建這三個query，就是三個不同的會話

查看語句：

-- 查看會話變量
SHOW SESSION VARIABLES;
-- 查看全局變量
SHOW GLOBAL VARIABLES;-- 模糊查詢
SHOW SESSION VARIABLES like 'auto%';
SHOW GLOBAL VARIABLES like 'auto%';-- 直接選擇具體的變量
SELECT @@global.autocommit;

查詢結果：

變量可以看到有很多。

模糊查詢結果：

具體查詢結果：

更改語句：

-- 設置會話變量
SET SESSION autocommit = 0;
-- 設置全局變量
SET GLOBAL autocommit = 0;

②、用戶自定義變量

用戶定義變量是用戶根據需要自己定義的變量，用戶變量不用提前聲明，在用的時候直接用“@變量名”使用就可以。其作用域為當前連接。

使用方式：

-- 設置變量SET @user_name := 'zhangsan';select COUNT(*) into @num from student;-- 使用變量SELECT @num, @user_name;

注意點：賦值時使用 := 來賦值，然后賦值的話，也可以將查詢的結果賦值給一個變量，如第二條查詢語句。最后如果要查看自定義變量則使用最后一條的語法查詢，查詢結果如下：

③、局部變量

局部變量是根據需要定義的在局部生效的變量，訪問之前，需要DECLARE聲明。可用作存儲過程中的局部變量和輸入參數，局部變量的范圍是在其聲明的BEGIN … END塊

使用方式：

CREATE PROCEDURE p2()
BEGIN-- 局部變量定義，前面為變量名，后面為變量類型，如int、varchar等。DECLARE stu_count int;-- 對變量賦值select COUNT(*) INTO stu_count from student;-- 查詢變量select stu_count;
END-- 調用存儲過程
CALL p2();

調用結果：

3、流程控制語句

這部分其實和編程語言的流程控制基本相同，分支、循環，傳參等，我們直接實戰來演示。

①、if語句

根據分數判斷是否合格。

-- if
CREATE PROCEDURE p3()
BEGINDECLARE score INT DEFAULT 58;DECLARE result VARCHAR(10);-- if語句使用IF score >= 85 THENSET result = '優秀';ELSEIF score >= 60 THENSET result = '合格';ELSESET result = '不合格';end IF;-- 查詢結果SELECT result;
END
-- 調用流程
CALL p3();

運行結果：

②、參數

只需要知道三個聲名參數的方法，分別是in、out、inout。

in用來聲名輸入參數，out用來聲名輸出參數，輸出參數一般由一個變量來接收。

-- 參數
-- in表示聲名輸入參數，out表示聲名返回結果
CREATE PROCEDURE p4(in score INT, out result VARCHAR(10))
BEGIN-- if語句使用IF score >= 85 THENSET result = '優秀';ELSEIF score >= 60 THENSET result = '合格';ELSESET result = '不合格';end IF;
END
-- 調用過程
CALL p4(58, @result);SELECT @result;

查詢結果：

inout表明輸入和輸出都是同一個變量。

-- 將數字換成百分制的。
CREATE PROCEDURE p5(inout score DOUBLE)
BEGINset score := score * 0.01
END;set @SCORE = 78
-- 調用過程
CALL p5(@SCORE);SELECT @SCORE;

運行結果：

③、case語句

根據月份判斷第幾季度。

-- case語句
CREATE PROCEDURE p6(in month INT)
BEGINdeclare result VARCHAR(10);casewhen month >= 1 and month <= 3 thenset result := '第一季度';when month >= 4 and month <= 6 thenset result := '第二季度';when month >= 7 and month <= 9 thenset result := '第三季度';when month >= 10 and month <= 12 thenset result := '第四季度';else set result := '非法參數';end case;select result;
END;CALL p6(7);

運行結果：

④、while語句

求n到1的累加值。

CREATE PROCEDURE p7(in n INT)
BEGINdeclare total INT DEFAULT 0;while n>0 doset total := total + n;set n := n - 1;end while;select total;
END;CALL p7(10);

運行結果：

⑤、repeat語句

repeat是有條件的循環退出語句，類似c語言的do while語句

CREATE PROCEDURE p8(in n INT)
BEGINdeclare total INT DEFAULT 0;repeatset total := total + n;set n := n - 1;until n <= 0end repeat;select total;
END;CALL p8(10);

運行結果：

⑥、loop語句

里面注意需要給loop代碼命個名，退出循環需要指定loop的名稱。 leave等同于c語言中的break, iterate 效果等同于c語言中的continue

需求：從n 到 1中所有偶數相加的和

CREATE PROCEDURE p9(in n INT)
BEGINdeclare total INT DEFAULT 0;sum:loopif n<=0 thenleave sum;end if;if n%2=1 thenset n := n - 1;iterate sum;end if;set total := total + n;set n := n - 1;end loop sum;select total;
END;CALL p9(10);

運行結果：

⑦、cursor游標

游標 (CURSOR) 是用來存儲查詢結果集的游標類型，在存儲過程和函數中可以使用游標來循環處理查詢結果集中的每行記錄。

游標可以存儲sql查詢的結果集合，而之前的參數只能傳單行單列的數據。

聲明游標：

DECLARE 游標名稱 CURSOR FOR 查詢語句;

打開游標：

OPEN 游標名稱;

獲取游標記錄：

FETCH 游標名稱 INTO 變量[變量];

關閉游標：

CLOSE 游標名稱;

需求：將查詢到的數據，存儲到新表tb_user_pro中，游標語法如下：

CREATE PROCEDURE p10(in uid INT)
BEGIN-- 定義游標與變量declare u_name varchar(50);declare u_gender varchar(50);declare u_cursor cursor for select stu_name,gender from student where stu_id <= uid;-- 創建測試表create table if not exists tb_user_pro(id int PRIMARY key auto_increment,name2 VARCHAR(50),gender VARCHAR(50));-- 開啟游標open u_cursor; WHile true dofetch u_cursor into u_name, u_gender; -- 獲取游標中的數據并賦值給變量insert into tb_user_pro(name2, gender) VALUES (u_name, u_gender); -- 執行插入語句end while;-- 關閉游標close u_cursor;
END;CALL p10(3);

運行結果：

可以看到，我們成功完成了功能，但是在執行的時候，有一些問題，在while true的時候，我們沒有設置跳出循環的邏輯，導致結果正確，但執行會報錯：

⑧、handler

條件處理程序 (Handler) 可以用來定義在流程控制結構過程中遇到問題時相應的處理步驟。具體語法為：

DECLARE handler_action HANDLER FOR condition_value [condition_value]... statement;handler_action
CONTINUE: 繼續執行當前程序
EXIT: 終止執行當前程序condition_value
SQLSTATE sqlstate_value: 狀態碼，如 02000
SQLWARNING: 所有以01開頭的SQLSTATE代碼的警告
NOT FOUND: 所有以02開頭的SQLSTATE代碼的警告SQLEXCEPTION: 所有沒有被SQLWARNING 或 NOT FOUND捕獲的SQLSTATE代碼的警告

這里我們直接基于上一個案例來解釋語法怎么用

我們在上一個案例的PROCEDURE中定義一個條件處理程序，在報錯時就會執行這個程序：

-- exit表示退出程序，
-- SQLSTATE '02000' 等價于 not found 
-- 該程序最后執行的語句為：close u_cursor
declare exit handler for SQLSTATE '02000' close u_cursor;

最后運行的時候，發現程序就不再報錯了，功能也正常了。

4、存儲函數

存儲函數是否有可能返回值的存儲過程，存儲函數的參數只能是IN類型的。具體語法如下：

CREATE FUNCTION 存儲函數名稱( [參數列表] )
RETURNS type [characteristic …]
BEGIN-- SQL語句RETURN …;
END;characteristic說明：DETERMINISTIC: 相同的輸入參數總是產生相同的結果
NO SQL: 不包含SQL語句。
READS SQL DATA: 包含讀取數據的語句，但不包含寫入數據的語句。

舉例說明，
功能：實現從n到1的累加：

create function fun1(n INT)
-- 必須指定返回類型 和 characteristic
returns int DETERMINISTICBEGINdeclare total INT default 0;while n > 0 doset total := total + n;set n := n - 1;end while;return total;
END;select fun1(100)

運行結果：