SQL自定义函数

在SQL中不但可以定义存储过程，还可以定义函数，触发器。这里我们简单讲讲函数的使用。

如果你对这些语法不熟悉，可以查看sql存储过程入门系列课程。这里所用到的语法都是那个课程涉及到。

查看连接SQL存储过程入门系列教程

http://www.cnblogs.com/lideng/archive/2013/04/11/3013966.html

函数我们分为系统函数和自定义函数。

一，系统函数

请参考一下链接 http://msdn.microsoft.com/zh-cn/library/ms191238(v=sql.105).aspx

这里还是简单说下系统函数。

比如在存储过程（四）中提到了捕获错误的函数

  　　　　 ERROR_NUMBER() 返回错误号。

　　　　ERROR_SEVERITY() 返回严重性。

　　　　ERROR_STATE() 返回错误状态号。

　　　　ERROR_PROCEDURE() 返回出现错误的存储过程或触发器的名称。

　　　　ERROR_LINE() 返回导致错误的例程中的行号。

　　　　ERROR_MESSAGE() 返回错误消息的完整文本。该文本可包括任何可替换参数所提供的值，如长度、对象名或时间。

这些函数都是系统函数。

比如我们想查看当前用户登录的用户名

SELECT SUSER_NAME();

这样便会返回登录的用户名了。好了关于系统函数，我们就将这样多了。

我们主要讲讲自定义函数，怎么编写，怎么调用。

二，自定义函数

自定义函数分为：标量值函数或表值函数两种。

• 标量值函数：如果 RETURNS 子句指定一种标量数据类型，则函数为标量值函数。
• 表值函数：如果 RETURNS 子句指定 TABLE，则函数为表值函数。
  

表值函数又分为两种：内嵌表值函数（行内函数）或多语句函数

• 如果 RETURNS 子句指定的 TABLE 不附带列的列表，则该函数为内嵌表值函数。
• 如果 RETURNS 子句指定的 TABLE 类型带有列及其数据类型，则该函数是多语句表值函数

如果你不晓得Returns从哪里来，请看创建函数的语法（这里是创建标量值函数的语法）：

Create function 函数名（参数）
Returns 返回值数据类型
[with {Encryption | Schemabinding }]
[as]
begin
SQL语句(必须有return 变量或值)
End

这里的with为附加选项：

• 如果需要对函数体进行加密，可使用WITH ENCRYPTION；
• 如果需要将创建的函数与引用的数据库绑定，可以使用WITH SCHEMABINDING（注：函数一旦绑定，则不能删除、修改，除非删除绑定）

现在我们就为三种函数分别举个例子看看。

1. 首先看一个标量值函数

           

CREATE FUNCTION Foo(@ret int )  --传入了一个int类型的参数
RETURNS int       --注意这里返回的是一个数据类型
AS  
BEGIN 
    declare @n int
    set @n = 3
    return @n* @ret
END

函数我们创建好了，怎么调用呢，看看下面

select dbo.foo(2)

结果输出为6,这里需要注意的是：创建函数的时候不需要加dbo.，但在访问的时候，标量函数要加.dbo的，否则的话会报错“不是可以识别的 内置函数名称。”

2.首先看定义一个内嵌表值函数语法：

create function 函数名（参数）
returns table
[with {Encryption | Schemabinding }]
as
return(一条SQL语句)

还是来看个例子比较直观:

create function GetUser(@name varchar(10))
returns table
as
return select * from userInfo where userName=@name

函数创建好了，怎么调用呢

select * from getuser('admin')

上面的sql将会返回一行数据，如果记录存在的话，不存在的话当然就不显示了哈。调用是不是很简单呢。

3，这里就看第三种，多语句表值函数，查看定义：

 --多句表格值函数
   create function 函数名（参数）
   returns 表格变量名table (表格变量定义)
   [with {Encryption | Schemabinding }]
as
   begin
    SQL语句
   end
--多句表格值函数包含多条SQL语句，至少有一条在表格变量中填上数据值

我们来看个例子：

create function GetInfo(@name varchar(20))
returns @cTable table(UserName varchar(10),UserPwd varchar(10))
as
begin
　　insert into @cTable
　　select userName,userPass from userinfo where username=@name
return   --函数中最后一条语句必须是返回语句。
end
--调用
select * from GetInfo('admin')
------------
UserName UserPwd
admin     amin

对于sql的函数大体都这样了，这里我们来看个例子。

如果我们想在sql 中写一个函数，输入一个参数，返回是1到这个参数的求和值，参数当然是正整数类型的。

于是写了下面这个函数。

create function sumUp(@number int)
returns int
as 
begin
    declare @sum int,@i int;
    set @sum = 0;
    set @i = 0;
    while @i <= @number
    begin
         set @sum=@sum+@i
         set @i=@i+1
     end
    return @sum
end

从1到10的求和

select dbo.sumUp(10)

------
55

从1到100的求和

select dbo.sumUp(100)

--------
5050

看到函数的方便处了赛。

当然函数还有其他的用法，比如我们动态构造一张表，根据某些业务生产这张表，然后返回来，并与数据库中的表连接查询。

如果你对上面的语法不熟悉，比如变量的声明啊，赋值啊，while的循环啊，可以查看SQL存储过程入门系列，语法都是一样的。

http://www.cnblogs.com/lideng/archive/2013/04/11/3013966.html

我这里有个SQL题目，当然是网上找的哈，欢迎各位尝试交流，

Design a product catalogue with products (name, price, description), and n-level and multiple categories and manufacturer (name, logo). Draw normalized table structure with primary & foreign keys and write SQL to retrieve all n-level category products recursively, expected output:

Books – Philosophy – Metaphysics

Books – Philosophy – Confucianism - Mencius

Books – Literature – Lin Yutang

Software – Utilities – File Management