MySQL學習之聊聊流程控制和遊標

本篇文章進行MySQL的學習，聊聊MySQL中的流程控制和遊標，希望對大家有所幫助。

學習或者瞭解過程式語言的都知道，任何一門程式語言都不可能只通過一兩句語句程式碼完成。

流程控制語句的作用就是在程式執行過程中控制語句的執行順序，是我們完成複雜操作必不可少的一部分。

只要是執行的程式，流程就分為三大類：

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

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

條件判斷語句：IF 語句和 CASE 語句
迴圈語句：LOOP、WHILE 和 REPEAT 語句
跳轉語句：ITERATE 和 LEAVE 語句

準備工作：

建立資料庫以及兩張表，並插入資料：

create database dbtest16;
use dbtest16;

create table employees 
as 
select * from atguigudb.employees;
create table departments
as
select * from atguigudb.departments;

select * from employees;
select * from departments;

分支結構

分支，即二選一。在SQL中分支結構主要有兩種展現形式：

IF
CASE

IF語句

IF 語句的語法結構是：

IF 表示式1 THEN 操作1
[ELSEIF 表示式2 THEN 操作2]……
[ELSE 操作N]
END IF

根據表示式的結果為TRUE或FALSE執行相應的語句。這裡「[]」中的內容是可選的。

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

舉例一：單判斷

delimiter //
create procedure test_if()
begin
		# 宣告區域性變數
		declare stu_name varchar(15);
		if stu_name is null
							then select 'stu_name is null';
		end if;
end//
delimiter ;
call test_if();

舉例二：二選一

delimiter //
create procedure test_if2()
begin
		declare email varchar(25);
		
		if email is null
					then select 'email is null';
		else
					select 'email is not null';
		end if;
end//
delimiter ;
call test_if2();

舉例三：多選一

delimiter //
create procedure test_if3()
begin
		declare age int default 20;
		
		if age > 40
					then select '中老年';
		elseif age > 18
					then select '青壯年';
		elseif age > 10
					then select '青少年';
		else 
					select '孩童';
		end if;
end//
delimiter ;
call test_if3();

場景舉例一：宣告儲存過程「update_salary_by_eid1」，定義IN引數emp_id，輸入員工編號。判斷該員工薪資如果低於8000元並且入職時間超過5年，就漲薪500元；否則就不變。

delimiter //
create procedure update_salary_by_eid1(in emp_id int)
begin
  # 宣告變數 
	declare sal double;  # 記錄員工工資
	declare hiredate date;  # 記錄入職日期
	# 查詢賦值
	select salary,hire_date into sal,hiredate from employees 
	where employee_id = emp_id;
	# 判斷條件並修改
	if sal < 8000 and datediff(now(),hiredate)/365 > 5 
				then update employees set salary = salary + 500 where employee_id = emp_id;
	end if;
end//
delimiter;

set @emp_id = 104;
# 更新前104號員工工資情況：
select salary,employee_id from employees where employee_id = @emp_id;
# 更新工資：
call update_salary_by_eid1(@emp_id);
# 再次查詢104號員工工資情況：
select salary,employee_id from employees where employee_id = @emp_id;

場景舉例二：宣告儲存過程「update_salary_by_eid2」，定義IN引數emp_id，輸入員工編號。判斷該員工薪資如果低於9000元並且入職時間超過5年，就漲薪500元；否則就漲薪100元。

delimiter //
create procedure update_salary_by_eid2(in emp_id int)
begin

  # 宣告變數 

	declare sal double;  # 記錄員工工資
	declare hiredate date;  # 記錄入職日期
	# 查詢賦值
	select salary,hire_date into sal,hiredate from employees 
	where employee_id = emp_id;
	# 判斷條件並修改
	if sal < 9000 and datediff(now(),hiredate)/365 > 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;
# 以103,104員工為例
# 更新前員工工資情況：
select salary,employee_id from employees where employee_id in (103,104);
# 更新工資：
call update_salary_by_eid2(103);
call update_salary_by_eid2(104);
# 再次查詢員工工資情況：
select salary,employee_id from employees where employee_id in (103,104);

場景舉例三:宣告儲存過程「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 sal double;  # 記錄員工工資
	declare emp_commission_pct double;  # 記錄獎金比例
	# 查詢賦值
	select salary,commission_pct into sal,emp_commission_pct from employees 
	where employee_id = emp_id;
	# 判斷條件並修改
	if sal < 9000
			then update employees set salary = 9000 where employee_id = emp_id;
	elseif sal < 10000 and emp_commission_pct 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;

# 以102,103,104員工為例
# 更新前員工工資情況：
select salary,employee_id,commission_pct from employees where employee_id in (102,103,104);
# 更新工資：
call update_salary_by_eid3(102);
call update_salary_by_eid3(103);
call update_salary_by_eid3(104);
# 再次查詢員工工資情況：
select salary,employee_id,commission_pct from employees where employee_id in (102,103,104);

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後面不需要）

舉例一：case ... when ... then ...

delimiter //
create procedure test_case()
begin
		declare var int default 2;
		
		case var
			    when 1 then select 'var = 1';
					when 2 then select 'var = 2';
					when 3 then select 'var = 3';
					else select 'other';
		end case;
end //
delimiter ;
call test_case();

舉例二：case when ... then ...

delimiter //
create procedure test_case2()
begin
		declare var1 int default 10;
		
		case  when var1 >= 100 then select '三位數';
					when var1 >=10 then select '兩位數';
					else select '個位數';
		end case;
end //
delimiter ;
call test_case2();

場景舉例一：-- 宣告儲存過程「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 sal double;  # 記錄員工工資
	declare emp_commission_pct double;  # 記錄獎金比例
	# 查詢賦值
	select salary,commission_pct into sal,emp_commission_pct from employees 
	where employee_id = emp_id;
	# 判斷條件並修改
	case
	when sal < 9000
			then update employees set salary = 9000 where employee_id = emp_id;
	when sal < 10000 and emp_commission_pct 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;
# 以103,104,105員工為例
# 更新前員工工資情況：
select salary,employee_id,commission_pct from employees where employee_id in (103,104,105);
# 更新工資：
call update_salary_by_eid3(103);
call update_salary_by_eid3(104);
call update_salary_by_eid3(105);
# 再次查詢員工工資情況：
select salary,employee_id,commission_pct from employees where employee_id in (103,104,105);

場景舉例二：-- 宣告儲存過程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 sal double;  # 記錄員工工資
	declare hire_year double;  # 記錄入職日期
	# 查詢賦值
	select salary,round(datediff(now(),hire_date)/365) into sal,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;
# 以107員工為例
# 更新前員工工資情況：
select salary,employee_id,hire_date from employees where employee_id = 107;
# 更新工資：
call update_salary_by_eid5(107);
# 再次查詢員工工資情況：
select salary,employee_id,hire_date from employees where employee_id = 107;

# 針對場景二，此種寫法略顯不足，重複的書寫相同的更新語句，
# 其實觀察下來也就金額不同，可以有改進改進如下：
delimiter //
create procedure update_salary_by_eid6(in emp_id int)
begin
  # 宣告變數 
	declare sal double;  # 記錄員工工資
	declare hire_year double;  # 記錄入職日期
	declare add_sal double; # 儲存更新的金額
	# 查詢賦值
	select salary,round(datediff(now(),hire_date)/365) into sal,hire_year from employees 
	where employee_id = emp_id;
	# 判斷條件並修改
	case hire_year
			when 0 then set add_sal = 50;
			when 1 then set add_sal = 100;
			when 2 then set add_sal = 200;
			when 3 then set add_sal = 300;
			when 4 then set add_sal = 400;
			else set add_sal = 500;
	end case;
	# 根據當前add_sal值修改
	update employees set salary = salary + add_sal where employee_id = emp_id;
end//
delimiter;
# 以108員工為例
# 更新前員工工資情況：
select salary,employee_id,hire_date from employees where employee_id = 108;
# 更新工資：
call update_salary_by_eid5(108);
# 再次查詢員工工資情況：
select salary,employee_id,hire_date from employees where employee_id = 108;

迴圈結構

有時候我們需要重複的執行某條語句，而藉助迴圈結構可以很好地實現。在MySQL中我們可以有三種方式實現迴圈：

LOOP
WHILE
REPEAT

凡是迴圈結構都遵循的四要素：

1.初始化條件
2.迴圈條件
3.迴圈體
4.迭代條件

LOOP語句

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

LOOP語句的基本格式如下：

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

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

舉例一：

delimiter //
create procedure test_loop()
begin
		# 宣告變數
		declare num int default 1;
		
		soberw:loop
				# 重新賦值
				set num = num + 1;
				if num >= 10 
						then leave soberw;
				end if;
		end loop soberw;
		
		# 檢視num
		select num;
end //
delimiter ;

call test_loop();

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

delimiter //
create procedure update_salary_loop(out num int)
begin
    # 宣告變數
		# 記錄儲存平均薪資
		declare avg_sal double default 0;
		# 記錄迴圈次數
		declare count int default 0;
		# 獲取當前平均薪資
		select avg(salary) into avg_sal from employees;
		
		soberw:loop
				# 結束條件
				if avg_sal >= 12000 
								then leave soberw;
						end if;
				# 更新工資
				update employees set salary = salary * 1.1;
				# 保證當前平均薪資為最新
				select avg(salary) into avg_sal from employees;
				# 記錄次數
				set count = count + 1;
		end loop soberw;
		# 返回num
		set num = count;
end //
delimiter ;

call update_salary_loop(@num);
select @num;
select avg(salary) from employees;

WHILE語句

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

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

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

舉例一：WHILE語句範例，i值小於10時，將重複執行迴圈過程

delimiter //
create procedure test_while()
begin
    # 初始化條件
		declare i int default 1;
		#迴圈條件
		while i < 10 do
				# 迴圈體略
				#迭代條件
				set i = i + 1;
		end while;
		select i;
end//
delimiter ;

call test_while();

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

delimiter //
create procedure update_salary_while(out num int)
begin
    # 宣告變數
		# 記錄儲存平均薪資
		declare avg_sal double default 0;
		# 記錄迴圈次數
		declare count int default 0;
		# 獲取當前平均薪資 初始化條件
		select avg(salary) into avg_sal from employees;
		
		#迴圈條件
		soberw:while avg_sal > 5000 do
				# 迴圈體		
				# 更新工資
				update employees set salary = salary * 0.9;
				# 記錄次數
				set count = count + 1;
				
				# 迭代條件
				# 保證當前平均薪資為最新
				select avg(salary) into avg_sal from employees;
				
		end while soberw;
		# 返回num
		set num = count;
end //
delimiter ;

call update_salary_while(@num);
select @num;
select avg(salary) from employees;

REPEAT語句

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

REPEAT語句的基本格式如下：

[repeat_label:] REPEAT
　　　　迴圈體的語句
UNTIL 結束迴圈的條件表示式
END REPEAT [repeat_label]

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

舉例一：

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 ;

call test_repeat();

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

delimiter //
create procedure update_salary_repeat(out num int)
begin
		# 宣告變數
		# 記錄儲存平均薪資
		declare avg_sal double default 0;
		# 記錄迴圈次數
		declare count int default 0;
		# 獲取當前平均薪資 初始化條件
		select avg(salary) into avg_sal from employees;
		
		#迴圈條件
		soberw:repeat  
				# 迴圈體		
				# 更新工資
				update employees set salary = salary * 1.15;
				# 記錄次數
				set count = count + 1;
				
				# 迭代條件
				# 保證當前平均薪資為最新
				select avg(salary) into avg_sal from employees;
				
		until avg_sal >= 13000
		end repeat soberw;
		# 返回num
		set num = count;
end //
delimiter ;

call update_salary_repeat(@num);
select @num;
select avg(salary) from employees;

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

跳轉語句

跳轉語句可以協助我們更好的控制迴圈。

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)
soberw:begin
		if num <= 0 
				then leave soberw;
		elseif num = 1
				then select avg(salary) from employees;
		elseif num = 2
				then select min(salary) from employees;
		elseif num > 2
				then select max(salary) from employees;
		end if;
		select count(1) from employees;
end//
delimiter ;

call leave_begin(2);
call leave_begin(-1);

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

delimiter//
create procedure leave_while(out num int)
begin
		declare avg_sal double;
		declare count int default 0;
		
		select avg(salary) into avg_sal from employees;
		soberw:while true do
		
				if(avg_sal <= 10000) 
						then leave soberw;
				end if;
				
				update employees set salary = salary * 0.9;
				select avg(salary) into avg_sal from employees;
				set count = count + 1;
				
		end while soberw;
		
		set num = count;
end//
delimiter;

call leave_while(@num);
select @num;
select avg(salary) from employees;

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;
		soberw:loop
				set num = num + 1;
				if num < 10
				   then iterate soberw;
			  end if;
				if num >15
					then leave soberw;
			  end if;
		end loop soberw;
		
		select num;
end//
delimiter ;

call test_iterate();

遊標

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

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

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

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

遊標使用的步驟：

1)宣告遊標 DECLARE cursor_name CURSOR FOR select_statement;
2)開啟遊標 OPEN cursor_name
3)使用遊標（從遊標中獲取資料） FETCH cursor_name INTO var_name [, var_name] ...
4)關閉遊標 CLOSE cursor_name

注意：遊標的查詢結果集中的欄位數，必須跟 INTO 後面的變數數一致，否則，在儲存過程執行的時候，MySQL 會提示錯誤。
有 OPEN 就會有 CLOSE，也就是開啟和關閉遊標。當我們使用完遊標後需要關閉掉該遊標。因為遊標會佔用系統資源，如果不及時關閉，遊標會一直保持到儲存過程結束，影響系統執行的效率。而關閉遊標的操作，會釋放遊標佔用的系統資源。

舉例： -- 建立儲存過程「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_sal double default 0;
		# 儲存累加人數
		declare count int default 0;
		# 定義單個工資
		declare emp_salary double default 0;
		# 定義遊標
		declare cursor_sal cursor for select salary from employees order by salary desc;
		# 開啟遊標
		open cursor_sal;
		# 使用遊標
		while sum_sal < limit_total_salary do
		   fetch cursor_sal into emp_salary;
			set sum_sal = sum_sal + emp_salary;
			set count = count + 1;
		end while;
		#關閉遊標
		close cursor_sal;
		# 給total_count賦值
		set total_count = count;
end//
delimiter ;

set @limit_total_salary = 200000;
call get_count_by_limit_total_salary(@limit_total_salary,@total_count);
select @total_count;

【相關推薦：】

以上就是MySQL學習之聊聊流程控制和遊標的詳細內容，更多請關注TW511.COM其它相關文章！