Mysql資料庫基礎第六章：變數、儲存過程與函式
本文已參與「新人創作禮」活動，一起開啟掘金創作之路
Mysql資料庫基礎系列
軟體下載地址 提取碼：7v7u 資料下載地址 提取碼：e6p9
1、變數
在mysql中，可以使用變數來儲存查詢或計算結果，類似python中的變數。在mysql中主要分為：系統變數和自定義變數
1.1 系統變數
系統變數是在啟動mysql服務時會給系統變數賦值，定義了當前mysql服務例項的一些特徵，例如在事務中的autocommit 系統變數分為： - 全域性變數（global）：全域性變數對所有的連線有效，但是重啟會失效。 - 會話變數（session，mysql預設）：會話變數，不能跨連線
1.1.1檢視系統變數
- 檢視所有或者部分系統變數 ```sql
檢視所有全域性變數
show global variables;
檢視所有會話變數
show variables;
檢視部分需要的變數
show variables like '%aa%'
- 檢視指定系統變數，使用@@sql
1.檢視指定系統變數的值
select @@global.autocommit;
2.檢視指定的會話變數的值
SELECT @@變數名;
- 修改系統變數值sql
set @@global.變數名 = 值;
set global 變數名=值
set @@session.變數名 = 值; set session 變數名=值 ```
1.2 自定義變數
自定義變數是使用者自己定義的，根據其使用範圍分為使用者變數和區域性變數
1.2.1 使用者變數
- 變數定義 ```sql
1. 使用set
set @使用者變數 = value;
2. 使用select
select @使用者變數 := 表示式;
select 表示式 Into @使用者變數
- 變數賦值sql
set @使用者變數 = value
- 變數使用sql
select @使用者變數
```
1.2.2 區域性變數
只能使用在begin end中，且只能放在第一個語句
- 變數定義
sql
declare 變數 型別 [default 預設值]
- 變數賦值
sql
set 變數名 = 值
select 值 into 變數名 from
- 變數使用
sql
select 區域性變數
案例：查詢兩個變數的和
1.使用使用者變數
sql
SET @m = 1;
SET @n = 1;
SET @sum = @[email protected];
SELECT @sum;
2.使用區域性變數
```sql
DELIMITER $
CREATE PROCEDURE sum_2()
BEGIN
DECLARE m INT DEFAULT 1;
DECLARE n INT DEFAULT 1;
DECLARE SUM INT;
SET SUM= m+n;
SELECT SUM;
END$
DELIMITER ;
```
1.2.3 區別
定義 作用域 定義位置
使用者變數 需要用@ 當前會話 會話中的任何位置
區域性變數 不需要@ 只用於begin-end中 begin-end的第一句話
2.儲存過程
類似於python中的方法，將一組預先編譯好的sql語句進行封裝。
好處:
-1、儲存過程可以一次編譯多次使用。儲存過程只在建立時進行編譯，之後的使用都不需要重新編譯，這就提升了 SQL 的執行效率。
-2、可以減少開發工作量。將程式碼 封裝 成模組，實際上是程式設計的核心思想之一，這樣可以把複雜的問題拆解成不同的模組，然後模組之間可以 重複使用 ，在減少開發工作量的同時，還能保證程式碼的結構清晰。
-3、儲存過程的安全性強。我們在設定儲存過程的時候可以 設定對使用者的使用許可權 ，這樣就和檢視一樣具有較強的安全性。
-4、可以減少網路傳輸量。因為程式碼封裝到儲存過程中，每次使用只需要呼叫儲存過程即可，這樣就減少了網路傳輸量。
-5、良好的封裝性。在進行相對複雜的資料庫操作時，原本需要使用一條一條的 SQL 語句，可能要連線多次資料庫才能完成的操作，現在變成了一次儲存過程，只需要 連線一次即可 。
缺點：
- 1、可移植性差。儲存過程不能跨資料庫移植，比如在 MySQL、Oracle 和 SQL Server 裡編寫的儲存過程，在換成其他資料庫時都需要重新編寫。
- 2、除錯困難。只有少數 DBMS 支援儲存過程的除錯。對於複雜的儲存過程來說，開發和維護都不容易。雖然也有一些第三方工具可以對儲存過程進行除錯，但要收費。
- 3、儲存過程的版本管理很困難。比如資料表索引發生變化了，可能會導致儲存過程失效。我們在開發軟體的時候往往需要進行版本管理，但是儲存過程本身沒有版本控制，版本迭代更新的時候很麻煩。
- 4、它不適合高併發的場景。高併發的場景需要減少資料庫的壓力，有時資料庫會採用分庫分表的方
式，而且對可擴充套件性要求很高，在這種情況下，儲存過程會變得難以維護， 增加資料庫的壓力 ，顯然就不適用了。
2.1 分類
儲存過程包含三類
1.沒有引數
2.in:引數輸入
3.out:引數輸出
4.inout:引數既可以輸入也可以輸出
2.2 語法
2.2.1 建立儲存過程
sql
create procedure 儲存過程名字(引數)
begin
sql語句
end
注意事項：
1.BEGIN…END：BEGIN…END 中間包含了多個語句，每個語句都以（;）號為結束符。
2.DECLARE：DECLARE 用來宣告變數，使用的位置在於 BEGIN…END 語句中間，而且需要在其他語句使用之前進行變數的宣告。
3. SET：賦值語句，用於對變數進行賦值。
4. SELECT… INTO：把從資料表中查詢的結果存放到變數中，也就是為變數賦值。
5.儲存過程的結尾可以使用 delimiter 重新設定
2.2.2 程式碼案例
1.不帶任何引數：查詢employees表中的所有資料 ```sql delimiter $ # 設定結束符 create procedure select_all() # 建立儲存過程 begin select * from employees; end $ # 結束 delimiter ;
call select_all(); #呼叫儲存過程
2.返回員工的平均工資sql
delimiter $ # 設定結束符
create procedure avg_salary() # 建立儲存過程
begin
select avg(salary) from employees;
end $ # 結束
delimiter ;
call avg_salary();
3.out型別的儲存過程：將employees表中最低工資通過ms引數輸出sql
delimiter $ # 設定結束符
create procedure min_salary(out ms double) # 建立out型別儲存過程，ms為引數
begin
select min(salary) into ms
from employees;
end $ # 結束
delimiter ;
call min_salary(@ms);
select @ms;
4.in型別的儲存過程,檢視某個員工的工資，引數enamesql
delimiter $ # 設定結束符
create procedure name_salary(in ename varchar(20)) # 建立in型別儲存過程，ename為引數
begin
select salary from employees
where last_name = ename;
end $ # 結束
delimiter ;
call name_salary('Abel');
5.同時帶in和out型別的儲存過程。（1）檢視某個員工的薪資（2）用out引數esalary輸出sql
delimiter $ # 設定結束符
create procedure name_salary2(in ename varchar(20), out esalary decimal(10,2)) # 建立in-out型別儲存過程，ename,esalary為引數
begin
select salary into esalary #存入到額salary中
from employees
where last_name = ename;
end $ # 結束
delimiter ;
call name_salary2('Abel',@esalary);
select @esalary;
6.帶inout型別的儲存過程,查詢某個員工的管理者名字sql
delimiter $ # 設定結束符
create procedure manager_name(inout ename varchar(20)) # 建立inout型別儲存過程，ename為引數
begin
select last_name into ename # 傳入到inout引數
from employees
where employee_id = (
select manager_id from employees
where last_name = ename
); # 使用子查詢得到管理者的id
end $ # 結束
delimiter ;
call manager_name('Abel',@ename);
select @ename; ```
2.2.3 呼叫儲存過程
- 格式 ```sql
1.in型別
call sp('值')
2.out型別
call sp(@name); select @name;
3.inout型別
set @name = '值';
call sp(@name);
select @name;
7.建立一個儲存過程，計算1+...+n的和sql
DELIMITER $ # 設定結束符
CREATE PROCEDURE sum_n(IN n INT ) # 建立in,n為引數
BEGIN
定義區域性變數
DECLARE i INT; DECLARE SUM INT; SET i = 1; SET SUM = 0; WHILE i<=n DO # 開啟迴圈 SET SUM = SUM + i; SET i = i + 1; END WHILE;#結束while迴圈 SELECT SUM; END $ # 結束 DELIMITER ; CALL sum_n(100); ```
3.函式
函式一組預先編譯好的SQL語句的集合，理解成批處理語句 - 優點： 1、提高程式碼的重用性 2、簡化操作 3、減少了編譯次數並且減少了和資料庫伺服器的連線次數，提高了效率
- 與儲存過程的區別： 儲存過程：可以有0個返回，也可以有多個返回，適合做批量插入、批量更新 函式：有且僅有1 個返回，適合做處理資料後返回一個結果
3.1函式建立
sql
create function func_name(引數) return 返回型別
[characteristics]
begin
函式語句
end
3.2注意事項：
1.引數列表 包含兩部分：
引數名 引數型別，函式預設是in引數
2.函式體：肯定會有return語句，如果沒有會報錯
如果return語句沒有放在函式體的最後也不報錯，但不建議
return 值;
3.函式體中僅有一句話，則可以省略begin end
4.使用 delimiter語句設定結束標記
3.3函式呼叫
函式呼叫方法和系統函式一樣，案例如下
1.空引數，名稱為email_by_name()，該函式查詢Abel的email，並返回，資料型別為字串型。
sql
delimiter $
create function email_by_name() returns varchar(20)
begin
return (select email from employees where last_name = 'Abel');
end $
delimiter ;
如果出現 you might want to use the less safe log_bin_trust_function_creators variable錯誤，將系統變數設定SET GLOBAL log_bin_trust_function_creators = 1;即可，也可以增加函式特性
2.名稱為email_by_id()，引數傳入emp_id，該函式查詢emp_id的email，並返回，資料型別為字串型。 ```sql
delimiter $
create function email_by_id(emp_id int) returns varchar(20)
begin
return (select email from employees where employee_id = emp_id);
end $
delimiter ;
select email_by_id(101);
3.建立儲存函式count_by_id()，引數傳入dept_id，該函式查詢dept_id部門的員工人數，並返回，資料型別為整型。sql
delimiter $
create function count_by_id(dept_id int) returns int
begin
return (select count(*) from employees where department_id = dept_id);
end $
delimiter ;
select count_by_id(50);
```
3.4 函式檢視
方式1. 使用SHOW CREATE語句檢視儲存過程和函式的建立資訊 ```sql SHOW CREATE PROCEDURE show_mgr_name;
SHOW CREATE FUNCTION count_by_id;
方式2. 使用SHOW STATUS語句檢視儲存過程和函式的狀態資訊sql
SHOW PROCEDURE STATUS;
SHOW PROCEDURE STATUS LIKE 'show_max_salary';
SHOW FUNCTION STATUS LIKE 'email_by_id';
```
3.5 函式刪除
```sql DROP FUNCTION IF EXISTS count_by_id;
DROP PROCEDURE IF EXISTS show_min_salary; ```