MySQL 游标的基本用法

MySQL游标只能用于存储过程。
使用游标的步骤：
①在能够使用游标前，必须声明它。这个过程实际上没有检索数据，它只是定义要使用的select语句。
游标的定义格式：
DECLARE 光标名称 CURSOR FOR 查询语法
declare cursor_name cursor for select_statement

②声明游标后，要打开游标以供使用。这个过程就是把前面定义的select语句把数据实际检索出来。
打开游标格式：
OPEN 光标名称
open cursor_name

③对于填有数据的游标，根据需要取出（检索）各行。
在游标被打开后，使用fetch语句分别访问它的每一行。fetch指定检索的列，并存储到已定义好的列中。然后继续向前移动游标中的内部行指针，使下一条fetch语句检索下一行（不重复读取同一行）。
取游标中的数据格式：
FETCH 光标名称 INFO var_name [，var_name ].....
fetch cursor_name info var_name

④在结束游标使用后，必须关闭游标。
关闭游标
CLOSE curso_name;
close 光标名称

创建部门表：

CREATE TABLE IF NOT EXISTS depart(
    bumenTableid INT(11) NOT NULL primary key auto_increment COMMENT'部门编号（主键）',
    bumenTablename VARCHAR(50) COMMENT'部门名称',
    bumenTableaddress VARCHAR(50) COMMENT'部门地址'
);

添加部门表数据：

INSERT INTO  depart(bumenTablename,bumenTableaddress) VALUES 
('销售部','销售部地址'),
('学业部','学业部地址'),
('董事部','董事部地址'),
('人力资源部','人力资源部地址'),
('产品部','产品部地址');

创建员工表：

CREATE TABLE emp(
    id INT(11) NOT NULL primary key auto_increment COMMENT'员工编号',
    yuangongname VARCHAR(50) COMMENT'员工姓名',
    word VARCHAR(50) COMMENT'员工工作',
    lineManagerId INT(11) COMMENT'员工直属领导编号',
    entryTime datetime COMMENT'员工入职时间',
    wage INT(11) COMMENT'员工工资',
    bonus INT(11) COMMENT'员工奖金',
    bumenTableId INT(11) NOT NULL COMMENT'对应部门表的外键',
    FOREIGN KEY(bumenTableId) REFERENCES  depart(bumenTableid)
);

添加员工表数据：

INSERT INTO emp (yuangongname,word,lineManagerId,entryTime,wage,bonus,bumenTableId) VALUES ('小王', '职员', '2', '2017-06-14 14:30:50', '4000', null, '1');
INSERT INTO emp (yuangongname,word,lineManagerId,entryTime,wage,bonus,bumenTableId) VALUES ('小李', '销售经理', '4', '2016-08-16 14:32:08', '20800', '5000', '1');
INSERT INTO emp (yuangongname,word,lineManagerId,entryTime,wage,bonus,bumenTableId) VALUES ('小张', '产品经理', '4', '2016-05-04 14:33:05', '22700', null, '5');
INSERT INTO emp (yuangongname,word,lineManagerId,entryTime,wage,bonus,bumenTableId) VALUES ('小高', '职员', null, '2015-07-08 14:33:54', '5000', null, '2');
INSERT INTO emp (yuangongname,word,lineManagerId,entryTime,wage,bonus,bumenTableId) VALUES ('小刘', 'HR经理', '4', '2017-11-08 14:35:35', '10000', null, '4');
INSERT INTO emp (yuangongname,word,lineManagerId,entryTime,wage,bonus,bumenTableId) VALUES ('王一', '学业经理', '4', '2016-11-01 14:36:28', '20000', '5000', '2');
INSERT INTO emp (yuangongname,word,lineManagerId,entryTime,wage,bonus,bumenTableId) VALUES ('王二', '职员', '3', '2018-03-22 14:38:44', '5000', null, '5');
INSERT INTO emp (yuangongname,word,lineManagerId,entryTime,wage,bonus,bumenTableId) VALUES ('李四', '职员', '5', '2017-04-01 14:39:53', '5000', null, '4');
INSERT INTO emp (yuangongname,word,lineManagerId,entryTime,wage,bonus,bumenTableId) VALUES ('李一', '职员', '6', '2018-08-01 14:40:43', '5000', null, '2');
INSERT INTO emp (yuangongname,word,lineManagerId,entryTime,wage,bonus,bumenTableId) VALUES ('李二', '职员', '2', '2018-05-17 14:41:30', '5000', null, '1');
INSERT INTO emp (yuangongname,word,lineManagerId,entryTime,wage,bonus,bumenTableId) VALUES ('李三', '职员', '2', '2017-05-01 14:42:20', '5000', null, '1');
INSERT INTO emp (yuangongname,word,lineManagerId,entryTime,wage,bonus,bumenTableId) VALUES ('公司人员', '其他', null, '2015-07-08 15:31:52', '1234567', '1234567', '5');

示例1（游标返回单条记录）：查询某个员工的姓名，职位、工资等

DROP PROCEDURE IF EXISTS pro_empIndepart;

delimiter //
CREATE PROCEDURE pro_empIndepart (IN empid INT)
    READS SQL DATA
BEGIN
    DECLARE  c_id INT;
    DECLARE  c_empname VARCHAR(50);
    DECLARE  c_job VARCHAR(50);
    DECLARE  c_salary INT;
    DECLARE cur CURSOR for select  e.id,e.yuangongname,e.word,e.wage from emp e where  e.id =  empid;    
    OPEN cur;
        FETCH cur INTO c_id,c_empname,c_job,c_salary;
        SELECT c_id,c_empname,c_job,c_salary;
    CLOSE cur;
END//

delimiter ;

调用存储过程：

CALL pro_empIndepart(5);

image.png

上述示例返回的是单条记录，所以不需要遍历结果集。

示例2（游标返回结果集）：查询某个部门下员工信息

DROP PROCEDURE IF EXISTS empIndepart_list;

delimiter //
create procedure empIndepart_list(IN departid INT)
begin
    declare done boolean default 0;
    DECLARE  c_id,c_salary INT;
  -- 注意 接收游标值为中文时 需要 给变量 指定 字符集为utf8
  DECLARE  c_empname,c_job VARCHAR(50) character set utf8;

    declare cur cursor
            for
          select  e.id,e.yuangongname,e.word,e.wage from emp e where e.bumenTableId=departid;
  declare continue handler for sqlstate '02000' set done=1;
    create table if not  exists emp_dempart_temp(id int,empname VARCHAR(50),job VARCHAR(50),salary int);
  truncate TABLE emp_dempart_temp;

    open cur;
         REPEAT
            fetch cur into  c_id,c_empname,c_job,c_salary;
                if done != 1 then
                    insert into emp_dempart_temp(id,empname,job,salary) values(c_id,c_empname,c_job,c_salary);
        end if;
    until done =1 end repeat;
    close cur;
  
end//
delimiter;

该示例，使用fetch检索指定列到声明的4个变量中。但与上一个例子不同的是，这个fetch是在repeat内，因为它反复执行直到done为真（由 until done =1 end repeat;规定）。为了使该语句起作用，用一个default 0来定义变量done。那么done怎样才能在结束时被设置为真呢？要使用以下语句：

declare continue handler for SQLSTATE '02000' SET done=1;

该语句定义了一个continue handler，它是在条件出现时被执行的代码。当SQLSTATE‘02000’出现时，set done=1。SQLSTATE’02000’是一个未找到条件，当REPEAT由于没有更多的行供循环而不能继续时，出现该条件。

注意：用declare定义的局部变量必须在定义任意游标或句柄之前定义，而句柄必须在游标之后定义（例如上面done语句）。不遵守此顺序将产生错误消息。

执行该存储过程，它将定义几个变量和一个continue handler，定义并打开一个游标，重复读取所有行，在fetch语句之后，循环结束之前可以在循环内放入任意需要的处理，例如向临时表中插入数据，查询某变量的值等。
上述示例，在打开游标之前创建了一个临时表：emp_dempart_temp，在遍历游标查询结果时，在满足done != 1的条件下向该表插入 存储过程中游标查询生成的结果。

调用存储过程：

CALL empIndepart_list(2);

使用select语句查看emp_dempart_temp的内容：

select * from emp_dempart_temp;

image.png