除了使用系統提供的函數外,使用者還可以根據需要自定義函數。使用者自定義函數是 SQL Server 2000 新增的資料庫對象,是 SQL Server 的一大改進。與程式設計語言中的函數類似,Microsoft SQL Server 使用者定義函數是接受參數、執行操作(例如複雜計算)并将操作結果以值的形式傳回的例程。傳回值可以是單個标量值或表變量結果集。
使用者自定義函數不能用于執行一系列改變資料庫狀态的操作,但它可以像系統函數一樣在查詢或存儲過程等的程式段中使用,也可以像存儲過程一樣通過 EXECUTE 指令來執行。
在 SQL Server 中根據函數傳回值形式的不同将使用者自定義函數分為三種類型:
(1)标量函數
(2)内聯表值函數
(3)多語句表值函數
注意:與程式設計語言中的函數不同的是,SQL Server 自定義函數必須具有傳回值。
标量函數
标量函數傳回一個确定類型的标量值,其傳回值類型為除TEXT、NTEXT、IMAGE、CURSOR、TIMESTAMP和TABLE類型外的其它資料類型。函數體語句定義在BEGIN-END語句内。在 RETURNS 子句中定義傳回值的資料類型,并且函數的最後一條語句必須為Return語句。建立标量函數的格式:
Create Function 函數名(參數) Returns 傳回值資料類型 [With {Encryption|Schemabinding}] [AS] BEGIN SQL語句(必須有Return子句) END 舉例: CREATE FUNCTION dbo.Max ( @a int, @b int ) RETURNS int AS DECLARE @max int IF @a>@b SET @max=@a ELSE SET @max=@b Return @max
調用标量函數
可以在 T-SQL 語句中允許使用标量表達式的任何位置調用傳回标量值(與标量表達式的資料類型相同)的任何函數。必須使用至少由兩部分組成名稱的函數來調用标量值函數,即架構名.對象名,如dbo.Max(12,34)。
内聯表值函數
内聯表值型函數以表的形式傳回一個傳回值,即它傳回的是一個表。内聯表值型函數沒有由BEGIN-END 語句括起來的函數體。其傳回的表是由一個位于 RETURN 子句中的 SELECT 指令從資料庫中篩選出來。内聯表值型函數功能相當于一個參數化的視圖。
RETURNS table [with {Encryption|Schemabinding}] AS Return(一條SQL語句) CREATE FUNCTION func (@id char(8)) RETURNS TABLE RETURN SELECT * FROM student WHERE SID = @id
調用内聯表值函數:調用時不需指定架構名,如select * from func('51300521')
多語句表值函數
多語句表值函數可以看作标量函數和内聯表值函數的結合體。它的傳回值是一個表,但它和标量型函數一樣有一個用 BEGIN-END 語句括起來的函數體,傳回值的表中的資料是由函數體中的語句插入的。由此可見,它可以進行多次查詢,對資料進行多次篩選與合并,彌補了内聯表值函數的不足。
RETURNS 表變量名 (表變量字段定義) SQL語句 Return CREATE FUNCTION func(@selection int) RETURNS @table TABLE SID char(4) primary key not null, SName nvarchar(4) null IF @selection = 0 INSERT INTO @table (SELECT SID,SName FROM student0) ELSE INSERT INTO @table (SELECT SID,SName FROM student1)
調用多語句表值函數:和調用内聯表值函數一樣,調用時不需制定架構名。
注意:Schemabinding用于将函數綁定到它引用的對象上。函數一旦綁定,則不能删除、修改,除非删除綁定。
本文轉自左正部落格園部落格,原文連結:http://www.cnblogs.com/soundcode/archive/2012/01/16/2323312.html,如需轉載請自行聯系原作者