sqlserver另类非递归的无限级分类(存储过程版)

下面是我统计的几种方案: 第一种方案(递归式): 简单的表结构为:
CategoryID int(4), CategoryName nvarchar(50), ParentID int(4), Depth
int(4) 这样根据ParentID一级级的运用递归找他的上级目录。
还有可以为了方便添加CategoryLeft,CategoryRight保存他的上级目录或下级目录
第二种方案:
设置一个varchar类型的CategoryPath字段来保存目录的完整路径,将父目录id用符号分隔开来。比如:1,5,8,10
第三种方案: 每级分类递增两位数字的方法 示例:
一级分类:01,02,03,04… 二级分类:0101,0102,0103,0104…
三级分类:010101,010102,010103…
分析一下,其实第三种方案并不能真正意义上做无限级的分类,而第二种方案,虽然比较容易得到各上级及下级的分类信息。但,添加和转移分类的时候操作将很麻烦。
而且,也完全违反了数据库设计范式。
其实我也一直在用第二种方案的。为了查找方便,我有时都在新闻表里加上CategoryID和CategoryPath
而我今天要说的算法其实是第二种方案的改进版,一般做分类都是使用一个表格来保存分类信息。
而我这里,要新建两个表格,一个表格是保存分类信息表,一个保存分类关系表。
表结构如下: 表1:tomi_Category CategoryID int(4), ‘编号 CategoryName
nvarchar(50), ‘分类名称 Depth int(4), ‘深度 表2:tomi_CategoryBind
CategoryID int(4), BindCategoryID int(4), Depth int(4),
添加,编辑,删除操作有点麻烦。。我是直接用存储过程的。。不知道大家能看得懂不。。哈哈。
1、添加分类(Category_Add) 复制代码
代码如下: CREATE proc [dbo].[Category_Add] @CategoryName
nvarchar(50), @BindCategoryID int, @CategoryID int output as declare
@Success bit set @Success=1 –生成不重复的CategoryID declare @i bit set
@i=0 while @i=0 begin set @CategoryID=LEFT(10000000 + CONVERT(bigint,
ABS(CHECKSUM(NEWID()))), 8) if(not exists(select CategoryID from
tomi_Category where CategoryID=@CategoryID)) set @i=1 end –得到depth
declare @depth int set @depth=0 select @depth=depth from tomi_Category
where CategoryID=@BindCategoryID set @depth=@depth+1 –插入 BEGIN TRAN
insert into tomi_Category(categoryID,CategoryName,Depth)
values(@CategoryID,@CategoryName,@Depth) if(@@ERROR0) BEGIN ROLLBACK
TRAN set @Success=0 END insert into
tomi_CategoryBind(CategoryID,BindCategoryID,Depth)
values(@CategoryID,@CategoryID,@Depth) if(@@ERROR0) BEGIN ROLLBACK TRAN
set @Success=0 END insert into
tomi_CategoryBind(CategoryID,BindCategoryID,Depth) select
@CategoryID,BindCategoryID,Depth from tomi_CategoryBind where
CategoryID=@BindCategoryID if(@@ERROR0) BEGIN ROLLBACK TRAN set
@Success=0 END COMMIT TRAN print @CategoryID
每个分类在tomi_CategoryBind有完整的目录结构。。一个分类在tomi_CategoryBind的记录数等于他在tomi_Category的depth值。
图片: 2、编辑修改分类(Category_Edit) 复制代码 代码如下: CREATE proc
[dbo].[Category_Edit] @CategoryID int, @CategoryName nvarchar(50),
@BindCategoryID int as –更新 BEGIN TRAN update tomi_Category set
CategoryName=@CategoryName where CategoryID=@CategoryID IF @@ERROR0
BEGIN ROLLBACK TRAN return 0 END COMMIT TRAN –检测是否更改了上级目录
declare @is bit set @is=0 if(exists(select CategoryID from
tomi_CategoryBind where CategoryID=@CategoryID and
BindCategoryID=@BindCategoryID and Depth=(select Depth-1 from
tomi_Category where CategoryID=@CategoryID))) set @is=1 print @is
–更改了深度 if(@is=0) BEGIN –得到上级目录的depth declare @depth int
set @depth=0 select @depth=depth from tomi_Category where
CategoryID=@BindCategoryID set @depth=@depth+1 –print @depth
–更改子目录 declare @i int declare @sCategoryID int declare
@sBindCategoryID int declare @tCategoryIDList Table ( CategoryID int,
FlagID tinyint ) insert @tCategoryIDList select c.CategoryID,0 from
tomi_Category c left join tomi_CategoryBind b on
c.CategoryID=b.CategoryID where b.BindCategoryID=@CategoryID order by
c.Depth set @i=1 set @sBindCategoryID=@BindCategoryID declare @errs int
set @errs=0 BEGIN TRAN while(@i=1) BEGIN select @sCategoryID=0 select
Top 1 @sCategoryID=CategoryID from @tCategoryIDList where FlagID=0 set
@i=@@RowCount –print @sCategoryID if @sCategoryID0 BEGIN –删除,更新
delete from tomi_CategoryBind where CategoryID=@sCategoryID set
@errs=@errs+@@error update tomi_www.463.com,Category set depth=@depth where
CategoryID=@sCategoryID set @errs=@errs+@@error –插入 insert into
tomi_CategoryBind(CategoryID,BindCategoryID,Depth)
values(@sCategoryID,@sCategoryID,@Depth) set @errs=@errs+@@error insert
into tomi_CategoryBind(CategoryID,BindCategoryID,Depth) select
@sCategoryID,BindCategoryID,Depth from tomi_CategoryBind where
CategoryID=@sBindCategoryID set @errs=@errs+@@error set
@sBindCategoryID=@sCategoryID set @Depth=@Depth+1 –print @sCategoryID
–print @sBindCategoryID –print @Depth –print ‘–‘ END update
@tCategoryIDList set FlagID=1 where CategoryID=@sCategoryID END
if(@errs0) BEGIN ROLLBACK TRAN return 0 END else COMMIT TRAN END
3、删除分类(Category_Del) 会直接删除子分类 复制代码 代码如下: create proc Category_Del
@CategoryID int as BEGIN TRAN delete from tomi_Category where
CategoryID in (select CategoryID from tomi_CategoryBind where
CategoryID=@CategoryID or BindCategoryID=@CategoryID) if(@@ERROR0) BEGIN
ROLLBACK TRAN return 0 END delete from tomi_CategoryBind where
CategoryID in (select CategoryID from tomi_CategoryBind where
CategoryID=@CategoryID or BindCategoryID=@CategoryID) if(@@ERROR0) BEGIN
ROLLBACK TRAN return 0 END COMMIT TRAN
4、分类列表,显示分类(Category_List) 复制代码 代码如下: CREATE proc Category_List
as select c.* from tomi_Category c left join tomi_CategoryBind b on
c.CategoryID=b.CategoryID where b.Depth=1 order by
b.BindCategoryID,c.Depth GO exec Category_List
可以直接让分类等级查询出来。而且显示全部的话,一次查询即可,只需判断depth就行。
图片: 5、上级子分类列表 (Category_upTree) 复制代码 代码如下: Create Proc Category_UpTree
@CategoryID int as select c.* from tomi_Category c left join
tomi_CategoryBind b on c.CategoryID=b.BindCategoryID where
b.CategoryID=@CategoryID order by c.Depth GO exec Category_UpTree
63919523 这样就可以得到一个分类的完整子目录集,方便吧,只要一条sql.
图片: 6、下级子分类列表(Category_downTree) 复制代码 代码如下: Create Proc
Category_DownTree @CategoryID int as select c.* from tomi_Category c
left join tomi_CategoryBind b on c.CategoryID=b.CategoryID where
b.BindCategoryID=@CategoryID order by c.Depth GO exec Category_DownTree
21779652
这样可以得到一个分类完整下级目录。比如得到某个分类和其分类的子分类下的所有产品用这个就好。。方便,一条sql.
图片: 以上是初稿,只是随意的测试了几次。。。有错误的,还请大家指出。。
呵呵。转载请注明链接,博客园首发,多谢。 作者:TomiWong 时间:2010.07.18

MSSQL2000和MSSQL2005以上版本的异常处理语法是不相同的。

SQL Server 2005以上版本支持结构化异常处理,而MSSQL2000是不支持的。

1)先看MSSQL 2000的异常处理语法:

create proc sp_mssql2000
www.463.com 1(@TitleName nvarchar(128))
www.463.com 2as
www.463.com 3
www.463.com 4declare     @err int,
www.463.com 5
www.463.com 6begin transaction
www.463.com 7
www.463.com 8insert into…
www.463.com 9
www.463.com 10select @err = @@error
www.463.com 11if @err <> 0
www.463.com 12    GOTO ERROR_HANDLER
www.463.com 13
www.463.com 14update…set…
www.463.com 15
www.463.com 16if @err <> 0
www.463.com 17    GOTO ERROR_HANDLER
www.463.com 18
www.463.com 19GOTO EXIT_Proc
www.463.com 20
www.463.com 21ERROR_HANDLER:
www.463.com 22ROLLBACK TRANSACTION
www.463.com 23
www.463.com 24— Log the error 
www.463.com 25insert
Log (tableName, UserName, errorNumber, errorSeverity, errorState)
www.463.com 26values (@tableName, suser_sname(), @err, 0, 0)
www.463.com 27
www.463.com 28EXIT_Proc:
www.463.com 29commit tran

2)MSSQL2005的异步处理语法:

create proc sp_mssql2005
(@TitleName nvarchar(128))
as
declare @err int

BEGIN TRY

发表评论

电子邮件地址不会被公开。 必填项已用*标注