SqlCollections

--==============================用户定义数据类型和函数===============================
--用户定义数据类型
EXEC sp_addtype 说明,'nvarchar(500)','null'

--删除用户定义数据类型
DROP TYPE 说明
--OR:
EXEC sp_droptype 说明

--标量值函数
--目标：返回某年份的订单总金额
CREATE FUNCTION 订单总额(@year int)
RETURNS money    --RETURNS + 返回值类型
BEGIN
    DECLARE @total money
    SELECT @total = SUM(b.单价*b.数量*(1-b.折扣))
    FROM 订单 a 
    JOIN 订单明细 b
    ON a.订单ID = b.订单ID
    WHERE YEAR(订购日期) = @year
    GROUP BY YEAR(订购日期)

    RETURN @total;
END
GO
--注意Schema不能丢
PRINT '1998年的订单总额为：'+CONVERT(nvarchar(10),dbo.订单总额(1998))

--内联表值函数
--目标：显示某时间段内的订单
CREATE FUNCTION 时间段订单(@beginDate datetime,@endDate datetime)
RETURNS TABLE
RETURN SELECT * FROM 订单 WHERE 订购日期 BETWEEN @beginDate AND @endDate
GO
SELECT * FROM 时间段订单('1996-7-1','1999-05-30')

--多语句表值函数
--目标：显示全部的人员，包括供应商、客户、运货商、雇员
CREATE FUNCTION 全部人员()    --就算没有参数，括号也不能少
RETURNS @名单 TABLE
    (
        编号 int IDENTITY(1,1) NOT NULL Primary key,
        姓名 nvarchar(30) NOT NULL,
        地址 nvarchar(500) NULL,
        邮编 varchar(20) NULL,
        电话 varchar(20) NULL,
        类别 nvarchar(10) NOT NULL
    )
BEGIN
    INSERT INTO @名单
    SELECT 联系人姓名,地址,邮政编码,电话,'供应商' FROM 供应商

    INSERT INTO @名单
    SELECT 联系人姓名,地址,邮政编码,电话,'客户' FROM 客户

    INSERT INTO @名单
    SELECT 公司名称,NULL,NULL,电话,'运货商' FROM 运货商

    INSERT INTO @名单
    SELECT 姓氏+名字,地址,邮政编码,家庭电话+'-'+分机,'雇员' FROM 雇员

    RETURN
END
GO
SELECT * FROM 全部人员()


select 雇员ID from 订单
WHERE YEAR(订购日期) = 1998
group by 雇员ID
ORDER BY COUNT(订单ID) DESC

select * from 订单 
WHERE YEAR(订购日期) = 1998
and 雇员ID = 5

--用户定义函数也可以用来设置默认值、约束、计算列等
--目标：新建一个表，将编号字段的默认值设为TCP-1,TCP-2,TCP-3...
--创建函数
CREATE FUNCTION 自增长编号()
RETURNS varchar(10)
BEGIN
    DECLARE @编号 varchar(10)
    DECLARE @id int
    SELECT TOP 1 @编号 = 编号 FROM 自增长编号测试表 ORDER BY 编号 DESC
    IF(@@ROWCOUNT = 0)
        SET @编号 = 'TCP-1'
    ELSE
        BEGIN
            --获取编号的数字部分并+1
            SET @id = SUBSTRING(@编号,5,LEN(@编号)-4) + 1
            SET @编号 = 'TCP-' + CONVERT(varchar(10),@id)
        END
    RETURN @编号
END
GO
--创建表
CREATE TABLE 自增长编号测试表
(
    编号 varchar(10) DEFAULT dbo.自增长编号() NOT NULL,
    名称 varchar(10) NULL
)
GO
--插入数据
INSERT INTO 自增长编号测试表(名称) VALUES('aaa')
INSERT INTO 自增长编号测试表(名称) VALUES('bbb')
INSERT INTO 自增长编号测试表(名称) VALUES('ccc')
SELECT * FROM 自增长编号测试表

--删除用户定义函数
DROP FUNCTION 订单总额