MySQL游标

最新推荐文章于 2025-02-23 23:10:15 发布

Tybyqi

最新推荐文章于 2025-02-23 23:10:15 发布

阅读量584

点赞数

分类专栏：数据库

本文链接：https://blog.youkuaiyun.com/Tybyqi/article/details/86607737

版权

数据库专栏收录该内容

103 篇文章

订阅专栏

本文深入探讨了MySQL中游标的使用方法，包括声明、打开、使用和关闭游标的过程。并通过三种不同的循环结构（While、Repeat、Loop）演示了如何在存储过程和函数中利用游标遍历数据，同时介绍了如何将检索到的数据插入到新的表中。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

MySQL游标

While循环

-- MySQL游标只能用于存储过程和函数
-- 使用过程 声明、打开游标、使用游标、关闭游标
-- 如果未明确关闭游标，END时会自动关闭
-- https://dev.mysql.com/doc/refman/8.0/en/error-handling.html
drop PROCEDURE if exists process_user;
create procedure process_user()
begin
	-- Declare local variables
	declare done boolean default true;
	declare user_id int;
	declare user_info varchar(50);
	
	-- Declare the cursor
	declare cursor_user cursor for select id, user_name from sys_user;
	
	-- Declare continue handler
	declare continue handler for sqlstate '02000' set done=false;
	
	-- Create a table to store the results;
	create table if not exists process_user_result(user_id int, user_name varchar(50));
	
	-- Open the cursor
	open cursor_user;
	
	-- Repeat through all rows
	repeat
		fetch cursor_user into user_id, user_info;
		if done then 
			insert into process_user_result(user_id, user_name) 
			values(user_id, user_info);
		end if;
	until (not done) end repeat;
	
	-- Close the cursor
	close cursor_user;
end;

call process_user();

Repeat循环

drop PROCEDURE if exists process_user;
create procedure process_user()
begin
	-- Declare local variables
	declare done boolean default true;
	declare user_id int;
	declare user_info varchar(50);
	
	-- Declare the cursor
	declare cursor_user cursor for select id, user_name from sys_user;
	
	-- Declare continue handler
	declare continue handler for not found set done=false;
	
	-- Create a table to store the results;
	create table if not exists process_user_result(user_id int, user_name varchar(50));
	
	-- Open the cursor
	open cursor_user;
	
	-- Loop through all rows
	fetch cursor_user into user_id, user_info;
	while done do
		insert into process_user_result(user_id, user_name) 
		values(user_id, user_info); 
		fetch cursor_user into user_id, user_info;
	end while;
	
	-- Close the cursor
	close cursor_user;
end;

call process_user();

Loop循环

drop PROCEDURE if exists process_user;
create procedure process_user()
begin
	-- Declare local variables
	declare done boolean default true;
	declare user_id int;
	declare user_info varchar(50);
	
	-- Declare the cursor
	declare cursor_user cursor for select id, user_name from sys_user;
	
	-- Declare continue handler
	declare continue handler for not found set done=false;
	
	-- Create a table to store the results;
	create table if not exists process_user_result(user_id int, user_name varchar(50));
	
	-- Open the cursor
	open cursor_user;
	
	-- Loop through all rows
	read_loop:loop
		fetch cursor_user into user_id, user_info;
		if not done then
			leave read_loop;
		end if;
		insert into process_user_result(user_id, user_name) values(user_id, user_info);
	end loop;
	
	-- Close the cursor
	close cursor_user;
end;

call process_user();