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;
不使用游标,该方法利用 row_number 开窗函数给每个结果集编号,再利用循环即可实现最终结果。
总结
通过游标的知识和示例,可以发现游标其实并不复杂,非常好理解,就是依次遍历结果集,如果结果集中有特殊的序号,也是可以实现一样的效果,方法不嫌多,多去实践用起来。
参考资料:
本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。 如若内容造成侵权/违法违规/事实不符,请联系我的编程经验分享网邮箱:veading@qq.com进行投诉反馈,一经查实,立即删除!