mysql数据游标嵌套写法问题

最新推荐文章于 2025-12-19 16:00:26 发布

最新推荐文章于 2025-12-19 16:00:26 发布 · 262 阅读

文章标签：

#Mysql #数据库

本文提供了一个MySQL存储过程的示例代码，该过程使用了嵌套游标来处理数据。具体而言，外层游标遍历用户表，内层游标针对每个用户获取参数并插入到指定表中。但在SQLYog Enterprise中执行时出现了语法错误。

如下代码来自 http://www.iteye.com/topic/1026084

1.drop procedure if exists `sp_nested_cursor`;
2.create procedure `sp_nested_cursor`()
3.begin
4.    declare v_uid bigint(22);
5.    declare v_code varchar(100);
6.    declare v_value varchar(100);
7.    declare _done TINYINT(1) default 0;
8.    declare cur_user cursor for select id from `tb_user`;
9.    declare continue handler for not found set _done = 1;
10.
11.    open cur_user;
12.    loop_xxx:loop
13.        fetch cur_user into v_uid;
14.        if _done=1 then
15.            leave loop_xxx;
16.        end if;
17.        begin
18.            declare _inner tinyint(1) default 0;
19.            declare cur_param cursor for select code, value
20.                                         from `tb_user_param`
21.                                         where user_id=v_uid;
22.            declare continue handler for not found set _inner = 1;
23.            open cur_param;
24.            loop_yyy:loop
25.                fetch cur_param into v_code, v_value;
26.                if _inner=1 then
27.                    leave loop_yyy;
28.                end if;
29.                insert into tb_key_value values (v_uid, v_code, v_value);
30.            end loop;
31.            commit;
32.        end;
33.    end loop;
34.end;

在sqlyog enterprise工具中执行

Error Code : 1064
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'loop_yyy:loop
fetch cur_param into v_code, v_value' at line 1
(0 ms taken)

mysql5.0 不支持这种写法？