MySQL的变量、流程控制与游标

#查看全局系统变量
SHOW GLOBAL VARIABLES;

#查看会话系统变量
SHOW SESSION VARIABLES;

#查询指定会话系统变量
SELECT @@global.max_connections;

#查询指定全局系统变量
SELECT @@global.character_set_client;

查询结果：

全局系统变量：

会话系统变量：

3.修改系统变量的值

• set @@global.变量名

• set global 全局变量名

SET @@global.max_connections = 161;
SET GLOBAL max_connections = 171;

• 针对于当前的数据库实例有效，一旦重启mysql服务，就失效了

• set @@session.变量名

• set session 变量名

SET @@session.admin_port = 161;
SET SESSION admin_port = 171;

• 针对于当前会话有效，一旦结束会话，重新建立起新的会话，就失效

4.用户变量

• 分为会话用户变量(@修饰)、局部变量

• 会话用户变量：使用@开头，只对当前连接会话有效

• 局部变量：只在begin和end语句有效，只能在存储过程和函数使用

4.1会话用户变量

1.变量声明与赋值

• set @用户变量 := 值

• select @用户变量 := 语句 from

• select 语句 into @用户变量 from 表名

#设置会话用户变量
SET @m1 := 1;

SELECT @count := COUNT(*) FROM employees;

SELECT AVG(salary) INTO @salary FROM employees;

2.变量使用

• select @变量名

#变量的使用
SET @sum := @m1 + @count;

SELECT @sum;

4.2.局部变量

1.使用declare 声明

• 必须声明并使用在begin…end中

• 必须使用在存储过程/函数中

• declare的方式声明的局部变量必须声明在begin中的首行的位置

• 必须变量指明类型

2.局部变量声明格式

• declare 变量名类型

3.局部变量赋值

• set 变量 := 值

• select 语句 into 变量 from 表名

4.变量使用

• select 变量名

以上所有的代码示例：

DELIMITER $

CREATE PROCEDURE test()
BEGIN
	#局部变量声明
	DECLARE a INT DEFAULT 0;
	DECLARE emp_name VARCHAR(20);

	#局部变量赋值
	SET a := 1;
	SELECT last_name INTO emp_name FROM employees WHERE emp_id = 111;

	#局部变量使用
	SELECT a,emp_name;
END $

DELIMITER ;

#调用存储过程
CALL test();

5.定义条件与处理程序

1.定义条件：

给mysql中的错误码命名，有助于存储的程序代码更清晰

将一个错误名字和指定的错误条件关联起来

2.定义条件格式：

declare 错误名称 condition for 错误码

错误码：举例：

ERROR 1148(42000)；

1418-mysql_error_code；

HY000-sqlstate_value

#定义 “ERROR 1148(42000)”错误，名称为command_not_allowed
#定义方式1：使用MySQL_error_code
DECLARE command_not_allowed CONDITION FOR 1148;

#定义方式2：使用sqlstate_value
DECLARE command_not_allowed CONDITION FOR SQLSTATE '42000';

3.处理程序：

遇到问题时应当采取的处理方式，并且保证存储过程/函数在遇到错误时能够继续执行

• declare 处理方式 handler for 错误类型处理语句

• 处理方式：continue、exit、undo(不支持）

#定义处理程序
#方式1：捕获sqlstate_value
DECLARE CONTINUE HANDLER FOR SQLSTATE '42000' SET @info = 'NO_SUCH_TABLE';

#方式2：捕获MySQL_error_code
DECLARE CONTINUE HANDLER FOR 1148 SET @info = 'NO_SUCH_TABLE';

#方式3
DECLARE EXIT HANDLER FOR SQLWARNING SET @info = 'ERROR';

6.流程控制之分支结构

• if使用

#IF的使用
DELIMITER $

CREATE PROCEDURE test()
BEGIN
	DECLARE var INT DEFAULT 20;
	
	IF var > 40
		THEN SELECT '中年';
	ELSEIF var > 18
		THEN SELECT '青年';
	ELSEIF var > 8
		THEN SELECT '少年';
	ELSE 
		SELECT '幼年';
	END IF;
END $

DELIMITER ;

• case使用

#CASE的使用
DELIMITER $

CREATE PROCEDURE test()
BEGIN
	DECLARE var INT DEFAULT 20;
	
	CASE var
		WHEN 40 THEN SELECT '中年';
		WHEN 20 THEN SELECT '青年';
		WHEN 10 THEN SELECT '少年';
		ELSE SELECT '幼年';
	END CASE;
END $

DELIMITER ;

7.循环结构

• 循环结构四要素：初始化条件、循环条件、循环体、迭代条件

• loop

#loop的使用
DELIMITER $

CREATE PROCEDURE test()
BEGIN
	DECLARE var INT DEFAULT 20;
	loop_label:LOOP
		SET var = var + 1;
		IF var >= 10 THEN LEAVE loop_label;
		END IF;
	END LOOP loop_label;

END $

DELIMITER ;

• while

#while的使用
DELIMITER $

CREATE PROCEDURE test()
BEGIN
	DECLARE var INT DEFAULT 20;
	WHILE var <= 10 DO
		SET num = num + 1;
	END WHILE;

END $

DELIMITER ;

• repeat相当于-do…while…

#repeat的使用
DELIMITER $

CREATE PROCEDURE test()
BEGIN
	DECLARE var INT DEFAULT 20;
	REPEAT
		SET var = var + 1;
		UNTIL var >= 10
	END REPEAT;
END $

DELIMITER ;

8.跳转语句

• leave-相当于break

• iterate-相当于continue；只能在循环结构中使用

#跳转语句之leave
DELIMITER $

CREATE PROCEDURE leave_begin(IN num INT)
begin_label: BEGIN
IF num<=0
THEN LEAVE begin_label;
ELSEIF num=1
THEN SELECT AVG(salary) FROM employees;
ELSEIF num=2
THEN SELECT MIN(salary) FROM employees;
ELSE
SELECT MAX(salary) FROM employees;
END IF;
SELECT COUNT(*) FROM employees;
END $

DELIMITER ;

9.游标

1.游标的作用

• 游标让SQL这种面向集合的语言有了面向过程开发的能力

• 游标充当了指针的作用，是一种临时的数据库对象

2.使用游标的步骤

1. 定义游标：declare…cursor for…；（在oracle中使用cursor is）

2. 打开游标：open cursor_name

3. 使用游标(从游标中取得数据):fetch into

4. 关闭游标：close …

#游标的使用
DELIMITER //

CREATE PROCEDURE get_count_by_limit_total_salary(IN limit_total_salary DOUBLE,
OUT total_count INT)
BEGIN
	DECLARE emp_sal DOUBLE DEFAULT 0.0;
	DECLARE sum_sal DOUBLE DEFAULT 0.0;
	DECLARE count_re INT DEFAULT 0;
	
	
	#定义游标
	DECLARE emp_cursor CURSOR FOR SELECT salary FROM employees ORDER BY salary DESC;
	#打开游标
	OPEN emp_cursor;
	
	REPEAT
		#使用游标
		FETCH emp_cursor INTO emp_sal;
		SET sum_sal = sum_sal + emp_sal;
		SET count_re = count_re + 1;
		UNTIL sum_sal >= limit_total_salary
	END REPEAT;
	
	SET total_count = count_re;
	
	#关闭游标
	CLOSE emp_cursor;
END //

DELIMITER ;

为什么要关闭游标：

因为游标会占用系统资源，不及时关闭，游标会一直保持到存储过程结束，影响系统运行的效率