|
目录
一、流程控制
1.1 分支结构
1.1.1 IF THEN
1.1.2 CASE
1.2?循环结构
1.2.1?LOOP
1.2.2?WHILE
1.2.3?REPEAT
1.3 跳转语句
二、游标
2.1 概念
2.2 使用步骤
2.3 举例
一、流程控制
????????流程控制为三大类：
????????????????顺序结构 ：程序从上往下依次执行
????????????????分支结构 ：程序按条件进行选择执行，从两条或多条路径中选择一条执行
????????????????循环结构 ：程序满足一定条件下，重复执行一组语句
????????针对于MySQL 的流程控制语句主要有 3 类。注意：只能用于存储程序。
????????????????条件判断语句 ：IF 语句和 CASE 语句
????????????????循环语句 ：LOOP、WHILE 和 REPEAT 语句
????????????????跳转语句 ：ITERATE 和 LEAVE 语句
1.1 分支结构
1.1.1 IF THEN
IF 表达式1 THEN 操作1
[ELSEIF 表达式2 THEN 操作2]……
[ELSE 操作N]
END IF
????????特点：① 不同的表达式对应不同的操作 ② 使用在begin end中
????????举例：声明存储过程“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 ;
1.1.2 CASE
#情况一：类似于switch
CASE 表达式
WHEN 值1 THEN 结果1或语句1(如果是语句，需要加分号)
WHEN 值2 THEN 结果2或语句2(如果是语句，需要加分号)
...
ELSE 结果n或语句n(如果是语句，需要加分号)
END [case]（如果是放在begin end中需要加上case，如果放在select后面不需要）
#情况二：类似于多重if
CASE
WHEN 条件1 THEN 结果1或语句1(如果是语句，需要加分号)
WHEN 条件2 THEN 结果2或语句2(如果是语句，需要加分号)
...
ELSE 结果n或语句n(如果是语句，需要加分号)
END [case]（如果是放在begin end中需要加上case，如果放在select后面不需要）
#例：
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;
????????声明存储过程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 ;
1.2?循环结构
1.2.1?LOOP
????????LOOP循环语句用来重复执行某些语句。LOOP内的语句一直重复执行直到循环被退出（使用LEAVE子句），跳出循环过程。
[loop_label:] LOOP
循环执行的语句
END LOOP [loop_label]
#loop_label表示LOOP语句的标注名称，该参数可以省略。
????????使用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;
1.2.2?WHILE
????????WHILE语句创建一个带条件判断的循环过程。先判断，再执行。WHILE语句的基本格式如下：
[while_label:] WHILE 循环条件 DO
循环体
END WHILE [while_label];
#while_label为WHILE语句的标注名称
????????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();
1.2.3?REPEAT
????????REPEAT语句创建一个带条件判断的循环过程，先执行再判断。类似于Java的 DO WHILE。
[repeat_label:] REPEAT
循环体的语句
UNTIL 结束循环的条件表达式
END REPEAT [repeat_label]
#repeat_label为REPEAT语句的标注名称
????????当市场环境变好时，公司为了奖励大家，决定给大家涨工资。声明存储过程 “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.3 跳转语句
? ? ? ? LEAVE：
? ? ? ? 可以用在循环语句内，或者以 BEGIN 和 END 包裹起来的程序体内，表示跳出循环或者跳出 程序体的操作。可以把 LEAVE 理解为 break。
????????ITERATE：
????????只能用在循环语句（LOOP、REPEAT和WHILE语句）内，表示重新开始循环，将执行顺序 转到语句段开头处。可以把 ITERATE 理解为 continue。
二、游标
2.1 概念
????????游标，提供了一种灵活的操作方式，让我们能够对结果集中的每一条记录进行定位，并对指向的记录中的数据进行操作的数据结构，类似一个指针。游标让 SQL 这种面向集合的语言有了面向过程开发的能力。
????????MySQL中游标可以在存储过程和函数中使用。
????????游标为 逐条读取 结果集中的数据，提供了完美的解决方案。跟在应用层面实现相同的功能相比，游标可以在存储程序中使用，效率高，程序也更加简洁。 但同时也会带来一些性能问题，比如在使用游标的过程中，会对数据行进行加锁 ，这样在业务并发量大的时候，不仅会影响业务之间的效率，还会消耗系统资源 ，造成内存不足，这是因为游标是在内存中进行的处理。
2.2 使用步骤
1 声明游标
????????在MySQL中，使用DECLARE关键字来声明游标
DECLARE cursor_name CURSOR FOR select_statement;
#要使用 SELECT 语句来获取数据结果集，而此时还没有开始遍历数据，这里 select_statement
#代表的是SELECT 语句，返回一个用于创建游标的结果集，例如：
DECLARE cur_emp CURSOR FOR
SELECT employee_id,salary FROM employees;
2 打开游标
OPEN cursor_name
#当我们定义好游标之后，如果想要使用游标，必须先打开游标。打开游标的时候 SELECT 语句的
#查询结果集就会送到游标工作区，为后面游标的 逐条读取 结果集中的记录做准备。
3 使用游标
FETCH cursor_name INTO var_name [, var_name] ...
#这句的作用是使用 cursor_name 这个游标来读取当前行，并且将数据保存到 var_name 这个变量中，游
标指针指到下一行。如果游标读取的数据行有多个列名，则在 INTO 关键字后面赋值给多个变量名即可。
#注意：var_name必须在声明游标之前就定义好。
游标的查询结果集中的字段数，必须跟 INTO 后面的变量数一致。
例如：
FETCH cur_emp INTO emp_id, emp_sal ;
4 关闭游标
CLOSE cursor_name
#当我们使用完游标后需要关闭掉该游标。因为游标会占用系统资源，
如果不及时关闭，游标会一直保持到存储过程结束，影响系统运行的效率。
而关闭游标的操作，会释放游标占用的系统资源。
2.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 ;
|