表值函數(shù)
表值函數(shù)提供強大的結(jié)果集生成能力。它可以在查詢內(nèi)部表或視圖允許的任何地方使用。表值函數(shù)在使用上比返回一個結(jié)果集的存儲過程更靈活，因為函數(shù)的結(jié)果集可以聯(lián)接到查詢中的其他表。
SQL Server中有兩種表值函數(shù)。內(nèi)聯(lián)表值函數(shù)在概念上與帶參數(shù)的視圖類似。多語句表值函數(shù)允許多條語句在表變量中創(chuàng)建結(jié)果集來返回。
1. 內(nèi)聯(lián)表值函數(shù)
創(chuàng)建內(nèi)聯(lián)表值函數(shù)很簡單。內(nèi)聯(lián)表值函數(shù)的內(nèi)容是一條帶參數(shù)的SELECT語句。返回數(shù)據(jù)類型永遠是表，不過返回表的結(jié)構(gòu)由SELECT語句的結(jié)構(gòu)來定義。下面是內(nèi)聯(lián)表值函數(shù)的一個例子，檢索給定CustomerID的商品銷售總量。
- USE AdventureWorks2008;
- GO
- CREATE FUNCTION Sales.ufnSalesByCustomer (@CustomerID int)
- RETURNS TABLE
- AS
- RETURN
- (
- SELECT P.ProductID, P.Name, SUM(SD.LineTotal) AS Total
- FROM Production.Product AS P
- JOIN Sales.SalesOrderDetail AS SD
- ON SD.ProductID = P.ProductID
- JOIN Sales.SalesOrderHeader AS SH
- ON SH.SalesOrderID = SD.SalesOrderID
- WHERE SH.CustomerID = @CustomerID
- GROUP BY P.ProductID, P.Name
- );
- GO
注意，函數(shù)體由一條RETURN語句組成。使用這個函數(shù)的一個例子如下所示：
- SELECT * FROM Sales.ufnSalesByCustomer(30052);
內(nèi)聯(lián)表值函數(shù)功能強大，在要求參數(shù)化查詢的情況下值得考慮。它們在結(jié)果集如何使用上提供更多的靈活性。
2. 多語句表值函數(shù)
多語句表值函數(shù)允許多條語句來創(chuàng)建表的內(nèi)容。多語句表值函數(shù)可以用來替換使用多個步驟來構(gòu)建結(jié)果集的存儲過程。
多語句表值函數(shù)允許開發(fā)人員使用多個步驟動態(tài)地填充表，這一點與存儲過程類似，不過它們可以在SELECT語句中像表那樣被引用。
使用多語句表值函數(shù)時，表的結(jié)構(gòu)必須在函數(shù)頭定義。要為表使用一個變量名，并且所有修改數(shù)據(jù)的操作只能引用表變量。
下面的例子是一個函數(shù)，類似上一節(jié)中創(chuàng)建的ufnSalesByCustomer。首先創(chuàng)建表變量，然后使用剛才創(chuàng)建的標量函數(shù)來更新表變量，讓它包含總的存貨清單。創(chuàng)建函數(shù)的語句如下所示：
- USE AdventureWorks2008;
- GO
- CREATE FUNCTION Sales.ufnSalesByCustomerMS (@CustomerID int)
- RETURNS @table TABLE
- ( ProductID int PRIMARY KEY NOT NULL,
- ProductName nvarchar(50) NOT NULL,
- TotalSales numeric(38,6) NOT NULL,
- TotalInventory int NOT NULL )
- AS
- BEGIN
- INSERT INTO @table
- SELECT P.ProductID, P.Name, SUM(SD.LineTotal) AS Total, 0
- FROM Production.Product AS P
- JOIN Sales.SalesOrderDetail SD ON SD.ProductID = P.ProductID
- JOIN Sales.SalesOrderHeader SH ON SH.SalesOrderID = SD.SalesOrderID
- WHERE SH.CustomerID = @CustomerID
- GROUP BY P.ProductID, P.Name;
-
- UPDATE @table
- SET TotalInventory = dbo.ufnGetTotalInventoryStock(ProductID);
-
- RETURN;
- END;
執(zhí)行這個函數(shù)與執(zhí)行前面的內(nèi)聯(lián)函數(shù)一樣：
- SELECT * FROM Sales. ufnSalesByCustomerMS (30052);
-------------------------------------------------------------------------------------
表值函數(shù)和標量值函數(shù)的不同是 表值函數(shù)是返回一個Table類型 Table類型相當與一張存儲在內(nèi)存中的一張?zhí)摂M表.
表值函數(shù)的語法:
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 [ ) ]
[ ; ]
現(xiàn)在來寫一個比較實用的表值函數(shù)..
寫一個切割字符串的表值函數(shù)
view plaincopy to clipboardprint?
--------------------------這個函數(shù)用來切割字符串的-----------------
--函數(shù)的參數(shù) 第一個是要切割的字符串 第二個是要以什么字符串切割
CREATE FUNCTION Split(@Text NVARCHAR(4000),@Sign NVARCHAR(4000))
RETURNS @tempTable TABLE(id INT IDENTITY(1,1) PRIMARY KEY,[VALUE] NVARCHAR(4000))
AS
BEGIN
DECLARE @StartIndex INT --開始查找的位置
DECLARE @FindIndex INT --找到的位置
DECLARE @Content VARCHAR(4000) --找到的值
--初始化一些變量
SET @StartIndex = 1 --T-SQL中字符串的查找位置是從1開始的
SET @FindIndex=0
--開始循環(huán)查找字符串逗號
WHILE(@StartIndex <= LEN(@Text))
BEGIN
--查找字符串函數(shù) CHARINDEX 第一個參數(shù)是要找的字符串
-- 第二個參數(shù)是在哪里查找這個字符串
-- 第三個參數(shù)是開始查找的位置
--返回值是找到字符串的位置
SELECT @FindIndex = CHARINDEX(@Sign,@Text,@StartIndex)
--判斷有沒找到 沒找到返回0
IF(@FindIndex =0 OR @FindIndex IS NULL)
BEGIN
--如果沒有找到者表示找完了
SET @FindIndex = LEN(@Text)+1
END
--截取字符串函數(shù) SUBSTRING 第一個參數(shù)是要截取的字符串
-- 第二個參數(shù)是開始的位置
-- 第三個參數(shù)是截取的長度
--@FindIndex-@StartIndex 表示找的的位置-開始找的位置=要截取的長度
--LTRIM 和 RTRIM 是去除字符串左邊和右邊的空格函數(shù)
SET @Content = LTRIM(RTRIM(SUBSTRING(@Text,@StartIndex,@FindIndex-@StartIndex)))
--初始化下次查找的位置
SET @StartIndex = @FindIndex+1
--把找的的值插入到要返回的Table類型中
INSERT INTO @tempTable ([VALUE]) VALUES (@Content)
END
RETURN
END
--------------------------這個函數(shù)用來切割字符串的-----------------
--函數(shù)的參數(shù) 第一個是要切割的字符串 第二個是要以什么字符串切割
CREATE FUNCTION Split(@Text NVARCHAR(4000),@Sign NVARCHAR(4000))
RETURNS @tempTable TABLE(id INT IDENTITY(1,1) PRIMARY KEY,[VALUE] NVARCHAR(4000))
AS
BEGIN
DECLARE @StartIndex INT --開始查找的位置
DECLARE @FindIndex INT --找到的位置
DECLARE @Content VARCHAR(4000) --找到的值
--初始化一些變量
SET @StartIndex = 1 --T-SQL中字符串的查找位置是從1開始的
SET @FindIndex=0
--開始循環(huán)查找字符串逗號
WHILE(@StartIndex <= LEN(@Text))
BEGIN
--查找字符串函數(shù) CHARINDEX 第一個參數(shù)是要找的字符串
-- 第二個參數(shù)是在哪里查找這個字符串
-- 第三個參數(shù)是開始查找的位置
--返回值是找到字符串的位置
SELECT @FindIndex = CHARINDEX(@Sign,@Text,@StartIndex)
--判斷有沒找到 沒找到返回0
IF(@FindIndex =0 OR @FindIndex IS NULL)
BEGIN
--如果沒有找到者表示找完了
SET @FindIndex = LEN(@Text)+1
END
--截取字符串函數(shù) SUBSTRING 第一個參數(shù)是要截取的字符串
-- 第二個參數(shù)是開始的位置
-- 第三個參數(shù)是截取的長度
--@FindIndex-@StartIndex 表示找的的位置-開始找的位置=要截取的長度
--LTRIM 和 RTRIM 是去除字符串左邊和右邊的空格函數(shù)
SET @Content = LTRIM(RTRIM(SUBSTRING(@Text,@StartIndex,@FindIndex-@StartIndex)))
--初始化下次查找的位置
SET @StartIndex = @FindIndex+1
--把找的的值插入到要返回的Table類型中
INSERT INTO @tempTable ([VALUE]) VALUES (@Content)
END
RETURN
END
這個函數(shù)的作用就是類似.Net中的string類的Split方法
現(xiàn)在來測試這個函數(shù)
這個函數(shù)返回的是Table類型 所以可以用下面的語法來調(diào)用
view plaincopy to clipboardprint?
SELECT * FROM dbo.Split('a,b,c,d,e,f,g',',')
SELECT * FROM dbo.Split('a,b,c,d,e,f,g',',')
得到的結(jié)果
