SQL SERVER 中的自訂函數(二) 資料表值函式

資料表值函式

資料表值函式和純量值函式的不同是 資料表值函式是返回一個Table類型  Table類型相當與一張儲存在記憶體中的一張虛擬表.

資料表值函式的文法:

CREATE FUNCTION [ schema_name. ] function_name
( [ { @parameter_name [ AS ] [ type_schema_name. ] parameter_data_type
    [ = default ] }
    [ ,...n ]
  ]
)
RETURNS TABLE
    [ WITH <function_option> [ ,...n ] ]
    [ AS ]
    RETURN [ ( ] select_stmt [ ) ]
[ ; ]

 

現在來寫一個比較實用的資料表值函式..

寫一個切割字串的資料表值函式

Code
 1 --------------------------這個函數用來切割字串的-----------------
 2 --函數的參數  第一個是要切割的字串 第二個是要以什麼字串切割
 3 CREATE FUNCTION Split(@Text NVARCHAR(4000),@Sign NVARCHAR(4000))
 4 RETURNS  @tempTable TABLE(id INT IDENTITY(1,1) PRIMARY KEY,[VALUE] NVARCHAR(4000))
 5 AS
 6 BEGIN
 7     DECLARE @StartIndex INT                --開始尋找的位置
 8     DECLARE @FindIndex  INT                --找到的位置
 9     DECLARE @Content    VARCHAR(4000)    --找到的值
10     --初始化一些變數
11     SET @StartIndex = 1 --T-SQL中字串的尋找位置是從1開始的
12     SET @FindIndex=0
13     
14     --開始迴圈尋找字串逗號
15     WHILE(@StartIndex <= LEN(@Text))
16     BEGIN
17         --尋找字串函數 CHARINDEX  第一個參數是要找的字串
18         --                            第二個參數是在哪裡尋找這個字串
19         --                            第三個參數是開始尋找的位置
20         --傳回值是找到字串的位置
21         SELECT @FindIndex = CHARINDEX(@Sign,@Text,@StartIndex)
22         --判斷有沒找到 沒找到返回0
23         IF(@FindIndex =0 OR @FindIndex IS NULL)
24         BEGIN
25             --如果沒有找到者表示找完了
26             SET @FindIndex = LEN(@Text)+1
27         END
28         --截取字串函數 SUBSTRING  第一個參數是要截取的字串
29         --                            第二個參數是開始的位置
30         --                            第三個參數是截取的長度
31         --@FindIndex-@StartIndex 表示找的的位置-開始找的位置=要截取的長度
32         --LTRIM 和 RTRIM 是去除字串左邊和右邊的空格函數
33         SET @Content = LTRIM(RTRIM(SUBSTRING(@Text,@StartIndex,@FindIndex-@StartIndex)))
34         --初始化下次尋找的位置
35         SET @StartIndex = @FindIndex+1
36         --把找的的值插入到要返回的Table類型中
37         INSERT INTO @tempTable ([VALUE]) VALUES (@Content) 
38     END
39     RETURN
40 END

 

這個函數的作用就是類似.Net中的string類的Split方法

現在來測試這個函數

 這個函數返回的是Table類型   所以可以用下面的文法來調用

 

Code
SELECT * FROM dbo.Split('a,b,c,d,e,f,g',',')

得到的結果

 

這個函數還是比較實用的..

 如果還有什麼不明白可以 留言.....