用户自定义函数


 

  在使用SQL server的时候,除了其内置的函数之外,还允许用户根据需要自己定义函数。根据用户定义函数返回值的类型,可以将用户定义的函数分为三个类别:

  • 返回值为可更新表的函数

select语句且语句可更新,则该函数返回的表也可更新,这样的函数称为内嵌表值函数。

  • 返回值不可更新表的函数

select语句,则该函数返回的表不可更新。这样的函数称为多语句表值函数。

  • 返回标量值的函数

标量函数。


 

  在这里需要说明一下,用户定义的函数是可以接受零个或多个输入参数的,函数的返回值可以是一个数值,也可以是一个表。用户定义的函数不支持输出函数;

alter function可以对用户定义函数进行修改,用drop function 可以删除用户定义函数(当然,也可以直接通过图形界面操作进行删除,但这里不多累述);

 

标量函数的定义与调用

  标量函数定义的语法格式如下: 



1  create function [owner_name] function_name
2  ([{@parameter_name [as] scalar_parameter_date_type [=default]}[,…n]])
3  returns scalar_return_data_type [with encryption] [as]
4  begin
5      function_body
6      return scalar_expression
7  end



  其中的含义分别如下:

  • owner_name : 数据库所有名。
  • function_name:用户定义函数名,函数名必须符合标示符规范,对其所有者来说,该用户名在数据库中必须是唯一的。
  • @parameter_name:用户定义函数的形参名, create function 语句中可以申明一个或多个参数,用@符号作为第一个字符来指定形参名 ,每个函数的参数局部作用于该函数。
  • scalar_parameter_data_type:参数的数据类型,可为系统支持的基本标量类型,不能为 timestamp 类型、用户定义数据类型、非标量类型(如 cursor 和 tabel)。
  • default:指定默认值。
  • with 子句指出了创建函数的选项,如果指出了 encryption 参数,则创建的函数是被加密的,函数定义的文本将以不可读的形式存储在 syscomments 表中,任何人都不能查看该函数的定义,包括函数的创建者和系统管理员。
  • begin 和 end 之间定义了函数题,该函数体中必须包括一条 return语句,用于返回一个值。函数返回 scalar_expression 表达式的值。
  • scalar_return_data_type:用户定义函数的返回类型,可以是 SQL Server 支持的基本标量类型,但 text 、nterxt 、image 和 timestamp 除外。

 

  根据上面的解释不妨来自定义一个函数,用来做一个统计 ——

统计学院学生的机试与笔试的平均成绩

假设已经有一个 exam 表,表中有 stuno 字段,便于查询。

written 和机试字段 lab ,且已经有记录存在。



1 create function getScore(@stuno varchar(20))
 2 returns  float as
 3 begin
 4     declare @Score float
 5     set @Score = -1;
 6     selecet @Score = (isnull(written,0)+isnull(lab,0))/2
 7     from exam
 8     where stuno = @stuno
 9     return @Score
10 end



 

   

  标量函数的调用:

  当调用用户定义函数的时候,必须要提供至少有两个部分组成的所有名称 (所有者.函数名)。调用用户定义函数的方法有两种:

1.在 SELECT 语句中调用 ——

  owner_name.function_name (@parameter_name1, 2 ……)

  参数可为赋值的局部变量或表达式,例如:调用用户定义函数 getScore.



select dbo.getScore('123456789');



 

  2.用 EXEC 语句执行

  用 T-SQL 的 execute 语句调用自定义函数的时候,实参的标识次序与函数定义中的参数标识次序可以不同,其具体调用形式为:

  owner_name.function_name @parameter_name1 , .... @parameter_name_n

  或

  owner_name.function_name @fparameter_name 1 = @aparameter_name1, .... @fparameter_name_n = @aparameter_name_n

  前者实参顺序应该要与函数定义的形参顺序一致,后者实参顺序可以与函数定义的形参书讯不一致。

  如果函数的参数有默认值,在调用该函数的时候必须指定 default 关键字才能获得默认值 , 这不同于存储过程中有默认值的参数,在存储过程中省略参数意味着使用默认值。