SQL自定义函数 字符串截取 递归查询 以及一个小例子

背景

最近遇到一个需求，抽出与本文最相关的，概括大致如下：

1. 有三个表，描述某个File Share的文件拓扑：
   
   • User表，记录file owner name(domain\user_name)，唯一主键UserId
   • Location表，记录folder的父子关系，唯一主键LocationId
   • File表，记录文件metadata
2. 有些folder以某些user_name为文件夹名，称此类文件夹为user folder
3. 若某user folder下有子文件夹，子文件夹也属于此user folder
4. 找出所有user folder下的所有file

解决逻辑：

1. 找出所有的user_name（Table_UFName）
2. 根据user name列表，找出对应的user folder（Table_UFLocation）
3. 递归找出所有的user folder（包括所有的子文件夹，Table_AllUFLocation）
4. 找到所有user folder下的所有file（Table_AllUFFile）

找个自己拿手的语言，把数据从表中取出来，然后照上面的逻辑来做，这样比较简单，但为了学习和练手，就拿Transact-SQL来写实现。上面四步中，每步的输出都是一个table。

---

START

1. Table_UFName

对于此表，首先，需要写个String Split方法，能够截取出来“\”后面的user_name；然后对于User表中每个user，都执行该Split方法，结果写入此表。

自定义SQL function

我们需要创建一个新的自定义SQL Function。SQL function细节很多，此处不展开，有需求看前链接。此function的输入是一个String，一个Char，调用的时候指定index（取截取出来的String list中的第几个）

-- CREATE SPLITSTRING FUNCTIONIF EXISTS (    SELECT *    FROM SYS.OBJECTS    WHERE OBJECT_ID = OBJECT_ID(N'[dbo].[SplitString]')    AND TYPE IN ( N'FN', N'IF', N'TF', N'FS', N'FT' ))DROP FUNCTION [dbo].[SplitString]GOCREATE FUNCTION [dbo].[SplitString](    @str NVARCHAR(MAX),     @separator CHAR(1))RETURNS TABLEASRETURN (WITH tokens(p, a, b) AS (    SELECT         CAST(1 AS BIGINT),         CAST(1 AS BIGINT),         CHARINDEX(@separator, @str)    UNION ALL    SELECT        p + 1,         b + 1,         CHARINDEX(@separator, @str, b + 1)    FROM tokens    WHERE b > 0)SELECT    p-1 ItemIndex,    SUBSTRING(        @str,         a,         CASE WHEN b > 0 THEN b-a ELSE LEN(@str) END)     AS ItemFROM tokens);GO

使用方法如下

SELECT Item FROM [dbo].[SplitString]('XIAOF.com\Shawn','\') WHERE ItemIndex = 1

返回的是

Shawn

T-SQL while循环

对每个user name执行SplitString方法，结果输出到一个表中，该表就是Table_UFName（代码中就是临时表 @Table_UFName）。（感觉T-SQL的循环应该有更简单的写法，这里暂时不纠结）

DECLARE @userid INTDECLARE @maxuserid INTDECLARE @username NVARCHAR(260)DECLARE @userfoldername NVARCHAR(260)DECLARE @Table_UFName TABLE (UserFolderName NVARCHAR(260))SELECT @userid = MIN(UserId) FROM [dbo].[User]SELECT @maxuserid = MAX(UserId) FROM [dbo].[User]-- FIND USER PROFILE FOLDER NAMES FROM SPLITTING USER NAMESWHILE(@userid <= @maxuserid)BEGIN    SELECT @username = [UserName] FROM [dbo].[User] WHERE [UserId] = @userid    SELECT @userfoldername = Item FROM [dbo].[SplitString](@username,'\') WHERE ItemIndex = 1    INSERT INTO @Table_UFName ([UserFolderName]) VALUES    (@userfoldername)    SET @userid = @userid + 1END

---

2. Table_UFLocation

此表比较简单，就是根据Table_UFName表，取出所有的root user folder（root user folder指那些文件夹名直接就在Table_UFName中的那些folder，不包含其中的子文件夹），写入Table_UFLocation

-- FIND USER FOLDER LOCATIONSIF EXISTS (SELECT OBJECT_ID FROM SYS.TABLES WHERE name = 'Table_UFLocation' AND TYPE = 'U')BEGINDROP TABLE [dbo].[Table_UFLocation]END--BEGINSELECT IDENTITY(INT, 1, 1) AS [LocId], [LocationId], [ParentId], [FolderName], [Level] INTO [dbo].[Table_UFLocation]FROM [dbo].[Location]WHERE [FolderName] IN (SELECT UserFolderName FROM @Table_UFName)--ENDGO

NOTE
因为上面保存Table_UFName的时候，用的是临时表，所以，这里正好也能体现BEGIN-END和GO的区别（就是上面代码块中被注释了的BEGIN-END，可以自行comment GO，uncomment BEGIN-END，看看有什么效果）

---

3. Table_AllUFLocation

这里需要用到递归，对每个根user folder，找到其所有sub folder

T-SQL的递归查询

这里就需要提到CTE（common table expression）。

A common table expression (CTE) can be thought of as a temporary result set that is defined within the execution scope of a single SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW statement. A CTE is similar to a derived table in that it is not stored as an object and lasts only for the duration of the query. Unlike a derived table, a CTE can be self-referencing and can be referenced multiple times in the same query.

最重要的，就是这个self-referencing，确保CTE能够较简单地支持使用递归查询。下面就是使用CTE实现递归查询的T-SQL（line 25~37）。创建Table_UFAllLocation，然后依次对每个root user folder执行递归查询，找到所有的子user folder，并插入Table_UFAllLocation

DECLARE @maxlocid INTDECLARE @locid INTDECLARE @id INTSELECT @maxlocid = MAX(LocId) FROM [dbo].[Table_UFLocation]SELECT @locid = MIN(LocId) FROM [dbo].[Table_UFLocation]IF EXISTS (SELECT OBJECT_ID FROM SYS.TABLES WHERE name = 'Table_AllUFLocation' AND TYPE = 'U')BEGINDROP TABLE [dbo].[Table_AllUFLocation]ENDCREATE TABLE [dbo].[Table_AllUFLocation](    [LocationId] INT NOT NULL,    [ParentId] INT NOT NULL,    [FolderName] NVARCHAR(260) NOT NULL,    [Level] INT NOT NULL,    [UF_Tag] NVARCHAR(260) NOT NULL);-- FIND ALL FOLDERS IN USER ROOT FOLDERSWHILE(@locid <= @maxlocid)BEGIN    SELECT @id = LocationId FROM Table_UFLocation WHERE LocId = @locid    ;WITH allloc AS    (        SELECT loc.[LocationId], loc.[ParentId], loc.[FolderName], loc.[Level], ufloc.[FolderName] AS [UF_Tag]        FROM [dbo].[Location] AS loc        INNER JOIN [dbo].[Table_UFLocation] AS ufloc ON loc.[LocationId] = ufloc.[LocationId]        WHERE loc.[LocationId] = @id        UNION ALL        SELECT loc.[LocationId], loc.[ParentId], loc.[FolderName], loc.[Level], allloc.[UF_Tag]        FROM [dbo].[Location] AS loc        JOIN allloc ON loc.[ParentId] = allloc.[LocationId]    )    INSERT INTO [dbo].[Table_AllUFLocation]    SELECT * FROM allloc    SET @locid = @locid + 1ENDGO

---

4. Table_AllUFFile

这步就是简单的query了，找到所有user folder下的所有file，写入Table_AllUFFile

IF EXISTS (SELECT OBJECT_ID FROM SYS.TABLES WHERE name = 'Table_AllUFFile' AND TYPE = 'U')BEGINDROP TABLE [dbo].[Table_AllUFFile]ENDSELECT fi.[FileId], fi.[FileName], fi.[Location], upfallloc.[UF_Tag] INTO [dbo].[Table_AllUFFile]FROM [dbo].[File] AS fiINNER JOIN [dbo].[Table_AllUFLocation] AS upfallloc ON fi.[Location] = upfallloc.[LocationId]WHERE fi.[Location] IN(    SELECT DISTINCT [LocationId] FROM [dbo].[Table_AllUFLocation])

FINISH

---

关于BEGIN-END与GO的区别，这里可以补充说一下，通过以下code能看得很清楚：

• BEGIN-END代表的是代码块的分隔符，意义在于分隔，就像是其他编程语言里面的花括号一样。必须配对，有BEGIN就要有对应的END，例如下面的code是错误的，会报Incorrect syntax near '@string_test'.（就像没有写反花括号一样）

DECLARE @string_test nvarchar(100)SET @string_test = 'A'BEGIN    PRINT @string_test

• GO不仅仅代表代码块的形式上的分隔，更有含义上的分隔，就像是function的概念。若干个GO之间的代码块与代码块之间，在code执行的过程中，是没有关系的，这样的code是正确的

DECLARE @string_test1 nvarchar(100)SET @string_test1 = 'A'BEGIN    PRINT @string_test1ENDGODECLARE @string_test2 nvarchar(100)SET @string_test2 = 'B'BEGIN    PRINT @string_test2ENDGO

而这样的code就有误，报Must declare the scalar variable "@string_test".

DECLARE @string_test nvarchar(100)SET @string_test = 'A'BEGIN    PRINT @string_testENDGOSET @string_test = 'B'BEGIN    PRINT @string_testENDGO

因为@string_test的作用域是第一个GO之前，就像是一个function中定义的变量，出了这个function就无效了

— 在这里下载以上完整测试代码 —

总结

1. T-SQL自定义函数。
2. T-SQL实现字符串截取的函数
3. BEGIN-END与GO的区别
4. 使用T-SQL CTE实现递归查询
5. 实现博客零的突破，finally……