delimiter //
CREATE Procedure UpClass()
begin
declare YX_ID int;
declare F_ParentID varchar(100);/*父级分类*/
declare F_Path varchar(100);/*父级分类路径*/
declare F_Child int;/*父级分类的Child*/
declare F_PrevID int;/*父级分类的同深度分类上个ID*/
declare F_NextID int;/*父级分类的同深度分类下个ID*/
declare F_OrderID int; /*父级分类的排序ID*/
declare TempPath varchar(100); /*临时路径*/
declare TempNum int; /*临时int型*/
declare F_RootID int;
select F_ParentID=YX_ParentID,F_Path=YX_ParentPath,F_Child=YX_Child,F_RootID=YX_RootID,F_PrevID=YX_PrevID,F_NextID=YX_NextID
,F_OrderID=YX_OrderID from YX_productclass where YX_ID=YX_ID;
/**找到父级分类下的所有分类数目**/
declare F_NextClassPath varchar(100);
set F_NextClassPath=F_Path+','+Convert(varchar(100),YX_ID);
declare F_ChildNum int;
select F_ChildNum=(select count(*) from YX_ProductClass where YX_ParentPath like F_NextClassPath+'%' and YX_RootID=F_RootID);
set F_ChildNum=F_ChildNum+1;
/******************************/
/*找到父级分类的同深度分类的信息*/
declare Prv_PrevID int;
declare Prv_RootID int;
if F_PrevID>0 then
/*该分类的相关信息*/
declare NextClassPath varchar(100);/*下属分类的路径*/
select NextClassPath=YX_ParentPath,Prv_RootID=YX_RootID from YX_ProductClass where YX_ID=F_PrevID;
set NextClassPath=NextClassPath+','+Convert(varchar(100),F_PrevID);
declare Prv_ChildNum int; /*上个分类的数目*/
select Prv_ChildNum=(select count(*) from YX_ProductClass where YX_ParentPath like NextClassPath+'%' and YX_RootID=Prv_RootID);
/*求出该分类的总数*/
set Prv_ChildNum=Prv_ChildNum+1;
(select Prv_PrevID=YX_PrevID from YX_ProductClass where YX_ID=F_PrevID);
/*修改该分类的位置和上下相关ID*/
update YX_ProductClass set YX_PrevID=YX_ID,YX_NextID=F_NextID,
YX_OrderID=YX_OrderID+F_ChildNum where YX_ID=F_PrevID;
/*修改该分类下的排序ID*/
update YX_ProductClass set YX_OrderID=YX_OrderID+F_ChildNum
where YX_ParentPath like NextClassPath+'%' and YX_RootID=Prv_RootID;
/*************************/
/*****************/
/**修改该分类上级分类的nextID*/
update YX_productclass set YX_NextID=YX_ID where YX_ID=Prv_PrevID;
/*修改源分类ID*/
set TempPath=F_Path+','+Convert(varchar(100),YX_ID);
update YX_ProductClass set YX_PrevID=Prv_PrevID,YX_NextID=F_PrevID,
YX_OrderID=YX_OrderID-Prv_ChildNum where YX_ID=YX_ID;
/*修改该分类下的排序ID*/
update YX_ProductClass set YX_OrderID=YX_OrderID-Prv_ChildNum
where YX_ParentPath like TempPath+'%' and YX_RootID=F_RootID;
/*************/
if F_NextID >0 then
/*修改下个相临类别的PrevID*/
Update YX_ProductClass set YX_PrevID=F_PrevID where YX_ID=F_NextID;
end if;
end if;
end;//在
/**找到父级分类下的所有分类数目**/
declare F_NextClassPath varchar(100);
set F_NextClassPath=F_Path+','+Convert(varchar(100),YX_ID);这块报错了,Mysql如何向MSsql一样使用Convert
CREATE Procedure UpClass()
begin
declare YX_ID int;
declare F_ParentID varchar(100);/*父级分类*/
declare F_Path varchar(100);/*父级分类路径*/
declare F_Child int;/*父级分类的Child*/
declare F_PrevID int;/*父级分类的同深度分类上个ID*/
declare F_NextID int;/*父级分类的同深度分类下个ID*/
declare F_OrderID int; /*父级分类的排序ID*/
declare TempPath varchar(100); /*临时路径*/
declare TempNum int; /*临时int型*/
declare F_RootID int;
select F_ParentID=YX_ParentID,F_Path=YX_ParentPath,F_Child=YX_Child,F_RootID=YX_RootID,F_PrevID=YX_PrevID,F_NextID=YX_NextID
,F_OrderID=YX_OrderID from YX_productclass where YX_ID=YX_ID;
/**找到父级分类下的所有分类数目**/
declare F_NextClassPath varchar(100);
set F_NextClassPath=F_Path+','+Convert(varchar(100),YX_ID);
declare F_ChildNum int;
select F_ChildNum=(select count(*) from YX_ProductClass where YX_ParentPath like F_NextClassPath+'%' and YX_RootID=F_RootID);
set F_ChildNum=F_ChildNum+1;
/******************************/
/*找到父级分类的同深度分类的信息*/
declare Prv_PrevID int;
declare Prv_RootID int;
if F_PrevID>0 then
/*该分类的相关信息*/
declare NextClassPath varchar(100);/*下属分类的路径*/
select NextClassPath=YX_ParentPath,Prv_RootID=YX_RootID from YX_ProductClass where YX_ID=F_PrevID;
set NextClassPath=NextClassPath+','+Convert(varchar(100),F_PrevID);
declare Prv_ChildNum int; /*上个分类的数目*/
select Prv_ChildNum=(select count(*) from YX_ProductClass where YX_ParentPath like NextClassPath+'%' and YX_RootID=Prv_RootID);
/*求出该分类的总数*/
set Prv_ChildNum=Prv_ChildNum+1;
(select Prv_PrevID=YX_PrevID from YX_ProductClass where YX_ID=F_PrevID);
/*修改该分类的位置和上下相关ID*/
update YX_ProductClass set YX_PrevID=YX_ID,YX_NextID=F_NextID,
YX_OrderID=YX_OrderID+F_ChildNum where YX_ID=F_PrevID;
/*修改该分类下的排序ID*/
update YX_ProductClass set YX_OrderID=YX_OrderID+F_ChildNum
where YX_ParentPath like NextClassPath+'%' and YX_RootID=Prv_RootID;
/*************************/
/*****************/
/**修改该分类上级分类的nextID*/
update YX_productclass set YX_NextID=YX_ID where YX_ID=Prv_PrevID;
/*修改源分类ID*/
set TempPath=F_Path+','+Convert(varchar(100),YX_ID);
update YX_ProductClass set YX_PrevID=Prv_PrevID,YX_NextID=F_PrevID,
YX_OrderID=YX_OrderID-Prv_ChildNum where YX_ID=YX_ID;
/*修改该分类下的排序ID*/
update YX_ProductClass set YX_OrderID=YX_OrderID-Prv_ChildNum
where YX_ParentPath like TempPath+'%' and YX_RootID=F_RootID;
/*************/
if F_NextID >0 then
/*修改下个相临类别的PrevID*/
Update YX_ProductClass set YX_PrevID=F_PrevID where YX_ID=F_NextID;
end if;
end if;
end;//在
/**找到父级分类下的所有分类数目**/
declare F_NextClassPath varchar(100);
set F_NextClassPath=F_Path+','+Convert(varchar(100),YX_ID);这块报错了,Mysql如何向MSsql一样使用Convert
1. MYSQL不使用 + ,字符串相加是CONCAT()函数 set F_NextClassPath=concat(F_Path,',',)
2. MYSQL中不需要用CONVERT,直接加就行了。set F_NextClassPath= concat (F_Path,',',YX_ID);
set F_NextClassPath=concat(F_Path,',',YX_ID)
,F_OrderID=YX_OrderID from YX_productclass where YX_ID=YX_ID;这个也不对。MYSQL中是 select col into var from ...
或者 select var:=col from ..
select YX_ParentID,YX_ParentPath,YX_Child,YX_RootID,YX_PrevID,YX_NextID
,YX_OrderID into F_ParentID,F_Path,F_Child,F_RootID ,F_PrevID,F_NextID,F_OrderID from YX_NewsClass where YX_ID=YX_ID;这样子也不对啊