變量、流程控制與游標

一樣先跳過了

1. 變數

在MySQL資料庫的存儲過程和函數中，可以使用變數來存儲查詢或計算的中間結果資料，或者輸出最終的結果資料。

在 MySQL 資料庫中，變數分為系統變數以及使用者自訂變數。

1.1 系統變數

1.1.1 系統變數分類

變數由系統定義，不是使用者定義，屬於伺服器層面。啟動MySQL服務，生成MySQL服務實例期間，MySQL將為MySQL伺服器記憶體中的系統變數賦值，這些系統變數定義了當前MySQL服務實例的屬性、特徵。這些系統變數的值要麼是編譯MySQL時參數的預設值，要麼是設定檔（例如my.ini等）中的參數值。大家可以通過網址 https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html 查看MySQL文檔的系統變數。

系統變數分為全域系統變數（需要添加global 關鍵字）以及會話系統變數（需要添加 session 關鍵字），有時也把全域系統變數簡稱為全域變數，有時也把會話系統變數稱為local變數。**如果不寫，預設會話級別。**靜態變數（在 MySQL 服務實例運行期間它們的值不能使用 set 動態修改）屬於特殊的全域系統變數。

每一個MySQL客戶機成功連接MySQL伺服器後，都會產生與之對應的會話。會話期間，MySQL服務實例會在MySQL伺服器記憶體中生成與該會話對應的會話系統變數，這些會話系統變數的初始值是全域系統變數值的複製。如下圖：

全域系統變數針對於所有會話（連接）有效，但不能跨重啟
會話系統變數僅針對於當前會話（連接）有效。會話期間，當前會話對某個會話系統變數值的修改，不會影響其他會話同一個會話系統變數的值。
會話1對某個全域系統變數值的修改會導致會話2中同一個全域系統變數值的修改。

在MySQL中有些系統變數只能是全域的，例如 max_connections 用於限制伺服器的最大連接數；有些系統變數作用域既可以是全域又可以是會話，例如 character_set_client 用於設置用戶端的字元集；有些系統變數的作用域只能是當前會話，例如 pseudo_thread_id 用於標記當前會話的 MySQL 連接 ID。

1.1.2 查看系統變數

查看所有或部分系統變數

#查看所有全域變數
SHOW GLOBAL VARIABLES;

#查看所有會話變數
SHOW SESSION VARIABLES;
或
SHOW VARIABLES;

#查看滿足條件的部分系統變數。
SHOW GLOBAL VARIABLES LIKE '%識別字%';

#查看滿足條件的部分會話變數
SHOW SESSION VARIABLES LIKE '%識別字%';

舉例：

SHOW GLOBAL VARIABLES LIKE 'admin_%';

查看指定系統變數

作為 MySQL 編碼規範，MySQL 中的系統變數以兩個"@"開頭，其中"@@global"僅用於標記全域系統變數，"@@session"僅用於標記會話系統變數。"@@“首先標記會話系統變數，如果會話系統變數不存在，則標記全域系統變數。

#查看指定的系統變數的值
SELECT @@global.變數名;

#查看指定的會話變數的值
SELECT @@session.變數名;
#或者
SELECT @@變數名;

修改系統變數的值

有些時候，資料庫管理員需要修改系統變數的預設值，以便修改當前會話或者MySQL服務實例的屬性、特徵。具體方法：

方式1：修改MySQL設定檔，繼而修改MySQL系統變數的值（該方法需要重啟MySQL服務）

方式2：在MySQL服務運行期間，使用"set"命令重新設置系統變數的值

#為某個系統變數賦值
#方式1：
SET @@global.變數名=變數值;
#方式2：
SET GLOBAL 變數名=變數值;


#為某個會話變數賦值
#方式1：
SET @@session.變數名=變數值;
#方式2：
SET SESSION 變數名=變數值;

舉例：

SELECT @@global.autocommit;
SET GLOBAL autocommit=0;

SELECT @@session.tx_isolation;
SET @@session.tx_isolation='read-uncommitted';

SET GLOBAL max_connections = 1000;
SELECT @@global.max_connections;

1.2 使用者變數

1.2.1 使用者變數分類

使用者變數是使用者自己定義的，作為 MySQL 編碼規範，MySQL 中的使用者變數以一個"@"開頭。根據作用範圍不同，又分為會話使用者變數和區域變數。

會話使用者變數：作用域和會話變數一樣，只對當前連接會話有效。
區域變數：只在 BEGIN 和 END 語句塊中有效。區域變數只能在存儲過程和函數中使用。

1.2.2 會話使用者變數

變數的定義

#方式1："="或":="
SET @使用者變數 = 值;
SET @使用者變數 := 值;

#方式2：":=" 或 INTO關鍵字
SELECT @使用者變數 := 運算式 [FROM 等子句];
SELECT 運算式 INTO @使用者變數  [FROM 等子句];

查看使用者變數的值（查看、比較、運算等）

SELECT @使用者變數

舉例

SET @a = 1;

SELECT @a;

SELECT @num := COUNT(*) FROM employees;

SELECT @num;

SELECT AVG(salary) INTO @avgsalary FROM employees;

SELECT @avgsalary;

SELECT @big;  #查看某個未聲明的變數時，將得到NULL值

1.2.3 區域變數

定義：可以使用DECLARE語句定義一個區域變數

作用域：僅僅在定義它的 BEGIN … END 中有效

位置：只能放在 BEGIN … END 中，而且只能放在第一句

BEGIN
	#聲明區域變數
	DECLARE 變數名1 變數資料類型 [DEFAULT 變數預設值];
	DECLARE 變數名2,變數名3,... 變數資料類型 [DEFAULT 變數預設值];

	#為區域變數賦值
	SET 變數名1 = 值;
	SELECT 值 INTO 變數名2 [FROM 子句];

	#查看區域變數的值
	SELECT 變數1,變數2,變數3;
END

1.定義變數

DECLARE 變數名 類型 [default 值];  # 如果沒有DEFAULT子句，初始值為NULL

舉例：

DECLARE　myparam　INT　DEFAULT 100;

2.變數賦值

方式1：一般用於賦簡單的值

SET 變數名=值;
SET 變數名:=值;

方式2：一般用於賦表中的欄位值

SELECT 欄位名或運算式 INTO 變數名 FROM 表;

3.使用變數（查看、比較、運算等）

SELECT 區域變數名;

舉例1：聲明區域變數，並分別賦值為employees表中employee_id為102的last_name和salary

DELIMITER //

CREATE PROCEDURE set_value()
BEGIN
	DECLARE emp_name VARCHAR(25);
	DECLARE sal DOUBLE(10,2);

	SELECT last_name,salary INTO emp_name,sal
	FROM employees
	WHERE employee_id = 102;

	SELECT emp_name,sal;
END //

DELIMITER ;

舉例2：聲明兩個變數，求和並列印（分別使用會話使用者變數、區域變數的方式實現）

#方式1：使用使用者變數
SET @m=1;
SET @n=1;
SET @sum=@m+@n;

SELECT @sum;

#方式2：使用區域變數
DELIMITER //

CREATE PROCEDURE add_value()
BEGIN
	#區域變數
	DECLARE m INT DEFAULT 1;
	DECLARE n INT DEFAULT 3;
	DECLARE SUM INT;

	SET SUM = m+n;
	SELECT SUM;
END //

DELIMITER ;

舉例3：創建存儲過程"different_salary"查詢某員工和他領導的薪資差距，並用IN參數emp_id接收員工id，用OUT參數dif_salary輸出薪資差距結果。

#聲明
DELIMITER //

CREATE PROCEDURE different_salary(IN emp_id INT,OUT dif_salary DOUBLE)
BEGIN
	#聲明區域變數
	DECLARE emp_sal,mgr_sal DOUBLE DEFAULT 0.0;
	DECLARE mgr_id INT;

	SELECT salary INTO emp_sal FROM employees WHERE employee_id = emp_id;
	SELECT manager_id INTO mgr_id FROM employees WHERE employee_id = emp_id;
	SELECT salary INTO mgr_sal FROM employees WHERE employee_id = mgr_id;
	SET dif_salary = mgr_sal - emp_sal;

END //

DELIMITER ;

#調用
SET @emp_id = 102;
CALL different_salary(@emp_id,@diff_sal);


#查看
SELECT @diff_sal;

1.2.4 對比會話使用者變數與區域變數

			  作用域					定義位置				  語法
會話使用者變數	  當前會話				   會話的任何地方				加@符號，不用指定類型
區域變數	   定義它的BEGIN END中 		BEGIN END的第一句話		  一般不用加@,需要指定類型

2. 定義條件與處理常式

定義條件是事先定義程式執行過程中可能遇到的問題，處理常式定義了在遇到問題時應當採取的處理方式，並且保證存儲過程或函數在遇到警告或錯誤時能繼續執行。這樣可以增強存儲程式處理問題的能力，避免程式異常停止運行。

說明：定義條件和處理常式在存儲過程、存儲函數中都是支援的。

2.1 案例分析

**案例分析：**創建一個名稱為"UpdateDataNoCondition"的存儲過程。代碼如下：

DELIMITER //

CREATE PROCEDURE UpdateDataNoCondition()
	BEGIN
		SET @x = 1;
		UPDATE employees SET email = NULL WHERE last_name = 'Abel';
		SET @x = 2;
		UPDATE employees SET email = 'aabbel' WHERE last_name = 'Abel';
		SET @x = 3;
	END //

DELIMITER ;

調用存儲過程：

mysql> CALL UpdateDataNoCondition();
ERROR 1048 (23000): Column 'email' cannot be null

mysql> SELECT @x;
+------+
| @x   |
+------+
|   1  |
+------+
1 row in set (0.00 sec)

可以看到，此時@x變數的值為1。結合創建存儲過程的SQL語句代碼可以得出：在存儲過程中未定義條件和處理常式，且當存儲過程中執行的SQL語句報錯時，MySQL資料庫會拋出錯誤，並退出當前SQL邏輯，不再向下繼續執行。

2.2 定義條件

定義條件就是給MySQL中的錯誤碼命名，這有助於存儲的程式碼更清晰。它將一個錯誤名字和指定的錯誤條件關聯起來。這個名字可以隨後被用在定義處理常式的DECLARE HANDLER語句中。

定義條件使用DECLARE語句，語法格式如下：

DECLARE 錯誤名稱 CONDITION FOR 錯誤碼（或錯誤條件）

錯誤碼的說明：

MySQL_error_code和sqlstate_value都可以表示MySQL的錯誤。
- MySQL_error_code是數數值型別錯誤代碼。
- sqlstate_value是長度為5的字串類型錯誤代碼。
例如，在ERROR 1418 (HY000)中，1418是MySQL_error_code，‘HY000’是sqlstate_value。
例如，在ERROR 1142（42000）中，1142是MySQL_error_code，‘42000’是sqlstate_value。

**舉例1：**定義"Field_Not_Be_NULL"錯誤名與MySQL中違反非空約束的錯誤類型是"ERROR 1048 (23000)“對應。

#使用MySQL_error_code
DECLARE Field_Not_Be_NULL CONDITION FOR 1048;

#使用sqlstate_value
DECLARE Field_Not_Be_NULL CONDITION FOR SQLSTATE '23000';

**舉例2：**定義"ERROR 1148(42000)“錯誤，名稱為command_not_allowed。

#使用MySQL_error_code
DECLARE command_not_allowed CONDITION FOR 1148;

#使用sqlstate_value
DECLARE command_not_allowed CONDITION FOR SQLSTATE '42000';

2.3 定義處理常式

可以為SQL執行過程中發生的某種類型的錯誤定義特殊的處理常式。定義處理常式時，使用DECLARE語句的語法如下：

DECLARE 處理方式 HANDLER FOR 錯誤類型 處理語句

處理方式：處理方式有3個取值：CONTINUE、EXIT、UNDO。
- CONTINUE：表示遇到錯誤不處理，繼續執行。
- EXIT：表示遇到錯誤馬上退出。
- UNDO：表示遇到錯誤後撤回之前的操作。MySQL中暫時不支持這樣的操作。
錯誤類型（即條件）可以有如下取值：
- SQLSTATE '字串錯誤碼'：表示長度為5的sqlstate_value類型的錯誤代碼；
- MySQL_error_code：匹配數數值型別錯誤代碼；
- 錯誤名稱：表示DECLARE … CONDITION定義的錯誤條件名稱。
- SQLWARNING：匹配所有以01開頭的SQLSTATE錯誤代碼；
- NOT FOUND：匹配所有以02開頭的SQLSTATE錯誤代碼；
- SQLEXCEPTION：匹配所有沒有被SQLWARNING或NOT FOUND捕獲的SQLSTATE錯誤代碼；
處理語句：如果出現上述條件之一，則採用對應的處理方式，並執行指定的處理語句。語句可以是像”SET 變數 = 值“這樣的簡單語句，也可以是使用BEGIN ... END編寫的複合陳述式。

定義處理常式的幾種方式，代碼如下：

#方法1：捕獲sqlstate_value
DECLARE CONTINUE HANDLER FOR SQLSTATE '42S02' SET @info = 'NO_SUCH_TABLE';

#方法2：捕獲mysql_error_value
DECLARE CONTINUE HANDLER FOR 1146 SET @info = 'NO_SUCH_TABLE';

#方法3：先定義條件，再調用
DECLARE no_such_table CONDITION FOR 1146;
DECLARE CONTINUE HANDLER FOR NO_SUCH_TABLE SET @info = 'NO_SUCH_TABLE';

#方法4：使用SQLWARNING
DECLARE EXIT HANDLER FOR SQLWARNING SET @info = 'ERROR';

#方法5：使用NOT FOUND
DECLARE EXIT HANDLER FOR NOT FOUND SET @info = 'NO_SUCH_TABLE';

#方法6：使用SQLEXCEPTION
DECLARE EXIT HANDLER FOR SQLEXCEPTION SET @info = 'ERROR';

2.4 案例解決

在存儲過程中，定義處理常式，捕獲sqlstate_value值，當遇到MySQL_error_code值為1048時，執行CONTINUE操作，並且將@proc_value的值設置為-1。

DELIMITER //

CREATE PROCEDURE UpdateDataNoCondition()
	BEGIN
		#定義處理常式
		DECLARE CONTINUE HANDLER FOR 1048 SET @proc_value = -1;

		SET @x = 1;
		UPDATE employees SET email = NULL WHERE last_name = 'Abel';
		SET @x = 2;
		UPDATE employees SET email = 'aabbel' WHERE last_name = 'Abel';
		SET @x = 3;
	END //

DELIMITER ;

調用過程：

mysql> CALL UpdateDataWithCondition();
Query OK, 0 rows affected (0.01 sec)

mysql> SELECT @x,@proc_value;
+------+-------------+
| @x   | @proc_value |
+------+-------------+
|    3 |       	 -1  |
+------+-------------+
1 row in set (0.00 sec)

舉例：

創建一個名稱為"InsertDataWithCondition"的存儲過程，代碼如下。

在存儲過程中，定義處理常式，捕獲sqlstate_value值，當遇到sqlstate_value值為23000時，執行EXIT操作，並且將@proc_value的值設置為-1。

#準備工作
CREATE TABLE departments
AS
SELECT * FROM atguigudb.`departments`;

ALTER TABLE departments
ADD CONSTRAINT uk_dept_name UNIQUE(department_id);

DELIMITER //

CREATE PROCEDURE InsertDataWithCondition()
	BEGIN
		DECLARE duplicate_entry CONDITION FOR SQLSTATE '23000' ;
		DECLARE EXIT HANDLER FOR duplicate_entry SET @proc_value = -1;

		SET @x = 1;
		INSERT INTO departments(department_name) VALUES('測試');
		SET @x = 2;
		INSERT INTO departments(department_name) VALUES('測試');
		SET @x = 3;
	END //

DELIMITER ;

調用存儲過程：

mysql> CALL InsertDataWithCondition();
Query OK, 0 rows affected (0.01 sec)

mysql> SELECT @x,@proc_value;
+------+-------------+
| @x   | @proc_value |
+------+-------------+
|    2 |       	 -1  |
+------+-------------+
1 row in set (0.00 sec)

3. 流程控制

解決複雜問題不可能通過一個 SQL 語句完成，我們需要執行多個 SQL 操作。流程控制語句的作用就是控制存儲過程中 SQL 語句的執行順序，是我們完成複雜操作必不可少的一部分。只要是執行的程式，流程就分為三大類：

順序結構：程式從上往下依次執行
分支結構：程式按條件進行選擇執行，從兩條或多條路徑中選擇一條執行
迴圈結構：程式滿足一定條件下，重複執行一組語句

針對於MySQL 的流程控制語句主要有 3 類。注意：只能用於存儲程式。

條件判斷語句：IF 語句和 CASE 語句
迴圈語句：LOOP、WHILE 和 REPEAT 語句
跳躍陳述式：ITERATE 和 LEAVE 語句

3.1 分支結構之 IF

IF 語句的語法結構是：

IF 運算式1 THEN 操作1
[ELSEIF 運算式2 THEN 操作2]……
[ELSE 操作N]
END IF

根據運算式的結果為TRUE或FALSE執行相應的語句。這裡”[]“中的內容是可選的。

特點：① 不同的運算式對應不同的操作 ② 使用在begin end中

舉例1：

IF val IS NULL
	THEN SELECT 'val is null';
ELSE SELECT 'val is not null';

END IF;

**舉例2：**聲明存儲過程"update_salary_by_eid1”，定義IN參數emp_id，輸入員工編號。判斷該員工薪資如果低於8000元並且入職時間超過5年，就漲薪500元；否則就不變。

DELIMITER //

CREATE PROCEDURE update_salary_by_eid1(IN emp_id INT)
BEGIN
	DECLARE emp_salary DOUBLE;
	DECLARE hire_year DOUBLE;

	SELECT salary INTO emp_salary FROM employees WHERE employee_id = emp_id;

	SELECT DATEDIFF(CURDATE(),hire_date)/365 INTO hire_year
	FROM employees WHERE employee_id = emp_id;

	IF emp_salary < 8000 AND hire_year > 5
	THEN UPDATE employees SET salary = salary + 500 WHERE employee_id = emp_id;
	END IF;
END //


DELIMITER ;

**舉例3：**聲明存儲過程"update_salary_by_eid2”，定義IN參數emp_id，輸入員工編號。判斷該員工薪資如果低於9000元並且入職時間超過5年，就漲薪500元；否則就漲薪100元。

DELIMITER //

CREATE PROCEDURE update_salary_by_eid2(IN emp_id INT)
BEGIN
	DECLARE emp_salary DOUBLE;
	DECLARE hire_year DOUBLE;

	SELECT salary INTO emp_salary FROM employees WHERE employee_id = emp_id;

	SELECT DATEDIFF(CURDATE(),hire_date)/365 INTO hire_year
	FROM employees WHERE employee_id = emp_id;

	IF emp_salary < 8000 AND hire_year > 5
		THEN UPDATE employees SET salary = salary + 500 WHERE employee_id = emp_id;
	ELSE
		UPDATE employees SET salary = salary + 100 WHERE employee_id = emp_id;
	END IF;
END //


DELIMITER ;

**舉例4：**聲明存儲過程"update_salary_by_eid3”，定義IN參數emp_id，輸入員工編號。判斷該員工薪資如果低於9000元，就更新薪資為9000元；薪資如果大於等於9000元且低於10000的，但是獎金比例為NULL的，就更新獎金比例為0.01；其他的漲薪100元。

DELIMITER //

CREATE PROCEDURE update_salary_by_eid3(IN emp_id INT)
BEGIN
	DECLARE emp_salary DOUBLE;
	DECLARE bonus DECIMAL(3,2);

	SELECT salary INTO emp_salary FROM employees WHERE employee_id = emp_id;
	SELECT commission_pct INTO bonus FROM employees WHERE employee_id = emp_id;

	IF emp_salary < 9000
		THEN UPDATE employees SET salary = 9000 WHERE employee_id = emp_id;
	ELSEIF emp_salary < 10000 AND bonus IS NULL
		THEN UPDATE employees SET commission_pct = 0.01 WHERE employee_id = emp_id;
	ELSE
		UPDATE employees SET salary = salary + 100 WHERE employee_id = emp_id;
	END IF;
END //

DELIMITER ;

3.2 分支結構之 CASE

CASE 語句的語法結構1：

#情況一：類似於switch
CASE 運算式
WHEN 值1 THEN 結果1或語句1(如果是語句，需要加分號)
WHEN 值2 THEN 結果2或語句2(如果是語句，需要加分號)
...
ELSE 結果n或語句n(如果是語句，需要加分號)
END [case]（如果是放在begin end中需要加上case，如果放在select後面不需要）

CASE 語句的語法結構2：

#情況二：類似於多重if
CASE
WHEN 條件1 THEN 結果1或語句1(如果是語句，需要加分號)
WHEN 條件2 THEN 結果2或語句2(如果是語句，需要加分號)
...
ELSE 結果n或語句n(如果是語句，需要加分號)
END [case]（如果是放在begin end中需要加上case，如果放在select後面不需要）

舉例1：

使用CASE流程控制語句的第1種格式，判斷val值等於1、等於2，或者兩者都不等。

CASE val
　　　WHEN 1 THEN SELECT 'val is 1';
　　　WHEN 2 THEN SELECT 'val is 2';
　　　ELSE SELECT 'val is not 1 or 2';
END CASE;

舉例2：

使用CASE流程控制語句的第2種格式，判斷val是否為空、小於0、大於0或者等於0。

CASE
	WHEN val IS NULL THEN SELECT 'val is null';
	WHEN val < 0 THEN SELECT 'val is less than 0';
	WHEN val > 0 THEN SELECT 'val is greater than 0';
	ELSE SELECT 'val is 0';
END CASE;

**舉例3：**聲明存儲過程"update_salary_by_eid4"，定義IN參數emp_id，輸入員工編號。判斷該員工薪資如果低於9000元，就更新薪資為9000元；薪資大於等於9000元且低於10000的，但是獎金比例為NULL的，就更新獎金比例為0.01；其他的漲薪100元。

DELIMITER //

CREATE PROCEDURE update_salary_by_eid4(IN emp_id INT)
BEGIN
	DECLARE emp_sal DOUBLE;
	DECLARE bonus DECIMAL(3,2);

	SELECT salary INTO emp_sal FROM employees WHERE employee_id = emp_id;
	SELECT commission_pct INTO bonus FROM employees WHERE employee_id = emp_id;

	CASE
	WHEN emp_sal<9000
		THEN UPDATE employees SET salary=9000 WHERE employee_id = emp_id;
	WHEN emp_sal<10000 AND bonus IS NULL
		THEN UPDATE employees SET commission_pct=0.01 WHERE employee_id = emp_id;
	ELSE
		UPDATE employees SET salary=salary+100 WHERE employee_id = emp_id;
	END CASE;
END //

DELIMITER ;

舉例4：聲明存儲過程update_salary_by_eid5，定義IN參數emp_id，輸入員工編號。判斷該員工的入職年限，如果是0年，薪資漲50；如果是1年，薪資漲100；如果是2年，薪資漲200；如果是3年，薪資漲300；如果是4年，薪資漲400；其他的漲薪500。

DELIMITER //

CREATE PROCEDURE update_salary_by_eid5(IN emp_id INT)
BEGIN
	DECLARE emp_sal DOUBLE;
	DECLARE hire_year DOUBLE;

	SELECT salary INTO emp_sal FROM employees WHERE employee_id = emp_id;

	SELECT ROUND(DATEDIFF(CURDATE(),hire_date)/365) INTO hire_year FROM employees WHERE employee_id = emp_id;

	CASE hire_year
		WHEN 0 THEN UPDATE employees SET salary=salary+50 WHERE employee_id = emp_id;
		WHEN 1 THEN UPDATE employees SET salary=salary+100 WHERE employee_id = emp_id;
		WHEN 2 THEN UPDATE employees SET salary=salary+200 WHERE employee_id = emp_id;
		WHEN 3 THEN UPDATE employees SET salary=salary+300 WHERE employee_id = emp_id;
		WHEN 4 THEN UPDATE employees SET salary=salary+400 WHERE employee_id = emp_id;
		ELSE UPDATE employees SET salary=salary+500 WHERE employee_id = emp_id;
	END CASE;
END //

DELIMITER ;

3.3 迴圈結構之LOOP

LOOP迴圈語句用來重複執行某些語句。LOOP內的語句一直重複執行直到迴圈被退出（使用LEAVE子句），跳出迴圈過程。

LOOP語句的基本格式如下：

[loop_label:] LOOP
	迴圈執行的語句
END LOOP [loop_label]

其中，loop_label表示LOOP語句的標注名稱，該參數可以省略。

舉例1：

使用LOOP語句進行迴圈操作，id值小於10時將重複執行迴圈過程。

DECLARE id INT DEFAULT 0;
add_loop:LOOP
	SET id = id +1;
	IF id >= 10 THEN LEAVE add_loop;
	END IF;

END LOOP add_loop;

**舉例2：**當市場環境變好時，公司為了獎勵大家，決定給大家漲工資。聲明存儲過程"update_salary_loop()"，聲明OUT參數num，輸出迴圈次數。存儲過程中實現迴圈給大家漲薪，薪資漲為原來的1.1倍。直到全公司的平均薪資達到12000結束。並統計迴圈次數。

DELIMITER //

CREATE PROCEDURE update_salary_loop(OUT num INT)
BEGIN
	DECLARE avg_salary DOUBLE;
	DECLARE loop_count INT DEFAULT 0;

	SELECT AVG(salary) INTO avg_salary FROM employees;

	label_loop:LOOP
		IF avg_salary >= 12000 THEN LEAVE label_loop;
		END IF;

		UPDATE employees SET salary = salary * 1.1;
		SET loop_count = loop_count + 1;
		SELECT AVG(salary) INTO avg_salary FROM employees;
	END LOOP label_loop;

	SET num = loop_count;

END //

DELIMITER ;

3.4 迴圈結構之WHILE

WHILE語句創建一個帶條件判斷的迴圈過程。WHILE在執行語句執行時，先對指定的運算式進行判斷，如果為真，就執行迴圈內的語句，否則退出迴圈。WHILE語句的基本格式如下：

[while_label:] WHILE 迴圈條件  DO
	循環體
END WHILE [while_label];

while_label為WHILE語句的標注名稱；如果迴圈條件結果為真，WHILE語句內的語句或語句群被執行，直至迴圈條件為假，退出迴圈。

舉例1：

WHILE語句示例，i值小於10時，將重複執行迴圈過程，代碼如下：

DELIMITER //

CREATE PROCEDURE test_while()
BEGIN
	DECLARE i INT DEFAULT 0;

	WHILE i < 10 DO
		SET i = i + 1;
	END WHILE;

	SELECT i;
END //

DELIMITER ;
#調用
CALL test_while();

**舉例2：**市場環境不好時，公司為了渡過難關，決定暫時降低大家的薪資。聲明存儲過程"update_salary_while()"，聲明OUT參數num，輸出迴圈次數。存儲過程中實現迴圈給大家降薪，薪資降為原來的90%。直到全公司的平均薪資達到5000結束。並統計迴圈次數。

DELIMITER //

CREATE PROCEDURE update_salary_while(OUT num INT)
BEGIN
	DECLARE avg_sal DOUBLE ;
	DECLARE while_count INT DEFAULT 0;

	SELECT AVG(salary) INTO avg_sal FROM employees;

	WHILE avg_sal > 5000 DO
		UPDATE employees SET salary = salary * 0.9;

		SET while_count = while_count + 1;

		SELECT AVG(salary) INTO avg_sal FROM employees;
	END WHILE;

	SET num = while_count;

END //

DELIMITER ;

3.5 迴圈結構之REPEAT

REPEAT語句創建一個帶條件判斷的迴圈過程。與WHILE迴圈不同的是，REPEAT 迴圈首先會執行一次迴圈，然後在 UNTIL 中進行運算式的判斷，如果滿足條件就退出，即 END REPEAT；如果條件不滿足，則會就繼續執行迴圈，直到滿足退出條件為止。

REPEAT語句的基本格式如下：

[repeat_label:] REPEAT
　　　　循環體的語句
UNTIL 結束迴圈的條件運算式
END REPEAT [repeat_label]

repeat_label為REPEAT語句的標注名稱，該參數可以省略；REPEAT語句內的語句或語句群被重複，直至expr_condition為真。

舉例1：

DELIMITER //

CREATE PROCEDURE test_repeat()
BEGIN
	DECLARE i INT DEFAULT 0;

	REPEAT
		SET i = i + 1;
	UNTIL i >= 10
	END REPEAT;

	SELECT i;
END //

DELIMITER ;

**舉例2：**當市場環境變好時，公司為了獎勵大家，決定給大家漲工資。聲明存儲過程"update_salary_repeat()"，聲明OUT參數num，輸出迴圈次數。存儲過程中實現迴圈給大家漲薪，薪資漲為原來的1.15倍。直到全公司的平均薪資達到13000結束。並統計迴圈次數。

DELIMITER //

CREATE PROCEDURE update_salary_repeat(OUT num INT)
BEGIN
	DECLARE avg_sal DOUBLE ;
	DECLARE repeat_count INT DEFAULT 0;

	SELECT AVG(salary) INTO avg_sal FROM employees;

	REPEAT
		UPDATE employees SET salary = salary * 1.15;

		SET repeat_count = repeat_count + 1;

		SELECT AVG(salary) INTO avg_sal FROM employees;
	UNTIL avg_sal >= 13000
	END REPEAT;

	SET num = repeat_count;

END //

DELIMITER ;

對比三種迴圈結構：

1、這三種迴圈都可以省略名稱，但如果迴圈中添加了迴圈控制語句（LEAVE或ITERATE）則必須添加名稱。 2、 LOOP：一般用於實現簡單的"死"迴圈 WHILE：先判斷後執行 REPEAT：先執行後判斷，無條件至少執行一次

3.6 跳躍陳述式之LEAVE語句

LEAVE語句：可以用在迴圈語句內，或者以 BEGIN 和 END 包裹起來的程式體內，表示跳出迴圈或者跳出程式體的操作。如果你有面向過程的程式設計語言的使用經驗，你可以把 LEAVE 理解為 break。

基本格式如下：

LEAVE 標記名

其中，label參數表示迴圈的標誌。LEAVE和BEGIN … END或迴圈一起被使用。

**舉例1：**創建存儲過程 “leave_begin()"，聲明INT類型的IN參數num。給BEGIN…END加標記名，並在BEGIN…END中使用IF語句判斷num參數的值。

如果num<=0，則使用LEAVE語句退出BEGIN…END；
如果num=1，則查詢"employees"表的平均薪資；
如果num=2，則查詢"employees"表的最低薪資；
如果num>2，則查詢"employees"表的最高薪資。

IF語句結束後查詢"employees"表的總人數。

DELIMITER //

CREATE PROCEDURE leave_begin(IN num INT)

	begin_label: BEGIN
		IF num<=0
			THEN LEAVE begin_label;
		ELSEIF num=1
			THEN SELECT AVG(salary) FROM employees;
		ELSEIF num=2
			THEN SELECT MIN(salary) FROM employees;
		ELSE
			SELECT MAX(salary) FROM employees;
		END IF;

		SELECT COUNT(*) FROM employees;
	END //


DELIMITER ;

舉例2：

當市場環境不好時，公司為了渡過難關，決定暫時降低大家的薪資。聲明存儲過程"leave_while()"，聲明OUT參數num，輸出迴圈次數，存儲過程中使用WHILE迴圈給大家降低薪資為原來薪資的90%，直到全公司的平均薪資小於等於10000，並統計迴圈次數。

DELIMITER //
CREATE PROCEDURE leave_while(OUT num INT)

BEGIN
	#
	DECLARE avg_sal DOUBLE;#記錄平均工資
	DECLARE while_count INT DEFAULT 0; #記錄迴圈次數

	SELECT AVG(salary) INTO avg_sal FROM employees; #① 初始化條件

	while_label:WHILE TRUE DO  #② 迴圈條件

		#③ 循環體
		IF avg_sal <= 10000 THEN
			LEAVE while_label;
		END IF;

		UPDATE employees SET salary  = salary * 0.9;
		SET while_count = while_count + 1;

		#④ 反覆運算條件
		SELECT AVG(salary) INTO avg_sal FROM employees;

	END WHILE;

	#賦值
	SET num = while_count;

END //

DELIMITER ;

3.7 跳躍陳述式之ITERATE語句

ITERATE語句：只能用在迴圈語句（LOOP、REPEAT和WHILE語句）內，表示重新開始迴圈，將執行順序轉到語句段開頭處。如果你有面向過程的程式設計語言的使用經驗，你可以把 ITERATE 理解為 continue，意思為"再次迴圈”。

語句基本格式如下：

ITERATE label

label參數表示迴圈的標誌。ITERATE語句必須跟在迴圈標誌前面。

舉例： 定義區域變數num，初始值為0。迴圈結構中執行num + 1操作。

如果num < 10，則繼續執行迴圈；
如果num > 15，則退出迴圈結構；

DELIMITER //

CREATE PROCEDURE test_iterate()

BEGIN
	DECLARE num INT DEFAULT 0;

	my_loop:LOOP
		SET num = num + 1;

		IF num < 10
			THEN ITERATE my_loop;
		ELSEIF num > 15
			THEN LEAVE my_loop;
		END IF;

		SELECT '尚矽谷：讓天下沒有難學的技術';

	END LOOP my_loop;

END //

DELIMITER ;

4. 游標

4.1 什麼是游標（或游標）

雖然我們也可以通過篩選條件 WHERE 和 HAVING，或者是限定返回記錄的關鍵字 LIMIT 返回一條記錄，但是，卻無法在結果集中像指標一樣，向前定位一條記錄、向後定位一條記錄，或者是隨意定位到某一條記錄，並對記錄的資料進行處理。

這個時候，就可以用到游標。游標，提供了一種靈活的操作方式，讓我們能夠對結果集中的每一條記錄進行定位，並對指向的記錄中的資料進行操作的資料結構。游標讓 SQL 這種面向集合的語言有了面向過程開發的能力。

在 SQL 中，游標是一種臨時的資料庫物件，可以指向存儲在資料庫表中的資料行指標。這裡游標充當了指標的作用，我們可以通過操作游標來對資料行進行操作。

MySQL中游標可以在存儲過程和函數中使用。

比如，我們查詢了 employees 資料表中工資高於15000的員工都有哪些：

SELECT employee_id,last_name,salary FROM employees
WHERE salary > 15000;

這裡我們就可以通過游標來運算元據行，如圖所示此時游標所在的行是"108"的記錄，我們也可以在結果集上滾動游標，指向結果集中的任意一行。

4.2 使用游標步驟

游標必須在聲明處理常式之前被聲明，並且變數和條件還必須在聲明游標或處理常式之前被聲明。

如果我們想要使用游標，一般需要經歷四個步驟。不同的 DBMS 中，使用游標的語法可能略有不同。

第一步，聲明游標

在MySQL中，使用DECLARE關鍵字來聲明游標，其語法的基本形式如下：

DECLARE cursor_name CURSOR FOR select_statement;

這個語法適用於 MySQL，SQL Server，DB2 和 MariaDB。如果是用 Oracle 或者 PostgreSQL，需要寫成：

DECLARE cursor_name CURSOR IS select_statement;

要使用 SELECT 語句來獲取資料結果集，而此時還沒有開始遍歷資料，這裡 select_statement 代表的是 SELECT 語句，返回一個用於創建游標的結果集。

比如：

DECLARE cur_emp CURSOR FOR
SELECT employee_id,salary FROM employees;

DECLARE cursor_fruit CURSOR FOR
SELECT f_name, f_price FROM fruits ;

第二步，打開游標

打開游標的語法如下：

OPEN cursor_name

當我們定義好游標之後，如果想要使用游標，必須先打開游標。打開游標的時候 SELECT 語句的查詢結果集就會送到游標工作區，為後面游標的逐條讀取結果集中的記錄做準備。

OPEN　cur_emp ;

第三步，使用游標（從游標中取得資料）

語法如下：

FETCH cursor_name INTO var_name [, var_name] ...

這句的作用是使用 cursor_name 這個游標來讀取當前行，並且將資料保存到 var_name 這個變數中，游標指標指到下一行。如果游標讀取的資料行有多個列名，則在 INTO 關鍵字後面賦值給多個變數名即可。

注意：var_name必須在聲明游標之前就定義好。

FETCH　cur_emp INTO emp_id, emp_sal ;

注意：游標的查詢結果集中的欄位數，必須跟 INTO 後面的變數數一致，否則，在存儲過程執行的時候，MySQL 會提示錯誤。

第四步，關閉游標

CLOSE cursor_name

有 OPEN 就會有 CLOSE，也就是打開和關閉游標。當我們使用完游標後需要關閉掉該游標。因為游標會佔用系統資源，如果不及時關閉，游標會一直保持到存儲過程結束，影響系統運行的效率。而關閉游標的操作，會釋放游標佔用的系統資源。

關閉游標之後，我們就不能再檢索查詢結果中的資料行，如果需要檢索只能再次打開游標。

CLOSE　cur_emp;

4.3 舉例

創建存儲過程"get_count_by_limit_total_salary()"，聲明IN參數 limit_total_salary，DOUBLE類型；聲明OUT參數total_count，INT類型。函數的功能可以實現累加薪資最高的幾個員工的薪資值，直到薪資總和達到limit_total_salary參數的值，返回累加的人數給total_count。

DELIMITER //

CREATE PROCEDURE get_count_by_limit_total_salary(IN limit_total_salary DOUBLE,OUT total_count INT)

BEGIN
	DECLARE sum_salary DOUBLE DEFAULT 0;  #記錄累加的總工資
	DECLARE cursor_salary DOUBLE DEFAULT 0; #記錄某一個工資值
	DECLARE emp_count INT DEFAULT 0; #記錄迴圈個數
	#定義游標
	DECLARE emp_cursor CURSOR FOR SELECT salary FROM employees ORDER BY salary DESC;
	#打開游標
	OPEN emp_cursor;

	REPEAT
		#使用游標（從游標中獲取資料）
		FETCH emp_cursor INTO cursor_salary;

		SET sum_salary = sum_salary + cursor_salary;
		SET emp_count = emp_count + 1;

		UNTIL sum_salary >= limit_total_salary
	END REPEAT;

	SET total_count = emp_count;
	#關閉游標
	CLOSE emp_cursor;

END //

DELIMITER ;

4.5 小結

游標是 MySQL 的一個重要的功能，為逐條讀取結果集中的資料，提供了完美的解決方案。跟在應用層面實現相同的功能相比，游標可以在存儲程式中使用，效率高，程式也更加簡潔。

但同時也會帶來一些性能問題，比如在使用游標的過程中，會對資料行進行加鎖，這樣在業務併發量大的時候，不僅會影響業務之間的效率，還會消耗系統資源，造成記憶體不足，這是因為游標是在記憶體中進行的處理。

建議：養成用完之後就關閉的習慣，這樣才能提高系統的整體效率。

補充：MySQL 8.0的新特性—全域變數的持久化

在MySQL資料庫中，全域變數可以通過SET GLOBAL語句來設置。例如，設置伺服器語句超時的限制，可以通過設置系統變數max_execution_time來實現：

SET GLOBAL MAX_EXECUTION_TIME=2000;

使用SET GLOBAL語句設置的變數值只會臨時生效。資料庫重啟後，伺服器又會從MySQL設定檔中讀取變數的預設值。 MySQL 8.0版本新增了SET PERSIST命令。例如，設置伺服器的最大連接數為1000：

SET PERSIST global max_connections = 1000;

MySQL會將該命令的配置保存到資料目錄下的mysqld-auto.cnf檔中，下次啟動時會讀取該檔，用其中的配置來覆蓋預設的設定檔。

舉例：

查看全域變數max_connections的值，結果如下：

mysql> show variables like '%max_connections%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| max_connections        | 151   |
| mysqlx_max_connections | 100   |
+------------------------+-------+
2 rows in set, 1 warning (0.00 sec)

設置全域變數max_connections的值：

mysql> set persist max_connections=1000;
Query OK, 0 rows affected (0.00 sec)

重啟MySQL伺服器，再次查詢max_connections的值：

mysql> show variables like '%max_connections%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| max_connections        | 1000  |
| mysqlx_max_connections | 100   |
+------------------------+-------+
2 rows in set, 1 warning (0.00 sec)

上次修改於 2021-12-27