Usar funções escalares

Concluído

As funções escalares retornam um único valor e geralmente funcionam em uma única linha de dados. O número de valores de entrada que eles usam pode ser zero (por exemplo, GETDATE), um (por exemplo, UPPER) ou múltiplo (por exemplo, ROUND). Como as funções escalares sempre retornam um único valor, elas podem ser usadas em qualquer lugar onde um único valor (o resultado) seja necessário. Eles são mais comumente usados em cláusulas SELECT e predicados de cláusulas WHERE. Eles também podem ser usados na cláusula SET de uma instrução UPDATE.

As funções escalares incorporadas podem ser organizadas em muitas categorias, como string, conversão, lógica, matemática e outras. Este módulo analisará algumas funções escalares comuns.

Algumas considerações ao usar funções escalares incluem:

• Determinismo: Se a função retorna o mesmo valor para o mesmo estado de entrada e banco de dados cada vez que é chamada, dizemos que é determinística. Por exemplo, ROUND(1.1, 0) sempre retorna o valor 1.0. Muitas funções internas são não determinísticas. Por exemplo, GETDATE() retorna a data e a hora atuais. Os resultados de funções não determinísticas não podem ser indexados, o que afeta a capacidade do processador de consultas de criar um bom plano para executar a consulta.
• Agrupamento: Ao usar funções que manipulam dados de caracteres, qual agrupamento será usado? Algumas funções usam o agrupamento (ordem de classificação) do valor de entrada; outros usam o agrupamento do banco de dados se nenhum agrupamento de entrada for fornecido.

Exemplos de funções escalares

No momento em que este artigo foi escrito, a Documentação Técnica do SQL Server listava mais de 200 funções escalares que abrangem várias categorias, incluindo:

• Funções de configuração
• Funções de conversão
• Funções do cursor
• Funções Date and Time
• Funções matemáticas
• Funções de metadados
• Funções de segurança
• Funções de cadeia
• Funções do sistema
• Funções estatísticas do sistema
• Funções de texto e imagem

Não há tempo suficiente neste curso para descrever cada função, mas os exemplos abaixo mostram algumas funções comumente usadas.

O exemplo hipotético a seguir usa várias funções de data e hora:

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;

Os resultados parciais são mostrados abaixo:

SalesOrderID

OrderDate

Ano da encomenda

EncomendarMês

OrderDay

OrdemDia da Semana

AnosadesOrdem

71774

2008-06-01T00:00:00

2008

Junho

1

Domingo

13

...

...

...

...

...

...

...

O próximo exemplo inclui algumas funções matemáticas:

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;

Resultados parciais:

TaxAmt

Arredondado

Piso

Ceiling

Ao quadrado

Raiz

Registo

Aleatorizado

70.4279

70.0000

70.0000

71.0000

4960.089098

8.392133221

4.254589491

28.64120429

...

..

...

...

...

...

...

...

O exemplo a seguir usa algumas funções de cadeia de caracteres:

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;

Resultados parciais:

CompanyName

Maiúsculas

Minúsculas

Duração

Invertido

Primeiro Espaço

Primeira Palavra

RestOfName

Uma loja de bicicletas

UMA LOJA DE BICICLETAS

uma loja de bicicletas

12

erotS ekiB A

2

A

Loja de Bicicletas

Desportos Progressivos

DESPORTOS PROGRESSIVOS

desportos progressivos

18

stropS evissergorP

12

Progressivo

Desporto

Componentes Avançados para Bicicletas

COMPONENTES AVANÇADOS PARA BICICLETAS

Componentes avançados da bicicleta

24

stnenopmoC ekiB decnavdA

9

Avançado

Componentes da bicicleta

...

...

...

...

...

...

...

...

Funções lógicas

Outra categoria de funções permite determinar qual dos vários valores deve ser retornado. As funções lógicas avaliam uma expressão de entrada e retornam um valor apropriado com base no resultado.

IIF

A função IIF avalia uma expressão de entrada booleana e retorna um valor especificado se a expressão for avaliada como True e um valor alternativo se a expressão for avaliada como False.

Por exemplo, considere a consulta a seguir, que avalia o tipo de endereço de um cliente. Se o valor for "Escritório Principal", a expressão retornará "Faturamento". Para todos os outros valores de tipo de endereço, a expressão retorna "Mailing".

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

Os resultados parciais desta consulta podem ter esta aparência:

AddressType

UseAddressFor

Escritório Principal

Faturação

Envio

Envio por correspondência

...

...

ESCOLHA

A função CHOOSE avalia uma expressão inteira e retorna o valor correspondente de uma lista com base em sua posição ordinal (baseada em 1).

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

Os resultados dessa consulta podem ter esta aparência:

SalesOrderID

Status

Estado da Encomenda

1234

3

Entregues

1235

2

Enviada

1236

2

Enviada

1237

1

Encomendado

...

...

...