第十六章:变量、流程控制与游标
16.1:变量
在MySQL
数据库的存储过程和函数中,可以使用变量来存储查询或计算的中间结果数据,或输出最终的结果数据。变量分为系统变量和用户自定义变量。
-
系统变量
变量由系统定义,不是用户定义,输入服务器层面。启动
MySQL
服务,生成MySQL
服务实例期间,MySQL
将为MySQL
服务器内存中的系统变量赋值,这些系统变量定义了当前MySQL
服务实例的属性、特征。 可以通过网址
https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html
查看MySQL
文档的系统变量。-
系统变量的分类
- 全局系统变量:需要添加
global
关键字,全局系统变量针对于所有的会话(连接)有效,但不能跨重启。 - 会话系统变量:需要添加
session
关键字,会话系统变量仅针对于当前会话(连接)有效,当前会话对某个系统变量值的修改,不会影响其他会话同一个系统变量的值。
- 全局系统变量:需要添加
-
查看系统变量
#1.查看所有或部分系统变量 #查询全局系统变量 SHOW GLOBAL VARIABLES; SHOW GLOBAL VARIABLES LIKE 'admin_%'; #查询会话系统变量 SHOW SESSION VARIABLES; SHOW VARIABLES; #默认查询的是会话系统变量 SHOW VARIABLES LIKE 'character_%'; #2.查看指定系统变量 SELECT @@global.max_connections; SELECT @@session.pseudo_thread_id; SELECT @@character_set_client; #先查询会话系统变量,再查询全局系统变量 #3.修改系统变量的值 #全局系统变量:方式1: SET @@global.max_connections = 161; #方式2: SET GLOBAL max_connections = 171; #会话系统变量:方式1: SET @@session.character_set_client = 'gbk'; #方式2: SET SESSION character_set_client = 'utf8mb4';
-
-
全局变量持久化(
MySQL8.0
新特性)-
在
MySQL
数据库中,全局变量可以通过SET GLOBAL
语句来设置。但是使用SET GLOBAL
语句设置的变量只会临时生效。数据库重启后,服务器又会从MySQL
配置文件中读取变量的默认值。 -
MySQL8.0
版本新增了SET PERSIST
命令,MySQL
会将该命名的配置保存到数据目录下的mysqld-auto.cnf
文件中,下次启动是会读取该文件,用其中的配置来覆盖默认的配置文件。 -
举例
SET PERSIST GLOBAL max_connections = 1000;
-
-
用户变量
-
用户变量分类
用户变量是用户自己定义的,作为
MySQL
编码规范,MySQL
中的用户变量以一个@
开头。根据作用范围不同,又分为会话用户变量和局部变量。- 会话用户变量:作用域和会话变量一样,只对当前连接会话有效。
- 局部变量:只在
BEGIN
和END
语句块中有效。局部变量只能在存储过程和函数中使用。
-
会话用户变量
# 定义用户变量方式一 SET @m1 = 1; SET @m2 := 2; # 定义用户变量方式二 SELECT @count := COUNT(*) FROM employees; SELECT AVG(salary) INTO @avg_sal FROM employees; #查看用户变量 SELECT @m1; SELECT @count; SELECT @avg_sal;
-
局部变量
可以使用
DECLARE
语句定义一个局部变量,仅仅在定义它的BEGIN ... END
中有效,只能放在BEGIN ... END
中,而且只能放在第一句。#定义变量的语法 DECLARE 变量名 类型 [default 值]; #如果没有default子句,初始值为NULl #举例:声明局部变量,并分别赋值为employees表中employee_id为102的last_name和salary DELIMITER // CREATE PROCEDURE test_pro() BEGIN #声明 DECLARE emp_name VARCHAR(25); DECLARE sal DOUBLE(10, 2) DEFAULT 0.0; #赋值 SELECT last_name, salary INTO emp_name, sal FROM employees WHERE employee_id = 102; #使用 SELECT emp_name, sal; END // DELIMITER; #调用存储过程 CALL test_pro();
-
对比会话用户变量与局部变量
作用域 定义位置 语法 会话用户变量 当前会话 会话的任何地方 加 @
符号,不用指定类型局部变量 定义它的的 BEGIN END
中BEGIN END
的第一行一般不用加 @
,需要指定类型
-
16.2:定义条件与处理程序
定义条件是事先定义程序执行过程中可能遇到的问题,处理程序定义了在遇到问题时应当采取方式,并且保证存储过程或函数在遇到警告或错误时能继续执行。这样可以增强存储程序处理问题的能力,避免程序异常停止运行。
-
定义条件
定义条件就是给
MySQL
中的错误码命名,这有助于存储的程序代码更清晰。它将一个错误名字和指定的错误条件关联起来。这个名字可以随后被用在定义处理程序的DECLARE HAVDLER
语句中。语法格式如下:DECLARE 错误名称 CONDITION FOR 错误码(或错误条件)
-
错误码说明
-
MySQL_error_code
是数值类型错误代码。 -
sqlstate_value
是长度为5的字符串类型错误代码。
-
-
举例
#方式1:使用MySQL_error_code DECLARE field_Not_Be_NULL CONDITION FOR 1048; #方式2:使用sqlstate_value DECLARE command_not_allowed CONDITION FOR SQLSTATE '42000';
-
-
定义处理程序
可以为
SQL
执行过程中发生的某种类型的错误定义特殊的处理程序。定义处理程序时,使用DECLARE
语句的语法如下:DECLARE 处理方式 HANDER FOR 错误类型 处理语句
-
处理方式
-
CONITNUE
:表示遇到错误不处理,继续执行。 -
EXIT
:表示遇到错误马上退出。 -
UNDO
:表示遇到错误后撤回之前的操作。MySQL
中暂时不支持这样的操作。
-
-
错误类型(即条件)可以有如下取值:
-
SQLSTATE '字符串错误码'
:表示长度为5sqlstate_value
类型的错误代码。 -
MySQL_error_code
:匹配数值类型错误代码。 - 错误名称:表示
DECLAER ... CONDITION
定义的错误条件名称。 -
SQLWARNING
:匹配所有以01
开头的SQLSTATE
错误代码。 -
NOT FOUND
:匹配所有以02
开头的SQLSTATE
错误代码。 -
SQLEXCEPTION
:匹配所有没有被SQLWARNING
或NOT FOUND
捕获的SQLSTATE
错误代码。
-
-
处理语句:
如果出现上述条件之一,则采用对应的处理方式,并执行指定的处理语句。语句可以是想
SET 变量 = 值
这样的简单,也可以是使用BEGIN ... END
编写复合语句。 -
举例
#方法1:捕获sqlstate_value DECLARE CONTINUE HANDLER FOR SQLSTATE '42S02' SET INTO = 'NO_SUCH_TABLE'; #方法2:捕获mysql_error_value DECLARE CONTINUE HANDLER FOR 1146 SET @info = 'NO_SUCH_TABLE'; #方法3:先定义条件,在调用 DECLARE no_such_table CONDITION FOR 1146; DECLARE CONTINUE HANDLER FOR no_such_table SET @info = 'NO_SUCH_TABLE';
-
-
案例解决
在存储过程中,定义处理程序,捕获
sqlstate_value
值,当遇到MySQL_error_code
值为1048
时,执行CONTINUE
操作,并且将@proc_value
的值设置为-1
。DELIMITER // CREATE PROCEDURE UpdateDataNoCondition() BEGIN #声明处理程序 DECLARE CONTINUE HANDLER FOR 1048 SET @proc_value = -1; SET @x = 1; UPDATE employees SET email = NULL WHERE last_name = 'Abel'; SET @x = 2; UPDATE employees SET email = 'aabbel' WHERE last_name = 'Abel'; SET @x = 3; END // DELIMITER; #调用存储过程: CALL UpdateDataNoCondition(); #查看变量: SELECT @x, @proc_value;
16.3:流程控制
解决复杂问题不可能通过一个SQL
语句完成,我们需要执行多个SQL
操作。流程控制语句的作用就是控制存储过程中SQL
语句的执行顺序,是我们完成复杂操作比不可少的一部分。只要是执行的程序,流程就分为三大类:
-
顺序结构:程序从上往下依次执行。
-
分支结构:程序按条件进行选择执行,从两条或多条路径中选择一条执行。
-
IF
语句:-
语法
IF 表达式1 THEN 操作1 [ELSEIF 表达式2 THEN 操作2] ... [ELSE 操作N] END IF
特点:不同的表达式对应不同的操作,使用在
BEGIN END
中。 -
举例
#举例1 DELIMITER // CREATE PROCEDURE test_if() BEGIN DECLARE age INT DEFAULT 20; IF age > 40 THEN SELECT '中老年'; ELSEIF age > 18 THEN SELECT '青壮年'; ELSEIF age > 8 THEN SELECT '青少年'; ELSE SELECT '婴幼儿'; END IF; END // DELIMITER; #调用 CALL test_if(); #举例2:声明存储过程“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 emp_sal DOUBLE; DECLARE bonus DOUBLE; #赋值 SELECT salary INTO emp_sal FROM employees WHERE employee_id = emp_id; SELECT commission_pct INTO bonus FROM employees WHERE employee_id = emp_id; #判断 IF emp_sal < 9000 THEN UPDATE employees SET salary = 9000 WHERE employee_id = emp_id; ELSEIF emp_sal < 10000 AND bonus 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; #调用 CALL update_salary_by_eid3(102);
-
-
CASE
语句:-
语法
# 情况一 CASE 表达式 WHEN 值1 THEN 结果1或语句1(如果是语句,需要加分号) WHEN 值2 THEN 结果2或语句2(如果是语句,需要加分号) ... ELSE 结果n或语句n(如果是语句,需要加分号) END [CASE](如果是放在begin end中需要加上case, 如果放在select后面不需要) # 情况二 CASE WHEN 条件1 THEN 结果1或语句1(如果是语句,需要加分号) WHEN 条件2 THEN 结果2或语句2(如果是语句,需要加分号) ... ELSE 结果n或语句n(如果是语句,需要加分号) END [CASE](如果是放在begin end中需要加上case, 如果放在select后面不需要)
-
举例
#举例1 DELIMITER // CREATE PROCEDURE test_case() BEGIN declare var1 int default 10; case when var1 >= 1000 then select '三位数'; when var1 >= 10 then select '两位数'; else select '个位数'; end case; END // DELIMITER; #调用 CALL test_case(); #举例2:声明存储过程update_salary_by_eid5,定义IN参数emp_id,输入员工编号。 #判断该员工的入职年限,如果是0年,薪资涨50;如果是1年,薪资涨100; #如果是2年,薪资涨200;如果是3年,薪资涨300;如果是4年,薪资涨400;其他的涨薪500。 DELIMITER // CREATE PROCEDURE update_salary_by_eid(IN emp_id INT) BEGIN #声明局部变量 DECLARE hire_year INT; #记录员工入职公司的总时间(单位:年) #赋值 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 = em_id; WHEN 1 THEN UPDATE employees SET salary = salary + 100 WHERE employee_id = em_id; WHEN 2 THEN UPDATE employees SET salary = salary + 200 WHERE employee_id = em_id; WHEN 3 THEN UPDATE employees SET salary = salary + 300 WHERE employee_id = em_id; WHEN 4 THEN UPDATE employees SET salary = salary + 400 WHERE employee_id = em_id; ELSE UPDATE employees SET salary = salary + 500 WHERE employee_id = emp_id; END CASE; END // DELIMITER; #调用 CALL update_salary_by_eid(101);
-
-
-
循环结构:程序满足一定条件下,重复执行一组语句。
-
LOOP
语句-
语法
[loop_lebel:] LOOP 执行循环的语句 END LOOP [loop_lebel](表示LOOP语句的标注名称,该参数可以省略)
-
举例
#举例1:当市场环境变好时,公司为了奖励大家,决定给大家涨工资。 #声明存储过程“update_salary_loop()”,声明OUT参数num,输出循环次数。 #存储过程中实现循环给大家涨薪,薪资涨为原来的1.1倍。直到全公司的平均薪资达到12000结束。并统计循环次数。 DELIMITER // CREATE PROCEDURE update_salary_loop(OUT num INT) BEGIN #声明变量 DECLARE avg_sal DOUBLE; #记录员工的平均工资 DECLARE loop_count INT DEFAULT 0; #记录循环的次数 #① 初始化条件 #获取员工的平均工资 SELECT AVG(salary) INTO avg_sal FROM employees; loop_lab: LOOP #② 循环条件 #结束循环的条件 IF avg_sal >= 12000 THEN LEAVE loop_lab; END IF; #③ 循环体 #如果低于12000,更新员工的工资 UPDATE employees SET salary = salary * 1.1; #④ 迭代条件 #更新avg_sal变量的值 SELECT AVG(salary) INTO avg_sal FROM employees; #记录循环次数 SET loop_count = loop_count + 1; END LOOP loop_lab; #给num赋值 SET num = loop_count; END // DELIMITER; #调用 CALL update_salary_loop(@num); SELECT @num;
-
-
WHILE
语句-
语法
# WHILE在执行语句执行时,先对指定的表达式进行判断,如果为真,就执行循环内的语句,否则退出循环。 [while_label:] WHILE 循环条件 DO 循环体 END WHILE [while_label];
-
举例
#举例2:市场环境不好时,公司为了渡过难关,决定暂时降低大家的薪资。 #声明存储过程“update_salary_while()”,声明OUT参数num,输出循环次数。 #存储过程中实现循环给大家降薪,薪资降为原来的90%。直到全公司的平均薪资达到5000结束。并统计循环次数。 DELIMITER // CREATE PROCEDURE update_salary_while(OUT num INT) BEGIN #声明变量 DECLARE avg_sal DOUBLE; DECLARE while_count INT DEFAULT 0; #赋值 SELECT AVG(salary) INTO avg_sal FROM employees; WHILE avg_sal > 5000 DO UPDATE employees SET salary = salary * 0.9; SET while_count = while_count + 1; SELECT AVG(salary) INTO avg_sal FROM employees; END WHILE; #给num赋值 SET num = while_count; END // DELIMITER; #调用 CALL update_salary_while(@num); SELECT @num;
-
-
REPEAT
语句-
语法
#REPEAT循环首先会执行一次循环,然后在UNTIL中进行表达式的判断,如果满足条件就退出,即 END REPEAT; #如果条件不满足,则会就继续执行循环,直到满足退出条件为止。 [repeat_label:] REPEAT 循环体的语句 UNTIL 结束循环的条件表达式 END REPEAT[repeat_label]
-
举例
#举例2:当市场环境变好时,公司为了奖励大家,决定给大家涨工资。 #声明存储过程“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.5; SET repeat_count = repeat_count + 1; SELECT AVG(salary) INTO avg_sal FROM employees; UNTIL avg_sal >= 13000 END REPEAT; #给num赋值 SET num = repeat_count; END // DELIMITER; #调用 CALL update_salary_repeat(@num); SELECT @num;
-
-
-
跳转语句:在某些特定的条件下,跳出循环结构语句。
-
LEAVE
语句可以用在循环语句内,或者以
BEGIN
和END
包裹起来的程序题内,表示跳出循环或者跳出程序体的操作。LEAVE 标记名;
-
ITERATE
语句只能用在循环语句(
LOOP
、REPEAT
和WHILE
)内,表示循环开始循环,将执行顺序转到语句段开头处。ITERATE label;
-
举例
#LEAVE的使用 #举例1:当市场环境不好时,公司为了渡过难关,决定暂时降低大家的薪资。 #声明存储过程“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 THEN LEAVE 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; #调用 CALL leave_while(@num); SELECT @num; #ITERATE的使用 /* 举例2: 定义局部变量num,初始值为0。循环结构中执行num + 1操作。 如果num < 10,则继续执行循环;如果num > 15,则退出循环结构; */ DELIMITER // CREATE PROCEDURE test_iterate() BEGIN DECLARE num INT DEFAULT 0; loop_label: LOOP #赋值 SET num = num + 1; IF num < 10 THEN ITERATE loop_label; ELSEIF num > 15 THEN LEAVE loop_label; END IF; SELECT '小王同学在学mysql'; END LOOP; END // DELIMITER; #调用 CALL test_iterate();
-
16.4:游标
-
什么是游标
游标提供了一种灵活的操作方式,让我们能够对结果集中的每一条记录进行定位,并对指向的记录中的数据进行操作的数据结构。游标让
SQL
这种面向集合的语言有了面向过程开发的能力。 在
SQL
中,游标是一种临时的数据库对象,可以指向存储在数据库表中的数据行指针。这里游标充当了指针的作用,我们可以通过操作游标来对数据进行操作。
MySQL
中游标可以在存储过程和存储函数中使用。 -
使用游标步骤
游标必须在声明处理程序之前被声明,并且变量和条件还必须在声明游标或处理程序之前被声明。
-
第一步:声明游标
在
MySQL
中,使用DECLARE
关键字来声明游标,其语法的基本形式如下:# 适用于mysql和SQL Server、DB2和MariaDB DECLARE cursor_name CURSOR FOR select_statement; # 适用于Oracle或者PostgreSQL DECLARE cursor_name CURSOR IS select_statement;
要使用
SELECT
语句来获取数据结果集,而此时还没有开始遍历数据,这里select_statement
代表的是SELECT
语句,返回一个用于创建游标的结果集。 -
第二步:打开游标
OPEN cursor_name;
当我们定义好游标之后,如果想要使用游标,必须先打开游标。打开游标的时候
SELECT
语句的查询结果集就会送到游标工作区,为后面游标的逐条读取结果集中的记录做准备。 -
第三步:使用游标(从游标中取得数据)
FETCH cursor_name INTO var_name [, var_name]....
使用
cursor_name
这个游标来读取当前行,并且将数据保存到var_name
这个变量中,游标指针到下一行。如果游标读取的数据行有多个列名,则在INTO
关键字后面赋值给多个变量名即可。 -
第四步:关闭游标
CLOSE cursor_name;
有
OPEN
就会有CLOSE
,也就是打开和关闭游标。当我们使用完游标后需要关掉该游标。因为游标会占用系统资源,如果不及时关闭,游标会一直保持到存储过程结束,影响系统运行的效率。而关闭游标的操作,会释放游标占用的系统资源。文章来源:https://www.toymoban.com/news/detail-476323.html
-
-
举例文章来源地址https://www.toymoban.com/news/detail-476323.html
# 举例:创建存储过程“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.0; DECLARE emp_sal DOUBLE; DECLARE emp_count INT DEFAULT 0; #1.声明游标 DECLARE emp_cursor CURSOR FOR SELECT salary FROM employees ORDER BY salary DESC; #2.打开游标 OPEN emp_cursor; REPEAT #3.使用游标 FETCH emp_cursor INTO emp_sal; SET sum_sal = sum_sal + emp_sal; SET emp_count = emp_count + 1; UNTIL sum_sal >= limit_total_salary END REPEAT; SET total_count = emp_count; #4.关闭游标 CLOSE emp_cursor; END// DELIMITER; #调用 CALL get_count_by_limit_total_salary(200000, @total_count); SELECT @total_count;
到了这里,关于第十六章:变量、流程控制与游标的文章就介绍完了。如果您还想了解更多内容,请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章,希望大家以后多多支持TOY模板网!