Utiliser les fonctions scalaires

  • 3 minutes

Les fonctions scalaires retournent une valeur unique et travaillent généralement sur une seule ligne de données. Le nombre de valeurs d’entrée qu’elles prennent peut être égal à zéro (par exemple, GETDATE), un (par exemple, UPPER) ou plus (par exemple, ROUND). Étant donné que les fonctions scalaires renvoient toujours une seule valeur, elles peuvent être utilisées partout où une seule valeur (le résultat) est nécessaire. Elles sont le plus souvent utilisées dans les clauses SELECT et les prédicats de clause WHERE. Elles peuvent également être utilisées dans la clause SET d’une instruction UPDATE.

Les fonctions scalaires intégrées peuvent être organisées en nombreuses catégories : chaîne, conversion, logique, mathématiques, etc. Ce module va examiner quelques fonctions scalaires courantes.

Voici quelques points à prendre en compte lors de l’utilisation de fonctions scalaires :

  • Déterminisme : si la fonction retourne la même valeur pour les mêmes entrée et état de base de données chaque fois qu’elle est appelée, nous disons qu’elle est déterministe. Par exemple, ROUND(1.1, 0) retourne toujours la valeur 1.0. De nombreuses fonctions intégrées sont non déterministes. Par exemple, GETDATE() renvoie les date et heure actuelles. Les résultats des fonctions non déterministes ne peuvent pas être indexés, ce qui affecte la capacité du processeur de requêtes à trouver un bon plan d’exécution de la requête.
  • Classement : lors de l’utilisation de fonctions qui manipulent des données caractères, quel classement sera utilisé ? Certaines fonctions utilisent le classement (ordre de tri) de la valeur d’entrée ; d’autres utilisent le classement de la base de données si aucun classement d’entrée n’est fourni.

Exemples de fonctions scalaires

Au moment de la rédaction de ce document, la documentation technique de SQL Server répertorie plus de 200 fonctions scalaires qui couvrent plusieurs catégories, notamment :

  • Fonctions de configuration
  • Fonctions de conversion
  • Fonctions curseur
  • Fonctions de date et heure
  • Fonctions mathématiques
  • Fonctions de métadonnées
  • Fonctions de sécurité
  • Fonctions de chaînes
  • Fonctions système
  • Fonctions de statistiques système
  • Fonctions texte et image

Nous n’avons pas assez de temps dans ce cours pour décrire chaque fonction, mais les exemples ci-dessous illustrent des fonctions couramment utilisées.

L’exemple hypothétique suivant utilise plusieurs fonctions de date et d’heure :

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;

Les résultats partiels sont affichés ci-dessous :

SalesOrderID

OrderDate

OrderYear

OrderMonth

OrderDay

OrderWeekDay

YearsSinceOrder

71774

2008-06-01T00:00:00

2008

June

1

Dimanche

13

...

...

...

...

...

...

...

L’exemple suivant comprend certaines fonctions mathématiques :

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;

Résultats partiels :

TaxAmt

Arrondi

Floor

Ceiling

Squared

Root

Journal

Aléatoire

70.4279

70.0000

70.0000

71.0000

4960.089098

8.392133221

4.254589491

28.64120429

...

..

...

...

...

...

...

...

L’exemple suivant utilise des fonctions de chaîne :

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;

Résultats partiels :

CompanyName

UpperCase

LowerCase

Longueur

Inversé

FirstSpace

FirstWord

RestOfName

A Bike Store

A BIKE STORE

a bike store

12

erotS ekiB A

2

Un

Bike Store

Progressive Sports

PROGRESSIVE SPORTS

progressive sports

18

stropS evissergorP

12

Progressif

Sports

Advanced Bike Components

ADVANCED BIKE COMPONENTS

advanced bike components

24

stnenopmoC ekiB decnavdA

9

Avancé

Bike Components

...

...

...

...

...

...

...

...

Fonctions logiques

Une autre catégorie de fonctions permet de déterminer laquelle, parmi plusieurs valeurs, doit être retournée. Les fonctions logiques évaluent une expression d’entrée et retournent une valeur appropriée en fonction du résultat.

IIF

La fonction IIF évalue une expression d’entrée booléenne et retourne une valeur spécifiée si l’expression prend la valeur True et une autre valeur si l’expression prend la valeur False.

Par exemple, considérez la requête suivante, qui évalue le type d’adresse d’un client. Si la valeur est « Main Office », l’expression retourne « Billing ». Pour toutes les autres valeurs de type d’adresse, l’expression retourne « Mailing ».

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

Les résultats partiels de cette requête peuvent ressembler à ceci :

Typedadresse

UseAddressFor

Main Office

Facturation

Expédition

Mailing

...

...

CHOOSE

La fonction CHOOSE évalue une expression entière et retourne la valeur correspondante d’une liste en fonction de sa position ordinale (de base 1).

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

Les résultats de cette requête peuvent se présenter comme suit :

SalesOrderID

Statut

OrderStatus

1234

3

Delivered (Livrée)

1235

2

Shipped

1236

2

Shipped

1237

1

Ordered (Validée)

...

...

...