存儲過程

概念：

存儲過程時事先編譯并存儲在數據庫中的一段SQL語句的集合。

調用存儲過程可以簡化應用開發人員的很多工作，減少數據在數據庫和應用服務器之間的傳輸，對于提高數據處理的效率是有好處的。

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

特點：

1. 封裝，復用。
2. 可以接收參數，也可以返回數據
3. 減少網絡交互，效率提升

一、基本語法

(1)創建存儲過程

create procedure 存儲過程名稱([參數列表])
begin-- sql語句
end;

(2)調用存儲過程

call 存儲過程名稱([參數列表])

(3)查看存儲過程

-- 查詢指定數據庫的存儲過程及狀態信息
select * from information_schema.routines where routine_schema = 'xxx';-- 查詢某個存儲過程的定義
show create procedure 存儲過程名稱;

• information_schema.routines：系統視圖，存儲了數據庫中所有存儲過程和函數的元數據。

• routine_schema = 'xxx'：過濾條件，xxx需替換為目標數據庫名稱，用于指定查詢哪個數據庫的存儲過程。

(4)刪除存儲過程

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

(5)設置結束符

sql默認的結束符是分號；

但是存儲過程中的SQL語句每一句結束都是分號；

導致存儲過程無法正確執行。

這時候就需要修改結束的語句

delimiter $$

**但是自此以后，所有的sql語句都會需要$$**結束

CREATE PROCEDURE get_student(IN student_id INT)
BEGIN-- 內部SQL語句仍用;結束SELECT * FROM students WHERE id = student_id;SELECT COUNT(*) FROM scores WHERE student_id = student_id;
END $$  -- 這里用$$表示存儲過程定義結束（與修改后的結束符一致）

#改回分號結束
delimiter ;

(6)參數

類型	含義	備注
in	該類參數作為輸入，也就是需要調用時傳入值	默認
out	該類參數作為輸出，也就是該參數可以作為返回值
inout	既可以作為輸入參數，可以作為輸出參數

1. in/out:

傳值和返回值；

-- 案例
-- 1.根據傳入參數score，判定當前分數對應的分數等級，并返回。
-- score >= 85分，等級為優秀。
-- score >= 60分 且 score < 85分，等級為優秀。
-- score < 60分，等級為優秀。
create procedure p1(in score int,out result varchar(10))
beginif score >= 85 thenset result := '優秀';elseif score >= 60 thenset result := '及格';elseset result := '不及格';end if;
end;call p4(60,@result);

之后 select @result 就是’及格’了

2. inout:

即將傳入的值進行加工，改變傳入值

-- 案例
-- 2.將傳入的200分制的分數，進行換算，換算成百分制，然后返回分數 ---> inout
create procedure p2(inout score double)
beginset score := score * 0.5;
end;set @score = 200;
call p5(@score);
select @score; -- 輸出為100

二、變量

(1)系統變量

系統變量是MySQL服務器提供，不是用戶定義的，屬于服務器層面。分為全局變量(global)、會話變量(seesion).

1.查看系統變量

不指定，默認選擇session

show [session|global] variables;				-- 查看所有系統變量
show [session|global] variables like '......';	-- 可以通過like模糊匹配方式查找變量，如'auto%'
select @@[session|global].系統變量名;			 -- 查看指定變量的值

2.設置系統變量

設置系統變量后，重啟服務器會重新變為默認值；

如果 不想消失需要在/etc/my.cnf中配置

set [session|global] 系統變量名 = 值;
set @@[session|global].系統變量名；

(2)用戶自定義變量

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

• 賦值

set @變量名 = 值;
set @變量名 := 值;select @變量名 := 值;
select 字段名 into @變量名 from 表名;

• 使用

select @變量名;

注意：

用戶定義的變量無需對齊進行聲明或初始化，只不過獲取到的值為NULL。

(3)局部變量

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

• 聲明

declare 變量名 變量類型[default...];

變量類型就是數據庫字段類型：int、bigint、char、varchar、date、time等

• 賦值

set 變量名 = 值;
set 變量名 := 值;
select 字段名 into 變量名 from 表名...;

-- 例子
create procedure p1()
begindeclare stu_count int default 0;
end;

三、基本語句

(1)if判斷

1.語法

if 條件1 then.....
elseif 條件2 then  -- 可選.....
else			   -- 可選.....
end if;

2.練習

-- 根據定義的分數score變量，判斷當前分數對應的分數等級。
-- 1.score >= 85分，等級為優秀。
-- 2.score >= 60分 且 score < 85分，等級為優秀。
-- 3.score < 60分，等級為優秀create procedure p1()
begindeclare score int default 58;declare result varchar(10);if score >= 85 thenset result := '優秀';elseif score >= 60 thenset result := '及格';elseset result := '不及格';end if;select resultl;
end;

(2)case

1.語法

• 語法一

case case_valuewhen when_value then 語句1;[when when_value then 語句2;][else 語句3];
end case;

when條件都不符合，就會進入else。

• 語法二

casewhen 條件判斷 then 語句1;[when 條件判斷 then 語句2;][else 語句3];
end case;

2.練習

-- 根據傳入的月份，判定月份所屬的季節（要求采用case結構）。
-- 1.1-3月份，為第一季度
-- 2.4-6月份，為第二季度
-- 3.7-9月份，為第三季度
-- 4.10-12月份，為第四季度create procedure p1(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 := '第四季度';elseset result := '非法參數';end case;select concat('您輸入的月份為：',month,'所屬季度為：',result);end

(3)while循環

1.語法

while循環是有條件的循環控制語句。滿足條件后，再執行循環體中的SQL語句。

-- 先判斷條件，如果條件為true，則執行邏輯，否則，不執行邏輯
while 條件 doSQL邏輯....
end while;

2.練習

-- 計算1累加到n的值，n為傳入的參數值。
create procedure p1(in n int)
begindeclare total int default 0;while n>0 doset total := total + n;set n := n - 1;end whileselect total;
end;

(4)repeat

1.語法

repeat是有條件的循環控制語句，當滿足條件的時候退出循環。

-- 先執行一次邏輯，然后判斷邏輯是否滿足，如果滿足，則退出。如果不滿足，則繼續下一次循環
repeatSQL邏輯....until 條件
end repeat;

2.練習

-- 計算1累加到n
create procedure p2(in n int)
begindeclare total int default 0;repeatset total := n + total;set n := n - 1;until n <= 0end repeat;select total;
end;

(5)loop循環

1.語法

loop實現簡單的循環，如果不在SQL邏輯中增加退出循環的條件，可以用其實現簡單的死循環。

loop可以配合以下兩個語句使用：

• leave：配合循環使用，退出循環。（跳出循環必用）

• iterate：必須用在循環中，作用是跳過當前循環剩下的語句，直接進入下一次循環。

[begin_label:] loopSQL邏輯
end loop [end_label]

-- label有如下幾種：
leave label; -- 退出指定標記的循環體
iterate label -- 直接進入下一次循環

2.練習

了解如何跳出循環、了解如何跳過當前循環進入下一次循環

(1)了解如何跳出循環leave

-- 1.計算從1累加到n的值，n為傳入的參數值
create procedure p3(in n int)
begindeclare total int default 0;sum:loopif n<0 thenleave sum;end if;set total := total + n;set n := n-1;end loop sum;select total;
end;

(2)了解如何跳過當前循環進入下一次循環(iterate)

-- 2.計算從1到n之間的偶數累加的值，n為傳入的參數值
create procedure p4(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;

四、游標

(1)基本說明

游標（cursor）是用來存儲查詢結果集的數據類型，在存儲過程和函數中可以使用游標對結果集進行循環的處理。游標的使用包括游標的聲明、open、fetch和close。

游標需要聲明在普通變量之后，否則會報錯

-- 聲明游標
declare 游標名稱 cursor for 查詢語句;
-- 打開游標
open 游標名稱;
-- 獲取游標記錄
fetch 游標名稱 into 變量[,變量...];
-- 關閉游標
close 游標名稱;

(2)案例

根據傳入的參數uage，來查詢用戶表tb_user中，所有的用戶年齡小于等于uage的用戶的姓名（name）和專業（profession），并將用戶的姓名和專業插入到所創建的一張新表(id,name,profession)中。

-- 邏輯
-- 1.聲明游標，存儲查詢結果集
-- 2.準備：創建表結構
-- 3.開啟游標（open）
-- 4.獲取游標中的記錄（fetch）
-- 5.插入數據到新表中
-- 6.關閉游標（close）

create procedure p6(in max_age int)
begin-- 聲明變量接收數據declare uname varchar(50) default null;declare uprofession varchar(50) default null;-- 1.聲明游標，存儲查詢結果集declare u_cursor cursor for select name,profession from tb_user where age<max_age;-- 2.準備：創建表結構drop table if exists tb_user_pro;create table if not exists tb_user_pro(id int primary key auto_increment,name varchar(50),profession varchar(50));-- 3.開啟游標open u_cursor;while true do-- 4.獲取游標中的記錄fetch u_cursor into uname,uprofession;-- 5.插入數據到新表insert into tb_user_pro(name,age) values (uname,uage);end while;-- 6.關閉游標close u_cursor;
end;

以上可以很容易看出，while的條件是true，一直為真，會導致fetch取到空數據從而報錯，為了解決這個問題，我們有了條件處理程序；

-- 定義條件處理程序
declare exit handler for SQLSTATE '02000' close u_cursor; 
-- exit放入while中即可

五、條件處理程序

條件處理程序（Handler）可以用來定義在流程控制結構執行過程中遇到問題時相應的處理步驟。

declare handler_action handler for conditon_value [,conditon_value]... satement;handler_acion：continue -- 繼續執行當前程序exit -- 終止執行當前程序
conditon_value:sqlstate 狀態碼 -- 如:sqlstate 2000sqlwarning 	-- 所有01開頭的sqlstate代碼的簡寫not found		-- 所有02開頭的sqlstate代碼的簡寫sqlexception	-- 所有沒有被sqlwarning和notfound捕獲的sqlstate代碼的簡寫

#狀態碼為02000
declare exit handler for SQLSTATE '02000' close u_cursor;#當狀態為02開頭語句時
declare exit handler for not found colse u_cursor;

六、存儲函數

(1)基本說明

存儲函數是有返回值的存儲過程，存儲函數的參數只能是in類型的。

能夠使用存儲函數的地方都可以用存儲過程替代

create function 存儲函數名稱([參數列表])
returns  返回類型
[characteristic...] -- 特性名稱
begin-- SQL語句return...;
end;-- characteristic說明:
deterministic  -- 相同輸入參數總是產生相同的結果
not sql		-- 不包含sql語句
reads sql data	-- 包含讀取數據的語句，但不包含寫入數據的語句

如果不加特性characteristic，會報錯，如下圖：

(2)案例

-- 計算從1累加到n的值，n為傳入的參數值
create function fun1(n int)
returns int deterministic
begindeclare total int default 0;while n>0 doset total := total+n;set n := n-1;end while;return total;end;select fun1(100);