请问下MYSQL怎么实现三表循环展现数据? 我如下写的哪里不对?
1单层查询没问题,可以编译.
2引入双层后就不可编译了一直报错.
我用的是EMS,一款mysql的客户端软件编译的.
在CMD命令模式下也不能正常编译.
==
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 'declare cur_dad cursor for select dad_id,re from t_gpa where gpa_id=v_gpa_id' at line 21
3相关信息如下:
表结构
t_gpa gpa_id[int] re[char(10)]
11 t_dad dad_id[int] gpa_id[int] re[char(10)]
21 11
22 11
23 11 t_son son_id[int] dad_id[int] re[char(10)]
31 21
32 21
33 22
34 23
35 23
36 23
存储过程,多层循环
drop procedure if exists p_level3;
create procedure p_level3(in v_in_gpa_id int)
begin
declare v_gpa_id,v_dad_id,v_son_id int;
declare v_count_gpa,v_count_dad,v_count_son int;
declare v_sqlerr_gpa,v_sqlerr_dad,v_sqlerr_son int default 0;
declare v_re char(200);
declare cur_gpa cursor for select gpa_id,re from t_gpa where gpa_id=v_in_gpa_id;
declare continue handler for not found set v_sqlerr_gpa = 1403;
open cur_gpa;
#
set v_count_gpa:= 0;
set v_count_dad:= 0;
set v_count_son:= 0; Loop_gpa:LOOP
fetch cur_gpa into v_gpa_id, v_re;
if v_sqlerr_gpa = 1403 then
leave Loop_gpa;
end if;
set v_count_gpa:= v_count_gpa + 1;
declare cur_dad cursor for select dad_id,re from t_gpa where gpa_id=v_gpa_id;
declare continue handler for not found set v_sqlerr_dad = 1403;
open cur_dad;
Loop_dad:LOOP
fetch cur_gpa into v_dad_id, v_re;
if v_sqlerr_dad = 1403 then
leave Loop_dad;
end if;
set v_count_dad:= v_count_dad + 1;
declare cur_son cursor for select son_id,re from t_son where dad_id=v_dad_id;
declare continue handler for not found set v_sqlerr_son = 1403;
open cur_son;
Loop_son:LOOP
fetch cur_son into v_son_id, v_re;
if v_sqlerr_son = 1403 then
leave Loop_son;
end if;
set v_count_son:= v_count_son + 1;
end LOOP;
close cur_son;
end LOOP;
close cur_dad;
end LOOP;
close cur_gpa;
#
select v_count_gpa,v_count_dad,v_count_son;
end;
4单层循环没有问题:可编译可运行
drop procedure if exists p_level3;
create procedure p_level3(in v_in_gpa_id int)
begin
declare v_gpa_id,v_dad_id,v_son_id int;
declare v_count_gpa,v_count_dad,v_count_son int;
declare v_sqlerr_gpa,v_sqlerr_dad,v_sqlerr_son int default 0;
declare v_re char(200);
declare cur_gpa cursor for select gpa_id,re from t_gpa where gpa_id=v_in_gpa_id;
declare continue handler for not found set v_sqlerr_gpa = 1403;
open cur_gpa;
#
set v_count_gpa:= 0;
set v_count_dad:= 0;
set v_count_son:= 0; Loop_gpa:LOOP
fetch cur_gpa into v_gpa_id, v_re;
if v_sqlerr_gpa = 1403 then
leave Loop_gpa;
end if;
set v_count_gpa:= v_count_gpa + 1; end LOOP;
close cur_gpa;
#
select v_count_gpa,v_count_dad,v_count_son;
end;
call p_level3(11);
1单层查询没问题,可以编译.
2引入双层后就不可编译了一直报错.
我用的是EMS,一款mysql的客户端软件编译的.
在CMD命令模式下也不能正常编译.
==
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 'declare cur_dad cursor for select dad_id,re from t_gpa where gpa_id=v_gpa_id' at line 21
3相关信息如下:
表结构
t_gpa gpa_id[int] re[char(10)]
11 t_dad dad_id[int] gpa_id[int] re[char(10)]
21 11
22 11
23 11 t_son son_id[int] dad_id[int] re[char(10)]
31 21
32 21
33 22
34 23
35 23
36 23
存储过程,多层循环
drop procedure if exists p_level3;
create procedure p_level3(in v_in_gpa_id int)
begin
declare v_gpa_id,v_dad_id,v_son_id int;
declare v_count_gpa,v_count_dad,v_count_son int;
declare v_sqlerr_gpa,v_sqlerr_dad,v_sqlerr_son int default 0;
declare v_re char(200);
declare cur_gpa cursor for select gpa_id,re from t_gpa where gpa_id=v_in_gpa_id;
declare continue handler for not found set v_sqlerr_gpa = 1403;
open cur_gpa;
#
set v_count_gpa:= 0;
set v_count_dad:= 0;
set v_count_son:= 0; Loop_gpa:LOOP
fetch cur_gpa into v_gpa_id, v_re;
if v_sqlerr_gpa = 1403 then
leave Loop_gpa;
end if;
set v_count_gpa:= v_count_gpa + 1;
declare cur_dad cursor for select dad_id,re from t_gpa where gpa_id=v_gpa_id;
declare continue handler for not found set v_sqlerr_dad = 1403;
open cur_dad;
Loop_dad:LOOP
fetch cur_gpa into v_dad_id, v_re;
if v_sqlerr_dad = 1403 then
leave Loop_dad;
end if;
set v_count_dad:= v_count_dad + 1;
declare cur_son cursor for select son_id,re from t_son where dad_id=v_dad_id;
declare continue handler for not found set v_sqlerr_son = 1403;
open cur_son;
Loop_son:LOOP
fetch cur_son into v_son_id, v_re;
if v_sqlerr_son = 1403 then
leave Loop_son;
end if;
set v_count_son:= v_count_son + 1;
end LOOP;
close cur_son;
end LOOP;
close cur_dad;
end LOOP;
close cur_gpa;
#
select v_count_gpa,v_count_dad,v_count_son;
end;
4单层循环没有问题:可编译可运行
drop procedure if exists p_level3;
create procedure p_level3(in v_in_gpa_id int)
begin
declare v_gpa_id,v_dad_id,v_son_id int;
declare v_count_gpa,v_count_dad,v_count_son int;
declare v_sqlerr_gpa,v_sqlerr_dad,v_sqlerr_son int default 0;
declare v_re char(200);
declare cur_gpa cursor for select gpa_id,re from t_gpa where gpa_id=v_in_gpa_id;
declare continue handler for not found set v_sqlerr_gpa = 1403;
open cur_gpa;
#
set v_count_gpa:= 0;
set v_count_dad:= 0;
set v_count_son:= 0; Loop_gpa:LOOP
fetch cur_gpa into v_gpa_id, v_re;
if v_sqlerr_gpa = 1403 then
leave Loop_gpa;
end if;
set v_count_gpa:= v_count_gpa + 1; end LOOP;
close cur_gpa;
#
select v_count_gpa,v_count_dad,v_count_son;
end;
call p_level3(11);
MySQL官方文档 http://dev.mysql.com/doc/refman/5.1/zh/index.html
'截至2010-07-11 23:35:16 用户结帖率50.00% 总发帖:2 正常结帖:0
当您的问题得到解答后请及时结贴.
http://topic.csdn.net/u/20090501/15/7548d251-aec2-4975-a9bf-ca09a5551ba5.html
http://topic.csdn.net/u/20100428/09/BC9E0908-F250-42A6-8765-B50A82FE186A.html
http://topic.csdn.net/u/20100626/09/f35a4763-4b59-49c3-8061-d48fdbc29561.html8、如何给分和结贴?
http://community.csdn.net/Help/HelpCenter.htm#结帖