SQLServer自定義函數(shù)

在SQLServer中不僅可以使用系統(tǒng)函數(shù)（如：聚合函數(shù)，字符串函數(shù)，時(shí)間日期函數(shù)等）還可以根據(jù)需要自定義函數(shù)。

自定義函數(shù)分為標(biāo)量值函數(shù)和表值函數(shù)。

其中，標(biāo)量值函數(shù)用于返回單個(gè)值，而表值函數(shù)用于返回一個(gè)結(jié)果集。

函數(shù)參數(shù)

參數(shù)可以是常量、表中的某個(gè)列、表達(dá)式或其他類型的值。在函數(shù)中有三種類型的參數(shù)。

1、輸入：指必須輸入一個(gè)值。

2、可選值：在執(zhí)行該參數(shù)時(shí)，可以選擇不輸入?yún)?shù)。

3、默認(rèn)值：函數(shù)中默認(rèn)有值存在，調(diào)用時(shí)可以不指定該值。

創(chuàng)建標(biāo)量值函數(shù)

語法：

Create function 函數(shù)名（參數(shù)）Returns 返回值數(shù)據(jù)類型[with {Encryption | Schemabinding }][as]begin　　SQL語句(必須有return 變量或值)EndSchemabinding :將函數(shù)綁定到它引用的對(duì)象上（注：函數(shù)一旦綁定，則不能刪除、修改，除非刪除綁定）

例子：

drop function dbo.input  --刪除函數(shù)gocreate function dbo.input --定義函數(shù) 架構(gòu).方法名(@num1 int,   --輸入?yún)?shù)@num2 int = null,  --可選參數(shù)@oper varchar = '+' --默認(rèn)參數(shù))returns intasbegin    declare @sum int    if(@oper='+')    begin        set @sum = @num1 + @num2    end    else    begin        set @sum = 0    end    return @sumendgoselect dbo.input(1,null,default)  --參1必填，參2可選，參3默認(rèn)select dbo.input(1,2,default) --輸出3select dbo.input(1,2,'*') --輸出0 *沒判斷

自定義函數(shù)可以將值放在局部變量中，用set select exec賦值

declare @number intselect @number = dbo.input(1,2,default)PRint @numberdeclare @set intset @set = dbo.input(1,2,default)print @setdeclare @exec intexec @exec = dbo.input 1,2,'+'print @exec

在查詢中引用函數(shù)

create table test(    id int identity(1,1),    name varchar(10),    birthDay datetime)insert into test values('張三','1998-02-01'),('李四','1981-10-1'),('王五','1985-5-2')select * from test --測試信息

創(chuàng)建函數(shù)并執(zhí)行后

create function dbo.getAge(@birthDay datetime)returns intasbegindeclare @age intset @age = datediff(yy,@birthDay,getdate())return @ageendselect name as 姓名,dbo.getAge(birthDay) as 年齡 from test

注意：標(biāo)量值函數(shù)不可以返回文本（text、ntext）、圖像、游標(biāo)或時(shí)間戳類型的數(shù)據(jù)，并且不能用來修改數(shù)據(jù)庫狀態(tài)。

在select語句中使用函數(shù)可能會(huì)帶來負(fù)面影響，因?yàn)槊糠祷匾恍卸紩?huì)調(diào)用函數(shù)一次。所以在返回大型數(shù)據(jù)集時(shí)應(yīng)該格外避免使用復(fù)雜的函數(shù)。

表值函數(shù)

表值函數(shù)包含兩種類型：內(nèi)聯(lián)函數(shù)和多語句函數(shù)。

內(nèi)聯(lián)表值函數(shù)僅返回一個(gè)結(jié)果集，而多語句函數(shù)可以在函數(shù)體中包含一些控制邏輯。

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

語法：

create function 函數(shù)名（參數(shù)）returns table[with {Encryption | Schemabinding }]asreturn(一條SQL語句)

例子：

--創(chuàng)建create function getDetails(@id int)returns tableasreturn(select * from student where id = @id) --執(zhí)行一條語句后返回--調(diào)用select * from dbo.getDetails(10)

2、多語句函數(shù)

多語句函數(shù)可以通過多條語句來創(chuàng)建臨時(shí)表，具體需要哪些字段，以及符合哪些要求的數(shù)據(jù)被添加到臨時(shí)表中等。

語法：

create function 函數(shù)名（參數(shù)）returns 表格變量名 table (表格變量定義)[with {Encryption | Schemabinding }]asbegin    SQL語句end 

例子：

create function dbo.Test()returns @temp table (    name varchar(20),    sex char(2),    age int)asbegininsert into @temp (name,sex,age) values ('多語句','嘛',18)insert into @temp (name,sex,age) select name,sex,age from student where age > 18return end

可以看出，多語句函數(shù)返回結(jié)果是定義好表結(jié)構(gòu)的虛擬表，最后有一個(gè)return用來告訴sql多語句已經(jīng)執(zhí)行完畢。不寫會(huì)返回不了。