本篇包含两方面的内容：一个是学习掌握用户定义数据类型的特点及定义方法，以及如何使用用户定义数据类型；另一个是学习掌握用户自定义函数的概念、创建及使用方法。

用户定义数据类型

(1)为何使用用户定义数据类型

当一个或多个表的字段中要存储同样类型的数据，且想确保这些字段具有完全相同的数据类型、长度和是否为NULL时，可使用用户定义数据类型。

例如，学生号和教师号的数据类型都是基于CHAR的有固定长度6个字符，且不为空。

(2)用户定义数据类型的特点

用户定义的数据类型名称在数据库中必须是唯一的，但是名称不同的用户定义的数据类型可以有相同的定义。

(3)创建用户定义数据类型语句

sp_addtype [@typename = ] 类型,
           [@phystype = ] 系统定义的数据类型
           [, [@nulltype = 'null | not null']
           [, [@owner = '属主名']

2.用户定义函数

(1)为何使用用户定义函数

用户定义函数可以让用户针对特定应用程序问题提供解决方案。这些函数可以简单到计算一个值，也可以复杂到定义和实现业务处理规则。定义了这些函数以后，在需要的时候调用即可，如果业务处理的规则发生变化，只需要修改相应的函数，只要这些接口未发生变化，就可以保持原来的函数调用。

(2)用户定义函数的类型

SQL Server有2种自定义函数：表值函数、标量值函数。

用户定义函数采用零个或更多的输入参数并返回标量值或表。函数最多可以有1124个输人参数。当函数的参数有默认值时，调用该函数时必须指定默认DEFAULT关键字才能获取默认值。用户定义函数不支持输出参数。

• 标量函数。标量函数返回在RETURNS子句中定义的类型的单个数据值。返回类型可以是除text、 ntext、image、cursor 和 timestamp 之外的任何数据类型。不支持用户定义数据类型和非标量类型(如table或cursor)。
• 表值函数。表值函数返回table。对于内联表值函数，没有函数主体；表是单个SELECT语句的结果集。对于多语句表值函数，在 BEGIN…END语句块中定义的函数体包含一系列Transact-SQL语句，这些语句可生成行并将其插入将返回的表中。

(3)创建用户定义函数语句

• 标量函数

create function [属主名.] 函数名
  ([{@parameter_name [as] 标量参数数据类型 [ = default]} [, ...n]])
returns 标量返回数据类型
[with <function_option> [[, ]...n]]
[as]
begin
函数体
return 标量表达式
end

• 内嵌表值函数

create function [属主名.] 函数名
  ([{@parameter_name [as] 标量参数数据类型 [ = default]} [, ...n]])
returns table
[with <function_option> [[, ]...n]]
[as]
return [() select 语句]

• 多语句表值函数

create function [属主名.] 函数名
  ([{@parameter_name [as] 标量参数数据类型 [ = default]} [, ...n]])
returns @return_variable table <table_type_definition>
[with <function_option> [[, ]...n]]
[as]
begin
函数体
return
end
<function_option>::=
  {encryption | schemabinding}
<table_type_definition>::=
  ({column_definition | table_constraint} [, ...n])

创建和使用用户定义数据类型

 (1)用系统存储过程创建一用户定义的数据类型Idnum。要求：系统数据类型为char，长度为6，不为空。用于学号、教师号字段的数据类型。

exec sp_addtype Idnum, 'char(6)', 'not null'
go
create table student(SNO Idnum,
                     SN char(11),
		     SEX char(2))
go
create table teacher(TNO Idnum,
                     TN char(11),
		     SEX char(2))
go

单击工具栏中的执行按钮。在“对象资源管理器”中，展开“数据库”jxsk 的“可编程性”→“类型”→“用户定义的数据类型”节点，可以看到数据类型Idnum已经存在。

再展开表STUDENT和TEACHER,可以看到字段SNO和TNO都是用户定义的数据类型Idnum。

(2)交互式创建一用户定义的数据类型Nameperson。要求：系统数据类型char，长度为10，不为空。用于学生姓名、教师姓名字段的数据类型。

启动SQLServer Management Studio。展开“数据库”→jxsk→“可编程性”→“类型”节点，右击“用户定义的数据类型”节点，在打开的快捷菜单中选择“新建用户定义的数据类型”选项。打开“新建用户定义数据类型”对话框。

在名称输入栏中输入：Nameperson；数据类型下拉列表中选择：char；长度输入：10，NOT NULL。

单击“确定”按钮，保存用户定义数据类型Nameperson定义。

单击数据库 jxsk 中“可编程性”→“类型”→“用户定义数据类型对象”节点，查看右窗格中的内容，可以看到Nameperson定义。

使用用户定义的数据类型Nameperson。在查询编辑器窗口中输人下面程序，修改学生表STUDENT中的姓名类型为Nameperson和教师表中的教师姓名类型为Nameperson。

alter table student
  alter column SN Nameperson
go
alter table teacher
  alter column TN Nameperson
go

删除用户定义数据类型

(1)使用系统存储过程删除用户定义的数据类型Nameperson。

 打开查询编辑器窗口。在查询窗口中输人下面程序，先解除对用户定义的数据类型Nameperson的使用，然后再删除用户定义的数据类型 Nameperson。

alter table student
  alter column SN char(10) not null
go
alter table teacher
  alter column TN char(10) not null
go
exec sp_droptype Nameperson
go

(2)交互式删除用户定义的数据类型Idnum。

启动SQL Server Management Studio。先修改student和teacher表中SNO和TNO的数据类型。

展开数据库jxsk 中的“可编程性”→“类型”→“用户定义数据类型”对象节点。

右击Idnum，在打开的快捷菜单中，选择“删除”选项，弹出“删除对象”对话框，单击“确定”按钮，Idnum即被删除。

创建和使用用户定义的函数

(1)交互式创建一个标量值函数Score_FUN，根据学生姓名和课程名查询成绩。

启动SQL Server Management Studio。展开数据库jxsk，右击“可编程性”→“函数”→“标量值函数”节点，在打开的快捷菜单中，选择“新建标量值函数”选项，打开用户定义的函数的编辑窗口，其中包含模板语句。

将模板语句修改为下列SQL语句。

set ansi_nulls on
go
set quoted_identifier on
go
create function Score_FUN (@SNAME_IN char(8), @CNAME_IN char(10))
returns tinyint
as
begin
declare @SCORE_OUT tinyint
select @SCORE_OUT = SCORE from SC,S,C
  where S.SNO = SC.SNO and C.CNO = SC.CNO and
        SN = @SNAME_IN and CN = @CNAME_IN
return(@SCORE_OUT)
end
go

使用用户定义的函数Score_FUN，查询学生钱尔的编译原理课程的成绩。

declare @S_Score tinyint
exec @S_Score = Score_FUN '钱尔','编译原理'
print '钱尔的编译原理成绩是：'+ltrim(str(@S_Score))
go

(2)创建一个内嵌表值函数S_Score_FUN，根据学生姓名查询该生所有选课的成绩。

create function S_Score_FUN (@SNAME_IN char(8))
returns table
as
return(select CN, SCORE from S, C, SC
       where S.SNO = SC.SNO and C.CNO = SC.CNO and SN = @SNAME_IN)
go

select * from S_Score_FUN ('钱尔')
go

(3)创建一个多语句表值函数ALL_Score_FUN，根据课程名查询所有选择该课程学生的成绩信息，包括:学号SNO、姓名SN、性别SEX、成绩SCROE。结果按成绩降序排列。

create function ALL_Score_FUN (@CNAME_IN char(10))
returns @ALL_SCORE_TAB table (SNO char(2) primary key,
                              SN char(8) not null,
                              SEX char(2),
                              SCORE tinyint)
as
begin
insert into @ALL_SCORE_TAB
select S.SNO, SN, SEX, SCORE
from S, SC, C
where S.SNO = SC.SNO and C.CNO = SC.CNO and CN = @CNAME_IN
return
end
go

select * from ALL_Score_FUN ('微机原理')
go

修改用户定义的函数

(1)交互式修改函数Score_FUN，将成绩转换为等级输出。

启动SQL Server Management Studio。展开数据库jxsk，右击“可编程性”→“函数”→“标量值函数”节点，在弹出的快捷菜单中，选择“修改”选项，打开修改用户定义的函数的编辑窗口。修改窗口中 T-SQL内容为下列T-SQL语句。

alter function Score_FUN (@SNAME_IN char(8), @CNAME_IN char(10))
returns char(8)
as
begin
declare @SCORE_OUT char(8)
select @SCORE_OUT = case
                      when SCORE is null then '未考'
                      when SCORE < 60 then '不及格'
                      when SCORE >= 60 and SCORE < 70 then '及格'
                      when SCORE >= 70 and SCORE < 80 then '中'
                      when SCORE >= 80 and SCORE < 90 then '良好'
                      when SCORE >= 90 then '优秀'
		    end
from SC,S,C
where S.SNO = SC.SNO and C.CNO = SC.CNO and
      SN = @SNAME_IN and CN = @CNAME_IN
return(@SCORE_OUT)
end

打开查询编辑器窗口。在查询编辑器窗口中输入下列程序，使用用户定义的函数Score_FUN，查询学生钱尔的编译原理课程的成绩。

declare @S_Score char(8)
exec @S_Score = Score_FUN '钱尔','编译原理'
print '钱尔的编译原理成绩是：'+rtrim(@S_Score)
go

(2)用alter语句修改函数S_Score_FUN，要求增加一输出列对应成绩的等级。

alter function S_Score_FUN (@SNAME_IN char(8))
returns table
as
return(select CN, SCORE,
              level = case
                        when SCORE is null then '未考'
                        when SCORE < 60 then '不及格'
                        when SCORE >= 60 and SCORE < 70 then '及格'
                        when SCORE >= 70 and SCORE < 80 then '中'
                        when SCORE >= 80 and SCORE < 90 then '良好'
                        when SCORE >= 90 then '优秀'
		      end
       from S, C, SC
       where S.SNO = SC.SNO and C.CNO = SC.CNO and SN = @SNAME_IN)
go

select * from S_Score_FUN ('钱尔')
go

删除用户定义的函数

(1)交互式删除函数Score_FUN。

展开“数据库”→jxsk →“可编程性”→“函数”→“标量值函数”。

右击dbo.Score_FUN，在弹出的快捷菜单中，选择“删除”选项，弹出“删除对象”对话框，单击“确定”按钮，函数Score_FUN即被删除。

(2)用drop语句删除函数S_Score_FUN。

drop function S_Score_FUN
go