mysql while,loop,repeat循环，符合条件跳出循环,游标

最新推荐文章于 2025-05-26 17:49:36 发布

转载最新推荐文章于 2025-05-26 17:49:36 发布 · 7.7k 阅读

本文展示了SQL中使用WHILE循环、LOOP循环、REPEAT直到和CURSOR游标的用法，通过创建多个存储过程实例来实现不同类型的循环控制和数据检索。

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

DROP PROCEDURE IF EXISTS mywhile;
CREATE PROCEDURE mywhile ()
BEGIN
DECLARE i INT default 0;
outer_label: BEGIN #设置一个标记并设置开始
while i<10 DO
SET i = i + 1;
IF i > 2 THEN
select '跳出循环' column1;
LEAVE outer_label; #满足条件，终止循环，跳转到end outer_label标记
ELSE
SELECT i as column1;
END IF;

end while;
END outer_label; #设置标记并结束
END
call mywhile;

DROP PROCEDURE IF EXISTS myloop;

CREATE PROCEDURE myloop ()
BEGIN
DECLARE i INT default 0;

loop_label:LOOP#循环开始
SET i = i + 1;
IF i > 3 THEN
select '跳出循环' column1;
LEAVE loop_label; #终止循环
ELSE
SELECT NOW() as column1;
END IF;
END LOOP;
ENd

call myloop;

DROP PROCEDURE IF EXISTS myrepeat;
CREATE PROCEDURE myrepeat ()
BEGIN
DECLARE i INT default 0;
REPEAT
SET i = i + 1;
until i>2
end REPEAT;
END

call myrepeat;

DROP PROCEDURE IF EXISTS myCURSOR;
CREATE PROCEDURE myCURSOR ()
BEGIN
DECLARE user_name VARCHAR(32);

/* 声明游标 */
DECLARE my_cursor CURSOR FOR select nickname from users;

/* 打开游标 */
OPEN my_cursor;
sale_loop: LOOP
FETCH my_cursor INTO user_name;
select user_name;
END LOOP;
-- 关闭游标
close my_cursor;
end