T-SQL编程 —— 用户自定义函数(标量函数)

用户自定义函数

---

 

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

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

　　如果用户定义函数包含了单个select语句且语句可更新，则该函数返回的表也可更新，这样的函数称为内嵌表值函数。

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

　　如果用户定义函数包含多个select语句，则该函数返回的表不可更新。这样的函数称为多语句表值函数。

• 返回标量值的函数

　　用户定义函数返回值为标量值，这样的函数称为标量函数。

---

 

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

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

 

标量函数的定义与调用

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

 create function [owner_name] function_name
 ([{@parameter_name [as] scalar_parameter_date_type [=default]}[,…n]])
 returns scalar_return_data_type [with encryption] [as]
 begin
     function_body
     return scalar_expression
 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 ，且已经有记录存在。

create function getScore(@stuno varchar(20))
returns  float as
begin
    declare @Score float
    set @Score = -1;
    selecet @Score = (isnull(written,0)+isnull(lab,0))/2
    from exam
    where stuno = @stuno
    return @Score
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 关键字才能获得默认值 ， 这不同于存储过程中有默认值的参数，在存储过程中省略参数意味着使用默认值。