chapitre 7 langage sql -...

39
Chapitre 7 Langage SQL I. Introduction SQL (Structured Query Language, ou langage de requêtes structurées) est l'interface externe la plus répandue pour gérer une base de données. C'est un langage déclaratif dans le sens où il suffit d'exprimer ce que l'on veut obtenir, sans devoir expliquer la méthode pour y parvenir. Ainsi, les opérations directement utilisables par les usagers sont en général celles des langages dits assertionnels. Ces langages, bâtis sur l'algèbre relationnelle, permettent de manipuler des bases de données relationnelles. Nous pouvons citer à titre d'exemple : - QUEL (QUEry Language) par Zook en 1977 - QBE (Query By Example) par Zloof en 1977 - SQL (Structured Query Language) par IBM Aujourd'hui, le langage SQL est normalisé et constitue le standard d'accès aux bases de données relationnelles. Les autres interfaces par menus, fenêtres, grilles ou de programmation type langage de 3ème ou 4ème génération (L3G et L4G) sont le plus souvent offertes au-dessus du langage SQL. Celui-ci constitue donc le point d'entrée des SGBD relationnels. Il a été normalisé à quatre reprises (ISO et ANSI sont des organismes chargés de la normalisation) : - 1986 : SQL 86 - ANSI* - 1989 : - ISO* et ANSI - 1992 : SQL 2 - ISO et ANSI - 1999 : SQL 3 - ISO II. Présentation de SQL SQL est un langage de gestion de bases de données relationnelles. Il étend l'algèbre relationnelle et permet : - de créer des bases de données - d'ajouter, modifier et consulter des données d'une base de données existante - de contrôler les accès aux informations d'une BD SQL est un langage ensembliste qui : - respecte l’indépendance des niveaux - garantit la sécurité - permet une gestion multi-utilisateurs - utilise un dictionnaire - respecte les contraintes d’intégrité - est portable (standardisation) SQL étend l'algèbre relationnelle, cependant il utilise les termes table, ligne et colonne au lieu des termes relationnels relation, tuple et attribut. Une instruction SQL peut s'écrire sur plusieurs lignes. Pour être exécutée, l'instruction doit se terminer par un point-virgule. SQL est un langage de requête, pas un langage de programmation. Par conséquent, il n'y pas de variables ni de structures de contrôles (if, while, for). C'est pourquoi on peut éventuellement l'intégrer dans un langage de programmation comme PL/SQL, C ou Java. III. Les 3 facettes de SQL SQL se subdivise en 3 sous-langages : - LDD (Langage de Définition de Données) : création, modification et suppression des objets que peut manipuler une BD (tables, vues et index, etc). - LMD (Langage de Manipulation de Données) : ajout, suppression, modification et extraction des données. - LCD (Langage de Contrôle de Données) : sécurisation et validation des données.

Upload: trannhi

Post on 17-Jun-2018

243 views

Category:

Documents


2 download

TRANSCRIPT

Page 1: Chapitre 7 Langage SQL - x.heurtebise.free.frx.heurtebise.free.fr/Enseignements/Monitorat/L3/Info/BDD/PDF/BDD... · SQL (Structured Query Language, ou langage de requêtes structurées)

Chapitre 7 Langage SQL

I. Introduction SQL (Structured Query Language, ou langage de requêtes structurées) est l'interface externe la plus répandue pour gérer une base de données. C'est un langage déclaratif dans le sens où il suffit d'exprimer ce que l'on veut obtenir, sans devoir expliquer la méthode pour y parvenir. Ainsi, les opérations directement utilisables par les usagers sont en général celles des langages dits assertionnels.

Ces langages, bâtis sur l'algèbre relationnelle, permettent de manipuler des bases de données relationnelles. Nous pouvons citer à titre d'exemple : - QUEL (QUEry Language) par Zook en 1977 - QBE (Query By Example) par Zloof en 1977 - SQL (Structured Query Language) par IBM

Aujourd'hui, le langage SQL est normalisé et constitue le standard d'accès aux bases de données relationnelles. Les autres interfaces par menus, fenêtres, grilles ou de programmation type langage de 3ème ou 4ème génération (L3G et L4G) sont le plus souvent offertes au-dessus du langage SQL. Celui-ci constitue donc le point d'entrée des SGBD relationnels. Il a été normalisé à quatre reprises (ISO et ANSI sont des organismes chargés de la normalisation) :

- 1986 : SQL 86 - ANSI* - 1989 : - ISO* et ANSI - 1992 : SQL 2 - ISO et ANSI - 1999 : SQL 3 - ISO

II. Présentation de SQL SQL est un langage de gestion de bases de données relationnelles. Il étend l'algèbre relationnelle et permet : - de créer des bases de données - d'ajouter, modifier et consulter des données d'une base de données existante - de contrôler les accès aux informations d'une BD

SQL est un langage ensembliste qui : - respecte l’indépendance des niveaux - garantit la sécurité - permet une gestion multi-utilisateurs - utilise un dictionnaire - respecte les contraintes d’intégrité - est portable (standardisation)

SQL étend l'algèbre relationnelle, cependant il utilise les termes table, ligne et colonne au lieu des termes relationnels relation, tuple et attribut. Une instruction SQL peut s'écrire sur plusieurs lignes. Pour être exécutée, l'instruction doit se terminer par un point-virgule. SQL est un langage de requête, pas un langage de programmation. Par conséquent, il n'y pas de variables ni de structures de contrôles (if, while, for). C'est pourquoi on peut éventuellement l'intégrer dans un langage de programmation comme PL/SQL, C ou Java.

III. Les 3 facettes de SQL SQL se subdivise en 3 sous-langages : - LDD (Langage de Définition de Données) : création, modification et suppression des objets que peut

manipuler une BD (tables, vues et index, etc). - LMD (Langage de Manipulation de Données) : ajout, suppression, modification et extraction des

données. - LCD (Langage de Contrôle de Données) : sécurisation et validation des données.

Page 2: Chapitre 7 Langage SQL - x.heurtebise.free.frx.heurtebise.free.fr/Enseignements/Monitorat/L3/Info/BDD/PDF/BDD... · SQL (Structured Query Language, ou langage de requêtes structurées)

Base de données 37

Chacun de ces sous-langages propose ses mots-clés propres. Voici les principales primitives que nous verrons au cours de ce chapitre :

LDD LMD LCD CREATEALTERDROP

SELECTINSERTUPDATEDELETE

GRANT REVOKE COMMIT

ROLLBACK

IV. Le Langage de Définition de Données (LDD) Définition : Le langage de définition de données permet la définition du schéma d'une base de données ainsi que certaines de ses contraintes d'intégrité. Il comporte également les opérations de mise à jour de schéma telles que la suppression d'une table ou la modification de la définition d'une table.

Le LDD est donc composé de 3 primitives : - CREATE pour la création d'un objet (table, index, vue, etc). - ALTER pour la modification d'un objet. - DROP pour la suppression d'un objet.

1. Création de table La création de table se fait grâce au mot-clé CREATE TABLE.

Chaque table est définie par : - son nom - sa liste d'attributs - sa liste de contraintes sur table

Chaque attribut est défini par : - son type - sa valeur par défaut - sa liste des contraintes sur attribut

Le type des attributs est à choisir parmi un ensemble fini de types. Les principaux types possibles sont selon les standards: - VARCHAR2(n) : chaîne de caractères de longueur variable (maximum n) - CHAR(n) : chaîne de caractères de longueur fixe (n caractères) - NUMBER : nombre entier (40 chiffres au maximum) - NUMBER(n,m) : nombre entier de longueur totale n avec m décimales - INTEGER : entier - FLOAT : réel - DATE : date (DD-MON-YY est le format par défaut) - ...

Syntaxe de définition d'une table en SQL

CREATE TABLE nom_table

(attribut1 type1 [DEFAULT valeur_par_défaut] [contrainte sur attribut1], …, attributN typeN [DEFAULT valeur_par_défaut] [contrainte sur attributN],

CONSTRAINT contrainte1 sur relation), …, CONSTRAINT contrainteM sur relation);

Page 3: Chapitre 7 Langage SQL - x.heurtebise.free.frx.heurtebise.free.fr/Enseignements/Monitorat/L3/Info/BDD/PDF/BDD... · SQL (Structured Query Language, ou langage de requêtes structurées)

Base de données 38

Notation (les éléments entre [ ] sont optionnels) : - Valeur_par_défaut doit être compatible avec le type de l'attribut. Elle peut prendre une des valeurs

parmi : o constante o NULL

- Contrainte sur attribut décrit les contraintes mono-attributs. Elle peut prendre une valeur parmi : o NOT NULL o UNIQUE o REFERENCES nom_de_relation2 (pour les clés étrangères) o CHECK (expression logique) o PRIMARY KEY

Les contraintes sur attribut restreignent les valeurs d'un attribut (ex : l'âge d'une personne – attribut Age – ne peut pas être négatif et ne dépasse pas 130 ans). Remarque : S'il y a plusieurs contraintes sur un attribut, elles sont séparées par des virgules.

- Contrainte sur relation décrit les contraintes multi-attributs. Elle peut prendre une valeur parmi : o UNIQUE (liste d'attributs) o PRIMARY KEY (liste d'attributs) o FOREIGN KEY (liste d'attributs) REFERENCES nom_de_relation2 (liste d'attributs)

S'il y a plusieurs contraintes multi-attribut, elles sont séparées par des virgules.

Les contraintes sur relation définissent des règles sémantiques entre plusieurs attributs. Il n'y a pas d'ordre dans la définition des attributs ou des contraintes sur relation.

Les définitions d'attributs et de contraintes sont séparées par des virgules. Chaque définition de relation se termine par un point-virgule.

Il est recommandé de donner des noms de contraintes explicites (après le mot clé CONSTRAINT) afin de retrouver plus facilement l'origine des erreurs qui pourront être signalées par le SGBD, suite à des violations de ces contraintes.

La signification des différents mots-clés (NULL, NOT NULL, UNIQUE,…) est donnée dans la suite du chapitre.

a. Règles d'écriture des contraintes Les contraintes sur attribut (C/A) ou sur relation (C/R) doivent satisfaire les règles d'écriture suivantes :

1. La clause NOT NULL dans les C/A et la valeur par défaut NULL sont incompatibles. Un attribut ne peut pas être NULL et NOT NULL en même temps.

2. La clause UNIQUE (aussi bien dans les C/A que dans les C/R) ne peut être spécifiée que sur des attributs déclarés NOT NULL.

3. L'expression logique de la condition CHECK ne peut contenir que des prédicats de comparaison d'attributs avec des constantes. La comparaison avec d'autres attributs ou l'usage de fonctions ou de sous-requêtes ne sont pas autorisés.

4. Les attributs de la clause PRIMARY KEY doivent être préalablement déclarés avec la clause NOT NULL.

5. Dans les contraintes de relation, les clauses FOREIGN KEY et REFERENCES doivent apparaître simultanément

6. Les attributs de la clause FOREIGN KEY doivent correspondre soit à des attributs déclarés comme PRIMARY KEY dans une autre relation, soit à des attributs déclarés UNIQUE dans une autre table. La première option correspond à une contrainte référentielle avec la clé primaire, la seconde correspond à une contrainte référentielle avec une clé candidate.

7. Si les attributs de FOREIGN KEY correspondent à une clé primaire dans une autre relation, la clause REFERENCES ne spécifie que le nom de cette dernière.

Page 4: Chapitre 7 Langage SQL - x.heurtebise.free.frx.heurtebise.free.fr/Enseignements/Monitorat/L3/Info/BDD/PDF/BDD... · SQL (Structured Query Language, ou langage de requêtes structurées)

Base de données 39

8. Si les attributs de FOREIGN KEY correspondent à une clé candidate dans une autre relation, la clause REFERENCES doit mentionner le nom de cette relation ainsi que la liste des attributs composant la clé candidate.

9. Les clauses PRIMARY KEY, UNIQUE et NOT NULL sont redondantes lorsqu'il n'y a qu'une clé par relation. L'une ou l'autre des clauses suffit à la spécifier. Selon la clause utilisée, il faut assurer la cohérence de la définition des contraintes référentielles en utilisant respectivement la règle 7 ou la règle 8.

10. Il faut enfin noter que l'ordre des CREATE TABLE peut être très important par rapport aux contraintes : les tables référencées doivent apparaître avant les tables référençantes.

b. Exemple

CREATE TABLE VOITURE ( Immatriculation VARCHAR(8), Marque VARCHAR(20), Couleur VARCHAR(15), Prix INTEGER CONSTRAINT C1 CHECK (Prix>=0), CONSTRAINT C2 PRIMARY KEY (Immatriculation) );

Cette instruction SQL crée la table VOITURE suivante :

Immatriculation Marque Couleur Prix Texte Texte Texte Nombre

On aperçoit qu'on a inclut une contrainte sur l'attribut Prix, à savoir qu'un prix ne peut pas être négatif. On a également indiqué que le numéro d'immatriculation est la clé primaire de la relation VOITURE.

CREATE TABLE PERSONNE ( NuméroSS INTEGER NOT NULL UNIQUE, Nom VARCHAR ), (30 Prénom VARCHAR 20), ( Adresse VARCHAR(80) );

Cette instruction SQL crée la table PERSONNE suivante :

NuméroSS Nom Prénom AdresseNombre Texte Texte Texte

Ici, on a déclaré la clé primaire NuméroSS, d'une autre manière que précédemment : dans la relation VOITURE, la clé primaire avait été déclarée grâce à la contrainte de relation PRIMARY KEY alors qu'ici, nous l'avons déclarée grâce aux contraintes sur attribut NOT NULL et UNIQUE.

Page 5: Chapitre 7 Langage SQL - x.heurtebise.free.frx.heurtebise.free.fr/Enseignements/Monitorat/L3/Info/BDD/PDF/BDD... · SQL (Structured Query Language, ou langage de requêtes structurées)

Base de données 40

CREATE TABLE ACHAT ( numImmatriculation VARCHAR(8), numPersonn INTEGER, e dateAchat DATE, CONSTRAINT C3 PRIMARY KEY (numImmatriculation, numPersonne), CONSTRAINT C4 FOREIGN KEY (numImmatriculation) REFERENCES VOITURE(Immatriculation), CONSTRAINT C5 FOREIGN KEY (numPersonne) REFERENCES Personne(NuméroSS) );

Cette instruction SQL crée la table ACHAT suivante :

numImmatriculation numPersonne dateAchatTexte Nombre Date

Dans cette instruction, nous avons déclaré dans la contrainte C3, la clé primaire (clé composée de numImmatriculation et numPersonne) et, dans les contraintes C4 et C5, les dépendances avec les tables VOITURE et PERSONNE via les clés étrangères.

c. Définition d'une table par requête Il est possible de créer une table par récupération des tuples d'une autre table. On peut, par exemple, créer une table appelée ma_table par récupération de la structure et du contenu de autre_table :

CREATE TABLE ma_table as SELECT * FROM autre_table;

On peut également créer une table appelée ma_table par récupération d'une partie seulement de la structure et du contenu de ACHAT. On renomme dans l'exemple ci-dessous les colonnes numImmatriculation et nomPersonne en num et nom :

CREATE TABLE ma_table ( num integer, nom varchar(20) ) as SELECT numImmatriculation, nomPersonne FROM ACHAT;

2. Modification d'une table La modification d'une table se fait grâce au mot-clé ALTER TABLE.

Les principales modifications sur la structure d'une table sont : - l'ajout d’un attribut - la modification d'un attribut - la suppression d'un attribut

Cependant, la modification de schéma est une opération délicate qui peut mettre en péril l'intégrité de la base de données. Aussi toutes les opérations de suppression ou de modification de relation, d'attribut ou de contrainte doivent être faites avec précaution. Pour éviter les erreurs, la plupart des systèmes

Page 6: Chapitre 7 Langage SQL - x.heurtebise.free.frx.heurtebise.free.fr/Enseignements/Monitorat/L3/Info/BDD/PDF/BDD... · SQL (Structured Query Language, ou langage de requêtes structurées)

Base de données 41

restreignent ou interdisent certaines de ces opérations. La syntaxe suivante d'ORACLE donne un aperçu de ces opérations.

Syntaxe de modification d'une table en SQL

ALTER TABLE nom_table [ ADD (attribut1 type1 [DEFAULT valeur_par_défaut] [contraintes sur attribut1], ..., attributN typeN [DEFAULT valeur_par_défaut] [contraintes sur attributN]); ] | [ADD (contrainte sur relation);] | [ MODIFY (attribut1 type1 [DEFAULT valeur_par_défaut] [contraintes sur attribut1], ..., attributN typeN [DEFAULT valeur_par_défaut] [contraintes sur attributN]); ] | [DROP CONSTRAINT nom_de_contrainte;] | DROP COLUMN nom_de_colonne;

Notation (les éléments entre [ ] sont optionnels) : - Le symbole "|" signifie "ou" ; ainsi dans l'instruction ALTER TABLE, ADD porte soit sur une

contrainte d'attribut soit sur une contrainte de relation. - Le mot-clé ADD permet donc d'ajouter soit un attribut et éventuellement les contraintes associées

à cet attribut, soit une contrainte sur la relation. - Le mot-clé MODIFY permet de modifier la définition d'un attribut. - Le mot-clé DROP CONSTRAINT permet de supprimer une contrainte sur relation. - Le mot-clé DROP COLUMN permet de supprimer une colonne (et donc un champ).

a. Règles Dans une table ayant déjà des tuples, l'ajout d'un attribut implique la mise à NULL de toute la colonne correspondant à cet attribut. Ceci implique que la clause NOT NULL ne doit pas être spécifiée pour cet attribut dans sa définition.

La suppression d'un attribut doit obéir aux quelques règles suivantes : - On ne peut supprimer un attribut-clé ou faisant partie de la clé. - La suppression d'une clé étrangère mono-attribut (une référence à la clé d'une autre table)

entraîne automatiquement la suppression de la contrainte référentielle correspondante (puisque cette contrainte est définie en même temps que l'attribut).

- La suppression d'une clé étrangère multi-attribut doit être précédée de la suppression de la contrainte référentielle correspondante (puisque cette dernière est déclarée à part). Sans cette suppression de la contrainte, toute mise à jour ultérieure de ces attributs peut entraîner une anomalie dans le contrôle de l'intégrité référentielle.

La modification d'un attribut doit respecter les règles suivantes : - Si le nom d'une clé change, il faut propager cette modification sur toutes les tables qui

référencent cette clé. - Toute modification d'une contrainte sur attribut doit respecter les règles de cohérence des

contraintes 1 à 9 spécifiées dans la leçon relative à la création de table.

Page 7: Chapitre 7 Langage SQL - x.heurtebise.free.frx.heurtebise.free.fr/Enseignements/Monitorat/L3/Info/BDD/PDF/BDD... · SQL (Structured Query Language, ou langage de requêtes structurées)

Base de données 42

La suppression de contrainte est une relaxation de contrainte qui rend les futures requêtes plus permissives qu'elles ne l'étaient avant. Cette suppression ne met pas particulièrement en péril l'intégrité de la base. - Toute suppression de contrainte doit conserver les règles sur contraintes 1 à 9 spécifiées

dans la leçon relative à la création de table.

La suppression de contrainte nécessite que cette contrainte possède un nom.

b. Exemple On désire ajouter les attributs Type et Puissance à la table VOITURE. Voici l'instruction SQL permettant d'ajouter ces attributs.

ALTER TABLE VOITURE ADD(type VARCHAR(20), puissance INTEGER);

Cette instruction SQL modifie la table VOITURE en conséquence :

Immatriculation Marque Couleur Prix Type Puissance Texte Texte Texte Nombre Texte Nombre

On souhaite désormais modifier la contrainte portant sur l'attribut Puissance. En effet, nous aimerions contraindre cette valeur pour qu'elle soit positive. Voici l'instruction SQL résultante :

ALTER TABLE VOITURE ADD (CONSTRAINT C8 (CHECK (Puissance>=0)));

3. Suppression d'une table La suppression d'une table se fait grâce au mot-clé DROP TABLE.

Syntaxe de suppression d'une table en SQL

DROP TABLE nom_table [CASCADE CONSTRAINTS];

Notation (les éléments entre [ ] sont optionnels) : Le mot-clé CASCADE CONSTRAINTS dans la clause DROP TABLE permet de supprimer toutes les références à cette table dans les autres tables.

a. Règles L'opération de suppression de table peut se faire moyennant certaines précautions : - Avant de supprimer une table, il faut supprimer toutes les références à cette table dans les

autres tables (ou utiliser le CASCADE CONSTRAINTS). - Avant de supprimer une table, il faut avoir redéfini ou supprimé toutes les vues impliquant

cette table (nous reviendrons un peu plus loin sur la définition des vues).

La suppression d'une table implique de redéfinir ou supprimer toutes les requêtes impliquant la table supprimée.

b. Exemple On désire supprimer la table VOITURE. Voici l'instruction SQL permettant d'effectuer cette requête :

DROP TABLE VOITURE CASCADE CONSTRAINTS;

Page 8: Chapitre 7 Langage SQL - x.heurtebise.free.frx.heurtebise.free.fr/Enseignements/Monitorat/L3/Info/BDD/PDF/BDD... · SQL (Structured Query Language, ou langage de requêtes structurées)

Base de données 43

Remarque : on insère la clause CASCADE CONSTRAINTS afin de supprimer les références à la table VOITURE dans la table ACHAT.

4. Renommage d’une table Le renommage d'une table se fait grâce au mot-clé RENAME.

Syntaxe de renommage d'une table en SQL

RENAME nom_table TO nouvelle_table;

5. Consultation d’une table La consultation d'une table se fait grâce au mot-clé DESC[RIBE].

Syntaxe de consultation d'une table en SQL

DESC[RIBE] nom_table;

V. Le Langage de Manipulation de Données (LMD) Définition : Le Langage de Manipulation de Données permet de : - afficher ; - insérer ; - mettre à jour ; - supprimer des données dans les tables.

Le LMD est donc composé de 4 primitives : - SELECT pour l'affichage des données des tables. - INSERT pour l'insertion de données dans les tables. - UPDATE pour la mise à jour des données. - DELETE pour la suppression des données.

1. SELECT L'affichage des données d'une ou plusieurs tables se fait grâce au mot-clé SELECT.

Syntaxe de la primitive SELECT en SQL

SELECT * | [DISTINCT | ALL] <attributs> FROM <tables> [WHERE <condition logique de niveau tuple>] [GROUP BY <attributs> [HAVING <condition logique de niveau groupe>]] [ORDER BY <attributs>];

Notation (les éléments entre [ ] sont optionnels) : - Le symbole "|" signifie "ou" ; ainsi on peut faire suivre le mot-clé SELECT par une * OU par

<attributs> précédé le cas échéant par DISTINCT ou ALL.

La clause SELECT décrit la relation résultat, <attributs> désigne : - soit une liste d'attributs ; - soit une expression obtenue à l'aide des fonctions statistiques SUM (somme), AVG (moyenne),

COUNT (compte), MIN et MAX ; - soit une expression.

Page 9: Chapitre 7 Langage SQL - x.heurtebise.free.frx.heurtebise.free.fr/Enseignements/Monitorat/L3/Info/BDD/PDF/BDD... · SQL (Structured Query Language, ou langage de requêtes structurées)

Base de données 44

Les mots-clés DISTINCT et ALL permettent respectivement d'éliminer ou de conserver les doublons après une projection mise en œuvre par l'ordre SELECT suivi d'une liste d'attributs.

La clause FROM désigne les relations concernées par la requête. Chaque nom de relation peut être suivi d'un alias (variables synonymes pour chaque relation). Cet alias permet parfois des économies d'écritures, mais il permet surtout de lever des ambiguïtés sur les noms d'attributs (plusieurs relations qui possèdent le même nom d'attribut). Des exemples de définition d'alias sont proposés dans la partie présentant des exercices corrigés.

La clause WHERE, optionnelle, spécifie les critères de sélection. La condition logique de niveau tuple est une expression logique spécifiant les prédicats de restriction ou de jointure à satisfaire par la réponse. On peut également insérer dans la clause WHERE, une nouvelle instruction SELECT (requête imbriquée) ce qui permet de travailler sur un ensemble de tuples restreints par cette sélection.

Lorsque ces critères portent sur des fonctions de groupe, on utilise la clause HAVING.

La clause GROUP BY partitionne la relation en groupes. La liste des attributs spécifiés dans cette clause indique le critère de groupement.

La clause ORDER BY permet de trier les résultats obtenus selon un tri ascendant (ASC) ou descendant (DESC). On veillera à choisir des colonnes de tri présent dans les attributs du SELECT.

a. La clause SELECT et la clause FROM La clause SELECT et la clause FROM sont liées. En effet, lors de chaque opération de sélection, le mot-clé FROM permet d'identifier la ou les tables nécessaires à la construction du résultat de la requête.

SELECT * FROM VOITURE;

Cette instruction SQL permet d'afficher tous les tuples de la table VOITURE :

Immatriculation Marque Couleur Puissance Prix 4578QS59 Renault Noire 7 20 000 1952LM62 Rover Bleue 5 28 000 3664PN75 Citroën Rouge 6 30 000 3671KH60 Peugeot Verte 6 10 000 4691TR95 Renault Jaune 7 35 000

Remarque : le symbole * dans la clause SELECT signifie qu'on désire récupérer TOUS les champs de la table.

SELECT Marque FROM VOITURE;

Cette instruction SQL permet d'afficher le contenu du champ Marque des tuples de la table VOITURE :

MarqueRenault Rover

Citroën Peugeot Renault

Remarque : la requête ne porte que sur un seul champ, cela équivaut à une projection de la table VOITURE sur ce champ. On peut ajouter autant d’attributs que l’on désire afficher.

SELECT DISTINCT Marque FROM VOITURE;

Page 10: Chapitre 7 Langage SQL - x.heurtebise.free.frx.heurtebise.free.fr/Enseignements/Monitorat/L3/Info/BDD/PDF/BDD... · SQL (Structured Query Language, ou langage de requêtes structurées)

Base de données 45

Cette instruction SQL permet d'afficher le contenu du champ Marque des tuples de la table VOITURE, sans répétition de Marque, c'est-à-dire sans doublon de Marque :

MarqueRenault Rover

Citroën Peugeot

b. Les prédicats Une condition est appelée prédicat en SQL. Un prédicat permet de comparer 2 expressions de valeurs : - la première expression contenant des spécifications de colonnes est appelée terme ; - la seconde expression contenant seulement des spécifications de constantes est appelée

constante.

Il existe une grande diversité de prédicats en SQL, on trouve en effet :

1. un prédicat de comparaison permettant de comparer un terme à une constante à l'aide des opérateurs suivants : - = : égal - <> ou != : différent - > : plus grand que - >= : plus grand ou égal - < : plus petit que - <= : plus petit ou égal Ces opérateurs sont valables pour les types NUMBER (NUMBER est un terme englobant les types INTEGER et FLOAT désignant les nombres, ce type n'est cependant pas disponible pour tous les SGBD), CHAR, VARCHAR, DATE et valent NULL si un des termes de la comparaison est NULL.

2. un prédicat d'intervalle BETWEEN permettant de tester si la valeur d'un terme est comprise entre la valeur de 2 constantes ;

3. un prédicat de comparaison de texte noté LIKE permettant de tester si un terme de type chaîne de caractères contient une ou plusieurs sous-chaînes (ce prédicat est donc réservé aux types CHAR et VARCHAR). Le caractère % remplace une chaîne de caractères quelconque y compris la chaîne vide, le caractère _ remplace n'importe quel caractère unique ;

4. un prédicat de test de nullité qui permet de tester si un terme a une valeur convenue NULL, signifiant que sa valeur est inconnue ou que le champ n'a pas été renseigné. Pour tester la nullité, on utilise IS NULL, et la non nullité par IS NOT NULL ;

5. un prédicat d'appartenance noté IN qui permet de tester si la valeur d'un terme appartient à une liste de valeurs constantes.

c. La clause WHERE La clause WHERE spécifie une condition de sélection. Une condition de sélection définit un critère qui, appliqué à un tuple, est vrai, faux ou inconnu (mettant en cause un attribut de valeur NULL). Cette condition peut inclure des opérateurs booléens (AND, OR, NOT), des conditions élémentaires et des parenthèses.

Les tableaux ci-après donnent les tables de vérité permettant de calculer les valeurs de vérité d'une condition de sélection. Seuls les tuples satisfaisant la condition de sélection sont pris en compte par la requête.

AND Vrai Faux InconnuVrai Vrai Faux Inconnu Faux Faux Faux Faux

Inconnu Inconnu Faux Inconnu

Page 11: Chapitre 7 Langage SQL - x.heurtebise.free.frx.heurtebise.free.fr/Enseignements/Monitorat/L3/Info/BDD/PDF/BDD... · SQL (Structured Query Language, ou langage de requêtes structurées)

Base de données 46

OR Vrai Faux InconnuVrai Vrai Vrai Vrai Faux Vrai Faux Faux

Inconnu Vrai Faux Inconnu

NOT Vrai Faux Inconnu Faux Vrai Inconnu

SELECT * FROM VOITURE WHERE Puissance <= 6 AND Prix >= 20 000;

Cette instruction SQL permet d’affiche les tuples de la table VOITURE dont la Puissance est inférieure ou égale à 6 chevaux et dont le prix est supérieur ou égal à 20 000 € :

Immatriculation Marque Couleur Puissance Prix 1952LM62 Rover Bleue 5 28 000 3664PN75 Citroën Rouge 6 30 000

AND : permet de vérifier TOUTES les conditions simultanément.

SELECT * FROM VOITURE WHERE Puissance <= 6 OR Prix >= 20 000;

Cette instruction SQL permet d’affiche les tuples de la table VOITURE dont la Puissance est inférieure ou égale à 6 chevaux ou dont le prix est supérieur ou égal à 20 000 € :

Immatriculation Marque Couleur Puissance Prix 4578QS59 Renault Noire 7 20 000 1952LM62 Rover Bleue 5 28 000 3664PN75 Citroën Rouge 6 30 000 3671KH60 Peugeot Verte 6 10 000 4691TR95 Renault Jaune 7 35 000

OR : permet de vérifier AU MOINS UNE des conditions.

SELECT * FROM VOITURE WHERE NOT (Puissance = 6);

Cette instruction SQL permet d’affiche les tuples de la table VOITURE dont la Puissance n’est égale à 6 chevaux :

Immatriculation Marque Couleur Puissance Prix 4578QS59 Renault Noire 7 20 000 1952LM62 Rover Bleue 5 28 000 4691TR95 Renault Jaune 7 35 000

NOT : permet de donner la négation de la condition.

Remarque : Les opérateurs logiques peuvent être combinés entre eux. Cependant, il faut veiller à respecter un ordre de priorité. Ainsi, on trouve, du plus prioritaire au moins prioritaire : - NOT - AND - OR L'emploi de parenthèses est possible pour changer l'ordre d’évaluation ou rendre la lecture plus facile.

Page 12: Chapitre 7 Langage SQL - x.heurtebise.free.frx.heurtebise.free.fr/Enseignements/Monitorat/L3/Info/BDD/PDF/BDD... · SQL (Structured Query Language, ou langage de requêtes structurées)

Base de données 47

Prédicats divers :

SELECT * FROM VOITURE WHERE Prix BETWEEN 25 000 AND 40 000;

Cette instruction SQL permet d’afficher les tuples de la table VOITURE dont le Prix est compris entre 25 000€ et 40 000€ :

Immatriculation Marque Couleur Puissance Prix 1952LM62 Rover Bleue 5 28 000 3664PN75 Citroën Rouge 6 30 000 4691TR95 Renault Jaune 7 35 000

BETWEEN min AND max : permet de récupérer les données dont les valeurs sont comprises entre min et max (bornes incluses).

SELECT FROM VOI * TURE WHERE Marque LIKE 'Ren%' AND Couleur IN ('Noire', 'Bleue', 'Jaune') AND Prix IS NOT NULL;

Cette instruction SQL permet d’afficher tous les tuples de la table VOITURE dont le Marque commence par ‘Ren’, dont la couleur est soit noire, bleue ou jaune, et dont le prix n’est pas inconnu (donc connu) :

Immatriculation Marque Couleur Puissance Prix 4578QS59 Renault Noire 7 20 000 4691TR95 Renault Jaune 7 35 000

d. Les opérateurs arithmétiques Les opérateurs arithmétiques ('+', '−', '×', '/') peuvent être utilisés dans les clauses SELECT, WHERE et HAVING.

Ces opérateurs sont valables pour les types NUMBER et DATE. Si une des opérandes est NULL, le résultat est NULL. Ces opérateurs peuvent être combinés entre eux mais respectent une priorité d'évaluation : - '×' et '/' - '+' et '−'

Cet ordre peut, bien entendu, être modifié à l'aide de parenthèses.

e. Les fonctions statistiques Les fonctions statistiques (MAX, MIN, SUM, AVG, COUNT, STDDEV, VARIANCE) peuvent être utilisées dans les clauses SELECT, WHERE et HAVING. Les fonctions statistiques sont appliquées à l'ensemble d'une colonne (ou d'un groupe) et fournissent une valeur unique.

La fonction MAX et la fonction MIN Les fonctions MAX et MIN renvoient respectivement le maximum et le minimum d'un champ. Ces fonctions sont utilisables pour les types CHAR, VARCHAR2, DATE et NUMBER.

SELECT MAX(Prix) FROM VOITURE;

Cette requête permet d'afficher le prix maximal parmi les tuples de la table VOITURE.

MAX(Prix)35 000

Page 13: Chapitre 7 Langage SQL - x.heurtebise.free.frx.heurtebise.free.fr/Enseignements/Monitorat/L3/Info/BDD/PDF/BDD... · SQL (Structured Query Language, ou langage de requêtes structurées)

Base de données 48

La fonction COUNT La fonction COUNT comptabilise le nombre de lignes pour lesquelles l'expression est non NULL. Une * est souvent utilisée avec COUNT pour indiquer le nombre de lignes. Si on ne veut pas compter plusieurs fois les valeurs identiques d'une même colonne, il faut utiliser le mot-clé DISTINCT suivi du nom de la colonne (sauf s'il s'agit d'un attribut clé de la relation).

SELECT COUNT(DISTINCT Marque) FROM VOITURE WHERE Puissance > 6;

Cette requête permet d'afficher le nombre de tuples vérifiant la condition "Puissance supérieure à 6 chevaux" de la table VOITURE, en ne comptant qu'une seule fois la marque même si elle vérifie de nouveau la condition (3 tuples comptés, on ne compte qu'une fois la Renault).

COUNT(Marque)1

SELECT COUNT(*) FROM VOITURE WHERE Puissance > 6;

Cette requête permet d'afficher le nombre de tuples vérifiant la condition "Puissance supérieure à 6 chevaux" de la table VOITURE, en comptant toutes les lignes (4 tuples comptés).

COUNT(Marque)2

La fonction SUM La fonction SUM effectue, pour un ensemble de tuples, la somme des valeurs d'un attribut. Cette fonction est uniquement utilisable pour le type NUMBER.

SELECT SUM(Prix) FROM VOITURE WHERE Puissance = 6;

Cette requête permet de calculer la somme des prix des VOITURES ayant une Puissance de 6 chevaux.

SUM(Prix)45 000

La fonction AVG La fonction AVG calcule, pour un ensemble de tuples, la moyenne arithmétique des valeurs d'un attribut. Cette fonction est uniquement utilisable pour le type NUMBER. Elle vérifie la formule suivante : AVG = Somme des valeurs non NULL / nombre de valeurs non NULL.

SELECT AVG(Prix) FROM VOITURE WHERE Puissance = 6;

Cette requête permet de calculer le prix moyen des VOITURES ayant une Puissance de 6 chevaux.

AVG(Prix)20 000

La fonction VARIANCE La fonction VARIANCE calcule, pour un ensemble de tuples, la variance des valeurs d'un attribut. Cette fonction est uniquement utilisable pour le type NUMBER. Elle vérifie la formule suivante : VARIANCE = Somme des carrés de la différence entre attribut de valeur non NULL et la moyenne arithétique / nombre d’attributs de valeur non NULL.

Page 14: Chapitre 7 Langage SQL - x.heurtebise.free.frx.heurtebise.free.fr/Enseignements/Monitorat/L3/Info/BDD/PDF/BDD... · SQL (Structured Query Language, ou langage de requêtes structurées)

Base de données 49

SELECT VARIANCE(Prix) FROM VOITURE WHERE Puissance = 6;

Cette requête permet de calculer la variance des prix des VOITURES ayant une Puissance de 6 chevaux.

VARIANCE(Prix)100 000 000

La fonction STDDEV La fonction STDDEV calcule, pour un ensemble de tuples, l’écart type des valeurs d'un attribut. Cette fonction est uniquement utilisable pour le type NUMBER. Elle vérifie la formule suivante : STDDEV = racine carrée de la variance.

SELECT STDDEV(Prix) FROM VOITURE WHERE Puissance = 6;

Cette requête permet de calculer l’écart type des prix des VOITURES ayant une Puissance de 6 chevaux.

STDDEV(Prix)10 000

f. La clause GROUP BY La clause GROUP BY permet de partitionner la relation résultat selon les valeurs d'un ou de plusieurs attributs. Les seuls noms de colonnes (en dehors des fonctions statistiques) qui peuvent apparaître dans le SELECT sont celles qui figurent dans le GROUP BY.

SELECT SUM(Prix), Puissance FROM VOITURE GROUP BY Puissance;

Cette commande permet de créer des partitions selon la Puissance dans la table VOITURE. On aura donc, pour notre exemple, 3 partitions correspondant aux puissances 5, 6 et 7 chevaux. La somme des Prix s'affichera pour chacune de ces partitions.

Puissance SUM(Prix)5 28 000 6 40 000 7 55 000

g. La clause HAVING La clause HAVING définit les conditions que les groupes doivent respecter pour être retenus, elle sélectionne les partitions désirées. Elle ne peut comprendre que des conditions dont le premier terme est une fonction statistique. La clause HAVING est aux groupes (GROUP BY) ce que la clause WHERE est aux lignes (SELECT).

SELECT MAX(Prix), Puissance FROM VOITURE GROUP BY Puissance HAVING COUNT(*)>1;

Page 15: Chapitre 7 Langage SQL - x.heurtebise.free.frx.heurtebise.free.fr/Enseignements/Monitorat/L3/Info/BDD/PDF/BDD... · SQL (Structured Query Language, ou langage de requêtes structurées)

Base de données 50

Cette commande permet de créer des partitions selon la Puissance dans la table VOITURE. Elle calculera le prix maximum sur ces partitions. La clause HAVING limite l'affichage aux partitions contenant au moins 2 éléments (tuples) : COUNT(*)>1 . On aura donc, pour notre exemple, 2 partitions correspondant aux puissances 6 et 7 chevaux (la partition relative à la Puissance de 5 chevaux n'a pas été retenue car elle ne contient pas assez d'éléments pour satisfaire la condition énoncée dans la clause HAVING).

Puissance MAX(Prix)6 30 000 7 35 000

h. La clause ORDER BY La clause ORDER BY permet de trier les tuples du résultat final. La clause ORDER BY permet d'ordonner la relation résultat sur un ou plusieurs attributs, l'ordre pouvant être croissant (grâce au mot-clé ASC) ou décroissant (grâce au mot-clé DESC), ASC étant l'ordre par défaut.

Remarque : Dans une requête SQL, la clause ORDER BY se situe juste après une clause WHERE ou après une clause GROUP BY.

SELECT * FROM VOITURE ORDER BY Puissance [ASC];

Ces 2 requêtes (avec ASC ou sans ASC) sont identiques et permettent d'afficher l'ensemble des tuples de la table VOITURE triés sur l'attribut Puissance selon un ordre croissant.

Immatriculation Marque Couleur Puissance Prix 1952LM62 Rover Bleue 5 28 000 3664PN75 Citroën Rouge 6 30 000 3671KH60 Peugeot Verte 6 10 000 4578QS59 Renault Noire 7 20 000 4691TR95 Renault Jaune 7 35 000

La clause ORDER BY permet également un tri sur plusieurs attributs : le tri sera effectué prioritairement sur le premier attribut, puis sur le second et ainsi de suite.

SELECT * FROM VOITURE ORDER BY Puissance DESC, Couleur;

Cette requête permet d'afficher l'ensemble des tuples de la table VOITURE triés sur l'attribut Puissance selon un ordre décroissant puis sur l'attribut Couleur (ordre alphabétique) selon un ordre croissant .

Immatriculation Marque Couleur Puissance Prix 4691TR95 Renault Jaune 7 35 000 4578QS59 Renault Noire 7 20 000 3664PN75 Citroën Rouge 6 30 000 3671KH60 Peugeot Verte 6 10 000 1952LM62 Rover Bleue 5 28 000

i. Les opérateurs relationnels Les opérateurs relationnels en SQL sont basés sur les opérateurs relationnels vus dans le chapitre Modèle Relationnel. En SQL, les principaux opérateurs sont UNION, INTERSECTION, MINUS (différence). Les deux relations R1 et R2 sur lesquelles sont appliqués les opérateurs UNION, INTERSECTION et MINUS doivent absolument avoir avoir le même schéma de table (nombre équivalent d'attributs et attributs identiques deux à deux).

Page 16: Chapitre 7 Langage SQL - x.heurtebise.free.frx.heurtebise.free.fr/Enseignements/Monitorat/L3/Info/BDD/PDF/BDD... · SQL (Structured Query Language, ou langage de requêtes structurées)

Base de données 51

SELECT * FROM VOITURE WHERE Puissance = 5 UNION SELECT * FROM VOITURE WHERE Puissance = 7;

Cette requête permet d'afficher l'ensemble des tuples de la table VOITURE dont la Puissance est égale à 5 ou à 7 chevaux.

Immatriculation Marque Couleur Puissance Prix 1952LM62 Rover Bleue 5 28 000 4578QS59 Renault Noire 7 20 000 4691TR95 Renault Jaune 7 35 000

j. La jointure Le procédé de la jointure en SQL est celui énoncé dans le chapitre Algèbre Relationnelle. La jointure est formulée dans la clause WHERE

Syntaxe de la primitive SELECT en SQL

SELECT <attributs> FROM <liste_de_noms_de_relations> WHERE <expression de jointure>;

Cas de l'équijointure

SELECT Immatriculation, numPersonne, dataAchat, Marque, Couleur, Puissance, Prix FROM VOITURE, ACHAT WHERE Immatriculation = numImmatriculation;

Cette requête permet de faire la jointure entre la table VOITURE et la table ACHAT ; cette jointure porte sur le numéro d'immatriculation. Cette requête renverra donc tous les tuples de la table VOITURE et de la table ACHAT qui ont même numéro d'immatriculation (on n’a pas affiché numImmatriculation).

Immatriculation numPersonne dateAchat Marque Couleur Puissance Prix 4578QS59 1591259 12/12/2001 Renault Noire 7 20 000 1952LM62 2630662 08/11/1998 Rover Bleue 5 28 000 4691TR95 1120606 05/09/1997 Renault Jaune 7 35 000

Pour les autres types de jointure (non équijointure, autojointure, jointure externe), le principe est identique, seule l'expression de jointure diffère.

Cas de la jointure naturelle

SELECT <attributs> FROM relation1 NATURAL JOIN relation2;

Cas de la thêta-jointure

SELECT <attributs> FROM relation1 JOIN relation2 ON relation1.attrbut1 = relation2.attribut2;

Page 17: Chapitre 7 Langage SQL - x.heurtebise.free.frx.heurtebise.free.fr/Enseignements/Monitorat/L3/Info/BDD/PDF/BDD... · SQL (Structured Query Language, ou langage de requêtes structurées)

Base de données 52

Cas de la clause AS (c'est-à-dire deux relations identiques qui ont le même attribut)

SELECT <attributs> FROM relation R1, relation R2 WHERE R1.attribut = R2.attribut;

k. La requête imbriquée et les quantificateurs En SQL, il est possible d'imbriquer plusieurs requêtes. L'imbrication se fait dans la clause WHERE.

SELECT * FROM ITURE VO WHERE Prix > (SELECT AVG(Prix) FROM VOITURE);

Cette requête permet d'afficher tous les tuples de la table VOITURE dont le prix est supérieur à la moyenne des prix de tous les tuples de cette même table :

Immatriculation Marque Couleur Puissance Prix 1952LM62 Rover Bleue 5 28 000 3664PN75 Citroën Rouge 6 30 000 4691TR95 Renault Jaune 7 35 000

Remarque : Toute requête de jointure peut se mettre sous forme de requête imbriquée. Mais l'inverse n'est pas vrai comme l'illustre l'exemple proposé ci-dessus.

Dans une requête imbriquée, il est possible d’ajouter un quantificateur. Il permet de faire une comparaison entre une ligne et toutes les lignes d’une table retournée par un select imbriqué. Le ALL correspond à ∀ et le SOME (ou ANY) à ∃.

SELECT * FROM COMMANDE dateCommande > ( WHERE ALL SELECT dateLivraison FROM LIVRAISON);

Cette requête permet d'afficher tous les tuples de la table COMMANDE dont la date de commande est passée après la dernière livraison.

SELECT * FROM MMANDE CO WHERE dateCommande < SOME(SELECT dateLivraison FROM LIVRAISON);

Cette requête permet d'afficher tous les tuples de la table COMMANDE dont la date de commande est passée avant la dernière livraison.

l. Les alias Les alias concourent à améliorer la lisibilité et la concision d'une requête. Il existe deux types d'alias : les alias de tables et les alias de champs. Ils peuvent également s'appliquer à une fonction d'agrégation retournant des données sous forme de colonnes.

SELECT Alias_table.nom_champ AS Alias_champ FROM nom_table AS Alias_table;

Page 18: Chapitre 7 Langage SQL - x.heurtebise.free.frx.heurtebise.free.fr/Enseignements/Monitorat/L3/Info/BDD/PDF/BDD... · SQL (Structured Query Language, ou langage de requêtes structurées)

Base de données 53

La clause AS, affectant un alias à une table ou une colonne, peut être remplacée par un simple espace blanc.

SELECT Alias_table.nom_champ Alias_champ FROM nom_table Alias_table;

Les alias de champs peuvent être des chaînes de caractères composées de mots et d'espaces. Dans ce cas, il faut placer l'alias entre simple guillemet.

SELECT Alias_table.nom_champ AS ‘Un alias de champ’ FROM nom_table AS Alias_table;

Lors de l'affichage des lignes résultantes, les alias de champs se substitueront aux noms de colonnes. De cette manière, il devient possible d'attribuer des noms de colonnes plus explicites pour les utilisateurs de la base de données.

Les alias sont particulièrement utiles dans le cadre des jointures et des requêtes imbriquées. Dans les deux cas, il devient possible de faire appel à des champs de noms identiques en les distinguant par des préfixes qui sont les alias de table.

Cas de la jointure

SELECT v.Immatrivulation, v.Marque, v.Couleur, v.Puissance, v.Prix, a.numPersonne, a.dateAchat FROM Voiture v, Achat a WHERE v.Immatriculation = a.numImmatriculation;

Cas de la jointure réflexive

SELECT v.*, a.* FROM Voiture AS v, Achat AS a WHERE v.Immatriculation = a.numImmatriculation;

Cas de la requête imbriquée

SELECT v.*, a.* FROM Voiture AS v, Achat AS a WHERE v.Immatriculation = a.numImmatriculation AND v.Prix >(SELECT AVG(Prix) FROM VOITURE);

2. INSERT L'opération d'insertion se fait grâce au mot-clé INSERT. Elle permet d'ajouter un ou plusieurs tuples à une relation.

Syntaxe de la primitive INSERT en SQL

INSERT INTO <nom_table> [attribut1, attribut2, …] VALUES (val1, val2, …) | clause SELECT;

Page 19: Chapitre 7 Langage SQL - x.heurtebise.free.frx.heurtebise.free.fr/Enseignements/Monitorat/L3/Info/BDD/PDF/BDD... · SQL (Structured Query Language, ou langage de requêtes structurées)

Base de données 54

Exemples :

INSERT INTO VOITURE VALUES (‘2759QF78’, ‘BMW’, ‘Grise’, 8, 45 000);

Cette commande permet d'insérer le tuple ('2759QF78', 'BMW', 'Grise', 8, 45 000) dans la table VOITURE :

Immatriculation Marque Couleur Puissance Prix 4578QS59 Renault Noire 7 20 000 1952LM62 Rover Bleue 5 28 000 3664PN75 Citroën Rouge 6 30 000 3671KH60 Peugeot Verte 6 10 000 4691TR95 Renault Jaune 7 35 000 2759QF78 BMW Grise 8 45 000

Les domaines des valeurs insérées doivent correspondre aux types déclarés.

INSERT INTO VOITURE (Immatriculation, Couleur, Prix) VALUES (‘1379FG09’, ‘Rouge’, 29 500);

Cette commande permet d'insérer un nouveau tuple ayant les valeurs '1379FG09' pour l'Immatriculation, une couleur 'Rouge', et un Prix de 29 500€ dans la table VOITURE.

Immatriculation Marque Couleur Puissance Prix 4578QS59 Renault Noire 7 20 000 1952LM62 Rover Bleue 5 28 000 3664PN75 Citroën Rouge 6 30 000 3671KH60 Peugeot Verte 6 10 000 4691TR95 Renault Jaune 7 35 000 2759QF78 BMW Grise 8 45 000 1379FG09 NULL Rouge NULL 29 500

Les champs autres champs de ce tuple prendront la valeur NULL (ils ne devront pas avoir été déclarés NOT NULL).

INSERT INTO VOITURE_ACHETEES (Immatriculation) SELECT numImmatriculation FROM ACHAT;

Cette commande permet de créer des tuples dans la table VOITURE_ACHETEES (qui aura été créée préalablement) initialisés avec les numéros d'immatriculation présents dans la table ACHAT. Les autres attributs étant initialisés à NULL.

Immatriculation4578QS591952LM624691TR95

3. UPDATE La commande UPDATE permet de mettre à jour un ou plusieurs tuples d'une relation.

Page 20: Chapitre 7 Langage SQL - x.heurtebise.free.frx.heurtebise.free.fr/Enseignements/Monitorat/L3/Info/BDD/PDF/BDD... · SQL (Structured Query Language, ou langage de requêtes structurées)

Base de données 55

Syntaxe de la primitive UPDATE en SQL

UPDATE <nom_table> SET <attribut1> = <expression1>, <attribut2> = <expression2>, … [WHERE <condition>];

La clause WHERE est optionnelle ; cependant sans le WHERE, la mise à jour affectera TOUS les tuples de la relation.

Exemples :

UPDATE VOITURE SET Prix = Prix x 1.10;

Cette commande permet d'augmenter le Prix de vente de 10% (on multiplie par le coefficient 1.10) de tous les tuples de la table VOITURE :

Immatriculation Marque Couleur Puissance Prix 4578QS59 Renault Noire 7 22 000 1952LM62 Rover Bleue 5 30 800 3664PN75 Citroën Rouge 6 33 000 3671KH60 Peugeot Verte 6 11 000 4691TR95 Renault Jaune 7 38 500 2759QF78 BMW Grise 8 49 500 1379FG09 NULL Rouge NULL 32 450

Ceci permet de modifier rapidement une colonne pour la globalité d’une table.

UPDATE VOITURE SET Prix = Prix x 1.10 WHERE Marque = ‘Renault’;

Cette commande permet d'augmenter le Prix de vente de 10% uniquement sur les voitures de Marque Renault dans la table VOITURE.

Immatriculation Marque Couleur Puissance Prix 4578QS59 Renault Noire 7 22 000 1952LM62 Rover Bleue 5 28 000 3664PN75 Citroën Rouge 6 30 000 3671KH60 Peugeot Verte 6 10 000 4691TR95 Renault Jaune 7 38 500 2759QF78 BMW Grise 8 45 000 1379FG09 NULL Rouge NULL 29 500

Ceci permet de modifier rapidement une colonne pour une partie de la table.

UPDATE VOITURE SET Prix = Prix x 1.10 WHERE Marque = ‘Renault’ AND Prix > SELECT AVG(Prix) FROM VOITURE);

Cette commande permet d'augmenter le Prix de vente de 10% uniquement sur les voitures de Marque Renault dont le prix est supérieur au prix moyen de toutes les voitures de la table VOITURE.

Page 21: Chapitre 7 Langage SQL - x.heurtebise.free.frx.heurtebise.free.fr/Enseignements/Monitorat/L3/Info/BDD/PDF/BDD... · SQL (Structured Query Language, ou langage de requêtes structurées)

Base de données 56

Immatriculation Marque Couleur Puissance Prix 4578QS59 Renault Noire 7 20 000 1952LM62 Rover Bleue 5 28 000 3664PN75 Citroën Rouge 6 30 000 3671KH60 Peugeot Verte 6 10 000 4691TR95 Renault Jaune 7 38 500 2759QF78 BMW Grise 8 45 000 1379FG09 NULL Rouge NULL 29 500

4. DELETE La suppression de tuples se fait grâce à la commande DELETE. La suppression peut concerner tous les tuples d'une relation ou un sous-ensemble de tuples qui vérifie une condition de sélection. Il faut noter que DELETE opère sur le contenu d'une relation, c'est-à-dire que le schéma d'une relation persiste après suppression de tous les tuples de la relation.

Syntaxe de la primitive DELETE en SQL

DELETE FROM <nom_table> [WHERE <condition>];

La clause WHERE est optionnelle ; cependant sans le WHERE, la mise à jour affectera TOUS les tuples de la relation.

Exemples :

DELETE FROM VOITURE;

Cette commande permet de supprimer tous les tuples de la table VOITURE. Le schéma de la table persiste cependant.

Immatriculation Marque Couleur Puissance Prix

Ceci permet de supprimer rapidement tous les tuples d’une table.

DELETE FROM VOITURE WHERE Marque = ‘Renault’;

Cette commande permet de supprimer toutes les voitures de Marque Renault de la table VOITURE.

Immatriculation Marque Couleur Puissance Prix 1952LM62 Rover Bleue 5 28 000 3664PN75 Citroën Rouge 6 30 000 3671KH60 Peugeot Verte 6 10 000 2759QF78 BMW Grise 8 45 000 1379FG09 NULL Rouge NULL 29 500

Ceci permet de modifier rapidement une partie des tuples de la table.

DELETE FROM VOITURE WHERE Marque = ‘Renault’ AND Prix > SELECT AVG(Prix) FROM VOITURE);

Cette commande permet de supprimer toutes les voitures de Marque Renault dont le prix est supérieur au prix moyen de toutes les voitures de la table VOITURE.

Page 22: Chapitre 7 Langage SQL - x.heurtebise.free.frx.heurtebise.free.fr/Enseignements/Monitorat/L3/Info/BDD/PDF/BDD... · SQL (Structured Query Language, ou langage de requêtes structurées)

Base de données 57

Immatriculation Marque Couleur Puissance Prix 4578QS59 Renault Noire 7 22 000 1952LM62 Rover Bleue 5 28 000 3664PN75 Citroën Rouge 6 30 000 3671KH60 Peugeot Verte 6 10 000 2759QF78 BMW Grise 8 45 000 1379FG09 NULL Rouge NULL 29 500

VI. Le Langage de Contrôle de Données (LCD) Définition : Les instructions de contrôle des données donnent à l'administrateur de base de données le pouvoir de contrôler la sécurité de la base. Le LCD est composé de 4 commandes SQL : - GRANT - REVOKE - COMMIT - ROLLBACK

GRANT et REVOKE sont utilisées pour exercer un contrôle sur l'accès des données. COMMIT et ROLLBACK sont utilisées pour préserver l'intégrité des données. Pour utiliser une base de données, l'utilisateur doit passer par une procédure de connexion. Lors de cette procédure, il doit saisir un login (par exemple PDupont) et un mot de passe (par exemple xyz2V12). Ce login permet d'identifier chaque utilisateur et sert de repère pour lui accorder (ou lui enlever) des droits de manipulation de la base.

1. La commande GRANT La commande GRANT permet d'autoriser un accès aux données de la base soit total, soit à des degrés limités. Ainsi, il est possible, par exemple, d'autoriser la consultation de certaines tables sans possibilité de les modifier.

a. La syntaxe de la commande GRANT Syntaxe de la commande GRANT en SQL

GRANT ALL PRIVILEGES | accès_spécifique ON nom_table | nom_vue TO PUBLIC nom_autorisé | [WITH GRANT OPTION];

Avec la convention suivante : - Il est possible de donner tous les types de droits d'accès (consultation, modification,

suppression,…) avec ALL PRIVILEGES ou d'accorder des privilèges spécifiques (accès_spécifique).

- nom_autorisé (ou login) : nom donné par l'utilisateur lors de sa connexion à la base de données (créé par l'administrateur)

- PUBLIC : tout le monde reçoit le privilège accordé (accès_spécifique) ou tous les privilèges (ALL PRIVILEGES).

- WITH GRANT OPTION : celui qui reçoit le privilège peut lui-même l'accorder à un autre (opération dangereuse, à éviter).

Concernant les types de droits d'accès, si des tables de la base sont identifiées à l'aide du nom du créateur de la base, cet utilisateur peut (si on lui a accordé le privilège requis) autoriser l'accès à ses tables pour d'autres utilisateurs.

Page 23: Chapitre 7 Langage SQL - x.heurtebise.free.frx.heurtebise.free.fr/Enseignements/Monitorat/L3/Info/BDD/PDF/BDD... · SQL (Structured Query Language, ou langage de requêtes structurées)

Base de données 58

GRANT ALL PRIVILEGES ON VOITURE TO Martin WITH GRANT OPTION;

Cette instruction SQL permet à l'administrateur de la base de donner à Martin tous les droits sur la table VOITURE, il lui accorde également l'autorisation de transférer ces privilèges.

b. Les droits d'accès La gestion des droits d'accès aux tables est décentralisée : il n'existe pas d'administrateur global attribuant des droits. Seul le propriétaire (créateur) d'une table peut attribuer des droits sur celle-ci. Les principaux droits d'accès spécifiques sont : - sélection (SELECT) - insertion (INSERT) - suppression (DELETE) - mise à jour (UPDATE) - indexation (INDEX) - référencer la table dans une contrainte (REFERENCES)

Il peut ensuite passer ses droits sélectivement à d'autres utilisateurs ou à tout le monde (PUBLIC). Un droit peut être passé avec le droit de le transmettre (WITH GRANT OPTION) ou non. L'ensemble des droits d'accès (ALL PRIVILEGES) inclut les droits d'administration (changement de schéma et destruction de la relation).

GRANT SELECT, UPDATE ON VOITURE, ACHAT TO Smith;

Cette commande permet de passer des droits de consultation et de mise à jour de la table VOITURE et de la table ACHAT à l'utilisateur Smith.

GRANT ALL PRIVILEGES (Immatriculation, Prix) ON VOITURE TO Smith, Vandenbrouck, Dubois;

Cette commande permet de passer l'ensemble des droits d'accès aux utilisateurs Smith, Vandenbrouck et Dubois uniquement sur les colonnes Immatriculation et Prix de la table VOITURE.

GRANT INSERT ON PERSONNE TO PUBLIC;

Cette commande attribue le droit d'insérer de nouveaux enregistrements dans la table PERSONNE à tous ceux qui, dans la société, disposent d'une identification pour se connecter sur la BD.

2. La commande REVOKE La commande REVOKE permet de retirer l'accès, c'est la commande inverse de GRANT.

Page 24: Chapitre 7 Langage SQL - x.heurtebise.free.frx.heurtebise.free.fr/Enseignements/Monitorat/L3/Info/BDD/PDF/BDD... · SQL (Structured Query Language, ou langage de requêtes structurées)

Base de données 59

Syntaxe de la commande REVOKE en SQL

REVOKE ALL PRIVILEGES | accès_spécifique ON nom_table | nom_vue FROM nom_utilisateur | PUBLIC;

Exemples :

REVOKE SELECT, UPDATE ON VOITURE, ACHAT FROM Smith;

Cette commande supprime les droits de consultation et de mise à jour de la table VOITURE et de la table ACHAT qui avait été accordés à l'utilisateur Smith.

REVOKE ALL PRIVILEGES ON VOITURE FROM Martin;

Cette commande retire tous les privilèges accordés sur la table VOITURE à Martin.

3. Le contrôle d'intégrité Les commandes COMMIT et ROLLBACK constituent des contrôles (cf. le chapitre sur les transactions) nécessaires à la préservation de l'intégrité de la base.

Des systèmes multi-utilisateurs disponibles dans le commerce emploient des contrôles supplémentaires comme par exemple la commande LOCK pour empêcher les valeurs de changer pendant qu'un utilisateur examine ou travaille sur ces valeurs (nous étudierons plus en détails ces notions dans le chapitre sur les transactions).

4. La commande COMMIT La commande COMMIT permet à l'utilisateur de fixer le moment où les modifications en cours affecteront la base de données. Dans ce cadre, on utilise le concept de transaction.

La transaction est une suite d'opérations telle que chaque opération de cette suite est nécessaire pour atteindre un résultat unitaire.

C'est la raison pour laquelle SQL propose à l'utilisateur de n'enregistrer les modifications dans la base qu'au moment où la transaction est achevée grâce à la commande COMMIT.

Une transaction (ou une partie de transaction) qui n'a pas encore été enregistrée définitivement n'est visible que pour l'utilisateur qui l'introduit. Elle n'affecte pas la base tant que l'instruction n'est pas exécutée.

Avant l'exécution de l'instruction COMMIT, il est possible de restaurer la base par ROLLBACK, c'est-à-dire d'éliminer les modifications récentes. Après l'enregistrement définitif d'une transaction par COMMIT, il n'est plus possible de restaurer l'état antérieur par ROLLBACK. S'il apparaît après coup qu'une transaction doive être modifiée ou corrigée, on ne pourra effecteur cette modification qu'au moyen d'une autre instruction SQL comme UPDATE ou DELETE.

5. La commande ROLLBACK La commande ROLLBACK permet à l'utilisateur de ne pas valider les dernières modifications en cours dans la base de données.

Par exemple, si au cours du déroulement d'une transaction, l'utilisateur fait une erreur ou si, pour une certaine raison, une transaction ne peut pas être achevée, l'utilisateur peut supprimer les modifications

Page 25: Chapitre 7 Langage SQL - x.heurtebise.free.frx.heurtebise.free.fr/Enseignements/Monitorat/L3/Info/BDD/PDF/BDD... · SQL (Structured Query Language, ou langage de requêtes structurées)

Base de données 60

afin d'éviter des incohérences dans la base grâce à la commande ROLLBACK. Cette commande élimine tous les changements depuis la dernière validation.

Dans le cas d'une défaillance du système, l'intégrité de la base peut être préservée par une option ROLLBACK automatique qui élimine les transactions inachevées et empêche donc qu'elles soient introduites dans la base.

6. La commande SAVEPOINT Une transaction étant une séquence de séquence de commandes SQL considérée comme unitaire, indivisible, il peut être nécessaire de pouvoir revenir en arrière, à n’importe quel endroit dans la transaction courante.

Pour cela, il suffit de réaliser des points de sauvegarde grâce à la commande SAVEPOINT <nom> à l’intérieur d’une transaction pour situer un point éventuel de retour vers l’état de la base de données. Le retour en arrière s’effectue grâce à la commande ROLLBACK WORK TO SAVEPOINT <nom>.

7. Terminaison d'une transaction

VII. Les vues Définition : Une vue est une relation virtuelle au sens où ses instances n'existent pas physiquement mais sont calculées à chaque invocation de la vue. Une vue est définie par une requête qui utilise des relations ou des vues existantes.

Syntaxe de la commande CREATE VIEW en SQL

CREATE VIEW nom_de_la_vue AS <clause SELECT>;

Exemples :

CREATE VIEW VOITURES_CHERES AS SELECT * FROM VOITURE WHERE Prix > 15 000;

La vue VOITURES_CHERES a le même schéma que la relation VOITURE.

Page 26: Chapitre 7 Langage SQL - x.heurtebise.free.frx.heurtebise.free.fr/Enseignements/Monitorat/L3/Info/BDD/PDF/BDD... · SQL (Structured Query Language, ou langage de requêtes structurées)

Base de données 61

SELECT * FROM VOITURES_CHERES;

On peut accéder grâce à cette vue à toutes les voitures dont le Prix est supérieur à 15 000 €.

CREATE VIEW VOITURES_PUISSANTES (immat_voiture, marquee_voiture) AS SELECT Immatri ulation, Marque c FROM VOITURE WHERE Puissance > 6;

La vue Voitures-puissantes comporte les attributs Immatriculation et Marque renommés en immat_voiture et marque_voiture. Elle permet d'accéder à toutes les voitures dont la puissance est supérieure à 6 chevaux.

En interrogation, une vue est utilisée comme toute autre relation. La seule différence réside dans le fait que ses tuples ne sont pas stockés mais ils sont le résultat de l'évaluation de la requête de définition.

En mise à jour, toute modification des relations ayant servi à la définition de la vue est répercutée sur la vue (lors d'une nouvelle évaluation de la requête de définition de la vue). Par contre, la mise à jour d'une base de données "à travers" une vue n'a pas trouvé de solution générale : le problème vient du fait qu'il est parfois impossible de répercuter la mise à jour de la vue sur les relations sur lesquelles elle est définie. Cependant, quand des contraintes logiques ne viennent pas l'empêcher la mise à jour au travers des vues est possible.

A titre d'exemple, si une vue comporte une colonne obtenue par une fonction d'agrégation, la mise à jour de cette colonne supposerait que l'on soit capable de mettre à jour les tuples concernés par la fonction d'agrégation, étant donnée la valeur introduite, ce qui est logiquement impossible.

Il est, de ce fait, nécessaire de consulter la documentation du système dont on dispose pour connaître les types de mises à jour autorisés en utilisant une vue.

Enfin la suppression d'une vue est faite explicitement par DROP VIEW.

VIII. Les séquences Définition : Définir une séquence équivaut à définir une suite de nombres entiers. L'évolution de cette suite est régie par un certain nombre de paramètres. L'utilisation d'une séquence permet donc d'avoir à disposition une suite de valeurs. Ceci peut permettre de : - générer des clés uniques dans des tables - avoir un compteur à titre informatif, que l'on incrémente quand on veut - etc...

1. Création du séquence Syntaxe de la commande CREATE SEQUENCE en SQL

CREATE SEQUENCE nom_de_la_sequence [START WITH valeur_initiale] [INCREMENT BY ] incrément [NOMAXVALUE NOMINVALUE | MAXVALUE maximum | MINVALUE minimum] | [NOCYCLE | CYCLE] [CACHE nombre_de_valeurs];

Notation (les éléments entre [ ] sont optionnels) : - START WITH permet de fixer la valeur initiale de la séquence. - INCREMENT BY permet de fixer le pas d’incrémentation. Si celui-ci est positif, la séquence est

croissante, sinon elle décroît.

Page 27: Chapitre 7 Langage SQL - x.heurtebise.free.frx.heurtebise.free.fr/Enseignements/Monitorat/L3/Info/BDD/PDF/BDD... · SQL (Structured Query Language, ou langage de requêtes structurées)

Base de données 62

- NOMAXVALUE et NOMMINVALUE permet de ne pas fixer de limite dans la séquence. Si l’on désire fixer un plafond à ne pas dépasser (pour une séquence ascendante), on utilise MAXVALUE. Si l’on désire fixer un plancher (pour une séquence descendante), on utilise MINVALUE.

- L’option CYCLE permet une fois la limite du compteur atteint, de reprendre le comptage à la valeur MINVALUE (pour une séquence ascendante) ou MAXVALUE (pour une suite descendante). Si l’on désire arrêter la séquence une fois la limite du compteur atteint, il suffit d’utiliser l’option NOCYCLE.

- L’option CACHE permet de mettre nombre_de_valeurs valeurs dans la mémoire cache afin d’optimiser l’utilisation des séquences et avoir un effet significatif sur les performances, surtout lorsque nombre_de_valeurs est élevé.

CREATE SEQUENCE SEQUENCE_VOITURE START WITH 5 INCREMENT BY 3;

Cette commande SQL permet de créer une séquence SEQUENCE_VOITURE commençant à la valeur 5 avec un pas d’incrémentation de 3. La séquence obtenue est alors : 5, 8, 11, 14, 17, 20, …

CREATE SEQUENCE SEQUENCE_VOITURE START WITH 5 INCREMENT BY 3 MAXVALUE 30;

Cette commande SQL permet de créer une séquence SEQUENCE_VOITURE commençant à la valeur 5 avec un pas d’incrémentation de 3, avec pour valeur maximale 30. La séquence obtenue est alors : 5, 8, 11, 14, 17, 20, 23, 26, 29.

CREATE SEQUENCE SEQUENCE_VOITURE START WITH 5 INCREMENT BY 3 [NOMAXVALUE NOMINVALUE];

Cette commande SQL (avec ou sans l’option NOMAXVALUE NOMINVALUE) permet de créer une séquence SEQUENCE_VOITURE commençant à la valeur 5 avec un pas d’incrémentation de 3 sans limite. La séquence obtenue est alors : 5, 8, 11, 14, 17, 20, …

CREATE SEQUENCE SEQUENCE_VOITURE START WITH 1 MAXVALUE 10 MINVALUE -10 CYCLE;

Cette commande SQL permet de créer une séquence SEQUENCE_VOITURE commençant à la valeur 1 avec un pas d’incrémentation de 1, avec pour valeur maximale 10 et pour valeur minimale -10. La séquence est cyclique, si bien que l’on a la séquence : 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, -10, -9, -8, -7, -6, …

CREATE SEQUENCE SEQUENCE_VOITURE CACHE 100;

Cette commande SQL permet de créer une séquence SEQUENCE_VOITURE en stockant les 100 premières valeurs dans la mémoire cache. Par défaut, la valeur est 20.

Page 28: Chapitre 7 Langage SQL - x.heurtebise.free.frx.heurtebise.free.fr/Enseignements/Monitorat/L3/Info/BDD/PDF/BDD... · SQL (Structured Query Language, ou langage de requêtes structurées)

Base de données 63

2. Interroger la séquence L'interrogation d'une séquence se fait par l'utilisation des "pseudo-colonnes" CURRVAL et NEXTVAL. On parle de pseudo-colonne car cela se manipule un peu comme une colonne de table, mais ce n'est pas une colonne de table. - La pseudo-colonne CURRVAL retourne la valeur courante de la séquence. - La pseudo-colonne NEXTVAL incrémente la séquence et retourne la nouvelle valeur.

SELECT SEQUENCE_VOITURE.NEXTVAL FROM DUAL; SELECT SEQUENCE_VOITURE.CURRVAL FROM DUAL;

Lors de la première utilisation d’un séquence, il faut utiliser NEXTVAL pour l’initialiser. Ensuite, CURRVAL permet d’obtenir la valeur courante de la séquence.

3. Modifier une séquence Syntaxe de la commande ALTER SEQUENCE en SQL

ALTER SEQUENCE nom_de_la_sequence [INCREMENT BY incrément] [NOMAXVALUE NOMINVALUE | MAXVALUE maximum | MINVALUE minimum] | [NOCYCLE | CYCLE] [CACHE nombre_de_valeurs];

Exemple :

ALTER SEQUENCE SEQUENCE_VOITURE INCREMENT BY 3;

Cette commande SQL permet de modifier le pas d’incrémentation (valeur : 3) de la séquence SEQUENCE_VOITURE.

4. Création d’une colonne de table auto-incrémentée (pour une clé primaire) Le principe est le suivant : - créer une séquence qui permettra de générer des valeurs entières uniques - créer un TRIGGER qui se déclenchera à chaque INSERT, pour alimenter le champ voulu avec

une valeur unique.

CREATE TRIGGER RIGGER_CL ITURE T E_PRIMAIRE_VOBEFORE INSERT ON VOITURE FOR EACH ROW BEGIN SELECT SEQUENCE_VOITURE.NEXTVAL INTO :VOITURE.cle_primaire FROM DUAL; END;

Cet exemple ne gère pas le contrôle d'unicité de la valeur que l'on va insérer, mais si le champ n'est alimenté QUE par l'utilisation de la séquence qui lui est dédiée, et si cette séquence n'est pas paramétrée pour reboucler, il n'y a pas de raison qu'une erreur de clé en double surgisse...

Page 29: Chapitre 7 Langage SQL - x.heurtebise.free.frx.heurtebise.free.fr/Enseignements/Monitorat/L3/Info/BDD/PDF/BDD... · SQL (Structured Query Language, ou langage de requêtes structurées)

Base de données 64

IX. BLOCS PL/SQL

1. L'intro de l'intro PL/SQL est un langage qui intègre SQL et permet de programmer de manière procédurale. Globalement, avec PL/SQL on aura à notre disposition un vrai langage de programmation moins intuitif mais aussi plus puissant que le SQL. Les 2 langages sont éminemment complémentaires.

Les principaux avantages / inconvénients sont les suivants : - ne dispense pas de connaître le SQL ; - permet le traitement par bloc de SQL et donc optimise le trafic réseau ; - l'utilisation de variable de stockage et de type simple et structuré dynamique (%TYPE,

%ROWTYPE, …) ; - des traitements plus complexes, notamment pour la gestion des cas particuliers et des erreurs

(traitement des exceptions) ; - un paramétrage et la création d'ordres SQL dynamiques.

Le PL/SQL peut être utilisé sous 3 formes : - un bloc de code, exécuté comme une commande SQL, via un interpréteur standard - un fichier de commande PL/SQL - un programme stocké (procédure, fonction, package ou trigger)

2. Ordres SQL supportés dans PL/SQL Les instructions du langage de manipulation de données (LMD) et certaines instructions de gestion de transaction, à savoir : - INSERT, UPDATE, DELETE, SELECT ; - COMMIT, ROLLBACK, SAVEPOINT.

3. Blocs et sections PL/SQL Les blocs de code s'appellent également des blocs anonymes. Ils commenceront simplement par un 'BEGIN' ou un 'DECLARE'. Ils sont composés de 1 à 3 sections :

Type de section Obligatoire ? Mots clés section déclarative non DECLARE … section exécutable oui BEGIN … END;

section de traitement des exceptions non EXCEPTION

La section 'EXCEPTION', quand elle est présente, est incluse dans la section et NON PAS à la suite de celle ci.

BEGIN NULL; END;

Le bloc précédent est le plus petit bloc PL/SQL au monde. Le bloc ne peut pas être vide et doit contenir au moins une instruction…

Syntaxe de bloc en PL/SQL (avec des exceptions)

DECLARE -- mes déclarations de variables -- BEGIN -- début de la section exécutable -- -- mes ordres SQL et PL/SQL -- EXCEPTION -- mon traitement des exceptions -- END; -- la fin du bloc exécutable --

Page 30: Chapitre 7 Langage SQL - x.heurtebise.free.frx.heurtebise.free.fr/Enseignements/Monitorat/L3/Info/BDD/PDF/BDD... · SQL (Structured Query Language, ou langage de requêtes structurées)

Base de données 65

Afin de pouvoir utiliser une exception, il faut tout d’abord déclarer une variable d’exception dans la partie DECLARE (ex : DECLARE erreur EXCEPTION;).

Ensuite, dans la partie entre le BEGIN et le END, pour appeler une exception et stopper le bloc PL/SQL, on utilise la commande RAISE nom_exception (ex : RAISE erreur;). Cette commande stoppe le bloc PL/SQL et va dans la partie EXCEPTION.

Dans cette partie, pour afficher un message d’erreur, on utilise la commande suivante : WHEN nom_exception THEN RAISE_APPLICATION_ERROR ( numero_erreur , message ); − numero_erreur : représente le numéro de l’erreur utilisateur. Ce numéro doit être compris entre

–20000 et –20999. − message : chaîne de caractères d’une longueur maximale de 2048 octets qui contient le message

associé à l’erreur.

Les exceptions prédéfinies sont : − NO_DATA_FOUND : cas où l’on n’a aucune donnée à la sortie d’un SELECT. − TO_MANY_ROWS : cas où l’on a trop de données à la sortie d’un SELECT. − VALUE_ERROR : cas où l’on a une erreur de valeur. − ZERO_DIVIDE : cas d’une division par zéro. − INVALIDE_NUMBER : cas d’un nombre invalide.

4. Procédure PL/SQL Une procédure est simplement un programme PL/SQL nommé, compilé et stocké dans la base.

Syntaxe de procédure en PL/SQL (avec des exceptions)

CREATE [OR REPLACE] PROCEDURE nom_de_procédure ( par1 type1, par2 type2, … ) IS BEGIN -- mes ordres SQL et PL/SQL -- EXCEPTION -- mon traitement des exceptions -- END;

Ce code est une commande SQL, qui crée la procédure PL/SQL, et donc compile et stocke dans la base le bloc PL/SQL compris entre le BEGIN et le END, en le référençant par ‘nom_de_procédure’. Et pour exécuter cette procédure de manière autonome, on utilise la commande : ‘EXECUTE nom_de_procédure’. Les paramètres d’entrée doivent être déclarés (nom et type).

CREATE OR REPLACE PROCEDURE Client (nom VARCHAR2, ville VARCHAR2) IS BEGIN INSERT INTO CLIENTS (numclient,nomclient,villeclient) VALUES (SEQUENCE_NUM_CLIENT.NEXTVAL, nom, ville); COMMIT; END;

Cette commande permet de créer une procédure nommée Client, dont les paramètres d’entrée sont des chaînes de caractères nom et ville. La procédure permet d’insérer un nouveau tuple dans la table CLIENTS de valeurs SEQUENCE_NUM_CLIENT.NEXTVAL (pour créer automatiquement une nouvelle valeur de clé primaire), nom et ville.

5. Fonctions PL/SQL Une fonction est une procédure retournant une valeur.

Page 31: Chapitre 7 Langage SQL - x.heurtebise.free.frx.heurtebise.free.fr/Enseignements/Monitorat/L3/Info/BDD/PDF/BDD... · SQL (Structured Query Language, ou langage de requêtes structurées)

Base de données 66

CREATE [OR REPLACE] FUNCTION nom_de_fonction ( par1 type1, par2 type2, …) RETURN type_de_sortie IS variable_de_sortie type_de_sortie IS BEGIN -- mes ordres SQL et PL/SQL -- EXCEPTION -- mon traitement des exceptions -- END;

Ce code est une commande SQL, qui crée la fonction PL/SQL, et donc compile et stocke dans la base le bloc PL/SQL compris entre le BEGIN et le END, en le référençant par ‘nom_de_fonction’. La valeur retournée en sortie correspond à la variable_de_sortie dont le type est type_de_sortie. Les paramètres d’entrée doivent être déclarés (nom et type).

CREATE OR REPLACE FUNCTION solde (numero NUMBER) RETURN REAL IS le_solde REAL BEGIN SELECT solde INTO le_solde FROM clients WHERE numclient = numero; RETURN le_solde; END;

Cette commande permet de créer une fonction nommée solde, dont le paramètre d’entrée est le numéro de client. La fonction permet de retourner en sortie, dans la variable réelle le_solde, le solde du compte du client identifié par numero. Pour appeler cette fonction et utiliser son résultat au sein d’un ordre SQL, il suffit d’exécuter la commande suivante : ‘SELECT solde(1000) FROM DUAL’.

6. Trigger Les déclencheurs (Triggers) sont des procédures stockées appartenant à une table précise et s'exécutant lorsqu'une action spécifique se produit sur la table concernée. Le déclenchement d'une telle procédure s'effectue subséquemment à une instruction de manipulation de données (DML) comme INSERT, DELETE ou UPDATE. Il existe donc trois types de déclencheurs : sur insertion, sur mise à jour et sur suppression.

Une table peut comporter plusieurs déclencheurs d'un type donné, à condition que chacun possède un nom différent. Cependant, un déclencheur donné ne peut être assigné qu'à une seule et unique table tout en s'appliquant à la fois, à l'insertion, la mise à jour et la suppression d'enregistrements sur la table en question. Une table ne peut posséder qu'un seul déclencheur INSTEAD OF d'un type donné.

Les déclencheurs se produisent soit après (AFTER), soit avant (BEFORE) soit à la place (INSTEAD OF) d'une action DML : - Un déclencheur sur INSERT s'exécute à chaque opération d'insertion lancée par l'utilisateur ou

par un programme. Lors d'une insertion, l'enregistrement est inséré à la fois dans la table cible est dans une table temporaire dénommée inserted. Une telle table peut permettre de vérifier la cohérence des enregistrements.

- Un déclencheur sur DELETE s'exécute à chaque opération de suppression lancée par l'utilisateur ou un programme. Lors d'une suppression, l'enregistrement est supprimé physiquement de la table cible et l'insère dans une table temporaire dénommée deleted. Cela peut permettre de récupérer l'enregistrement supprimé.

- Un déclencheur sur UPDATE s'exécute à chaque opération de mise à jour lancée par l'utilisateur ou par un programme. Lors d'une mise à jour, l'ancien enregistrement est supprimé et inséré dans la table temporaire deleted, tandis que le nouveau est inséré à la fois dans la table cible et dans la table inserted.

Page 32: Chapitre 7 Langage SQL - x.heurtebise.free.frx.heurtebise.free.fr/Enseignements/Monitorat/L3/Info/BDD/PDF/BDD... · SQL (Structured Query Language, ou langage de requêtes structurées)

Base de données 67

La suppression des déclencheurs s'effectue par l'intermédiaire de l'instruction DROP.

DROP TRIGGER nom_trigger1 [,…, nom_triggerN ];

La modification des déclencheurs s'effectue par l'intermédiaire de l'instruction ALTER. La syntaxe complète de la commande ALTER TRIGGER est en fait identique à celle de CREATE TRIGGER.

ALTER TRIGGER nom_trigger ON nom_table FOR INSERT | UPDATE | DELETE AS instruction_SQL…;

Tous les déclencheurs (ALL) ou certains peuvent être activés (ENABLE) ou désactivés (DISABLE) au moyen de l'instruction ALTER TABLE.

ALTER TABLE nom_table { ENABLE | DISABLE } TRIGGER { ALL | nom_trigger1 [,…,nom_triggerN]};

Syntaxe de création de trigger en PL/SQL

CREATE [OR REPLACE] TRIGGER nom_trigger AFTER | BEFORE | INSTEAD OF { [INSERT [OR DELETE [OR UPDATE OF nom_colonne1,…,nom_colonneN] ] ] } nom_tableON FOR EACH ROW [WHEN (condition)] Instruction_PL/SQL | instruction_prodécure;

Notation (les éléments entre [ ] sont optionnels) : - La commande OR REPLACE recrée le déclencheur s'il existe déjà. - La clause BEFORE indique que le déclencheur doit être lancé avant l'exécution de l'événement. - La clause AFTER indique que le déclencheur doit être lancé après l'exécution de l'événement. - Les instructions INSERT et DELETE indique au déclencheur de s'exécuter lors respectivement

d'une insertion ou d'une suppression dans la table. - La clause UPDATE OF indique que le déclencheur doit être lancé lors de chaque mise à jour

d'une des colonnes spécifiées. Si elle est omise, n'importe quelle colonne de la table modifiée provoque le déclenchement du Trigger.

- La clause ON désigne le nom de la table associé à son schéma pour lequel le déclencheur a été spécifiquement créé.

- La clause FOR EACH ROW désigne le déclencheur pour être un déclencheur de ligne. Oracle lance un déclencheur de ligne une fois pour chaque ligne qui est affectée par l'instruction de déclenchement. Si la clause est omise, le déclencheur est un déclencheur d'instruction. Oracle lance un déclencheur de d'instruction une fois seulement lorsque l'instruction déclenchante est émise si la contrainte du déclencheur optionnelle est rencontrée.

CREATE TABLE TABLE_1 ( numero NUMBER, chaine CHAR(20) ); CREATE TABLE DELETE_LOG ( table CHAR(20), action CHAR(50), jour CHAR(20) ); CREATE OR REPLACE TRIGGER declencheur_suppression AFTER DELETE ON TABLE_1

Page 33: Chapitre 7 Langage SQL - x.heurtebise.free.frx.heurtebise.free.fr/Enseignements/Monitorat/L3/Info/BDD/PDF/BDD... · SQL (Structured Query Language, ou langage de requêtes structurées)

Base de données 68

FOR EACH ROW DECLARE action_utilisateur VARCHAR2(50); BEGIN SELECT user INTO action_utilisateur FROM DUAL; INSERT INTO DELETE_LOG VALUES ('table_1',action_utilisateur,TO_CHAR(SYSDATE, 'DD/MON/YYYY-HH24:MI:SS')); END;

Cette commande SQl'intérieur de DELET

L permet de créer deux tables puis crée un déclencheur qui insère un champ log à E_LOG, pour chaque ligne supprimée dans la table TABLE_1.

CREATE TABLE TABLE_1 ( numero NUMBER, chaine CHAR(20) ); CREATE TABLE TABLE_2 ( chaine CHAR(20), numero NUMBER) ); CREATE TRIGGER declencheur_insertion AFTER INSERT ON TABLE_1 FOR EACH ROW WHEN (NEW.numero <= 10) BEGIN INSERT INTO TABLE_2 VALUES (:NEW.numero, :NEW.chaine); END;

Cette commande insère un enregistr

SQL permet de créer deux tables TABLE_1 et TABLE_2 puis crée un déclencheur qui ement à l'intérieur de TABLE_2 lorsqu’une opération d'insertion s'est accomplie dans

TABLE_1. Le déclencheur vérifie si le nouvel enregistrement possède un premier composant inférieur ou égal à 10 et si c'est le cas, inverse les enregistrements à l'intérieur de TABLE_2.

Les variables spéciales NEW et OLD sont disponibles pour se référer respectivement à des nouveaux ou d'anciens enregistrements. Les deux points (:) précédent NEW et OLD dans VALUES sont dans ce

WHENcas obligatoires, par contre dans la clause conditionnelle , ils doivent être omis.

CREATE TABLE EMP ( EmpNo NUMBER, Enom CHAR(20), Job CHAR(20), Sal NUMBER ); CREATE TABLE SALGRADE ( jobclass CHAR(20), minSal NUMBER, maxSal NUMBER ); CREATE TRIGGER sal_check BEFORE INSERT OR UPDATE OF sal, job ON EMP FOR EACH ROW DECLARE minSal NUMBER NUMBER; sal_hors_rang EXCEPTION; ; maxSal BEGIN SELECT minS xSal INTOal, ma minSal, maxSal FROM SALGRADE WH .job; ERE jobclass = :NEW IF (:NEW.sal<minSal OR :NEW.sal>maxSal) THEN RAISE sal_hors g; _ran END IF; EXCEPTION WHEN sal_hors_rang THEN RAISE_APPLICATION_ERROR(-20300,‘Salaire ’||TO_CHAR (:NEW.sal)||‘ hors rang’); WHEN NO_DATA_FOUND THEN RAISE_APPLICATION_ERROR(-20322,‘Job invalide’); END;

Cette commandesalaires), puis crée

SQL permet de créer deux tables EMP (employés) et SALGRADE (grades de un déclencheur pour vérifier, avant une insertion ou modification de sal et job dans

la table EMP, que le nouveau salaire soit valide (c'est-à-dire que le nouveau salaire ne soit pas inférieur à minSal et ne soit pas supérieur à maxSal) pour le nouvel emploi.

Page 34: Chapitre 7 Langage SQL - x.heurtebise.free.frx.heurtebise.free.fr/Enseignements/Monitorat/L3/Info/BDD/PDF/BDD... · SQL (Structured Query Language, ou langage de requêtes structurées)

Base de données 69

X. CURSEURS Les curseurs sont des pointeurs sur une zone mémoire pour les données extraites de la base. Il existe des curseurs implicites et explicites. Oracle ouvre toujours un curseur implicite pour traiter une instruction SQL, celui-ci ne se rapporte qu’à la dernière instruction SQL exécutée et il se nomme « SQL ». Le curseur contient des attributs (%NOTFOUND, %FOUND, %ROWCOUNT) qui fournissent des informations sur l’exécution des instructions INSERT, UPDATE, DELETE, SELECT INTO. Un curseur implicite pour une instruction SELECT INTO ne peut gérer qu’une seule ligne. Le curseur explicite quant à lui place le résultat d’une requête multi-lignes dans un tampon mémoire et libère les lignes les unes après les autres lors du traitement.

Le curseur se définit dans la partie déclarative du bloc PL/SQL (la requête n’est pas exécutée à ce moment-là). Dans cette déclaration, il est possible de donner une clause FOR UPDATE OF nom_colonne(s) qui permet de verrouiller les lignes sélectionnées (aucun autre utilisateur ne peut mettre à jour tant que le verrou n’est pas retiré). La commande OPEN nom_curseur exécute la requête et place le curseur en mémoire, elle ne retourne aucun résultat. L’instruction FETCH nom_curseur INTO variable extrait la ligne courante du curseur, la place dans une variable et fait avancer le curseur à la ligne suivante. Pour parcourir toutes les lignes du curseur, il faut utiliser une boucle LOOP. La clause CURRENT OF nom_curseur est utilisée dans le WHERE d’une commande UPDATE pour modifier la ligne courante (si un FOR UPDATE a été utilisé préalablement). L’arrêt de la boucle est obtenu grâce à nom_curseur%NOTFOUND qui retourne false s’il ne reste plus de lignes. Pour libérer l’espace mémoire, il faut fermer explicitement le curseur en utilisant CLOSE nom_curseur.

Un curseur peut accepter des paramètres en entrée, ils servent à passer des informations au curseur et sont généralement utilisés dans un WHERE pour limiter la requête. Les paramètres ont un type associé qui ne peut pas avoir d’indication de longueur. Ils sont passés lors de la commande OPEN.

Syntaxe de la commande DECLARE CURSOR en SQL

DECLARE nom1 table1.attribut1%TYPE; …; omN tableN.attributN%TYPE; n CURSOR nom_du_curseur [(paramètre1 type1 [,…,paramètreN typeN])] [RETURN type_du_retour] IS SELECT attribut(s) FROM table(s) WHERE condition(s); BEGIN instruction_curseurs; END;

Il est également possible de passer des paramètres à un curseur. Suite au DECLARE, on trouve la déclaration des variables utiles au curseur en les définissant par nom table.attribut%TYPE.

DECLARE employe EMP.Ena %TYPE; me salaire EMP.Sal%TYPE; CURSOR curseur (nom VARCHAR2, sal NUMBER) IS SELECT e.Empno, e.Ename, e.Job, r.Sal FROM EMP AS e, REMNUERATION AS r WHERE e.Ename = nom AND r.Sal = sal; BEGIN OPEN curseur(‘DUPONT’,3000); LOOP FETCH curseur INTO @employe, @salaire; EXIT WHEN curseur%NOTFOUND; END LOOP; CLOSE curseur; END;

La commande SQL crée un curseur dont les paramètres d’entrée sont le nom et le salaire d’un employé. La structure de sélection permet de récupérer la liste des employés (numéro, nom, métier) et de leur salaire. Dans la structure BEGIN…END, on retrouve l’opération d’ouverture du curseur suivi de la commande FETCH…INTO… qui permet d’extraire les différentes lignes des tuples renvoyé par la structure de sélection afin de les sauvegarder dans les variables employe et salaire définies dans la déclaration du

Page 35: Chapitre 7 Langage SQL - x.heurtebise.free.frx.heurtebise.free.fr/Enseignements/Monitorat/L3/Info/BDD/PDF/BDD... · SQL (Structured Query Language, ou langage de requêtes structurées)

Base de données 70

curseur. L'attribut Oracle %NOTFOUND retourne FALSE si la dernière instruction FETCH renvoie un enregistrement ou TRUE en cas d'échec. Suite à la fin de son utilisation, le curseur peut être fermé afin de ne plus consommer de ressources.

XI. FONCTIONS SQL

1. Fonctions arithmétiques - ABS(nb) : Renvoie la valeur absolue de nb. - CEIL(nb) : Renvoie le plus petit entier supérieur ou égal à nb. - COS(n) : Renvoie le cosinus de n, n étant un angle exprimé en radians. - COSH(n) : Renvoie le cosinus hyperbolique de n. - EXP(n) : Renvoie e puissance n. - FLOOR(nb) : Renvoie le plus grand entier inférieur ou égal à nb. - LN(n) : Renvoie le logarithme népérien de n qui doit être un entier strictement positif. - LOG(m,n) : Renvoie le logarithme en base m de n. m doit être un entier strictement supérieur à 1,

et n un entier strictement positif. - MOD(m,n) : Renvoie le reste de la division entière de m par n, si n vaut 0 alors renvoie m.

Attention, utilisée avec au moins un de ses arguments négatifs, cette fonction donne des résultats qui peuvent être différents d'un modulo classique. Cette fonction ne donne pas toujours un résultat dont le signe du diviseur.

- POWER(m,n) : Renvoie m puissance n, m et n peuvent être des nombres quelconques entiers ou réels mais si m est négatif n doit être un entier.

- ROUND(n[,m]) : Si m est positif, renvoie n arrondi (et non pas tronqué) à m chiffres après la virgule. Si m est négatif, renvoie n arrondi à m chiffres avant la virgule. m doit être un entier et il vaut 0 par défaut.

- SIGN(nb) : Renvoie -1 si nb est négatif, 0 si nb est nul, 1 si nb est positif. - SIN(n) : Renvoie le sinus de n, n étant un angle exprimé en radians. - SINH(n) : Renvoie le sinus hyperbolique de n. - SQRT(nb) : Renvoie la racine carrée de nb qui doit être un entier positif ou nul. - TAN(n) : Renvoie la tangente de n, n étant un angle exprimé en radians. - TANH(n) : Renvoie la tangente hyperbolique de n. - TRUNC(n[,m]) : Si m est positif, renvoie n arrondi tronqué à m chiffres après la virgule. Si m est

négatif, renvoie n tronqué à m chiffres avant la virgule. m doit être un entier et il vaut 0 par défaut.

SELECT nom, ROUND(salaire/22.2) FROM EMP;

Cette instruction SQL permet d’afficher le nom et le salaire journalier, arrondi à l’entier le plus proche, de chaque employé de la table EMP.

2. Expressions et fonctions sur les chaînes de caractères - CONCAT(chaîne1,chaîne2) = chaîne1 || chaîne2 : Renvoie la chaîne obtenue en concaténant

chaîne1 à chaîne2. Cette fonction est équivalente à l'opérateur de concaténation | |. - INITCAP(chaîne) : Renvoie chaîne en ayant mis la première lettre de chaque mot en majuscule et

toutes les autres en minuscule. Les séparateurs de mots sont les espaces et les caractères non alphanumériques.

- LOWER(chaîne) : Renvoie chaîne en ayant mis toutes ses lettres en minuscules. - LPAD(chaîne,long,[char]) : Renvoie la chaîne obtenue en complétant, ou en tronquant, chaîne

pour qu'elle ait comme longueur long en ajoutant éventuellement à gauche le caractère (ou la chaîne de caractères) char. La valeur par défaut de char est un espace.

- LTRIM(chaîne[,ens]) : Renvoie la chaîne obtenue en parcourant à partir de la gauche chaîne et en supprimant tous les caractères qui sont dans ens. On s'arrête quand on trouve un caractère qui n'est pas dans ens. La valeur de défaut de ens est un espace.

- REPLACE(chaine,avant,après) : Renvoie chaine dans laquelle toutes les occurrences de la chaîne de caractères avant ont été remplacés par la chaîne de caractères après.

Page 36: Chapitre 7 Langage SQL - x.heurtebise.free.frx.heurtebise.free.fr/Enseignements/Monitorat/L3/Info/BDD/PDF/BDD... · SQL (Structured Query Language, ou langage de requêtes structurées)

Base de données 71

- RPAD(chaîne,n,[char]) : Renvoie la chaîne obtenue en complétant, ou en tronquant, chaîne pour qu'elle ait comme longueur n en ajoutant éventuellement à droite le caractère (ou la chaîne de caractères) char. La valeur par défaut de char est un espace.

- RTRIM(chaîne[,ens]) : Renvoie la chaîne obtenue en parcourant à partir de la droite chaîne et en supprimant tous les caractères qui sont dans ens. On s'arrête quand on trouve un caractère qui n'est pas dans ens. La valeur de défaut de ens est un espace.

- SOUNDEX(chaîne) : Renvoie la chaîne de caractères constituée de la représentation phonétique des mots de chaîne.

- SUBSTR(chaîne,m[,n]) : Renvoie la partie de chaîne commençant au caractère m et ayant une longueur de n.

- TRANSLATE(chaîne,avant,après) : Renvoie une chaîne de caractères en remplaçant chaque caractère de chaîne présent dans avant par le caractère situé à la même position dans après. Les caractères de chaîne non présents dans avant ne sont pas modifiés. avant peut contenir plus de caractères que après, dans ce cas les caractères de avant sans correspondant dans après seront supprimés de chaîne.

- UPPER(chaîne) : Renvoie chaîne en ayant mis toutes ses lettres en majuscules. - number = INSTR(chaîne,sous-chaîne,debut,occ) : Renvoie la position, noté number, du premier

caractère de chaîne correspondant à l'occurrence occ de sous-chaîne en commençant la recherche à la position début.

- number = LENGTH(chaîne) : Renvoie la longueur de chaîne, noté number, exprimée en nombre de caractères.

SELECT UPPER nom FROM EMP;

Cette instruction SQL permet d’afficher le nom des employés de la table EMP en majuscule.

3. Expressions et fonctions sur les dates

a. Opérateurs sur les dates - date +/– nombre : le résultat est une date obtenue en ajoutant/soustrayant le nombre de jours

nombre à la date date. - date2 – date1 : le résultat est le nombre de jours entre les deux dates.

b. Fonctions sur les dates - ADD_MONTHS(date,n) : Renvoie la date obtenue en ajoutant n mois à date. n peut être un

entier quelconque. Si le mois obtenu a moins de jours que le jour de date, le jour obtenu est le dernier du mois.

- LAST_DAY(date) : Renvoie la date du dernier jour du mois de date. - MONTHS_BETWEEN(date2, date1) : Renvoie le nombre de mois entre date2 et date1, si

date2 est après date1 le résultat est positif, sinon le résultat est négatif. Si les jours date2 et date1 sont les mêmes, ou si ce sont les derniers jours du mois, le résultat est un entier. La partie fractionnaire est calculée en considérant chaque jour comme 1/31ème de mois

- NEXT_DAY(date,nom_du_jour) : Renvoie la date du prochain jour de la semaine dont le nom est nom_de_jour.

- ROUND(date[,précision]) : Renvoie date arrondie à l'unité spécifiée dans précision. L'unité de précision est indiquée en utilisant un des masques de mise en forme de la date. On peut ainsi arrondir une date à l'année, au mois, à la minute,... Par défaut la précision est le jour.

- SYSDATE : Renvoie la date et l'heure courantes du système d'exploitation hôte. - TRUNC(date[,précision]) : Renvoie date tronquée à l'unité spécifiée dans précision. Les

paramètres sont analogues à ceux de la fonction ROUND.

SELECT NEXT_DAY(embauche,’MONDAY’) FROM EMP;

Page 37: Chapitre 7 Langage SQL - x.heurtebise.free.frx.heurtebise.free.fr/Enseignements/Monitorat/L3/Info/BDD/PDF/BDD... · SQL (Structured Query Language, ou langage de requêtes structurées)

Base de données 72

Cette instruction SQL permet d’afficher la date du lundi suivant l’embauche de chaque employé de la table EMP.

SELECT ROUND(embauche,’Y’) FROM EMP;

Cette instruction SQL permet d’afficher la date d’embauche de chaque employé de la table EMP arrondie à l’année.

SELECT ROUND(SYSDATE-embauche) FROM EMP;

Cette instruction SQL permet d’afficher le nombre de jours depuis l’embauche de chaque employé de la table EMP.

4. Fonctions de conversion - ASCII(chaîne) : Renvoie le nombre correspondant au code ascii du premier caractère de chaine. - CHR(nombre) : Renvoie le caractère dont nombre est le code ascii. - TO_CHAR(nombre,format) : Renvoie la chaîne de caractères en obtenue en convertissant nombre

en fonction de format. Format est une chaîne de caractères pouvant contenir les caractères suivants :

o 9 : représente un chiffre (non représenté si non significatif) o 0 : représente un chiffre (représenté même si non significatif) o . : point décimal apparent o V : définit la position du point décimal non apparent o , : une virgule apparaîtra à cet endroit o $ : un $ précédera le premier chiffre significatif o B : le nombre sera représenté par des blancs s'il vaut 0 o EEEE : le nombre sera représenté avec un exposant (le spécifier avant MI ou PR) o MI : le signe négatif sera à droite o PR : un nombre négatif sera entre <>

- TO_CHAR(date,format) : Renvoie conversion d'une date en chaîne de caractères. Le format indique quelle partie de la date doit apparaître, c'est une combinaison des codes suivants :

o scc : siècle avec signe o cc : siècle o sy,yyy : année (avec signe et virgule) o y,yyy : année( avec virgule) o yyyy : année o yyy : 3 derniers chiffres de l'année o yy : 2 derniers chiffres de l'année o y : dernier chiffre de l'année o q : numéro du trimestre dans l'année o ww : numéro de la semaine dans l'année o w : numéro de la semaine dans le mois o mm : numéro du mois o ddd : numéro du jour dans l'année o dd : numéro du jour dans le mois o d : numéro du jour dans la semaine o hh ou hh12 : heure (sur 12 heures) o hh24 : heure sur 24 heures o mi : minutes o ss : secondes o sssss : secondes après minuit o j : jour du calendrier julien

Page 38: Chapitre 7 Langage SQL - x.heurtebise.free.frx.heurtebise.free.fr/Enseignements/Monitorat/L3/Info/BDD/PDF/BDD... · SQL (Structured Query Language, ou langage de requêtes structurées)

Base de données 73

Les formats suivants permettent d'obtenir des dates en lettres (en anglais) : o syear ou year : année en toutes lettres o month : nom du mois o mon : nom du mois abrégé sur 3 lettres o day : nom du jour o dy : nom du jour abrégé sur 3 lettres o am ou pm : indication am ou pm o bc ou ad : indication avant ou après Jésus Christ

Les suffixes suivants modifient la présentation du nombre auquel ils sont accolés : o th : ajout du suffixe ordinat st, nd, rd, th o sp : nombre en toutes lettres

Tout caractère spécial inséré dans le format sera reproduit tel quel dans la chaîne de caractères résultat.

- TO_DATE(chaîne,format) : Permet de convertir une chaîne de caractères en donnée de type date. Le format est identique à celui de la fonction TO_CHAR.

- TO_NUMBER(chaîne) : Convertit chaîne en sa valeur numérique.

Remarque : On peut également insérer dans le format une chaîne de caractères quelconque, à condition de la placer entre guillemets "".

SELECT TO_CHAR(embauche,‘DD/MM/YY HH24:MI:SS’) FROM EMP;

Cette instruction SQL permet d’afficher les dates d’embauche de chaque employé de la table EMP sous le format jour/mois/année heure:minute:seconde.

SELECT nom FROM EMP WHERE SOUNDEX(nom)=SOUNDEX(‘DUPONT’);

Cette instruction SQL permet d’afficher la liste de tous les employés de la table EMP dont le nom ressemble à ‘DUPONT’.

SELECT LTRIM(nom,‘LE’) FROM EMP;

Cette instruction SQL permet d’afficher la liste de tous les employés de la table EMP en ayant supprimé tous les ‘L’ et les ‘E’ en tête des noms.

SELECT TRANSLATE(nom,‘AM’,‘**’) FROM EMP;

Cette instruction SQL permet d’afficher la liste de tous les employés de la table EMP en ayant remplacé les ‘A’ et les ‘M’ par des ‘*’ dans les noms.

SELECT TO_CHAR(salaire,‘<MATH>99900.00’) FROM EMP;

Cette instruction SQL permet d’afficher les salaires de chaque employé de la table EMP avec un $ en tête et au moins trois chiffres (dont deux décimales).

Page 39: Chapitre 7 Langage SQL - x.heurtebise.free.frx.heurtebise.free.fr/Enseignements/Monitorat/L3/Info/BDD/PDF/BDD... · SQL (Structured Query Language, ou langage de requêtes structurées)

Base de données 74

5. Autres fonctions - GREATEST(expr1,expr2,…) : Renvoie la plus grande des valeurs expr1, expr2,.... Toutes les

expressions sont converties au format de expr1 avant comparaison. - LEAST(expr1,expr2,…) : Renvoie la plus petite des valeurs expr1, expr2,.... Toutes les

expressions sont converties au format de expr1 avant comparaison. - NVL(expr1,expr2) : Prend la valeur expr1, sauf si expr1 est NULL auquel cas NVL prend la

valeur expr2. Une valeur NULL en SQL est une valeur non définie. Lorsque l'un des termes d'une expression a la valeur NULL, l'expression entière prend la valeur NULL. D'autre part, un prédicat comportant une comparaison avec une expression ayant la valeur NULL prendra toujours la valeur faux. La fonction NVL permet de remplacer une valeur NULL par une valeur significative.

- DECODE(crit,val_1,res_1[,val_2,res_2…],def) : Cette fonction permet de choisir une valeur parmi une liste d'expressions, en fonction de la valeur prise par une expression servant de critère de sélection. Le résultat récupéré est :

o res_1 si l'expression crit a la valeur val_1 o res_2 si l'expression crit a la valeur val_2 o def (la valeur par défaut) si l'expression crit n'est égale à aucune des expressions val_1,

val_2,...,. Les expressions résultats res_1, res_2, ..., def peuvent être de types différents : caractère et numérique, ou caractère et date (le résultat est du type de la première expression rencontré dans le DECODE). La fonction DECODE permet également de mélanger dans une colonne résultat des informations venant de plusieurs colonnes d'une même table.

SELECT nom, salaire, comm, salaire+NVL(comm,0) FROM EMP;

Cette instruction SQL permet d’afficher le salaire, la commission et le total des gains de chaque employé de la table EMP.

SELECT nom, DECODE(function,'PRESIDENT',1,'DIRECTEUR',2,3) FROM EMP;

Cette instruction SQL permet d’afficher la liste des employés de la table EMP avec pour chacun d'eux sa catégorie (président = 1, directeur = 2, autre = 3).

SELECT DECODE(Detpno,10,fonction,nom) FROM EMP;

Cette instruction SQL permet d’afficher la liste des employés de la table EMP en les identifiant par leur fonction dans le département 10 et par leur nom dans les autres départements.