Wikilivres

Microsoft SQL Server/Fonctions

< Microsoft SQL Server
Procédures stockées Procédures stockées
Bases de données spatiales Bases de données spatiales
  1. Introduction
  2. Bases de données
  3. Gestion des utilisateurs
  4. Variables
  5. Tables
  6. Procédures stockées
  7. Fonctions
  8. Bases de données spatiales
  9. Performances
  10. Importer et exporter
  11. SugarCRM
Annexes
  1. Mots réservés
  2. Problèmes connus

[ Modifier le sommaire ]

Procédures stockées Procédures stockées
Bases de données spatiales Bases de données spatiales

Introduction

modifier

On distingue deux types de fonctions :

  1. scalaire : renvoie n'importe quel type de données applicables aux champs, hormis text, ntext, image, cursor et timestamp[1].
  2. table : renvoie une table.

Environnement SERVERPROPERTY

modifier

La fonction suivante permet de récupérer le nom du serveur courant : 

SELECT SERVERPROPERTY('MachineName')

Condition ISNULL

modifier

Cette fonction ne renvoie pas vrai si la variable est nulle, comme dans d'autres langages de programmation, mais plutôt son second paramètre, un substitut à NULL obligatoire. Ceci permet de lever les exceptions sur les variables nulles directement dans les requêtes, sans ajout de ligne supplémentaire.

Voici un exemple où Les NULL sont traités comme les vides : 

select Champ1 = case when isnull(@Colonne,'')='' then '*' else @Colonne end
from Table1

Extrémums : MIN, MAX

modifier

Les fonctions Min() et Max() renvoient respectivement le minimum et le maximum d'une liste de champs. 

select min(Date) from Calendrier where RDV = 'Important'

Conversions : CAST et CONVERT

modifier

CAST modifie le type d'une variable : 

 cast(Champ as decimal(12, 6)) -- sinon '9' > '10'

CONVERT modifie le type d'une variable en premier paramètre, et sa longueur en second. 

 convert(varchar, Champ1, 112)
 convert(datetime, Champ2, 121)       -- sinon impossible de parcourir le calendrier (ex : J + 1)

 

Tous les types de variable ne sont pas compatibles entre eux[2].

Exemple de problème : 

 select Date1
 from Table1
 where Date1 between '01/10/2013' and '31/10/2013'

Les dates ne sont pas forcément reconnues sans utiliser convert. La solution est donc de stocker les dates si possible dans le format datetime : 

 select Date1
 from Table1
 where Date1 between convert(varchar,'20131001',112) and convert(varchar,'20131031',112)

Si par contre la date du paragraphe ci-dessus est stockée en varchar avec des slashs, il devient obligatoire de la reformater pour pouvoir la comparer.

De nombreux formats de dates sont disponibles[3].

Arrondis : FLOOR, CEILING, ROUND

modifier

Pour arrondir un nombre :

  • À l'inférieur : floor(nombre).
  • Au supérieur : ceiling(nombre).
  • Au nombre de chiffres précisé : round(nombre, chiffres). Ex :
    round(499, -3) = 0.
    round(500, -3) = 1000.
    round(0.45, 1) = 0.50.
    round(0.44, 1) = 0.40.

 

Pour arrondir une opération sur un entier (ex : COUNT), il faut le convertir sinon il s'arrondit à l'inférieur. Ex : 

SELECT ceiling(13 / 12) // = 0
SELECT ceiling(convert(decimal(12, 6), 13) / 12) // = 1

Troncatures : LEFT, RIGHT, et SUBSTRING

modifier

Permettent de découper des chaines de caractères selon les positions de leurs caractères[4]. 

 select substring('13/10/2013 00:09:19', 7, 4) -- renvoie les quatre caractères à partir du septième, soit "2013"

Par exemple dans le cas de la date avec slashs vue dans le paragraphe précédent : 

 select Date1
 from Table1
 where right(Date1, 4) + substring(Date1, 4, 2) + left(Date1, 2) between convert(varchar,'20131001',112) and convert(varchar,'20131031',112)

Recherche CHARINDEX

modifier

Pour rechercher la position d'un mot dans une chaine de caractères[5] : 

select CHARINDEX ('table2', 'table1 table2 table3');
-- égal 8

Manipulations : REPLACE et STUFF

modifier

Permettent de remplacer des caractères dans une chaine selon leur valeur[6] : rechercher et remplacer.

Par exemple pour mettre à jour le chemin d'un répertoire renommé[7] : 

update Table1
SET Champ1 = replace(Champ1,'\Ancien chemin\','\Nouveau chemin\')
where Champ1 like '%\Ancien chemin\%'

Dates

modifier

Format date

modifier

La fonction GETDATE est utilisée pour la date courante. Pour obtenir plutôt la date d'un jour donné au format date, il faut utiliser CONVERT : 

select convert(smalldatetime, '2016-01-02', 121)

Découpage

modifier

La fonction DATEPART extrait une partie de date sans avoir besoin de son emplacement[8].

Toutefois, trois fonctions permettent d'accélérer l'écriture de ce type d'extractions : 

-- Jour
select day(getdate())
-- Mois
select month(getdate())
-- Année
select year(getdate())
-- Année précédente
select getdate(), 'Année précédente : ' + str(year(getdate()) - 1)

Noms

modifier

La fonction DATENAME()[9] permet d'obtenir les noms des éléments d'une date. Ex : 

select datename(month, '20160712')   -- affiche "juillet"
select datename(weekday, '20160712') -- affiche "mardi"

Addition et soustraction de jours

modifier

Voici deux fonctions de manipulation de dates[10] :

  • DATEDIFF calcule l’intervalle entre deux dates[11].
  • DATEADD retourne la date issue d'une autre plus un intervalle[12].
-- Dernier jour du mois précédent
SELECT DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0))
-- Dernier jour du mois courant
SELECT DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE())+1,0))
-- Dernier jour du mois prochain
SELECT DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE())+2,0))

Exemple : 

SELECT DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,'20150101'),0)) as date

donne : 

date
2014-12-31 23:59:59.000

Divers

modifier

COALESCE

modifier

Ce verbe anglais signifie "fusionner". La fonction affiche la première colonne en paramètre, puis si elle est nulle la deuxième, et ainsi de suite.

Exemple : 

SELECT Champ1, Champ2, Champ3
FROM Table1
/* Affiche :
1 NULL NULL
NULL 2 NULL
NULL NULL 3
*/

SELECT COALESCE(Champ1, Champ2, Champ3)
FROM Table1
/* Affiche :
1
2
3
*/

RAND

modifier

Abréviation de random (aléatoire), génère un nombre au hasard entre zéro et un[13].

Exemple : 

SELECT rand(), rand()
-- 0,733812301862999        0,043991986929891

Personnalisée

modifier

On peut aussi créer ses propres fonctions avec CREATE FUNCTION[14], qui sont stockées dans un menu séparé à côté des tables et des procédures stockées. 

CREATE FUNCTION MaFonction1(@Paramètre1 varchar) returns varchar AS
BEGIN
  return 'Hello ' + @Paramètre1
END

Triggers

modifier

Les triggers sont des scripts qui se déclenchent selon certains évènements, et sont créés avec CREATE TRIGGER[15]. Dans SSMS, on les trouvent dans le menu Déclencheurs de base de données.

Par exemple pour afficher un texte à chaque création de base : 

CREATE TRIGGER MonTrigger1
ON ALL SERVER 
FOR CREATE_DATABASE 
AS
BEGIN
  PRINT 'Une base a été créée.'
END

Parmi les évènements disponibles, on trouve :

  1. FOR CREATE_DATABASE
  2. IF UPDATE
  3. IF INSERTED

Les actions à entreprendre peuvent être :

  1. INSTEAD OF INSERT : l'action du trigger remplace une insertion.
  2. INSERT AFTER
  3. UPDATE AFTER

EVENTDATA

modifier

EVENTDATA est une fonction qui renvoie une valeur XML[16] si elle est référencée dans un trigger.

Références

modifier
  1. ↑ https://technet.microsoft.com/fr-fr/library/ms177499(v=sql.105).aspx
  2. ↑ man CONVERT
  3. ↑ http://stackoverflow.com/questions/74385/how-to-convert-datetime-to-varchar
  4. ↑ man SUBSTRING
  5. ↑ https://msdn.microsoft.com/fr-fr/library/ms186323.aspx
  6. ↑ man STUFF
  7. ↑ man REPLACE
  8. ↑ man DATEPART
  9. ↑ https://msdn.microsoft.com/fr-fr/library/ms174395.aspx
  10. ↑ http://blog.sqlauthority.com/2007/08/18/sql-server-find-last-day-of-any-month-current-previous-next/
  11. ↑ man DATEDIFF
  12. ↑ man DATEADD
  13. ↑ https://msdn.microsoft.com/fr-fr/library/ms177610.aspx
  14. ↑ https://msdn.microsoft.com/fr-fr/library/ms186755(v=sql.120).aspx
  15. ↑ https://msdn.microsoft.com/fr-fr/library/ms189799(v=sql.100).aspx
  16. ↑ https://msdn.microsoft.com/fr-fr/library/ms173781.aspx
Récupérée de « https://fr.wikibooks.org/w/index.php?title=Microsoft_SQL_Server/Fonctions&oldid=674025 »