SQL Sever中系统内置函数、用户定义函数

Summ
303 阅读4分钟

SQL Sever中的函数分为两大类：
系统内置函数、用户定义函数

系统内置函数：

image.png

Microsoft SQL 详细数据库函数，请点击

CASE函数

（1）简单的CASE函数

语法格式：

CASE  input_expression
   WHEN  when_expression  THEN  result_expression[…n]
   [ELSE  else_result_expression]
END
  • 计算input_expression表达式的值，并与每一个when_expression表达式的值比较： 若相等，则返回对应的result_expression表达式的值；
    否则，返回else_result_expression表达式的值。
  • 参数input_expression和when_expression的数据类型必须相同（或可以隐性转换）。
  • n表示可以使用多个WHEN when_expression THEN result_expression子句。
    例：查询所有学生的学号和性别。要求使用简单CASE函数将XSDA表中性别列的值由“男”、“女”替换成“男生”、“女生”进行显示，性别列的标题为sex。
SELECT 学号，sex=
CASE 性别
WHEN ‘男' THEN ‘男生' 
WHEN ‘女' THEN ‘女生' 
END
FROM XSDA

（2）搜索型CASE函数

语法格式：

CASE  
   WHEN  Boolean_expression  THEN  result_expression[…n]
   [ELSE  else_result_expression]
END

按指定顺序为每个WHEN子句的Boolean_expression表达式求值。
返回第一个取值为TRUE的Boolean_expression表达式对应的result_expression表达式的值；
如果没有取值为TRUE的Boolean_expression表达式，则当指定ELSE子句时，返回else_result_expression的值；
若没有指定ELSE子句，则返回NULL。 例：查询XSDA表中所有学生的学号和性别，要求使用搜索型CASE函数将性别列的值由“男”、“女”替换成“男生”、“女生”进行显示，性别列的标题为sex。

SELECT 学号， sex=
   CASE
WHEN 性别= '男'   THEN '男生' 
WHEN 性别= '女'   THEN '女生' 
   END
FROM  XSDA

用户定义函数

1．标量函数
2．内嵌表值函数

标量函数

对单一值进行运算，然后返回单一值。
若用户定义函数的返回值为标量值，则该函数称为标量函数。

定义标量函数

语法格式：

CREATE   FUNCTION  function_name      /*函数名部分*/                
([{@parameter_name  [AS]  parameter_data_type [=default]}[,…n]])    /*形参定义部分*/
RETURNS   return_data_type           /*返回值类型*/
[AS]
BEGIN
    function_body                   /*函数体部分*/
    RETURN  expression              /*返回语句*/
END

function_name：函数名。在数据库中必须是唯一的。
@parameter_name：函数的形参名。
parameter_data_type：参数的数据类型。
return_data_type：返回值类型。
function_body：由T-SQL语句序列构成的函数体。
函数返回expression表达式的值。

例：定义一函数sum_G，求某学生的总分。
XSCJ(学号, 课程编号,成绩)

Create  function  sum_G(@Snum char(6))
RETURNS   int
AS
BEGIN
    DECLARE @总分  int
    select @总分 =sum(成绩)
        from  XSCJ
        where  学号=@Snum 
     RETURN @总分 
END

调用标量函数

以下两种方式调用标量函数：
① 利用SELECT语句调用
语法格式：dbo.函数名(实参1，…，实参n)

例：调用上例定义的函数，求学号为201501的学生的总分。

Declare @num1 char(6)
Declare @sum1 int
Set @num1=‘201501’
Select @sum1=dbo.sum_G(@num1)  /*dbo: 数据库所有者此处必写！！！*/
Select @sum1

① 利用EXECUTE语句调用
语法格式：dbo.函数名(实参1，…，实参n)

例：调用上例定义的函数，求学号为201501的学生的总分。

Declare @num1 char(6)
Declare @sum1 int
Set @num1=‘201501’
exec @sum1=dbo.sum_G(@num1)  /*dbo: 数据库所有者此处必写！！！*/
Select @sum1

或： dbo.函数名形参名1=实参1，...，形参名n=实参n

Declare  @总分2  int
exec  @总分2=  dbo.sum_G @snum='201501'
Select  @总分2

内嵌表值函数

定义内嵌表值函数

语法格式：

CREATE   FUNCTION  [owner_name.]function_name       /*定义函数名部分*/                                             
(@parameter_name   parameter_data_type[,…n])       /*定义形参部分*/
RETURNS   TABLE   /*返回值为表类型
AS   RETURN
SELECT语句        /*通过SELECT语句返回内嵌表

RETURNS子句仅包含关键字TABLE，表示此函数返回一个表（即SELECT 语句的结果）。
内嵌表值函数的函数体仅有一个RETURN语句，并通过SELECT语句返回内嵌表值。
TABLE指定表值函数的返回值为表。只有常量和@local_variables可以传递到表值函数。
在内联表值函数中，TABLE返回值是通过单个SELECT语句定义的。内联函数没有关联的返回变量。
语法格式中的其他参数项同标量函数的定义。

例：创建函数查询某学生的各科成绩信息。
XSCJ(学号, 课程编号,成绩)

CREATE  FUNCTION XS_成绩 (@stu_no char(6))   
RETURNS TABLE         
AS  RETURN
SELECT 学号,课程编号,成绩
FROM  XSCJ
WHERE 学号= @stu_no

调用以上函数，查询学号为“200801”学生的各科成绩。

 SELECT * FROM XS_成绩 (‘200801’)

例：创建函数查询某学生的各科成绩，要求学号,姓名,课程名称,成绩。

CREATE  FUNCTION XS_成绩 (@stu_no char(6))   
RETURNS TABLE         
AS  RETURN
SELECT 学号,姓名,课程名称,成绩
FROM  xsda,kcxx,xscj
WHERE xsda.学号=xscj.学号 and

调用以上函数，查询学号为“200801”学生的各科成绩。

 SELECT * FROM XS_成绩 (‘200801’)

删除用户定义函数

语法格式：

DROP  FUNCTION  {[owner_name.]function_name} [,…n]

owner_name：指所有者名。
function_name：指要删除的用户定义的函数名称。
n：表示可以指定多个用户定义的函数予以删除。

例：删除函数XS_成绩

DROP  FUNCTION  XS_成绩
avatar
文章
阅读
粉丝