mysql 遍历 父子_[mysql存储过程]爷父子三表循环

请问下MYSQL怎么实现三表循环展现数据如下写的哪里不对p>

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,remark from t_gpa where gpa_id=v_gpa_id’ at line 21

3相关信息如下:

表结构

t_gpa gpa_id[int] remark[char(10)]

11

t_dad dad_id[int] gpa_id[int] remark[char(10)]

21 11

22 11

23 11

t_son son_id[int] dad_id[int] remark[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_remark char(200);

declare cur_gpa cursor for select gpa_id,remark 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_remark;

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,remark 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_remark;

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,remark 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_remark;

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_remark char(200);

declare cur_gpa cursor for select gpa_id,remark 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_remark;

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

2010年7月12日 11:48

文章知识点与官方知识档案匹配，可进一步学习相关知识MySQL入门技能树数据库组成表32989 人正在系统学习中 相关资源：一款简单实用的flash制作软件,狐狸少爷汉化版-Flash文档类资源…