20. Mysql 游标的定义和使用

文章目录

- 概念
- 游标的基本语法
- - 声明游标
  - 打开游标
  - 使用游标
  - 关闭游标
  - 精选示例
- 总结

概念

游标（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;