mysql8.0游标

4、游标

4.1、什么是游标

虽然我们也可以通过筛选条件WHERE和HAVING，或者是限定返回记录的关键字LIMIT返回一条记录，
但是，却无法在结果集中像指针一样，向前定位一条记录、向后定位一条记录，或者是随意定位到某一条记录并对记录的数据进行处理。
这个时候，就可以用到游标。游标，提供了一种灵活的操作方式，让我们能够对结果集中的每一条记录进行定位，并对指向的记录中的数据进行操作的数据结构。
游标让SQL这种面向集合的语言有了面向过程开发的能力。

在SQL中，游标是一种临时的数据库对象，可以指向存储在数据库表中的数据行指针。这里游标充当了指针的作用
我们可以通过操作游标来对数据行进行操作。
MySQL中游标可以在存储过程和函数中使用。
比如，我们查询了employees数据表中工资高于15000的员工都有哪些：
SELECT employee_id,last_name,salary FROM employees WHERE salary>15000;

这里我们就可以通过游标来操作数据行，如图所示此时游标所在的行是“108”的记录，我们也可以在结果集上滚动游标，指向结果集中的任意一行。

4.2、使用游标步骤

游标必须在声明处理程序之前被声明，并且变量和条件还必须在声明游标或处理程序之前被声明。
如果我们想要使用游标，一般需要经历四个步骤。不同的DBMS中，使用游标的语法可能略有不同。

第一步，声明游标
在MySQL中，使用DECLARE关键字来声明游标，其语法的基本形式如下：
DECLARE cursor_name CURSOR FOR select_statement;
这个语法适用于MySQL，SQLServer，DB2和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语句的查询结果集就会送到游标工作区，为后面游标的逐条读取结果集中的记录做准备

第三步，使用游标（从游标中取得数据）
语法如下：
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，也就是打开和关闭游标。当我们使用完游标后需要关闭掉该游标。因为游标会占用系统资源，如果不及时关闭，游标会一直保持到存储过程结束，影响系统运行的效率。而关闭游标

4.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_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;

4.4、小结

游标是MySQL的一个重要的功能，为逐条读取结果集中的数据，提供了完美的解决方案。跟在应用层面实现相同的功能相比，游标可以在存储程序中使用，效率高，程序也更加简洁。
但同时也会带来一些性能问题，比如在使用游标的过程中，会对数据行进行加锁，这样在业务并发量大的时候，不仅会影响业务之间的效率，还会消耗系统资源，造成内存不足，
这是因为游标是在内存中进行的处理。
建议：养成用完之后就关闭的习惯，这样才能提高系统的整体效率

练习

#创建存储过程update_salary()，参数1为 IN 的INT型变量dept_id，表示部门id；参数2为 IN的INT型变量
#change_sal_count，表示要调整薪资的员工个数。查询指定id部门的员工信息，按照salary升序排列，根
#据hire_date的情况，调整前change_sal_count个员工的薪资，详情如下。
hire_date                                salary
hire_date < 1995                         salary = salary*1.2
hire_date >=1995 and hire_date <= 1998   salary = salary*1.15
hire_date > 1998 and hire_date <= 2001   salary = salary *1.10
hire_date > 2001                         salary = salary * 1.05

DELIMITER //
CREATE PROCEDURE update_salary(IN dept_id INT,IN change_sal_count INT)
BEGIN
    #声明变量
    DECLARE int_count INT DEFAULT 0;
    DECLARE salary_rate DOUBLE DEFAULT 0.0;
    DECLARE emp_id INT;
    DECLARE emp_hire_date DATE;
    #声明游标
    DECLARE emp_cursor CURSOR FOR SELECT employee_id,hire_date FROM employees
    WHERE department_id = dept_id ORDER BY salary ;
    #打开游标
    OPEN emp_cursor;
    WHILE int_count < change_sal_count DO
        #使用游标
        FETCH emp_cursor INTO emp_id,emp_hire_date;
        IF(YEAR(emp_hire_date) < 1995)
            THEN SET salary_rate = 1.2;
        ELSEIF(YEAR(emp_hire_date) <= 1998)
            THEN SET salary_rate = 1.15;
        ELSEIF(YEAR(emp_hire_date) <= 2001)
            THEN SET salary_rate = 1.10;
        ELSE SET salary_rate = 1.05;
        END IF;
        #更新工资
        UPDATE employees SET salary = salary * salary_rate WHERE employee_id = emp_id;
        #迭代条件
        SET int_count = int_count + 1;
    END WHILE;
    #关闭游标
    CLOSE emp_cursor;
END //
DELIMITER ;
# 调用
CALL update_salary(50,2);