你瞭解MySQL中的變數、流程控制與遊標？

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)

更多程式設計相關知識，請存取：！！

以上就是你瞭解MySQL中的變數、流程控制與遊標？的詳細內容，更多請關注TW511.COM其它相關文章！