怎么做潮牌网站,农业畜牧网站开发,wordpress自定义域,手机网站建设讯息流程控制与游标 1.流程控制1.1分支结构之IF1.2分支结构值CASE1.3循环结构之LOOP1.4循环结构之WHILE1.5循环结构之REPEAT1.6跳转语句之LEAVE语句1.7跳转语句之ITERATE语句 2.游标2.1什么是游标2.2使用游标步骤4.3举例4.5小结 1.流程控制
解决复杂问题不可能通过一个 SQL 语句完… 流程控制与游标 1.流程控制1.1分支结构之IF1.2分支结构值CASE1.3循环结构之LOOP1.4循环结构之WHILE1.5循环结构之REPEAT1.6跳转语句之LEAVE语句1.7跳转语句之ITERATE语句 2.游标2.1什么是游标2.2使用游标步骤4.3举例4.5小结 1.流程控制
解决复杂问题不可能通过一个 SQL 语句完成我们需要执行多个 SQL 操作。流程控制语句的作用就是控制存储过程中 SQL 语句的执行顺序是我们完成复杂操作必不可少的一部分。只要是执行的程序流程就分为三大类
顺序结构程序从上往下依次执行分支结构程序按条件进行选择执行从两条或多条路径中选择一条执行循环结构程序满足一定条件下重复执行一组语句
针对于MySQL 的流程控制语句主要有 3 类。注意只能用于存储程序。
条件判断语句IF 语句和 CASE 语句循环语句LOOP、WHILE 和 REPEAT 语句跳转语句ITERATE 和 LEAVE 语句
1.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)BEGINDECLARE 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_yearFROM employees WHERE employee_id emp_id;IF emp_salary 8000 AND hire_year 5THEN 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)BEGINDECLARE 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_yearFROM employees WHERE employee_id emp_id;IF emp_salary 9000 AND hire_year 5THEN 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)BEGINDECLARE 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 9000THEN UPDATE employees SET salary 9000 WHERE employee_id emp_id;ELSEIF emp_salary 10000 AND bonus IS NULLTHEN UPDATE employees SET commission_pct 0.01 WHERE employee_id emp_id;ELSEUPDATE employees SET salary salary 100 WHERE employee_id emp_id;END IF;END //DELIMITER ;1.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 valWHEN 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
CASEWHEN 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)
BEGINDECLARE 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;CASEWHEN emp_sal9000THEN UPDATE employees SET salary9000 WHERE employee_id emp_id;WHEN emp_sal10000 AND bonus IS NULLTHEN UPDATE employees SET commission_pct0.01 WHERE employee_id emp_id;ELSEUPDATE employees SET salarysalary100 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)
BEGINDECLARE 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_yearWHEN 0 THEN UPDATE employees SET salarysalary50 WHERE employee_id emp_id;WHEN 1 THEN UPDATE employees SET salarysalary100 WHERE employee_id emp_id;WHEN 2 THEN UPDATE employees SET salarysalary200 WHERE employee_id emp_id;WHEN 3 THEN UPDATE employees SET salarysalary300 WHERE employee_id emp_id;WHEN 4 THEN UPDATE employees SET salarysalary400 WHERE employee_id emp_id;ELSE UPDATE employees SET salarysalary500 WHERE employee_id emp_id;END CASE;
END //DELIMITER ;1.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)
BEGINDECLARE avg_salary DOUBLE;DECLARE loop_count INT DEFAULT 0;SELECT AVG(salary) INTO avg_salary FROM employees;label_loop:LOOPIF 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 ;1.4循环结构之WHILE
WHILE语句创建一个带条件判断的循环过程。WHILE在执行语句执行时先对指定的表达式进行判断如果为真就执行循环内的语句否则退出循环。WHILE语句的基本格式如下
[while_label:] WHILE 循环条件 DO循环体
END WHILE [while_label];while_label为WHILE语句的标注名称如果循环条件结果为真WHILE语句内的语句或语句群被执行直至循环条件为假退出循环。
举例1WHILE语句示例i值小于10时将重复执行循环过程代码如下
DELIMITER //CREATE PROCEDURE test_while()
BEGIN DECLARE i INT DEFAULT 0;WHILE i 10 DOSET 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)
BEGINDECLARE avg_sal DOUBLE ;DECLARE while_count INT DEFAULT 0;SELECT AVG(salary) INTO avg_sal FROM employees;WHILE avg_sal 5000 DOUPDATE 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 ;1.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 10END REPEAT;SELECT i;
END //DELIMITER ;举例2 当市场环境变好时公司为了奖励大家决定给大家涨工资。声明存储过程“update_salary_repeat()”声明OUT参数num输出循环次数。存储过程中实现循环给大家涨薪薪资涨为原来的1.15倍。直到全公司的平均薪资达到13000结束。并统计循环次数。
DELIMITER //CREATE PROCEDURE update_salary_repeat(OUT num INT)
BEGINDECLARE avg_sal DOUBLE ;DECLARE repeat_count INT DEFAULT 0;SELECT AVG(salary) INTO avg_sal FROM employees;REPEATUPDATE employees SET salary salary * 1.15;SET repeat_count repeat_count 1;SELECT AVG(salary) INTO avg_sal FROM employees;UNTIL avg_sal 13000END REPEAT;SET num repeat_count;END //DELIMITER ;对比三种循环结构 1、这三种循环都可以省略名称但如果循环中添加了循环控制语句LEAVE或ITERATE则必须添加名称。 2、
LOOP一般用于实现简单的死循环WHILE先判断后执行REPEAT先执行后判断无条件至少执行一次
1.6跳转语句之LEAVE语句
LEAVE语句可以用在循环语句内或者以 BEGIN 和 END 包裹起来的程序体内表示跳出循环或者跳出程序体的操作。如果你有面向过程的编程语言的使用经验你可以把 LEAVE 理解为 break。 基本格式如下
LEAVE 标记名其中label参数表示循环的标志。LEAVE和BEGIN … END或循环一起被使用。 举例1 创建存储过程 “leave_begin()”声明INT类型的IN参数num。给BEGIN…END加标记名并在BEGIN…END中使用IF语句判断num参数的值。
如果num0则使用LEAVE语句退出BEGIN…END如果num1则查询“employees”表的平均薪资如果num2则查询“employees”表的最低薪资如果num2则查询“employees”表的最高薪资。IF语句结束后查询“employees”表的总人数
DELIMITER //CREATE PROCEDURE leave_begin(IN num INT)begin_label: BEGINIF num0 THEN LEAVE begin_label;ELSEIF num1 THEN SELECT AVG(salary) FROM employees;ELSEIF num2 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 THENLEAVE 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 ;1.7跳转语句之ITERATE语句
ITERATE语句只能用在循环语句LOOP、REPEAT和WHILE语句内表示重新开始循环将执行顺序转到语句段开头处。如果你有面向过程的编程语言的使用经验你可以把 ITERATE 理解为 continue意思为“再次循环”。 语句基本格式如下
ITERATE labellabel参数表示循环的标志。ITERATE语句必须跟在循环标志前面 举例 定义局部变量num初始值为0。循环结构中执行num 1操作。
如果num 10则继续执行循环如果num 15则退出循环结构
DELIMITER //CREATE PROCEDURE test_iterate()BEGINDECLARE num INT DEFAULT 0;my_loop:LOOPSET 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 ;2.游标
2.1什么是游标
虽然我们也可以通过筛选条件 WHERE 和 HAVING或者是限定返回记录的关键字 LIMIT 返回一条记录但是却无法在结果集中像指针一样向前定位一条记录、向后定位一条记录或者是随意定位到某一条记录并对记录的数据进行处理。
这个时候就可以用到游标。游标提供了一种灵活的操作方式让我们能够对结果集中的每一条记录进行定位并对指向的记录中的数据进行操作的数据结构。游标让 SQL 这种面向集合的语言有了面向过程开发的能力。
在 SQL 中游标是一种临时的数据库对象可以指向存储在数据库表中的数据行指针。这里游标充当了指针的作用我们可以通过操作游标来对数据行进行操作。
MySQL中游标可以在存储过程和函数中使用。
比如我们查询了 employees 数据表中工资高于15000的员工都有哪些
SELECT employee_id,last_name,salary FROM employees
WHERE salary 15000;这里我们就可以通过游标来操作数据行如图所示此时游标所在的行是“108”的记录我们也可以在结果集上滚动游标指向结果集中的任意一行。
2.2使用游标步骤
游标必须在声明处理程序之前被声明并且变量和条件还必须在声明游标或处理程序之前被声明。
如果我们想要使用游标一般需要经历四个步骤。不同的 DBMS 中使用游标的语法可能略有不同。 第一步声明游标 在MySQL中使用DECLARE关键字来声明游标其语法的基本形式如下
DECLARE cursor_name CURSOR FOR select_statement; 这个语法适用于 MySQLSQL ServerDB2 和 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_salaryDOUBLE类型声明OUT参数total_countINT类型。函数的功能可以实现累加薪资最高的几个员工的薪资值直到薪资总和达到limit_total_salary参数的值返回累加的人数给total_count。
DELIMITER //CREATE PROCEDURE get_count_by_limit_total_salary(IN limit_total_salary DOUBLE,OUT total_count INT)BEGINDECLARE 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_salaryEND REPEAT;SET total_count emp_count;#关闭游标CLOSE emp_cursor;END //DELIMITER ;4.5小结
游标是 MySQL 的一个重要的功能为逐条读取结果集中的数据提供了完美的解决方案。跟在应用层面实现相同的功能相比游标可以在存储程序中使用效率高程序也更加简洁。
但同时也会带来一些性能问题比如在使用游标的过程中会对数据行进行加锁这样在业务并发量大的时候不仅会影响业务之间的效率还会消耗系统资源造成内存不足这是因为游标是在内存中进行的处理。
建议养成用完之后就关闭的习惯这样才能提高系统的整体效率。
