SQLServer数据库技术-09用户定义数据类型和函数

Exec sp_unbindefault ‘用户表.性别’

16
删除默认对象


Drop Default 默认对象名
例：Drop Default sex_default 注意：删除默认对象时，首先要解除默认 对象的绑定，然后才能删除默认对象
17
规则 RULE


规则也是一种数据对象，可以绑定到一列 或多列上，作用与Check约束相似，在插入 数据行时，指定接受的数据值的范围。 规则对象在功能上与Check约束是一样的， 在使用上有所区别：

INSERT 用户表(编号,姓名) VALUES (1,'刘智勇')
DECLARE @ID 编号 DECLARE @name nvarchar(10) SELECT top 1 @ID = 编号, @name = 姓名 FROM 用户表 ORDER BY 编号 DESC print '编号为：' + CAST(@ID AS varchar(2)) print '姓名为：' + @name
例：删除规则绑定 Exec sp_unbindrule ’Test.chengji’
21
删除规则对象


Drop Rule 规则对象
Drop Rule value_rule 注意：删除规则对象时，首先要解除规则 对象的绑定，然后才能删除规则对象
22
用户定义函数



对系统函数的补充 用户定义函数可以接受参数、执行操作并 将操作结果以值的形式返还 用户定义函数也可以使用在表达式、查询 或存储过程中
27
例：创建一个标量函数，返还当前时间
CREATE FUNCTION 例五() RETURNS datetime begin return getdate() end GO
PRINT dbo.例五()
注意：执行标量值函数必须要指明架构，而在执行表值函数时可以不用 指定。
28
例：创建一个函数，用于统计某年的销售总额 CREATE FUNCTION 例六(@年份 int) RETURNS money BEGIN DECLARE @销售总值 money SELECT @销售总值= SUM( [Order Details].UnitPrice *[Order Details].Quantity * (1-[Order Details].Discount)) FROM Orders JOIN [Order Details] ON Orders.OrderID = [Order Details].OrderID WHERE YEAR(OrderDate) = @年份 GROUP BY YEAR(OrderDate) RETURN @销售总值 END GO PRINT '1998年的销售总值为' + CAST(dbo.例六(1998) as varchar(20)) +'元' GO
3
创建UDT


可以创建的数据类型
除timestamp、XML外
创建方式
SQL Server Management Studio
存储过程
4
创建UDT
5
存储过程sp_addtype
sp_addtype [ @typename = ] type, [ @phystype = ] system_data_type [ , [ @nulltype = ] 'null_type' ] ;
30
创建内联表值函数
CREATE FUNCTION [ schema_name. ] function_name ( [ { @parameter_name [ AS ] [ type_schema_name. ] parameter_data_type [ = default ] } [ ,...n ] ] ) RETURNS TABLE [ WITH <function_option> [ ,...n ] ] [ AS ] RETURN [ ( ] select_stmt [ ) ] [;]
23
用户定义函数 vs 存储过程
用户定义函数 返还值 执行方式 存储过程 除了text、ntext、image、 只能返还整数值 cursor和timestamp类型 Execute执行 表达式（以返回值的方式 来取代函数名） 用户定义函数只可接受参 数，不能通过output类型 的参数返回值 用户定义函数只能对数据 进行修改，不能对数据库 对象进行修改 Execute执行
9
删除用户定义数据类型


能否删除
查看依赖关系
如何删除
SQL Server Management Studio
T-SQL ▪ DROP TYPE 用户定义数据类型名 ▪ Sp_droptype 用户定义数据类型名
10
示例
例：删除“编号”和“姓名”两个用户定义数据类型 由于在“用户表”数据表中字段定义中使用了“编号”和“姓名”两个 用户定义数据类型，所以在删除用户定义数据类型之前，需要首先删除 引用用户定义数据类型的数据字段（数据表） DROP TABLE 用户表 GO
认约束也被删除
默认对象作为一种单独存储的数据库对象，独立于表之
外（用Create Default定义），删除表时并不删除默认对 象（用Drop Default删除）
13
创建默认对象

T-SQL
Create Default 默认对象名 As 默认值


例：定义默认对象：设置默认值为男 Create Default sex_default as ‘男’
@typename:用户定义数据类型的名称 @phystype:用户定义数据类型所基于的物理
数据类型或SQL Server提供的数据类型 @nulltype:处理空值的方式：’NOT NULL’,’NULL’
6
示例
例：创建一个名为“编号”的用户定义数据类型，该数据类型为int型， 不能为空 EXEC sp_addtype 编号,'int','not null‘
Check约束是和表的定义联系在一起的，删除表
的同时Check约束也被删除
规则对象作为一种单独存储的数据库对象，独
立于表之外（用Create Rule定义），删除表时 并不删除规则对象（用Drop Rule删除）
18
创建Rule


Create Rule 规则名 As 规则条件
例：定义规则对象，设置值在0和100之间 Create Rule value_rule as @valuev>=0 and @valuev<=100
SQL Server数据库技术 用户定义数据类型和函数
1
用户定义数据类型和函数

用户定义数据类型
创建 使用
删除

用户定义函数
类型 创建 使用
删除
2
用户定义数据类型

为什么需要用户定义数据类型？（User Defined Data Type, UDT）
有些字段会在数据表里出现多次 ▪ 主键-外键 很多字段使用的数据类型相同 ▪ “供应商”、“雇员”、“客户”表的“地址”字段
19
绑定规则对象

Sp_bindrule [@rulename=] ‘规则对象名’, [@objname=]’绑定的列名’ 例：将规则对象value_rule绑定到成绩表的 成绩列
Exec sp_bindrule ‘value_rule’,’Test.chengji’


20
删除绑定


sp_unbindrule [@objname=] ‘绑定的列名’
29
例：创建一个函数，用于统计某个雇员的订单总数
CREATE FUNCTION 例七(@姓氏 varchar(20),@名字 varchar(10)) RETURNS int BEGIN DECLARE @订单总数 int SELECT @订单总数 = count(Orders.OrderID) FROM Orders JOIN Employees ON Orders.EmployeeID = Employees.EmployeeID WHERE stName = @姓氏 AND Employees.FirstName = @名字 RETURN @订单总数 END GO Select dbo.例七('王','伟') --Select dbo.例七('Davolio','Nancy') GO
返回值
可通过output参数返还数 据 存储过程中可以对任何数 据及对象进行修改，报考 新建或删除数据表、修改 数据库设置等
24
操作对象
用户定义函数类型

根据用户定义函数的返还值
标量值函数 ▪ 返还一个在return字句里定义的确定类型的标量值。 ▪ 返回值可以是除了text, ntext, image, cursor, timestamp和table之外的所有数据类型 ▪ 在多语句标量函数里面，函数体语句定义在begin和 end之内 表值函数 ▪ 表值函数返回的是table数据类型 ▪ 内联表值函数 ▪ 多语句表值函数
例：创建一个名为“姓名”的用户定义数据类型，该数据类型为 varchar(10)，可以为空 EXEC sp_addtype 姓名,'varchar(10)','null'
7
使用用户定义数据类型

Create table 用户表(编号 编号,姓名 姓名)