日本免费高清视频-国产福利视频导航-黄色在线播放国产-天天操天天操天天操天天操|www.shdianci.com

學(xué)無(wú)先后，達(dá)者為師

網(wǎng)站首頁(yè) 編程語(yǔ)言 正文

SQL?Server創(chuàng)建用戶(hù)定義函數(shù)_MsSql

作者：springsnow ? 更新時(shí)間： 2022-07-15 編程語(yǔ)言

一、UDF的定義

和存儲(chǔ)過(guò)程很相似，用戶(hù)自定義函數(shù)也是一組有序的T-SQL語(yǔ)句，UDF被預(yù)先優(yōu)化和編譯并且可以作為一個(gè)單元來(lái)進(jìn)行調(diào)用。

UDF和存儲(chǔ)過(guò)程的主要區(qū)別在于返回結(jié)果的方式：

  • 使用UDF時(shí)可傳入?yún)?shù)，但不可傳出參數(shù)。輸出參數(shù)的概念被更為健壯的返回值取代了。
  • 和系統(tǒng)函數(shù)一樣，可以返回標(biāo)量值，這個(gè)值的好處是它并不像在存儲(chǔ)過(guò)程中那樣只限于整形數(shù)據(jù)類(lèi)型，而是可以返回大多數(shù)SQL Server數(shù)據(jù)類(lèi)型。

UDF有以下兩種類(lèi)型:

  • 返回標(biāo)量值的UDF。
  • 返回表的UDF。

創(chuàng)建語(yǔ)法：

CREATE FUNCTION [<schema name>.]<function name>
(
[ <@parameter name> [AS] [<schema name>.]<data type> [= <default value> [READONLY]] [,...n] ]
)
RETURNS { <scalar type> | TABLE [(<table definition>)] }
[ WITH [ENCRYPTION] | [SCHEMABINDING] | [RETURNS NULL ON NULL INPUT | CALLED ON NULL INPUT ] |
[EXECUTE AS {CALLER|SELF|OWNER|<'user name'>}]
[AS] { EXTERNAL NAME <externam method> |
BEGIN
[<function statements>]
{RETURN <type as defined in RETURNS clause | RETURN (<SELECT statement>)}
END}[;]

二、標(biāo)量值函數(shù)：

這種類(lèi)型的UDF和大多數(shù)SQL Server內(nèi)置函數(shù)一樣，會(huì)向調(diào)用腳本或存儲(chǔ)過(guò)程返回標(biāo)量值，像GETDATE()或USER()函數(shù)就會(huì)返回標(biāo)量值。

UDF的返回值并不限于整數(shù)，而是可以返回除了BLOB、游標(biāo)(cursor)和時(shí)間戳以外的任何有效的SQL Server數(shù)據(jù)類(lèi)型(包括用戶(hù)自定義類(lèi)型)。

與存儲(chǔ)過(guò)程不同，用戶(hù)自定義函數(shù)返回值的目的是提供有意義的數(shù)據(jù)（而對(duì)于存儲(chǔ)過(guò)程來(lái)說(shuō)，返回值只能說(shuō)明成功或失敗，如果失敗，則會(huì)提供一些關(guān)于失敗性質(zhì)的特定信息。）

可在查詢(xún)中內(nèi)聯(lián)執(zhí)行函數(shù)(如作為SELECT語(yǔ)句的一部分)，而用存儲(chǔ)過(guò)程則不行。

例1：應(yīng)用在where語(yǔ)句中

CREATE FUNCTION DateOnly(@Date DateTime)
　　RETURNS varchar(12)
AS
　　BEGIN
    　　RETURN CONVERT(varchar(12),@Date,102)
　　END

然后試著，運(yùn)用一下：

SELECT * FROM Nx_comment 
　　WHERE dbo.DateOnly(com_posttime) = '2012.04.28'　　--注意前面的dbo是必須的。

其實(shí)以上SQL語(yǔ)句相當(dāng)于：

SELECT * FROM Nx_comment 
　　WHERE CONVERT(varchar(12),com_posttime,102) = '2012.04.28'

例2：應(yīng)用在select語(yǔ)句中

SELECT Name,Age,
    　　(SELECT AVG(Age) FROM Person) AS AvgAge,
     　　Age - (SELECT AVG(Age) FROM Person) AS Difference 
　　FROM Person

這里要說(shuō)明一下，列的意思分別是，姓名，年齡，平均年齡以及與平均年齡的差值。

下面我們用UDF來(lái)實(shí)現(xiàn)，先定義兩個(gè)UDF如下：

CREATE FUNCTION dbo.AvgAge()
　　RETURNS int
AS
　　BEGIN
    　　RETURN (SELECT AVG(Age) FROM Person)
　　END
GO

CREATE FUNCTION dbo.AgeDifference(@Age int)
　　RETURNS int
AS
　　BEGIN
    　　RETURN @Age - dbo.AvgAge();        --在一個(gè)UDF內(nèi)引用另外一個(gè)UDF，好華麗的說(shuō)
　　END

然后執(zhí)行查詢(xún)：

SELECT Name,Age,dbo.AvgAge() AS AvgAge,dbo.AgeDifference(Age) as Difference 
　　FROM Person

三、內(nèi)聯(lián)表值函數(shù)

SQL Server中的用戶(hù)自定義函數(shù)并不只限于返回標(biāo)量值，也可以返回表。返回的表在很大程度上和其他表是一樣的。

可以對(duì)返回 表的UDF執(zhí)行JOIN，甚至對(duì)結(jié)果應(yīng)用WHERE條件。

改為用表作為返回值并不難，對(duì)于UDF來(lái)說(shuō)，表就像任何其他SQL Server數(shù)據(jù)類(lèi)型一樣。

例1：像表一樣地用UDF

CREATE FUNCTION dbo.fnContactName()
　　RETURNS TABLE
AS
　　RETURN (
        　　SELECT Id,LastName + ',' + FirstName AS Name 　FROM Man
        　 )

然后我們就可以像表一樣地用UDF了。

SELECT * FROM dbo.fnContactName()

例2：帶參數(shù)返回表

CREATE FUNCTION dbo.fnNameLike(@LName varchar(20))
　　RETURNS TABLE
　　AS
　　RETURN (
        　　SELECT Id,LastName + ',' + FirstName AS Name FROM Man　WHERE LastName Like @LName + '%'
        　　)

然后查詢(xún)的時(shí)候可以這樣用：

SELECT * FROM dbo.fnNameLike('劉')

沒(méi)有WHERE子句，沒(méi)有過(guò)濾SELECT列表，就可以反復(fù)使用該函數(shù)，而不需要進(jìn)行"剪切和粘貼"。

四、多語(yǔ)句表值函數(shù)

語(yǔ)法:

CREATE FUNCTION Funtion_name
(
    --這里定義傳入?yún)?shù)及類(lèi)型
)
RETURNS
@table_name TABLE
(
    --這里定義@table_name的列名
)
AS
BEGIN
    --這里寫(xiě)sql語(yǔ)句并且將最終需要返回的結(jié)果集塞到@table_name 這張表里面
    RETURN 
END
GO

這個(gè)函數(shù)通過(guò)傳入一個(gè)十進(jìn)制的數(shù)字，分別返回對(duì)應(yīng)的二進(jìn)制、八進(jìn)制、十六進(jìn)制。

Create FUNCTION F_TConversion
(
    @NUM INT
)
RETURNS
@t_table TABLE
(
    [Binary] varchar(64),
    Octal varchar(16),
    Hexadecimal varchar(8)
)
AS
BEGIN
    DECLARE @RESULT2 VARCHAR(500)='',@RESULT8 VARCHAR(500)='',@RESULT16 VARCHAR(500)=''; 
    WITH CTE AS( 
        SELECT @NUM/2 D2,@NUM%2 S2,@NUM/8 D8,@NUM%8 S8,@NUM/16 D16,@NUM%16 S16,1 [INDEX] 
        UNION ALL 
        SELECT D2/2 , D2%2,D8/8 , D8%8,D16/16 , D16%16,[INDEX]+1 FROM CTE WHERE D2>0 
    ) 
    SELECT @RESULT2+=CAST(S2 AS VARCHAR(1))
          ,@RESULT8+=CASE WHEN D8=0 AND S8=0 THEN '' ELSE CAST(S8 AS VARCHAR(1)) END
          ,@RESULT16+=CASE WHEN D16=0 AND S16=0 THEN ''
                           ELSE CASE CAST(S16 AS VARCHAR(5))
                                WHEN '10' THEN 'A' 
                                WHEN '11' THEN 'B' 
                                WHEN '12' THEN 'C' 
                                WHEN '13' THEN 'D' 
                                WHEN '14' THEN 'E' 
                                WHEN '15' THEN 'F' 
                                ELSE CAST(S16 AS VARCHAR(5))
                            END
                        END
    FROM CTE ORDER BY [INDEX] DESC
    INSERT INTO @t_table
    SELECT @RESULT2,@RESULT8,@RESULT16
    RETURN 
END
GO

五、理解確定性

用戶(hù)自定義函數(shù)可以是確定性的也可以是非確定性的。如果給定了一組特定的有效輸入，每次函數(shù)就都能返回相同的結(jié)果，那么就說(shuō)該函數(shù)是確定性的。

SUM()就是一個(gè)確定性的內(nèi)置函數(shù)。3、5、10的總合永遠(yuǎn)都是18，而GETDATE()的值就是非確定性的，因?yàn)槊看握{(diào)用它的時(shí)候GETDATE()都會(huì)改變。

如果視圖或計(jì)算列引用非確定性函數(shù)，則在該視圖或列上將不允許建立任何索引。

如果判定函數(shù)是否是確定性的？除了上面描述的規(guī)則外，這些信息存儲(chǔ)在對(duì)象的IsDeterministic屬性中，可以利用OBJECTPROPERTY屬性檢查。

SELECT OBJECTPROPERTY(OBJECT_ID('DateOnly'),'IsDeterministic');　　--只是剛才的那個(gè)自定義函數(shù)

輸出結(jié)果如下：

居然是非確定性的。原因在于之前在定義該函數(shù)的時(shí)候，并沒(méi)有加上這個(gè)"WITH SCHEMABINDING"。

ALTER FUNCTION dbo.DateOnly(@Date date)
　　RETURNS date
　　WITH SCHEMABINDING　　--當(dāng)我們加上這一句之后
　　AS
　　BEGIN
　　　　RETURN @Date
　　END

在執(zhí)行查詢(xún)，該函數(shù)就是確定性的了。

原文鏈接：https://www.cnblogs.com/springsnow/p/9876587.html

欄目分類(lèi)
最近更新