20. Mysql 游标的定义和使用

2024-01-03 09:35:22

概念

游标(Cursor)是一种数据库对象,可以指向存储在数据库表中的数据行指针。用于在 sql 语句的执行过程中,通过对查询结果集进行逐行的操作和访问。它提供了一种逐行遍历结果集的方式,可以在查询结果集上进行灵活的操作和处理。

使用场景:

  • 需要逐行处理查询结果集,进行一些特定的操作或计算。
  • 需要在一个事务中多次访问相同的查询结果集。
  • 需要在一个过程中对查询结果集进行多次迭代。
  • 需要对查询结果集进行一些复杂的业务逻辑处理。

使用限制:

  • 只能在存储过程、函数和触发器中使用游标。
  • 游标只能存放 select 语句查询的结果集合,不能存放 show 语句查询的结果。

游标的基本语法

声明游标

使用 declare 语句来声明一个游标,并指定查询语句作为游标的结果集。

示例代码:

declare cursor_name cursor for select_statement;

打开游标

使用 open 语句来打开一个游标,打开游标即将查询结果集加载到游标中,为后面游标的逐条读取结果集中的记录做准备。

示例代码:

open cursor_name;

使用游标

使用 fetch 语句,可以从游标中获取一行数据,并将数据赋值给变量。

示例代码:

fetch cursor_name into variable1, variable2[, variable3, ...];

使用游标的作用是让 cursor_name 这个游标来读取当前行,并且将数据保存到 variable 这个变量中,游标指针依次指到下一行。如果游标读取的数据行有多个列名,则在 into 关键字后面赋值给多个变量名即可。

variable1 必须在声明游标之前就定义好。游标的查询结果集中的列数,必须跟 into 后面的变量数一致,否则,在存储过程执行的时候,会提示错误。

关闭游标

使用 close 语句来关闭一个游标,因为游标会占用系统资源 ,如果不及时关闭,游标会一直保持到存储过程结束,影响系统运行的效率。

示例代码:

close cursor_name;

精选示例

需求:统计数据库中每个表的实际数量。

方法一:创建存储过程,并且使用游标。

drop procedure if exists get_table_info;
create procedure get_table_info()
begin
    -- 定义变量
    declare finished bool default 0;
    declare db_tb_name varchar(255) default '';
    declare for_cnt int default 0;

    -- 声明游标
    declare cur cursor for
        select concat('`', table_schema, '`.`', table_name, '`') as db_tb_name
        from information_schema.tables
        group by table_schema, table_name;-- 将所有表查询出来

    declare continue handler for not found set finished = 1;-- 无数据更新finished变量

    -- 打开游标
    open cur;
    truncate table sql_test1.table_info;-- 清空表数据
    l1:
    loop
        fetch cur into db_tb_name;-- 使用游标
        if finished = 1 then
            leave l1;
        end if;
        set @sql = '';
        set @sql = concat('insert into sql_test1.table_info(db_tb_name,fact_cnt) \n\tselect \'', db_tb_name,'\',count(*) \n\tfrom ', db_tb_name);
        prepare stmt from @sql;-- 预编译准备好的拼接sql
        execute stmt;-- 执行
        deallocate prepare stmt;-- 释放
    end loop;
    -- 关闭游标
    close cur;
    select '录入完成。' as info;
end;

call get_table_info();-- 调用
select * from sql_test1.table_info;-- 查询结果

在这里插入图片描述

其中,当游标无数据触发 not found 异常的时候,将变量更新为 finished = 1 ,这样就可以控制循环的退出了。

方法二:创建存储过程,不使用游标。

drop procedure if exists get_table_info1;
create procedure get_table_info1()
begin
    declare i int default 1;
    -- 计算出需要遍历查询的表总数
    set @max_db_tb_cnt = (select count(distinct table_schema, table_name) as db_tb_cnt
                          from information_schema.tables);

    truncate table sql_test1.table_info;-- 清空表数据
		-- 根据表的总数量遍历
    while i <= @max_db_tb_cnt
        do
            set @db_tb_name = '';
            set @sql = '';
            select db_tb_name
            into @db_tb_name
            from (select db_tb_name
                       , row_number() over (order by db_tb_name) as row_number1
                  from (select concat(table_schema, '.', table_name) as db_tb_name
                        from information_schema.tables) as t1) as t2
            where row_number1 = i;-- 根据遍历的i变量,查询出对应的表名,并且更新@db_tb_name变量
            set @sql = concat('insert into sql_test1.table_info(db_tb_name,fact_cnt) \n\tselect \'', @db_tb_name,
                              '\',count(*) \n\tfrom ', @db_tb_name);
            prepare stmt from @sql;
            execute stmt;
            deallocate prepare stmt;
            set i = i + 1;-- 更新
        end while;
    select '录入完成。' as info;
end;

call get_table_info1();
select * from sql_test1.table_info;

不使用游标,该方法利用 row_number 开窗函数给每个结果集编号,再利用循环即可实现最终结果。

总结

通过游标的知识和示例,可以发现游标其实并不复杂,非常好理解,就是依次遍历结果集,如果结果集中有特殊的序号,也是可以实现一样的效果,方法不嫌多,多去实践用起来。

参考资料

文章来源:https://blog.csdn.net/weixin_50357986/article/details/135337984
本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。