MySQL中流程控制和游標的示例分析

137次閱讀

沒有評論

共計 14744 個字符，預計需要花費 37 分鐘才能閱讀完成。

丸趣 TV 小編給大家分享一下 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
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）則必須添加名稱。
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 中流程控制和游標的示例分析”有了一定的了解，如果想了解更多相關知識，歡迎關注丸趣 TV 行業資訊頻道，感謝各位的閱讀！

正文完