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 不支持这种写法?