1、函數的執行速度比普通的SQL代碼快:函數在重執行時通過緩存計劃來降低SQL編譯開銷,從而避免了重複解析與優化,縮短了執行時間,提高數據庫的性能。
2、模塊化的設計,提高數據庫與應用程序開發性能:我們可以把一些複雜的SQL語句寫成函數,使用時只需調用函數,不用再去寫複雜的SQL語句,把函數作為一個模塊,提高性能。
3、減少網絡流量,提高數據庫運行性能:通過函數可以只返回需要的結果,減少傳遞資料量,縮短用戶的等待時間。
二、構成:
在 SQL Server 2000 中根据函数返回 值形式的不同将用 户自定 义函数分 为三种 类型 :标量函数(Scalar Function )、内嵌表值函数(Inline Function )、多声明(語句)表值函数(Multi-Statement Function )
1、标量函数:标量函数是对单一值操作,返回单一值。能够使用表达式的地方,就可以使用标量函数。像我们经常使用的left、getdate等,都属于标量函数。系统函数中的标量函数包括:数学函数、日期和时间函数、字符串函数、数据类型转换函数等
2、内嵌表值函数:内嵌表值函数的功能相当于一个参数化的视图。它返回的是一个表,内联表值型函数没有由BEGIN-END 语句括起来的函数体。其返回的表由一个位于RETURN 子句中的SELECT 命令段从数据库中筛选出来。
3、多声明表值函数:可以看作标量型和内嵌表值型函数的结合体。它的返回值是一个表,但它和标量型函数一样有一个用BEGIN-END 语句括起来的函数体,返回值的表中的数据是由函数体中的语句插入的。由此可见,它可以进行多次查询,对数据进行多次筛选与合并,弥补了内联表值型函数的不足。
三、使用方法:
一、標量函數:
1、創建语法:
create function 名称
([{@参数名称 参数类型[=默认值]}[,n]])
returns 返回值类型
[with encryption]
[as]
begin
函数体
return 函数返回值
end
2、实例 :创建用户自定义标量函数fnget_weekday,返回今天是一周的第几天。
- --創建函數fnget_weekday<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />
- CREATE FUNCTION fnget_weekday
- (@data DATETIME)
- RETURNS INT
- AS
- BEGIN
- RETURN DATEPART(weekday,@data)
- END
- --執行語句
- SELECT dbo.fnget_weekday(CONVERT(DATETIME,'20130211',111))
執行結果:
二、内嵌表值函数:
1、創建語法:
create function[属主名]函数名
([{@parameter_name[as]标量参数数据类型[=default]}[,...n]])
returns table
[with <function_option>[,...n]]
[as]
return [()select语句]
2、實例:创建用户自定义内嵌表值函数fnNameAndClass,返回學生姓名和班級的表。
創建資料表和資料:
- --創建Student表
- CREATE TABLE [dbo].[Student](
- [ID] [NCHAR](10) NOT NULL,
- [Name] [NCHAR](10) NOT NULL,
- [Class] [NCHAR](10) NOT NULL,
- [Age] INT NULL,
- CONSTRAINT [PK_Student] PRIMARY KEY CLUSTERED
- (
- [ID] ASC
- )WITH(PAD_INDEX = OFF,STATISTICS_NORECOMPUTE = OFF,IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
- ) ON [PRIMARY]
- GO
- --創建資料
- INSERT [Student] VALUES('001','張三','二班','15')
- INSERT [Student] VALUES('002','王五','五班','16')
- INSERT [Student] VALUES('003','趙六','一班','14')
- INSERT [Student] VALUES('004','李玉','二班','13')
- --查看Student表
- SELECT [ID],[Name],[Class],[Age] FROM [dbo].[Student]
創建函數fnNameAndClass:
- --創建fnNameAndClass函數
- CREATE FUNCTION fnNameAndClass
- (@ID NCHAR(10))
- RETURNS TABLE
- AS
- RETURN
- (SELECT Name,Class FROM dbo.Student WHERE ID=@ID)
- --執行fnNameAndClass函數SELECT * FROM dbo.fnNameAndClass('001')
執行結果:
三、多声明表值函数:
1、創建語法:
create function 函数名(参数)
returns 表变量名 ( 表变量字段定义 )[with {Encryption|Schemabinding}]
as
begin
SQL 语句
return
end
2、實例:创建用户自定义多声明表值函数CheckTables。
創建資料表和資料:
- --創建STInfro表
- CREATE TABLE [dbo].[STInfro](
- [SID] [NCHAR](10) NOT NULL,
- [STName] [NCHAR](10) NOT NULL,
- [Grade] [NCHAR](10) NOT NULL,
- CONSTRAINT [PK_STInfro] PRIMARY KEY CLUSTERED
- (
- [SID] ASC
- )WITH(PAD_INDEX = OFF,STATISTICS_NORECOMPUTE = OFF,IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
- ) ON [PRIMARY]
- GO
- --STInfro表新增資料
- INSERT STInfro VALUES('SY2012001','Tom','二年級')
- INSERT STInfro VALUES('SY2012002','Fun','三年級')
- INSERT STInfro VALUES('SY2011001','Kity','五年級')
- --創建STScore表
- CREATE TABLE [dbo].[STScore](
- [SID] [NCHAR](10) NOT NULL,
- [ClassName] [NCHAR](10) NOT NULL,
- [Score] [NCHAR](10) NOT NULL,
- CONSTRAINT [PK_STScore] PRIMARY KEY CLUSTERED
- (
- [SID] ASC
- )WITH(PAD_INDEX = OFF,STATISTICS_NORECOMPUTE = OFF,IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
- ) ON [PRIMARY]
- GO
- --STScore表新增資料
- INSERT STScore VALUES('SY2012001','美術','90')
- INSERT STScore VALUES('SY2012002','插花','80')
- INSERT STScore VALUES('SY2011001','音樂','70')
- --查看STInfro的資料
- SELECT * FROM dbo.STInfro
- --查看STScore的資料
- SELECT * FROM dbo.STScore
創建fnCheckTables函數:
- --創建函數fnCheckTables
- CREATE FUNCTION fnCheckTables(@B_ID NCHAR(10))
- RETURNS @fnCheckTables TABLE(B_Name NCHAR(10), B_Words NCHAR(10))
- AS
- BEGIN
- IF @B_ID='SY2011001'
- INSERT @fnCheckTables
- SELECT STName, Grade FROM STInfro WHERE SID=@B_ID
- ELSE
- INSERT @fnCheckTables
- SELECT ClassName, Score FROM STScore WHERE SID=@B_ID
- RETURN
- END
-
- --執行fnCheckTables函數SELECT * FROM dbo.fnCheckTables('SY2011001')
執行結果:
注意:在调用自定义函数时,必须指明函数的拥有者,比如本例中必须带dbo,否则不可识别;
如果指定了with encryption,则创建函数被加密。
四、適用範圍:
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后面不跟任何变量。
參考:
http://database.ctocio.com.cn/sqlserver/73/8858573.shtml
http://www.cnblogs.com/xueyuangudiao/archive/2011/08/03/2126580.html