《数据库树形数据处理方案》由会员分享，可在线阅读，更多相关《数据库树形数据处理方案（14页珍藏版）》请在金锄头文库上搜索。
1、/*-树形数据处理方案 树形数据的排序,新增,修改,复制,删除,数据完整性检查,汇总统计 -邹建 2003.9-*/ /*-数据测试环境 表名tb,如果修改表名,则相应修改所有数据处理中涉及到的表名tb id为编号(标识字段+主键) pid为上级编号 name为名称,后面可以自行增加其他字段. 凡是未特殊标注的地方,对自行增加的字段不影响处理结果 -*/ create table tb(id int identity(1,1) not null constraint PK_tb primary key clustered ,pid int,name varchar(20) insert int
2、o tb select 0,中国 union all select 0,美国 union all select 0,加拿大 union all select 1,北京 union all select 1,上海 union all select 1,江苏 union all select 6,苏州 union all select 7,常熟 union all select 6,南京 union all select 6,无锡 union all select 2,纽约 union all select 2,旧金山 go /*-数据处理-*/ /*- 一个重要的函数,很多处理的地方都会用到 -
3、*/ -自定义函数-获取编码累计 create function f_getmergid(id int) returns varchar(8000) as begin declare re varchar(8000),pid int -为了数字排序正常,需要统一编码宽度 declare idlen int,idheader varchar(20) select idlen=max(len(id) ,idheader=space(idlen) from tb -得到编码累计 set re=right(idheader+cast(id as varchar),idlen) select pid=p
4、id from tb where id=id while rowcount0 select re=right(idheader+cast(pid as varchar),idlen)+,+re ,pid=pid from tb where id=pid return(re) end go /*-数据显示排序-*/ -分级显示-横向,先一级,后二级. select * from tb order by pid -分级显示-纵向 select * from tb order by dbo.f_getmergid(id) go /*-数据统计-*/ -分级统计,每个地区下的明细地区数 select
5、*, 明细地区数=(select count(*) from tb where dbo.f_getmergid(id) like dbo.f_getmergid(a.id)+,%) from tb a order by dbo.f_getmergid(id) go /*-数据新增,修改 数据新增,修改(包括修改所属的类别)没有什么技巧 ,只需要检查所属的上级是否存在就行了.这个可以简单的用下面的语句来解决: if exists(select 1 from tb where id=id) print 存在 else print 不存在 -*/ /*-数据删除-*/ create proc p_d
6、elete id int, -要删除的id deletechild bit=0 -是否删除子 1.删除子,0.如果id有子,则删除失败. as if deletechild=1 delete from tb where dbo.f_getmergid(id) like dbo.f_getmergid(id)+% else if exists(select 1 from tb where pid=id) goto lbErr else delete from tb where id=id return lbErr: RAISERROR (该结点下有子结点,不能删除, 16, 1) go -调用示
7、例 -删除美国的数据 -exec p_delete 2 -不包含子,因为有美国下有子,所以删除会出错 exec p_delete 2,1 -包含子,将删除美国及所有数据 go /*-数据完整性检查-*/ -自定义函数-检测某个编码出发,是否被循环引用 create function f_chkid(id int) returns bit -循环,返回1,否则返回0 as begin declare re bit,pid int set re=0 -检测 select pid=pid from tb where id=id while rowcount0 begin if pid=id begi
8、n set re=1 goto lbErr end select pid=pid from tb where id=pid end lbErr: return(re) end go -显示表中的那些数据不符合规范 select * from tb a where not exists(select 1 from tb where id=a.pid) or dbo.f_chkid(id)=1 go /*-数据复制 如果表中包含自定义字段,需要修改存储过程 存在嵌套不超过32层的问题. -*/ -创建复制的存储过程-复制指定结点下的子结点到另一个结点下 create proc p_copy s_i
9、d int, -复制该项下的所有子项 d_id int, -复制到此项下 new_id int -新增加项的开始编号 as declare nid int,oid int,name varchar(20) select id,name into #temp from tb where pid=s_id and id0 while exists(select 1 from #temp) begin select oid=id,name=name from #temp insert into tb values(oid,name) set nid=identity exec p_copy oid,nid,nid delete from #temp where id=oid end go -测试 exec p_copystr 5,6 -显示处理结果 select
