Objectifs du module

! Étude approfondie des systèmes de gestion de bases de données, de leurs

architectures et de leur évolution
ÉLÉMENT'1':'BASES'DE'DONNÉES'AVANCÉES''
" Premier élément : consacré principalement à PL/SQL, au modèle objet-
relationnel et aux techniques d'évaluation et d’optimisation des requêtes

" Deuxième élément : consacré à l'administration du SGBD Oracle : architecture

interne, maintenance, journalisation, restauration, sécurité et confidentialité des
données

SAIDI Rajaa
[Link]@[Link]

Objectifs du cours Organisation du module

1. Étudier comment écrire des déclencheurs en PL/SQL

2. Étudier comment dériver un modèle relationnel de données à partir d’un

modèle orienté objets
Module VH Pourcentage note Détail note
3. Comprendre les concepts des bases de données objet-relationnelles et finale
leurs intérêts Bases de données 24h 50% 100% : Examen final
avancées
4. Comprendre pourquoi évaluer et optimiser une requête Administration des BD 24h 50% ?

5. Étudier comment obtenir « vite » des enregistrements satisfaisant un

prédicat en utilisant des indexes

3 4
Plan

Rappel cours BD1 : SQL

Chapitre 1 – PL/SQL, Triggers et Vues

Chapitre 2 – de l’UML vers une BD relationnelle

Chapitre 3 – Bases de données objet-relationnelles

Rappel
Chapitre 4 – Evaluation et optimisation des requêtes
Cours BD1- SQL
Chapitre 5 – Indexation

Exploitation des données dans une BD Le langage SQL

! Différentes opérations pour manipuler les données dans une BD : ! SQL comporte 5 mots-clés principaux
" recherche d’information (ou interrogation)
" insertion de données " SELECT : recherche
" mise à jour de données " CREATE : création
" suppression de données " INSERT : ajout
! Exemple d’une BD relationnelle " UPDATE : mise à jour
" DELETE : suppression
EST-FACTURÉ NUMFACT REFPROD QTE
PRODUIT REFPROD DESIGN PRIXHT 6 1 7
1 Livre 19,20 6 5 3
5 Cahier 2,30 2 5 10
9 Crayon 4,75 2 1 5
7 Stylo 2,20 3 7 4

FACTURE NUMFACT DATFACT ANC-PRODUIT REFPROD DESIGN PRIXHT

6 13/10/2010 1 Livre 19,10
2 16/10/2010 9 Crayon 4,75
3 16/10/2010 4 Cartable 80
5 Cahier 2,30
2 Trousse 10,50
7 8
La recherche de données : SELECT PRODUIT REFPROD
1
DESIGN
Livre
PRIXHT
19,20 La clause SELECT – 1 PRODUIT REFPROD
1
DESIGN
Livre
PRIXHT
19,20
5 Cahier 2,30 5 Cahier 2,30
9 Crayon 4,75 9 Crayon 4,75
7 Stylo 2,30 7 Stylo 2,30
! SELECT * : retourne tous les champs
! Syntaxe d’une requête SELECT simple : exemple :
champs présentés dans le "

résultat de la requête SELECT *

SELECT champ1, ... , champn REFPROD DESIGN PRIXHT
FROM PRODUIT
FROM table1, ... , tablem tables (et/ou requêtes) utilisées WHERE PRIXHT < 5
5 Cahier 2,30
9 Crayon 4,75
WHERE condition condition devant être vérifiée par un 7 Stylo 2,30
ORDER BY champ1, ... , champj enreg. pour figurer dans le résultat

ordre de présentation des enreg. ! SELECT DISTINCT : supprime les valeurs identiques
du résultat de la requête " exemple :
PRIXHT PRIXHT
! Exemple : référence et désignation des produits coûtant moins de 5 dh triés SELECT PRIXHT 2,30 SELECT DISTINCT PRIXHT
FROM PRODUIT FROM PRODUIT 2,30
par désignation 4,75 4,75
WHERE PRIXHT < 5 2,30 WHERE PRIXHT < 5
SELECT REFROD, DESIGN REFPROD DESIGN
FROM PRODUIT 5 Cahier
WHERE PRIXHT < 5 9 Crayon REFPROD PRIXHT
SELECT DISTINCT REFPROD, PRIXHT
ORDER BY DESIGN 7 Stylo 5 2,30
! FROM PRODUIT
9 4,75 ≠
WHERE PRIXHT < 5 7 2,30
sauts à la ligne facultatifs
9 10

La clause WHERE – 1 / 3 La clause WHERE – 2 / 3

PRODUIT REFPROD DESIGN PRIXHT PRODUIT REFPROD DESIGN PRIXHT
1 Livre 19,20 1 Livre 19,20
5 Cahier 2,30 5 Cahier 2,30
9 Crayon 4,75 9 Crayon 4,75
7 Stylo 2,20 7 Stylo 2,20
! Sélectionne les enregistrements vérifiant une ! L opérateur BETWEEN :
condition, composée de :
" noms de champs (ex: PRIXHT ou [Link] ) champ BETWEEN x AND y
" constantes num. (ex: 2.3), chaînes (ex: 'Dupont'), dates (ex: #2010/10/21# ) est équivalent à :
" opérateurs (=, <>, <, <=, >, >=, +, -, *, /, …) et fonctions (sin, log, …) champ >= x AND champ <= y
" opérateurs logiques : OR, AND, NOT
" Remarque : x et y peuvent être des expressions quelconques
! Exemple : désignation des produits dont le prix est compris entre 3 et 5 dh
SELECT DESIGN
DESIGN
FROM PRODUIT
Crayon ! Exemple : désignation des produits dont le prix est compris entre 3 et 5 dh
WHERE PRIXHT >= 3
AND PRIXHT <= 5
noter la répétition du champ PRIXHT SELECT DESIGN DESIGN
FROM PRODUIT Crayon
! Utiliser des parenthèses en cas d’utilisation conjointe de AND et OR WHERE PRIXHT BETWEEN 3 AND 5

• exemple : désign. des produits SELECT DESIGN

dont le prix est entre 3 et 5 dh ou FROM PRODUIT
WHERE (PRIXHT >= 3
dont la réf. est supérieure à 2 AND PRIXHT <= 5)
OR REFPROD > 2
11 12
La clause WHERE – 3 / 3 La clause ORDER BY
PRODUIT REFPROD DESIGN PRIXHT PRODUIT REFPROD DESIGN PRIXHT
1 Livre 19,20 1 Livre 19,20
5 Cahier 2,30 5 Cahier 2,30
9 Crayon 4,75 9 Crayon 4,75

! Opérateur LIKE : comparaison avec un motif

7 2,20
! Trie les résultats sur un ou plusieurs champs : 7 Stylo 2,20

SELECT ...
champ LIKE 'motif ' FROM …
WHERE … ASC : ordre croissant - par défaut
où motif contient : ORDER BY champ1,…, champn ASC ou DESC DESC : ordre décroissant
? un caractère quelconque optionnel
* une suite quelconque de caractères (éventuellement vide) " deux critères de tri: le plus à gauche est prioritaire ; le 2e utile en cas d’ex-aequo
# un chiffre quelconque exemple : ORDER BY NOM, PRENOM

" exemple : désignation produits dont le nom commence par 'c' " ordre utilisé :
SELECT DESIGN
FROM PRODUIT DESIGN type champ ordre
WHERE DESIGN LIKE 'c*' Cahier texte lexicographique (ordre alphabétique étendu)
Crayon numérique numérique
date / heure chronologique
! Opérateur IS NULL : champ non rempli
! Exemple : produits de moins de 5 dh triés par désignation croissante
" exemple : réf. des produits dont la désignation n’est pas remplie
SELECT REFPROD, DESIGN REFPROD DESIGN
SELECT REFPROD
FROM PRODUIT 5 Cahier
FROM PRODUIT REFPROD
WHERE PRIXHT < 5 9 Crayon
WHERE DESIGN IS NULL 7
13 ORDER BY DESIGN 7 Stylo 14

La clause FROM – 1 / 5 La clause FROM – 2 / 5

PRODUIT REFPROD DESIGN PRIXHT
1 Livre 19,20
5 Cahier 2,30
9 Crayon 4,75
! Exemple : désignation des produits facturés 7 Stylo 2,20
! Lorsque deux tables n’ont pas de champ commun, utiliser les tables inter-
EST-FACTURÉ NUMFACT REFPROD QTE
PRODUIT( REFPROD , DESIGN, PRIXHT) 6 1 7 médiaires pour effectuer la jointure : consulter le modèle entité-association
EST-FACTURE( NUMFACT, REFPROD , QTE) 6 5 3
2 5 10 " exemple : désignation des produits facturés après le 14/10/2010
2 1 5
SELECT DESIGN 3 7 4 PRODUIT FACTURE
FROM EST-FACTURE, PRODUIT PRODUIT
WHERE [Link] = [Link] jointure FACTURE( NUMFACT, DATFACT) FACTURE
EST-FACTURE REFPROD
champ REFPROD de la champ REFPROD de la ? NUMFACT
1,n QTE 0,n DESIGN
table EST-FACTURE table PRODUIT DATFACT PRIXHT
PRODUIT( REFPROD, DESIGN, PRIXHT)

! Lorsque plusieurs tables sont nécessaires

FACTURE( NUMFACT , DATFACT)
" il faut les mettre dans FROM
EST-FACTURE(NUMFACT , REFPROD , QTE)
" il faut les "joindre" sur leur(s) champ(s) commun(s)
PRODUIT( REFPROD , DESIGN , PRIXHT)
EST-FACTURE PRODUIT
DESIGN
NUMFACT REFPROD QTE REFPROD DESIGN PRIXHT Livre
6 1 7 1 Livre 19,20 SELECT DESIGN
Cahier
6 5 3 5 Cahier 2,30 FROM FACTURE, EST-FACTURE , PRODUIT
Cahier
2 5 10 5 Cahier 2,30 WHERE [Link] = [Link]
Livre jointures
2 1 5 1 Livre 19,20 AND [Link] = [Link]
Stylo
3 7 4 7 Stylo 2,20 AND DATFACT > #2010/10/14#
= 15 16
La clause FROM – 3 / 5 La clause FROM – 4 / 5
Désignation des produits ayant été facturés, dont
! Une requête peut être basée sur une (ou plusieurs) autres requête(s) : le prix a augmenté par rapport à l ancien
PRODUIT REFPROD DESIGN PRIXHT
1 Livre 19,20
SELECT ... catalogue (table ANC-PRODUIT)
req peut être utilisée comme une table dont les champs 5 Cahier 2,30
FROM req, ... ref. et prix des produits facturés 9 Crayon 4,75
sont ceux affichés dans la clause SELECT de req
... R1 7 Stylo 2,20

! Intérêt SELECT REFPROD,PRIXHT

EST-FACTURÉ NUMFACT REFPROD QTE
FROM EST-FACTURE, PRODUIT
écrire des requêtes qui peuvent être réutilisées dans d’autres requêtes 6 1 7
" WHERE [Link] = [Link]
6 5 3
" décomposer une requête complexe à écrire en requêtes plus simples
2 5 10
2 1 5
3 7 4

R1 REFPROD PRIXHT ANC-PRODUIT REFPROD DESIGN PRIXHT

1 19,20 1 Livre 19,10
5 2,30 9 Crayon 4,75
5 2,30 4 Cartable 80
1 19,20 5 Cahier 2,30
7 2,20 2 Trousse 10,50
SELECT DESIGN
FROM R1 , ANC-PRODUIT DESIGN
WHERE [Link] = ANC-PRODUIT. REFPROD Livre
AND [Link] > [Link]
17 18

La clause FROM – 5 / 5 Calculs dans les requêtes

PRODUIT REFPROD DESIGN PRIXHT
1 Livre 19,20
5 Cahier 2,30
9 Crayon 4,75
7 Stylo 2,20 ! Il est possible d’effectuer des calculs dans une requête
! Il arrive que l’on utilise plusieurs fois la même
table dans une requête ! Il existe deux types de calcul
" exemple : liste des produits ayant un prix supérieur à celui du produit n° 9 " sur les lignes
REFPROD PRIXHT
SELECT [Link] 1 19,20 19,20
FROM PRODUIT AS P1 , PRODUIT AS P2 DESIGN 5 2,30
Livre max(PRIXHT) 9 5,75
WHERE [Link] = 9
AND [Link] > [Link] 7 2,20

" sur les colonnes x 1,196

P1 P2 REFPROD PRIXHT REFPROD PRIXTTC
PRODUIT REFPROD DESIGN PRIXHT PRODUIT REFPROD DESIGN PRIXHT 1 19,20 1 22,96
1 Livre 19,20 1 Livre 19,20 5 2,30 5 2,75
5 Cahier 2,30 5 Cahier 2,30 9 5,75 9 6,87
9 Crayon 4,75 9 Crayon 4,75 7 2,20 7 2,63
7 Stylo 2,20 7 Stylo 2,20
! Dans les deux cas, les opérations s’effectuent dans la clause SELECT
SELECT opération (champ)
FROM ...
...
19 20
Calculs sur les lignes Calculs sur les colonnes
PRODUIT REFPROD DESIGN PRIXHT PRODUIT REFPROD DESIGN PRIXHT
1 Livre 19,20 1 Livre 19,20
5 Cahier 2,30 5 Cahier 2,30
9 Crayon 4,75 9 Crayon 4,75
! Principales opérations 7 Stylo 2,20 ! On peut effectuer des calculs sur une 7 Stylo 2,20

" COUNT : nombre d’enregistrements ou plusieurs colonnes en utilisant EST-FACTURÉ NUMFACT REFPROD QTE
6 1 7
" SUM : somme des valeurs du champ (numérique) sur un ensemble d’enreg. " des constantes
6 5 3
" AVG : moyenne des valeurs du champ (numérique) sur un ensemble d’enreg. " des noms de champ 2 5 10
2 1 5
" MIN, MAX : valeur min. et max. dans l’ensemble d’enregistrements " des opérateurs : +, -, *, /, ...
3 7 4
" opérateurs (+, -, *, /, ...) et fonctions (sin, exp, …) " des fonctions : sin, exp, …

! Exemple : prix le plus élevé parmi les produits ! Exemple : réf. des produits avec leur prix TTC REFPROD
SELECT MAX(PRIXHT) 1 22,96
SELECT REFPROD, PRIXHT * 1.196 5 2,75
FROM PRODUIT 19,20
FROM PRODUIT 9 6,87
7 2,63
! Ne pas confondre COUNT (nombre d’enreg.) et SUM (somme sur un champ) :
SELECT COUNT (PRIXHT)
! On peut combiner calcul sur lignes et colonnes dans une même requête :
(3 enregistrements
FROM PRODUIT 3 vérifiant la condition) " Exemple : montant HT de la facture 6
WHERE PRIXHT < 5
SELECT SUM(PRIXHT * QTE)
SELECT SUM (PRIXHT) FROM PRODUIT, EST-FACTURE
FROM PRODUIT (9,25 = 2,30 + 4,75 + 2,20) WHERE [Link] = [Link] 141,3
9,25 AND NUMFACT = 6
WHERE PRIXHT < 5
21 22

Nommage des colonnes Regroupements d’enregistrements

! On peut nommer une colonne affichée par une requête dans SELECT ! Regroupement : opération d’agrégation sur une ou plusieurs dimensions
" intérêt : réutiliser ce nom dans une autre requête ! Exemples
" CA par client (regroupement par client)
! Pour nommer une colonne :
" nombre de ventes par commercial et par mois (regroupement par
SELECT expr AS nom commercial et par mois )
FROM …
" …
WHERE …
affiche les groupes
! Regrouper des enregistrements :
! Exemple :
SELECT champ1,..., champn , expr1,..., exprm calculs sur les groupes
R1 R1 REFPROD PRIXTTC FROM …
1 22,96 WHERE … champ(s) sur le(s)quel(s)
SELECT REFPROD, PRIXHT * 1.196 AS PRIXTTC 5 2,75 GROUP BY champ1,..., champn s effectue les regroupements
FROM PRODUIT 9 6,87
7 2,63
! Exemple : quantités facturées par produit
SELECT REFPROD, PRIXTTC REFPROD PRIXTTC
EST- NUMFACT REFPROD QTE
FROM R1 1 22,96
FACTURÉ REFPROD
9 6,87 SELECT REFPROD, SUM(QTE)
WHERE PRIXTTC > 6.10 6 1 7 1 12
6 5 3 FROM EST-FACTURE 5 13
2 5 10 GROUP BY REFPROD 7 4
2 1 5
23 3 7 4 24
Exemple Opérations de définition et de manipulation de données en SQL

! Numéro de la facture ayant le montant le plus élevé PRODUIT REFPROD DESIGN PRIXHT
1 Livre 19,20 ! Création de table : CREATE
5 Cahier 2,30
R1 : Montant de chaque facture 9 Crayon 4,75
7 Stylo 2,20 ! Insertion d’enregistrements : INSERT
SELECT NUMFACT, SUM (PRIXHT *QTE) AS MONTANT
FROM PRODUIT, EST- FACTURE EST-FACTURÉ NUMFACT REFPROD QTE
WHERE [Link] = [Link] 6 1 7 ! Suppression d’enregistrements : DELETE
6 5 3
GROUP BY NUMFACT
2 5 10
2 1 5
! Mise à jour d’enregistrements : UPDATE
3 7 4

R1 NUMFACT MONTANT
R2 : Maximum des montants 2 119,00
3 8,80
SELECT MAX(MONTANT) AS MAXMONTANT 6 141,30
FROM R1
R2 MAXMONTANT
141,30
R3 : Numéro de la facture ayant le montant le plus élevé

SELECT NUMFACT R3 NUMFACT

FROM R1,R2 6
WHERE [Link]=R2. MAXMONTANT
25 26

Création de table Contraintes d’intégrité

FACTURE (NUMFACT, DATFACT) ! Clause permettant de contraindre la modification de tables (conformité aux
données attendues). Les contraintes doivent être exprimées dès la création
! Deux méthodes pour la création d’une table de la table grâce aux mots clés suivants
" en utilisant le langage SQL " en utilisant une interface graphique " DEFAULT : valeur par défaut
spécifique à chaque SGBD " NOT NULL : Le champ doit être saisi
CREATE TABLE FACTURE (
NUMFACT NUMBER IDENTITY " UNIQUE : valeur saisie pour un champ n'existe pas déjà dans la table
PRIMARY KEY, " CHECK : faire un test sur un champ
DATEFACT DATE NOT NULL
); " CONSTRAINT : donner un nom à une contrainte

! Exemple
CREATE TABLE client(
Numcli Number UNIQUE,
Nom varchar2(30) NOT NULL,
Prenom varchar2(30) NOT NULL,
Age Number, check (age < 100),
Email varchar2(50) NOT NULL, check (Email LIKE "%@%"),
DateEnreg date DEFAULT sysdate );
27 28
Contraintes : Définition des clés Insertion d’enregistrements : INSERT – 1 / 2
! L’insertion d’enregistrements permet
! Une clé primaire se définit grâce à la clause PRIMARY KEY suivie de la
liste de colonnes, séparées par des virgules, entre parenthèses : PRIMARY " d'ajouter un enregistrement dans une table
KEY (colonne1, colonne2, ...) " d'ajouter un ensemble d’enreg. spécifié au moyen d’une requête SELECT

! Lorsqu'une liste de colonnes de la table en cours de définition permet de ! L’ajout d’un seul enregistrement s’effectue de la manière suivante :
définir la clé primaire d'une table étrangère, on parle alors de clé INSERT INTO table (champ1, … , champn)
étrangère, et on utilise la clause FOREIGN KEY suivie de la liste de VALUES (valeur1 , … , valeurn)
colonnes de la table en cours de définition, séparées par des virgules, entre
parenthèses, puis de la clause REFERENCES suivie du nom de la table " exemple : ajouter le produit de ref. n° 19 et de désignation Agrafeuse’ dont le
prix HT est 50,6 dh (PRODUIT (REFPROD, DESIGN, PRIXHT) )
étrangère et de la liste de ses colonnes correspondantes, séparées par des
virgules, entre parenthèses : FOREIGN KEY (colonne1, colonne2, ...) INSERT INTO PRODUIT (REFPROD, DESIGN, PRIXHT)
REFERENCES NomTable (colonne1, colonne2, ...) VALUES (19, Agrafeuse', 50.6)

! Exemples ! Remarque : l’insertion dans une table dont la clé est auto-incrémentée
ne doit pas fixer de valeur pour ce champ
alter table client add CONSTRAINT pk_client
PRIMARYKEY (numcli); " exemple : insertion en supposant le champ REFPROD auto-incrémenté
INSERT INTO PRODUIT (DESIGN, PRIXHT)
alter table livraison add CONSTRAINT fk_livraison VALUES ( Agrafeuse', 50.6)
FOREIGN KEY (numfou) REFERENCES fournisseur (numfou); 29 30

Insertion d’enregistrements : INSERT – 2 / 2 Suppression d’enregistrements : DELETE – 1 / 2

! L’ajout d’enregistrements spécifiés par une requête s’effectue de la manière ! La suppression d’enregistrements s’effectue de la manière suivante :
suivante : DELETE
INSERT INTO table (champ1 , … , champn) FROM table
SELECT … FROM … WHERE … WHERE condition
! les enregistrements concernés sont immédiatement supprimés
" le nombre et le type des champs dans la clause SELECT doivent être identiques
à ceux des champs insérés dans table
! Exemple : supprimer toutes les factures émises après le 16/10/2010
DELETE
" exemple : ajouter à la table ANC-PRODUIT tous les produits de PRODUIT FROM FACTURE
WHERE DATFACT > #2010/10/16#
INSERT INTO ANC-PRODUIT (REFPROD, DESIGN, PRIXHT)
SELECT REFPROD, DESIGN, PRIXHT FROM PRODUIT

31 32
Mise à jour d’enregistrements : UPDATE – 1 / 2

! La mise à jour d’enregistrements s’effectue de la manière suivante :

UPDATE table
SET champ1 = valeur1 , … , champn = valeurn
WHERE condition

! les enregistrements sont définitivement mis à jour Chapitre 1

PL/SQL, Triggers et Vues

! Exemple : remplacer la référence de produit 4 par 7 dans les factures
UPDATE EST-FACTURE
SET REFPROD = 7
WHERE REFPROD = 4

33

PL-SQL PL/SQL : Que peut-on faire avec ?

! Langage Procédural pour SQL d’Oracle ! Code pour automatiser un traitement

" Processus périodiques
! Proche de Pascal et Ada
" Processus ponctuels
! Facilités pour " Contraintes d'intégrité élaborées
" Variables " Etc.
" Conditions
! Applications
" Boucles
" Auditing
" Exceptions
" Sécurité
" Stockage de procédures
" Vérification
! Implémentations plus ou moins conformes
" PostgreSQL : PL/pgSQL
" SQL Server : Transact-SQL
" DB2 : SQLPL
" MySql : Stored procedure
" etc.
35 36
PL/SQL : Structure d’un bloc PL/SQL : Déclarations

! Un bloc contient 3 parties ! Types

" Déclarations (optionnel) " issus de SQL : number, date, varchar2, ...
boolean, integer, float, real,...
" Commandes exécutables (corps) "

" Gestion des exceptions (optionnel) ! Variables

" De manière générale : <nom_variable> <type_variable> ;
[DECLARE
" Déclaration avec une valeur au départ <Nom_variable> <type> default <valeur> ;
-- déclarations de types,
-- variables locales au bloc, ! Exemples
-- constantes, " Nomcli varchar2(25) ;
-- exceptions et curseurs] " N number default 2;
BEGIN [<nombloc>]
-- instructions PL/SQL et SQL ! Visibilité d’une variable
-- possibilité de blocs imbriqués " dans le bloc où elle est déclarée
[EXCEPTION " dans les blocs imbriqués(sauf si redéfinie dans bloc imbriqué)
-- Traitement des erreurs]
! Déclarations de constantes
END; /* ou END <nombloc> ; */ <Nom_variable> constant < type> := <valeur>;

37 38

PL/SQL : Corps PL/SQL : Exemple

! Le corps peut comporter des instructions FOURNISSEUR ( numfou NUMBER, nomfou VARCHAR2(30));
" d’affectation
" SQL : commit, delete, insert, locktable, open, rollback, savepoint, select, set transaction, LIVRAISON (numli NUMBER, numfou NUMBER, dateli DATE default sysdate );
update...
" de contrôle (conditionnelles, répétitives) Nom du fournisseur de la livraison numéro 10 ?
" de gestion des erreurs DECLARE
num NUMBER:= 10;
! Chaque instruction est terminée par « ; »
nom VARCHAR2 (30) ;
! L’imbrication de blocs est possible, mais pas recommandée BEGIN
SELECT nomfou INTO nom
! Affectation d’une variable FROM Fournisseur, Livraison
" Opérateur d’affectation (:=) WHERE numli= num and [Link]=[Link];
" ex. [Link] := ‘Livre’ ; DBMS_OUTPUT.PUT_LINE(’La livraison numéro : ’ || num || ’concerne le
! Option into de l’ordre select
fournisseur : ’ || nom);
END;
Select LIBPROD into designation
from Produit
where REFPROD= 2;
39 40
PL/SQL : Structures de contrôle Structures de contrôle : Exemples

! Structure conditionnelle ! Loop

IF <condition>THEN
<instruction>;...
DECLARE
[ELSIF <condition> THEN <instruction>; ... <instruction>; ]
[ELSE <instruction>; ... <instruction>
compteur NUMBER;
END IF; somme NUMBER := 0;
moyenne NUMBER;
! Boucle répétitive simple BEGIN
compteur := 1;
LOOP Sortie d’une boucle
<instruction>; ... <instruction>; LOOP
IF <condition> THEN EXIT ; END IF;
END LOOP; Ou EXIT WHEN <condition> ; somme := somme + compteur ;
compteur := compteur + 1;
! Boucle Tant que EXIT WHEN compteur =11;
WHILE <condition> END LOOP;
LOOP <instruction>; ... <instruction>; moyenne := somme / 10;
END LOOP; DBMS_OUTPUT.PUT_LINE ('La moyenne est '||TO_CHAR(moyenne));
! Boucle For END;

FOR <variable_boucle> IN <borne_inf> ..<borne_sup>

LOOP
<instruction>; ... <instruction>;
END LOOP; 41 42

Structures de contrôle : Exemples Structures de contrôle : Exemples

! While ! For

DECLARE
DECLARE somme NUMBER := 0;
compteur NUMBER; moyenne NUMBER;
somme NUMBER := 0; BEGIN
moyenne NUMBER; FOR compteur IN 1 .. 10 LOOP
BEGIN somme := somme + compteur ;
compteur := 1; END LOOP;
WHILE compteur <= 10 LOOP moyenne := somme / 10;
somme := somme + compteur ; DBMS_OUTPUT.PUT_LINE ('La moyenne est '||TO_CHAR(moyenne));
compteur := compteur + 1; END;
END LOOP;
moyenne := somme / 10;
DBMS_OUTPUT.PUT_LINE ('La moyenne est '||TO_CHAR(moyenne));
END;

43 44
Procédures PL/SQL Procédures PL/SQL : Exemple

! Utilisées pour enregistrer des traitements fréquemment utilisés au niveau CREATE OR REPLACE PROCEDURE compteARebours (n NUMBER) IS
du noyau sans valeur de retour BEGIN
IF n >= 0 THEN
! Syntaxe DBMS_OUTPUT.PUT_LINE ( n ) ;
compteARebours ( n − 1 ) ;
CREATE OR REPLACE PROCEDURE / nom / ( / paramètres / ) IS END IF;
/ déclaration des variables locales / END;
BEGIN
/ instructions /

END; ! Invocation
" En PL/SQL, une procédure s’invoque tout simplement avec son nom
" Sous SQL+, on doit utiliser le mot-clé CALL. Par exemple, on invoque le compte
à rebours sous SQL+ avec la commande CALL compteARebours(20)

45 46

Fonctions PL/SQL Fonctions PL/SQL : Exemple

! Utilisées pour enregistrer des traitements fréquemment utilisés au niveau CREATE OR REPLACE FUNCTION Maximum( a NUMBER, b NUMBER)
du noyau avec valeur de retour RETURN NUMBER
IS
! Syntaxe BEGIN
IF a < b THEN RETURN b ;
ELSE
CREATE OR REPLACE FUNCTION / nom / ( / paramètres / )
RETURN a ;
RETURN / types /
END IF;
IS
END;
/ déclaration des variables locales /
BEGIN
/ instructions / ! Invocation
" En PL/SQL, une fonction s’invoque tout simplement avec son nom
END; " Sous SQL+, On passe par une pseudo-table nommée DUAL de la façon
suivante : SELECT Maximum(21, 12) FROM DUAL;

47 48
Triggers Syntaxe

! Objectif : Comment assurer une cohérence sémantique plus complexe

– Lorsqu’une table est modifiée, générer la modification d’une autre
– Lorsqu’une valeur est insérée la modifier avant de la stocker dans la BD
– Etc. Insert | Update | Delete
! Règle : Événement-Condition-Action (ECA)
" Evènement
– Insert, delete, update sur une table ou vue
" Condition
– Test ou prédicat logique
" Action
– Si la condition est satisfaite, code PL/SQL à exécuter

Les attributs des tables sont accessibles à travers les variable :NEW et :OLD

49 50

Triggers : Exemple Vues

CATALOGUE ( numcat NUMBER, datedeb DATE, datefin DATE) ! Une vue est une requête stockée qui est interrogée comme une table

!Trigger sur la relation CATALOGUE. On empêche l’insertion ou la ! Création d’une vue

modification d’un tuple si la date de début de la validité du catalogue est CREATE VIEW <nom-vue> AS <requête définissant la vue>
supérieure à celle de sa fin
CREATE OR REPLACE TRIGGER CATALOGUE_bef_ins_upd_row ! Exemple
BEFORE INSERT OR UPDATE PRODUIT ( numprod NUMBER, nomprod VARCHAR2(30))
ON CATALOGUE PROPOSER ( numfou NUMBER, numprod NUMBER, prix NUMBER NOTNULL)
FOR EACH ROW
" Créer une vue appelée «PoduitAffaire » sur les produits dont le prix ne dépasse pas
DECLARE
dates_error EXCEPTION; 1000DH (Attributs de la vue : numprod, nomprod, prix)
BEGIN
IF :[Link] > :[Link] THEN RAISE dates_error;
CREATE VIEW ProduitAffaire AS
END IF;
SELECT numprod, nomprod, prix
EXCEPTION
WHEN dates_error THEN FROM PRODUIT,PROPOSER
RAISE_APPLICATION_ERROR (-20001, 'Date de début est supérieure à la date WHERE prix<1000 and [Link] = [Link];
de fin');
END; 51 52