MySQL分析与整理 — 游标

素颜马尾好姑娘i 2023-02-27 08:26 52阅读 0赞

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xpbGk0MDM0Mg_size_16_color_FFFFFF_t_70_pic_center]

> 文章中所有操作均是在 MySQL 5.7 版本下进行的

到了 MySQL 高级阶段，不得不说下游标了，因为在高级的应用中，比如存储过程中，游标是经常涉及到问题。游标的设计是一种数据缓冲区的思想，用来存放 SQL 语句执行的结果。游标是一种能从包括多条数据记录的结果集中每次提取一条记录的机制。尽管游标能遍历结果中的所有行，但一次只指向一行。游标的作用就是用于对查询数据库所返回的记录进行遍历，以便进行相应的操作。

### 1 游标的几个操作 ###

游标不像函数，触发器，存储过程似的，创建是用 create 关键字，它更像是个变量，是用 declare 进行定义的。又因为游标是接收的结果集，所以它一般会和循环语句一起配合使用。

-- 定义游标（从定义游标发现它是用来接收一个查询语句结果集的）
    declare 游标名 cursor for select查询语句;  
    -- 打开游标
    open 游标名;
    -- 获取游标中的数据（游标的查询语句中的字段个数和变量个数对应）
    fetch 游标名 into 变量名 [, 变量名2, ... ]
    -- 指定游标循环结束时的返回的变量名和返回值（多用于循环语句的结束循环用）
    declare continue handler for not found set 变量名 = 值;
    -- 关闭游标
    close 游标名;

### 2 游标的使用示例 ###

#### 2.1 增加示例表和示例数据 ####

-- 示例数据库和示例数据
    drop table if exists tbl_account;
    create table tbl_account(
    	id int primary key,
        aname varchar(10),
        balance double
    )ENGINE=InnoDB default charset=utf8;
    insert into tbl_account values 
    (1001, '老孙', 1000),(1002, '老唐', 2300),(1003, '老猪', 20000);
    -- 再创建一个简单示例表
    drop table if exists tbl_account_bak;
    create table tbl_account_bak(
    	id int primary key,
        info varchar(10)
    )ENGINE=InnoDB default charset=utf8;

#### 2.2 在LOOP循环中的使用 ####

/*
    创建一个存储过程proc_test1
    读取tbl_account表的数据
    将id和aname存储到tbl_account_bak中
    */
    drop procedure if exists proc_test1;
    delimiter $
    create procedure proc_test1()
    begin
    	-- 定义两个变量接收id和balance
    	declare rs_name varchar(10);
    	declare rs_id int;
    	-- 定义游标结束变量
    	declare cur_done bit default false;
    	-- 游标定义
    	declare cursor_rs cursor for select id, aname from tbl_account;
    	declare continue handler for not found set cur_done = true;
    	-- 打开游标
    	open cursor_rs;
    	-- 开始循环
    	opt:loop
    		-- 每次循环读取游标数据并赋值给rs_id和rs_name，查询语句两个列，这里变量个数接收对应
    		fetch cursor_rs into rs_id, rs_name;
    		-- 加入判断循环跳出的条件
    		if cur_done then
    			leave opt;
    		end if;
    		-- 执行插入数据到tbl_account_bak
    		insert into tbl_account_bak values (rs_id, rs_name);
    	end loop;
        close cursor_rs;
    end
    $
    delimiter ;
    
    -- 调用测试
    call proc_test1();
    select * from tbl_account_bak;
    +------+--------+
    | id   | info   |
    +------+--------+
    | 1001 | 老孙   |
    | 1002 | 老唐   |
    | 1003 | 老猪   |
    +------+--------+

#### 2.3 在REPEAT循环中的使用 ####

/*
    创建一个存储过程proc_test2
    读取tbl_account表balance<20000的数据
    通过id删除tbl_account_bak中相应数据
    */
    drop procedure if exists proc_test2;
    delimiter $
    create procedure proc_test2()
    begin
    	-- 定义变量接收id
    	declare rs_id int;
    	-- 定义游标结束变量
    	declare cur_done bit default false;
    	-- 游标定义
    	declare cursor_rs cursor for select id from tbl_account where balance < 20000;
    	declare continue handler for not found set cur_done = true;
    	-- 打开游标
    	open cursor_rs;
    	-- 开始循环
    	repeat
            fetch cursor_rs into rs_id;
            delete from tbl_account_bak where id = rs_id;
            until cur_done
        end repeat;
        close cursor_rs;
    end
    $
    delimiter ;
    
    -- 调用测试
    call proc_test2();
    select * from tbl_account_bak;
    +------+--------+
    | id   | info   |
    +------+--------+
    | 1003 | 老猪   |
    +------+--------+

#### 2.4 在WHILE循环中的使用 ####

/*
    创建一个存储过程proc_test3
    清空tbl_account_bak中的数据
    读取tbl_account表balance<20000的数据
    将数据的id和aname插入到tbl_account_bak中
    注意：while循环和之前的循环有不相同之处
    */
    drop procedure if exists proc_test3;
    delimiter $
    create procedure proc_test3()
    begin
    	-- 定义变量接收id
    	declare rs_name varchar(10);
    	declare rs_id int;
    	-- 定义游标结束变量
    	declare cur_done bit default false;
    	-- 游标定义
    	declare cursor_rs cursor for select id, aname from tbl_account where balance < 20000;
    	declare continue handler for not found set cur_done = true;
    	-- 清空tbl_account_bak中的数据
    	delete from tbl_account_bak;
    	-- 打开游标
    	open cursor_rs;
    	-- 第一次读取数据
    	fetch cursor_rs into rs_id, rs_name;
    	-- 开始循环
    	while cur_done != true do
            insert into tbl_account_bak values (rs_id, rs_name);
            fetch cursor_rs into rs_id, rs_name;
        end while;
        close cursor_rs;
    end
    $
    delimiter ;
    
    -- 调用测试
    call proc_test3();
    select * from tbl_account_bak;
    +------+--------+
    | id   | info   |
    +------+--------+
    | 1001 | 老孙   |
    | 1002 | 老唐   |
    +------+--------+

### 结语 ###

游标除了在存储过程中使用的较多之外，可以在自定义函数、触发器、事件中使用游标。

如果你学过 Java 语言，Java语言中有个 ResultSet 用来接收 sql 语句查询返回的结果集，然后需要对这个结果集一行一行的处理，这个一行一行的处理就类似游标。如果你没看懂这块，就越过别看了，毕竟不是所有人都是搞 Java 的。

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xpbGk0MDM0Mg_size_16_color_FFFFFF_t_70_pic_center]: /images/20230209/e0be96e545e04971870c3b4287545e7a.png