使用标量函数

已完成

标量函数返回单个值,通常使用单行数据。 它们采用的输入值的数目可以是零个(例如 GETDATE)、一个(例如 UPPER)或多个(例如 ROUND)。 因为标量函数始终返回单个值,所以它们可以在需要单个值(结果)的任何场景中使用。 标量函数最常在 SELECT 子句和 WHERE 子句谓词中使用。 也可以在 UPDATE 语句的 SET 子句中使用标量函数。

内置标量函数可组织为多个类别,如字符串、转换、逻辑、数学等。 本模块将介绍一些常见的标量函数。

使用标量函数时,一些注意事项包括:

  • 确定性:如果每次调用函数时,对于相同的输入和数据库状态都返回相同的值,则我们说这个函数是确定性的。 例如,ROUND(1.1, 0) 始终返回值 1.0。 许多内置函数都是非确定性的。 例如,GETDATE() 返回当前日期和时间。 无法为非确定性函数的结果编制索引,因为这会影响查询处理器制定出色的查询执行计划的能力。
  • 排序规则:使用操作字符数据的函数时,将使用哪个排序规则? 有些函数使用输入值的排序规则(排序顺序);如果没有提供输入排序规则,其他函数则使用数据库的排序规则。

标量函数示例

在撰写本文时,SQL Server 文档列出了跨多个类别的 200 多个标量函数,包括:

  • 配置函数
  • 转换函数
  • 游标函数
  • Date 和 Time 函数
  • 数学函数
  • 元数据函数
  • 安全性函数
  • 字符串函数
  • 系统函数
  • 系统统计函数
  • 文本和图像函数

本课程没有足够的时间来描述每个函数,以下示例只显示了一些常用的函数。

下面的假设示例使用了若干日期和时间函数:

SELECT  SalesOrderID,
    OrderDate,
        YEAR(OrderDate) AS OrderYear,
        DATENAME(mm, OrderDate) AS OrderMonth,
        DAY(OrderDate) AS OrderDay,
        DATENAME(dw, OrderDate) AS OrderWeekDay,
        DATEDIFF(yy,OrderDate, GETDATE()) AS YearsSinceOrder
FROM Sales.SalesOrderHeader;

部分结果如下所示:

销售订单 ID

OrderDate

OrderYear

OrderMonth

OrderDay

OrderWeekDay

YearsSinceOrder

71774

2008-06-01T00:00:00

2008 年

June

1

星期日

13

...

...

...

...

...

...

...

下一个示例包括一些数学函数:

SELECT TaxAmt,
       ROUND(TaxAmt, 0) AS Rounded,
       FLOOR(TaxAmt) AS Floor,
       CEILING(TaxAmt) AS Ceiling,
       SQUARE(TaxAmt) AS Squared,
       SQRT(TaxAmt) AS Root,
       LOG(TaxAmt) AS Log,
       TaxAmt * RAND() AS Randomized
FROM Sales.SalesOrderHeader;

部分结果:

TaxAmt

已四舍五入

Floor

上限

Squared

Root

日志

具有随机性

70.4279

70.0000

70.0000

71.0000

4960.089098

8.392133221

4.254589491

28.64120429

...

..

...

...

...

...

...

...

以下示例使用一些字符串函数:

SELECT  CompanyName,
        UPPER(CompanyName) AS UpperCase,
        LOWER(CompanyName) AS LowerCase,
        LEN(CompanyName) AS Length,
        REVERSE(CompanyName) AS Reversed,
        CHARINDEX(' ', CompanyName) AS FirstSpace,
        LEFT(CompanyName, CHARINDEX(' ', CompanyName)) AS FirstWord,
        SUBSTRING(CompanyName, CHARINDEX(' ', CompanyName) + 1, LEN(CompanyName)) AS RestOfName
FROM Sales.Customer;

部分结果:

CompanyName

大写

小写

长度

Reversed

FirstSpace

FirstWord

RestOfName

自行车商店

A BIKE STORE

a bike store

12

erotS ekiB A

2

A

Bike Store

渐进性运动

PROGRESSIVE SPORTS

progressive sports

18

stropS evissergorP

12

渐进

体育游戏

高级自行车部件

ADVANCED BIKE COMPONENTS

advanced bike components

24

stnenopmoC ekiB decnavdA

9

高级

Bike Components

...

...

...

...

...

...

...

...

逻辑函数

另一类函数可用于确定要返回的多个值中的哪一个。 逻辑函数对输入表达式求值,并根据结果返回相应的值。

IIF

IIF 函数对布尔输入表达式求值,如果表达式求值结果为 True,则返回指定值;如果表达式求值结果为 False,则返回替代值。

例如,请考虑以下查询,该查询对客户的地址类型进行求值。 如果值为“Main Office”,则表达式返回“Billing”。 对于所有其他地址类型值,表达式返回“Mailing”。

SELECT AddressType,
      IIF(AddressType = 'Main Office', 'Billing', 'Mailing') AS UseAddressFor
FROM Sales.CustomerAddress;

此查询的部分结果可能如下所示:

AddressType

UseAddressFor

Main Office

计费

船运

Mailing

...

...

CHOOSE

CHOOSE 函数对一个整数表达式求值,并根据其序号位置(从 1 开始)从列表中返回对应的值。

SELECT SalesOrderID, Status,
CHOOSE(Status, 'Ordered', 'Shipped', 'Delivered') AS OrderStatus
FROM Sales.SalesOrderHeader;

该查询的结果可能如下所示:

销售订单 ID

Status

OrderStatus

1234

3

送达量

1235

2

已装运

1236

2

已装运

1237

1

已订购

...

...

...