T-SQL SQL Server UDF自定義函數(shù)概念與案例實(shí)戰(zhàn)

函數(shù)的定義

這里的函數(shù)指的是用戶自定義函數(shù)(UDF)全名為(user-defined function)，以下簡(jiǎn)稱為函數(shù)。

它是數(shù)據(jù)庫(kù)里的用戶自定義程序，用戶可以指定輸入?yún)?shù)，制定計(jì)算邏輯，最終返回一個(gè)標(biāo)量的值或者結(jié)果集。一般我們通過(guò)T-SQL或者CLR來(lái)定義函數(shù)，這里我們重點(diǎn)介紹的是T-SQL的方式。還有一類(lèi)函數(shù)是系統(tǒng)內(nèi)置的函數(shù)又稱之為系統(tǒng)函數(shù)，我們直接調(diào)用即可。

函數(shù)的使用場(chǎng)景

函數(shù)可以在很多場(chǎng)景下使用，對(duì)一個(gè)標(biāo)量和表（表變量）的字段或者計(jì)算列、檢查約束里都可適用。

函數(shù)的語(yǔ)法限制

有些語(yǔ)法在函數(shù)是不支持的，詳細(xì)見(jiàn)下：

1 用戶錯(cuò)誤異常處理,不能使用TRY CATCH、@ERROR、RAISERROR等語(yǔ)法

2 修改數(shù)據(jù)(DML),對(duì)表的數(shù)據(jù)進(jìn)行增加、修改、刪除，表變量除外。

3 使用用戶自定義(DDL)即對(duì)表結(jié)構(gòu)的增加、修改、刪除

4 不可以調(diào)用自定義存儲(chǔ)過(guò)程，但可以調(diào)用擴(kuò)展存儲(chǔ)過(guò)程

5 不能用臨時(shí)表

6 不能用動(dòng)態(tài)SQL

7 不能返回多個(gè)結(jié)果集

8 SET語(yǔ)句不能使用

9 不能調(diào)用系統(tǒng)影響型函數(shù)如NEWID、RAND

完整內(nèi)容詳細(xì)見(jiàn)官網(wǎng)介紹:?自定義函數(shù)的限制

函數(shù)的返回類(lèi)型

函數(shù)按照返回類(lèi)型可分為標(biāo)量值函數(shù)、內(nèi)聯(lián)型函數(shù)、多語(yǔ)內(nèi)聯(lián)型函數(shù)，下文將結(jié)合案例分別介紹這些類(lèi)型的函數(shù)。

標(biāo)量型函數(shù)

標(biāo)量型函數(shù)即是指函數(shù)返回的值是個(gè)標(biāo)量(單個(gè)值)。這種類(lèi)型的函數(shù)在函數(shù)體的頭部需要指定返回的類(lèi)型（如int、money、varchar等）。

#這里假設(shè)我們有個(gè)層次(父子)的樹(shù)形員工數(shù)據(jù)，比如某個(gè)職位是HR MANAGER(HR經(jīng)理)的，他下面有一些職位是HR的同事，這些職位是HR的下面是職位是HR Intern(實(shí)習(xí)HR)的同事。

DROP TABLE IF EXISTS dbo.Emp; GO CREATE TABLE dbo.emp ( empid INT NOT NULL CONSTRAINT PK_emp_empid PRIMARY KEY, mgrid INT NULL CONSTRAINT FK_emp_empid REFERENCES dbo.emp, empname VARCHAR(25) NOT NULL, jobtitle VARCHAR(25) NOT NULL, salary int NOT NULL, CHECK (empid <> mgrid) );INSERT INTO dbo.emp(empid, mgrid, empname,jobtitle, salary) VALUES(1, NULL, '張三','CEO', 10000), (2, 1, '李四','CTO', 7000), (3, 1, '王五','COO', 7500), (4, 2, '劉二','Product MANAGER', 6000), (5, 2, '馬六','Program MANAGER', 5500), (6, 2, '秦一','Test MANAGER', 4500), (7, 3, '宋二','HR MANAGER', 5000), (8, 4, '江五','Product', 5000), (9, 5, '谷三','C++', 2500), (10, 5, '谷三','Python', 2500), (11, 5, '楊八','Java', 3000), (12, 6, '紀(jì)一','Test', 2500), (13, 6, '黎七','Test', 2500), (14, 7, '管一','HR', 3000), (15, 7, '關(guān)三','HR', 3000), (16, 14, '孔十','HR Intern' , 2000), (17, 15, '金三','HR Intern', 2000), (18, 15, '錢(qián)二','HR Intern', 1500); -- 傳一個(gè)參數(shù)（員工姓名）后獲取他(她)下面的員工個(gè)數(shù)。 -- # 這里假定員工姓名唯一，比較嚴(yán)謹(jǐn)?shù)氖峭ㄟ^(guò)員工編號(hào)作為參數(shù)。 CREATE OR ALTER FUNCTION dbo.fun_getEmpnumber(@empname AS VARCHAR(10)) RETURNS INT AS BEGINDECLARE @totalemp AS INT;WITH EmpsCTE AS(SELECT empid,mgrid, salary,empname,jobtitleFROM dbo.EmpWHERE empname = @empnameUNION ALLSELECT S.empid,S.mgrid, S.salary,S.empname,S.jobtitleFROM EmpsCTE AS MINNER JOIN dbo.Emp AS SON S.mgrid = M.empid)SELECT @totalemp= COUNT(empid) - 1 FROM EmpsCTE;RETURN @totalemp; END; -- 調(diào)用， -- 返回員工”宋二”下的員工數(shù)(不含自己)。 SELECT dbo.fun_getEmpnumber('宋二') 5 -- 返回員工”紀(jì)一”下的員工數(shù)(因下面沒(méi)人，所以結(jié)果為0)。 SELECT dbo.fun_getEmpnumber('紀(jì)一') 0 -- 返回員工”方四”下的員工數(shù)(因沒(méi)有”方四”這個(gè)人，所以結(jié)果為-1)。 SELECT dbo.fun_getEmpnumber('方四') -1 -- 當(dāng)然也可以結(jié)合字段一起使用 SELECT empid,mgrid, salary,empname,jobtitle, dbo.fun_getEmpnumber(empname) undernum FROM dbo.emp WHERE empname='宋二'

確定與非確定值函數(shù)

像SYSDATETIME、 RAND(不帶種子)、NEWID內(nèi)置的系統(tǒng)函數(shù)都是不確定函數(shù)，所謂不確定函數(shù)即是每次執(zhí)行時(shí)返回的結(jié)果不固定、不確定,而固定函數(shù)則是如果參數(shù)給定,那么函數(shù)的返回值必然確定。

這里對(duì)于不確定函數(shù)按照函數(shù)對(duì)SQL Server系統(tǒng)的影響又分為系統(tǒng)獨(dú)立型和系統(tǒng)影響型，SYSDATETIME屬于系統(tǒng)獨(dú)立型，因?yàn)槊看畏祷氐臅r(shí)間不固定，但對(duì)下一次的執(zhí)行不受上一次的影響。而RAND和NEWID則是系統(tǒng)影響型，即下一次的執(zhí)行受上一次的影響，因?yàn)檫@倆函數(shù)具有唯一性，每次執(zhí)行出來(lái)的結(jié)果都依賴于上一次的結(jié)果且不能和它一樣。

所以SYSDATETIME可以在用戶自定義函數(shù)里引用,而RAND和NEWID則不可以。示例見(jiàn)下:

CREATE OR ALTER FUNCTION dbo.fun_rand() RETURNS FLOAT AS BEGINRETURN RAND(); END; GO

報(bào)錯(cuò)信息見(jiàn)下：

如果想繞過(guò)這個(gè)限制，可以將不確定值函數(shù)放置在視圖里，然后通過(guò)定義函數(shù)訪問(wèn)該視圖即可。示例腳本見(jiàn)下:

-- Way2 示例里引用 CREATE OR ALTER VIEW dbo.view_rand AS SELECT RAND() AS myrand; GO CREATE OR ALTER FUNCTION dbo.fun_rand() RETURNS FLOAT AS BEGIN RETURN (SELECT myrand FROM dbo.view_rand); END; GO SELECT TOP 3 empid,mgrid,empname,rand() sys_rand, dbo.fun_rand() f_rand FROM dbo.Emp A -- 結(jié)果

特別需要注意的是：

1 這里調(diào)用了系統(tǒng)內(nèi)置函數(shù)rand和自定義的rand函數(shù)，系統(tǒng)的函數(shù)是批查詢級(jí)別的(整個(gè)批查詢次僅執(zhí)行一次rand函數(shù))，而自定義函數(shù)是記錄級(jí)別的(每條記錄都執(zhí)行了rand函數(shù))。

2 仔細(xì)查看上述腳本執(zhí)行結(jié)果不能得出上述結(jié)論。

內(nèi)置(非)確定值函數(shù)

綁定模式(SCHEMABINDING)選項(xiàng)

在持久化計(jì)算列里或者索引視圖里的自定義函數(shù)必須是確定值的。這就要求用戶自定義函數(shù)里不能調(diào)用非確定值函數(shù)并且定義時(shí)加上SCHEMABINDING選項(xiàng)。詳見(jiàn)如下例子的演示。

關(guān)于計(jì)算列：計(jì)算列分為非持久化和持久化兩種，非持久化的僅在查詢時(shí)執(zhí)行且不保存計(jì)算列結(jié)果，而持久化則可以保存計(jì)算列結(jié)果且可以建立索引。示例見(jiàn)下：

-- 1 創(chuàng)建函數(shù) CREATE OR ALTER FUNCTION dbo.fun_endyear(@datevar AS DATE) RETURNS DATE WITH SCHEMABINDING AS BEGIN RETURN DATEFROMPARTS(YEAR(@datevar), 12, 31); END; GO -- 2 創(chuàng)建表并制定計(jì)算列對(duì)應(yīng)的函數(shù) CREATE TABLE dbo.test ( id INT NOT NULL IDENTITY CONSTRAINT PK_test_id PRIMARY KEY, insertdate DATE NOT NULL, insertyear AS dbo.fun_endyear(insertdate) PERSISTED );

特別需要說(shuō)明：

1 這里針對(duì)非確定型函數(shù)需要加WITH SCHEMABINDING選項(xiàng)。SCHEMABINDING選項(xiàng)主要應(yīng)用在視圖里，主要對(duì)視圖依賴的表和字段起到阻止結(jié)構(gòu)變化的作用。同樣的在函數(shù)里指定該選項(xiàng)是為了阻斷函數(shù)依賴的表結(jié)構(gòu)的變換。

2 針對(duì)計(jì)算列定義時(shí)需要加PERSISTED關(guān)鍵字。

3 關(guān)于函數(shù)DATEFROMPARTS、YEAR函數(shù)使用的說(shuō)明，這里針對(duì)不同的時(shí)間格式，如dmy、myd、ymd都可以提取時(shí)間。見(jiàn)如下示例：

SET DATEFORMAT dmy;? GO? DECLARE @datevar DATE = '27/07/2021';? SELECT @datevar,DATEFROMPARTS(YEAR(@datevar), 12, 31);? GO? SET DATEFORMAT myd;? GO? DECLARE @datevar DATE = '07/2021/27';? SELECT @datevar,DATEFROMPARTS(YEAR(@datevar), 12, 31);? GO -- 結(jié)果均為: /* 2021-07-27? 2021-12-31 */

內(nèi)聯(lián)表型函數(shù)

表型函數(shù)在定義和使用上和標(biāo)量值函數(shù)類(lèi)似，不過(guò)這里返回的類(lèi)型是表變量，即是一個(gè)結(jié)果集。針對(duì)emp員工表，按照指定的分頁(yè)和頁(yè)數(shù)，僅顯示最后一批次的數(shù)據(jù)，詳細(xì)代碼見(jiàn)下：

-- 1 分頁(yè)函數(shù)示例： CREATE OR ALTER FUNCTION dbo.fun_GetPage(@pagenum AS BIGINT, @pagesize AS BIGINT) RETURNS TABLE WITH SCHEMABINDING AS RETURN WITH C AS ( SELECT ROW_NUMBER() OVER(ORDER BY empid) AS rownum, empid, empname,jobtitle,salary FROM dbo.emp ) SELECT empid, empname,jobtitle,salary FROM C WHERE rownum BETWEEN (@pagenum - 1) * @pagesize + 1 AND @pagenum * @pagesize; GO -- 2 調(diào)用示例 SELECT empid, empname,jobtitle,salary FROM dbo.fun_GetPage(3,2)-- 3 結(jié)果

?延展閱讀，SQL Server 2012之后版本分頁(yè)可用OFFSET FETCH語(yǔ)法，所以上述代碼可改寫(xiě)為：

CREATE OR ALTER FUNCTION dbo.fun_GetPage_V2(@pagenum AS BIGINT, @pagesize AS BIGINT) RETURNS TABLE WITH SCHEMABINDING AS RETURN SELECT ROW_NUMBER() OVER(ORDER BY empid) AS rownum,empid, empname,jobtitle,salary FROM dbo.emp ORDER BY empid OFFSET (@pagenum - 1) * @pagesize ROWS FETCH NEXT @pagesize ROWS ONLY; GO

多語(yǔ)句內(nèi)聯(lián)表函數(shù)

多語(yǔ)句內(nèi)聯(lián)表函數(shù)是內(nèi)聯(lián)表函數(shù)的延展。內(nèi)聯(lián)表一般是單個(gè)查詢，而多語(yǔ)句內(nèi)聯(lián)表函數(shù)在頭部定義表變量后可在函數(shù)體內(nèi)執(zhí)行多語(yǔ)句對(duì)表變量進(jìn)行處理（(WHILE循環(huán)、插入、更新、刪除等），最終返回該表變量。

多語(yǔ)句內(nèi)聯(lián)表應(yīng)用在父子查詢時(shí)比較方便，因?yàn)槲覀兛梢苑奖愕目刂聘腹?jié)點(diǎn)。同時(shí)它可以有自己復(fù)雜的業(yè)務(wù)邏輯，而相比較而言內(nèi)聯(lián)表函數(shù)只能返回一個(gè)查詢。

-- 1 多語(yǔ)句內(nèi)聯(lián)函數(shù)(獲取級(jí)聯(lián)子節(jié)點(diǎn))定義 DROP FUNCTION IF EXISTS dbo.func_GetChildtree; GO CREATE FUNCTION dbo.func_GetChildtree (@mgrid AS INT, @maxlevels AS INT = NULL) RETURNS @Tree TABLE -- 定義表變量以接收處理結(jié)果(empid INT NOT NULL PRIMARY KEY,mgrid INT NULL,empname VARCHAR(25) NOT NULL,jobtitle VARCHAR(25) NOT NULL,salary MONEY NOT NULL,lvl INT NOT NULL,sortpath VARCHAR(892) NOT NULL ,INDEX idx_lvl_empid_sortpath NONCLUSTERED(lvl, empid, sortpath))WITH SCHEMABINDING -- 綁定選項(xiàng),阻止表表結(jié)構(gòu)變化 AS BEGINDECLARE @lvl AS INT = 0;-- 初始化@Tree里根節(jié)點(diǎn)數(shù)據(jù)(參數(shù)@mgrid對(duì)應(yīng)的那條數(shù)據(jù))。INSERT INTO @Tree(empid, mgrid, empname,jobtitle, salary, lvl, sortpath)SELECT empid, NULL AS mgrid, empname,jobtitle, salary, @lvl AS lvl, '.' AS sortpathFROM dbo.EmpWHERE empid = @mgrid; -- UPDATE @Tree SET empname='五王2' WHERE mgrid IS NULL; -- 演示更新-- 借助系統(tǒng)變量@@ROWCOUNT(影響的行)和@maxlevels參數(shù)進(jìn)入循環(huán)尋找子節(jié)點(diǎn)WHILE @@ROWCOUNT > 0 AND (@lvl < @maxlevels OR @maxlevels IS NULL)BEGINSET @lvl += 1;-- 通過(guò)父子關(guān)聯(lián)插入子節(jié)點(diǎn)數(shù)據(jù)到@Tree表INSERT INTO @Tree(empid, mgrid, empname,jobtitle ,salary, lvl, sortpath)SELECT S.empid, S.mgrid, S.empname,S.jobtitle, S.salary, @lvl AS lvl,M.sortpath + CAST(S.empid AS VARCHAR(10)) + '.' AS sortpathFROM dbo.Emp AS SINNER JOIN @Tree AS MON S.mgrid = M.empid AND M.lvl = @lvl - 1;END;RETURN; -- 返回,這里即對(duì)應(yīng)開(kāi)頭的定義，返回@Tree END; GO-- 2 執(zhí)行 SELECT empid, REPLICATE(' | ', lvl) + empname AS empname, mgrid, salary, lvl, sortpath FROM dbo.func_GetChildtree(3, NULL) AS T ORDER BY sortpath;-- 3 結(jié)果

總結(jié)

以上是生活随笔為你收集整理的T-SQL里数据库工程师都不知道的秘密之SQL Server自定义函数UDF的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問(wèn)題。

如果覺(jué)得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。