MySQL的变量、流程控制与游标-Toy模板网

这篇具有很好参考价值的文章主要介绍了MySQL的变量、流程控制与游标。希望对大家有所帮助。如果存在错误或未考虑完全的地方，请大家不吝赐教，您也可以点击"举报违法"按钮提交疑问。

1.变量的分类

1.系统变量的分类

1.1.二者关系

2.查看系统变量

3.修改系统变量的值

4.用户变量

4.1会话用户变量

1.变量声明与赋值

2.变量使用

4.2.局部变量

1.使用declare 声明

2.局部变量声明格式

3.局部变量赋值

4.变量使用

5.定义条件与处理程序

1.定义条件：

2.定义条件格式：

3.处理程序：

6.流程控制之分支结构

7.循环结构

8.跳转语句

9.游标

1.游标的作用

2.使用游标的步骤

3.游标的优缺点

1.变量的分类

变量分为：系统变量、用户变量

1.系统变量的分类

全局系统变量

会话系统变量

1.1.二者关系

• 全局系统变量针对于所有会话有效，但不能 跨重启

• 会话系统变量针对当前会话有效，修改某个会话系统变量不会影响其他会话系统变量

• 修改全局系统变量也会修改其他会话的全局系统变量值

• 有些系统变量只能是全局系统变量，有些能当全局也能当会话系统变量，有些只能是会话系统变量

2.查看系统变量

• show global variables

• show session variables

• show variables - 默认查询会话系统变量

• select @@global.变量名/查看指定系统变量

• select @@session.变量名

• select @@变量名先查看会话，没有就查看全局系统变量

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

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

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

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

查询结果：

全局系统变量：

MySQL的变量、流程控制与游标,mysql,数据库

会话系统变量：

MySQL的变量、流程控制与游标,mysql,数据库

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 ;

为什么要关闭游标：

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