欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

SQL 中用户定义函数的使用方法

程序员文章站 2024-01-09 19:00:40
--用户定义函数的分类: /* 1.标量函数 2.表值函数 2.1内联表值函数 返回单个SELECT语句, 它没有相关的返回变量和函数体 2.2多语句表值函数 是视图和存储过程的结合 可嵌套 */ 执行: 结果: 执行: 结果: 多语句表值函数 执行: 结果: ......

--用户定义函数的分类:

/*
1.标量函数
2.表值函数
  2.1内联表值函数  返回单个SELECT语句, 它没有相关的返回变量和函数体
  2.2多语句表值函数  是视图和存储过程的结合 可嵌套

*/

标量函数
--标量函数
IF EXISTS(SELECT * FROM sysobjects    WHERE name='fun_SeeEverySortAmount')
    DROP FUNCTION fun_SeeEverySortAmount
GO
CREATE FUNCTION fun_SeeEverySortAmount(@class varchar(20))
RETURNS INT
AS
    BEGIN
        DECLARE @Amount int
        SELECT @Amount=Amount FROM CommoditySort C INNER JOIN CommodityInfo I ON C.SortId=I.SortId
        WHERE I.CommodityName=@class
        RETURN @Amount
    END
GO

执行:

--执行
DECLARE @Amount int
EXEC @Amount= fun_SeeEverySortAmount '苹果iPhone6'
PRINT '苹果iPhone6的库存量为:'+CONVERT(varchar(20),@Amount)

结果:

SQL 中用户定义函数的使用方法

内联表值函数
--内联表值函数 返回单个SELECT语句, 它没有相关的返回变量和函数体
IF EXISTS (SELECT * FROM sysobjects WHERE name='fun_inlineSeeEverySortAmount')
    DROP FUNCTION fun_inlineSeeEverySortAmount
GO
CREATE FUNCTION fun_inlineSeeEverySortAmount(@class varchar(20))
RETURNS table
AS
    RETURN
        (
            SELECT Amount FROM CommodityInfo I 
            WHERE I.CommodityName=@class
        )
GO

执行:

--执行 调用内联函数和视图的使用一样
SELECT Amount 数量 FROM fun_inlineSeeEverySortAmount('苹果iPhone6')

结果:

SQL 中用户定义函数的使用方法

多语句表值函数

--多语句表值函数  是视图和存储过程的结合 可嵌套
--如果一件商品的销售数量超过3000则为销量品,1000-3000为一般商品,0-1000 为需要促销
IF EXISTS (SELECT * FROM sysobjects WHERE name='fun_MutilateStatementSalesSort')
    DROP FUNCTION fun_MutilateStatementSalesSort
GO
CREATE FUNCTION fun_MutilateStatementSalesSort(@class varchar(20))
RETURNS @salessort table(
            商品名 varchar(20),
            单价 money,
            商品类型 varchar(20),
            销售量     int,
            商品销售类型 varchar(20)
        )
AS
    BEGIN
        INSERT INTO @salessort
        SELECT I.CommodityName,I.InPrice,S.SortName,SUM(O.Amount),CASE
                                                                WHEN SUM(O.Amount)>3000 THEN '销量品'
                                                                WHEN SUM(O.Amount) BETWEEN 1000 AND 3000 THEN '一般商品'
                                                                WHEN SUM(O.Amount) BETWEEN 0 AND 1000 THEN '需要促销'
                                                            END
        FROM CommodityInfo I INNER JOIN CommoditySort S ON I.SortId=S.SortId
                            INNER JOIN OrderInfo O ON O.CommodityId =I.CommodityId
        WHERE I.CommodityName=@class
        GROUP BY I.CommodityName,I.InPrice,S.SortName
        RETURN
    END
GO

执行:

--执行 它相当于一个表
SELECT* FROM fun_MutilateStatementSalesSort('苹果MD760')
SELECT* FROM fun_MutilateStatementSalesSort('尼康D3300') 
SELECT* FROM fun_MutilateStatementSalesSort('小米平板')

结果:

SQL 中用户定义函数的使用方法