游标

# 准备工作：
# 1.导入atguigudb数据库
# 2.建立dbtest08数据库
CREATE DATABASE dbtest08 CHARACTER SET utf8;

#　3.切换当前数据库为dbtest08
USE dbtest08;

# 4.建立表
CREATE TABLE emps
AS
SELECT * FROM atguigudb.employees;

CREATE TABLE depts
AS
SELECT * FROM atguigudb.departments;

SELECT * FROM emps;

SELECT * FROM depts;
----------------------------------------游标-----------------------------------------------------
#例1：创建存储过程“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 i, emp_cnt INT;
    DECLARE emp_sal, sum_sal DOUBLE;
    # 定义游标
    DECLARE dataset CURSOR FOR SELECT salary FROM emps ORDER BY salary DESC;
    # 总人数
    SELECT COUNT(*) INTO emp_cnt FROM emps;
    SET i = 0;
    SET sum_sal = 0;
    SET total_count = 0;
    # 打开游标
    OPEN dataset;
    # 循环次数和总工资高于传入的工资数时停止
    WHILE i < emp_cnt AND sum_sal < limit_total_salary DO
        # 获取游标
	FETCH dataset INTO emp_sal;
	SET sum_sal = sum_sal + emp_sal;
	SET total_count = total_count + 1;
        SET i = i + 1;
    END WHILE;
    # 关闭游标
    CLOSE dataset;
END $
DELIMITER ;

CALL get_count_by_limit_total_salary(200000, @res);
SELECT @res;

DROP PROCEDURE get_count_by_limit_total_salary;

# 例2：创建存储过程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 i, emp_cnt INT;
    DECLARE emp_id, work_year INT;
    DECLARE dataset CURSOR FOR SELECT employee_id, YEAR(hire_date) FROM emps WHERE department_id = dept_id ORDER BY salary ASC;
    SET i = 0;
    SELECT COUNT(*) INTO emp_cnt FROM emps WHERE department_id = dept_id;
    OPEN dataset;
    WHILE i < emp_cnt AND i < change_sal_count DO
        FETCH dataset INTO emp_id, work_year;
        CASE
            WHEN work_year < 1995 THEN
                UPDATE emps SET salary = salary * 1.2 WHERE employee_id = emp_id;
            WHEN work_year BETWEEN 1995 AND 1998 THEN
                UPDATE emps SET salary = salary * 1.15 WHERE employee_id = emp_id;
            WHEN work_year BETWEEN 1999 AND 2001 THEN
                UPDATE emps SET salary = salary * 1.1 WHERE employee_id = emp_id;
            ELSE
                UPDATE emps SET salary = salary * 1.05 WHERE employee_id = emp_id;
        END CASE;
        SET i = i + 1;
    END WHILE;
    CLOSE dataset;
END //
DELIMITER ;

SELECT employee_id, salary, YEAR(hire_date) FROM emps WHERE department_id = 50 ORDER BY salary ASC;
CALL update_salary(50, 2);
SELECT employee_id, salary, YEAR(hire_date) FROM emps WHERE department_id = 50 ORDER BY salary ASC;
游标 ​

游标
