第六章 用户定义数据类型与自定义函数 6.1 用户定义数据类型简介nSQL SERVER2000允许用户自己定义数据类型，用户定义的数据类型基于 SQL Server 2000中的系统数据类型。在创建了用户数据类型之后，可以在 CREATE TABLE 或 ALTER TABLE 中使用它，也可以将默认值和规则绑定到用户定义的数据类型。6.1.1 为何使用用户定义数据类型 n当一个或多个表的字段中要存储同样类型的数据，且想确保这些字段具有完全相同的数据类型、长度、为空属性时，可使用用户定义数据类型。例如，可以基于 char 数据类型创建名为 post_code 的用户定义数据类型，该数据类型长度固定为6个字符。6.1.2 用户定义数据类型的特点n用户定义的数据类型名称在数据库中必须是唯一的，但是名称不同的用户定义的数据类型可以有相同的定义。n注意： 不能使用 SQL Server 的timestamp 数据类型来定义用户定义的数据类型。6.2 创建用户定义数据类型n在SQL SERVER中，用系统存储过程sp_addtype来创建用户定义数据类型，语法如下：sp_addtype typename = type, phystype = system_data_type , nulltype = null_type , owner = owner_name 6.2.1 参数type n该参数表示用户定义的数据类型的名称。数据类型名称必须遵照标识符的规则，而且在每个数据库中必须是唯一的。type 的数据类型为 sysname，没有默认值。6.2.2 参数system_data_type n该参数是用户定义的数据类型所基于的SQL Server 提供的数据类型（decimal、int 等等）。system_data_type 的数据类型是 sysname，没有默认值。 n如果参数中嵌入有空格或标点符号，则必须用引号将该参数引起来。有关可用的数据类型的更多信息，请参见数据类型。 6.2.3 参数null_type n指明用户定义的数据类型处理空值的方式。null_type 的数据类型为 varchar(8)，默认值为 NULL，并且必须用单引号引起来（NULL、NOT NULL 或 NONULL）。如果没有用 sp_addtype 显式定义 null_type，则将其设置为当前默认的为空性。使用 GETANSINULL 系统函数可确定当前默认的为空性，可以使用 SET 语句或 sp_dboption 对该为空性进行调整。应显式定义为空性。 nnull_type 参数只为该数据类型定义默认的为空性。如果在创建表的过程中使用用户定义的数据类型时显式地定义了为空性，那么该为空性优先于已定义好的为空性。 6.2.4 参数owner_namen指定新数据类型的创建者或所有者。owner_name 的数据类型为 sysname。当没有指定时，owner_name 为当前用户。6.2.5 用户定义数据类型范例n例程例程6.1：创建一个数据类型合同签定日期SignDate，基于日期型数据Datatime，要求数据不可空，代码如下： EXEC sp_addtype SignDate, datetime, NOT NULLn例程例程6.2：创建一个数据类型邮政编码PostCode，基于字符型Varchar创建，要求最大数据长度为6位，可以为空，代码如下： EXEC sp_addtype PostCode, Varchar(6), NULL6.3 删除用户定义数据类型n删除用户定义数据类型，用系统存储过程sp_droptype，语法如下： sp_droptype typename = typen如果表或其它数据库对象引用 type 用户定义数据类型，那么就不能除去这种数据类型。n例程例程6.3：删除用户创建的数据类型SignDate，代码如下： EXEC sp_droptype SignDate6.4 更改用户定义数据类型 n可以对用户定义的数据类型改名，使用系统存储过程sp_rename，系统存储过程sp_rename不光可以更改用户定义的数据类型的名称，也可以更改表、视图、列、存储过程、触发器、默认值、数据库、对象或规则的名称，语法如下：sp_rename objname = object_name , newname = new_name , objtype = object_type 其中的object_type就是重命名的对象的类型。n例程例程6.4：更改用户定义的数据类型SignDate为ContractDate，代码如下：EXEC sp_rename SignDate,ContractDate, USERDATATYPE6.5 使用数据类型n在创建了用户数据类型之后，可以在 CREATE TABLE 或 ALTER TABLE 中使用它， 例如在创建了一个ContractDate的数据类型之后，就可以在创建Contract表中使用，SQL语句如下：EXEC sp_addtype ContractDate, datetime, NOT NULLCREATE TABLE Contract(ContractIDvarchar(10),ContractNamevarchar(30),SignDatecontractdate NULLn注意：此处定义了一个默认为不可空的数据类型，在引用时如果显示地标明为NULL，则说明该数据实际为可以空。6.6 用户定义函数简介nSQL SERVER2000除了内置一些常用的函数外，还允许用户创建自己的函数，它其实是由一个或多个 Transact-SQL 语句组成的子程序。6.6.1 为何使用用户定义函数n用户自定义函数在实际编程中用处相当大，可以让你针对特定应用程序问题提供解决方案。这些函数可以简单到计算一个值，也可能复杂到定义和实现数据表的约束。定义了这些函数以后，在需要的时候调用即可，如果业务处理规则发生变化，只需要修改相应的函数，只要函数接口未发生变化，就可以保持原来的函数调用。 6.6.2 用户定义函数的特点 SQL SERVER2000有三种自定义函数：n标量函数 返回在returns子句中定义的类型的单个数据值。n内嵌表值函数 表值函数返回table，表是单个select语句的结果集，该函数没有函数主体n多语句表值函数 在BEGINEND块中定义的函数主体包含T-SQL语句，这些语句可生成行并将行插入将返回的表中。 6.6.2 用户定义函数的特点函数中的有效语句类型包括： nDECLARE 语句，该语句可用于定义函数局部的数据变量和游标。n为函数局部对象赋值，如使用 SET 给标量和表局部变量赋值。n游标操作，该操作引用在函数中声明、打开、关闭和释放的局部游标。不允许使用 FETCH 语句将数据返回到客户端。仅允许使用 FETCH 语句通过 INTO 子句给局部变量赋值。n控制流语句。nSELECT 语句，该语句包含带有表达式的选择列表，其中的表达式将值赋予函数的局部变量nINSERT、UPDATE 和 DELETE 语句，这些语句修改函数的局部 table 变量。nEXECUTE 语句，该语句调用扩展存储过程。6.7 创建用户定义函数nSQL Server 2000 支持三种用户定义函数：标量函数、内嵌表值函数、多语句表值函数。标量函数返回在 RETURNS 子句中定义的类型的单个数据值。表值函数包括内嵌表值函数和多语句表值函数，总是返回一个表。对于内嵌表值函数，没有函数主体；表是单个 SELECT 语句的结果集。对于多语句表值函数，在 BEGIN.END 块中定义的函数主体包含 TRANSACT-SQL 语句，这些语句可生成行并将行插入要返回的表中。6.7.1 标量函数n创建标量函数的语法如下：CREATE FUNCTION owner_name. function_name ( parameter_name AS scalar_parameter_data_type = default ,.n ) RETURNS scalar_return_data_type WITH , .n AS BEGIN function_body RETURN scalar_expressionEND6.7.1 标量函数nowner_name拥有该用户定义函数的用户ID的名称。nfunction_name用户定义函数的名称，符合命名规则，在库中唯一nparameter_name用户定义函数的参数，1到多个nscalar_parameter_data_type 参数的数据类型nscalar_return_data_type 函数返回值n function_body 指定一系列T-SQL语句定义函数的值6.7.1 标量函数n例程例程6.5：创建一个标量函数，在一个字符串中用字符串1来替换所有的字符串2，并且把结果反转，显示为大写字符结果。CREATE FUNCTION NoOfChar (char1 varchar,char2 varchar,char3 varchar)RETURNS varcharASBEGINDECLARE newchar varchar set newchar=REPLACE(char1,char2,char3)set newchar=REVERSE(newchar)set newchar=UPPER(newchar)RETURN (newchar)END6.7.2 内嵌表值函数n创建语法如下：CREATE FUNCTION owner_name. function_name ( parameter_name AS scalar_parameter_data_type = default ,.n ) RETURNS TABLE WITH , .n AS RETURN ( ) select-stmt 6.7.2 内嵌表值函数n例程例程6.6：创建一个内嵌表值函数，返回指定商品的库存情况。CREATE FUNCTION ProductStore (productid varchar(30)RETURNS TABLEASRETURN (SELECT productid,amount,volume FROM Storage WHERE Productid=productid)6.7.3 多语句表值函数n创建语法如下：CREATE FUNCTION owner_name. function_name ( parameter_name AS scalar_parameter_data_type = default ,.n ) RETURNS return_variable TABLE WITH , .n AS BEGIN function_body RETURNEND := ENCRYPTION | SCHEMABINDING := ( column_definition | table_constraint ,.n ) 6.7.3 多语句表值函数n例程例程6.7：假设公司表的层次关系如下： nCREATE TABLE Company(CompanyID nchar(5) PRIMARY KEY, CompanyName nvarchar(50), GenCompanyID nchar(5), -引用CompanyID Remark nvarchar(30) )其中的GenCompanyID代表一个公司的直接母公司的代码。表值函数 get_Company(InCompanyID) 有一个给定的CompanyID，它返回该公司所有的下属单位，包括直接下属与间接下属单位。该逻辑无法在单个查询中表现出来，可以用用户定义函数来实现。 6.7.3 多语句表值函数CREATE FUNCTION get_Company (InCompanyID nchar(5)RETURNS returnFind TABLE (CompanyID nchar(5) primary key, CompanyName nvarchar(50) NOT NULL, GenCompanyID nchar(5), Remark nvarchar(30)ASBEGIN DECLARE RowsAdded int DECLARE reports TABLE (CompanyID nchar(5) primary key, CompanyName nvarchar(50) NOT NULL, GenCompanyID nchar(5), Remark nvarchar(30), processed tinyint default 0) INSERT reports SELECT CompanyID, CompanyName, GenCompanyID, Remark, 0 FROM Company WHERE CompanyID = InCompanyID 6.7.3 多语句表值函数SET RowsAdded = rowcount WHILE RowsAdded 0 BEGIN UPDATE reports SET processed = 1 WHERE processed = 0 INSERT reports SELECT c.CompanyID, c.CompanyName, c.GenCompanyID, c.Remark, 0 FROM Company c, reports r WHERE c.GenCompanyID=r.CompanyID and c.GenCompanyID c.CompanyID and r.processed = 1 SET RowsAdded = rowcount UPDATE reportsSET processed = 2 WHERE processed = 1 END INSERT returnFind SELECT CompanyID, CompanyName, GenCompanyID, Remark FROM reports RETURNEND调用该函数方法如下：SELECT * FROM get_Company (00011)6.8 修改和删除用户定义函数 n可以用ALTER FUNCTION来修改对用户定义函数的定义，但是不能更改函数的类型，即不能将标量值函数更改为表值函数，反之亦然。同样地，也不能将内嵌表值函数更改为多语句函数，反之亦然。n用DROP FUNCTION来删除一个或多个用户定义函数。6.8 修改和删除用户定义函数 n例程例程6.8：创建一个内嵌表值函数，返回指定商品的库存情况，返回列：商品代码、库存数量、库存金额，然后更改内嵌表值函数的定义，使其返回列为：商品代码、库存单价CREATE FUNCTION ProductStore (productid varchar(30)RETURNS TABLEASRETURN (SELECT productid,amount,volumeFROM StorageWHERE ProductID = productid)6.8 修改和删除用户定义函数 n例程例程6.9修改函数的定义ALTER FUNCTION ProductStore (productid varchar(30)RETURNS TABLEASRETURN (SELECT productid,priceFROM StorageWHERE ProductID = productid)n例程例程6.10：删除自定义函数ProductStore。DROP FUNCTION ProductStore6.9 用户定义函数的限制 n用户定义函数提供了解决比较特殊的问题的办法，但是它们的使用有一定的限制。最大的局限就是应用范围。和系统内置函数不同，服务器上所有的数据库都可以使用内置函数，但是用户定义函数则只能在当前的数据库中可用。n注意：用户定义函数不能包含内置的、确定性的函数。函数的确定性：函数可以是确定的或不确定的。如果任何时候用一组特定的输入值调用函数时返回的结果总是相同的，则这些函数为确定的。如果每次调用函数时即使用的是相同的一组特定输入值，返回的结果总是不同的，则这些函数为不确定的。n例如在用户定义函数内部不能使用getdata函数，因为getdata是不确定函数，每次调用时将返回不同的结果。