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 FUNCTION
IF 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]
GO

CREATE FUNCTION [dbo].[SplitString]
(
    @str NVARCHAR(MAX), 
    @separator CHAR(1)
)
RETURNS TABLE
AS
RETURN (
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 Item
FROM 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 INT
DECLARE @maxuserid INT
DECLARE @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 NAMES
WHILE(@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 + 1
END

---

2. Table_UFLocation

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

-- FIND USER FOLDER LOCATIONS
IF EXISTS (SELECT OBJECT_ID FROM SYS.TABLES WHERE name = 'Table_UFLocation' AND TYPE = 'U')
BEGIN
DROP TABLE [dbo].[Table_UFLocation]
END
--BEGIN
SELECT 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)
--END
GO

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 INT
DECLARE @locid INT
DECLARE @id INT

SELECT @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')
BEGIN
DROP TABLE [dbo].[Table_AllUFLocation]
END
CREATE 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 FOLDERS
WHILE(@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 + 1
END
GO

---

4. Table_AllUFFile

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

IF EXISTS (SELECT OBJECT_ID FROM SYS.TABLES WHERE name = 'Table_AllUFFile' AND TYPE = 'U')
BEGIN
DROP TABLE [dbo].[Table_AllUFFile]
END
SELECT fi.[FileId], fi.[FileName], fi.[Location], upfallloc.[UF_Tag] INTO [dbo].[Table_AllUFFile]
FROM [dbo].[File] AS fi
INNER 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_test1
END
GO

DECLARE @string_test2 nvarchar(100)
SET @string_test2 = 'B'
BEGIN
    PRINT @string_test2
END
GO

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

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

SET @string_test = 'B'
BEGIN
    PRINT @string_test
END
GO

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

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

总结

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