ppt - HEC Lausanne

Université de Lausanne
Ecole des Hautes Etudes Commerciales (HEC)
Cours de Bases de Données Avancées
TP3 – Contraintes Oracle
BFSH1 - 1015 Lausanne - Switzerland - Tel. +41 21 692.3420 - [email protected] - http://www.hec.unil.ch/cparent
Université de Lausanne > Ecole des HEC > Cours de Bases de Données Avancées > TP3
Introduction
Domaine d’application
Règles de gestion
Modèle conceptuel
Règles d’intégrité
Système implémenté
Contraintes d’intégrité
Bases de Données Avancées – TP3
2
Université de Lausanne > Ecole des HEC > Cours de Bases de Données Avancées > TP3
Contrainte d ’intégrité
 Définition: propriété (formelle) qui doit toujours être vérifiée
– par chaque état de la base de données
– par chaque changement apporté à la base de donnée
 Une contrainte est définie par
– un contexte (définit les objets touchés par la contrainte)
– une expression sur les objets de ce contexte (propriété qui doit être vérifiée)
– une portée (opérations pouvant potentiellement invalider la règle)
Bases de Données Avancées – TP3
3
Université de Lausanne > Ecole des HEC > Cours de Bases de Données Avancées > TP3
Validation de contraintes
H
données brutes
Programme
de pré-validation
données
valides
Programme
I de post-validation
Programme
d'application
Programme
d'application
Programme
d'application
Chargeur
Interface
G utilisateur
Programme
d'application
Module
E d'accès
F
accès protégé
accès non protégé
DB schema
D
stored procedures
C views w. check option
B
triggers
A
check
declared
constructs
DB data
 Techniques de validation de
contraintes d’intégrité:
– 0) Contraintes déclarées
– A) Prédicats de contrainte
(CHECK)
– B) Procédures déclenchées
(TRIGGER)
– C) Vues filtrantes
– D) Procédures stockées
– E) Module d’accès
– F) Programme d’application
– G) Interface utilisateur
– H) Programme de pré-validation
– I) Programme de post-validation
Source: [Hainaut, 1998]
Bases de Données Avancées – TP3
4
Université de Lausanne > Ecole des HEC > Cours de Bases de Données Avancées > TP3
Les contraintes déclarées (voir TP2)
 Sont évaluées en premier lieu
 Contraintes de domaine
–
–
–
–
Choix du type de données (délimite les valeur pouvant être pris par les attributs)
NOT NULL
DEFAULT
CHECK (condition)
 Contraintes d’entité
– PRIMARY KEY
– UNIQUE
 Contraintes référentielles
– FOREIGN KEY
Bases de Données Avancées – TP3
5
Université de Lausanne > Ecole des HEC > Cours de Bases de Données Avancées > TP3
A) Les prédicats de contrainte (CHECK, ASSERTION)
 [CONSTRAINT name] CHECK (condition)
– Spécifie une condition qui doit être vérifiée à tout moment par chaque tuple
– Est évalué après toute opération d'insertion et de modification de lignes: si la
condition n'est pas vérifiée, l'opération n’est pas permise
– Peut être définie sur une colonne ou une table (ensemble d’attributs)
– Exemples de conditions:






Prendre une valeur dans une intervalle (CHECK a BETWEEN v1 AND v2)
Prendre une valeur dans un ensemble (CHECK a IN (v1, v2, ... vn))
Conditions par rapport à la même colonne (CHECK a > 0)
Conditions par rapport aux valeurs précédents (CHECK a > :old_a)
Conditions par rapport à d’autres colonnes (CHECK a = b - c)
Possibilité d’utiliser les opérateurs et fonctions logiques et mathématiques
– Note: bien que permis par le standard SQL, Oracle ne permet pas aux contraintes
CHECK de contenir des rêquetes ni référencer des attributs d’autres tables
 CREATE ASSERTION nom CHECK (condition)
– Permet de créer un prédicat de contrainte au niveau du schéma.
– Note: bien que permis par le standard SQL, cette possibilité n’est pas implementée
par Oracle (remplacée par les triggers)
Bases de Données Avancées – TP3
6
Université de Lausanne > Ecole des HEC > Cours de Bases de Données Avancées > TP3
B) Les procédures déclenchées (TRIGGER)
 Utilisés pour spécifier des contraintes plus complexes tels que:
– Contraintes portant sur plusieurs tables
– Contraintes nécessitant l’utilisation de requêtes
 Offrent une technique procédurale
– Procédures à exécuter programmés en PL/SQL
 Sont déclenchées suite à un événement
– Associé à une table particulière
– Déclenchés en réaction à un événement INSERT, UPDATE ou DELETE sur cette
table
– Possibilité de spécifier des conditions supplémentaires (WHEN)
– NB: Un trigger peut déclencher d’autres triggers en cascade si ses actions génèrent
des événement qui déclenchent d’autres trigger.
 Résultat du trigger
– Le trigger se termine favorablement s’il ne soulève pas d’exceptions ou ne viole pas
d’autres contraintes (contraintes déclarées ou check). Dans ce cas les actions
effectuées par l’événement et le trigger sont acceptées (commit implicite). Sinon
toutes les actions du trigger sont annulées (rollback implicite)
Bases de Données Avancées – TP3
7
Université de Lausanne > Ecole des HEC > Cours de Bases de Données Avancées > TP3
B) Les procédures déclenchées (TRIGGER) - Structure
 CREATE [OR REPLACE] TRIGGER nom-trigger
BEFORE | AFTER
INSERT OR UPDATE [OF column] OR DELETE ON nom-table
[FOR EACH ROW [WHEN (condition)]]
bloc d’instructions pl/sql
– BEFORE | AFTER
 spécifie le point d’execution de la procérure comme avant ou après l’éxecution
de l’événement (NB qui pourrait être annulée par le trigger)
– INSERT OR UPDATE [OF column] OR DELETE ON nom-table
 spécifie les événements qui feront déclencher le trigger (c’est à dire ceux qui
pourranient violer la contrainte d’integrité que le trigger doit implémenter)
– [FOR EACH ROW [WHEN (condition)]]
 spécifie le type de trigger comme étant un trigger de ligne (si omis c’est un
trigger d’instruction) et des conditions optionnelles pour son déclenchement
– bloc d’instructions pl/sql
 spécifie les actions à executer, programmées en pl/sql
Bases de Données Avancées – TP3
8
Université de Lausanne > Ecole des HEC > Cours de Bases de Données Avancées > TP3
B) Les procédures déclenchées (TRIGGER) - Types
 Types de trigger
– Déclencheur de ligne (spécifié par FOR EACH ROW),
 Sont exécutés une fois pour chaque ligne affectée par l’événement spécifié
 Permet d’accéder aux valeurs des lignes affectées avant (avec :old.nomcol
en cas de update ou delete) et après (avec :new.nomcol en cas d’insert ou
update) l’opération. Si le triggers est spécifié comme étant déclenché avant
l’événement (before) les valeurs du tuple peuvent aussi être changés avant
leur insertion ou modification dans la base de données.
 Il est possible de spécifier des conditions de déclenchement (WHEN)
 Par contre, il y a des restrictions sur les requêtes utilisés pour accéder aux
autres lignes de la table sur lequel le trigger est défini
– Déclencheur d’instruction (par défaut)
 Exécutés une seule fois avant ou après la totalité de l’événement,
indépendamment du nombre de lignes affectés
 Restrictions: ne permet pas d’accéder aux ou modifier les valeurs des lignes
affectées avant ou après l’événement (:old.nomcol et :new.nomcol) ni de
spécifier des conditions WHEN
Bases de Données Avancées – TP3
9
Université de Lausanne > Ecole des HEC > Cours de Bases de Données Avancées > TP3
B) Les procédures déclenchées (TRIGGER) - PL/SQL
 Structure du bloc d’instructions PL/SQL
– DECLARE
Déclaration de variables et constantes avec leur types
BEGIN
Bloc d’instructions PL/SQL
END
 Variables
– Déclaration: nomvar [CONSTANT] type
 A déclarer dans la partie DECLARE avec leur nom et type
 Les types sont les mêmes que définis par Oracle SQL. Note: nomcol%TYPE peut être
utilisé pour affecter à la variable le même type spécifié pour la colonne nomcol.
 Exemples: genre varchar(16); année année%TYPE;
– Affectation: nomvar := valeur | SELECT cols INTO vars FROM...WHERE...
 Exemple: genre := ‘Blues’; année := 1999;
 Exemple: SELECT genre INTO genre FROM Artist WHERE nom = :new.nom;
Bases de Données Avancées – TP3
10
Université de Lausanne > Ecole des HEC > Cours de Bases de Données Avancées > TP3
B) Les procédures déclenchées (TRIGGER) - PL/SQL
 Le bloc d’instructions PL/SQL peut contenir ...
– des blocs spécifiant des actions différentes selon l’événement
 IF INSERTING THEN bloc d’instructions pl/sql END IF
 IF UPDATING THEN bloc d’instructions pl/sql END IF
 IF DELETING THEN bloc d’instructions pl/sql END IF
– Instructions SQL
 SELECT, INSERT, UPDATE, DELETE
 à l’exception de COMMIT et ROLLBACK
– Instructions de contrôle de flux




IF condition THEN instructions pl/sql ELSE instructions pl/sql END IF;
LOOP instructions pl/sql EXIT WHEN (condition) END LOOP;
WHILE condition LOOP instructions pl/sql END LOOP;
FOR variable IN start..stop LOOP instructions pl/sql END LOOP;
– Générer des exceptions
 raise_application_error(nombre,message)
– Autres possibilités plus avancées
 Curseurs
 Procédures et fonctions
 Gestion des exceptions
Bases de Données Avancées – TP3
11
Université de Lausanne > Ecole des HEC > Cours de Bases de Données Avancées > TP3
B) Les procédures déclenchées (TRIGGER) - Curseurs
 Un curseur est une variable dynamique qui prend pour valeur le résultat d’une
requête et permet d’en parcourir les enregistrements
– Définition: permet de définir une variable de type curseur
 CURSOR nomcurseur IS requête;
– Ouverture: permet de positionner le curseur à son premier enregistrement
 OPEN nomcurseur;
– Fetch: permet de récupérer les tuples successifs de la requête et de les stoquer
dans une variable (définie avec un type conforme au résultat de la requête). La
première fois, c’est le premier tuple du qui est affecté à la variable, après chaque
fetch assigne un nouveau tuple à la variable
 FETCH nomcurseur INTO nomvariable;
– Fermeture: lorsque le traitement sur le résultat de la requête est terminée on ferme
le curseur
 CLOSE nomcurseur;
– On utilise normalement les curseurs dans des boucles qui parcourent la requête
 LOOP
FETCH nomcurseur INTO nomvariable;
instructions;
EXIT WHEN SalCur%NOTFOUND;
END LOOP;
 FOR nomvariable IN nomcurseur LOOP ... END LOOP
Bases de Données Avancées – TP3
12
Université de Lausanne > Ecole des HEC > Cours de Bases de Données Avancées > TP3
B) Les procédures déclenchées (TRIGGER) - TOAD
 Stored Procedure Edit / Compile Window (Database > Stored Procedure Edit)
Ouvrir une fênetre Stored Procedure Edit / Compile
Compiler le trigger
Espace où écrire le programme pl/sql correspondant au trigger
Espace où sont marquées les éventuels erreurs de compilation
Indique le statut du trigger (valide/invalide)
Indique le résultat de la compilation
Bases de Données Avancées – TP3
13
Université de Lausanne > Ecole des HEC > Cours de Bases de Données Avancées > TP3
B) Les procédures déclenchées (TRIGGER) - Exercices
 Charger le script sql tp3.sql pour charger une base de donnée d’exemple
 Exercice 1a: simulation d’une contrainte ON UPDATE CASCADE entre artiste et album.
Lorsque l’on met à jour le nom d’un artiste dans la table artiste, il faut affecter le nouveau
nom aux instances de la table album qui correspondaient à l’ancien nom.
– CREATE OR REPLACE TRIGGER TR1a
AFTER UPDATE OF Nom ON Artiste
FOR EACH ROW WHEN (new.nom <> old.nom)
BEGIN
UPDATE Album SET Artiste = :new.nom WHERE Artiste = :old.nom;
END;
 Exercice 1b: simulation d’une contrainte ON DELETE SET NULL entre artiste et album.
Lorsque l’on efface un artiste, il faut mettre les champs correspondant (colonne artiste)
de la table album à nul.
 Exercice 1c: simulation d’une contrainte ON DELETE CASCADE entre artiste, album et
chanson. Lorsque l’on efface un artiste, il faut effacer également ses albums et ses
chansons. Vous pouvez le programmer avec deux triggers qui s’enchainent.
 Exercice 1d: tout album doit être fait par un artiste contenu dans la base. Lors de
l’insertion d’un album avec un artiste qui n’existe pas encore, insérez dans artiste un
enregistrement correspondant à l’artiste avec les autres informations égales à null
Bases de Données Avancées – TP3
14
Université de Lausanne > Ecole des HEC > Cours de Bases de Données Avancées > TP3
B) Les procédures déclenchées (TRIGGER) - Exercices
 Exercice 2a: La durée de l’album doit correspondre à la durée des chansons qu’il
contient. Cette contrainte peut être invalidée par des insert, update ou delete sur la table
chanson (album ou durée) ou album (durée). On peut utiliser un trigger qui met à jour les
durées des albums suite à une événément sur chanson et un autre qui empêche de
mettre une durée fausse dans album.
– CREATE OR REPLACE TRIGGER ControleDureeAlbum
AFTER INSERT OR UPDATE OR DELETE ON Chanson
BEGIN
UPDATE album a SET duree = ( SELECT sum(duree) FROM chanson c WHERE c.album=a.titre);
END;
– CREATE OR REPLACE TRIGGER ModificationDureeAlbum
AFTER UPDATE OF Duree ON Album
FOR EACH ROW
DECLARE nombrechansons number(8,2); dureealbum number(8,2);
BEGIN
SELECT count(*) INTO nombrechansons FROM Chanson WHERE album = :new.titre;
SELECT sum(duree) INTO dureealbum FROM Chanson WHERE album = :new.titre;
IF dureealbum <> :new.duree OR (nombrechansons=0 AND :new.duree<>0)
THEN raise_application_error(-20001, ‘Duree de l''album erronée');
END IF;
END;
 Exercice 2b: La nombre de albums indiqués pour un artiste dans nombrealbums doit
correspondre au nombre d’albums correspondants contenus dans la base.
Bases de Données Avancées – TP3
15
Université de Lausanne > Ecole des HEC > Cours de Bases de Données Avancées > TP3
B) Les procédures déclenchées (TRIGGER) - Exercices
 Exemple 3: Les numéros des chansons d’un album doivent se
suivre (il ne doit pas y avoir de trous dans la numérotation)
– CREATE OR REPLACE TRIGGER TriggerNumeroChansons
AFTER INSERT OR UPDATE OR DELETE ON Chanson
DECLARE
CURSOR AlbumCursor IS SELECT album FROM Chanson;
cAlbum album%ROWTYPE;
mini number(2); maxi number(2); chk number(1); i number(2);
BEGIN
FOR cAlbum IN AlbumCursor LOOP
SELECT max(nombre) into maxi from chanson where album=cAlbum.album;
SELECT min(nombre) into mini from chanson where album=cAlbum.album;
IF mini <> 1 THEN
raise_application_error(-20002,‘Num chansons de'||cAlbum.album||' doit commencer à 1');
END IF;
FOR i in mini..maxi LOOP
SELECT count(*) INTO chk FROM chanson WHERE album=cAlbum.album AND nombre=i;
IF chk = 0 THEN
raise_application_error(-20002,‘Numeros chansons de '||cAlbum.album||' pas contigus');
END IF;
END LOOP;
END LOOP;
END;
Bases de Données Avancées – TP3
16