用户自定义函数
![T-SQL学习笔记-程序设计基础-自定义函数 - 钱途无梁 - Notebook of 钱途无梁 T-SQL学习笔记-程序设计基础-自定义函数 - 钱途无梁 - Notebook of 钱途无梁](https://i-blog.csdnimg.cn/blog_migrate/17c135a2763225eb253f4ff92e88a664.gif)
自定义函数不能执行一系列改变数据库状态的操作,可以像系统函数在查询或存储过程等的程序中使用,也可以像相信过程一样能过execute命令来执行。自定义函数中存储了一个Transact
- SQL例程可以返回一定的值。根据函数返回值形式的不同,将用户看定义函数分为三种类型:
![T-SQL学习笔记-程序设计基础-自定义函数 - 钱途无梁 - Notebook of 钱途无梁 T-SQL学习笔记-程序设计基础-自定义函数 - 钱途无梁 - Notebook of 钱途无梁](https://i-blog.csdnimg.cn/blog_migrate/17c135a2763225eb253f4ff92e88a664.gif)
(
1 ).标量型函数:标量型函数返回一个确定类型的标量值,其返回值类型为除了text, ntext , image ,
cursor ,timestampt和table类型外的其它数据类型。函数体语句定义在begin
- end语句内,其中包含了可以返回值的Transact
- SQL命令。
![T-SQL学习笔记-程序设计基础-自定义函数 - 钱途无梁 - Notebook of 钱途无梁 T-SQL学习笔记-程序设计基础-自定义函数 - 钱途无梁 - Notebook of 钱途无梁](https://i-blog.csdnimg.cn/blog_migrate/17c135a2763225eb253f4ff92e88a664.gif)
语法:
create
function
[
owner_name
] function_name
![T-SQL学习笔记-程序设计基础-自定义函数 - 钱途无梁 - Notebook of 钱途无梁 T-SQL学习笔记-程序设计基础-自定义函数 - 钱途无梁 - Notebook of 钱途无梁](https://i-blog.csdnimg.cn/blog_migrate/17c135a2763225eb253f4ff92e88a664.gif)
(
[
{@parameter_name [as
] scalar_parameter_data_type
[
=default
] }
[
,
n
] ])
returns scalar_return_data_type
[
with <function_option> [,
n
] ]
[
as
]
begin
![T-SQL学习笔记-程序设计基础-自定义函数 - 钱途无梁 - Notebook of 钱途无梁 T-SQL学习笔记-程序设计基础-自定义函数 - 钱途无梁 - Notebook of 钱途无梁](https://i-blog.csdnimg.cn/blog_migrate/17c135a2763225eb253f4ff92e88a664.gif)
function_body
return
[
scalar_expression
]
end
![T-SQL学习笔记-程序设计基础-自定义函数 - 钱途无梁 - Notebook of 钱途无梁 T-SQL学习笔记-程序设计基础-自定义函数 - 钱途无梁 - Notebook of 钱途无梁](https://i-blog.csdnimg.cn/blog_migrate/17c135a2763225eb253f4ff92e88a664.gif)
其中:
<
1
> .function_option有两个可选值:{encryption
| schemabinding}
![T-SQL学习笔记-程序设计基础-自定义函数 - 钱途无梁 - Notebook of 钱途无梁 T-SQL学习笔记-程序设计基础-自定义函数 - 钱途无梁 - Notebook of 钱途无梁](https://i-blog.csdnimg.cn/blog_migrate/17c135a2763225eb253f4ff92e88a664.gif)
encryption:加密选项,让SQL Server对系统表中有关create function的声明加密,以防止用户自定义函数作为SQL Server复制的一部分被发布。
![T-SQL学习笔记-程序设计基础-自定义函数 - 钱途无梁 - Notebook of 钱途无梁 T-SQL学习笔记-程序设计基础-自定义函数 - 钱途无梁 - Notebook of 钱途无梁](https://i-blog.csdnimg.cn/blog_migrate/17c135a2763225eb253f4ff92e88a664.gif)
schemabinding,计划绑定选项。将用户自定义函数绑定到它所引用的数据库对象,则函数所涉及的的数据库对象从此将不能被删除或修改,除非函数被删除或去掉此选项。应注意的是要绑定的数据库对象必须与函数在同一数据库中。
<
2
> .owner_name:指定用户自定义函数的所有者。
<
3
> .function_name:指定用户自定义函数的名称。
<
4
> .database_name.owner_name.function_name应是唯一的。
<
5
> .
@parameter_name :定义一个式多个参数的名称,一个函数最多可以定义1024个参数,每个参数前用
@符号标明 ,参数的作用范围是整个函数,参数只能替代常量,不能替代表名,列名或其它数据库对象名称,用户自定义函数不支持输出参数。
<
6
> .scalar_parameter_data_type:指定标量参数的数据类型,除了text, ntext , image ,
cursor ,timestampt和table类型外的其它数据类型。
<
7
> .scalar_return_data_type:指定标量返回值的数据类型,除了text, ntext , image ,
cursor ,timestampt和table类型外的其它数据类型。
<
8
> .scalar_expression:指定标量型用户自定义函数返回的标量值表达式。
<
9
> .function_body:指定一系列的Transact_SQL语句它们决定了函数的返回值。
![T-SQL学习笔记-程序设计基础-自定义函数 - 钱途无梁 - Notebook of 钱途无梁 T-SQL学习笔记-程序设计基础-自定义函数 - 钱途无梁 - Notebook of 钱途无梁](https://i-blog.csdnimg.cn/blog_migrate/17c135a2763225eb253f4ff92e88a664.gif)
例:创建军工龄工资计算函数
use taihang
go
--
创建函数
create
function workyearwage(
@hiredate datetime ,
@today datetime ,
@per_wage money )
--
hiredate表示雇佣期,today表示当前日期,par_wage表示每一年的工龄应得的工资额
returns money
as
begin
declare
@workyearwage money
set
@workyearwage
= (
year (
@today )
-
year (
@hiredate ))
*
@per_wage
return (
@workyearwage )
end
--
结束函数定义
go
--
调用函数
select taihang.dbo.workyearwage(
'
1999-7-1
' ,
getdate (),
15 )
as work_year_wage
![T-SQL学习笔记-程序设计基础-自定义函数 - 钱途无梁 - Notebook of 钱途无梁 T-SQL学习笔记-程序设计基础-自定义函数 - 钱途无梁 - Notebook of 钱途无梁](https://i-blog.csdnimg.cn/blog_migrate/17c135a2763225eb253f4ff92e88a664.gif)
注意:以上的函数体可简写为:
as
begin
return ((
year (
@today )
-
year (
@hiredate ))
*
@per_wage )
end
![T-SQL学习笔记-程序设计基础-自定义函数 - 钱途无梁 - Notebook of 钱途无梁 T-SQL学习笔记-程序设计基础-自定义函数 - 钱途无梁 - Notebook of 钱途无梁](https://i-blog.csdnimg.cn/blog_migrate/17c135a2763225eb253f4ff92e88a664.gif)
(
2 ).内嵌表值函数:以表的形式返回一个返回值,即它返回的是一个表。内嵌表值型函数没有由begin
- end语句括起来的函数体,其返回的表由一个位于return子句中的select命令段从数据库中筛先出来。内嵌表值型函数功能相当于一个参数化的视图。
![T-SQL学习笔记-程序设计基础-自定义函数 - 钱途无梁 - Notebook of 钱途无梁 T-SQL学习笔记-程序设计基础-自定义函数 - 钱途无梁 - Notebook of 钱途无梁](https://i-blog.csdnimg.cn/blog_migrate/17c135a2763225eb253f4ff92e88a664.gif)
创建函数语法:
create
function
[
owner_name
] function_name
![T-SQL学习笔记-程序设计基础-自定义函数 - 钱途无梁 - Notebook of 钱途无梁 T-SQL学习笔记-程序设计基础-自定义函数 - 钱途无梁 - Notebook of 钱途无梁](https://i-blog.csdnimg.cn/blog_migrate/17c135a2763225eb253f4ff92e88a664.gif)
(
[
{@parameter_name [as
] scalar_parameter_data_type
[
=default
] }
[
,
n
] ])
returns
table
[
with <function_option> [,
n
] ]
[
as
]
return (
select
- stmt)
![T-SQL学习笔记-程序设计基础-自定义函数 - 钱途无梁 - Notebook of 钱途无梁 T-SQL学习笔记-程序设计基础-自定义函数 - 钱途无梁 - Notebook of 钱途无梁](https://i-blog.csdnimg.cn/blog_migrate/17c135a2763225eb253f4ff92e88a664.gif)
其中:
<
1
> .
table :批定返回值为一个表。
<
2
> .
select
- stmt:单个select语句,确定返回的表的数据。
![T-SQL学习笔记-程序设计基础-自定义函数 - 钱途无梁 - Notebook of 钱途无梁 T-SQL学习笔记-程序设计基础-自定义函数 - 钱途无梁 - Notebook of 钱途无梁](https://i-blog.csdnimg.cn/blog_migrate/17c135a2763225eb253f4ff92e88a664.gif)
例:创建返回所有订购某类产品的公司信息函数
use taihang
go
create
function orderfirms(
@productid varchar (
30 ))
--
productid表示产品代号
returns
table
as
return (
select
*
from products p
where p.p_id
=
@productid )
go
![T-SQL学习笔记-程序设计基础-自定义函数 - 钱途无梁 - Notebook of 钱途无梁 T-SQL学习笔记-程序设计基础-自定义函数 - 钱途无梁 - Notebook of 钱途无梁](https://i-blog.csdnimg.cn/blog_migrate/17c135a2763225eb253f4ff92e88a664.gif)
(
3 ).多语句表值型函数:可以看作标量型和内嵌表值型函数的结合体,它的返回值是一个表,但它和标量型函数一样有一个用begin
- end语句括起来的函数体。返回值表中的数据是由函数体
![T-SQL学习笔记-程序设计基础-自定义函数 - 钱途无梁 - Notebook of 钱途无梁 T-SQL学习笔记-程序设计基础-自定义函数 - 钱途无梁 - Notebook of 钱途无梁](https://i-blog.csdnimg.cn/blog_migrate/17c135a2763225eb253f4ff92e88a664.gif)
语法:
create
function
[
owner_name
] function_name
![T-SQL学习笔记-程序设计基础-自定义函数 - 钱途无梁 - Notebook of 钱途无梁 T-SQL学习笔记-程序设计基础-自定义函数 - 钱途无梁 - Notebook of 钱途无梁](https://i-blog.csdnimg.cn/blog_migrate/17c135a2763225eb253f4ff92e88a664.gif)
(
[
{@parameter_name [as
] scalar_parameter_data_type
[
=default
] }
[
,
n
] ])
returns
@return_variable
table
< table_type_definition
>
[
with <function_option> [,
n
] ]
[
as
]
begin
![T-SQL学习笔记-程序设计基础-自定义函数 - 钱途无梁 - Notebook of 钱途无梁 T-SQL学习笔记-程序设计基础-自定义函数 - 钱途无梁 - Notebook of 钱途无梁](https://i-blog.csdnimg.cn/blog_migrate/17c135a2763225eb253f4ff92e88a664.gif)
function_body
return
end
![T-SQL学习笔记-程序设计基础-自定义函数 - 钱途无梁 - Notebook of 钱途无梁 T-SQL学习笔记-程序设计基础-自定义函数 - 钱途无梁 - Notebook of 钱途无梁](https://i-blog.csdnimg.cn/blog_migrate/17c135a2763225eb253f4ff92e88a664.gif)
其中:
<
1
> .
< table_type_definition
> :({column_definition
| table_constraint}
[
,
n
] )
<
2
> .
@return_variable :一个table类型的变量,用于存储和累积返回的表中的数据行。
4 .修改用户自定义函数
alter
function :此命令语法与create function相同,相当于重建。
5 .删除用户自定义函数
drop
function {
[
owner_name
] function_name}
[
,
n
]
![T-SQL学习笔记-程序设计基础-自定义函数 - 钱途无梁 - Notebook of 钱途无梁 T-SQL学习笔记-程序设计基础-自定义函数 - 钱途无梁 - Notebook of 钱途无梁](https://i-blog.csdnimg.cn/blog_migrate/17c135a2763225eb253f4ff92e88a664.gif)
建议到企业管理器里修改,简单、方便。
以下转自:http://www.cnblogs.com/rainman/archive/2009/04/26/1443833.html
变量函数
create function max2( @par1 real , @par2 real)
returns real
as
begin
declare @par real
if @par1 > @par2
set @par = @par1
else
set @par = @par2;
return @par
end
内联表值函数
create function showPerson( @par int )
returns table
as
return
select * from Person where Pno = @par
select * from dbo.showPerson(2);
多语句表值函数
create function showStudent ( @xuehao varchar(12) )
returns @chengji table( xuehao nchar(12) , xingming nchar(20) )
as
begin
insert @chengji
select xuehao , xingming from student where xuehao = @xuehao
return
end
sqlserver自定义函数返回表类型
Sql代码 www.2cto.com
use WANSYSTEMDB
go
create function query_question_option(
@pid int
)
returns @temptale table
(
pid int ,
optionstr varchar(5000)
)
as
begin
declare @fullText varchar(5000)
set @fullText=''
select @fullText=@fullText+oflag+soption+'>>' from question_option where qid=@pid
insert into @temptale values(@pid,@fullText)
return
end