mysql学习笔记-游标

1、游标的基本概念

有数据缓冲的思想：游标的设计是一种数据缓冲区的思想，用来存放SQL语句执行的结果。

先有数据基础：游标是在先从数据表中检索出数据之后才能继续灵活操作的技术。

类似于指针：游标类似于指向数据结构堆栈中的指针，用来pop出所指向的数据，并且只能每次取一个。

如果你前面看过mysql函数，会发现无法使用返回多行结果的语句。但如果你又确实想要使用时，就需要使用到游标，游标可以帮你选择出某个结果（这样就可以做到返回单个结果）。

另外，使用游标也可以轻易的取出在检索出来的行中前进或后退一行或多行的结果。

游标可以遍历返回的多行结果。

• Mysql中游标只适用于存储过程以及函数。

2、游标的使用

游标的使用一般分为5个步骤，主要是：定义游标->打开游标->使用游标->关闭游标->释放游标。

需要强调的是，游标必须在定义处理程序之前被定义，但变量必须在定义游标之前被定义，顺序就是变量定义-游标定义-处理程序。

前提：

-- 创建两个表，表没有主键会一直循环
CREATE TABLE class(id int(5) PRIMARY KEY,name VARCHAR(10));
CREATE TABLE class2(id int(5) PRIMARY KEY,name VARCHAR(10));

-- class里插入数据
INSERT INTO class(`id`, `name`) VALUES ('1', 'ceshi');
SELECT * FROM class ;

-- class表里数据删除或者删除表
DELETE FROM class2;
DROP TABLE class2;

create procedure p2()
begin
    declare id int;
    declare name varchar(15);
    -- 声明游标
    declare mc cursor for select * from class;
    -- 打开游标
    open mc;
    -- 获取结果
    loop  -- 循环，将表的内容都转移到class2中
    fetch mc into id,name;
    -- 这里是为了显示获取结果
    insert into class2 values(id,name);
    -- 关闭游标
    end loop;
    close mc;
    
end;

上述代码报错

 

表class2有主键，不断循环的话，始终会达到表的末尾，到了末尾就无法继续fetch，一般来说都要避免报错，到了末尾前会有一个mysql定义的

delimiter //
create procedure p8()
begin
    declare id int;
    declare name varchar(15);
    declare flag int default 0;
    -- 声明游标
    declare mc cursor for select * from class;
    declare continue handler for not found set flag = 1;
    -- 打开游标
    open mc;
    -- 获取结果
    l2:loop 
    
    fetch mc into id,name;
    if flag=1 then -- 当无法fetch会触发handler continue
        leave l2;
    end if;
    -- 这里是为了显示获取结果
    insert into class2 values(id,name);
    -- 关闭游标
    end loop;
    close mc;
    
end;

//
delimiter ;
CALL p8();

CREATE TABLE `emp1` (
  `empno` int(10) NOT NULL,
  `ename` varchar(10) COLLATE utf8_bin DEFAULT NULL,
  `deptno` int(5) DEFAULT NULL,
  `sal` int(10) DEFAULT NULL,
  PRIMARY KEY (`empno`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

delimiter //
CREATE PROCEDURE test_cfl()
BEGIN
    DECLARE empno INT;
    DECLARE ename VARCHAR(15);-- 带字节长度
    DECLARE deptno INT; 
    DECLARE sal BIGINT;
    DECLARE flag INT DEFAULT 0;
    -- 游标定义
    DECLARE     cr_cfl CURSOR FOR SELECT * FROM emp;
    -- 异常值设置
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET flag = 1;
    
    -- 打开游标
    OPEN cr_cfl    ;
    -- 循环设置
    loop1:LOOP
    -- 获取游标值
    FETCH cr_cfl INTO empno,ename,deptno,sal;
    -- 无法获取时退出
    IF flag=1 THEN 
        LEAVE loop1;
    END IF;
    -- 获取的值插入另外一张表
    INSERT INTO emp1 VALUES(empno,ename,deptno,sal);
    END LOOP;
    -- 关闭游标
    CLOSE cr_cfl;
END;
//
delimiter ;
CALL test_cfl();
SELECT * FROM emp1;