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();