ITPub博客

首页 > Linux操作系统 > Linux操作系统 > SQL Server 实现N级目录

SQL Server 实现N级目录

原创 Linux操作系统 作者:iSQlServer 时间:2009-04-17 13:37:28 0 删除 编辑

SQL实现N级目录,以前开发一网络E盘需求多级目录。对性能要求也比较高.所以就没有直接在代码里实现了.把一些不必要的处理都放在储存过程里.效率更高~~因为此项目已经完成很久了.所以保留的SQL储存过程可能不是很全面。嘿嘿。好咯。我废话少说。不全面的将补上去.........

----------------SQL表------------------

USE [QuPanStorData]
GO
/****** 对象:  Table [dbo].[Folder]    脚本日期: 12/06/2008 23:05:24 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Folder](
[Id] [bigint] IDENTITY(1,1) NOT NULL,
[Email] [varchar](200) COLLATE Chinese_PRC_CI_AS NULL,
[FolderName] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL,
[DirTime] [datetime] NULL,
[ColumnParentId] [bigint] NULL,
[ColumnChild] [int] NULL CONSTRAINT [DF_Folder_ColumnChild]  DEFAULT ((0)),
[ColumnPath] [varchar](900) COLLATE Chinese_PRC_CI_AS NULL,
[ColumnFileCount] [int] NULL,
CONSTRAINT [PK_Folder] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'文件夹ID' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'Folder', @level2type=N'COLUMN', @level2name=N'Id'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'用户Email映射' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'Folder', @level2type=N'COLUMN', @level2name=N'Email'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'文件夹名称' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'Folder', @level2type=N'COLUMN', @level2name=N'FolderName'

显示结果:


 附件: 您所在的用户组无法下载或查看附件

其中,ColumnParenId是记录父级ID,若值为0,则代表是根目录.ColumnChild是本目录下有多少子目录.ColumnPath,记录此目录的路径(感觉不是很有用)
ColumnFileCount记录当前目录下文件的总数.(避免了select count(*) from 表)优化思想


---------------------------------储存过程------------------------------

--插入数据
ALTER PROCEDURE [dbo].[insert_Dir]
      (
  @email varchar(200),        --用户
  @foldername varchar(50),    --文件夹名
  @ColumnParentId int        --文件夹父级ID,若是根目录则为0,
      )
AS 

Declare @NewColumnId int
Begin Tran start

--判断是否是顶级栏目,设置ColumnPath
Declare @ColumnPath varchar(1000)
Declare @ColumnId int 
select @ColumnId = ColumnParentId from Folder where Id=@ColumnParentId
--插入信息
Insert Into [Folder](Email,FolderName,DirTime,ColumnParentId,ColumnPath,ColumnFileCount)
Values(@email,@foldername,getdate(),@ColumnParentId,'','0')
declare @N int; 
set @N = @@IDENTITY
If @ColumnId=0 --根目录
      Set @ColumnPath='|'+Ltrim(Str(@N))+'|'
Else
Begin
      Select @ColumnPath=ColumnPath from Folder where Id=@ColumnParentId
      If @ColumnPath is null
Begin
Rollback tran start
return 0
      End
      Else
Set @ColumnPath=@ColumnPath+Ltrim(Str(@N))+'|'
End

update Folder set ColumnPath = @ColumnPath where Id = @N
--根据父栏目的id更新父栏目的ColumnChild
If @@RowCount=1
      Begin
Set @NewColumnId=@@identity
If @ColumnParentId >0
        Begin
        Update Folder Set ColumnChild=ColumnChild+1 where Id=@ColumnParentId
                              If @@Rowcount =1
                                  Begin
  Commit Tran Start
  Return 1 
                                  End
                            Else
      Begin
  Rollback Tran Start
  Commit Tran Start
  Return 0
        End
                      End
              Else
          Begin
  Commit Tran Start
  Return 1 
                    End                   
      End
Else
    Begin
Rollback Tran Start
Commit Tran Start
Return 0
      End
---删除表

ALTER PROCEDURE [dbo].[Proc_FolderColumnDel]
(
  @Columnid int,          --要删除目录行的ID
  @ColumnParentId int      --要删除目录行的父级Id
)
AS 
BEGIN
Declare @ColumnPath  varchar(1000)
--查看输入的@Columnparentid和@Columnid的父Columnparentid是否匹配
Declare @ParentId int
Select @ParentId=ColumnParentId,@ColumnPath=ColumnPath from Folder where Id=@Columnid
if  @ParentId <> @ColumnParentId
return ;
else
--删除指定Id的数据 
Delete from Folder where Id=@Columnid
--更新父栏目的子栏目个数
update Folder Set ColumnChild=ColumnChild-1 where Id=@ColumnParentId
--删除指定@Columnid的子栏目
Delete from Folder where ColumnPath like [email=]'%'+@ColumnPath+'%'[/email]
END

-------根据ID获取父级所有名称以及Id
ALTER PROCEDURE [dbo].[Proc_FolderTopName]
(
  @id int
)
AS
--根据ID获取父级所有名称以及Id
SET NOCOUNT ON   
Declare @FolderName varchar(50);    --目录名 
Declare @FolderId int              --目录ID
Declare @ColumnParentId int        --父级id
Declare @loction int   
Declare @start int;    --开始
Declare @length int;    --返回个数
Declare @ColumnPath varchar(900)    --文件夹路
--根据ID查询ColumnPath
select @FolderName =FolderName,@ColumnParentId =ColumnParentId,@ColumnPath=ColumnPath  from Folder where Id=@id
set @loction = charindex('|',@ColumnPath)
set @length = 0
while @loction <> 0
begin
set @start = @loction + 1
set @loction = charindex('|',@ColumnPath,@start)
set @length = @length +1    --查询结果需要-1
end
declare @i int                  --级别循环变量
set @i=(@length-1);
create table #tmptable        --创建临时表,存放各个级别层id
(
iid int IDENTITY(1,1),    --临时自动编号,用于排序
FolderId int,      --文件ID
itemname varchar(30)
)
insert into #tmptable(FolderId,itemname)values(@id,@FolderName)
 
while(@i>0) --循环读取上级ID
  begin
  select @FolderName=FolderName,@ColumnParentId =ColumnParentId ,@FolderId = Id from Folder where id =@ColumnParentId
  insert into #tmptable(FolderId,itemname)values(@FolderId,@FolderName)
  set @i=@i -1
  end
--按级别从高到低列出上级名称
select FolderId,itemname from #tmptable order by iid desc
drop table #tmptable 

---------修改文件夹与文件
ALTER PROCEDURE [dbo].[Proc_UpdateFileFolder]
(
@email varchar(200),  --用户
@id  int,    --文件List表的Id
@folderid int, --移动到的文件夹id
@currId int    --当前文件夹 
)
AS
BEGIN tran moveinfo
if @folderid ='0'
begin
update List set Folder=@folderid where Id=@id and Email=@email  --修改list表的Folder
if @@error <>0 goto errinfo
update Folder set ColumnFileCount = ColumnFileCount -1 where Id=@currId  --移开的-1
if @@error <>0 goto errinfo
    update Folder set ColumnFileCount = ColumnFileCount + 1 where Email= @email and ColumnParentId='-1' --修改根目录用户文件的个数
if @@error <>0 goto errinfo
end
else
begin
update List set Folder=@folderid where Id=@id
if @@error <>0 goto errinfo
    if @currId ='0'
  update Folder set ColumnFileCount = ColumnFileCount -1 where Email= @email and ColumnParentId='-1'
else
  update Folder set ColumnFileCount = ColumnFileCount -1 where Email= @email and Id=@currId
if @@error <>0 goto errinfo
update Folder set  ColumnFileCount = ColumnFileCount + 1 where Id =@folderid  --移动到的+1
if @@error <>0 goto errinfo
end
commit tran moveinfo
return 0
errinfo:
Rollback TRAN moveinfo
return -1

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/16436858/viewspace-590784/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2008-10-17

  • 博文量
    1319
  • 访问量
    2101385