Ce support a pour but de vous initier à la programmation des serveurs de bases de données relationnelles. Le système cible retenu est SQL Server et le langage de programmation Transact SQL.
La programmation des transactions au sein de programmes compilés et stockés sur le serveur SGBDR apporte de la rigueur à vos développements et facilite la maintenance à venir de vos applicatifs.
Le recours à la programmation des transactions dans des procédures stockées permet :
Pour commenter une ligne, il suffira de faire précéder le commentaire de deux tirets –
Pour commenter un bloc, de le délimiter par /* et */
Tous les noms d’objets (table, colonne, variable, etc.) doivent respecter les règles suivantes :
Il n’est pas nécessaire de respecter la casse des caractères mais par convention seuls les mots clefs du langage seront en majuscules.
Les variables sont typées. Leurs principaux types sont :
INT entier (et ses dérivés SMALLINT, TINYINT, BIGINT)
DECIMAL (11,2) nombre à 11 chiffres (décimaux) dont 2 après la virgule.
Il convient de définir la précision (nombre maximal de chiffres) et l’échelle (nombre de chiffres à droite de la virgule).
REAL réel flottant codé sur 4 octets et FLOAT (double précision) sur 8 octets.
CHAR (50) : chaîne de caractères de longueur fixe quelle que soit la valeur
VARCHAR (25) : chaîne de caractères de longueur variable ne pouvant contenir plus de 25 caractères.
NCHAR et NVARCHAR sont de même nature que les précédents mais la définition du caractère est en Unicode et stockée sur 2 octets par caractère.
Ce type est particulièrement intéressant si vous devez stocker des valeurs de données exprimées en plusieurs langues, comme c’est souvent le cas dans les applications multilingues.
La longueur d’une chaîne, exprimée selon ces types, ne peut excéder 8000 caractères. Pour des valeurs excédant cette taille, il vous faut utiliser les types prévus pour stocker des BLOBs (Binary Large OBject). Ces types sont définis comme :
TEXT ou NTEXT : Permet de stocker une chaîne au format ASCII ou Unicode.
IMAGE : Permet de stocker un flux d’octets.
Cette approche permet de stocker des valeurs qui excédent 2 milliards d’octets. En fait, la valeur réelle stockée au niveau de la table est un pointeur de référence de l’objet stocké sur disque.
MONEY : décimal avec symbole monétaire. Il est préférable de ne pas avoir recours à ce type et stocker la valeur de la devise dans une colonne spécifique.
DATETIME, SMALLDATETIME : date et heure. DATETIME s'emploie pour les données comprises entre le 1er janvier 1753 et le 31 décembre 9999 (chaque valeur est stockée dans 8 octets). L'autre type, SMALLDATETIME, s'applique aux dates comprises entre le 1er janvier 1900 et le 6 juin 2079 (chaque valeur est stockée dans 4 octets). Des fonctions spécifiques permettent de manipuler ces types.
BIT : Booléen.
Une variable se déclare à l’aide du mot clé DECLARE.
Par convention, le nom de la variable locale à la procédure ou au script est préfixé par un @.
Il existe aussi des variables dites globales qui représente des valeurs du système. Elles sont alors préfixées de @@.
L’opérateur SET permet d’affecter une valeur à une variable.
On peut aussi affecter une valeur à une variable par le biais de l’exécution d’une requête de sélection. Il convient de s’assurer que la requête ne renvoie alors qu’une seule ligne afin d’obtenir une valeur scalaire.
L’opérateur PRINT permet d’imprimer le contenu d’une variable. A utiliser lors des phases de tests…
Exemple :
L’exemple suivant montre les principes d’affectation selon les deux procédés et met en œuvre des opérations de conversion de type.
Vous retrouvez dans le langage Transact SQL les structures les plus usuelles d’un langage de programmation qui vous permettront de programmer l’exécution conditionnelle d’instructions ou la répétition de l’exécution de blocs d’instructions.
Un bloc d’instructions est délimité par les instructions BEGIN et END. Cette structure est utilisée par toutes les autres structures, conditionnelles, itératives, transactionnelles, …
IF expression conditionnelle
Instruction ou Bloc d’instructions
ELSE facultatif
Instruction ou Bloc d’instructions
Quelques exemples :
L’exemple ci-dessous met en œuvre des instructions conditionnées dans le cadre d’une procédure stockée dont le rôle est de supprimer un client référencé dans la base de données.
Il est possible d’avoir recours à la structure CASE qui permet l’évaluation successive de différentes conditions au sein d’un même groupe.
La structure CASE prend en charge un argument ELSE facultatif.
Les deux exemples qui suivent vous permettent de mieux appréhender la syntaxe de ces expressions conditionnelles.
A noter : lorsque vous recourez à cette structure pour réaliser des mises à jour conditionnelles, il faut prendre garde au fait que la colonne devant être modifiée vaudra Null si vous ne lui affectez aucune valeur et ne conservera donc pas sa valeur initiale.
D’où la programmation de l’instruction sur ELSE qui sera exécutée dans le cas où aucune condition n’a été vérifiée dans l’expression des différents cas.
Utilisation case forme simple
Utilisation case forme complexe
Il n’existe qu’une seule structure qui permette de spécifier des boucles d’instructions, la structure WHILE.
WHILE expression de la condition
Instruction ou bloc d’instructions
L’exemple suivant illustre la mise en place d’une itération similaire à une boucle FOR.
Vous pouvez éventuellement conditionner l’arrêt ou la poursuite d’un traitement en fonction d’une condition incluse dans la boucle.
IF Expression de la condition
BREAK
ELSE
CONTINUE
END
Certaines conversions ne peuvent être automatiquement réalisées par le système. Nous devons alors réaliser ces conversions de manière explicite au moyen des fonctions de conversion CAST et CONVERT.
Attention aux types d’origine et résultant de la conversion : toutes les combinaisons ne sont pas admises.
CONVERT permet de définir un style pour la donnée convertie alors que CAST ne le permet pas :
La fonction système GETDATE () renvoie la date du jour. Et si je souhaite convertir celle-ci dans un format américain, j’utilise la fonction CONVERT avec le style approprié.
Pour plus d’informations, voir l’aide de Transact SQL à l’index CONVERT
Je souhaite que le CA net soit converti et présenté dans un décimal de 10 de long avec 2 chiffres derrière la virgule
Fonctions de traitement de chaînes
Quelques exemples dans ce tableau car elles sont nombreuses … Voir aide à l’index fonctions, chaîne
|
LEFT, RIGHT |
Extraire des caractères à gauche ou à droite |
|
UPPER, LOWER |
Mettre en majuscules ou minuscules |
|
LTRIM, RTRIM |
Suppression des espaces à gauche ou à droite |
|
SUBSTRING |
Extraction d’une sous chaîne |
|
REVERSE |
Inversion d’une chaîne (miroir…) |
|
LEN |
Longueur d’une chaîne |
|
ASCII |
Valeur ascii d’un caractère |
|
NCHAR |
Renvoie le caractère Unicode fonction de la valeur donnée |
|
REPLACE |
Remplacement d’une occurrence de chaîne par une autre |
Liste des noms des pilotes formatés :
Le premier caractère de gauche est mis en majuscules
Les autres caractères en minuscules
Remplacement de l’occurrence Toulouse par Ville Rose dans l’attribut Ville de la Table Pilote :
Fonctions intégrées permettant de manipuler des valeurs de type DATETIME.
|
DATEADD |
Ajout d’un intervalle de temps à une date |
|
DATEDIFF |
Intervalle de temps entre deux dates |
|
DATEPART |
Extraction d’une partie de date |
|
DATENAME |
Chaîne représentant une partie de date |
|
DAY, MONTH, YEAR |
Renvoie d’une partie de date |
|
GETDATE, GETUTCDATE |
Date du système |
Quelques exemples :
Ajout de 3 jours à la date de naissance du Pilote :
Nombre de jours entre la date de naissance et la date du jour :
Extrait de portions de la date de naissance avec DatePart et DateName :
Un opérateur est un symbole spécifiant une action exécutée sur une ou plusieurs expressions. Nous trouvons en SQL, différentes catégories d’opérateurs. Je vous présente ici les principaux utilisables dans les requêtes de sélection.
Utilisables sur toute expression de 2 valeurs numériques et sur les valeurs de type DateTime ou SmallDateTime (+ et – uniquement)
+ Addition, - Soustraction, * Multiplication, / Division
% Modulo (Retourne le reste entier d'une division), Exemple : 12 % 5 = 2
Exemple :
Total du CA Net généré par commande
Remarques :
Les opérateurs de comparaison testent si deux expressions sont identiques.
Ils peuvent s'utiliser sur toutes les expressions composées de données structurées, donc à l'exception des expressions de type de données text, ntext ou image.
= Égal à, > Supérieur à, < Inférieur à, >= Supérieur ou égal à, <= Inférieur ou égal à, <> Différent de
Les opérateurs logiques testent la valeur logique d'une condition. Les opérateurs logiques, comme les opérateurs de comparaison, retournent un type de données booléen de valeur TRUE ou FALSE.
Un certain nombre d’entre eux (signalés par un * dans le tableau) sont utilisés pour comparer une valeur scalaire (unique) avec une sous requête.
|
Opérateur |
Description |
|
ALL (*) |
TRUE si tous les éléments d'un jeu de comparaisons sont TRUE. |
|
AND |
TRUE les deux expressions booléennes sont TRUE. |
|
ANY (*) |
TRUE si n'importe quel élément d'un jeu de comparaison est TRUE. |
|
BETWEEN |
TRUE si l'opérande est situé dans une certaine plage. |
|
EXISTS (*) |
TRUE si une sous-requête contient des lignes. |
|
IN (*) |
TRUE si l'opérande est égal à un élément d'une liste d'expressions. |
|
LIKE |
TRUE si l'opérande correspond à un modèle. |
|
NOT |
Inverse la valeur de tout autre opérateur booléen. |
|
OR |
TRUE si l'une ou l'autre expression booléenne est TRUE. |
|
SOME (*) |
TRUE si certains éléments d'un jeu de comparaisons sont TRUE. |
|
* |
Utilisés avec des sous-requêtes |
Exemple :
Liste des vols au départ de Toulouse et à destination de Paris entre 12 et 15 heures.
Syntaxe de l’opérateur Between : Between valeur de début AND valeur de fin.
Les bornes sont retenues dans la sélection comme le prouve la requête ci-dessus. Syntaxe de l’opérateur Like
|
Caractère générique |
Description |
|
% |
Toute chaîne de zéro caractère ou plus |
|
_ |
N'importe quel caractère à cet emplacement |
|
[ ] |
Tout caractère de l'intervalle ([a-f]) ou de l'ensemble spécifié ([abcdef]) |
|
[^] |
Tout caractère en dehors de l'intervalle ([^a-f]) ou de l'ensemble spécifié (^abcdef]). ^ représente le NOT |
L’opérateur LIKE est extrêmement puissant pour réaliser des recherches sur des chaînes avec des valeurs approximatives.
Quelques exemples illustrant son utilisation :
-- Liste des Pilotes dont le nom contient la chaîne el
--Liste des Pilotes dont le 3ème caractère du nom est un r
-- Liste des Pilotes dont le nom comporte les caractères u ou c
-- Liste des Pilotes dont le nom commence par le caractère b ou s
-- Liste des Pilotes dont le nom commence par le caractère m ou l et se termine par l
Le résultat sera affiché de la manière suivante :
Pour la ville de départ :
Le nombre de voyages est :
Le résultat sera affiché de la manière suivante :
Pour le voyage numéro :
Le nombre de billet est :
Le résultat sera affiché de la manière suivante :
Nom et prénom :
Date recrutement :
Le résultat sera affiché de la manière suivante :
Immatricule :
Marque :
Type :
Date de mise en service :
C. Dans la base de données “vol avion”
Le résultat sera affiché de la manière suivante :
Le nombre de pilotes existants dans la base est :
Le résultat sera affiché de la manière suivante :
Nom :
Date de début d’activité :
Ville :
a. Affiche le salaire d’un pilote et le salaire moyen des pilotes qui habitent la même ville
Le résultat sera affiché de la manière suivante :
Le salaire du pilote est :
Le salaire moyen des pilotes qui habitent la même ville :
Effectue la mise à jour du salaire d’un pilote :
Le résultat sera affiché de la manière suivante : le salaire initial: Le salaire résultant :
Tester le programme avec les différents scénarios possibles (pilote inexistant, pilote existant avec salaire < salaire moyen, pilote existant avec salaire > salaire moyen).
Ajouter un commentaire
Veuillez vous connecter pour ajouter un commentaire.
Pas encore de commentaires.