使用純量函數

3 分鐘

純量函數傳回單一值，通常處理單一資料列的資料。接受的輸入值可以是零個 (例如 GETDATE)、一個 (例如 UPPER) 或多個 (例如 ROUND)。因為純量函數一律傳回單一值，所以在需要單一值 (結果) 的任何地方都可使用。最常用於 SELECT 子句和 WHERE 子句述詞中。也可以用在 UPDATE 陳述式的 SET 子句中。

內建純量函數可以分成許多類別，例如字串、轉換、邏輯、數學和其他。本課程模組將看一下幾個常用的純量函數。

使用純量函數時的一些考量包括：

決定性：在相同的輸入和資料庫狀態下，如果每次呼叫函數都傳回相同的值，可謂此函數具有「決定性」。例如，ROUND(1.1, 0) 一律傳回值 1.0。許多內建函數都是「非決定性」。例如，GETDATE () 傳回目前的日期和時間。非決定性函數的結果無法編製索引，這使得查詢處理器無法產生良好計劃來執行查詢。
定序：使用函數來操作字元資料時，將使用哪一種定序？某些函數使用輸入值的定序 (排序次序)，但如果未提供輸入定序，則某些函數會使用資料庫的定序。

純量函數範例

在撰寫本文時，《SQL Server 技術文件》列出 200 多個純量函數，遍及多個種類，包括：

組態函數
轉換函數
資料指標函數
日期和時間函式
數學函式
中繼資料函數
安全性函數
字串函數
系統函式
系統統計函數
文字和影像函數

本課程沒有足夠的時間描述每個函數，但下列範例顯示一些常用的函數。

下列假設性範例使用幾個日期和時間函數：

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;

部分結果如下所示：

SalesOrderID

OrderDate

OrderYear

OrderMonth

OrderDay

OrderWeekDay

YearsSinceOrder

71774

2008-06-01T00:00:00

2008

6 月

星期日

...

下一個範例包含一些數學函數：

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

Rounded

底板

Ceiling

Squared

根目錄

記錄

隨機化

70.4279

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

UpperCase

LowerCase

長度

反轉

FirstSpace

FirstWord

RestOfName

A Bike Store

A BIKE STORE

a bike store

erotS ekiB A

Bike Store

Progressive Sports

PROGRESSIVE SPORTS

progressive sports

stropS evissergorP

Progressive

運動

Advanced Bike Components

ADVANCED BIKE COMPONENTS

advanced bike components

stnenopmoC ekiB decnavdA

進階

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;

此查詢的結果如下所示：

SalesOrderID

狀態

OrderStatus

1234

已送達

1235

已發貨

1236

已發貨

1237

排序

...

使用純量函數

純量函數範例

邏輯函式

IIF

CHOOSE

意見反應