Transact-SQL 程序设计（二） --------自定义函数

这篇文章主要介绍下Transact-SQL自定义函数的情况，你可以先查看下之前的文章

Transact-SQL 程序设计（一） ——–变量和表达式

Transact-SQL 函数

函数是一组编译好的Transact-SQL语句,它们可以带一个或者一组数据作为参数,返回一个数值,数值集合,或执行一些操作.

用户自定义SQL函数

可以拥有一个或者多个参数,但是必须返回单一的值,返回的值可以是单个数值,也可以是一个表

• 标量函数,返回一个确定类型的标量值,其函数值类型为系统的数据类型,函数体语句定义在BEGIN–END语句内.

create function data2quarter(@dqdate datetime)
returns char(6)
as 
begin
    return (datename(q,@dqdate) + 'Q'+ datename(yyyy,@dqdate)) 
end


-- 自定义函数的调用
-- 需要指明函数所有者
select dbo.data2quarter(getdate());
-- 2Q2016

• 内嵌表值函数，返回的函数值为一个表。内嵌表值函数不适用BEGIN–END语句，其返回的表是RETURN子句中的SELECT命令查询的结果集

-- 返回输入商品编号的商品名称和库存量
create function goodsq(@good_id varchar(30))
returns TABLE -- 此处表明是内嵌表值函数
as
return 
(select goods_name,stock_quantity from goods
 where goods_id = @good_id)

 -- 需使用查询表的形式操作返回值
 select * from dbo.goodsq('G00002');
/*
    goods_name  stock_quantity
    旭日 160-D1.7G    5
*/

• 多语句表值函数，可以看成标量函数和内嵌表值函数的结合体，其函数值也是一个表，但是函数体使用BEGIN–END进行定义，返回值的表中数据由函数体的语句插入。

-- 根据订单编号，返回对应的商品编号
 create function good_info(@in_o_id varchar(10))
 returns @goodinfo TABLE 
 -- 此处定义返回表的类型
 (
    o_id char(6),
    g_id char(6),
    g_name varchar(50),
    c_id char(6),
    c_name varchar(20)
 )
 as 
 begin
     declare @g_id varchar(10),@g_name varchar(30)
     declare @c_id varchar(10),@c_name varchar(30)
     select @g_id=goods_id from sell_order
     where order_id1 = @in_o_id

     select @g_name = goods_name,@c_id=classification_id
     from goods
     where goods_id = @g_id

     select @c_name = classification_name
     from goods_classification
     where @c_id = classification_id

     -- 将查询的值插入到表中
     insert @goodinfo
     values(@in_o_id, @g_id, @g_name, @c_id, @c_name)
     return 
 end



 -- 进行查询
 select * from  dbo.good_info('S00002');
/*
    o_id    g_id    g_name  c_id    c_name
    S00002  G00003  NEC S3000   P001    笔记本计算机
*/

修改和删除自定义函数

1. 使用ALTER FUNCTION 修改函数，与CREATE FUNCTION用法类似

2. 使用DROP FUNCTION funciton_name 删除函数

drop function date2quarter