数据库后台编程技术(二)

二：用户定义函数

SQL Server 2008 支持两类用户定义函数：标量函数；表值函数。

2.1：创建和调用标量函数

标量函数是返回单个数据值的函数

2.1.1：定义标量函数

CREATE FUNCTION function_name
(
   @parameter_name data_type
)
RETURNS return_data_type
AS
BEGIN
     funcytion_body
   RETURN scalar_expression
END

例 1：创建计算立方体体积的标量函数，此函数有三个输入参数，分别为立方体的长，宽和高，类型均为整形，函数的返回值的类型也为整形

CREATE FUNCTION dbo.CubicVolume
   (@CubeLength int, @CubeWidth int, @CubeHeight int)
RETURNS int
AS
BEGIN
   RETURN(@CubeLength*@CubeWidth*@CubeHeight)
END

例 2：创建查询指定商品种类数的标量函数

CREATE FUNCTION dbo.f_GoodsCount(@class varchar(10))
   RETURNS int 
   BEGUIN
      DECLARE @x int
      SELECT @x = count(*) FROM Table_GoodsClass a JOIN Table_Goods b
        ON a.GoodsCLassID = b.GoodsClassID
        WHERE GoodsClassName = @class
      RETURNS @x
    END

2.1.2：调用标量函数
①：调用例1定义的函数，计算长，宽，高分别为4，6，8的立方体体积的SQL语句为：

SELECT dbo.CubicVolume(4,6,8)

②：调用例2定义的函数，查询“服装”类商品的商品名和种类数的SQL语句为：

SELECT GoodsName AS 商品名, dbo.f_GOodsCount('服装') AS 种类数
   FROM Table_GoodsClass a JOIN Table_Goods b
   ON a.GoodsClassID = b.GoodsClassID
   WHERE GoodsClassName = '服装'

2.2：创建和调用内联表值函数

内联表值函数的返回值是一个表，该表的内容是一个查询语句的结果

2.2.1：创建内联表值函数

CREATE FUNCTION function_name
   (@parameter_name data_type)
RETURNS table
AS
RETURN(select_statement)

例 1：创建查询指定类别的商品名称和单价的内联表函数

CREATE FUNCTION f_GoodsInfo(@class char(10))
   RETURNS table
AS
   RETURN(
           SELECT GoddsName, SaleUnitPrice, FROM Table_GoodsClass a
             JOIN Table_Goods b ON a.GoodsClassID = b.GoodsClassID
             WHERE GOodsClassName = @class
              )

2.2.2：调用内联表值函数

对例1定义的内联表函数，查询“服装”类的商品信息的SQL语句为：

SELECT * FROM dbo.f_GoodsInfo('服装')

2.3：创建和调用多语句表值函数

2.3.1：创建多语句表值函数

CREATE FUNCTION function_name
               (parameter_name data_type)
  RETURNS @teturns_variable TABLE<table_type_definition>
AS
  BEGIN
     function_body
     RETURN
END

例 1：定义查询指令类别的商品的名称，单价生产日期和新旧商品的多语句表值函数，其中新旧商品的值为：如果到目前为止生产月数超过12个月，则为“旧商品”；若生产月数在6~12个月，则为“一般商品”；若生产月数小于6个月，则为“新商品”。

CREATE FUNCTION f_GoodsType(@ class varchar(20))
  RETURNS @ f_GoodsType table(
                               商品名         varchar(50),
                               单价           money,
                               生产日期       datetime,
                               类型           varchar(10),
                             )
AS
  BEGIN
  INSERT INTO @ f_GoodsType
    SELECT GoodsName, SaleUnitPrice, ProductionDate, CASE
      WHEN datediff(month,ProductionDate,'2007/2/10')>12
               THEN'新商品'
       END
    FROM Table_GoodsClass a JOIN Table_Goods b
    ON a.GoodsClassID = b.GoodsClassID
    WHERE GoodsClassName = @ class
RETURN
END

2.4：删除用户自定义函数

  DROP FUNCTION function_name

例

DROP FUNCTION f_GoodsType