Table des matières

Introduction Générale.................................................................................................................................2

I. Introduction.........................................................................................................................................2

II. Objectifs des SGBD............................................................................................................................2

III. Fonctionnalités d’un SGBD............................................................................................................4

IV. Architecture fonctionnelle d’un SGBD...........................................................................................4

V. Modèles de SGBD..............................................................................................................................5

Chapitre 1 : DBA (Data Base Administration).........................................................................................6

I. Objectifs du chapitre...........................................................................................................................6

II. Architecture d’une Base de données Oracle.......................................................................................6

III. Structures mémoire Oracle..............................................................................................................6

1. Mémoire SGA (System Global Area):............................................................................................7

2. Processus Oracle.............................................................................................................................7

3. Gestion d’une instance Oracle........................................................................................................8

IV. Structure physique d’une BD..........................................................................................................9

V. Structures logiques et physiques d’une BD......................................................................................10

VI. Gestion d’une instance..................................................................................................................12

VII. Conclusion : Récapitulatif des composants structurels.................................................................12

Chapitre 2 : Gestion des utilisateurs dans une BD Oracle.....................................................................13

I. Qu’est-ce qu’un privilège?................................................................................................................13

II. Les privilèges Système.....................................................................................................................13

III. Les privilèges Objet......................................................................................................................15

1. Accorder les privilèges Objet........................................................................................................15

2. Retirer les privilèges :...................................................................................................................15

Cours SGBD 1 ISET Gabès

Chapitre 3 : SQL avancé...........................................................................................................................16

I. Introduction :.....................................................................................................................................16

II. Les ordres de SQL :..........................................................................................................................16

III. Les requêtes simples :...................................................................................................................17

1. Syntaxe générale d'une recherche :...............................................................................................17

2. Expressions numériques :..............................................................................................................17

3. Expression textuelle Concaténation :............................................................................................18

IV. Jointures :......................................................................................................................................18

V. Les sous requêtes :............................................................................................................................19

1. Sous-requêtes simples :.................................................................................................................19

2. Sous-requêtes synchronisées :.......................................................................................................19

3. Les opérateurs associés aux sous-requêtes :..................................................................................19

VI. Fonctions de groupe :....................................................................................................................20

VII. Regroupement :.............................................................................................................................21

1. Calcul sur plusieurs groupes :.......................................................................................................21

2. Regroupement Ordre des critères :................................................................................................21

3. Regroupement clause SELECT :..................................................................................................21

VIII. DISTINCT :..................................................................................................................................22

IX. La clause HAVING :.....................................................................................................................22

X. Fonctions individuelles :...................................................................................................................22

1. Fonctions de manipulation de casse :............................................................................................22

2. Fonctions de manipulation de caractères :....................................................................................23

3. Fonctions de manipulation des dates :..........................................................................................23

XI. Conversion de données :...............................................................................................................24

XII. Les fonctions générales :...............................................................................................................25

XIII. Les expressions conditionnelles....................................................................................................26

1. Expression CASE :........................................................................................................................26

2. Expression DECODE :..................................................................................................................26

Cours SGBD 2 ISET Gabès

 XIV. Opérateurs de comparaison.......................................................................................................27

Chapitre 3 : Les objets de la BD Oracle..................................................................................................28

I. Définition et manipulation des tables :.............................................................................................28

II. Les vues :..........................................................................................................................................29

1. Définition :....................................................................................................................................29

2. Création d'une vue - CREATE VIEW :........................................................................................29

3. Création de vues à partir de plusieurs tables.................................................................................29

4. Suppression d'une vue...................................................................................................................30

5. Renommer une vue:......................................................................................................................30

III. Les Séquences :.............................................................................................................................30

1. Création d’une séquence :.............................................................................................................30

2. Modifier une séquence :................................................................................................................31

3. Supprimer une séquence :.............................................................................................................31

IV. Les Index :.....................................................................................................................................31

V. Synonyme.........................................................................................................................................32

Chapitre 3 : Le langage PL/SQL et les Triggers.....................................................................................33

I. Introduction.......................................................................................................................................33

II. Quelques éléments du langage..........................................................................................................33

1. Notion de bloc...............................................................................................................................33

2. Structures de contrôle....................................................................................................................33

3. Les boucles....................................................................................................................................34

4. Les types et les variables...............................................................................................................34

5. Déclaration d'un type composé.....................................................................................................34

6. Utilisation de types implicites.......................................................................................................35

III. Exploitation des requêtes SQL......................................................................................................35

1. Les curseurs...................................................................................................................................36

2. Manipulation d'un curseur :...........................................................................................................36

3. Curseur implicite...........................................................................................................................38

Cours SGBD 3 ISET Gabès

 IV. Les exceptions...............................................................................................................................38

1. Déclenchement et traitement d'une exception...............................................................................38

2. Quelques exceptions prédéfinies...................................................................................................39

3. Déclaration d'une exception..........................................................................................................40

4. Les exceptions et les codes d'erreur..............................................................................................40

V. Les modules stockés.........................................................................................................................41

1. Procédures et fonctions.................................................................................................................41

2. Les paramètres..............................................................................................................................41

3. Les paquetages..............................................................................................................................42

VI. Les Triggers..................................................................................................................................44

1. Structure d'un trigger.....................................................................................................................44

2. Entête du trigger............................................................................................................................44

3. After ou Before ?...........................................................................................................................45

4. Trigger ligne ou instruction ?........................................................................................................45

5. Clause When.................................................................................................................................45

6. Ordre d'exécution des triggers.......................................................................................................46

7. Corps du trigger.............................................................................................................................46

Bibliographie..............................................................................................................................................48

Cours SGBD 4 ISET Gabès

 Introduction Générale
I. Introduction
Un système de gestion de bases de données (SGBD) est un logiciel qui permet d’interagir avec une
base de données. Un SGBD est un logiciel permettant de :
- Décrire - la confidentialité
- Manipuler en assurant  - l’intégrité
- Consulter les données - la sécurité
- Définir des contraintes d’intégrités - le partage des données

Figure 1 : Emplacement d’un SGBD

II. Objectifs des SGBD

1. Indépendance physique : Plus besoin de travailler directement sur les fichiers physiques (tels
qu’ils sont enregistrés sur disque).Un SGBD nous permet de décrire les données et les liens entre
elles d’une façon logique sans se soucier du comment cela va se faire physiquement dans les
fichiers. On parle alors d’image logique de la base de données, (ou aussi description logique ou
conceptuelle ou encore de schéma logique).Ce schéma est décrit dans un modèle de données par
exemple le modèle de tables, appelé le modèle relationnel. La manipulation des données doit être
facilitée en travaillant directement sur le schéma logique. On peut insérer, supprimer, modifier des
données directement sur l’image logique. Le SGBD va s’occuper de faire le travail sur les fichiers
physiques.

Figure 2 : Independence physique

Cours SGBD 5 ISET Gabès

 2. Indépendance logique : Un même ensemble de données peut être vu différemment par des
utilisateurs différents. Toutes ces visions personnelles des données doivent être intégrées dans une
vision globale.
3. Manipulations des données par des non informaticiens : Il faut pouvoir accéder aux données
sans savoir programmer, ce qui signifie des langages « quasi naturels ».
4. Efficacité des accès aux données : Ces langages doivent permettre d’obtenir des réponses aux
interrogations en un temps « raisonnable ». Il doivent donc être optimisés et, entre autres, il faut
un mécanisme permettant de minimiser le nombre d’accès disques. Tout ceci, bien sur, de façon
complètement transparente pour l’utilisateur.
5. Administration centralisée des données : Des visions différentes des données (entre autres) se
résolvent plus facilement si les données sont administrées de façon centralisée.
6. Cohérence des données : Les données sont soumises à un certain nombre de contraintes
d’intégrité qui définissent un état cohérent de la base. Elles doivent pouvoir être exprimées
simplement et vérifiées automatiquement à chaque insertion, modification ou suppression de
Données, par exemple : l’âge d’une personne supérieur à zéro, Salaire supérieur à zéro Etc. Dès
que l’on essaye de saisir une valeur qui ne respecte pas cette contrainte, le SGBD le refuse.
7. Non redondance des données : Afin d’éviter les problèmes lors des mises à jour, chaque donnée
ne doit être présente qu’une seule fois dans la base.
8. Partageabilité des données : Il s’agit de permettre à plusieurs utilisateurs d’accéder aux mêmes
données au même moment. Si ce problème est simple à résoudre quand il s’agit uniquement
d’interrogations et quand on est dans un contexte mono‐utilisateur, cela n’est plus le cas quand il
s’agit de modifications dans un contexte multi‐utilisateurs. Il s’agit alors de pouvoir :
 Permettre à deux (ou plus) utilisateurs de modifier la même donnée « en même temps »;
 Assurer un résultat d’interrogation cohérent pour un utilisateur consultant une table pendant
qu’un autre la modifie.
9. Sécurité des données : Les données doivent pouvoir être protégées contre les accès non
autorisés. Pour cela, il faut pouvoir associer à chaque utilisateur des droits d’accès aux données.
10. Résistance aux pannes : Que se passe‐t‐il si une panne survient au milieu d’une modification, si
certains fichiers contenant les données deviennent illisibles?
Les pannes, bien qu’étant assez rares, se produisent quand même de temps en temps. Il faut
pouvoir, lorsque l’une d’elles arrive, récupérer une base dans un état « sain ».
Ainsi, après une panne intervenant au milieu d’une modification deux solutions sont possibles :
 soit récupérer les données dans l’état dans lequel elles étaient avant la modification,
 soit terminer l’opération interrompue.

Cours SGBD 6 ISET Gabès

III. Fonctionnalités d’un SGBD
1. Description des données : codification structuration, grâce à unLangage de Description de Données
(LDD)
2. Manipulation et restitution des données (insertion, mise à jour, interrogation)

 Mise en oeuvre à l’aide d’un Langage de Manipulation de Données(LMD)

 S.Q.L. (Structures Query Langage) : Langage standard
3. Contrôle (partage, intégrité, confidentialité, sécurité) Schéma = structure + contraintes

Formule logique (E.g. Nom character 20, non-NULL; age integer between 0 and 120; debit <= credit).
But: protéger les données

IV. Architecture fonctionnelle d’un SGBD

Figure 3 : Architecture fonctionnelle d’un SGBD

1. Architecture Ansi/Sparc – 3 niveaux:
Niveau externe: le niveau d’expression des besoins des users. Il formalise la manière dont les utilisateurs
voient les données. Environnement de programmation / interfaces graphiques /deboggueurs / …
Niveau conceptuel: décrit la structure de la base de données globalement à tous les utilisateurs (limite la
redondance). Compilation / optimisation des requêtes / maintien de l’intégrité /gestion de la
confidentialité.
Niveau interne: relatif à la mémoire physique
• Gestion de la mémoire secondaire (fichiers), schéma, index
• Gestion de la concurrence
• Reprise après panne

Cours SGBD 7 ISET Gabès

 Figure 4 : Séparation en trois niveaux d’un SGBD

2. Avantages de la séparation en 3 niveaux :

Indépendance physique: on peut modifier l’organisation des données sans toucher les programmes de
traitement. Limiter les modifications liées aux changements de matériel, de système d’exploitation ou des
logiciels utilisés.
Indépendance logique: une modification de l’organisation logique des fichiers (e.g. une nouvelle
rubrique) n’entraîne pas de modification dans les programmes d’application non concernés. la vision de
chaque utilisateur est indépendante des visions des autres utilisateurs et n’est pas modifiée par les
modifications du schéma conceptuel qui ne le concernent pas.

V. Modèles de SGBD
SGBD Hiérarchique: Les données sont représentées dans la base sous forme de structure arborescente.
Manipulation des données (balayage ascendant/descendant).

SGBD réseau: Les données sont représentées dans la base sous forme d’un graphe quelconque. Les
programmes ne sont pas indépendants de la structure logique de la base, doivent indiquer le chemin
d’accès aux données et utilisent un langage complexe pour travailler avec leurs données.

SGBD relationnel: fondé sur la théorie mathématique des relations; représentation très simple des
données (tables); langage non procédural (déclaratif), puissant et simple d’emploi SQL est un standard
parmi ces langages dominent le marché: Exemples : Oracle, DB2, SQLServer, Access, DBase, Paradox,
… etc.

SGBD Objet: enregistre les données sous forme d’objets E. g. O2

Cours SGBD 8 ISET Gabès

 Chapitre 1 : DBA (Data Base Administration)

I. Objectifs du chapitre
À la fin de ce chapitre, vous pourrez:

 Comprendre la structuration du SGBD Oracle

 Décrire l'architecture d'Oracle Database

II. Architecture d’une Base de données Oracle

Chaque BD (base de données) qui s’exécute est associée à une instance oracle. Une instance oracle est la
combinaison d’une SGA et de processus oracle en arrière-plan

Figure 5 : Architecture d’une Base de données Oracle

III. Structures mémoire Oracle

Les structures mémoire de base associées à une instance Oracle sont :
Mémoire SGA (System Global Area) : partagée par tous les processus serveur et processus en arrière-
plan. Est une zone de mémoire contenant les données et les informations de contrôle de l'instance
Mémoire PGA (Program Global Area) : propre à chaque serveur et processus en arrière-plan. Il existe
une mémoire PGA pour chaque processus

Cours SGBD 9 ISET Gabès

 Figure 6 : Structures mémoire Oracle

1. Mémoire SGA (System Global Area):

La mémoire SGA contient les structures de données suivantes :
Cache de tampons (buffer cache) de la base de données : met en mémoire cache les blocs de données
extraits de la base
Tampon de journalisation (redo log buffer) : met en mémoire cache les informations de journalisation
(utilisées pour la récupération de l'instance) jusqu'à ce qu'elles puissent être écrites dans les fichiers de
journalisation physiques stockés sur le disque
Zone de mémoire partagée (Shared Pool) : met en mémoire cache diverses structures pouvant être
partagées par les utilisateurs, utilisée pendant la phase d’analyse des ordres SQL passés à un processus
Oracle, elle contient principalement le cache du dictionnaire de données (Dictionnary Cache) et le cache
de bibliothèques (Library Cache)
Zone de mémoire LARGE POOL : zone facultative fournissant d'importantes allocations mémoire pour
certains processus utilisant beaucoup de mémoire, tels que les opérations de sauvegarde et de récupération
Oracle et les processus serveur d'entrée-sortie
Zone de mémoire Java : zone utilisée pour l'ensemble du code Java et des données propres à la session
dans la JVM (Java Virtual Machine)
Zone de mémoire Streams : zone utilisée par Oracle Streams (pour la réplication)

2. Processus Oracle

Figure 7: Processus Oracle

System Monitor (SMON) : effectue une récupération après panne lorsque l'instance est démarrée après
une défaillance.
Process Monitor (PMON) : effectue un nettoyage de processus lorsqu'un processus utilisateur échoue.

Cours SGBD 10 ISET Gabès

Database Writer (DBWn) : écrit les blocs modifiés du cache detampons de la base dans des fichiers de
données stockés sur disque.
Point de reprise (CKPT) : met à jour l'ensemble des fichiers dedonnées et de contrôle de la base afin
d'indiquer le point de reprise le plus récent.
LogWriter (LGWR) : écrit les entrées de journalisation sur le disque.
Processus d'archivage (ARCn) : copie les fichiers de journalisation (fichiers redo log) dans le lieu
destiné au stockage des archives lorsqu'un changement de fichier de journalisation se produit.

3. Gestion d’une instance Oracle

Figure 8: Gestion d’une instance Oracle 1

L’exemple illustre une configuration Oracle dans laquelle l'utilisateur et les processus serveur associés
utilisent des ordinateurs distincts (connectés entre eux via un réseau).
1 Une instance a été démarrée sur l'ordinateur exécutant Oracle (souvent appelé hôte ou serveur de base
de données).
2 L'ordinateur exécutant l'application (ordinateur local ou poste client) utilise un processus utilisateur.
L'application client tente d'établir une connexion avec l'instance en utilisant le pilote Oracle Net Services.
3 L'instance détecte la demande de connexion émanant de l'application et se connecte à un processus
serveur pour le compte du processus utilisateur.

Cours SGBD 11 ISET Gabès

 Figure 10: Gestion d’une instance Oracle 2

4 L'utilisateur met à jour une ligne

5 Le processus serveur reçoit l'instruction et vérifie si elle se trouve déjà dans la zone de mémoire
partagée de la mémoire SGA. Si une zone SQL partagée est détectée, le processus serveur vérifie les
privilèges d'accès de l'utilisateur par rapport aux données demandées et la zone SQL partagée existante
est utilisée pour le traitement de l'instruction. Si l'instruction ne se trouve pas dans la zone de mémoire
partagée, une nouvelle zone SQL partagée est allouée pour celle-ci afin qu'elle puisse être analysée et
traitée.
6 Le processus serveur extrait les valeurs des données nécessaires du fichier de données (table) ou des
blocs de données stockés dans la mémoire SGA
7 Le processus serveur modifie les données de la table dans la mémoire SGA.
8 Lorsque la transaction est validée (commit), le processus LGWR enregistre immédiatement la
transaction dans le fichier de journalisation (fichier redo log).
9 Le processus DBWn écrit les blocs modifiés sur le disque lorsque cela s'avère utile.
10 Le processus serveur envoie un message de succès ou d'erreur à l'application via le réseau.

IV. Structure physique d’une BD

Les fichiers constituant une base de données Oracle sont organisés de la façon suivante :
Fichiers de contrôle : contiennent des données sur la base elle-même (informations sur la structure
physique de la base de données). Ces fichiers sont d'une importance capitale pour la base. Sans eux, vous
ne pouvez pas ouvrir de fichiers de données pour accéder aux données de la base.
Fichiers de données : contiennent les données utilisateur ou les données d'application de la base.
Fichiers de journalisation en ligne : permettent la récupération d'une instance de base de données. S'il
se produit une panne de la base sans perte des fichiers de données, l'instance peut récupérer la base grâce
aux informations contenues dans ces fichiers.

Cours SGBD 12 ISET Gabès

 Les fichiers supplémentaires ci-dessous permettent à la base de données de s'exécuter correctement :
Fichier de paramètres : permet de définir comment l'instance est configurée lors de son lancement.
Fichier de mots de passe : permet aux utilisateurs de se connecter à distance à la base de données et
d'effectuer des tâches d'administration.
Fichiers de sauvegarde : ces fichiers sont utilisés pour la récupération de la base de données. Les
fichiers de sauvegarde sont généralement restaurés lorsqu'une défaillance physique ou une erreur
utilisateur a endommagé ou supprimé les fichiers d'origine.
Fichiers de journalisation archivés : contiennent l'historique complet des modifications de données
(informations de journalisation) générées par l'instance. Vous pouvez, à l'aide de ces fichiers et d'une
sauvegarde de la base, restaurer un fichier de données perdu. Les fichiers de journalisation archivés
permettent de récupérer des fichiers de données restaurés.
Fichiers trace : chaque processus serveur et chaque processus en arrière-plan peut écrire dans un fichier
trace associé. Lorsqu'une erreur interne est détectée par un processus, ce dernier procède à un dump des
informations sur l'erreur vers son fichier trace. Certaines informations écrites dans un fichier trace sont
destinées à l'administrateur de base de données, tandis que d'autres s'adressent aux services de support
technique
Fichiers d’alertes : sont des fichiers trace spécifiques. Le fichier d'alertes d'une base de données est un
journal chronologique des messages et des erreurs. Oracle recommande de consulter ces fichiers.

V. Structures logiques et physiques d’une BD

Une base de données Oracle est un ensemble de données traité comme un tout. L'objectif général d'une
base de données est de stocker et d'extraire des informations associées. Une base de données comprend
des structures logiques et des structures physiques

Figure 10 : V. Structures logiques et physiques d’une BD

Cours SGBD 13 ISET Gabès

Tablespaces : Une base de données est divisée en unités de stockage logiques appelées tablespaces, qui
regroupent des structures logiques associées. Par exemple, les tablespaces regroupent habituellement tous
les objets d'une application pour simplifier certaines opérations d'administration. Vous pouvez disposer
d'un premier tablespace pour les données d'application et d'un second pour les index d'application.
Schémas : Un schéma est un ensemble d'objets de base de données appartenant à un utilisateur de la base.
Les objets de schéma sont les structures logiques qui font directement référence aux données de la base.
Par exemple, ces structures peuvent être des tables, des vues, des séquences, des procédures stockées, des
synonymes, des index, des clusters et des liens de base de données. En général, les objets de schéma
incluent tout ce que l'application crée dans la base de données.
Blocs de Données : Au niveau de détail le plus fin, les données d'une base Oracle sont stockées dans des
blocs de données. Un bloc de données correspond à un nombre d'octets spécifique d'espace physique sur
le disque. La taille du bloc de données est indiquée pour chaque tablespace lors de la création de celui-ci.
Chaque base utilise et alloue de l'espace libre de base de données dans les blocs de données Oracle.
Extents : Extents (ensembles de blocs contigus) Le niveau logique d'une base de données s'appelle un
extent. Un extent est un nombre défini de blocs de données contigus (obtenus par une allocation unique)
permettant de stocker un type spécifique d'informations.
Segments : Le niveau logique de stockage situé au-dessus d'un extent s'appelle un segment. Un segment
est un ensemble d'extents alloués pour une certaine structure logique. Seuls les objets 'physiques' peuvent
être des segments.
Ainsi une vue ou un synonyme n'est pas un segment... Les différents types de segment Segments de
données : Chaque table comporte un segment de données. Toutes les données de la table sont stockées
dans les extents de son segment de données.
Segments d'index : Chaque index comporte un segment d'index qui stocke l'ensemble de ses données.
Segments d'annulation (rollback segment) : qui stocke l'image avant modification des données. Un
tablespace UNDO est créé par l'administrateur de base de données afin de stocker temporairement les
informations d'annulation. Les informations contenues dans un segment d'annulation permettent de
générer des informations de base de données cohérentes en lecture et, lors de la récupération de la base,
d'annuler les transactions non validées pour les utilisateurs.
Segments temporaires : La base de données Oracle crée des segments temporaires lorsqu'une instruction
SQL requiert une zone de travail temporaire pour son exécution (tri par exemple). Lorsque l'exécution de
l'instruction est terminée, les extents du segment temporaire sont rendus à l'instance en vue d'une
utilisation ultérieure. On peut indiquer un tablespace temporaire par défaut pour chaque utilisateur ou à
l'échelle de la base de données.

Cours SGBD 14 ISET Gabès

VI. Gestion d’une instance
Une instance de base de données Oracle peut être dans quatre statuts :
 Arrêtée
 Etat NoMount
 Etat Mount (montée)
 Ouverte

Démarrage d’une base de données Oracle

Il est possible de démarrer les bases de données avec les commandes suivantes:

Arrêt d’une base de données Oracle : quatre types d’arrêt

 Normal
 Transactionnel
 Immediate
 Abort

VII. Conclusion : Récapitulatif des composants structurels

Structures mémoire : Mémoire SGA (System Global Area) : cache de tampons (buffer cache) de la base
de données, tampon de journalisation (redo buffer) et différents pools Mémoire PGA (Program Global
Area)

Processus : Processus utilisateur et processus serveur Processus en arrière-plan : SMON, PMON,

DBWn, CKPT,LGWR, ARCn, etc.

Structures de stockage : Structures logiques : base de données, schémas, tablespaces,segments, extents

et blocs Oracle. Structures physiques : fichiers de données, fichiers de paramètres, fichiers de
journalisation et blocs du système d'exploitation

Cours SGBD 15 ISET Gabès

 Chapitre 2 : Gestion des utilisateurs dans une BD
Oracle

I. Qu’est-ce qu’un privilège?

Les privilèges sont des droits pour exécuter des requêtes. Le plus haut niveau de privilèges sont des
privilèges DBA, il a la possibilité de donner aux utilisateurs l’accès à la base de données. Les utilisateurs
doivent posséder des privilèges système pour se connecter à la base de données, et les privilèges objets
pour manipuler des données

II. Les privilèges Système :

Un DBA peut créer des utilisateurs en utilisant la requête CREATE USER. Lorsqu’un utilisateur est créé,
il ne possède aucun privilège. Le DBA doit lui donner des privilèges souhaités

Tableau 1 : Les privilèges DBA

Lorsque le DBA a créé un utilisateur, il lui donne des privilèges. Exemple : Le DBA donne à l’utilisateur
la possibilité de se connecter à la base de données. Ce privilège est donné grâce à CREATE SESSION

Tableau 2 : Privilèges système accordés à un utilisateur

Cours SGBD 16 ISET Gabès

Pour accorder un privilège il faut suivre les étapes suivantes :

1) Créer un nouvel utilisateur

2) Donner le privilège CREATE SESSION à l’utilisateur

3) Donner au nouvel utilisateur le privilège CREATE TABLE

Exemple :

Créer et accorder un privilège à un rôle

Un rôle est un ensemble de privilèges. Lorsque le rôle est créé, le DBA utilise la requête GRANT pour
assigner ce rôle aux utilisateurs.

Modification du mot de passe

Cours SGBD 17 ISET Gabès

Exemple :

III. Les privilèges Objet :

Tableau 3 : Les privilèges Objet

1. Accorder les privilèges Objet :

Exemple :

Le privilège accordé avec la clause WITH GRANT OPTION donne la possibilité au nouvel utilisateur
d’accorder les privilèges sur cet objet aux autres utilisateurs Si vous enlevez ensuite le privilège à cet
utilisateur, tous les utilisateurs à qui il aura donné ce privilège se le verront enlevé aussi de manière
automatique

Exemples :

2. Retirer les privilèges :

Cours SGBD 18 ISET Gabès

 Chapitre 3 : SQL avancé

I. Introduction :
SQL (Structured Query Language) ou langage de requêtes structuré est un langage destiné à interroger et
manipuler les SGBDR. Il a été introduit par IBM en 1979. Le langage SQL est devenu Standard ANSI en
86 et ISO en 87. Plusieurs révisions: SQL1 en 1989, SQL2 en 1992 en , SQL3 en 1999 et SQL2003.
Le langage SQL comporte:
 LDD : Langage de Définition de Données (définir les tables, les vues, ..)
 LMD : Langage de Manipulation de Données (MAJ, Requêtes, ..)

II. Les ordres de SQL :

Tableau 4 : Les ordres de SQL

Cours SGBD 19 ISET Gabès

III. Les requêtes simples :
1. Syntaxe générale d'une recherche :

SELECT <liste des attributs projetés>

FROM <liste des relations touchées par la question>

WHERE <liste des critères de restriction>

GROUP BY <liste des attributs d'agrégation>

HAVING <liste des critères de restriction d’agrégats>

ORDER BY <liste des attributs de tri du résultat>

Exemple 1: Requête SQL simple

SELECT *

FROM T_Customer;

Exemple 2: Ordre et choix des colonnes clause SELECT

SELECT customer_name, city

FROM T_Customer;

Exemple 3: Ordre des lignes clause ORDER BY

SELECT customer_name, city SELECT customer_name, city

FROM T_Customer FROM T_Customer
ORDER BY customer_name; ORDER BY city;

Cours SGBD 20 ISET Gabès

 2. Expressions numériques :

Opérations : addition (+), soustraction (-), multiplication (*), division (/) et modulo (%)

Ordre d'évaluation : parenthèses, multiplication et division de gauche à droite, addition et soustraction

de gauche à droite.

Exemple:

SELECT MATERIAL_ID, unit_price, unit_price*1.12

FROM T_Raws_Materials;

3. Expression textuelle Concaténation :

Exemple :

SELECT customer_name, city || ', ' || STATE

FROM T_Customer

ORDER BY city;

IV. Jointures :
Une jointure permet d'exprimer des requêtes portant sur des données réparties sur plusieurs tables

Exemple: Donner le nom et prénom de tous les participants d'un colloque

Select NomP, PrenomP

from Inscrit, Participant

where Inscrit.NumeroP =

Participant. NumeroP and

NomC='Colloque1';

Cours SGBD 21 ISET Gabès

Dans l'ambiguïté (deux attributs ayant le même nom), on met le nom du table comme préfixe. Comme ce
n'est pas pratique de recopier intégralement le nom d'une table, on peut définir et utiliser des synonymes

Exemple:

Select NomP, PrenomP On peut faire intervenir plus que deux tables

from Inscrit as I, Participant as P Select NomP, PrenomP, DateC

from Inscrit I, Participant P, colloque C

where I.NumeroP = P.NumeroP
andNomC='Colloque1'; where I.NumeroP = P.NumeroP and

I.NomC = C.NomC;
.
Jointure sur la même table

Exemple: Donner des couples de colloques organisés par la même université

Select C1.NomC, C2.NomC
from colloque C1, colloque C2
Where C1.Universite = C2.Universite

V. Les sous-requêtes :
SQL permet d'utiliser des requêtes pour élaborer des conditions plus complexes et "dynamiques", c'est à
dire qu'un critère de recherche employé dans la clause WHERE soit lui-même le résultat d'un SELECT. Il
exist deux types de sous requête : Sous- requêtes simples et Sous- requêtes synchronisées.

1. Sous-requêtes simples :
Dans ce cas la sous-requête est d'abord évaluée, puis le résultat est utilisé pour exécuter la requête
principale.

Exemple : sélectionner la liste des employés ayant même poste que JONES

select ENAME from EMP

where JOB = ( select JOB from EMP
where ENAME = 'JONES' );

Remarque : les sous-requêtes sont plus lisibles que des jointures

2. Sous-requêtes synchronisées :
Dans ce cas la sous-requête est évaluée pour chaque n-uplet de la requête principale

Exemple : sélectionner des employés ne travaillant pas dans le même département que leur manager.

Cours SGBD 22 ISET Gabès

select ENAME from EMP e
where DEPTNO != ( select DEPTNO from EMP
where e.MGR = EMPNO);

Remarque : le synonyme emp de la requête principale est utilisé dans la sous-requête.

3. Les opérateurs associés aux sous-requêtes :

L'opérateur EXISTS permet de construire un prédicat vrai si la sous-requête qui suit ramène au moins
une ligne.

Syntaxe: select ' from nom-table where exists ( select --- )

Exercice : Liste des employés travaillant dans un département qui contient au moins un ANALYSTE ?

Les opérateurs ensemblistes : Dans les exemples précédent, le SELECT de la sous-requête ramenait un
seul n-uplet, car à droite du signe "=" se trouvait une seule valeur. Cependant une sous-requête peut
ramener plusieurs n-uplets (une liste de valeur).Les opérateurs permettant de comparer une valeur à un
ensemble de valeurs sont les opérateurs obtenus en ajoutant ANY et ALL à la suite d'un opérateur de
comparaison

 ANY : la comparaison sera vraie si elle est vraie pour au moins un élément de l'ensemble.
 ALL : la comparaison sera vraie si elle est vraie pour tous les éléments de l'ensemble.

Exercice : sélectionner les employés gagnant plus que tous les employés du département 30

Remarque L'opérateur IN est équivalent à l'opérateur = ANY. L'opérateur NOT IN est équivalent à
l'opérateur != ALL

Exercice : Employés du département "RESEARCH" embauchés le même jour que quelqu'un du

département "SALES":

Les sous-requêtes ramenant plusieurs colonnes : II est possible de comparer le résultat d'un SELECT
ramenant plusieurs colonnes à une liste de colonnes. La liste de colonnes figurera entre parenthèse à
gauche de l'opérateur de comparaison.

Syntaxe:

select col1, ... from nom table

where (col2,col3) = (select col2, col3 )

Exercice : sélectionner la liste des employés ayant même job et même salaire que FORD

Cours SGBD 23 ISET Gabès

VI. Fonctions de groupe :
II est possible d'utiliser certaines fonctions en demandant de grouper les résultats selon une ou plusieurs
colonnes. Les fonctions de groupe sont les suivantes

avg (col) : moyenne des valeurs (les valeurs NULL sont ignorées)

count (col) : nombre de n-uplet satisfaisant à la condition WHERE. Les valeurs NULL sont ignorées.

max (col): valeur maximale des valeurs de la colonne

min (col) : valeur minimale des valeurs de la colonne

sum (col) : Somme des valeurs de la colonne

variance (col) : variance des valeurs de la colonne

Exemples :

SELECT MIN(unit_price), SELECT COUNT(*) SELECT

MAX(unit_price) FROM T_Product ; COUNT(product_description)
FROM T_Product ; FROM T_Product ;

SELECT SELECT AVG(unit_price) SELECT MAX(order_date)

SUM(on_hand*unit_price) FROM T_Product; FROM T_Order;
FROM T_Product;

VII. Regroupement :
1. Calcul sur plusieurs groupes :
II est possible de subdiviser la table en groupes, chaque groupe étant l'ensemble des lignes ayant une
valeur commune.

Syntaxe: group by expr [,expr2 ...]

Exemple : donner la somme des salaires pour chaque département

Select SUM(SAL) from EMP

group by DEPTNO;

2. Regroupement Ordre des critères :

Cours SGBD 24 ISET Gabès

 3. Regroupement clause SELECT :
Dans un regroupement la clause SELECT à deux parties liste des attributs servant au regroupement et la
liste des fonctions ensemblistes évaluées sur chaque groupe

VIII. DISTINCT :
Le mot-clé distinct permet de supprimer les lignes identiques du résultat

IX. La clause HAVING :

La clause HAVING permet, de façon analogue à la clause WHERE, de poser des conditions sur le résultat
du regroupement

select col1 [col2 ...]

from table [, table2j
[where prédicat]
[group by expression [, expression]
[having prédicat] ]

Le prédicat figurant dans la clause HAVING ne peut porter que sur des caractéristiques des fonctions de
groupe figurant dans la clause GROUP BY.

Cours SGBD 25 ISET Gabès

X. Fonctions individuelles :
1. Fonctions de manipulation de casse :
UPPER ('lili') retourne 'LILI'

LOWER ('MARLÈNE') retourne 'marlène‘

INITCAP (‘cours de SQL') retourne ‘Cours De Sql‘

Tableau 5 : Fonctions de manipulation de casse

2. Fonctions de manipulation de caractères :

LENGTH (‘Bonjour') retourne 4

INSTR (‘Bonjour', ‘j') retourne 4

SUBSTR (‘Bonjour', 1,3) retourne ‘Bon’

LPAD(sal,10, ‘*') retourne ******5000

RPAD(sal,10, ‘*') retourne 5000******

CONCAT(‘Bon’,’jour’) retourne ‘Bonjour’

TRIM(leading|trailing|both,trim_character FROM trim_source)

TRIM(‘S’ FROM ‘SSSMITH’) retourne ‘MITH’

3. Fonctions de manipulation des dates :

SYSDATE affiche la date système

Tableau 6 : Fonctions de manipulation des dates

Cours SGBD 26 ISET Gabès

 Tableau 7 : Fonctions de manipulation des dates

XI. Conversion de données :

Figure 11 : Conversion de données

Cours SGBD 27 ISET Gabès

 Tableau 8: Conversion de donnée

Figure 11 : Conversion de données

Exemples :

XII. Les fonctions générales :

Syntaxe : NVL(expr1,expr2)

Fonction : Substituer les valeurs nulles d’une colonne par une valeur choisie

Exemple:

Syntaxe : NVL2(expr1,expr2,expr3)

Fonction : Afficher expr2 si expr1 est non nulle, sinon afficher expr3

Exemple:

Cours SGBD 28 ISET Gabès

 Syntaxe : NULLIF(expr1,expr2)

Fonction : Afficher NULL si expr1=expr2, sinon afficher expr1

Exemple:

XIII. Les expressions conditionnelles

1. Expression CASE :
Syntaxe :

Exemple :

2. Expression DECODE :
Syntaxe :

Exemple :

Cours SGBD 29 ISET Gabès

XIV. Opérateurs de comparaison
Format: Expression 1 Op Expression 2, =, <, >, <=, >=, <>, BETWEEN, IS NULL, LIKE

Exemples:

SELECT * SELECT * SELECT * SELECT * SELECT * SELECT *

FROM FROM FROM FROM FROM FROM
T_Customer T_Customer T_Customer COMMANDE COMMANDE T_Customer
WHERE WHERE WHERE WHERE WHERE WHERE
customer_name = customer_id = 2; customer_name < MONTANT < MONTANT >= 500; customer_name
'IMPRESSIONS'; 'M'; 500; <= 'M';

SELECT * SELECT * SELECT * SELECT * SELECT SELECT

FROM FROM FROM FROM COUNT(product_id) product_id,
T_Customer COMMANDE COMMANDE COMMANDE FROM T_Product product_name
WHERE WHERE WHERE WHERE WHERE FROM T_Product
customer_name NO_VENDEUR MONTANT MONTANT NOT product_description WHERE
<> <> 9; BETWEEN 100 BETWEEN 100 IS NULL; product_name
'IMPRESSIONS'; AND 200; AND 200; LIKE '%Desk%'

Cours SGBD 30 ISET Gabès

 Chapitre 3 : Les objets de la BD Oracle

Les tables, Les vues, Les séquences, Les index et

Les synonymes

I. Définition et manipulation des tables :

Tableau 9 : Définition et manipulation des tables

Opération Syntaxe Exemple

Création d’une table CREATE TABLE nom_de_la_table( CREATE TABLE Client (
attribut_1 type_1 NumCli NUMBER(3),
[PRIMARY KEY], Nom CHAR(30),
… DateNaiss DATE,
attribut_n type_n Salaire NUMBER (8,2),
) ; NumEmp NUMBER(3),
CONSTRAINT cle_pri PRIMARY
KEY (NumCli),
CONSTRAINT cle_etr FOREIGN
Clé primaire CONSTRAINT nom_const PRIMARY KEY (NumEmp) REFERENCES
KEY (liste_d_attributs) EMPLOYEUR(NumEmp),
CONSTRAINT date_ok CHECK
Clé étrangère CONSTRAINT nom_const FOREIGN (DateNaiss SYSDATE));
KEY (liste_d_attributs) REFERENCES );
nom_table (liste_d_attributs)

Contrainte d’intégrité CONSTRAINT nom_contrainte

CHECK (condition)

Modification de ALTER TABLE nom_table ADD| ALTER TABLE Client MODIFY

tables MODIFY (attribut TYPE, …); (NumCli NUMBER(5)) ;

Suppression de ALTER TABLE nom_table DROP ALTER TABLE Client DROP

contraintes CONSTRAINT nom_const CONSTRAINT date_ok ;

Insertion d’un tuple INSERT INTO nom_table INSERT INTO Client values (1,’ ali’,
( liste_attribut) VALUES ‘13-09-80’,500,2);
(liste_valeurs)

Mise à jour d’un tuple UPDATE nom_table SET UPDATE Client SET Nom=’med’
attribut=valeur [WHERE condition]; where NumCli = 1 ;

Cours SGBD 31 ISET Gabès

Suppression d’un DELETE FROM nom_table [WHERE DELETE FROM Client WHERE
tuple condition]; NumCli=1;

II. Les vues :

1. Définition :
Une vue est une table virtuelle existe pas dans la base. Elle est construite à partir du résultat d'un
SELECT. La vue sera vu par l'utilisateur comme une table réelle. Les vues permettent des accès
simplifiés aux données, l'indépendance des données la confidentialité des données : restreint les droits
d'accès à certaines colonnes ou à certains n-uplets.

2. Création d'une vue - CREATE VIEW :

La commande CREATE VIEW permet de créer une vue en spécifiant le SELECT constituant la définition
de la vue

Syntaxe :

create view nom [(col1, ...) ] ,.as.

select col1, col2, ...
from tab
where prédicat
[with check option]

La spécification des noms de colonnes de la vue est facultative. Par défaut, les colonnes de la vue ont
pour nom les noms des colonnes résultat de SELECT.

3. Création de vues à partir de plusieurs tables.

Exemple : créer une vue comportant le nom des employés, le nom du service et le lieu de travail.

Create view EMPLOYES2

as select ENAME, DNAME, LOC
from EMP emp, DEPT dept
where emp. DEPTNO = dept.DEPTNO

Requêtes et vues : Pour récupérer les données de vues, on procédera comme si l'on était en face d'une
table classique. Select * from EMPLOYES2 …

En réalité, cette table est virtuelle et est reconstruite à chaque appel de la vue EMPLOYES par exécution
du SELECT constituant la définition de la vue.

4. Suppression d'une vue

Une vue peut être détruite par la commande: drop view nom vue
Cours SGBD 32 ISET Gabès
 5. Renommer une vue:
Une vue peut être renommée par la commande: rename ancien-nom-vue to nouveau-nom-vue

III. Les Séquences :

Une séquence est un objet créé par l’utilisateur. Elle sert à créer des valeurs pour les clés primaires, qui
sont incrémentées ou décrémentées par le serveur Oracle.

Noter que la séquence est stockée et générée indépendamment de la table, et une séquence peut être
utilisée pour plusieurs tables.

1. Création d’une séquence :

Syntaxe :

Exemples :

Cours SGBD 33 ISET Gabès

 2. Modifier une séquence :

3. Supprimer une séquence :

IV. Les Index :

Un index est un objet qui peut augmenter la vitesse de récupération des lignes en utilisant les pointeurs.
Les index peuvent être créés automatiquement par le serveur Oracle ou manuellement par l’utilisateur. Ils
sont indépendants donc lorsque vous supprimez ou modifiez un index les tables ne sont pas affectées.

Syntaxe :

Exemple :

Cours SGBD 34 ISET Gabès

V. Synonyme
Un synonyme est un nom alternatif pour désigner un objet de la base de données. C'est aussi un objet de
la base de données.

Syntaxe :

Exemple :

Cours SGBD 35 ISET Gabès

 Chapitre 3 : Le langage PL/SQL

et les Triggers
I. Introduction

PL/SQL signifie procedural language with SQL. C'est un langage de programmation propriétaire (Oracle)
inspiré d’ADA. Ce langage a fortement inspiré la norme SQL3 car il est bien-sûr bien adapté à la
manipulation d'une base de donnée relationnelle : il utilise les même types que SQL, permet d'intégrer
facilement des requêtes dans le code, de définir des curseurs pour parcourir séquentiellement le résultat
d'une requête ...

Oracle utilise ce langage à la fois côté serveur et côté client :

 Côté Serveur : Définir des objets "programmes" persistants comme les modules stockés
(packages, procédures et fonctions) ou les triggers
 Côté Client : en SQLPlus, permet d'écrire des blocs anonymes. C'est aussi le langage de l'outils de
développement d'interface graphique Developer

II. Quelques éléments du langage

1. Notion de bloc

Comme en ADA, on définit la notion de bloc, qui contient une partie déclarative puis un corps qui
contient éventuellement un traitement d'exception :

[declare
...]
begin
...
[exception
...]
end ;

2. Structures de contrôle

Les structures de contrôle sont celles du langage ADA : Instructions conditionnelles

if condition then instr

{elsif condition then instr}
[else instr]
end if ; case variable

{when expression then instr}

Cours SGBD 36 ISET Gabès

 [else instr]
end case;

3. Les boucles
for i in [reverse] deb .. fin loop
instr
end loop;
while condition loop
instr
end loop;
loop
instr
exit when condition;
instr
end loop;

4. Les types et les variables

Les variables peuvent être : D’un type de base SQL (CHAR, DATE, ...) ou PL/SQL (BOOLEAN, sous-
type d'un type de base,...) Ou d'un type composé. La syntaxe d'une déclaration de variable est :

identificateur [CONSTANT] typeDeDonnée [NOT NULL] [(:= | DEFAULT) expression];

Quelques exemples :

num NUMBER(4) ;
num2 NUMBER NOT NULL := 3.5 ;
en_stock BOOLEAN := false ;
limite CONSTANT REAL := 5000.00 ; -- une constante, donc forcément initialisée

La contrainte NOT NULL doit être suivie d'une clause d'initialisation.Les déclarations multiples ne sont
pas permises (hélas !). On ne peut donc pas écrire : v1 , v2 NUMBER ;

5. Déclaration d'un type composé

Un type composé est : un type "record". Quand tous les attributs sont d'un type SQL, une variable de type
record peut représenter une ligne d'une table relationnelle. un type collection : TABLE, VARRAY
utilisés en relationnel objet. Un type objet : modèle relationnel objet. Voici un exemple de déclaration
d'un type record :

Type PERSONNE_REC is record(

nom VARCHAR2(15),
prenom VARCHAR2(20)
);

Une variable de ce type possède des champs p.nom, p.prenom, et on la déclare de la façon suivante :

p PERSONNE_REC ;

Cours SGBD 37 ISET Gabès

 6. Utilisation de types implicites

L’attribut %type signifie du même type ligne (record) que

v1 Employee.emp_name%type ;
-- v1 du même type que la colonne emp_name
-- de la table Employee
v2 v1%type ;
-- v2 du même type que v1

num NUMBER(5) ;
numbis num%type ;
-- numbis du même type que num

L’attribut %rowtype signifie du même type ligne (record) que

emp_rec Employee%rowtype ;
-- emp_rec est de type record
-- ses attributs suivent le schéma de Employee

III. Exploitation des requêtes SQL

Les instructions INSERT, DELETE, UPDATE s'écrivent telles quelles dans un programme. Elles peuvent
utiliser les variables du programme : il faut donc donner des noms différents aux variables du programme
et aux colonnes des tables manipulées par le programme. Pour une requête dont le résultat est constitué
d'une unique ligne, on peut utiliser la syntaxe SELECT ... INTO....

Pour une requête qui ramène un nombre quelconque de lignes, il faut utiliser un curseur.

create or replace
procedure ajouterEmp(leNom employee.emp_name%type,
lePrenom employee.emp_firstname%type) is
nouveauNum NUMBER ;
begin
select nvl(max(emp_no),0)+1 into nouveauNum
from employee ;

insert into employee(emp_no, emp_name,

emp_firstname)
values(nouveauNum, leNom, lePrenom);
end ajouterEmp;

Select ... into...

Syntaxiquement, c'est une requête SQL qui peut utiliser des variables du programme, la clause into
permet de préciser les noms des variables qui vont recevoir le résultat de la requête. (cf exemple
précédent). La requête ne doit donc renvoyer qu'une seule ligne.

Select liste into listeVariables

from ...

Cours SGBD 38 ISET Gabès

Lorsqu'on sélectionne plusieurs colonnes, il est aussi possible de remplacer la liste de variables par une
variable de type record.

select emp_name, emp_firstname

into le_nom, le_prenom
from Employee
where emp_no = 346;
ou bien :

select *
into emp_rec -- emp_rec de type Employee%rowtype
from Employee
where emp_no = 346;

Si la requête ne renvoie aucune ligne, l'exception NO_DATA_FOUND est déclenchée. Si au contraire

elle renvoie plus d'une ligne, l'exception TOO_MANY_ROWS est déclenchée.

1. Les curseurs

Un curseur est une structure de donnée séquentielle avec une position courante. On utilise un curseur pour
parcourir le résultat d'une requête SQL dans un programme PL/SQL. On déclare un curseur en associant
un nom à une requête.

CURSOR nom_curseur IS une_requête;

On peut définir des paramètres en entrée utilisés dans la requête

CURSOR nom_curseur(p1, ..., pn) IS une_requête ;

cursor emp_cursor(dnum NUMBER) is
select salary, comm
from Employee
where deptno = dnum ;

Si c’est un curseur, la ligne courante est de type c%rowtype, c'est à dire du type de la ligne de la requête
associée à c.

2. Manipulation d'un curseur :

 Instructions :
 OPEN : initialise le curseur
 FETCH : extrait la ligne courante et passe à la suivante (pas d'exception si plus de ligne)
 CLOSE : invalide le curseur
 Si on veut parcourir toutes les lignes : boucle FOR

Cours SGBD 39 ISET Gabès

 Attributs du curseur :

 %found vrai si le dernier fetch a ramené une ligne

 %notfound vrai si le dernier fetch n'a pas ramené de ligne
 %isopen vrai ssi le curseur est ouvert
 %rowcount le nombre de lignes déjà ramenées

Exemple de curseur sans paramètre :

declare
cursor c_proj is
select proj_name, proj_budget
from project
order by proj_budget ;

proj_rec c_proj%rowtype ;
begin
open c_proj ;
loop -- parcours des lignes du curseur
fetch c_proj into proj_rec ;
exit when c_proj%notfound ;
... on utilise la ligne courante rangée dans proj_rec ...
end loop ;
close c_proj ;
end ;

L'exemple précédent peut s'écrire :

declare
cursor c_proj is
select proj_name, proj_budget
from project
order by proj_budget ;

begin
for proj_rec in c_proj loop -- parcours des lignes du curseur
... on utilise la ligne courante rangée dans proj_rec ...
end loop ;
end ;

Exemple de curseur avec paramètre : Reprenons l'exemple du curseurs emp_cursor déclaré

précédemment. Lors de son utilisation, il faut instancier le paramètre formel dnum.

open emp_cursor(56) ;
fetch emp_cursor into emp_cursor_rec ;
...
close emp_cursor ;
ou bien

for emp_cursor_rec in emp_cursor(56) loop

...
end loop ;

Cours SGBD 40 ISET Gabès

 3. Curseur implicite

Quelque fois, PL/SQL déclare implicitement un curseur :

 pour les instructions du DML qui modifient la base (INSERT, DELETE, UPDATE)
 pour les requêtes de la forme SELECT INTO.

Avec un curseur implicite, on peut obtenir des informations sur la requête réalisée, grâce aux attributs. En
effet SQL%attribut applique l'attribut sur la dernière requête SQL exécutée

delete from Employee where ...

if SQL%rowcount > 10 then
-- on a supprimé plus de 10 lignes
...
end if ;

Attention, lorsqu'une requête de la forme select ... into ... a déclenché une exception
(NO_DATA_FOUND ou TOO_MANY_ROWS) il ne faut pas se fier aux valeurs retournées par les
attributs.

IV. Les exceptions

En PL/SQL, la gestion des erreurs se fait grâce aux exceptions. Le mécanisme de déclenchement /
traitement d'exception est identique à celui du langage ADA, je ne m'étendrai donc pas sur le sujet.

Il existe un certain nombre d'exceptions prédéfinies mais le programmeur peut bien-sûr définir ses
propres exceptions.

1. Déclenchement et traitement d'une exception.

Le déclenchement d'une exception nomException se fait à l'aide de l'instruction raise nomException ; Le

traitement de l'exception se fait dans une partie EXCEPTION (cf structure d'un bloc), dont la syntaxe est
légèrement différente de l'ADA.

BEGIN
...corps du bloc...
EXCEPTION
when exception1 [or exception2 ...] then
instructions ;
when exception3 [or exception4 ...] then
instructions ;
...
[when others then instructions ;]
END;

Cours SGBD 41 ISET Gabès

Si une exception se produit dans le corps du bloc, l'exécution de ce corps est interrompue et la partie
EXCEPTION du bloc est exécutée. Dans cette partie, on peut redéclencher l'exception que l'on vient de
traiter grâce à l'instruction raise ; Si l'exception existe dans une clause When, alors les instructions de
cette clause When sont exécutées et le programme est terminé. Si l'exception n'existe pas dans une clause
When, Soit il existe une clause When Others et dans ce cas les instructions de cette clause sont exécutées
et le programme est terminé, Soit il n'y a pas de clause When Others et l'exception est propagée au bloc
englobant ou au programme appelant. Si aucun traitement d'exception n'est rencontré, la transaction qui a
déclenché l'exception est annulée.

Voici un exemple extrait de la documentation Oracle :

DECLARE
pe_ratio NUMBER(3,1);
BEGIN
SELECT price / earnings INTO pe_ratio
FROM stocks
WHERE symbol = 'XYZ';
-- might cause division-by-zero error
INSERT INTO stats (symbol, ratio)
VALUES ('XYZ', pe_ratio);
EXCEPTION -- exception handlers begin
WHEN ZERO_DIVIDE THEN
INSERT INTO stats (symbol, ratio)
VALUES ('XYZ', NULL);
END;

2. Quelques exceptions prédéfinies

Dans l'exemple précédent apparaît l'exception ZERO_DIVIDE. Il existe d'autres exceptions en

prédéfinies en PL/SQL :

TOO_MANY_ROWS : instruction select ... into qui ramène plus d'une ligne.

NO_DATA_FOUND : instruction select ... into qui ne ramène aucune ligne.

INVALID_CURSOR : ouverture de curseur non valide.

CURSOR_ALREADY_OPEN : ouverture d'un curseur déjà ouvert.

VALUE_ERROR : erreur arithmétique (conversion, taille, ...) pour un NUMBER.

ZERO_DIVIDE : division par 0 ;

STORAGE_ERROR : dépassement de capacité mémoire.

LOGIN_DENIED : connexion refusée

Cours SGBD 42 ISET Gabès
 3. Déclaration d'une exception

On déclare l'exception nomException grâce à l'instruction suivante :

nomException EXCEPTION ;

4. Les exceptions et les codes d'erreur

Les exceptions prédéfinies sont associées à des codes d'erreur Oracle. Lorsqu'une exception n'est pas
traitée dans le programme PL/SQL, le client qui a appelé ce programme reçoit le code d'erreur associé.
Malheureusement, tous les codes d'erreur Oracle ne sont pas associés à un nom d'exception. Pour traiter
une erreur non liée à une exception, on peut utiliser la clause When others mais ce n'est pas très précis ;
on ne sait pas exactement quelle erreur on traite. Une autre solution consiste à lier un nom d'exception à
un code d'erreur. Ceci est réalisé par la directive PRAGMA EXCEPTION_INIT(nomException,
codeErreur). Lier une exception à un code d'erreur peut aussi être utilisé pour définir des nouveaux codes
d'erreur à partir de nouvelles exceptions (i.e. non prédéfinies).

On suppose qu'il existe une table COMPAGNIE et une table PILOTE avec une contrainte de clef
étrangère entre les deux (un pilote appartient à une compagnie). Lorsqu'on veut supprimer une compagnie
qui a encore des pilotes, on reçoit l'erreur : ORA-02292 enregistrement fils existant.

La procédure suivante permet de gérer cette erreur, en la liant à un nom d'exception.

create procedure detruitCompagnie...

restePilote EXCEPTION ;
PRAGMA Exception_init(restePilote, -2292);
begin
delete from compagnie where comp = 'MaCompagnie' ;
...
exception
when restePilote then
... traitement de l'erreur ...
end ;

Dans ce programme, si aucune ligne de COMPAGNIE n'est effacée, ceci n'entraîne pas d'erreur. La
version suivante permet de déclencher une exception si la compagnie passée en paramètre n'existe pas :

create procedure delete from compagnie where comp =

detruitCompagnie(ma_comp VARCHAR2) is ma_comp ;
restePilote EXCEPTION ; if SQL%NOTFOUND then
PRAGMA Exception_init(restePilote, raise compagnieInexistante ;
-2292); end if ;
compagnieInexistante EXCEPTION ; exception
-- ce n'est pas nécessaire de la lier when restePilote then
à un code d'erreur ... traitement de l'erreur ...
begin when compagnieInexistante then
...

Cours SGBD 43 ISET Gabès

 when others then end ;
... on traite les autres erreurs

Pour terminer, il est aussi possible de déclencher une erreur, sans la lier à une exception. Pour cela, on
utilise l'instruction raise_application_error(codeErreur,messageErreur); où le code d'erreur est un entier
négatif compris entre -20999 et-20000 (codes réservés aux erreurs non prédéfinies) le message erreur est
celui communiqué au client (par exemple, affiché sous SQLPlus).

V. Les modules stockés

Un module stocké est un programme rangé dans la base de données. On peut ainsi définir en PL/SQL des
procédures, fonctions ou même des paquetages. Ces programmes peuvent être appelés par les
programmes clients, et sont exécutés par le serveur. Cette notion de module stocké fait partie de la norme
SQL3. Le langage utilisé dans la norme est très proche de PL/SQL.

1. Procédures et fonctions

La syntaxe permettant de définir une procédure stockée est :

create or replace procedure p_name [ les_parametres ] is

declarations
begin
code PL/SQL
end ;

Et celle permettant de définir une fonction stockée :

create or replace function f_name [ les_parametres ] return datatype is

declarations
begin
code PL/SQL
end ;

2. Les paramètres

Pour déclarer un paramètre, la syntaxe est :

nom_param [mode] datatype

[ := valeur_defaut ]

Il y a trois modes de passage de paramètre :

 mode IN : paramètre en entrée (par défaut, si l'on ne précise pas de mode)

 mode OUT : paramètre en sortie
 mode IN OUT : paramètre en entrée et sortie.

Cours SGBD 44 ISET Gabès

Voici une fonction qui prend en paramètre un numéro de département et retourne son nom :

create or replace function nom_dept(numero dept.dept_no%type)

return VARCHAR2 is
nom dept.dept_name%type ;
begin
select dept_name into nom from dept
where dept_no = numero ;
return nom ;
end ;

Et voici une procédure qui prend en entrée un numéro de département et donne en sortie le nom de ce
département (paramètre OUT au lieu de résultat de fonction)

create or replace procedure nom_dept2(numero IN dept.dept_no%type,

nom OUT dept.dept_name%type) is
begin
select dept_name into nom
from dept
where dept_no = numero ;
end ;

3. Les paquetages

Un paquetage permet de regrouper ensemble des procédures, exceptions, constantes...Un paquetage est
composé d'une spécification et d'un corps. La spécification du paquetage contient des éléments que l'on
rend accessibles à tous les utilisateurs du paquetage. Le corps du paquetage contient l'implémentation et
ce que l'on veut cacher.

Au niveau des droits, on donne le droit d'exécuter un paquetage : on a alors accès à toute la spécification,
pas au corps. Il faut mettre le minimum de choses dans la spécification (penser à l'encapsulation)

La surcharge est autorisée, on peut avoir plusieurs procédures ou fonctions de même nom, avec des
signatures différentes.

Chaque session qui utilise le paquetage possède une instance du paquetage (il n'y a donc pas partage des
variables)

Spécification

Elle contient : des signatures de procédures et fonctions, des constantes et des variables des définitions
d'exceptions des définitions de curseurs

Cours SGBD 45 ISET Gabès

Le corps

Il contient: Les corps des procédures et fonctions de la spécifications (obligatoire) D'autres procédures et
fonctions (cachées) Des déclarations que l'on veut rendre privées Un bloc d'initialisation du paquetage si
nécessaire. Voici un exemple (stupide) de paquetage :

-- spécification
create or replace package mon_paq as
procedure p ;
procedure p(i NUMBER) ;
function p(i NUMBER) return NUMBER ;
cpt NUMBER ;
function get_cpt return NUMBER ;
mon_exception EXCEPTION ;
PRAGMA EXCEPTION_INIT(mon_exception, -20101);
end ;
-- corps
create or replace package body mon_paq as
procedure p is
begin
dbms_output.put_line('toto');
end ;
procedure p(i NUMBER) is
begin
dbms_output.put_line(i);
end ;
function p(i NUMBER) return NUMBER is
begin
if (i > 10) then raise mon_exception ; end if ;
return i ;
end ;
function get_cpt return NUMBER is
begin return cpt ; end ;
end ;

Et voici un exemple d'utilisation de ce paquetage :

begin
mon_paq.cpt := 20 ;
end ;

select mon_paq.get_cpt from dual ;

GET_CPT
----------------------------
20

-- autre utilisateur :
select usertp.mon_paq.get_cpt from dual ;
GET_CPT
----------------------------
null

select mon_paq.p(11) from dual ;

-- avec le pragma exception_init ... on obtient
ORA-20101:
ORA-06512: à "USERTP.MON_PAQ", ligne 12
ORA-06512: à ligne 1

Cours SGBD 46 ISET Gabès

VI. Les Triggers

Un trigger (déclencheur) est un programme qui se déclenche automatiquement suite à un événement. C'est
donc un programme qui fait partie du schéma (comme les modules stockés) mais que l'on n'appelle pas
explicitement, à la différence d'une procédure stockée. Ils existent dans la plupart des SGBD et sont la
traduction d'une vision active de la base de données.

Les triggers que nous étudierons dans ce cours sont déclenchés par des instructions DML qui modifient
l'instance d'une table: INSERT, DELETE ou UPDATE. (il existe sous Oracle d'autres types de triggers)

Ces triggers existent dans la norme SQL3, sous une syntaxe très proche de celle d'Oracle (bien que
légèrement différente, comme d'habitude ...). Le mécanisme général ainsi que les différentes variantes de
triggers sont similaires en SQL3 et sous Oracle.

Les triggers peuvent servir à vérifier des contraintes que l'on ne peut pas définir de façon déclarative,
gérer de la redondance d'information, après une dénormalisation du schéma et collecter des informations
sur les mises à jour de la base.

1. Structure d'un trigger

CREATE [OR REPLACE] TRIGGER nom_trigger
instant liste_evts
ON nom_table [FOR EACH ROW]
[WHEN ( condition ) ]
corps

instant ::= AFTER | BEFORE

liste_evts ::= evt {OR evt}
evt ::= DELETE | INSERT |
UPDATE [OF { liste_cols }]
liste_col ::= nom_col { , nom_col }
corps corps de pgme PL/SQL

2. Entête du trigger

On définit :

 la table à laquelle le trigger est lié,

 les instructions du DML qui déclenchent le trigger
 le moment où le trigger va se déclencher par rapport à l'instruction DML (avant ou après)
 si le trigger se déclenche une seule fois pour toute l'instruction (i.e. trigger instruction), ou
une fois pour chaque ligne modifiée/insérée/supprimée. (i.e. trigger ligne, avec l'option
FOR EACH ROW) et éventuellement une condition supplémentaire de déclenchement
(clause WHEN)

Cours SGBD 47 ISET Gabès

 3. After ou Before ?

Si le trigger doit déterminer si l'instruction DML est autorisée, utiliser BEFORE. Si le trigger doit
"fabriquer" la valeur d'une colonne pour pouvoir ensuite la mettre dans la table : utiliser BEFORE. Si on a
besoin que l'instruction DML soit terminée pour exécuter le corps du trigger : utiliser AFTER

4. Trigger ligne ou instruction ?

Un trigger instruction se déclenche une fois, suite à une instruction DML. Un trigger ligne (FOR EACH
ROW) se déclenche pour chaque ligne modifiée par l'instruction DML.

Par exemple, si une instruction update sur une table T modifie 5 lignes, un trigger instruction lié à cet
événement se déclenchera une seule fois (avant ou après la modification en fonction de after/before) et un
trigger ligne se déclenchera 5 fois, une fois par ligne modifiée (avant ou après la modification en fonction
de after/before).

Dans un trigger ligne, on peut faire référence à la ligne courante, celle pour laquelle le trigger s'exécute.
Pour cette ligne, on a accès à la valeur avant l'instruction DML (nommée :old) et à la valeur après
l'instruction (nommée :new).

Tableau 10: la valeur avant ( : old) et à la valeur après ( : new)

:old :new

insert null ligne insérée

delete ligne supprimée null

update ligne avant modif ligne après modif

5. Clause When

On peut définir une condition pour un trigger ligne : le trigger se déclenchera pour chaque ligne vérifiant
la condition. Le trigger suivant insère une ligne dans une table de log, lorsque le salaire d'un employé
diminue.

Create or replace trigger journal_emp

after update of salary on EMPLOYEE
for each row
when (new.salary < old.salary) -- attention, ici on utilise new et pas :new
begin
insert into EMP_LOG(emp_id, date_evt, msg)
values (:new.empno, sysdate, 'salaire diminué');end ;

Cours SGBD 48 ISET Gabès

 6. Ordre d'exécution des triggers

Pour une instruction du DML sur une table de la base, il peut y avoir quatre familles de triggers possibles
selon l'instant (before, after) et le type (instruction ou ligne). Ces triggers se déclenchent dans l'ordre
suivant :

 Triggers instruction BEFORE

 Triggers ligne BEFORE (déclenchés n fois)
 Triggers ligne AFTER (déclenchés n fois)
 Triggers instruction AFTER

Dans une même famille, on ne contrôle pas l'ordre d'exécution des triggers : par exemple, s'il y a plusieurs
triggers instructions after pour l'instruction update sur une table T, on ne sait pas dans quel ordre ils vont
s'exécuter.

7. Corps du trigger

Le corps du trigger est écrit en PL/SQL. Par rapport à une procédure stockée, on peut utiliser des
prédicats qui permettent de savoir quel événement a déclenché le trigger : prédicat INSERTING (resp.
UPDATING, DELETING) qui vaut vrai ssi le trigger a été déclenché par un INSERT (resp UPDATE,
DELETE). Nous avons vu précédemment que dans le corps d'un trigger ligne, on peut faire référence aux
valeurs de la ligne courante (avant et après modif) par :old et :new.

Il ne faut pas, dans un trigger ligne, interroger une table qui est en cours de modification (pb mutating
table). En effet, l'état de la table est instable puisque l'instruction SQL est en cours d'exécution.

Exemple : génération automatique d'une clef primaire.

create table T1( declare

c1 number(3) la_clef number ;
constraint t1_pkey primary key, begin
c2 varchar2(20) select seq.NEXTVAL
); into la_clef from dual ;
:new.c1 := la_clef ;
create sequence seq end ;
increment by 1
start with 1 ; insert into T1(c2) values ('coucou') ;

create or replace trigger clef_auto select * from T1;

before insert C1 C2
on T1 ----------------------------
for each row 1 coucou
when (new.c1 is null)

Cours SGBD 49 ISET Gabès

Ca marche mais rien n'empêche l'utilisateur de mettre une valeur "à lui" pour la clef. Pour éviter ce
problème, on peut enlever la clause When. Dans ce cas, oin utilisera la séquence quoiqu'il arrive.

On peut aussi imaginer de calculer la clef en fonction des valeurs déjà présentes (sans utiliser de
séquence). C'est ce que propose le trigger suivant.

Si on veut calculer la clef en fonction des valeurs déjà présentes :

create or replace trigger clef_auto

before insert
on T1
for each row
when (new.c1 is null)
declare
la_clef number ;
begin
select nvl(max(c1),0)+1 into la_clef from T1 ;
:new.c1 := la_clef ;
end ;

Mais cette solution entraîne un problème de table en mutation :

insert into T1(c2) values ('abc'); insert into T1(c2)

--> ligne (2,'abc') select c2 from T1bis;
insert into T1 values (10,'def');
--> ligne(10,'def') ORA-04091: la table CARON.T1
insert into T1(c2) values ('ghi'); est en mutation ;
--> ligne(11,'ghi') le déclencheur ou la fonction ne peut
la voir...

Voici un exemple de gestion d'un attribut calculé :

create table T2( ----------------------------

clef number(3) constraint t2_pkey 1 coucou 0
primary key, 2 recoucou 0
le_T1 number(3) constraint t2_t1_fkey 10 fghfg 0
references T1, 11 ghgfhkl 0
blabla varchar2(10) create or replace trigger
); coherencet1_nb_t2
before insert
-- redondance : compteur du nombre de on t2
lignes de T2 for each row
-- qui font référence à begin
cette ligne de T1 update t1 set nb_t2 = nb_t2+1
alter table T1 add (nb_t2 number(5) where :new.le_t1 = c1 ;
default 0 not null); end ;
update T1 set nb_t2 = 0;
insert into t2 values
select * from t1 ; (1,10,'jhgfdlkgjh');
C1 C2 NB_T2

Cours SGBD 50 ISET Gabès

select * from t1;
C1 C2 NB_T2
----------------------------
1 coucou 0
2 recoucou 0
10 fghfg 1
11 ghgfhkl 0

-- faire la même chose pour delete et

update

Cours SGBD 51 ISET Gabès

Bibliographie

1. Gardarin G., Bases de Données - objet/relationnel, Eyrolles, 1999, ISBN: 2-212-09060-9

2. Gardarin G., Maîtriser les Bases de Données: modèles et langages, Eyrolles
3. Carrez C., Des Structures aux Bases de Données, Masson
4. Marcenac, P., SGBD relationnels, Optimisation des performances, Eyrolles
5. Date C.J., A Guide to SQL Standard, Addison Wesley
6. Delmal P., SQL2 - Application à Oracle, Access et RDB, 2ème Edition, De Boeck, 1998
7. Soutou C., SQL Pour Oracle (avec exercices corrigés), Eyrolles, 2005
8. J. Pratt P., Initiation à SQL (cours et exercices corrigés), Eyrolles, 2001
9. Brouad F. et Soutou C., SQL (cours et exercices corrigés), Coll. Synthex, Pearson
Education, 2005
10. Feuerstein S. Pribyl B. et Dawes C., Oracle PL/SQL - Précis & concis, O ’Reilly, 2000
11. Diana Lorentz, Oracle Database SQL Reference, Elizabeth Treacy 2005
12. Russ Lowenthal et Ric Van Dyke, ORACLE Database 10g Administation workshopI ,
Elizabeth Treacy 2004
13. Tom Best et M.J Billings, ORACLE Database 10g Administation workshopII , Sujatha
Negendra 2006

Cours SGBD 52 ISET Gabès