1 --==============================用户定义数据类型和函数===============================  2 --用户定义数据类型  3 EXEC sp_addtype 说明,‘nvarchar(500)‘,‘null‘  4   5 --删除用户定义数据类型  6 DROP TYPE 说明  7 --OR:  8 EXEC sp_droptype 说明  9  10 --标量值函数 11 --目标：返回某年份的订单总金额 12 CREATE FUNCTION 订单总额(@year int) 13 RETURNS money    --RETURNS + 返回值类型 14 BEGIN 15     DECLARE @total money 16     SELECT @total = SUM(b.单价*b.数量*(1-b.折扣)) 17     FROM 订单 a  18     JOIN 订单明细 b 19     ON a.订单ID = b.订单ID 20     WHERE YEAR(订购日期) = @year 21     GROUP BY YEAR(订购日期) 22  23     RETURN @total; 24 END 25 GO 26 --注意Schema不能丢 27 PRINT ‘1998年的订单总额为：‘+CONVERT(nvarchar(10),dbo.订单总额(1998)) 28  29 --内联表值函数 30 --目标：显示某时间段内的订单 31 CREATE FUNCTION 时间段订单(@beginDate datetime,@endDate datetime) 32 RETURNS TABLE 33 RETURN SELECT * FROM 订单 WHERE 订购日期 BETWEEN @beginDate AND @endDate 34 GO 35 SELECT * FROM 时间段订单(‘1996-7-1‘,‘1999-05-30‘) 36  37 --多语句表值函数 38 --目标：显示全部的人员，包括供应商、客户、运货商、雇员 39 CREATE FUNCTION 全部人员()    --就算没有参数，括号也不能少 40 RETURNS @名单 TABLE 41     ( 42         编号 int IDENTITY(1,1) NOT NULL Primary key, 43         姓名 nvarchar(30) NOT NULL, 44         地址 nvarchar(500) NULL, 45         邮编 varchar(20) NULL, 46         电话 varchar(20) NULL, 47         类别 nvarchar(10) NOT NULL 48     ) 49 BEGIN 50     INSERT INTO @名单 51     SELECT 联系人姓名,地址,邮政编码,电话,‘供应商‘ FROM 供应商 52  53     INSERT INTO @名单 54     SELECT 联系人姓名,地址,邮政编码,电话,‘客户‘ FROM 客户 55  56     INSERT INTO @名单 57     SELECT 公司名称,NULL,NULL,电话,‘运货商‘ FROM 运货商 58  59     INSERT INTO @名单 60     SELECT 姓氏+名字,地址,邮政编码,家庭电话+‘-‘+分机,‘雇员‘ FROM 雇员 61  62     RETURN 63 END 64 GO 65 SELECT * FROM 全部人员() 66  67  68 select 雇员ID from 订单 69 WHERE YEAR(订购日期) = 1998 70 group by 雇员ID 71 ORDER BY COUNT(订单ID) DESC 72  73 select * from 订单  74 WHERE YEAR(订购日期) = 1998 75 and 雇员ID = 5 76  77 --用户定义函数也可以用来设置默认值、约束、计算列等 78 --目标：新建一个表，将编号字段的默认值设为TCP-1,TCP-2,TCP-3... 79 --创建函数 80 CREATE FUNCTION 自增长编号() 81 RETURNS varchar(10) 82 BEGIN 83     DECLARE @编号 varchar(10) 84     DECLARE @id int 85     SELECT TOP 1 @编号 = 编号 FROM 自增长编号测试表 ORDER BY 编号 DESC 86     IF(@@ROWCOUNT = 0) 87         SET @编号 = ‘TCP-1‘ 88     ELSE 89         BEGIN 90             --获取编号的数字部分并+1 91             SET @id = SUBSTRING(@编号,5,LEN(@编号)-4) + 1 92             SET @编号 = ‘TCP-‘ + CONVERT(varchar(10),@id) 93         END 94     RETURN @编号 95 END 96 GO 97 --创建表 98 CREATE TABLE 自增长编号测试表 99 (100     编号 varchar(10) DEFAULT dbo.自增长编号() NOT NULL,101     名称 varchar(10) NULL102 )103 GO104 --插入数据105 INSERT INTO 自增长编号测试表(名称) VALUES(‘aaa‘)106 INSERT INTO 自增长编号测试表(名称) VALUES(‘bbb‘)107 INSERT INTO 自增长编号测试表(名称) VALUES(‘ccc‘)108 SELECT * FROM 自增长编号测试表109 110 --删除用户定义函数111 DROP FUNCTION 订单总额