Utilizar 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 elas assumem podem ser zero (por exemplo, GETDATE), um (por exemplo, UPPER) ou vários (por exemplo, ROUND). Como as funções escalares sempre retornam um único valor, elas podem ser usadas em qualquer lugar e um único valor (o resultado) é necessário. Elas são usadas com mais frequência em cláusulas SELECT e predicados de cláusula WHERE. Elas também podem ser usadas na cláusula SET de uma instrução UPDATE.

As funções escalares internas podem ser organizadas em várias categorias, como cadeia de caracteres, conversão, lógica, matemática e outras. Este módulo vai ver algumas funções escalares comuns.

Dentre as considerações ao usar funções escalares estão:

• Determinismo:se a função retornar o mesmo valor para o mesmo estado de entrada e banco de dados sempre que for chamada, dizemos que ela é 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 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.
• Ordenação:ao usar funções que manipulam dados de caracteres, qual ordenação será usada? Algumas funções usam a ordenação (ordem de classificação) do valor de entrada, outras usam a colagem do banco de dados se não for fornecida nenhuma ordenação de entrada.

Exemplos de função escalar

No momento da redação deste manual, a SQL Server Technical Documentation 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 de cursor
• Funções de data e hora
• Funções matemáticas
• Funções de metadados
• Funções de segurança
• Funções de cadeia de caracteres
• Funções do sistema
• Funções estatísticas de sistema
• Funções de texto e imagem

Não há tempo suficiente neste curso para descrever cada função, mas os exemplos a seguir 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;

Abaixo são mostrados resultados parciais:

ID do Pedido de Venda

Data do Pedido

AnoDoPedido

Mês do Pedido

OrderDay

OrderWeekDay

YearsSinceOrder

71774

2008-06-01T00:00:00

2008

Junho

1

Domingo

13

...

...

...

...

...

...

...

O exemplo a seguir 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

Teto

Quadrado

Raiz

Registro

Aleatória

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:

Nome da Empresa

Maiúscula

Minúsculo

Comprimento

Revertido

FirstSpace

PrimeiraPalavra

RestOfName

Uma Loja de Bicicletas

UMA LOJA DE BICICLETAS

uma loja de bicicletas

12

erotS ekiB A

2

A

Loja de bicicletas

Esportes progressivos

ESPORTES PROGRESSIVOS

ESPORTES PROGRESSIVOS

18

stropS evissergorP

12

Progressivo

Esportes

Componentes avançados da bicicleta

COMPONENTES DE BICICLETAS AVANÇADOS

componentes de bicicletas avançados

24

stnenopmoC ekiB decnavdA

9

Avançado

Componentes de 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 Truee 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á "Cobrança". Para todos os outros valores de tipo “endereço”, a expressão retorna "Mailing".

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

Os resultados parciais dessa consulta podem ter a seguinte aparência:

Tipo de Endereço

UseAddressFor

Escritório Principal

Cobrança

Remessa

Postagem

...

...

ESCOLHA

A função CHOOSE avalia uma expressão integral 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 a seguinte aparência:

ID do Pedido de Venda

Situação

Status do Pedido

1234

3

Entregue

1235

2

Enviado

1236

2

Enviado

1237

1

Encomendado

...

...

...