SQL Server 自訂函數

標籤：end   內嵌   select   刪除   文法   分割   執行   sql 語句   begin   

簡介

   SQL Server自訂函數分為三種類型：純量涵式（Scalar Function）、內聯資料表值函式（Inline Function）、多語句資料表值函式（Multi-Statement Function）

         純量涵式：純量涵式返回一個確定類型的標量值，傳回值類型為除TEXT、NTEXT、IMAGE、CURSOR、TIMESTAMP和TABLE類型外的其它資料類型。函數體語句定義在BEGIN-END語句內，在 RETURNS 子句中定義傳回值的資料類型，並且函數的最後一條語句必須為Return語句

   內聯資料表值函式：內聯表值型函數以表的形式返回一個傳回值，即它返回的是一個表。內聯表值型函數沒有由BEGIN-END 語句括起來的函數體。其返回的表是由一個位於 RETURN 子句中的 SELECT 命令從資料庫中篩選出來。內聯表值型函數功能相當於一個參數化的視圖

多語句資料表值函式：多語句資料表值函式可以看作純量涵式和內聯資料表值函式的結合體。它的傳回值是一個表，但它和標量型函數一樣有一個用 BEGIN-END 語句括起來的函數體，傳回值的表中的資料是由函數體中的語句插入的。由此可見，它可以進行多次查詢，對資料進行多次篩選與合并，彌補了內聯資料表值函式的不足

純量涵式

建立純量涵式的文法：

 create function [函數的所有者].函數名(標量參數 [as] 標量參數類型 [=預設值]) returns 標量傳回值類型 begin     函數體(即 Transact-SQL 陳述式)     return 變數/標量運算式 end

案例：將字串‘001.002.003.004’按照指定分隔字元進行分割，返回分割後的個數

create function dbo.Fun_GetStrListLeng(@originlStr varchar(500), --要分割的字串@splitvarchar(10)  --分隔字元)returns intasbegindeclare @location int,--定義起始位置@start    int,--定義從第幾個開始@length  int;--定義變數，用於接收計算元素的個數set @originlStr=ltrim(rtrim(@originlStr))--去掉左右兩邊的空格set @location=charindex(@split,@originlStr) --分割符號在字串中第一次出現的位置（索引從1開始計數）set @length=1while @location<>0beginset @[email protected]+1set @location=charindex(@split,@originlStr,@start)set @[email protected]+1endreturn @lengthend

執行使用者自訂純量涵式：

select dbo.Fun_GetStrListLeng(‘001.002.003.004.005‘,‘.‘) --返回5

    建立函數時指定了函數所有者，那麼調用的時候也必須指定函數的所有者。（一般都為 dbo）

調用自訂函數時如果想不傳入參數而使用預設值，那麼必須使用 default 關鍵字。如果自訂函數的參數沒有預設值，那麼會返回 null。

內聯資料表值函式

建立內聯資料表值函式的文法：

 create function [函數的所有者].函數名(標量參數 [as] 標量參數類型 [=預設值]) returns table [with {Encryption | Schemabinding }] [as] return(單個 SELECT 語句，確定返回的表的資料。)

案例：查詢指定學號的學生的選課情況（包括學號、姓名、課程號和成績），然後調用該函數查詢某位學生的選課情況

create function dbo.Fun_GetList(@學號 char(5))returns tablereturn(    select student.sno,student.sname,course.cno,score.degree    from student,course,score    where student.sno=score.sno and score.cno=course.cno    and [email protected]學號)

調用文法：

select * from dbo.Fun_GetList(‘2001‘)

多語句資料表值函式

建立多語句函數的文法：

 create function [函數的所有者].函數名(標量參數 [as] 標量參數類型 [=預設值]) returns @表變數 table 表的定義(即列的定義和約束) begin     函數體(即 Transact-SQL 陳述式)     return end

案列：將字串‘001.002.003.004’按照指定分隔字元進行分割，然後返回

 alter FUNCTION [dbo].[Fun_SplitStr] (     @originalStr      VARCHAR(8000), --要分割的字串     @split varchar(100)  --分隔字元號 ) RETURNS @temp TABLE(Result VARCHAR(100)) AS BEGIN     DECLARE @result AS VARCHAR(100);   --定義變數用於接收單個結果     SET @originalStr = @originalStr + @split ;     WHILE (@originalStr <> ‘‘)     BEGIN         SET @result = LEFT(@originalStr, CHARINDEX(@split, @originalStr) -1) ;         INSERT @temp VALUES(@result) ;         --STUFF()函數用於刪除指定長度的字元，並可以在指定的起點處插入另一組字元。         SET @originalStr = STUFF(@originalStr, 1, CHARINDEX(@split, @originalStr), ‘‘);     END     RETURN END

調用方式跟內嵌函式相同

適用範圍

1.      只查詢，不修改資料庫的狀態（修改、刪除表中記錄等）

2.      結果集需要通過遞迴等方法得到時，可以使用函數，函數比較靈活

3.      結果集需要直接被引用時，可以使用函數。需要對結果集進行再加工（指放在select語句中等），可以使用函數，函數可以嵌在select等sql語句中。

注意事項：

使用者自訂函數不能用於執行一系列改變資料庫狀態的操作

在編寫自訂函數時需要注意的：

對於純量涵式：

1.      所有的入參前都必須加@

2.      create後的返回，單詞是returns，而不是return

3.      returns後面的跟的不是變數，而是傳回值的類型，如：int，char等。

4.      在begin/end語句塊中，是return。

內嵌資料表值函式：

1.      只能返回table，所以returns後面一定是TABLE

2.      AS後沒有begin/end，只有一個return語句來返回特定的記錄。

多語句資料表值函式：

1.      returns後面直接定義返回的表類型，首先是定義表名，表明前面要加@，然後是關鍵字TABLE，最後是表的結構。

2.      在begin/end語句塊中，直接將需要返回的結果insert到returns定義的表中就可以了，在最後return時，會將結果返回。

3.      最後只需要return，return後面不跟任何變數。

參考：

https://www.cnblogs.com/Brambling/p/6686947.html

SQL Server 自訂函數