4.2.1 创建自定义数据类型

4.2  使用自定义数据类型

创建自定义数据类型的方法非常简单，Transact-SQL为自定义数据类型的管理提供了两个存储过程，如表4.1所示。

表4.1  与自定义数据类型相关的存储过程

4.2.1  创建自定义数据类型
数据库管理员可以使用系统存储过程sp_addtype来创建自定义数据类型。该存储过程的使用格式如下所示：

sp_addtype [ @typename = ] type, 
    [ @phystype = ] system_data_type 
    [ , "identity" |[ @nulltype = ] 'null_type' ]

因为，该存储过程是一个系统存储过程，因此可以通过Microsoft SQL Server Management Studio来查看此存储过程使用的参数。打开Microsoft SQL Server Management Studio，依次展开【数据库】|【系统数据库】|master|【可编程性】|【存储过程】|【系统存储过程】节点，右击名为sp_addtype的存储过程，然后从弹出的快捷菜单中选择【修改】命令，在管理器的右侧打开存储过程的Transact-SQL脚本，该脚本的开始列出了存储过程的输入参数，如图4.1所示。

图4.1 存储过程的输入参数

sp_addtype存储过程使用的参数如表4.2所示。

表4.2  存储过程使用的参数

上述参数中，需要特别介绍一下参数@nulltype，该参数用于指定自定义数据类型处理空值的方式。即用户定义的数据类型可以为空NULL或不能为空NOT NULL或NONULL。如果没有在@nulltype参数中指定一个自定义函数的空属性处理方式，则使用当前的默认空属性。如果想了解当前的空属性，可以使用GETANSINULL函数。例如，可在【查询编辑器】中输入下面的SQL脚本查看数据库Northwind的空属性：

USE Northwind
PRINT GETANSINULL('Northwind')

单击【执行】按钮，执行上述脚本。返回的结果值为‘1’，其中‘1’表示空属性值为ANSINULL，即空值。此外，数据库管理员可以使用存储过程sp_dboption将默认空属性设置为允许为空。

【示例1】使用sp_addtype存储过程来创建用户自定义函数。

完整过程如下。

(1) 首先要为示例数据创建一个账户数据表Account，其中包含能够访问该数据表的账户名称，以及与它们相关的信息，例如登录密码、账户类型(例如，一般用户或管理员等)以及EML等，如表4.3所示。

表4.3  数据表Account中包含的字段

这里不采用传统数据表创建方法，而是先创建用户自定义数据类型，然后再使用自定义数据类型来创建数据表Account。首先在【查询编辑器】中输入下面的Transact-SQL脚本：

USE Northwind
EXEC SP_ADDTYPE AccountType,'VARCHAR(20)','NOT NULL'
EXEC SP_ADDTYPE PasswordType,'VARCHAR(10)','NOT NULL'
EXEC SP_ADDTYPE ClassType,'CHAR(1)','NOT NULL'
EXEC SP_ADDTYPE EMLType,'VARCHAR(20)','NULL'

(2) 单击【执行】按钮创建上述用户自定义数据类型。切换到管理控制台左侧的【对象资源管理器】，单击Northwind数据库中的【可编程性】节点，并单击【类型】节点下的【用户定义数据类型】，可以看到上述SQL语句生成的自定义数据类型，如图4.2所示。

图4.2 生成的自定义数据类型

(3) 接下来在【查询编辑器】中输入下面的Transact-SQL语句：

USE Northwind
CREATE TABLE Account
(AccountName AccountType,
 Password PasswordType,
 AccountType ClassType,
 EML EMLType)

(4) 单击【执行】按钮创建名为Account的数据表，单击数据库Northwind节点下的【表】节点，会发现上述Transact-SQL创建的数据表Account。展开该数据表Account节点，查看数据表所含字段，如图4.3所示。

图4.3 查看数据表所含字段

(5) 下面继续定义一个数值类型的自定义类型。在【查询编辑器】中输入以下SQL脚本，如下所示：

USE Northwind
GO
sp_addtype IDType, 'NUMERIC (4,0)','NONULL'
GO

(6) 上面的SQL脚本创建了一个名为IDType的自定义数据类型。IDType为一个长为4位的整数数据类型。继续在【查询编辑器】中输入下面的SQL脚本，重新创建数据表Account：

USE Northwind
DROP TABLE Account
GO
CREATE TABLE Account
(
   Accountid IDType IDENTITY,
   AccountName AccountType,
   Password PasswordType,
   AccountType ClassType,
   EML EMLType
)
GO

(7) 按照上面介绍的方法查看数据表Account，会发现该数据表中多了一个名为Accountid的字段，该字段被用于标识账号记录的序号。右击数据表Account，从弹出的快捷菜单中选择【修改】命令，查看该数据表的【属性】对话框。该对话框中的【表设计器】选项中显示该数据表的【标识列】为Accountid，如图4.4所示。

硕图4.4 显示该数据表的【标识列】为Accountid

试一试

除了可以使用Transact-SQL创建一个自定义数据类型之外，也可以使用Microsoft SQL Server Management Studio创建一个自定义数据类型。使用Microsoft SQL Server Management Studio创建一个自定义数据类型的步骤如下所示。

(1) 单击当前数据库(本示例中为Northwind)节点下的【类型】节点。然后右击该节点下的【用户定义数据类型】节点，从弹出的快捷菜单中选择【新建用户定义数据类型】命令，如图4.5所示。

图4.5 选择【新建用户定义数据类型】命令

(2) 选择【新建用户定义数据类型】命令，打开【新建用户定义数据类型】对话框，可以在该对话框中创建自己所需的数据类型。例如，通过该对话框创建数据类型IDType的方法如图4.6所示。

图4.6 创建数据类型IDType