SQL 自定義函數

一、作用：
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，返回今天是一周的第几天。

1. --創建函數fnget_weekday<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />
   
2. CREATE FUNCTION fnget_weekday
   
3. (@data DATETIME)
   
4. RETURNS INT
   
5. AS
   
6. BEGIN
   
7.     RETURN DATEPART(weekday,@data)
   
8. END
   
9. 
   
10. --執行語句
    
11. SELECT dbo.fnget_weekday(CONVERT(DATETIME,'20130211',111))
    

复制代码

         執行結果：

        

1.png(15.40 K)

2013/3/26 下午 06:10:05

    二、内嵌表值函数：

        1、創建語法：

          create function[属主名]函数名
          ([{@parameter_name[as]标量参数数据类型[=default]}[,...n]])
          returns table
          [with <function_option>[,...n]]
          [as]
          return [()select语句]

        2、實例：创建用户自定义内嵌表值函数fnNameAndClass，返回學生姓名和班級的表。

          創建資料表和資料：

1. --創建Student表
   
2. CREATE TABLE [dbo].[Student](
   
3.     [ID] [NCHAR](10) NOT NULL,
   
4.     [Name] [NCHAR](10) NOT NULL,
   
5.     [Class] [NCHAR](10) NOT NULL,
   
6.     [Age] INT NULL,
   
7. CONSTRAINT [PK_Student] PRIMARY KEY CLUSTERED
   
8. (
   
9.     [ID] ASC
   
10. )WITH(PAD_INDEX = OFF,STATISTICS_NORECOMPUTE = OFF,IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    
11. ) ON [PRIMARY]
    
12. GO
    
13. 
    
14. --創建資料
    
15. INSERT [Student] VALUES('001','張三','二班','15')
    
16. INSERT [Student] VALUES('002','王五','五班','16')
    
17. INSERT [Student] VALUES('003','趙六','一班','14')
    
18. INSERT [Student] VALUES('004','李玉','二班','13')
    
19. 
    
20. --查看Student表
    
21. SELECT [ID],[Name],[Class],[Age] FROM [dbo].[Student]
    

复制代码

         創建函數fnNameAndClass：

1. --創建fnNameAndClass函數
   
2. CREATE FUNCTION fnNameAndClass
   
3. (@ID NCHAR(10))
   
4. RETURNS TABLE
   
5. AS
   
6. RETURN
   
7. (SELECT Name,Class FROM dbo.Student WHERE ID=@ID)
   
8. 
   
9. 
   
10. --執行fnNameAndClass函數SELECT * FROM dbo.fnNameAndClass('001')
    

复制代码

        執行結果：

        

2.png(13.49 K)

2013/3/26 下午 06:10:05

    三、多声明表值函数：

        1、創建語法：

         create function 函数名（参数）
         returns 表变量名 ( 表变量字段定义 )[with {Encryption|Schemabinding}]
         as

         begin
         SQL 语句
         return
         end

        2、實例：创建用户自定义多声明表值函数CheckTables。

           創建資料表和資料：

1. --創建STInfro表
   
2. CREATE TABLE [dbo].[STInfro](
   
3.     [SID] [NCHAR](10) NOT NULL,
   
4.     [STName] [NCHAR](10) NOT NULL,
   
5.     [Grade] [NCHAR](10) NOT NULL,
   
6. CONSTRAINT [PK_STInfro] PRIMARY KEY CLUSTERED
   
7. (
   
8.     [SID] ASC
   
9. )WITH(PAD_INDEX = OFF,STATISTICS_NORECOMPUTE = OFF,IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
   
10. ) ON [PRIMARY]
    
11. GO
    
12. 
    
13. --STInfro表新增資料
    
14. INSERT STInfro VALUES('SY2012001','Tom','二年級')
    
15. INSERT STInfro VALUES('SY2012002','Fun','三年級')
    
16. INSERT STInfro VALUES('SY2011001','Kity','五年級')
    
17. 
    
18. --創建STScore表
    
19. CREATE TABLE [dbo].[STScore](
    
20.     [SID] [NCHAR](10) NOT NULL,
    
21.     [ClassName] [NCHAR](10) NOT NULL,
    
22.     [Score] [NCHAR](10) NOT NULL,
    
23. CONSTRAINT [PK_STScore] PRIMARY KEY CLUSTERED
    
24. (
    
25.     [SID] ASC
    
26. )WITH(PAD_INDEX = OFF,STATISTICS_NORECOMPUTE = OFF,IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    
27. ) ON [PRIMARY]
    
28. GO
    
29. 
    
30. --STScore表新增資料
    
31. INSERT STScore VALUES('SY2012001','美術','90')
    
32. INSERT STScore VALUES('SY2012002','插花','80')
    
33. INSERT STScore VALUES('SY2011001','音樂','70')
    
34. 
    
35. --查看STInfro的資料
    
36. SELECT * FROM dbo.STInfro
    
37. 
    
38. --查看STScore的資料
    
39. SELECT * FROM dbo.STScore
    

复制代码

         創建fnCheckTables函數：

1. --創建函數fnCheckTables
   
2. CREATE FUNCTION fnCheckTables(@B_ID NCHAR(10))
   
3. RETURNS @fnCheckTables TABLE(B_Name NCHAR(10), B_Words NCHAR(10))
   
4. AS
   
5.     BEGIN
   
6.         IF @B_ID='SY2011001'
   
7.             INSERT @fnCheckTables
   
8.             SELECT STName, Grade FROM STInfro WHERE SID=@B_ID
   
9.         ELSE
   
10.             INSERT @fnCheckTables
    
11.             SELECT ClassName, Score FROM STScore WHERE SID=@B_ID
    
12.         RETURN
    
13.     END
    
14.     
    
15. --執行fnCheckTables函數SELECT * FROM dbo.fnCheckTables('SY2011001')
    

复制代码

         執行結果：

        

vn.png(13.93 K)

2013/3/26 下午 06:10:05

      注意：在调用自定义函数时，必须指明函数的拥有者，比如本例中必须带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