小弟做了一个mysql的存储过程,用来拼出一,二,三级菜单名称.此存储过程能正常保存.可以正常拼出一级菜单,但无法拼出二,三级菜单,请问错误在哪里?
另此存储过程原来是在mssql上的,运行正常,后来改写到mysql上就出现此问题了.
以下为此存储过程的代码,大家帮忙解决,谢谢.BEGIN
DECLARE andid,andname,andpid,ands,ands1,ands2,ands3,ands4,ands5,ands6 varchar(50);
declare i_error int default 0;
DECLARE cname CURSOR FOR (SELECT id,name,pid FROM tb_menuitem);
declare continue handler for sqlstate '02000' set i_error=1;
open cname;
while i_error=0 do
FETCH NEXT FROM cname into andid,andname,andpid;
set ands=andname;
if andpid<>'-1' then
select ands1=id,ands2=name,ands3=pid from tb_menuitem where id=andpid;
set ands=concat(ands2,'>>',ands);
if ands3<>'-1' then
select ands4=id,ands5=name,ands6=pid from tb_menuitem where id=ands3;
set ands=concat(ands5,'>>',ands);
set andpid=ands3;
end if;
end if;
update tb_menuitem set caption=ands where id=andid;
FETCH NEXT FROM cname into andid,andname,andpid;
end while;
CLOSE cname;
END
另此存储过程原来是在mssql上的,运行正常,后来改写到mysql上就出现此问题了.
以下为此存储过程的代码,大家帮忙解决,谢谢.BEGIN
DECLARE andid,andname,andpid,ands,ands1,ands2,ands3,ands4,ands5,ands6 varchar(50);
declare i_error int default 0;
DECLARE cname CURSOR FOR (SELECT id,name,pid FROM tb_menuitem);
declare continue handler for sqlstate '02000' set i_error=1;
open cname;
while i_error=0 do
FETCH NEXT FROM cname into andid,andname,andpid;
set ands=andname;
if andpid<>'-1' then
select ands1=id,ands2=name,ands3=pid from tb_menuitem where id=andpid;
set ands=concat(ands2,'>>',ands);
if ands3<>'-1' then
select ands4=id,ands5=name,ands6=pid from tb_menuitem where id=ands3;
set ands=concat(ands5,'>>',ands);
set andpid=ands3;
end if;
end if;
update tb_menuitem set caption=ands where id=andid;
FETCH NEXT FROM cname into andid,andname,andpid;
end while;
CLOSE cname;
END
`id` varchar(50) NOT NULL default '',
`pid` varchar(30) default NULL,
`PowerID` varchar(50) default NULL,
`caption` varchar(30) default NULL,
`name` varchar(200) default NULL,
`description` varchar(50) default NULL,
`location` tinytext,
`target` varchar(10) default NULL,
`onclick` varchar(100) default NULL,
`onmouseover` varchar(100) default NULL,
`onmouseout` varchar(100) default NULL,
`image` varchar(50) default NULL,
`altImage` varchar(30) default NULL,
`tooltip` varchar(100) default NULL,
`roles` varchar(100) default NULL,
`page` tinytext,
`width` varchar(5) default NULL,
`height` varchar(5) default NULL,
`forward` varchar(50) default NULL,
`action` varchar(50) default NULL,
`morder` int(11) default NULL,
`lvl` int(11) default NULL,
`active` varchar(50) default NULL,
`type` varchar(255) default NULL,
PRIMARY KEY (`id`)
)以上的这张就是原表结构
谢谢大家帮忙,这问题到现在还困扰着我.
set ands=andname;
if andpid <> '-1' then
select ands1=id,ands2=name,ands3=pid from tb_menuitem where id=andpid;
set ands=concat(ands2,'> > ',ands);
if ands3 <> '-1' then
select ands4=id,ands5=name,ands6=pid from tb_menuitem where id=ands3;
这段错了吧参考以下语法:FETCH cursor_name INTO var_name [, var_name] ...
SELECT col_name[,...] INTO var_name[,...] table_expr
我对mysql的语句只会最基本的.而以上的代码原来在mssql中都调试通过了.