错误出现在这段代码里面,这是一个存储过程的片段,这段注释掉就不会错,但我还要这功能呢,求大神解答。IF ClassID=-1 THEN
begin
DECLARE X int;
DECLARE done int;
DECLARE t_cursor cursor for SELECT ID From TopWinCMS_Class where ParentID=0 and IsShowInIndex=1 and
LinkUrl =N'' and `ModelID` = ModelID order by `RootID` limit 1;
declare continue handler FOR NOT FOUND SET done = 1;
SET @SQL='';
SET done = 0;
open t_cursor;
while done = 0 do
begin
fetch t_cursor into X;
SET @SQL=concat('select ID,ModelID,ClassID,ClassName,ContentTitle,Hits,'''' as Content,ColorStyle,FontStyle,UpdateTime,TemplateFileName from VW_',TableName,' where deleted=0 and Pass=1 and ClassID in (',Get_ClassIDs(X),') limit ',TOP);
PREPARE stmt1 FROM @SQL;
EXECUTE stmt1;
DEALLOCATE PREPARE stmt1;
end;
end while;
close t_cursor;
end;
END IF;
Get_ClassIDs(X)
代码如下delimiter $$
drop function if exists Get_ClassIDs;
CREATE FUNCTION Get_ClassIDs(iid int)
RETURNS VARCHAR(8000)
BEGIN
DECLARE IDs VARCHAR(255);
DECLARE PPt VARCHAR(255);
Select concat(`Path`,'%') into PPt FROM TopWinCMS_Class where `ID`=iid;
set @IDs='';
select concat(@IDs,`ID`,',') into @IDs from TopWinCMS_Class where Path Like PPt;
IF LENGTH(@IDs)>0 THEN
set @IDs=left(@IDs,lenth(@IDs)-1);
ELSE
set @IDs='0';
end if;
return @IDs;
end$$
begin
DECLARE X int;
DECLARE done int;
DECLARE t_cursor cursor for SELECT ID From TopWinCMS_Class where ParentID=0 and IsShowInIndex=1 and
LinkUrl =N'' and `ModelID` = ModelID order by `RootID` limit 1;
declare continue handler FOR NOT FOUND SET done = 1;
SET @SQL='';
SET done = 0;
open t_cursor;
while done = 0 do
begin
fetch t_cursor into X;
SET @SQL=concat('select ID,ModelID,ClassID,ClassName,ContentTitle,Hits,'''' as Content,ColorStyle,FontStyle,UpdateTime,TemplateFileName from VW_',TableName,' where deleted=0 and Pass=1 and ClassID in (',Get_ClassIDs(X),') limit ',TOP);
PREPARE stmt1 FROM @SQL;
EXECUTE stmt1;
DEALLOCATE PREPARE stmt1;
end;
end while;
close t_cursor;
end;
END IF;
Get_ClassIDs(X)
代码如下delimiter $$
drop function if exists Get_ClassIDs;
CREATE FUNCTION Get_ClassIDs(iid int)
RETURNS VARCHAR(8000)
BEGIN
DECLARE IDs VARCHAR(255);
DECLARE PPt VARCHAR(255);
Select concat(`Path`,'%') into PPt FROM TopWinCMS_Class where `ID`=iid;
set @IDs='';
select concat(@IDs,`ID`,',') into @IDs from TopWinCMS_Class where Path Like PPt;
IF LENGTH(@IDs)>0 THEN
set @IDs=left(@IDs,lenth(@IDs)-1);
ELSE
set @IDs='0';
end if;
return @IDs;
end$$
select concat(@IDs,`ID`,',') into @IDs from TopWinCMS_Class where Path Like PPt;检查这两句的返回,估计是多条记录了。
Select concat(`Path`,'%') into PPt FROM TopWinCMS_Class where `ID`=iid;
select concat(@IDs,`ID`,',') into @IDs from TopWinCMS_Class where Path Like PPt;
SET @SQL=concat('select ID,ModelID,ClassID,ClassName,ContentTitle,Hits, '''' as
Content,ColorStyle,FontStyle,UpdateTime,TemplateFileName from VW_',TableName,' where deleted=0 and Pass=1 and
ClassID in (',Get_ClassIDs(ClassID),') order by UpdateTime desc limit ',TOP);
PREPARE stmt2 FROM @SQL;
EXECUTE stmt2;
DEALLOCATE PREPARE stmt2;
END IF;