T-SQL 自定义函数

 

初始化创建一个student表

 

sno sname ssex sage sdept 1001 张三 男 20 C1 1002 李四 男 22 C2 1003 李莉 女 20 C3 1004 陈旭 女 22 C1 1005 陈宇 男 23 C3 1006 王五 男 21 C1 1007 张莉莉 女 20 C2 1008 王晓 女 23 C3 1009 张斌 男 22 C1 1010 张淑芳 女 20 C2 1011 李天才 男 21 C3

 

1).创建标量函数的语法格式

 

 

CREATE FUNCTION [ schema_name. ] function_name 
( [ { @parameter_name [ AS ][ type_schema_name. ] parameter_data_type 
    [ = default ] } 
    [ ,...n ]
  ]
)
RETURNS return_data_type
    [ WITH <function_option> [ ,...n ] ]
    [ AS ]
    BEGIN 
                function_body 
        RETURN scalar_expression
    END
[ ; ]

 

 

其中参数说明如下：

   （1）function_name ：用户自定义函数的名称，该名称在数据库中必须唯一。

   （2）@parameter_name ：用户自定义的参数，如果函数的参数有默认值，在调用该函数是必须指定“DEFAULT”关键字才能获得默认值。

   （3）parameter_data_type ：参数的l数据类型。

   （4）return_data_type：是标量用户自定义函数的返回值类型。

   （5） function_body ：有一系列T-SQL语句组成的函数体。

   （6）scalar_expression：指定标量返回的数量值。

   【例】 统计某个系的平均年龄

 

 

 CREATE FUNCTION [dbo].[stu_info](@sdept varchar(10))
        RETURNS float
        AS 
         BEGIN 
           declare @sage float
           set @sage= (select avg(sage) from student where sdept=@sdept  group by sdept)
           RETURN @sage
        END
  ------------------------------------
SELECT dbo.stu_info('C1') as age

 

age

21

 

 2）创建直接表值函数语法格式 

 

 

CREATE FUNCTION [ schema_name. ] function_name 
( [ { @parameter_name [ AS ][ type_schema_name. ] parameter_data_type 
    [ = default ] } 
    [ ,...n ]
  ]
)
RETURNS TABLE
    [ WITH <function_option> [ ,...n ] ]
    [ AS ]
        RETURN (select_statement[])
   [ ; ]

 

 

参数说明如下：

 （1）TABLE：指定返回值为一个表。

 （2）select_statement：单个select语句确定返回的表的数据。

  【例】返回系别为C1的学生信息

 

 

CREATE FUNCTION stu_info(@sdept varchar(10))
RETURNS TABLE 
AS 
RETURN(SELECT * FROM dbo.student WHERE sdept=@sdept)
------------------------------
SELECT * FROM dbo.stu_info('C1')

 

 

 

 

3）创建多语句表值函数语法格式 

 

CREATE FUNCTION [ schema_name. ] function_name 
( [ { @parameter_name [ AS ][ type_schema_name. ] parameter_data_type 
    [ = default ] } 
    [ ,...n ]
  ]
)
RETURNS @return_variable TABLE   <ftable_type_definination> 
     [ WITH <function_option> [ ,...n ] ]
    [ AS ]
    BEGIN 
                function_body 
        RETURN scalar_expression
    END
[ ; ]

 

参数说明如下：

 

 （1）@return_variable：一个TABLE类型的变量用于存储和累积返回的表中的数据行。

【例】 查询年龄大于22的学生信息

 

CREATE FUNCTION stu_hage(@age int)
RETURNS @height_age TABLE(sno int ,sname varchar(10),ssex varchar(10),sage int ,sdept varchar(10)) 
AS 
BEGIN
  INSERT @height_age 
  SELECT * FROM  dbo.student where sage>@age
RETURN
END
------------------------
SELECT * FROM dbo.stu_hage(22)

 

sno	sname	ssex	sage	sdept
1005	陈宇	男	23	C3
1008	王晓	女	23	C3

 

sno sname ssex sage sdept 1001 张三 .. .. .. 1004 陈旭 .. .. .. 1006 王五 .. .. .. 1009 张斌 .. .. ..