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中流程控制和游標的示例分析”有了一定的了解，如果想了解更多相關知識，歡迎關注億速云行業資訊頻道，感謝各位的閱讀！