SQL自定义函数

自定义函数与存储过程的区别（存在的意义）：

1.     能够在select等SQL语句中直接使用自定义函数，存储过程不行。

2.     自定义函数可以调用其他函数，也可以调用自己（递归）

3.     可以在表列和 CHECK 约束中使用自定义函数来实现特殊列或约束

4.       自定义函数不能有任何副作用。函数副作用是指对具有函数外作用域（例如数据库表的修改）的资源状态的任何永久性更改。函数中的语句唯一能做的更改是对函数上的局部对象（如局部游标或局部变量）的更改。不能在函数中执行的操作包括：对数据库表的修改，对不在函数上的局部游标进行操作，发送电子邮件，尝试修改 目录，以及生成返回至用户的结果集。存储过程没有此限制

5.       函数只能返回一个变量。而存储过程可以返回多个

用户自定义函数的类型:

标量函数:返回一个标量值

表格值函数{内联表格值函数、多表格值函数}:返回行集（即返回多个值）

1、标量函数

Create function 函数名（参数）

Returns 返回值数据类型

[with {Encryption | Schemabinding }]

[as]

begin

SQL语句(必须有return 变量或值)

End

2、表格值函数

a、 内联表格值函数

格式：

create function 函数名（参数）

returns table

[with {Encryption | Schemabinding }]

as

return(一条SQL语句)

 

create function tabcmess(@code varchar(10))

returns table

as

return(select ccode,scode from cmessage where ccode like @ccode)

 

b、 多句表格值函数

   create function 函数名（参数）

   returns 表格变量名table (表格变量定义)

   [with {Encryption | Schemabinding }]

as

   begin

    SQL语句

   end

 

 多句表格值函数包含多条SQL语句，至少有一条在表格变量中填上数据值

 表格变量格式

returns @变量名 table (column 定义| 约束定义 [,…])

对表格变量中的行可执行select,insert,update,delete ， 但select into 和 insert 语句的结果集是从存储过程插入。

 

Create function tabcmessalot (@code varchar(10))

Returns @ctable table(code varchar(10) null,cname varchar(100) null)

As

Begin

Insert @ctable

Select ccode,explain from cmessage

Where scode like @code

return

End

适用范围

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后面不跟任何变量。