oracle_trés bon

Cours Oracle

Alexandre Mesle

13 octobre 2009

Table des matieres

1 Notes de cours 41.1 Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4

1.1.1 Qu’est-ce qu’un SGBD ? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 41.1.2 Organisation relationnelle des donnees . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 41.1.3 Survol de SQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 51.1.4 SQL+ et iSQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6

1.2 Contraintes declaratives . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 81.2.1 Valeurs par defaut . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 81.2.2 Champs non renseignes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 81.2.3 Cle primaire . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 81.2.4 Cle etrangere . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 81.2.5 Syntaxe alternative . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8

1.3 Introduction aux requetes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 101.3.1 Complements sur SELECT . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 101.3.2 Instruction WHERE . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 101.3.3 Conditions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 101.3.4 Suppression . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 111.3.5 Mise a jour . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 12

1.4 Jointures . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 131.4.1 Principe . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 131.4.2 Produit cartesien . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 141.4.3 Jointure . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 141.4.4 Jointures reflexives . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 15

1.5 Agregation de donnees . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 171.5.1 Fonctions d’agregation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 171.5.2 Groupage . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 19

1.6 Vues . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 211.6.1 Definition . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 211.6.2 Syntaxe . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 211.6.3 Application . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 211.6.4 Suppression . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 22

1.7 Requetes imbriquees . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 231.7.1 Sous requetes renvoyant une valeur scalaire . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 231.7.2 Sous requetes renvoyant une colonne . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 241.7.3 Sous requetes non correlees renvoyant une table . . . . . . . . . . . . . . . . . . . . . . . . . . . 251.7.4 Sous requetes correlees . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 26

1.8 Complements sur les types . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 281.8.1 Types numeriques . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 281.8.2 Types chaine de caracteres . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 291.8.3 Types date . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 291.8.4 La fonction inclassable . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 301.8.5 Contraintes CHECK . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 30

1.9 Introduction au PL/SQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 311.9.1 PL/SQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 311.9.2 Blocs . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 311.9.3 Affichage . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 311.9.4 Variables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 31

1

1.9.5 Traitements conditionnels . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 311.9.6 Traitements repetitifs . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 32

1.10 Tableaux et structures . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 331.10.1 Tableaux . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 331.10.2 Structures . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 34

1.11 Utilisation du PL/SQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 361.11.1 Affectation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 361.11.2 Tables et structures . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 361.11.3 Transactions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 37

1.12 Exceptions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 381.12.1 Rattraper une exception . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 381.12.2 Exceptions predefinies . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 391.12.3 Codes d’erreur . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 391.12.4 Declarer et lancer ses propres exceptions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 39

1.13 Sous-programmes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 411.13.1 Procedures . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 411.13.2 Fonctions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 41

1.14 Curseurs . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 431.14.1 Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 431.14.2 Les curseurs . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 43

1.15 Curseurs parametres . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 451.15.1 Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 451.15.2 Definition . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 451.15.3 Declaration . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 451.15.4 Ouverture . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 451.15.5 Lecture d’une ligne, fermeture . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 451.15.6 Boucle pour . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 461.15.7 Exemple recapitulatif . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 46

1.16 Triggers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 471.16.1 Principe . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 471.16.2 Classification . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 471.16.3 Creation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 471.16.4 Acces aux lignes en cours de modification . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 481.16.5 Contourner le probleme des tables en mutation . . . . . . . . . . . . . . . . . . . . . . . . . . . 50

1.17 Packages . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 531.17.1 Principe . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 531.17.2 Specification . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 531.17.3 Corps . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 53

2 Exercices 552.1 Contraintes declaratives . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 552.2 Introduction aux requetes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 562.3 Jointures . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 582.4 Agregation de donnees . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 602.5 Vues . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 612.6 Requetes imbriquees . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 622.7 Complements sur les types . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 632.8 Revisions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 642.9 Introduction au PL/SQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 652.10 Tableaux et Structures . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 662.11 Utilisation PL/SQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 682.12 Exceptions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 692.13 Sous-programmes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 702.14 Curseurs . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 712.15 Curseurs parametres . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 722.16 Triggers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 732.17 Packages . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 742.18 Revisions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 75

2

3 Corriges 763.1 Contraintes declaratives . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 763.2 Introduction aux requetes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 783.3 Jointures . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 803.4 Agregation de donnees . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 823.5 Vues . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 833.6 Requetes imbriquees . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 843.7 Complements sur les types . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 863.8 Revisions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 873.9 Examen Type . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 883.10 Introduction au PL/SQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 923.11 Tableaux et Structures . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 933.12 Application du PL/SQL et Exceptions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 963.13 Sous-programmes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1003.14 Curseurs . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1033.15 Curseurs parametres . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1063.16 Triggers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1073.17 Packages . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1163.18 Revisions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 117

A Scripts de creation de bases 121A.1 Livraisons Sans contraintes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 121A.2 Modules et prerequis . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 122A.3 Geometrie . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 123A.4 Livraisons . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 124A.5 Arbre genealogique . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 125A.6 Comptes bancaires . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 126A.7 Comptes bancaires avec exceptions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 128A.8 Secretariat pedagogique . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 130A.9 Mariages . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 132

3

Chapitre 1

Notes de cours

1.1 Introduction

1.1.1 Qu’est-ce qu’un SGBD ?

– Definition : logiciel qui stocke des donnees de facon organisee et coherente.– Access : version edulcoree. Mais mono-utilisateur, et faible capacite.– Les donnees sont stockees dans des fichiers geres par le serveur de base de donnees. Cette operation est opaque.

On transmet depuis un client des instructions a la base par l’intermediaire du langage SQL.

Avantages :– permet de maintenir de facon fiable l’integrite des donnees– operations de bas niveau opaques– rapide– multi-utilisateurs– moins de trafic sur le reseau– securite

Inconvenient :– Un peu plus long que bien programme en C, et encore...

Plusieurs facons d’organiser les donnees :– hierarchique– relationnel– deductif– objet– etc.

Les gros SGBD-R :– DB2 (IBM)– Oracle– Microsoft SQL Server– mySQL

SQL

Structured Query Language. SQL est le langage standard de la base de donnees. D’un SGBD a l’autre, le SQLchange tres peu. Ce cours est surtout un cours de SQL.

1.1.2 Organisation relationnelle des donnees

– Les donnes sont stockees dans des tables.– Table : tableau a deux entrees.

Le MPD nous donne, pour chaque table, les colonnes (i.e. champs) que doit comporter la table.

nomtable ( colonne1 , colonne2 , . . . , co lonnen )

Par exemple :

4

CLIENT( numero , prenom , nom, emai l )

nous donnera la table

numero nom prenom email20505372 Alexandre Mesle [email protected]

... ... ... ...

Supposons que l’on veuille gerer des commandes emises par des clients. Nous souhaitons stocker les donnees sui-vantes :

– nom, prenom et adresse complete des clients– produits proposes et couts unitaires– pour chaque commande, le detail et le montant.

Apres maintes peripeties, nous en arrivons au MPD suivant :

CLIENT( numero c l i ent , prenom , nom, adresse1 , adresse2 , #CP)CODE POSTAL(CP, v i l l e )PRODUIT( numero produit , d e s c r i p t i o n , p r i x u n i t a i r e )COMMANDE(#numero c l i ent , numero commande )LIGNECOMMANDE(#numero c l i ent , #numero commande , #numero produit , quant i t e )

Nous pouvons representer la base, avec quelques donnees inserees, par des tableaux

numero client prenom nom adresse1 adresse2 #CP1 Alexandre Mesle Cite les framboises 28, rue du chemin vert 750132 Amedee Morflegroin 29, rue de Choisy 952003 Medor dans sa niche 750134 Louis-Hubert Martin 112, rue de la Pompe 75016

CP ville75013 Paris75016 Paris95200 Sarcelles

numero produit decription prix unitaire1 Boite de cornichons 2.52 Goupillon 6.53 Cotons-tige 1.24 Ajax WC 1.25 Place concert des Stones 1456 Roue de secours 757 Compas 48 Armoire Gludehnblourf 1469 Pate dietetique 1210 Croquettes laxatives 8

#numero client numero commande1 11 22 13 1

#numero client #numero commande #numero produit quantite1 1 5 21 2 4 291 2 7 12 1 1 43 1 9 238

1.1.3 Survol de SQL

Le SQL de base se decline en quatre parties :

5

– DDL : Data definition language– DML : Data manipulation language– DQL : Data query language– DCL : Data control languageA cela s’ajoute le PL/SQL. Celui-ci permet de gerer presque toutes les contraintes et de maintenir la coherence de

la base de donnees. Mais c’est beaucoup plus complique...

Creer des tables

Les types pour commencer– numeriques : number– chaınes de caracteres : varchar2(taille)syntaxe :

CREATE TABLE <nomdelatable>(<d e s c r i p t i o n c o l o n n e 1 >, . . . , <d e s c r i p t i o n c o l o n n e n >)

Pour chaque colonne :

<nomcolonne> <type> [< o p t i o n s e v e n t u e l l e s >]

exemple :

create table c l i e n t(numcli number ,nom varchar2 (256) ,prenom varchar2 (256))

Afficher le contenu d’une table

syntaxe :

SELECT ∗ FROM <nomdelatable>

exemple :

select ∗ from c l i e n t

Ajouter une ligne dans une table

syntaxe :

INSERT INTO <nomdelatable> (<nomcolonne 1 >, . . . , <nomcolonne n >)VALUES (<va l eurco lonne 1 >, . . . , <va leurco lonne n >)

exemple :

INSERT INTO CLIENT ( numcli , nom, prenom ) VALUES (1 , ’ Mesle ’ , ’ Alexandre ’ )

1.1.4 SQL+ et iSQL

Connection

login : scottpassword : tiger

Liste des tables

SELECT table name FROM u s e r t a b l e s ;

Description des tables

Syntaxe :

6

DESC <nomtable >;

Exemple :

DESC c l i e n t ;

Attention

Les commandes de SQL+ se terminent par un point-virgule !

7

1.2 Contraintes declaratives

1.2.1 Valeurs par defaut

create table c l i e n t(numcli number ,nom varchar2 (256) default ’Moi ’ ,prenom varchar2 (256))

fait de ’Moi’ le nom par defaut.1.2.2 Champs non renseignes

create table c l i e n t(numcli number ,nom varchar2 (256) NOT NULL,prenom varchar2 (256) NOT NULL)

force la saisie des champs nom et prenom.1.2.3 Cle primaire

Une cle primaire est :– toujours renseignee– uniqueOn peut preciser PRIMARY KEY dans la creation de table

create table c l i e n t(numcli number PRIMARY KEY,nom varchar2 (256) ,prenom varchar2 (256))

La colonne numcli est cle primaire, toute insertion ne respectant pas la contraine de cle primaire sera refusee parOracle.

1.2.4 Cle etrangere

Une cle etrangere reference une ligne d’une table quelconque :Syntaxe :

REFERENCES <nomtable> (<nomcolonne>)

create table c l i e n t(numcli number PRIMARY KEY,nom varchar2 (256) ,prenom varchar2 (256) ,numdept number REFERENCES DEPT (nd))

Une ligne ne pourra etre inseree dans la table client que s’il existe dans la table DEPT une ligne dont la valeur ndest la meme que la valeur numdept en cours d’insertion.

On remarque qu’il devient impossible d’ecraser la table DEPT si elle est referencee par une cle etrangere.

1.2.5 Syntaxe alternative

ALTER TABLE <nomtable>ADD [CONSTRAINT <nomcontrainte >] <d e s c r i p t i o n c o n t r a i n t e >

8

descriptioncontrainte d’une cle primaire :

PRIMARY KEY(<co lonne 1 >, . . . , <colonne n >)

descriptioncontrainte d’une cle etrangere :

FOREIGN KEY(<co lonne 1 >, . . . , <colonne n >)REFERENCES <t a b l e r e f e r e n c e e > (<co lonne 1 >, . . . , <colonne n >)

Il est aussi possible de placer une descriptioncontrainte dans le CREATE TABLE. Par exemple,

create table c l i e n t(numcli number ,nom varchar2 (256) ,prenom varchar2 (256) ,numdept number ,PRIMARY KEY (number) ,FOREIGN KEY ( numdept ) REFERENCES DEPT (nd))

On remarque qu’il est possible de nommer une contrainte. C’est utile si on souhaite la supprimer :

ALTER TABLE <nomtable> DROP CONSTRAINT <nomcontrainte>

Pour lister les contraintes :

SELECT ∗ FROM USER CONSTRAINTS

9

1.3 Introduction aux requetes

1.3.1 Complements sur SELECT

Il est possible d’utiliser SELECT pour n’afficher que certaines colonnes d’une table. Syntaxe :

SELECT <co lonne 1 >, <co lonne 2 >, . . . , <colonne n>FROM <table>

Cette instruction s’appelle une requete, elle affichera pour chaque ligne de la table les valeurs des colonnes colonne1

a colonnen. Il est possible de supprimer les lignes en double a l’aide du mot-cle DISTINCT. Par exemple :

SELECT DISTINCT <co lonne 1 >, <co lonne 2 >, . . . , <colonne n>FROM <table>

Pour trier les donnees, on utilise ORDER BY. Exemple :

SELECT <co lonne 1 >, <co lonne 2 >, . . . , <colonne n>FROM <table>ORDER BY <co lonne 1b i s >, <co lonne 2b i s >, . . . , <co lonne nb i s >

Cette instruction trie les donnees par colonne1bis croissants. En cas d’egalite, le tri est fait par colonne2bis croissants,etc. Pour trier par ordre decroissant, on ajoute DESC apres le nom de la colonne choisie comme critere decroissant. Parexemple :

SELECT <co lonne 1 >, <co lonne 2 >, . . . , <colonne n>FROM <table>ORDER BY <co lonne 1b i s > DESC, <co lonne 2b i s >, . . . , <co lonne nb i s >

1.3.2 Instruction WHERE

Cette instruction permet de ne selectionner que certaines lignes de la table. Par exemple la requete

SELECT nom cl ient , prenom c l i entFROM c l i e n tWHERE numero c l i ent = 1

va afficher les nom et prenom du client dont le numero est 1. La syntaxe generale est

SELECT <co lonne 1 >, <co lonne 2 >, . . . , <colonne n>FROM <table>WHERE <cond i t ion >

condition sera evaluee pour chaque ligne de la table, et seules celles qui vefieront cette condition feront partie duresultat de la requete.

1.3.3 Conditions

Comparaison

Les conditions peuvent etre des relations d’egalite (=), de difference (<>), d’inegalite (<, >, >= ou <=) sur descolonnes :

numero c l i ent = 2nom cl i ent = ’ Chirac ’prenom c l i ent <> ’ Hubert ’s a l a r y < 230taxes >= 23000

Negation

La negation d’une condition s’obtient a l’aide de NOT. Par exemple, il est possible de re-ecrire les conditions ci-avant :

NOT ( numero c l i ent <> 2)NOT ( nom cl i ent <> ’ Chirac ’ )NOT ( prenom c l i ent = ’ Hubert ’ )NOT ( s a l a r y >= 230)NOT ( taxes < 23000)

10

Connecteurs logiques

De meme, vous avez a votre disposition tous les connecteurs logiques binaires : AND, OR. Ainsi, les deux conditionssuivantes sont les memes :

NOT( (nom = ’ Bush ’ ) AND ( prenom <> ’ Medor ’ ) )(nom <> ’ Bush ’ ) OR ( prenom = ’ Medor ’ )

NULLite

Un champ non renseigne a la valeur NULL, dans une comparaison, NULL n’est jamais egal a quelque valeur qu’ilsoit ! La condition suivante est toujours fausse :

NULL = NULL;

La requete suivante ne renvoie aucune ligne :

SELECT ∗ FROM EMP WHERE COMM=NULL;

Pour tester la nullite d’un champ, on utilise IS NULL, par exemple :

SELECT ∗ FROM EMP WHERE COMM IS NULL;

La non-nullite se teste de deux facons :

WHERE NOT (COMM IS NULL) ;WHERE COMM IS NOT NULL

Encadrement

Une valeur numerique peut etre encadree a l’aide de l’operateur BETWEEN, par exemple les deux conditions suivantessont equivalentes :

SALAIRE BETWEEN 1000 AND 5000(SALAIRE >= 1000) AND (SALAIRE <= 5000)

Inclusion

L’operateur IN permet de tester l’appartenance a une liste de valeurs. Les deux propositions suivantes sontequivalentes

NAME IN ( ’ Mesle ’ , ’ Bush ’ , ’ Medor ’ )(NAME = ’ Mesle ’ ) OR (NAME = ’ Bush ’ ) OR (NAME = ’ Medor ’ )

LIKE

LIKE sert a comparer le contenu d’une variable a un litteral generique. Par exemple, la condition

NAME LIKE ’M%’

sera verifiee si NAME commence par un ’M’. Ca fonctionne aussi sur les valeurs de type numerique, la condition

SALARY LIKE ’ %000000000 ’

sera verifiee si SALARY se termine par 000000000. Le caractere % peut remplacer dans le litteral n’importe que suite,vide ou non, de caracteres ; il a le meme role que * en DOS et en SHELL. Le caractere remplace un et un seulcaractere dans le litteral. Par exemple, la condition

NAME LIKE ’ B s%’

ne sera verifiee que si NAME commence par un ’B’ et contient un ’s’ en troisieme position.

1.3.4 Suppression

L’expression

DELETE FROM <NOMTABLE> WHERE <CONDITION>

efface de la table NOMTABLE toutes les lignes verifiant condition. Attention ! La commande

DELETE FROM <NOMTABLE>

efface toutes les lignes de la table NOMTABLE !

11

1.3.5 Mise a jour

L’expression

UPDATE <NOMTABLE> SET<co lonne 1> = <va leur 1 >,<co lonne 2> = <va leur 2 >,. . . ,

<colonne n> = <valeur n>WHERE <CONDITION>

modifie les lignes de la table NOMTABLE verifiant condition. Elle affecte au champ colonnei la valeur valeuri. Parexemple,

UPDATE CLIENT SET NAME = ’ Medor ’ WHERE LUNCH = ’ Bones ’

affecte la valeur ’Medor’ aux champs noms de toutes les lignes dont la valeur LUNCH est egale a ’Bones’. Il est possible,dans une modification, d’utiliser les valeurs des autres champs de la ligne, voire meme l’ancienne valeur de ce champ.Par exemple,

UPDATE CLIENT SET SALARY = SALARY + 5000

augmente tous les salaires de 5000 (choisissez l’unite !).

12

1.4 Jointures

1.4.1 Principe

Etant donne le code ci-dessous,

CREATE TABLE MODULE(numMod number primary key ,nomMod varchar2 (30)) ;

CREATE TABLE PREREQUIS(numMod number r e f e r e n c e s MODULE(numMod) ,numModPrereq number r e f e r e n c e s MODULE(numMod) ,noteMin number(2 ) DEFAULT 10 NOT NULL ,PRIMARY KEY(numMod, numModPrereq )) ;

INSERT INTO MODULE VALUES (1 , ’ ORacle ’ ) ;INSERT INTO MODULE VALUES (2 , ’C++’ ) ;INSERT INTO MODULE VALUES (3 , ’C ’ ) ;INSERT INTO MODULE VALUES (4 , ’ Algo ’ ) ;INSERT INTO MODULE VALUES (5 , ’ Merise ’ ) ;INSERT INTO MODULE VALUES (6 , ’PL/SQL Oracle ’ ) ;INSERT INTO MODULE VALUES (7 , ’mySQL ’ ) ;INSERT INTO MODULE VALUES (8 , ’ Algo avancee ’ ) ;

INSERT INTO PREREQUIS (numMod, numModPrereq ) VALUES (1 , 5 ) ;INSERT INTO PREREQUIS (numMod, numModPrereq ) VALUES (2 , 3 ) ;INSERT INTO PREREQUIS VALUES (6 , 1 , 1 2 ) ;INSERT INTO PREREQUIS (numMod, numModPrereq ) VALUES (6 , 5 ) ;INSERT INTO PREREQUIS (numMod, numModPrereq ) VALUES (8 , 5 ) ;INSERT INTO PREREQUIS (numMod, numModPrereq ) VALUES (7 , 5 ) ;

Si on souhaite connaıtre les numeros des modules prerequis pour s’inscrire dans le module ’PL/SQL Oracle’, ilnous faut tout d’abord le numero de ce module :

SQL> SELECT numMod FROM module WHERE nomMod = ’PL/SQL Oracle ’ ;

NUMMOD−−−−−−−−−−

6

Ensuite, cherchons les numeros des modules prerequis pour s’inscrire dans le module numero 6,

SQL> SELECT numModPrereq FROM p r e r e q u i s WHERE numMod = 6 ;

NUMMODPREREQ−−−−−−−−−−−−

15

Et pour finir, allons recuperer les noms de ces modules,

SQL> SELECT nomMod FROM module WHERE numMod IN (1 , 5 ) ;

NOMMOD−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−OracleMerise

Vous etes probablement tous en train de vous demander s’il n’existe pas une methode plus simple et plus rapide,et surtout une facon d’automatiser ce que nous venons de faire. Il existe un moyen de selectionner des donnees dansplusieurs tables simultanement. Pour traiter la question ci-dessus il suffisait de saisir :

13

SQL> SELECT m2.nomMod2 FROM module m1, module m2, p r e r e q u i s p3 WHERE m1.numMod = p .numMod AND m2.numMod = p . numModprereq4 AND m1.nomMod = ’PL/SQL Oracle ’ ;

NOMMOD−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−OracleMerise

Le but de ce chapitre est d’expliciter ce type de commande.

1.4.2 Produit cartesien

L’instruction SELECT ... FROM ... peut s’etendre de la facon suivante :

SELECT < l i s t e c o l o n n e s >FROM < l i s t e t a b l e s >

L’exemple ci-dessous vous montre le resultat d’une telle commande.

SQL> SELECT ∗ FROM proposer , produ i t ;

NUMFOU NUMPROD PRIX NUMPROD NOMPROD−−−−−−−−−− −−−−−−−−−− −−−−−−−−−− −−−−−−−−−− −−−−−−−−−−−−−−−−−−−−−−−−−−−−−−

1 1 200 1 Roue de s e cour s1 1 200 2 Poupee Batman1 1 200 3 Cotons t i g e s1 1 200 4 Cornichons1 2 15 1 Roue de s e cour s1 2 15 2 Poupee Batman1 2 15 3 Cotons t i g e s1 2 15 4 Cornichons2 2 1 1 Roue de s e cour s2 2 1 2 Poupee Batman2 2 1 3 Cotons t i g e s


2 2 1 4 Cornichons3 3 2 1 Roue de s e cour s3 3 2 2 Poupee Batman3 3 2 3 Cotons t i g e s3 3 2 4 Cornichons

16 l i g n e ( s ) s e l e c t i o n n e e ( s ) .

Placer une liste de tables dans le FROM revient a former toutes les combinaisons de lignes possibles. Cependant, celaa relativement peu de sens.

1.4.3 Jointure

Il serait plus interessant, dans le cas present, de ne voir s’afficher que des lignes dont les numeros de produitsconcordent. Pour ce faire, il suffit d’utiliser WHERE. Par exemple,

SQL> SELECT ∗ FROM proposer , produ i t2 WHERE proposer . numprod = produi t . numprod ;


1 1 200 1 Roue de s e cour s2 2 1 2 Poupee Batman1 2 15 2 Poupee Batman

14

3 3 2 3 Cotons t i g e s

Nous avons mis en correspondance des lignes de la table proposer avec des lignes de la table produit en utilisant lefait que numprod est une cle etrangere dans proposer. Comme la colonne numprod apparait deux fois dans la requete,il est necessaire de la prefixer par le nom de la table de sorte que chaque colonne puisse etre designee de facon nonambigue. Si on veut mettre face a face les noms des produits et les noms des fournisseurs, il suffit de saisir la requete

SQL> SELECT nomfou , nomprod2 FROM produit , f o u r n i s s e u r , proposer3 WHERE produi t . numProd = proposer . numProd4 AND f o u r n i s s e u r . numFou = proposer . numFou ;

NOMFOU NOMPROD−−−−−−−−−−−−−−−−−−−−−−−−−−−−−− −−−−−−−−−−−−−−−−−−−−−−−−−−−−−−f 1 Roue de s e cour sf 2 Poupee Batmanf1 Poupee Batmanf3 Cotons t i g e s

1.4.4 Jointures reflexives

En utilisant la syntaxe suivante, il est possible de rennomer les tables,

FROM <tab l e 1 > <table 1 renommee >, . . . , <tab le n > <table n renommee>

Reformulons la requete ci-dessus,

SQL> SELECT nomfou , nomprod2 FROM produi t p , f o u r n i s s e u r f , proposer pr3 WHERE p . numProd = pr . numProd4 AND f . numFou = pr . numFou ;

NOMFOU NOMPROD−−−−−−−−−−−−−−−−−−−−−−−−−−−−−− −−−−−−−−−−−−−−−−−−−−−−−−−−−−−−f 1 Roue de s e cour sf 2 Poupee Batmanf1 Poupee Batmanf3 Cotons t i g e s

Le renommage permet entre autres de faire des jointures reflexives, c’est a dire entre une table et elle meme. Parexemple, en reprenant la table intervalle,

SQL> SELECT ∗ FROM i n t e r v a l l e ;

BORNEINF BORNESUP−−−−−−−−−− −−−−−−−−−−

0 302 32 565 107 328 27

12 312 3021 834 26


La commande ci-dessous affiche tous les couples d’intervalles ayant une borne en commun,

SQL> SELECT ∗ FROM i n t e r v a l l e i , i n t e r v a l l e j2 WHERE ( i . borne In f = j . borne In f OR i . borneSup = j . borneSup )3 AND i . rowid <> j . rowid ;

15

BORNEINF BORNESUP BORNEINF BORNESUP−−−−−−−−−− −−−−−−−−−− −−−−−−−−−− −−−−−−−−−−

0 30 12 302 3 2 562 3 12 32 56 2 3

12 3 2 312 3 12 3012 30 0 3012 30 12 3


Que ceux qui ont du courage reformulent la requete sans utiliser le rowid !

16

1.5 Agregation de donnees

1.5.1 Fonctions d’agregation

Exemple introductif

Nous voulons connaıtre le nombre de lignes de table produit. Deux facons de proceder :

1. Solution moche

SQL> SELECT ∗ FROM PRODUIT;

NUMPROD NOMPROD−−−−−−−−−− −−−−−−−−−−−−−−−−−−−−−−−−−−−−−−

1 Roue de s e cour s2 Poupee Batman3 Cotons t i g e s4 Cornichons


On a la reponse avec le nombre de lignes selectionnees.2. Solution belle

SQL> SELECT count (∗ ) FROM PRODUIT;

COUNT(∗ )−−−−−−−−−−

4

1 l i g n e s e l e c t i o n n e e .

La reponse est le resultat de la requete.

Definition

Une fonction d’agregation retourne une valeur calculee sur toutes les lignes de la requete (nombre, moyenne...).Nous allons utiliser les suivantes :

– COUNT(col) : retourne le nombre de lignes dont le champ col est non NULL.– AVG(col) : retourne la moyenne des valeurs col sur toutes les lignes dont le champ col est non NULL.– MAX(col) : retourne la plus grande des valeurs col sur toutes les lignes dont le champ col est non NULL.– MIN(col) : retourne la plus petite des valeurs col sur toutes les lignes dont le champ col est non NULL.– SUM(col) : retourne la somme des valeurs col sur toutes les lignes dont le champ col est non NULL.

Exemples d’utilisation

L’exemple suivant retourne le prix du produit propose au prix maximal.

SQL> SELECT MAX( pr ix )2 FROM PROPOSER;

MAX(PRIX)−−−−−−−−−−

200


Il est possible de renommer la colonne MAX(prix), en utilisant le mot cle AS :

SQL> SELECT MAX( pr ix ) AS PRIX MAXIMAL2 FROM PROPOSER;

PRIX MAXIMAL−−−−−−−−−−−−

200

17


Les requetes suivantes recuperent le nom du fournisseur proposant l’article ’Poupee Batman’ au prix le moins eleve :

SQL> SELECT MIN( pr ix ) AS PRIX MINIMUM2 FROM PROPOSER PR, PRODUIT P3 WHERE PR. numprod = P. numprod4 AND nomprod = ’ Poupee Batman ’ ;

PRIX MINIMUM−−−−−−−−−−−−

1


SQL> SELECT nomfou2 FROM FOURNISSEUR F, PROPOSER PR, PRODUIT P3 WHERE F. numfou = PR. numfou4 AND PR. numprod = P. numprod5 AND nomprod = ’ Poupee Batman ’6 AND pr ix = 1 ;

NOMFOU−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−f 2


Il est possible de faire cela avec une seule requete en recuperant le prix minimum dans une requete imbriquee. Maiscela sera pour un cours ulterieur.

Complements sur COUNT

On recupere le nombre de ligne retournees par une requete en utilisant COUNT(*). Par exemple, si on souhaiteconnaıtre le nombre de produits proposes par le fournisseur ’f1’ :

SQL> SELECT COUNT(∗ ) AS NB PROD2 FROM FOURNISSEUR F, PROPOSER P3 WHERE F. numfou = P. numfou4 AND nomfou = ’ f1 ’ ;

NB PROD−−−−−−−−−−

2


On aurait aussi pu saisir :

SQL> SELECT COUNT(numprod) AS NB PROD2 FROM FOURNISSEUR F, PROPOSER P3 WHERE F. numfou = P. numfou4 AND nomfou = ’ f1 ’ ;

NB PROD−−−−−−−−−−

2


Pour connaıtre le nombre de produits proposes, c’est a dire dont le numprod a une occurence dans la table PROPOSER,on procede de la facon suivante :

18

SQL> SELECT COUNT(DISTINCT numprod ) AS NB PRODUITS PROPOSES2 FROM PROPOSER;

NB PRODUITS PROPOSES−−−−−−−−−−−−−−−−−−−−

3


Le DISTINCT nous sert a eviter qu’un meme produit propose par des fournisseurs differents soit comptabiliseplusieurs fois.

1.5.2 Groupage

L’instruction GROUP BY

Les operation d’agregation considerees jusqu’a maintenant portent sur la totalite des lignes retournees par lesrequetes, l’instruction GROUP BY permet de former des paquets a l’interieur desquels les donnees seront agregees. Cetteinstruction s’utilise de la maniere suivante

SELECT . . .FROM . . .WHERE. . .GROUP BY < l i s t e c o l o n n e s >ORDER BY . . .

La liste des colonnes sert de critere pour repartir les lignes dans des paquets de lignes. Si par exemple noussouhaitons afficher la liste des nombres de produits proposes par chaque fournisseur :

SQL> SELECT nomfou , COUNT(DISTINCT numprod) AS NB PRODUITS PROPOSES2 FROM FOURNISSEUR F, PROPOSER P3 WHERE F. numfou = P. numfou4 GROUP BY nomfou ;

NOMFOU NB PRODUITS PROPOSES−−−−−−−−−−−−−−−−−−−−−−−−−−−−−− −−−−−−−−−−−−−−−−−−−−f 1 2f2 1f3 1


L’instruction HAVING

Supposons que de la requete precedente, nous ne souhaitions garder que les lignes pour lesquelles la valeurNB PRODUITS PROPOSES est egale a 1. Ajouter une condition dans WHERE serait inutile, le filtrage occasionne par WHEREest effectue avant l’agregation. Il nous faudrait une instruction pour n’inclure que des groupes de donnees repondantcertains criteres. L’instruction utilisee pour ce faire est HAVING. Son utilisation est la suivante :

SELECT . . .FROM . . .WHERE . . .GROUP BY. . .HAVING <cond i t ion >ORDER BY . . .

Par exemple,

SQL> SELECT nomfou , COUNT(DISTINCT numprod ) AS NB PRODUITS PROPOSES2 FROM FOURNISSEUR F, PROPOSER P3 WHERE F. numfou = P. numfou4 GROUP BY nomfou5 HAVING COUNT(DISTINCT numprod ) = 1

19

6 ORDER BY nomfou DESC;

NOMFOU NB PRODUITS PROPOSES−−−−−−−−−−−−−−−−−−−−−−−−−−−−−− −−−−−−−−−−−−−−−−−−−−f 3 1f2 1


Affichons les noms des fournisseurs qui ont livre strictement plus d’un produit different (toutes livraisons confon-dues),

SQL> SELECT nomfou2 FROM FOURNISSEUR F, DETAILLIVRAISON D3 WHERE F. numfou = D. numfou4 GROUP BY nomfou5 HAVING count (DISTINCT D. numprod) > 1 ;

NOMFOU−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−f 1


20

1.6 Vues

1.6.1 Definition

Une vue est une table contenant des donnees calculees sur celle d’une autre table. Les donnees d’une vue sont toutle temps a jour. Si vous modifiez les donnees d’une des tables sur lesquelles est calculee la vue, alors les modificationssont automatiquement repercutees sur la vue.

1.6.2 Syntaxe

Appreciez la simplicite de la syntaxe :

CREATE VIEW <nom vue> AS <requete>

1.6.3 Application

Par exemple, la requete suivante met en correpondance les noms des produits avec le nombre de fournisseurs quile proposent :

SQL> SELECT nomprod , COUNT( numfou ) AS NB FOURNISSEURS2 FROM PRODUIT P3 LEFT OUTER JOIN PROPOSER PR4 ON P. numprod = PR. numprod5 GROUP BY nomprod6 ORDER BY COUNT( numfou ) ;

NOMPROD NB FOURNISSEURS−−−−−−−−−−−−−−−−−−−−−−−−−−−−−− −−−−−−−−−−−−−−−Cornichons 0Cotons t i g e s 1Roue de s e cour s 1Poupee Batman 2


Ce type de requete sera explite dans un cours ulterieur. Pour le moment, notez juste que les outils dont vousdisposez pour le moment ne vous permettront pas de formuler une requete affichant les noms des produits n’ayantaucun fournisseur. Creons une vue pour ne pas avoir a se farcir la requete chaque fois que nous aurons besoin de cesinformations :

SQL> CREATE VIEW NB FOURNISSEURS PAR PRODUIT AS2 SELECT nomprod , COUNT( numfou ) AS NB FOURNISSEURS3 FROM PRODUIT P4 LEFT OUTER JOIN PROPOSER PR5 ON P. numprod = PR. numprod6 GROUP BY nomprod7 ORDER BY COUNT( numfou ) ;

Vue c r e e e .

Une fois creee, on peut interroger une vue de la meme facon qu’on interroge une table :

SQL> SELECT ∗2 FROM NB FOURNISSEURS PAR PRODUIT;



21

Notez que toute modification dans la table PROPOSER ou PRODUIT sera immediatement repercutee sur la vue.

SQL> INSERT INTO PROPOSER VALUES (3 , 4 , 9 ) ;

1 l i g n e c r e e e .

SQL> SELECT ∗2 FROM NB FOURNISSEURS PAR PRODUIT;



Maintenant, nous souhaitons voir s’afficher, pour tout i, le nombre de produits proposes par exactement i fournis-seurs.

SQL> SET head o f fSQL> SELECT ’ I l y a ’ | | COUNT(NOMPROD) | | ’ p rodui t ( s ) qui e s t / sont ’ | |

2 ’ propose ( s ) par ’ | | NB FOURNISSEURS | | ’ f o u r n i s s e u r ( s ) . ’3 FROM NB FOURNISSEURS PAR PRODUIT4 GROUP BY NB FOURNISSEURS5 ORDER BY NB FOURNISSEURS;

I l y a 3 produi t ( s ) qui e s t / sont propose ( s ) par 1 f o u r n i s s e u r ( s ) .I l y a 1 produi t ( s ) qui e s t / sont propose ( s ) par 2 f o u r n i s s e u r ( s ) .


SQL> SET head on

1.6.4 Suppression

On supprime une vue avec l’instruction suivante :

DROP VIEW <nom vue>;

22

1.7 Requetes imbriquees

Oracle permet d’imbriquer les requetes, c’est-a-dire de placer des requetes dans les requetes. Une requete imbriqueepeut renvoyer trois types de resultats :

– une valeur scalaire– une colonne– une table

1.7.1 Sous requetes renvoyant une valeur scalaire

Le resultat d’une requete est dit scalaire s’il comporte une seule ligne et une seule colonne. Par exemple :

SQL> SELECT COUNT(∗ ) FROM PERSONNE;

COUNT(∗ )−−−−−−−−−−

21

On peut placer dans une requete une sous-requete calculant un resultat scalaire. Un tel type de sous-requete se placesoit comme une colonne supplementaire, soit comme une valeur servant a evaluer des conditions (WHERE ou HAVING).

Colonne fictive

On peut ajouter une colonne dans une requete, et choisir comme valeurs pour cette colonne le resultat d’unerequete. Ce type de requete est souvent une alternative a GROUP BY. Par exemple, la requete suivante nous renvoie,pour tout produit, le nombre de fournisseurs proposant ce produit :

SQL> SELECT nomprod , (SELECT COUNT(∗ )2 FROM PROPOSER PR3 WHERE PR. numprod = P. numprod)4 AS NB FOURNISSEURS5 FROM PRODUIT P;

NOMPROD NB FOURNISSEURS−−−−−−−−−−−−−−−−−−−−−−−−−−−−−− −−−−−−−−−−−−−−−Roue de s e cour s 1Poupee Batman 2Cotons t i g e s 1Cornichons 0

Conditions complexes

On peut construire une condition en utilisant le resultat d’une requete. Pour notre exemple, declarons d’abord unevue contenant le nombe d’articles proposes par chaque fournisseur,

SQL> CREATE VIEW NB PROD PAR FOU AS2 SELECT numfou , (SELECT COUNT(∗ )3 FROM PROPOSER P4 WHERE P. numfou = F. numfou )5 AS NB PROD6 FROM FOURNISSEUR F;

Vue c r e e e .

Ensuite, recherchons les noms des fournisseurs proposant le plus de produits :

SQL> SELECT nomfou2 FROM FOURNISSEUR F, NB PROD PAR FOU N3 WHERE F. numfou = N. numfou4 AND NB PROD = (SELECT MAX(NB PROD)5 FROM NB PROD PAR FOU) ;

NOMFOU

23

−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−f 1

La requete SELECT MAX(NB PROD) FROM NB PROD PAR FOU est evaluee avant, et son resultat lui est substitue dansl’expression de la requete. Comme on a

SQL> SELECT MAX(NB PROD) FROM NB PROD PAR FOU;

MAX(NB PROD)−−−−−−−−−−−−

2

Alors la requete precedente, dans ce contexte, est equivalente a

SQL> SELECT nomfou2 FROM FOURNISSEUR F, NB PROD PAR FOU N3 WHERE F. numfou = N. numfou4 AND NB PROD = 2 ;

NOMFOU−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−f 1

INSERT et UPDATE

On peut placer dans des instructions de mises a jour ou d’insertions des requetes imbriquees. Par exemple,

SQL> INSERT INTO PERSONNE ( numpers , nom, prenom )2 VALUES ( (SELECT MAX( numpers ) + 1 FROM PERSONNE) ,3 ’ Darth ’ , ’ Vador ’ ) ;

1 l i g n e c r e e e .SQL> UPDATE PERSONNE SET

2 pere = (SELECT numpers3 FROM PERSONNE4 WHERE nom = ’ Socrate ’5 AND prenom IS NULL) ,6 mere = (SELECT numpers7 FROM PERSONNE8 WHERE nom = ’ Fabian ’9 AND prenom = ’ Lara ’ )

10 WHERE numpers = (SELECT numpers11 FROM PERSONNE12 WHERE nom = ’ Darth ’13 AND prenom = ’ Vador ’ ) ;

1 l i g n e mise a jour .

1.7.2 Sous requetes renvoyant une colonne

On considere une colonne comme une liste de valeurs, on peut tester l’appartance d’un element a cette liste a l’aidede l’operateur IN. On peut s’en servir comme une alternative aux jointures, par exemple, reecrivons la requete de lasection precedente. La requete suivante nous renvoie le nombre de produits proposes par les fournisseurs proposant leplus de produits :

SQL> SELECT MAX(NB PROD) FROM NB PROD PAR FOU;

MAX(NB PROD)−−−−−−−−−−−−

2

Maintenant, recherchons les numeros des fournisseurs proposant un tel nombre de produits :

24

SQL> SELECT N. numfou2 FROM NB PROD PAR FOU N3 WHERE NB PROD = (SELECT MAX(NB PROD)4 FROM NB PROD PAR FOU) ;

NUMFOU−−−−−−−−−−

1

Notons que s’il existe plusieurs fournisseurs proposant 2 produits, cette requete renverra plusieurs lignes. C’estdonc par hasard qu’elle ne retourne qu’une ligne. Le numero du fournisseur proposant le plus de produits est donc le1. Cherchons ce fournisseur :

SQL> SELECT nomfou2 FROM FOURNISSEUR F3 WHERE F. numfou IN ( 1 ) ;

NOMFOU−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−f 1

Il suffit donc dans la requete ci-dessous de remplacer le 1 par la requete qui a retourne 1. On a finalement :

SQL> SELECT nomfou2 FROM FOURNISSEUR F3 WHERE F. numfou IN (SELECT N. numfou4 FROM NB PROD PAR FOU N5 WHERE NB PROD = (SELECT MAX(NB PROD)6 FROM NB PROD PAR FOU) ) ;

NOMFOU−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−f 1

1.7.3 Sous requetes non correlees renvoyant une table

On peut remplacer le nom d’une table dans la clause FROM par une sous-requete. Par exemple, la requete suivanterenvoie une table.

SQL> SELECT2 (SELECT COUNT(∗ )3 FROM PROPOSER PR4 WHERE PR. numfou = F. numfou5 ) AS NB PROD6 FROM FOURNISSEUR F;

NB PROD−−−−−−−−−−

2110

Cette table contient, pour chaque fournisseur, le nombre de produits proposes. Si l’on souhaite connaıtre le plusgrand nombre de produits proposes, on se sert du resultat de la requete ci-dessus comme d’une table :

SQL> SELECT MAX(NB PROD) AS MAX NB PROD2 FROM3 (SELECT4 (SELECT COUNT(∗ )5 FROM PROPOSER PR6 WHERE PR. numfou = F. numfou7 ) AS NB PROD

25

8 FROM FOURNISSEUR F9 ) ;

MAX NB PROD−−−−−−−−−−−

2

Ce type de requete est une alternative aux vues. Recuperons maintenant les noms des fournisseurs proposant leplus de produits (sans jointure et sans vue !) :

SQL> SELECT nomfou2 FROM FOURNISSEUR3 WHERE numfou IN4 (SELECT numfou5 FROM6 (SELECT numfou ,7 (SELECT COUNT(∗ )8 FROM PROPOSER PR9 WHERE PR. numfou = F. numfou

10 ) AS NB PROD11 FROM FOURNISSEUR F12 ) N13 WHERE NB PROD =14 (SELECT MAX(NB PROD)15 FROM16 (SELECT numfou ,17 (SELECT COUNT(∗ )18 FROM PROPOSER PR19 WHERE PR. numfou = F. numfou20 ) AS NB PROD21 FROM FOURNISSEUR F22 ) N23 )24 ) ;

NOMFOU−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−f 1

Vous constatez que la solution utilisant les vues est nettement plus simple.

1.7.4 Sous requetes correlees

Une sous-requete peut etre de deux types :– simple : Elle evaluee avant la requete principale– correlee : Elle est evaluee pour chaque ligne de la requete principalePar exemple, la requete suivante renvoie le nombre de produits livres pour chaque fournisseur. Elle contient une

sous-requete correlee.

SQL> SELECT numfou ,2 (SELECT SUM( qte )3 FROM DETAILLIVRAISON D4 WHERE D. numfou = F. numfou5 ) NB PROD L6 FROM FOURNISSEUR F;

NUMFOU NB PROD L−−−−−−−−−− −−−−−−−−−−

1 4523 104

26

Cette meme requete, une fois evaluee, peut server de requete non correlee si on souhaite connaıtre les noms de cesfournisseurs :

SQL> SELECT nomfou , NB PROD L2 FROM FOURNISSEUR F,3 (SELECT numfou ,4 (SELECT SUM( qte )5 FROM DETAILLIVRAISON D6 WHERE D. numfou = F. numfou7 ) NB PROD L8 FROM FOURNISSEUR F9 ) L

10 WHERE F. numfou = L . numfou ;

NOMFOU NB PROD L−−−−−−−−−−−−−−−−−−−−−−−−−−−−−− −−−−−−−−−−f 1 45f2f3 10f4

Amusons-nous : quel sont, pour chaque fournisseur, les produits qui ont ete les plus livres ?

SQL> SELECT nomfou , nomprod2 FROM FOURNISSEUR F, PRODUIT P,3 (SELECT FF. numfou , PP. numprod4 FROM FOURNISSEUR FF, PRODUIT PP5 WHERE6 (SELECT SUM( qte )7 FROM DETAILLIVRAISON L8 WHERE L . numfou = FF. numfou9 AND L . numprod = PP. numprod

10 )11 =12 (SELECT MAX(NB PROD L)13 FROM14 (SELECT numfou , SUM( qte ) AS NB PROD L15 FROM DETAILLIVRAISON L16 GROUP BY numprod , numfou17 ) Q18 WHERE Q. numfou = FF. numfou19 )20 GROUP BY numfou , numprod21 ) M22 WHERE M. numprod = P. numprod23 AND M. numfou = F. numfou ;

NOMFOU NOMPROD−−−−−−−−−−−−−−−−−−−−−−−−−−−−−− −−−−−−−−−−−−−−−−−−−−−−−−−−−−−−f 1 Roue de s e cour sf 3 Cotons t i g e s

Dans la requete precedente, quelles sous-requetes sont correlees et lesquelles ne le sont pas ?

27

1.8 Complements sur les types

1.8.1 Types numeriques

NUMBER(p, s) definit un type numerique de au plus (p − s) chiffres avant la virgule et au plus s chiffres apres lavirgule.

SQL> CREATE TABLE TOTO2 ( tutu number( 4 , 2 )3 ) ;

Table c r e e e .

SQL> INSERT INTO TOTO VALUES( 1 0 . 2 ) ;


SQL> INSERT INTO TOTO VALUES( 1 0 ) ;


SQL> INSERT INTO TOTO VALUES( . 0 1 ) ;


SQL> INSERT INTO TOTO VALUES( 2 1 . 0 1 ) ;


SQL> INSERT INTO TOTO VALUES( 2 1 . 0 ) ;


SQL> INSERT INTO TOTO VALUES( 2 1 . 0 1 2 ) ;


SQL> INSERT INTO TOTO VALUES( 3 2 1 . 0 ) ;INSERT INTO TOTO VALUES( 3 2 1 . 0 )

∗ERREUR a l a l i g n e 1 :ORA−01438: va l eur incohe r ente avec l a p r e c i s i o n ind iquee pour c e t t e co lonne

SQL> INSERT INTO TOTO VALUES( 3 2 1 ) ;INSERT INTO TOTO VALUES(321)

∗ERREUR a l a l i g n e 1 :ORA−01438: va l eur incohe r ente avec l a p r e c i s i o n ind iquee pour c e t t e co lonne

SQL> SELECT ∗2 FROM TOTO;

TUTU−−−−−−−−−−

10 ,210

,0121 ,01

28

2121 ,01


1.8.2 Types chaine de caracteres

Une petite liste de proprietes et de fonctions qui peuvent servir :– Pour concatener deux chaınes de caracteres, on utilise l’operateur ||– Il est aussi possible de comparer deux chaines de caracteres avec >, l’ordre considere est l’ordre ”dictionnaire”

(ou lexicographique).– La longueur s’obtient avec la fonction LENGTH.– On extrait une sous-chaine de caracteres de longueur l a partir de l’indice i (les indices commencent a 1) de la

chaıne s avec la fonction SUBSTR(S, i, l). Par exemple, SUBSTR(’oracle’, 3, 2) = ’ac’– UPPER convertit en majuscules, LOWER convertit en minuscules.

1.8.3 Types date

Une date en SQL est considere comme un point dans le temps. On le convertit en chaine de carateres avec lafonction to char(date, format), ou format est une chaıne de caracteres optionnelle. Par exemple,

SQL> SELECT to char ( d a t e l i ) AS DT2 FROM LIVRAISON;

DT−−−−−−−−30/10/0630/10/06SQL> SELECT to char ( d a t e l i , ’ yyyy ’ ) AS ANNEE

2 FROM LIVRAISON;

ANNE−−−−20062006SQL> SELECT to char ( d a t e l i , ’ yyyy/mm/dd ’ ) AS DT

2 FROM LIVRAISON;

DT−−−−−−−−−−2006/10/302006/10/30SQL> SELECT to char ( d a t e l i , ’yyyymmdd ’ ) AS DT

2 FROM LIVRAISON;

DT−−−−−−−−2006103020061030

On convertit une chaine de caracteres en date avec la fonction to date(date, format). Par exemple :

SQL> UPDATE LIVRAISON2 SET d a t e l i = to date ( ’ 1934 ’ | | to char ( d a t e l i , ’mmdd ’ ) , ’yyyymmdd ’ ) ;

2 l i g n e ( s ) mise ( s ) a jour .

SQL> SELECT ∗2 FROM LIVRAISON;

NUMFOU NUMLI DATELI

29

−−−−−−−−−− −−−−−−−−−− −−−−−−−−1 1 30/10/343 1 30/10/34

SQL> UPDATE LIVRAISON2 SET d a t e l i = to date ( ’ 2006 ’ | | to char ( d a t e l i , ’mmdd ’ ) , ’yyyymmdd ’ ) ;

2 l i g n e ( s ) mise ( s ) a jour .

SQL> SELECT ∗ FROM LIVRAISON;

NUMFOU NUMLI DATELI−−−−−−−−−− −−−−−−−−−− −−−−−−−−

1 1 30/10/063 1 30/10/06

1.8.4 La fonction inclassable

nvl(valeur1, valeur2) renvoie valeur1 si valeur1 est non NULL, valeur2 sinon. Par exemple,

SQL> DELETE FROM TOTO;

6 l i g n e ( s ) supprimee ( s ) .

SQL> SELECT SUM( tutu )2 FROM TOTO;

SUM(TUTU)−−−−−−−−−−

SQL> SELECT nvl (SUM( tutu ) , 0)2 FROM TOTO;

NVL(SUM(TUTU) , 0 )−−−−−−−−−−−−−−−−

0

1.8.5 Contraintes CHECK

La contrainte declarative de type permet de tester une condition portant les lignes de la table prises une par une.La syntaxe est :

ALTER TABLE nomtable ADD CONSTRAINT nomcontrainte CHECK( cond i t i on ) ;

Par exemple,

SQL> ALTER TABLE EMP ADD CONSTRAINT c k s a l a r y CHECK(SAL > 0 ) ;

Table modi f i e e .

SQL> INSERT INTO EMP (EMPNO, SAL) VALUES (15 , −1);INSERT INTO EMP (EMPNO, SAL) VALUES (15 , −1)∗ERREUR a l a l i g n e 1 :ORA−02290: v i o l a t i o n de c o n t r a i n t e s (SCOTT.CK SALARY) de v e r i f i c a t i o n

Une contrainte de type CHECK ne peut pas contenir de requetes ni de valeurs non constantes (sysdate par exemple).

30

1.9 Introduction au PL/SQL

1.9.1 PL/SQL

Le PL de PL/SQL signifie Procedural Language. Il s’agit d’une extension procedurale du SQL permettant d’effectuerdes traitements complexes sur une base de donnees. Les possibilites offertes sont les memes qu’avec des langagesimperatifs (instructions en sequence) classiques.

Ecrivez-le dans un editeur dont vous copierez le contenu dans SQL+. Un script ecrit en PL/SQL se termine obliga-toirement par un /, sinon SQL+ ne l’interprete pas. S’il contient des erreurs de compilation, il est possible d’afficher lesmessages d’erreur avec la commande SQL+ : SHOW ERRORS.

1.9.2 Blocs

Tout code ecrit dans un langage procedural est forme de blocs. Chaque bloc comprend une section de declarationde variables, et un ensemble d’instructions dans lequel les variables declarees sont visibles.

La syntaxe est

DECLARE/∗ d e c l a r a t i o n de v a r i a b l e s ∗/

BEGIN/∗ i n s t r u c t i o n s a e x e c u t e r ∗/

END;

1.9.3 Affichage

Pour afficher le contenu d’une variable, les procedures DBMS OUTPUT.PUT() et DBMS OUTPUT.PUT LINE() prennenten argument une valeur a afficher ou une variable dont la valeur est a afficher. Par defaut, les fonctions d’affichagesont desactivees. Il convient, a moins que vous ne vouliez rien voir s’afficher, de les activer avec la commande SQL+SET SERVEROUTPUT ON.

1.9.4 Variables

Une variable se declare de la sorte :

nom type [ := i n i t i a l i s a t i o n ] ;

L’initisation est optionnelle. Nous utiliserons les memes types primitifs que dans les tables. Par exemple :

SET SERVEROUTPUT ONDECLARE

c varchar2 (15) := ’ He l lo World ! ’ ;BEGIN

DBMS OUTPUT. PUT LINE( c ) ;END;/

Les affectations se font avec la syntaxe variable := valeur ;

1.9.5 Traitements conditionnels

Le IF et le CASE fonctionnent de la meme facon que dans les autres langages imperatifs :

IF /∗ c o n d i t i o n 1 ∗/ THEN/∗ i n s t r u c t i o n s 1 ∗/

ELSE/∗ i n s t r u c t i o n s 2 ∗/

END IF ;

voire

IF /∗ c o n d i t i o n 1 ∗/ THEN/∗ i n s t r u c t i o n s 1 ∗/

ELSIF /∗ c o n d i t i o n 2 ∗//∗ i n s t r u c t i o n s 2 ∗/

31

ELSE/∗ i n s t r u c t i o n s 3 ∗/

END IF ;

Les conditions sont les memes qu’en SQL. Le switch du langage C s’implemente en PL/SQL de la facon suivante :

CASE /∗ v a r i a b l e ∗/WHEN /∗ v a l e u r 1 ∗/ THEN

/∗ i n s t r u c t i o n s 1 ∗/WHEN /∗ v a l e u r 2 ∗/ THEN

/∗ i n s t r u c t i o n s 2 ∗/. . .

WHEN /∗ v a l e u r n ∗/ THEN/∗ i n s t r u c t i o n s n ∗/

ELSE/∗ i n s t r u c t i o n s par d e f a u t ∗/

END CASE;

1.9.6 Traitements repetitifs

LOOP ... END LOOP ; permet d’implementer les boucles

LOOP/∗ i n s t r u c t i o n s ∗/

END LOOP;

L’instruction EXIT WHEN permet de quitter une boucle.

LOOP/∗ i n s t r u c t i o n s ∗/EXIT WHEN /∗ c o n d i t i o n ∗/ ;

END LOOP;

La boucle FOR existe aussi en PL/SQL :

FOR /∗ v a r i a b l e ∗/ IN /∗ i n f ∗/ . . /∗ sup ∗/ LOOP/∗ i n s t r u c t i o n s ∗/

END LOOP;

Ainsi que la boucle WHILE :

WHILE /∗ c o n d i t i o n ∗/ LOOP/∗ i n s t r u c t i o n s ∗/

END LOOP;

Est-il possible, en bidouillant, d’implementer une boucle DO ... WHILE ?

32

1.10 Tableaux et structures

1.10.1 Tableaux

Creation d’un type tableau

Les types tableau doivent etre definis explicitement par une declaration de la forme

TYPE /∗ type ∗/ IS VARRAY ( /∗ t a i l l e ∗/ ) OF /∗ typeElements ∗/ ;

– type est le nom du type tableau cree par cette instruction– taille est le nombre maximal d’elements qu’il est possible de placer dans le tableau.– typeElements est le type des elements qui vont etre stockes dans le tableau, il peut s’agir de n’importe quel

type.Par exemple, creons un type tableau de nombres indice de 1 a 10, que nous appelerons numberTab

TYPE numberTab IS VARRAY (10) OF NUMBER;

Declaration d’un tableau

Dorenavant, le type d’un tableau peut etre utilise au meme titre que NUMBER ou VARCHAR2. Par exemple, declaronsun tableau appele t de type numberTab,

DECLARETYPE numberTab IS VARRAY (10) OF NUMBER;t numberTab ;

BEGIN/∗ i n s t r u c t i o n s ∗/

END;/

Allocation d’un tableau

La creation d’un type tableau met a disposition un constructeur du meme nom que le type cree. Cette fonctionreserve de l’espace memoire pour ce tableau et retourne l’adresse memoire de la zone reservee, il s’agit d’une sorte demalloc. Si, par exemple, un type tableau numtab a ete cree, la fonction numtab() retourne une tableau vide.


BEGINt := numberTab ( ) ;/∗ u t i l i s a t i o n du t a b l e a u ∗/

END;/

Une fois cette allocation faite, il devient presque possible d’utiliser le tableau...

Dimensionnement d’un tableau

Le tableau retourne par le constructeur est vide. Il convient ensuite de reserver de l’espace pour stocker les elementsqu’il va contenir. On utilise pour cela la methode EXTEND(). EXTEND s’invoque en utilisant la notation pointee. Parexemple,


BEGINt := numberTab ( ) ;t .EXTEND( 4 ) ;/∗ u t i l i s a t i o n du t a b l e a u ∗/

END;/

33

Dans cet exemple, t.EXTEND(4) ; permet par la suite d’utiliser les elements du tableau t(1), t(2), t(3) et t(4).Il n’est pas possible ”d’etendre” un tableau a une taille superieure a celle specifiee lors de la creation du type tableauassocie.

Utilisation d’un tableau

On accede, en lecture et en ecriture, au i-eme element d’une variable tabulaire nomme T avec l’instruction T(i).Les elements sont indices a partir de 1.

Effectuons, par exemple, une permutation circulaire vers la droite des elements du tableau t.

DECLARETYPE numberTab IS VARRAY (10) OF NUMBER;t numberTab ;i number ;k number ;

BEGINt := numberTab ( ) ;t .EXTEND( 1 0 ) ;FOR i IN 1 . . 1 0 LOOP

t ( i ) := i ;END LOOP;k := t ( 1 0 ) ;FOR i in REVERSE 2 . . 1 0 LOOP

t ( i ) := t ( i − 1 ) ;END LOOP;t (1 ) := k ;FOR i IN 1 . . 1 0 LOOP

DBMS OUTPUT. PUT LINE( t ( i ) ) ;END LOOP;

END;/

1.10.2 Structures

Un structure est un type regroupant plusieurs types. Une variable de type structure contient plusieurs variables,ces variables s’appellent aussi des champs.

Creation d’un type structure

On definit un type structure de la sorte :

TYPE /∗ nomType ∗/ IS RECORD(

/∗ l i s t e des champs ∗/) ;

nomType est le nom du type structure construit avec la syntaxe precedente. La liste suit la meme syntaxe que laliste des colonnes d’une table dans un CREATE TABLE. Par exemple, construisons le type point (dans IR2),

TYPE point IS RECORD(

a b s c i s s e NUMBER,ordonnee NUMBER

) ;

Notez bien que les types servant a definir un type structure peuvent etre quelconques : variables scalaires, tableaux,structures, etc.

Declaration d’une variable de type structure

point est maintenant un type, il devient donc possible de creer des variables de type point, la regle est toujours lameme pour declarer des variables en PL/SQL, par exemple

34

p point ;

permet de declarer une variable p de type point.

Utilisation d’une variable de type structure

Pour acceder a un champ d’une variable de type structure, en lecture ou en ecriture, on utilise la notation pointee :v.c est le champ appele c de la variable structure appelee v. Par exemple,

DECLARETYPE point IS RECORD(a b s c i s s e NUMBER,ordonnee NUMBER) ;p po int ;

BEGINp . a b s c i s s e := 1 ;p . ordonnee := 3 ;DBMS OUTPUT. PUT LINE( ’p . a b s c i s s e = ’ | | p . a b s c i s s e | |

’ and p . ordonnee =’ | | p . ordonnee ) ;END;/

Le script ci-dessous cree le type point, puis cree une variable t de type point, et enfin affecte aux champs abscisseet ordonnee du point p les valeurs 1 et 3.

35

1.11 Utilisation du PL/SQL

Ce cours est une introduction aux interactions possibles entre la base de donnees et les scripts PL/SQL.

1.11.1 Affectation

On place dans une variable le resultat d’une requete en utilisant le mot-cle INTO. Les instructions

SELECT champ 1 , . . . , champ n INTO v 1 , . . . , v nFROM . . .

affecte aux variables v 1, ..., v n les valeurs retournees par la requete. Par exemple

DECLAREnum NUMBER;nom VARCHAR2(30) := ’ Poupee Batman ’ ;

BEGINSELECT numprod INTO num

FROM PRODUITWHERE nomprod = nom ;

DBMS OUTPUT. PUT LINE( ’L ’ ’ a r t i c l e ’ | |nom | | ’ a pour numero ’ | | num ) ;

END;/

Pretez attention au fait que la requete doit retourner une et une une seule ligne, sinon, une erreur se produit al’execution.

1.11.2 Tables et structures

Si vous ne tenez pas a vous prendre la tete pour choisir le type de chaque variable, demandez-vous ce que vousallez mettre dedans ! Si vous tenez a y mettre une valeur qui se trouve dans une colonne d’une table, il est possible devous referer directement au type de cette colonne avec le type nomTable.nomColonne%type. Par exemple,

DECLAREnum PRODUIT. numprod%type ;nom PRODUIT. nomprod%type := ’ Poupee Batman ’ ;




END;/

Pour aller plus loin, il est meme possible de declarer une structure pour representer une ligne d’une table, le typeporte alors le nom suivant : nomTable%rowtype.

DECLAREnom PRODUIT. nomprod%type := ’ Poupee Batman ’ ;l i g n e PRODUIT%rowtype ;

BEGINSELECT ∗ INTO l i g n e


DBMS OUTPUT. PUT LINE( ’L ’ ’ a r t i c l e ’ | |l i g n e . nomprod | | ’ a pour numero ’ | | l i g n e . numprod ) ;

END;/

36

1.11.3 Transactions

Un des mecanismes les plus puissants des SGBD recents reside dans le systeme des transactions. Une transactionest un ensemble d’operations “atomiques”, c’est-a-dire indivisible. Nous considererons qu’un ensemble d’operations estindivisible si une execution partielle de ces instructions poserait des problemes d’integrite dans la base de donnees.Par exemple, dans le cas d’une base de donnees de gestion de comptes en banque, un virement d’un compte a un autrese fait en deux temps : crediter un compte d’une somme s, et debiter un autre de la meme somme s. Si une erreursurvient pendant la deuxieme operation, et que la transaction est interrompue, le virement est incomplet et le patronva vous assassiner.

Il convient donc de disposer d’un mecanisme permettant de se proteger de ce genre de desagrement. Plutot quese casser la tete a tester les erreurs a chaque etape et a balancer des instructions permettant de “revenir en arriere”,nous allons utiliser les instructions COMMIT et ROLLBACK.

Voici le squelette d’un exemple :

/∗ i n s t r u c t i o n s ∗/IF /∗ erreur ∗/ THEN

ROLLBACK;ELSE

COMMIT;END;

Le ROLLBACK annule toutes les modifications faites depuis le debut de la transaction (donc depuis le precedentCOMMIT), COMMIT les enregistre definitivement dans la base de donnees.

La variable d’environnement AUTOCOMMIT, qui peut etre positionnee a ON ou a OFF permet d’activer la gestion destransactions. Si elle est positionnee a ON, chaque instruction a des repercussions immediates dans la base, sinon, lesmodifications ne sont effectives qu’une fois qu’un COMMIT a ete execute.

37

1.12 Exceptions

Le mecanisme des exceptions est implemente dans la plupart des langages recent, notament orientes objet. Cettefacon de programmer a quelques avantages immediats :

– obliger les programmeurs a traiter les erreurs : combien de fois votre prof de C a hurle en vous suppliantde verifier les valeurs retournees par un malloc, ou un fopen ? La plupart des compilateurs des langages aexceptions (notamment java) ne compilent que si pour chaque erreur potentielle, vous avez prepare un bloc decode (eventuellement vide...) pour la traiter. Le but est de vous assurer que vous n’avez pas oublie d’erreur.

– Rattraper les erreurs en cours d’execution : Si vous programmez un systeme de securite de centralenucleaire ou un pilote automatique pour l’aviation civile, une erreur de memoire qui vous afficherait l’ecranbleu de windows, ou le message “Envoyer le rapport d’erreur ?”, ou plus simplement le fameux “Segmentationfault” produirait un effet des plus mauvais. Certaines erreurs d’execution sont rattrapables, autrement dit, il estpossible de resoudre le probleme sans interrompre le programme.

– Ecrire le traitement des erreurs a part : Pour des raisons fiabilite, de lisibilite, il a ete considere quemelanger le code “normal” et le traitement des erreurs etait un style de programmation perfectible... Dans leslangages a exception, les erreurs sont traitees a part.

1.12.1 Rattraper une exception

Je vous ai menti dans le premier cours, un bloc en PL/SQL a la forme suivante :

DECLARE/∗ d e c l a r a t i o n s ∗/


EXCEPTION/∗ t r a i t e m e n t des e r r e u r s ∗/

END;

Une exception est une “erreur type”, elle porte un nom, au meme titre qu’une variable a une identificateur, parexemple GLUBARF. Lorsque dans les instructions, l’erreur GLUBARF se produit, le code du BEGIN s’interrompt et lecode de la section EXCEPTION est lance. On dit aussi que quand une exception est levee (raised) (on dit aussi jetee(thrown)), on la rattrape (catch) dans le bloc EXCEPTION. La section EXCEPTION a la forme suivante :

EXCEPTIONWHEN E1 THEN

/∗ t r a i t e m e n t ∗/WHEN E2 THEN

/∗ t r a i t e m e n t ∗/WHEN E3 THEN

/∗ t r a i t e m e n t ∗/WHEN OTHERS THEN

/∗ t r a i t e m e n t ∗/END;

On enumere les erreurs les plus pertinentes en utilisant leur nom et en consacrant a chacune d’elle un traitementparticulier pour rattraper (ou propager) l’erreur. Quand un bloc est traite, les WHEN suivants ne sont pas evalues.OTHERS est l’exception par defaut, OTHERS est toujours verifie, sauf si un cas precedent a ete verifie. Dans l’exemplesuivant :

DECLARE/∗ d e c l a r a t i o n s ∗/

BEGIN/∗ i n s t r u c t i o n s ∗/COMMIT;

EXCEPTIONWHEN GLUBARF THEN

ROLLBACK;DBMS OUTPUT. PUT LINE( ’GLUBARF except ion r a i s e d ! ’ ) ;

WHEN OTHERS THENDBMS OUTPUT. PUT LINE( ’SQLCODE = ’ | | SQLCODE) ;DBMS OUTPUT. PUT LINE( ’SQLERRM = ’ | | SQLERRM) ;

END;

38

Les deux variables globales SQLCODE et SQLERRM contiennent respectivement le code d’erreur Oracle et un messaged’erreur correspondant a la derniere exception levee. Chaque exception a donc, en plus d’un nom, un code et unmessage.

1.12.2 Exceptions predefinies

Bon nombre d’exceptions sont predefinies par Oracle, par exemple– NO DATA FOUND est levee quand la requete d’une instruction de la forme SELECT ... INTO ... ne retourne

aucune ligne– TOO MANY ROWS est levee quand la requete d’une instruction de la forme SELECT ... INTO ... retourne plusieurs

lignes– DUP VAL ON INDEX est levee si une insertion (ou une modification) est refusee a cause d’une contrainte d’unicite.On peut enrichir notre exemple de la sorte :

DECLAREnum NUMBER;nom VARCHAR2(30) := ’ Poupee Batman ’ ;




EXCEPTIONWHEN NO DATA FOUND THEN

DBMS OUTPUT. PUT LINE( ’Aucun a r t i c l e ne porte l e nom ’| | nom ) ;

WHEN TOO MANY ROWS THENDBMS OUTPUT. PUT LINE( ’ P l u s i e u r s a r t i c l e s portent l e nom ’| | nom ) ;

WHEN OTHERS THENDBMS OUTPUT. PUT LINE( ’ I l y a un gros probleme . . . ’ ) ;

END;/

SELECT numprod INTO num... leve une exception si la requete renvoie un nombre de lignes different de 1.

1.12.3 Codes d’erreur

Je vous encore menti, certaines exceptions n’ont pas de nom. Elle ont seulement un code d’erreur, il est conseillede se reporter a la documentation pour les obtenir. On les traite de la facon suivante

EXCEPTIONWHEN OTHERS THEN

IF SQLCODE = CODE1 THEN/∗ t r a i t e m e n t ∗/

ELSIF SQLCODE = CODE2 THEN/∗ t r a i t e m e n t ∗/

ELSEDBMS OUTPUT. PUT LINE( ’ J ’ ’ v o i s pas c ’ ’ que capeut e t r e . . . ’ ) ;

END;

C’est souvent le cas lors de violation de contraintes.

1.12.4 Declarer et lancer ses propres exceptions

Exception est un type, on declare donc les exceptions dans une section DECLARE. Une exception se lance avecl’instruction RAISE. Par exemple,

DECLAREGLUBARF EXCEPTION;

39

BEGINRAISE GLUBARF;

EXCEPTIONWHEN GLUBARF THEN

DBMS OUTPUT. PUT LINE( ’ g l u b a r f r a i s e d . ’ ) ;END;/

40

1.13 Sous-programmes

1.13.1 Procedures

Syntaxe

On definit une procedure de la sorte

CREATE OR REPLACE PROCEDURE /∗ nom ∗/ ( /∗ parametres ∗/ ) IS/∗ d e c l a r a t i o n des v a r i a b l e s l o c a l e s ∗/


END;

les parametres sont une simple liste de couples nom type. Par exemple, la procedure suivante affiche un compte arebours.

CREATE OR REPLACE PROCEDURE compteARebours (n NUMBER) ISBEGIN

IF n >= 0 THENDBMS OUTPUT. PUT LINE(n ) ;compteARebours (n − 1 ) ;

END IF ;END;

Invocation

En PL/SQL, une procedure s’invoque tout simplement avec son nom. Mais sous SQL+, on doit utiliser le mot-cleCALL. Par exemple, on invoque le compte a rebours sous SQL+ avec la commande CALL compteARebours(20).

Passage de parametres

Oracle permet le passage de parametres par reference. Il existe trois types de passage de parametres :– IN : passage par valeur– OUT : aucune valeur passee, sert de valeur de retour– IN OUT : passage de parametre par referencePar defaut, le passage de parametre se fait de type IN.

CREATE OR REPLACE PROCEDURE i n c r ( va l IN OUT NUMBER) ISBEGIN

val := va l + 1 ;END;

1.13.2 Fonctions

Syntaxe

On cree une nouvelle fonction de la facon suivante :

CREATE OR REPLACE FUNCTION /∗ nom ∗/ ( /∗ parametres ∗/ ) RETURN /∗ type∗/ IS

/∗ d e c l a r a t i o n des v a r i a b l e s l o c a l e s ∗/BEGIN

/∗ i n s t r u c t i o n s ∗/END;

L’instruction RETURN sert a retourner une valeur. Par exemple,

CREATE OR REPLACE FUNCTION module ( a NUMBER, b NUMBER) RETURN NUMBER ISBEGIN

IF a < b THENRETURN a ;

ELSERETURN module ( a − b , b ) ;

41

END IF ;END;

Invocation

Tout comme les procedures, l’invocation des fonctions ne pose aucun probleme en PL/SQL, par contre, sous SQL+,c’est quelque peu particulier. On passe par une pseudo-table nommee DUAL de la facon suivante :

SELECT module (21 , 12) FROM DUAL;

Passage de parametres

Les parametres sont toujours passes avec le type IN.

42

1.14 Curseurs

1.14.1 Introduction

Les instructions de type SELECT ... INTO ... manquent de souplesse, elles ne fontionnent que sur des requetesretourant une et une seule valeur. Ne serait-il pas interessant de pouvoir placer dans des variables le resultat d’unerequete retournant plusieurs lignes ? A mediter...

1.14.2 Les curseurs

Un curseur est un objet contenant le resultat d’une requete (0, 1 ou plusieurs lignes).

declaration

Un curseur se declare dans une section DECLARE :

CURSOR /∗ nomcurseur ∗/ IS /∗ r e q u e t e ∗/ ;

Par exemple, si on tient a recuperer tous les employes de la table EMP, on declare le curseur suivant.

CURSOR emp cur ISSELECT ∗ FROM EMP;

Ouverture

Lors de l’ouverture d’un curseur, la requete du curseur est evaluee, et le curseur contient toutes les donneesretournees par la requete. On ouvre un curseur dans une section BEGIN :

OPEN /∗ nomcurseur ∗/ ;

Par exemmple,

DECLARECURSOR emp cur IS

SELECT ∗ FROM EMP;BEGIN

OPEN emp cur ;/∗ U t i l i s a t i o n du curseur ∗/

END;

Lecture d’une ligne

Une fois ouvert, le curseur contient toutes les lignes du resultat de la requete On les recupere une par une enutilisant le mot-cle FETCH :

FETCH /∗ nom curseur ∗/ INTO /∗ l i s t e v a r i a b l e s ∗/ ;

La liste de variables peut etre remplacee par une structure de type nom curseur%ROWTYPE. Si la lecture de la ligneechoue, parce qu’il n’y a plus de ligne a lire, l’attribut %NOTFOUND prend la valeur vrai.


SELECT ∗ FROM EMP;l i g n e emp cur%rowtype

BEGINOPEN emp cur ;LOOP

FETCH emp cur INTO l i g n e ;EXIT WHEN emp cur%NOTFOUND;DBMS OUTPUT. PUT LINE( l i g n e . ename ) ;

END LOOP;/∗ . . . ∗/

END;

43

Fermeture

Apres utilisation, il convient de fermer le curseur.

CLOSE /∗ nomcurseur ∗/ ;

Completons notre exemple,


SELECT ∗ FROM EMP;l i g n e emp cur%rowtype ;

BEGINOPEN emp cur ;LOOP

FETCH emp cur INTO l i g n e ;EXIT WHEN emp cur%NOTFOUND;DBMS OUTPUT. PUT LINE( l i g n e . ename ) ;

END LOOP;CLOSE emp cur ;

END;/

Le programme ci-dessus peut aussi s’ecrire



BEGINOPEN emp cur ;FETCH emp cur INTO l i g n e ;WHILE emp cur%FOUND LOOP

DBMS OUTPUT. PUT LINE( l i g n e . ename ) ;FETCH emp cur INTO l i g n e ;

END LOOP;CLOSE emp cur ;

END;

Boucle FOR

Il existe une boucle FOR se chargeant de l’ouverture, de la lecture des lignes du curseur et de sa fermeture,

FOR l i g n e IN emp cur LOOP/∗ Traitement ∗/

END LOOP;

Par exemple,



BEGINFOR l i g n e IN emp cur LOOP

DBMS OUTPUT. PUT LINE( l i g n e . ename ) ;END LOOP;

END;/

44

1.15 Curseurs parametres

1.15.1 Introduction

A votre avis, le code suivant est-il valide ?

DECLARENUMBER n := 14 ;

BEGINDECLARE

CURSOR C ISSELECT ∗FROM PERSONNEWHERE numpers >= n ;

ROW C%rowType ;BEGIN

FOR ROW IN C LOOPDBMS OUTPUT. PUT LINE(ROW. numpers ) ;

END LOOP;END;

END;/

Reponse : non. La requete d’un curseur ne peut pas contenir de variables dont les valeurs ne sont pas fixees.Pourquoi ? Parce que les valeurs des ces sont susceptibles de changer entre la declaration du curseur et son ouverture.Le remede est un curseur parametre.

1.15.2 Definition

Un curseur parametre est un curseur dont la requete contient des variables dont les valeurs ne seront fixees qu’al’ouverture.

1.15.3 Declaration

On precise la liste des noms et des type des parametres entre parentheses apres le nom du curseur :

CURSOR /∗ nom ∗/ ( /∗ l i s t e des parametres ∗/ ) IS/∗ r e q u e t e ∗/

Par exemple, creeons une requete qui, pour une personne donnee, nous donne la liste des noms et prenoms de sesenfants :

CURSOR en fant s ( numparent NUMBER) ISSELECT ∗FROM PERSONNEWHERE pere = numparentOR mere = numparent ;

1.15.4 Ouverture

On ouvre un curseur parametre en passant en parametre les valeurs des variables :

OPEN /∗ nom ∗/ ( /∗ l i s t e des parametres ∗/ )

Par exemple,

OPEN en fant s ( 1 ) ;

1.15.5 Lecture d’une ligne, fermeture

la lecture d’une ligne suit les memes regles qu’avec un curseur non parametre.

45

1.15.6 Boucle pour

La boucle pour se charge de l’ouverture, il convient donc de placer les parametre dans l’entete de la boucle,

FOR /∗ v a r i a b l e ∗/ IN /∗ nom ∗/ ( /∗ l i s t e parametres ∗/ ) LOOP/∗ i n s t r u c t i o n s ∗/

END LOOP;

Par exemple,

FOR e IN en fant s (1 ) LOOPDBMS OUTPUT. PUT LINE( e . nompers | | ’ ’ | | e . prenompers ) ;

END LOOP;

1.15.7 Exemple recapitulatif

DECLARECURSOR parent IS

SELECT ∗FROM PERSONNE;

p parent%rowtype ;CURSOR en fant s ( numparent NUMBER) IS

SELECT ∗FROM PERSONNEWHERE pere = numparentOR mere = numparent ;

e en fant s%rowtype ;BEGIN

FOR p IN parent LOOPDBMS OUTPUT. PUT LINE( ’ Les en fant s de ’ | | p . prenom | |

’ ’ | | p . nom | | ’ sont : ’ ) ;FOR e IN en fant s (p . numpers ) LOOP

DBMS OUTPUT. PUT LINE( ’ ∗ ’ | | e . prenom| | ’ ’ | | e . nom ) ;

END LOOP;END LOOP;

END;/

46

1.16 Triggers

1.16.1 Principe

Un trigger est une procedure stockee qui se lance automatiquement lorsqu’un evenement se produit. Par evenement,on entend dans ce cours toute modification des donnees se trouvant dans les tables. On s’en sert pour controler ouappliquer des contraintes qu’il est impossible de formuler de facon declarative.

1.16.2 Classification

Type d’evenement

Lors de la creation d’un trigger, il convient de preciser quel est le type d’evenement qui le declenche. Nous realiseronsdans ce cours des triggers pour les evenements suivants :

– INSERT– DELETE– UPDATE

Moment de l’execution

On precise aussi si le trigger doit etre execute avant (BEFORE) ou apres (AFTER) l’evenement.

Evenements non atomiques

Lors que l’on fait un DELETE ..., il y a une seule instruction, mais plusieurs lignes sont affectees. Le trigger doit-iletre execute pour chaque ligne affectee (FOR EACH ROW), ou seulement une fois pour toute l’instruction (STATEMENT) ?

– un FOR EACH ROW TRIGGER est execute a chaque fois qu’une ligne est affectee.– un STATEMENT TRIGGER est executee a chaque fois qu’une instruction est lancee.

1.16.3 Creation

Syntaxe

On declare un trigger avec l’instruction suivante :

CREATE OR REPLACE TRIGGER nomtrigger[BEFORE | AFTER] [INSERT | DELETE | UPDATE] ON nomtable[FOR EACH ROW | ]DECLARE

/∗ d e c l a r a t i o n s ∗/BEGIN

/∗ i n s t r u c t i o n s ∗/END;

Par exemple,

SQL> CREATE OR REPLACE TRIGGER pasDeDeleteDansClient2 BEFORE DELETE ON CLIENT3 BEGIN4 RAISE APPLICATION ERROR(−20555 , ’Va te f a i r e . . . ’ ) ;5 END;6 /

Dec lencheur c r e e .

SQL> SELECT COUNT(∗ )2 FROM CLIENT;

COUNT(∗ )−−−−−−−−−−

21

SQL> DELETE FROM CLIENT;

47

DELETE FROM CLIENT∗

ERREUR a l a l i g n e 1 :ORA−20555: Va te f a i r e . . .ORA−06512: a ”SCOTT.PASDEDELETEDANSCLIENT” , l i g n e 2ORA−04088: e r r e u r l o r s d ex e cut ion du d ec l encheur ’SCOTT.PASDEDELETEDANSCLIENT ’

SQL> SELECT COUNT(∗ )2 FROM CLIENT;

COUNT(∗ )−−−−−−−−−−

21

L’instruction RAISE APPLICATION ERROR(code, message) leve une exception sans nom portant un code code etun message d’erreur message. Vous remarquez que comme l’erreur a ete levee avant la suppression, les donnees sonttoujours presentes dans la table CLIENT. Le trigger a controle une regle, et comme elle n’etait pas respectee, il a lanceune erreur.

Combinaisons d’evenements

Il est possible, en separant les types d’evenement par le mot-cle OR, de definir un trigger declenche par plusieursevenements. Les variables booleennes INSERTING, UPDATING et DELETING permettent d’identifier l’evenement qui adeclenche le trigger.

CREATE OR REPLACE TRIGGER af f icheEvenementBEFORE INSERT OR UPDATE OR DELETE ON CLIENTFOR EACH ROWBEGIN

IF INSERTING THENDBMS OUTPUT. PUT LINE( ’ I n s e r t i o n dans CLIENT ’ ) ;

ELSIF UPDATING THENDBMS OUTPUT. PUT LINE( ’ Mise a jour dans CLIENT ’ ) ;

ELSEDBMS OUTPUT. PUT LINE( ’ Suppress ion dans CLIENT ’ ) ;

END IF ;END;

1.16.4 Acces aux lignes en cours de modification

Dans les FOR EACH ROW triggers, il est possible avant la modification de chaque ligne, de lire l’ancienne ligne et lanouvelle ligne par l’intermediaire des deux variables structurees :old et :new. Par exemple le trigger suivant empechede diminuer un salaire :

CREATE OR REPLACE TRIGGER pasDeBaisseDeSala i reBEFORE UPDATE ON EMPFOR EACH ROWBEGIN

IF ( : o ld . s a l > : new . s a l ) THENRAISE APPLICATION ERROR(−20567 ,

’ Pas de b a i s s e de s a l a i r e ! ’ ) ;END IF ;

END;

Tables en mutation

Il est impossible, dans un trigger de type FOR EACH ROW de faire un SELECT sur la table en cours de modification.

SQL> CREATE OR REPLACE TRIGGER beforeStatement2 BEFORE UPDATE ON CLIENT

48

3 DECLARE4 NB NUMBER;5 BEGIN6 SELECT COUNT(∗ ) INTO NB7 FROM CLIENT;8 END;9 /


SQL>SQL> CREATE OR REPLACE TRIGGER a f te rStatement

2 AFTER UPDATE ON CLIENT3 DECLARE4 NB NUMBER;5 BEGIN6 SELECT COUNT(∗ ) INTO NB7 FROM CLIENT;8 END;9 /


SQL>SQL> UPDATE CLIENT SET nomcli = nomcli ;

21 l i g n e ( s ) mise ( s ) a j our .

SQL>SQL> CREATE OR REPLACE TRIGGER beforeForEachRow

2 BEFORE UPDATE ON CLIENT3 FOR EACH ROW4 DECLARE5 NB NUMBER;6 BEGIN7 SELECT COUNT(∗ ) INTO NB8 FROM CLIENT;9 END;

10 /


SQL>SQL> UPDATE CLIENT SET nomcli = nomcli ;UPDATE CLIENT SET nomcli = nomcli

∗ERREUR a l a l i g n e 1 :ORA−04091: l a table SCOTT.CLIENT e s t en mutation ; l e d e c l encheur ou l af o n c t i o n ne peut l a v o i rORA−06512: a ”SCOTT.BEFOREFOREACHROW” , l i g n e 4ORA−04088: e r r e u r l o r s d ex e cut ion du d ec l encheur ’SCOTT.BEFOREFOREACHROW’

SQL> DROP TRIGGER beforeForEachRow ;

Dec lencheur supprime .

SQL>SQL>

49

SQL> CREATE OR REPLACE TRIGGER afterForEachRow2 AFTER UPDATE ON CLIENT3 FOR EACH ROW4 DECLARE5 NB NUMBER;6 BEGIN7 SELECT COUNT(∗ ) INTO NB8 FROM CLIENT;9 END;

10 /

Declencheur c r e e .

SQL>SQL> UPDATE CLIENT SET nomcli = nomcli ;UPDATE CLIENT SET nomcli = nomcli

∗ERREUR a l a l i g n e 1 :ORA−04091: l a table SCOTT.CLIENT e s t en mutation ; l e d e c l encheur ou l af o n c t i o n ne peut l a v o i rORA−06512: a ”SCOTT.AFTERFOREACHROW” , l i g n e 4ORA−04088: e r r e u r l o r s d ex e cut ion du d ec l encheur ’SCOTT.AFTERFOREACHROW’

1.16.5 Contourner le probleme des tables en mutation

Il existe plusieurs facons de contourner ce probleme :– Utiliser un STATEMENT trigger. Comme on ne sait pas quelles lignes ont ete modifiees, on est oblige de toutes

les traiter. Cette approche presente donc un inconvenient majeur : elle nous amene a effectuer de nombreuxtraitements inutiles.

– En ayant des donnees redondantes. Il suffit que les donnees servant a la verification se trouvent dans une autretable que celle en mutation. Cette methode a pour inconvenient la memoire occupee et la quantite de code a ecrirepour maintenir la coherence des donnees. Dans la plupart des cas, cette solution est malgre tout la meilleure.

Colonnes supplementaires

Par exemple, si l’on souhaite empecher un client d’avoir plus de 10 comptes en banque, une solution est de placerdans la table client une colonne contenant le nombre de comptes.

ALTER TABLE CLIENT ADD nbComptes number ;UPDATE CLIENT SET nbComptes = 0 ;

Une fois cette table cree, il convient de s’assurer que les donnees de la colonne nbComptes contient toujours lesbonnes valeurs. On le fait avec plusieurs sous-programmes :

CREATE OR REPLACE TRIGGER metAJourNbComptesAFTER INSERT OR UPDATE OR DELETE ON COMPTECLIENTBEGIN

UPDATE CLIENT SET nbComptes =(SELECT COUNT(∗ )FROM COMPTECLIENT CCWHERE CC. numCli = numCli) ;

END;/

CREATE OR REPLACE TRIGGER veri f ieNbComptesBEFORE INSERT ON COMPTECLIENTFOR EACH ROWDECLARE

nbComptes NUMBER;

50

BEGINSELECT nbComptes INTO nbComptesFROM CLIENTWHERE numCli = : new . numcli ;IF ( nbComptes >= 10) THEN

RAISE APPLICATION ERROR(−20556 ,’Ce c l i e n t a de ja trop de comptes ’ ) ;

END IF ;END;/

On peut affiner en remplacant metAJourNbComptes par plusieurs sous-programmes :

CREATE OR REPLACE TRIGGER i n i t i a l i s eNbComptesBEFORE INSERT ON CLIENTFOR EACH ROWBEGIN: new . nbComptes := 0 ;

END;/

CREATE OR REPLACE TRIGGER metAJourNbComptesAFTER INSERT OR UPDATE OR DELETE ON COMPTECLIENTFOR EACH ROWBEGIN

IF DELETING OR UPDATING THENUPDATE CLIENT SET nbComptes = nbComptes − 1WHERE numcli = : o ld . numcli ;

END IF ;IF INSERTING OR UPDATING THEN

UPDATE CLIENT SET nbComptes = nbComptes + 1WHERE numcli = : new . numcli ;

END IF ;END;/

Tables supplementaires

Si l’on souhaite par exemple empecher les circuits dans la table PERSONNE, il est necessaire de faire un parcoursde graphe. Ce qui necessite des SELECT dans la table en cours de mutation. La seule solution est dans ce cas d’avoirune table miroir qui contient les colonnes cles primaire et etrangeres de cette table, et de s’en servir pour detecter lescircuits.

CREATE TABLE MIRRORPERSONNE(numpers NUMBERPRIMARY KEY,pere NUMBER,mere NUMBER) ;

Nous allons ensuite proceder de meme, en repercutant chaque operation de PERSONNE sur MIRRORPERSONNE.

CREATE OR REPLACE TRIGGER miseAJourMirrorPersonneBEFORE UPDATE OR INSERT OR DELETE ON PERSONNEFOR EACH ROWBEGIN

IF DELETING OR UPDATING THENDELETE FROM MIRRORPERSONNEWHERE numpers = : o ld . numpers ;


INSERT INTO MIRRORPERSONNE VALUES

51

( : new . numpers , : new . pere , : new . mere ) ;END IF ;

END;/

Une fois cela fait, il suffit de rechercher si une personne inseree est une descendante d’elle meme dans MIRRORPERSONNE.

CREATE OR REPLACE FUNCTION tr ouveC i r cu i t ( cur rent NUMBER, toFind NUMBER)RETURN BOOLEAN IS

numPere NUMBER;numMere NUMBER;

BEGINIF ( cur rent IS NULL) THEN

RETURN FALSE;END IF ;SELECT pere , mere INTO numPere , numMere

FROM MIRRORPERSONNEWHERE numPers = current ;

RETURN ( numPere = toFind OR numMere = toFind ORt r ouve C i r cu i t (numPere , toFind ) ORt r ouve C i r cu i t (numMere , toFind ) ) ;

END;/

CREATE OR REPLACE TRIGGER v e r i f i e C i r c u i tAFTER UPDATE OR INSERT ON PERSONNEFOR EACH ROWBEGIN

IF ( t r o uveC i r cu i t ( : new . numPers , : new . numPers ) ) THENRAISE APPLICATION ERROR(−20557 ,’ C i r c u i t dans l ’ ’ a rbre g en e a l og ique . ’ ) ;

END IF ;END;/

52

1.17 Packages

1.17.1 Principe

Un package est un ensemble de sous-programmes et de variables forme par– Une specification : declaration de variables et de sous-programmes– Un corps : implementation des sous-programmesTout ce qui se trouve dans la specification doit se trouver dans le corps, mais la reciproque est fausse. Un package

satisfait les points suivants :– encapsulation : certains traitements sont masques, seule la specification du package est visible. Cela a pour

avantage de simplifier la tache de celui qui va utiliser le package.– modularite : il est possible de developper separement les diverses parties de l’application. le developpement

devient ainsi un assemblage de package.Ces deux aspects fournissent une souplesse certaine au niveau du developpement : il est possible de modifier le

corps d’un package sans changer sa specification, donc sans modifier le fonctionnement de l’application.

1.17.2 Specification

La syntaxe permettant de creer l’entete est la suivante :

CREATE OR REPLACE PACKAGE nompackage IS/∗

d e c l a r a t i o n s∗/END nomPackage ;/

Par exemple,

CREATE OR REPLACE PACKAGE compteur ISprocedure r e s e t ;f unc t i on nextValue re turn number ;

END compteur ;/

1.17.3 Corps

La syntaxe permettant de creer le corps est la suivante :

CREATE OR REPLACE PACKAGE BODY nompackage IS/∗

implementat ion∗/END nomPackage ;/

Par exemple,

CREATE OR REPLACE PACKAGE BODY compteur IScpt NUMBER := 0 ;

PROCEDURE r e s e t ISBEGIN

cpt := 0 ;END;

FUNCTION nextValue RETURN NUMBER ISBEGIN

cpt := cpt + 1 ;RETURN cpt − 1 ;

END;END compteur ;/

53

On peut utiliser un package depuis n’importe quel script PL/SQL :

DECLAREnb NUMBER;

BEGINFOR nb IN 4 . . 2 0 LOOP

DBMS OUTPUT. PUT LINE(COMPTEUR. nextValue ( ) ) ;END LOOP;COMPTEUR.RESET( ) ;FOR nb IN REVERSE 0 . . 1 0 LOOP

DBMS OUTPUT. PUT LINE(COMPTEUR. nextValue ( ) ) ;END LOOP;

END;/

54

Chapitre 2

Exercices


Nous nous proposons de modifier le script de creation de table de l’annexe A.1.

Exercice 1

Modifiez le script de facon a ce que les saisies des valeurs suivantes soit obligatoire :– Le prix des articles proposes– La quantite des produits livres

Exercice 2

Reperez les colonnes, couples de colonnes, voire triplets de colonnes, a choisir comme cles primaires. Modifiez lescript de facon a ce que cela se fasse.

Exercice 3

Faites de meme pour les cles etrangeres, sans oublier qu’il ne peut figurer dans une livraison que des produitsproposes par le fournisseur qui effectue cette livraison.

Exercice 4

Inserez quelques lignes dans chaque table.

Exercice 5

Reperez toutes les contraintes des questions precedentes dans user constraints, supprimez-les.

Exercice 6

Redefinissez toutes ces contraintes avec la syntaxe ALTER TABLE

55


Nous souhaitons gerer un secretatiat pedagogique avec la base de A.2.Les sorties generees par les deux premieres questions sont donnees. Apres, ca sera a vous de verifier si les resultats

de vos requetes est coherent ou non.

Exercice 1

Afficher la liste des noms des modules.

OracleC++CAlgoMerisePL/SQL OraclemySQLAlgo avance

8 ligne(s) selectionnee(s).

Exercice 2

Afficher la liste des numeros des modules prerequis pour d’autres modules.

135


Exercice 3

En utilisant le resultat de la requete precedente, et l’operateur IN, affichez les noms de ces trois modules.

Exercice 4

Augmentez les notesMin necessaires pour s’inscrire en ’Algo avance’ de deux points. Celles necessaires pour alleren ’PL/SQL Oracle’ d’un point.

Exercice 5

Affichez, par ordre de noteMin croissantes, les numeros des modules necessaires pour acceder au module ’PL/SQLOracle’.

Exercice 6

Affichez les numeros des modules dans lequels je ne peux pas m’inscrire avec 10 en merise.

Exercice 7

Affichez les noms de tous les modules dont le libelle contient les mots ’Algo’ ou ’SQL’.

Exercice 8

On utilisera pour les questions suivantes les tables de A.3.

56

Exercice 9

La table RECTANGLE contient des rectangles specifies par les coordonnees de deux sommets diametralement opposes,leurs aretes sont paralleles aux axes. Certains rectangles ont des coordonnees erronnees, c’est-a dire que soit ils sont dessegments, soit les coordonnees de certains points ne sont pas dans le bon ordre. Supprimez, avec une seule instruction,tous les rectangles-segments.

Exercice 10

Affichez tous les intervalles contenant la valeur 10.

Exercice 11

Afficher tous les intervalles qui contiennent [5, 7]. Puis tous les intervalles contenus dans [5, 35]. Puis tous lesintervalles ayant une intersection non vide avec [15, 20].

Exercice 12

Certains des rectangles sont pathologiques, dans le sens ou les valeurs des coordonnees font que l’on n’a pas unpoint en haut a gauche et un en bas a droite. Afficher a ces rectangles.

Exercice 13

Certains rectangles mal definis peuvent etre repares si on permute les valeurs de xHautGauche et de xBasDroitet/ou celles de yHautGauche et de yBasDroit. Faites-le avec deux instructions UPDATE.

Exercice 14

Soit le point de coordonnees (x, y) = (2, 2), afficher les coordonnees des rectangles qui contiennent ce point.

Exercice 15

Afficher tous les rectangles ayant une intersection non vide avec le rectangle (4, 9, 5, 10).

57

2.3 Jointures

Reprenons pour ce tp la base de donees de A.4.

Exercice 1

Afficher tous les noms des produits dont le numero a une occurence dans la table PROPOSER.

NOMPROD------------------------------Cotons tigesPoupee BatmanRoue de secours

Exercice 2

Afficher tous les noms des fournisseurs dont le numero a une occurence dans la table PROPOSER.

NOMFOU------------------------------f1f2f3

Exercice 3

Afficher les noms des fournisseurs avec pour chaque fournisseur la liste des produits proposes.

NOMFOU NOMPROD------------------------------ ------------------------------f1 Roue de secoursf1 Poupee Batmanf2 Poupee Batmanf3 Cotons tiges

Exercice 4

Afficher les nom des fournisseurs proposant des ’Poupees Batman’ par ordre de prix croissant.

Exercice 5

Afficher les dates des livraisons effectuees par le fournisseur ’f1’ ;

Exercice 6

Afficher les noms de tous les produits deja livres par le fournisseur ’f3’ ;

Exercice 7

Afficher toutes les lignes de la table LIVRAISON correspondant a des livraisons dans lesquelles figure le produit’Poupee Batman’.

Exercice 8

Pour les exercices suivants, nous travaillerons sur les donnees de A.5. Dans les questions ou il vous est demande deformuler des requetes retournant plusieurs personnes, il faut qu’il y ait une ligne par personne. Afficher les noms etprenoms des enfants de Sofia Kobalevskaıa.

NOM PRENOM------------------------------ ------------------------------Leibniz Gottfried WilhemBach Johann Sebastien

58

Exercice 9

Afficher les noms et prenoms des parents de Edvard Dijkstra.

NOM PRENOM------------------------------ ------------------------------Jacqou Le CroquantGranger Hermione

Exercice 10

Afficher les noms et prenoms des enfants de jean-Sebastien Bach et Mireille Mathieu.

Exercice 11

Afficher les noms et prenoms du frere de Jacqouille la Fripouille.

Exercice 12

Afficher les noms et prenoms du cousin germain de Dijkstra du cote de son pere.

Exercice 13

Afficher les noms et prenoms du demi-frere (du cote de sa mere) du pere de Lara Fabian.

Exercice 14

Afficher les noms et prenoms des peres des enfants de Kate Bush.

Exercice 15

Afficher les noms et prenoms du neveu de Jacqou le Croquant.

Exercice 16

Afficher les noms et prenoms de la mere du fils du fils d’Andre Rieu.

Exercice 17

Afficher les noms et prenoms des parents et grand-parents de Sharon Stone.

NOM PRENOM------------------------------ ------------------------------Bartoli JenniferGranger HermioneLa Fripouille JacqouilleLeibniz Gottfrie1d WilhemLeroy NolwenSocrate


N’oubliez pas : une ligne par personne.

59


Nous utiliserons les donnees de A.4. Il est demande dans chaque exercice de formuler une requete.

Exercice 1

Donner le nombre de fournisseurs.

Exercice 2

Donner le nombre de fournisseurs ayant deja effectue une livraison.

Exercice 3

Quel est le prix du produit propose au prix le plus eleve par ’f1’ ?

Exercice 4

Combien de produits sont proposes pour chaque fournisseur proposant au moins un produit ?

Exercice 5

Afficher le nombre de produits qui ne sont proposes par aucun fournisseur.

Exercice 6

Afficher, pour chaque produit (dont on affichera le nom), le nombre de fournisseurs l’ayant deja livre.

Exercice 7

Donner pour chaque livraison le nom du fournisseur, le numero de livraison et le nombre de produits livres.

Exercice 8

Donner pour chaque livraison le nom du fournisseur, le numero de livraison, la date et le montant de la facture.

Exercice 9

Donner les noms des produits qui ne sont proposes que par un seul fournisseur.

Exercice 10

Donner les noms des fournisseurs qui ont livre au moins une fois chaque produit qu’ils proposent.

60

2.5 Vues

Nous utiliserons les donnees de A.4. N’hesitez pas, pour tester vos requetes et a inserer d’autres donnees dans labase.

Exercice 1

Creez une vue affichant pour chaque produit ayant deja ete livre le numero du produit et la somme des quantiteslivrees (toutes livraisons confondues).

Exercice 2

Affichez la quantite totale livree du produit dont la quantite totale livree est la plus elevee.

Exercice 3

Creez une vue affichant pour chaque livraison, le montant de la facture.

Exercice 4

Creez une vue affichant pour chaque fournisseur, le total des factures sur toutes les livraisons.

Exercice 5

Affichez le total des factures du fournisseurs dont le total des factures est le moins eleve.

Exercice 6

Affichez le nombre de produits distincts livres par le fournisseur ayant livre le plus de produits distincts. Par nombrede produits distincts, on entend sans tenir compte de la quantite.

61


Nous utiliserons les donnees de A.4. Presque tous les stratagemes sont autorises, vous pouvez utiliser des fonctionsd’agregation, des vues, et des requetes imbriquees. Bon courage, l’aspirine n’est pas fournie.

Exercice 1

Donner, pour chaque fournisseur (afficher son nom), le nombre de produits proposes, meme si ce fournisseur n’enpropose aucun. Il est interdit d’utiliser OUTER JOIN !

Exercice 2

Afficher les noms des fournisseurs qui proposent le produit numero 2, il est interdit de faire des jointures !

Exercice 3

Afficher les noms des fournisseurs qui proposent des poupees Batman.

Exercice 4

Afficher les noms des fournisseurs qui ont deja livre des poupees Batman.

Exercice 5

Quels sont les noms des fournisseurs qui ont deja livre tous leurs produits au moins une fois ?

Exercice 6

Donner, pour chaque fournisseur (afficher son nom), le produit propose au prix le plus eleve.

Exercice 7

Pour chaque produit p, quel sont les noms des fournisseurs qui, sur toutes ses livraisons, ont livre la plus grandequantite cumulee de produits p.

Exercice 8

¡Afficher le nombre de produits proposes par les fournisseurs proposant le moins de produits. Normalement, un 0devrait s’afficher... Pas un 1.

Exercice 9

Afficher le(s) nom(s) du(des) fournisseur(s) proposant le moins de produits.

Exercice 10

Afficher, pour chaque produit, le(s) nom(s) du(des) fournisseur(s) qui l’a(ont) le plus livre (en quantite cumulee).

62


Vous modifierez pour faire ces exercices le script de creation de tables de A.4.

Exercice 1

Rendez, a l’aide de contraintes de type CHECK les saisies des champs qte, prix et dateli oligatoires.

Exercice 2

Empechez la saisie de prix negatifs ou nuls et de quantites negatives ou nulles.

Exercice 3

On sait que la base a ete creee vers le mois d’Octobre 2006, empechez l’ajout de livraisons anterieures a ce mois.

Exercice 4

Implementez une contrainte declarative empechant les livraisons les premiers mai de chaque annee.

Exercice 5

Implementez une contrainte declarative obligeant les noms des produits a commencer par une majuscule et a necomporter ensuite sur des minuscules. Si l’ajout d’une des contraintes est refusee, demandez vous pourquoi et faites lenecessaire (une commande UPDATE sera la bienvenue...) pour y remedier.

63

2.8 Revisions

Nous utiliserons les donnees de A.4.

Exercice 1

Donner le nombre de fournisseurs ayant effectue un nombre de livraisons superieur au egal a deux.

Exercice 2

Quelles sont les annees pendant lesquelles le plus de livraisons ont ete effectuees ?

Exercice 3

Parmi les fournisseurs qui ont livre au moins une fois chaque produit qu’ils proposent, quels sont les derniers aavoir effectue une livraison.

64


Exercice 1

Ecrivez un programme affectant les valeurs 1 et 2 a deux variables a et b, puis permutant les valeurs de ces deuxvariables.

Exercice 2

Ecrivez un programme placant la valeur 10 dans une variable a, puis affichant la factorielle de a.

Exercice 3

Ecrivez un programme placant les valeurs 48 et 84 dans deux variables a et b puis affichant le pgcd de a et b.

65

2.10 Tableaux et Structures

Exercice 1

1. Creez un type tableau pouvant contenir jusqu’a 50 entiers.

2. Creez une variable de ce type , faites une allocation dynamique et dimensionnez ce tableau a 20 emplacements.

3. Placez dans ce tableau la liste des 20 premiers carres parfaits : 1, 4, 9, 16, 25, . . .

4. Inversez l’ordre des elements du tableau

5. Affichez le tableau.

Exercice 2

Triez le tableau precedent avec la methode du tri a bulle.

Exercice 3

Recherchez, par dichotomie, si l’element 225 se trouve dans le tableau.

Exercice 4

On implemente des listes chaınees avec des tableaux de la sorte,

SET SERVEROUTPUT ONDECLARE-- Maillon d’une liste chaıneeTYPE CELL IS RECORD(-- Donnee de chaque maillondata INTEGER,-- Indice du maillon precedent de la liste,

-- -1 s’il n’y en a pasprevious INTEGER,-- Indice du maillon suivant de la liste,

-- -1 s’il n’y en a pasnext INTEGER);-- Type tableau contenant les maillons de la listeTYPE TREE IS VARRAY (19) OF CELL;-- Tableau contenant les maillons de la listet TREE;-- indice du premier element de la listefirst integer;-- indice du dernier element de la listelast integer;BEGINt := TREE();t.extend(19);

-- InitialisationFOR i IN 1..19 LOOPt(i).data := power(i, 5) mod 19 ;t(i).previous := i-1;t(i).next := i+1;END LOOP;first := 1;last := 19;t(first).previous := -1;t(last).next := -1;

66

-- AffichageDECLAREp integer := first;BEGINWHILE p <> -1 LOOPDBMS_OUTPUT.PUT_LINE(’(’ || p || ’, ’ ||

t(p).data || ’, ’ ||t(p).previous || ’, ’ || t(p).next || ’)’);p := t(p).next;END LOOP;END;

/* Ecrivez la suite vous-meme... */END;/

Inversez l’ordre des elements de la liste, sans changer les indices des maillons (seulement en modifiant le chaınage).

Exercice 5

Utilisez le tri a bulle pour remettre les elements dans l’ordre. Les indications sont les memes : ne deplacez pas lesmaillons, vous n’avez le droit de toucher qu’au chaınage. Bon courage, l’aspirine n’est pas fournie.

67

2.11 Utilisation PL/SQL

Nous travaillerons sur les donnees A.6 et A.5.Vous n’oublierez pas de placer des commit en des lieux bien choisis.

Exercice 1

Vous remarquerez que les valeurs des numpers de la table PERSONNE forment une sequence de nombres de 1 a 21.Utilisez une boucle dans laquelle vous placerez une requete pour recopier les couples nom/prenom de la table personnedans la table CLIENT.

Exercice 2

Ecrivez un script recuperant le client de cle primaire la plus elevee, et injectant ce client dans la table PERSONNEL.

Exercice 3

Ouvrez un compte courant pour chaque personne, effectuez un depot en espece egal a numpers ∗ 100 euros.

Exercice 4

Ouvrez un livret pour chaque personne ayant un numpers pair, faites un virement de leur compte courant vers celivret de sorte qu’il ne reste plus que 500 sur leur compte.

68

2.12 Exceptions

Nous utiliserons les donnees de A.7 et A.5Vous etes invites a modifier le code de la seance precedente. Chaque fois qu’un SELECT ... INTO ... sera effectue,

vous rattraperez les exceptions NO DATA FOUND et TOO MANY ROWS. A chaque insertion, vous ratrapperez l’exceptionDUP VAL ON INDEX.

Exercice 1

Faites de sorte que les scripts important les donnees des tables CLIENT ne puissent etre executes qu’une seule fois.

Exercice 2

Les scripts remplissant la table Operation ne fonctionneront pas aujourd’hui... Meme s’il fonctionnaient la dernierefois. Trouvez les codes d’erreurs des exceptions levees par ces scripts, rattrapez-les de la facon la plus appropriee quisoit.

69


Exercice 1

Ecrire une fonction recursive retournant bn, avec n entier positif ou nul.

Exercice 2

Ameliorer la fonction precedente en utilisant le fait que

bn = (b2)n2

si n est pair.

Pour les questions suivantes, utilisez les donnees de A.5.

Exercice 3

Ecrire une fonction demi-freres prenant deux numeros de personnes en parametre et retournant vrai si et seulementsi ces deux personnes ont un parent en commun.

Exercice 4

Ecrire une fonction cousins germains prenant deux numeros de personnes en parametre et retournant vrai si etseulement si ces deux deux individus sont cousins germains.

Exercice 5

Ecrire une procedure recursive affichant le nom de la personne dont le numero est passe en parametre et se rappellantrecursivement sur le pere de cette personne. Faites de sorte a ne pas utiliser d’exceptions.

Exercice 6

Ecrire une procedure recursive affichant les noms des ascendants de sexe masculin de la personne dont le numeroest passe en parametre.

Exercice 7

Ecrire une fonction recursive prenant deux numeros de personne A et B et retournant vrai si A est un ascendantde B.

Exercice 8

Ecrire une fonction prenant en parametre deux numeros de personne A et B et retournant, si l’un est un ascendantde l’autre, le nombre de generations les separant, −1 si l’un n’est pas un ascendant de l’autre.

Exercice 9

Preparez un verre d’aspirine et ecrivez une requete retournant le(s) couples(s) personnes separees par le plus degenerations.

Exercice 10

Reprendre le code du tp precedent, le decouper en sous-programmes de la facon la moins inintelligente possible.Bon courage.

70

2.14 Curseurs

Exercice 1

Refaites les exercices de 2.11 en utilisant les curseurs.

Exercice 2

En utlisant les donnees A.5, ecrivez une fonction affichant toute la descendance d’une personne.

71


L’interet de ces exercices etant de vous familiariser avec les curseurs parametres, vous ferez en sorte de ne pascontourner leur usage. Nous utiliserons les donnees de A.6

Exercice 1

Ecrire une procedure qui affiche tous les clients, et pour chaque client, la liste des comptes.

Exercice 2

Ecrire une procedure qui affiche tous les clients, et pour chaque client, la liste des comptes, et pour chacun de cescomptes, l’historique des operations.

72

2.16 Triggers

Implementez les contraintes suivantes dans les donnees de les donnees de A.8. Vous ferez des sous-programmestenant sur une page, et ne contenant pas plus de trois niveaux d’imbrication. Vous repertorierez les numeros d’erreursque vous affecterez a chaque levee d’exception.

1. Il ne doit pas etre possible de modifier la note min dans la table prerequis.

2. Dans un module, il ne doit pas y avoir plus de effecMax eleves inscrits.

3. On ne peut creer un examen pour un module que s’il y a des eleves inscrits dans ce module.

4. Un eleve ne peut passer un examen que si sa date d’inscription est anterieure a la date de l’examen.

5. Il ne doit pas y avoir de circuit dans la table prerequis (il existe une facon de la verifier en PL/SQL, maiscomme vous ne la connaissez pas, faites un parcours en profondeur du graphe des pre-requis)

6. Un eleve s’inscrivant a un module doit avoir eu au moins la note min a tous les modules pre-requis.

7. Ajouter dans etudiant un champ moyenne, celui-ci contiendra la moyenne de chaque etudiant s’il a passe lesexamens de tous les modules dans lesquels il est inscrit.

8. Revenez sur la premiere contrainte : il ne doit etre possible de modifier une note min dans la table prerequis ques’il n’existe pas d’eleve dont une inscription serait invalidee.

9. Il ne doit etre possible de modifier effecMax que si des etudiants ne se retrouvent pas avec une inscriptioninvalidee.

Libre a vous par la suite de trouver d’autres contraintes et de les implementer.

73

2.17 Packages

Exercice 1

Lancez deux sessions simultanement sur le meme serveur et invoquez les sous-programmes du package compteurdepuis chacune des sessions. Que remarquez-vous ?

Exercice 2

Implementer le corps du package suivant (utilisez les donnees de A.5).CREATE OR REPLACE PACKAGE g e s t i o n a r b r e IS

c i r c u i t exception ;

cu r so r f e u i l l e s re turn personne%rowtype ;

procedure ajoutePersonne (nom personne .nom%type ,prenom personne . prenom%type , pere personne . pere%type ,mere personne . mere%type ) ;

procedure modi f i eParents ( pers personne . numpers%type ,numPere personne . pere%type , numMere personne . mere%type ) ;

END g e s t i o n a r b r e ;/

74

2.18 Revisions

Implementez les contraintes suivantes dans les donnees de A.9.

1. Les parents d’une meme personne sont des personnes differentes.

2. L’arbre genealogique ne contient pas de circuit.

3. Les dates de divorce sont ulterieures aux dates de mariage.

4. Une meme personne ne peut pas etre mariee a plusieurs personnes simultanement.

5. Personne ne peut etre pere d’une personne et mere d’une autre.

6. Un mari ne peut pas etre mere et une femme ne peut pas etre pere.

7. Deux personnes ayant du sang en commun ne peuvent se marier.

75

Chapitre 3

Corriges


−− ques t ions 1 , 2 e t 3

DROP TABLE DETAILLIVRAISON;DROP TABLE LIVRAISON;DROP TABLE PROPOSER;DROP TABLE PRODUIT;DROP TABLE FOURNISSEUR;

CREATE TABLE PRODUIT(numprod number PRIMARY KEY,nomprod varchar2 ( 3 0 ) ) ;

CREATE TABLE FOURNISSEUR( numfou number PRIMARY KEY,nomfou varchar2 ( 3 0 ) ) ;

CREATE TABLE PROPOSER( numfou number ,numprod number ,p r ix number NOT NULL,PRIMARY KEY ( numfou , numprod ) ,FOREIGN KEY ( numfou ) REFERENCES f o u r n i s s e u r ( numfou ) ,FOREIGN KEY (numprod) REFERENCES produi t (numprod ) ) ;

CREATE TABLE LIVRAISON( numfou number ,numli number ,d a t e l i date default sysdate ,PRIMARY KEY ( numfou , numli ) ,FOREIGN KEY ( numfou ) REFERENCES f o u r n i s s e u r ( numfou ) ) ;

CREATE TABLE DETAILLIVRAISON( numfou number ,numli number ,numprod number ,qte number NOT NULL,PRIMARY KEY ( numfou , numli , numprod ) ,FOREIGN KEY ( numfou , numli ) REFERENCES l i v r a i s o n ( numfou , numli ) ,FOREIGN KEY ( numfou , numprod) REFERENCES proposer (numfou , numprod ) ) ;

−− quest ion 4

INSERT INTO PRODUIT values (1 , ’Roue de s e cour s ’ ) ;INSERT INTO PRODUIT values (2 , ’ Poupee Batman ’ ) ;INSERT INTO PRODUIT values (3 , ’ Cotons t i g e s ’ ) ;INSERT INTO PRODUIT values (4 , ’ Cornichons ’ ) ;

INSERT INTO FOURNISSEUR values (1 , ’ f 1 ’ ) ;INSERT INTO FOURNISSEUR values (2 , ’ f 2 ’ ) ;INSERT INTO FOURNISSEUR values (3 , ’ f 3 ’ ) ;INSERT INTO FOURNISSEUR values (4 , ’ f 4 ’ ) ;

INSERT INTO PROPOSER values (1 , 1 , 200 ) ;INSERT INTO PROPOSER values (1 , 2 , 1 5 ) ;INSERT INTO PROPOSER values (2 , 2 , 1 ) ;INSERT INTO PROPOSER values (3 , 3 , 2 ) ;

INSERT INTO LIVRAISON (numfou , numli ) values (1 , 1 ) ;INSERT INTO LIVRAISON (numfou , numli ) values (1 , 2 ) ;INSERT INTO LIVRAISON (numfou , numli ) values (3 , 1 ) ;

INSERT INTO DETAILLIVRAISON values (3 , 1 , 3 , 1 0 ) ;INSERT INTO DETAILLIVRAISON values (1 , 1 , 1 , 2 5 ) ;

76

INSERT INTO DETAILLIVRAISON values (1 , 1 , 2 , 2 0 ) ;INSERT INTO DETAILLIVRAISON values (1 , 2 , 1 , 1 5 ) ;INSERT INTO DETAILLIVRAISON values (1 , 2 , 2 , 1 7 ) ;

−− quest ion 5

−− Le s c r i p t ci−dessous va vous a f f i c h e r la s o l u t i o n .−− Vous pouvez proceder de deux facons :−− ∗ copier−c o l l e r c e t t e s o l u t i o n a f f i c h e e−− par c e t t e s e r i e de commandes

set und o f fset heading o f fset f e ed o f fselect ’ a l t e r t ab l e ’ | | table name | | ’ drop c o n s t r a i n t ’ | |

constra int name | | ’ ; ’ from u s e r c o n s t r a i n t swhere table name in

( ’PRODUIT ’ , ’FOURNISSEUR ’ , ’PROPOSER’ ,’LIVRAISON ’ , ’DETAILLIVRAISON ’ )

AND c o n s t r a i n t t y p e IN ( ’R ’ , ’P ’ )ORDER BY c o n s t r a i n t t y p e DESC ;set und onset heading onset f e ed on

−− ∗ p lacer cec i dans l e f i c h i e r dp . s q l−− e t l ’ ex ecuter en s a i s i s s a n t @<cheminabsolu>/dp . s q l

set tr imout o f f ;Set f e ed o f f ;set echo o f f ;set heading o f f ;set termout o f f ;set v e r i f y o f f ;set space 0 ;SET NEWPAGE 0 ;SET PAGESIZE 0 ;spoo l d r o p c o n s t r a i n t s . l s tselect ’ a l t e r t ab l e ’ | | table name | | ’ drop c o n s t r a i n t ’ | |

constra int name | | ’ ; ’ from u s e r c o n s t r a i n t swhere table name in

( ’PRODUIT ’ , ’FOURNISSEUR ’ ,’PROPOSER’ , ’LIVRAISON ’ , ’DETAILLIVRAISON ’ )

AND c o n s t r a i n t t y p e IN ( ’R ’ , ’P ’ )ORDER BY c o n s t r a i n t t y p e DESC ;spoo l o f f@drop const ra int s . l s tset tr imout on ;Set f e ed on ;set echo on ;set heading on ;set termout on ;set v e r i f y on ;

−− quest ion 6

alter table produi t addconstraint pk produi tPRIMARY KEY (numprod ) ;

alter table f o u r n i s s e u r addconstraint p k f o u r n i s s e u rPRIMARY KEY ( numfou ) ;

alter table proposer addconstraint pk proposerPRIMARY KEY ( numfou , numprod ) ;

alter table l i v r a i s o n addconstraint p k l i v r a i s o nPRIMARY KEY ( numfou , numli ) ;

alter table d e t a i l l i v r a i s o n addconstraint p k d e t a i l l i v r a i s o nPRIMARY KEY ( numfou , numli , numprod ) ;

alter table proposer addconstraint f k p r o p o s e r f o u r n i s s e u rFOREIGN KEY ( numfou )REFERENCES f o u r n i s s e u r ( numfou ) ;

alter table proposer add constraint f k p r o p o s e r p r o d u i tFOREIGN KEY (numprod)REFERENCES produi t (numprod ) ;

alter table l i v r a i s o n add constraint f k l i v r a i s o nFOREIGN KEY ( numfou )REFERENCES f o u r n i s s e u r ( numfou ) ;

alter table d e t a i l l i v r a i s o n add constraint f k d e t a i l l i v r a i s o nFOREIGN KEY ( numfou , numli )REFERENCES l i v r a i s o n ( numfou , numli ) ;

alter table d e t a i l l i v r a i s o n add constraint f k d e t a i l l i v r a i s o n p r o p o s e rFOREIGN KEY ( numfou , numprod)REFERENCES proposer (numfou , numprod ) ;

77


−− Exercice 1

SELECT nomModFROM MODULE;

−− Exercice 2

SELECT DISTINCT numModPrereqFROM PREREQUIS

−− Exercice 3

SELECT nomModFROM MODULEWHERE numMod IN (1 , 3 , 5 ) ;

−− Exercice 4

SQL> SELECT ∗ FROM MODULE WHERE nomMod = ’ Algo avancee ’ ;

NUMMOD NOMMOD−−−−−−−−−− −−−−−−−−−−−−−−−−−−−−−−−−−−−−−−

8 Algo avancee


SQL> UPDATE pr e r equ i s SET noteMin = 12 WHERE numMod = 8 ;SQL> SELECT ∗ FROM module WHERE nomMod = ’PL/SQL Oracle ’ ;

NUMMOD NOMMOD−−−−−−−−−− −−−−−−−−−−−−−−−−−−−−−−−−−−−−−−

6 PL/SQL Oracle

1 l i g n e s e l e c t i o n n e e .SQL> UPDATE pr e r equ i s SET noteMin = 11 WHERE numMod = 6 ;

−− Exercice 5

SELECT numModPrereq , noteMinFROM PREREQUISWHERE numMod = 6ORDER BY noteMin ;

−− Exercice 6

SELECT numModFROM pr e r equ i sWHERE numModPrereq = 5AND noteMin > 10 ;

−− Exercice 7

SELECT nomModFROM moduleWHERE nomMod LIKE ’%Algo%’

OR nomMod LIKE ’%SQL%’ ;

−− Exercice 8

DELETE FROM i n t e r v a l l eWHERE borneSup < borneIn f ;

−− Exercice 9

DELETE FROM r e c t a n g l eWHERE xHautGauche = xBasDroit

OR yHautGauche = yBasDroit ;

−− Exercice 10

SELECT ∗FROM i n t e r v a l l eWHERE 10 BETWEEN borneIn f AND borneSup ;

−− Exercice 11

SELECT ∗FROM i n t e r v a l l eWHERE borneIn f <= 5 AND borneSup >= 7 ;

SELECT ∗FROM i n t e r v a l l eWHERE borneIn f >= 5 AND borneSup <= 35 ;

SELECT ∗

78

FROM i n t e r v a l l eWHERE (15 BETWEEN borneIn f AND borneSup )

OR (20 BETWEEN borneIn f AND borneSup )OR ( borne In f BETWEEN 15 AND 2 0 ) ;

−− Exercice 12

SELECT ∗FROM r e c t a n g l eWHERE ( xHautGauche > xBasDroit )

OR ( yHautGauche < yBasDroit ) ;

−− Exercice 13

UPDATE r e c t a n g l e SETxHautGauche = xBasDroit ,xBasDroit = xHautGauche

WHERE xHautGauche > xBasDroit ;

UPDATE r e c t a n g l e SETyHautGauche = yBasDroit ,yBasDroit = yHautGauche

WHERE yHautGauche < yBasDroit ;

−− Exercice 14

SELECT ∗FROM r e c t a n g l eWHERE (2 BETWEEN xHautGauche AND xBasDroit )

AND (2 BETWEEN yBasDroit AND yHautGauche ) ;

79

3.3 Jointures

−− Exercice 1

SELECT dist inct nomprodFROM produit , proposerWHERE produi t . numprod = proposer . numprod ;

−− Exercice 2

SELECT dist inct nomfouFROM f o u r n i s s e u r f , proposer pWHERE f . numfou = p . numfou ;

−− Exercice 3

SELECT nomfou , nomprodFROM f o u r n i s s e u r f , produi t p , proposer prWHERE f . numfou = pr . numfouAND pr . numprod = p . numprod ;

−− Exercice 4

SELECT nomfou , pr ixFROM f o u r n i s s e u r f , produi t p , proposer prWHERE f . numfou = pr . numfouAND pr . numprod = p . numprodAND nomProd = ’ Poupee Batman ’ORDER BY pr ix ;

−− Exercice 5

SELECT d a t e l iFROM l i v r a i s o n l , f o u r n i s s e u r fWHERE l . numfou = f . numfouAND f . nomFou = ’ f1 ’ ;

−− Exercice 6

SELECT nomprodFROM f o u r n i s s e u r f , produi t p , d e t a i l l i v r a i s o n d , l i v r a i s o n lWHERE nomfou = ’ f3 ’AND f . numfou = l . numfouAND l . numfou = d . numfouAND l . numli = d . numliAND d . numprod = p . numprodAND d a t e l i < sysdate ;

−− Exercice 7

SELECT l . numfou , l . numli , d a t e l iFROM produi t p , l i v r a i s o n l , d e t a i l l i v r a i s o n dWHERE p . numprod = d . numprodAND l . numfou = d . numfouAND l . numli = d . numliAND p . nomprod = ’ Poupee Batman ’ ;

−− Exercice 8

SELECT en f . nom, en f . prenomFROM personne s f , personne enfWHERE en f . mere = s f . numpersAND s f . prenom = ’ S o f i a ’ ;

−− Exercice 9

SELECT par . nom, par . prenomFROM personne ed , personne parWHERE ( ed . pere = par . numpers OR ed . mere = par . numpers )AND ed .nom = ’ D i j k s t r a ’ ;

−− Exercice 10

SELECT e . nom, e . prenomFROM personne e , personne p , personne mWHERE e . pere = p . numpersAND e . mere = m. numpersAND p .nom = ’ Bach ’AND m.nom = ’ Mathieu ’ ;

−− Exercice 11

SELECT f . nom, f . prenomFROM personne f , personne jWHERE f . pere = j . pereAND f . mere = j . mereAND j . nom = ’La F r i p o u i l l e ’AND j . nom <> f . nom;

80

−− Exercice 12

SELECT c . nom, c . prenomFROM personne c , personne o , personne p , personne dWHERE d .nom = ’ D i j k s t r a ’AND d . pere = p . numpersAND p . pere = o . pereAND p . mere = o . mereAND p . numpers <> o . numpersAND c . pere = o . numpers ;

−− Exercice 13

SELECT dm. nom, dm. prenomFROM personne dm, personne l f , personne pWHERE l f . nom = ’ Fabian ’AND p . numpers = l f . pereAND p . mere = dm. mereAND p . numpers <> dm. numpers ;

−− Exercice 14

SELECT p . nom, p . prenomFROM personne p , personne kb , personne enfWHERE kb .nom = ’ Bush ’AND en f . mere = kb . numpersAND en f . pere = p . numpers ;

−− Exercice 15

SELECT nev . nom, nev . prenomFROM personne nev , personne j l c , personne f rWHERE j l c . prenom = ’ Jacqou ’AND f r . pere = j l c . pereAND f r . mere = j l c . mereAND f r . numpers <> j l c . numpersANd ( nev . pere = f r . numpers OR nev . mere = f r . numpers ) ;

−− Exercice 16

SELECT m. nom, m. prenomFROM personne ar , personne f , personne mWHERE f . pere = ar . numpersAND ar . nom = ’ Rieu ’AND f . mere = m. numpers ;

−− Exercice 17

SELECT DISTINCT gp . nom, gp . prenomFROM personne sh , personne p , personne gpWHERE sh .nom = ’ Stone ’AND (

( sh . pere = gp . numpers )OR ( sh . mere = gp . numpers )OR (

( sh . mere = p . numpers OR sh . pere = p . numpers )AND (p . mere = gp . numpers OR p . pere = gp . numpers )

)) ;

81


−− Exercice 1

SELECT COUNT(∗ )FROM FOURNISSEUR;

−− Exercice 2

SELECT COUNT(DISTINCT NUMFOU)FROM LIVRAISON;

−− Exercice 3

SELECT MAX( pr ix ) AS PRIX MAXFROM PROPOSER PR, FOURNISSEUR FWHERE F. numfou = PR. numfouAND nomfou = ’ f1 ’ ;

−− Exercice 4

SELECT nomfou , count (DISTINCT numprod) AS NB PROD PROPOSESFROM FOURNISSEUR F, PROPOSER PWHERE F. numfou = P. numfouGROUP BY nomfou ;

−− Exercice 5

SELECT COUNT(DISTINCT P. numprod) − count (DISTINCT PR. numprod)FROM PRODUIT P, PROPOSER PR;

−− Exercice 6

SELECT nomprod , COUNT(DISTINCT D. numfou )FROM PRODUIT P, DETAILLIVRAISON DWHERE P. numprod = D. numprodGROUP BY nomprod ;

−− Exercice 7

SELECT nomfou , L . numli , d a t e l i , COUNT(numprod) AS NB PRODUITSFROM FOURNISSEUR F, LIVRAISON L , DETAILLIVRAISON DWHERE F. numfou = L . numfouAND D. numfou = L . numfouAND D. numli = L . numliGROUP BY nomfou , L . numli , d a t e l i ;

−− Exercice 8

SELECT nomfou , L . numli , d a t e l i , SUM( qte ∗ pr ix ) AS TOTALFROM FOURNISSEUR F, LIVRAISON L , DETAILLIVRAISON D, PROPOSER PWHERE F. numfou = L . numfouAND D. numfou = L . numfouAND D. numli = L . numliAND P. numfou = F. numfouAND D. numprod = P. numprodGROUP BY nomfou , L . numli , d a t e l i ;

−− Exercice 9

SELECT nomprodFROM PRODUIT P, PROPOSER PRWHERE P. numprod = PR. numprodGROUP BY nomprodHAVING COUNT(D. numfou ) = 1 ;

−− Exercice 10

SELECT nomfouFROM FOURNISSEUR F, PROPOSER P, DETAILLIVRAISON LWHERE F. numfou = P. numfouAND L . numfou = F. numfouGROUP BY nomfouHAVING COUNT(DISTINCT P. numprod) = COUNT(DISTINCT L . numprod ) ;

82

3.5 Vues

−− Exercice 1

CREATE VIEW QUANTITE LIVREE PAR PRODUIT ASSELECT numprod , SUM( qte ) AS QUANTITE LIVREEFROM DETAILLIVRAISONGROUP BY numprod ;

−− Exercice 2

SELECT MAX(QUANTITE LIVREE)FROM QUANTITE LIVREE PAR PRODUIT;

−− Exercice 3

CREATE VIEW FACTURE PAR LIVRAISON ASSELECT D. numfou , D. numli , SUM( qte ∗ pr ix ) AS MONTANT FACTUREFROM PROPOSER PR, DETAILLIVRAiSON DwHERE PR. numfou = D. numfouAND D. numprod = PR. numprodGROUP BY D. numfou , D. numli ;

−− Exercice 4

CREATE VIEW FACTURE PAR FOURNISSEUR ASSELECT numfou , SUM(MONTANT FACTURE) AS TOTAL FACTUREFROM FACTURE PAR LIVRAISONGROUP BY numfou ;

−− Exercice 5

SELECT MIN(TOTAL FACTURE)FROM FACTURE PAR FOURNISSEUR;

−− Exercice 6

CREATE VIEW NB PROD DIST LIVRES PAR FOU ASSELECT DISTINCT numfou ,

COUNT(DISTINCT numprod) AS NB PRODUITS DISTINCTS LIVRESFROM DETAILLIVRAISONGROUP BY numfou ;

SELECT MAX(NB PRODUITS DISTINCTS LIVRES)FROM NB PROD DIST LIVRES PAR FOU ;

83


−− Exercice 1

SELECT nomfou ,(SELECT COUNT(numprod)FROM PROPOSER PWHERE P. numfou = F. numfou) AS NB PROD PROPOSES

FROM FOURNISSEUR F;

−− Exercice 2

SELECT nomfouFROM FOURNISSEURWHERE numfou IN

(SELECT numfouFROM PROPOSERWHERE numprod = 2 ) ;

−− Exercice 3


(SELECT numfouFROM PROPOSERWHERE numprod =

(SELECT numprodFROM PRODUITWHERE nomprod = ’ Poupee Batman ’)

) ;

−− Exercice 4


(SELECT numfouFROM DETAILLIVRAISONWHERE numprod IN

(SELECT numprodFROM PRODUITWHERE nomprod = ’ Poupee Batman ’)

) ;

−− Exercice 5

SELECT nomfouFROM FOURNISSEUR FWHERE

(SELECT COUNT(∗ )FROM PROPOSER PRWHERE F. numfou = PR. numfou) > 0

AND(SELECT COUNT(DISTINCT numprod)FROM DETAILLIVRAISON DWHERE F. numfou = D. numfou)=(SELECT COUNT(∗ )FROM PROPOSER PRWHERE F. numfou = PR. numfou) ;

−− Exercice 6

SELECT nomfou ,(SELECT nomprodFROM PRODUIT PWHERE P. numprod IN

(SELECT numprodFROM PROPOSER PR1WHERE PR1. numfou = F. numfouAND pr ix =

(SELECT MAX( pr ix )FROM PROPOSER PR2WHERE PR2. numfou = F. numfou)

))

FROM FOURNISSEUR F;

−− Exercice 7

84

CREATE VIEW NB PROD LIVRES PAR FOU ASSELECT numfou , numprod , SUM( qte ) AS QTEFROM DETAILLIVRAISONGROUP BY numfou , numprod ;

SELECT nomprod , nomfouFROM FOURNISSEUR F, PRODUIT PWHERE

(SELECT QTEFROM NB PROD LIVRES PAR FOU DWHERE D. numprod = P. numprodAND D. numfou = F. numfou)=(SELECT MAX(QTE)FROM NB PROD LIVRES PAR FOU DWHERE D. numprod = P. numprod) ;

−− Exercice 8

SELECT MIN(NB PROD)FROM

(SELECT(SELECT COUNT(∗ )FROM PROPOSER PRWHERE PR. numfou = F. numfou) AS NB PROD

FROM FOURNISSEUR F) ;

−− Exercice 9


(sELECT numfouFROM

(SELECT numfou ,(SELECT COUNT(∗ )FROM PROPOSER PRWHERE F. numfou = PR. numfou) AS NB PROD

FROM FOURNISSEUR F)

WHERE NB PROD =(SELECT MIN(NB PROD)FROM

(SELECT numfou ,(SELECT COUNT(∗ )FROM PROPOSER PRWHERE F. numfou = PR. numfou) AS NB PROD

FROM FOURNISSEUR F)

)) ;

−− Exercice 10

SELECT nomprod , nomfouFROM PRODUIT P, FOURNISSEUR F,

(SELECT F1 . numfou , P1 . numprodFROM FOURNISSEUR F1 , PRODUIT P1WHERE

(SELECT SUM(QTE)FROM DETAILLIVRAiSON DWHERE D. numfou = F1 . numfouAND D. numprod = P1 . numprod

)=

(SELECT MAX(NB LIV)FROM

(SELECT numprod , SUM(QTE) AS NB LIVFROM DETAILLIVRAiSON DGROUP BY numprod , numfou

) QWHERE Q. numprod = P1 . numprod)

) MWHERE P. numprod = M. numprodAND F. numfou = M. numfou ;

85


−− Exercice 1

ALTER TABLE LIVRAISON ADD CONSTRAINT ck date mandatoryCHECK ( d a t e l i IS NOT NULL) ;

ALTER TABLE PROPOSER ADD CONSTRAINT ck pr ice mandatoryCHECK ( pr ix IS NOT NULL) ;

ALTER TABLE DETAILLIVRAISON ADD CONSTRAINT ck qte mandatoryCHECK ( qte IS NOT NULL) ;

−− Exercice 2

ALTER TABLE PROPOSER ADD CONSTRAINT c k p r i c e p o s i t i v eCHECK ( pr ix > 0 ) ;

ALTER TABLE DETAILLIVRAISON ADD CONSTRAINT c k q t e p o s i t i v eCHECK ( qte > 0 ) ;

−− Exercice 3

ALTER TABLE LIVRAISON ADD CONSTRAINT ck oc tobre 2006CHECK ( to char ( d a t e l i , ’yyyymm ’ ) >= ’ 200610 ’ ) ;

−− Exercice 4

ALTER TABLE LIVRAISON ADD CONSTRAINT c k f e t e d u t r a v a i lCHECK ( to char ( d a t e l i , ’mmdd ’ ) <> ’ 0501 ’ ) ;

−− Exercice 5

UPDATE PRODUIT SETnomprod = upper ( substr (nomprod , 1 , 1 ) ) | |

substr (nomprod , 2 , LENGTH( nomprod ) − 1 ) ;

ALTER TABLE PRODUIT ADDCONSTRAINT ck majuscu l e produ i tCHECK (upper ( substr (nomprod , 1 , 1 ) ) = substr (nomprod , 1 , 1 ) ) ;

UPDATE FOURNISSEUR SETnomfou = upper ( substr ( nomfou , 1 , 1 ) ) | |

substr ( nomfou , 2 , LENGTH( nomfou ) − 1 ) ;

ALTER TABLE FOURNISSEUR ADDCONSTRAINT c k m a j u s c u l e f o u r n i s s e u rCHECK (upper ( substr ( nomfou , 1 , 1 ) ) = substr ( nomfou , 1 , 1 ) ) ;

86

3.8 Revisions

−− Exercice 1

SELECT nomfouFROM FOURNISSEUR FWHERE

(SELECT COUNT(∗ )FROM LIVRAISON LWHERE L . numfou = F. numfou)>= 2 ;

−− Exercice 2

CREATE VIEW LIVRAISONS PAR ANNEE ASSELECT ANNEE, COUNT(∗ ) AS NB LIVRAISONSFROM

(SELECT to char ( d a t e l i , ’ yyyy ’ ) AS ANNEE, numfou , numliFROM LIVRAISON)

GROUP BY ANNEE;

SELECT ANNEEFROM LIVRAISONS PAR ANNEEWHERE NB LIVRAISONS =

(SELECT MAX(NB LIVRAISONS)FROM LIVRAISONS PAR ANNEE) ;

−− Exercice 3

CREATE VIEW FOU KI ONT TOU LIVRE ASSELECT numfouFROM FOURNISSEUR FWHERE

(SELECT COUNT(∗ )FROM PROPOSER PRWHERE PR. numfou = F. numfou )=(SELECT COUNT(DISTINCT numprod)FROM DETAILLIVRAISON DWHERE D. numfou = F. numfou) ;

CREATE VIEW DERNIERE LI PAR FOU ASSELECT numfou , MAX( d a t e l i ) AS DATE MAXFROM LIVRAISONGROUP BY numfou ;


(SELECT F. numfouFROM FOU KI ONT TOU LIVRE F, DERNIERE LI PAR FOU DWHERE F. numfou = D. numfouAND DATE MAX =

(SELECT MAX(DATE MAX)FROM FOU KI ONT TOU LIVRE F, DERNIERE LI PAR FOU DWHERE F. numfou = D. numfou)

) ;

87

3.9 Examen Type

DROP TABLE RESULTAT;DROP TABLE EXAMEN;DROP TABLE PREREQUIS;DROP TABLE INSCRIPTION ;DROP TABLE MODULE;DROP TABLE ETUDIANT;

−− Exercice 1 et 3

CREATE TABLE ETUDIANT(numEtud number ,nom varchar2 (40 ) ,prenom varchar2 (40 ) ,da t ena i s s date ,c i v i l i t e varchar2 ( 4 ) ,patronyme varchar2 (40 ) ,numsecu varchar2 (15) NOT NULL) ;

CREATE TABLE MODULE(codMod number ,nomMod varchar2 (15 ) ,ef fecMax number DEFAULT 3 0 ) ;

CREATE TABLE EXAMEN(codMod number ,codExam number ,dateExam date ) ;

CREATE TABLE INSCRIPTION(numEtud number ,codMod number ,da te Insc date default sysdate ) ;

CREATE TABLE PREREQUIS(codMod number ,codModPrereq number ,noteMin number(4 , 2) NOT NULL) ;

CREATE TABLE RESULTAT(codMod number ,codExam number ,numEtud number ,note number(4 , 2 ) ) ;

−− Exercice 2

ALTER TABLE ETUDIANT ADDCONSTRAINT pk etud iantPRIMARY KEY (numEtud ) ;

ALTER TABLE MODULE ADDCONSTRAINT pk modulePRIMARY KEY (codMod ) ;

ALTER TABLE EXAMEN ADDCONSTRAINT pk examenPRIMARY KEY (codMod , codExam ) ;

ALTER TABLE PREREQUIS ADDCONSTRAINT pk pre r equ i sPRIMARY KEY (codMod , codModPrereq ) ;

ALTER TABLE INSCRIPTION ADDCONSTRAINT p k i n s c r i p t i o nPRIMARY KEY (codMod , numEtud ) ;

ALTER TABLE RESULTAT ADDCONSTRAINT p k r e s u l t a tPRIMARY KEY (codMod , numEtud , codExam ) ;

ALTER TABLE INSCRIPTION ADD(CONSTRAINT f k i n s c r i p t i o n e t u d i a n tFOREIGN KEY (numEtud)REFERENCES ETUDIANT(numEtud ) ,CONSTRAINT f k i n s c r i p t i o n m o d u l eFOREIGN KEY (codMod)REFERENCES MODULE(codMod ) ) ;

ALTER TABLE PREREQUIS ADD(CONSTRAINT fk prerequ i s codmodFOREIGN KEY (codMod)REFERENCES MODULE(codMod ) ,CONSTRAINT f k pre requ i s codmodpre reqFOREIGN KEY ( codModPrereq )REFERENCES MODULE(codMod ) ) ;

ALTER TABLE EXAMEN ADDCONSTRAINT fk examenFOREIGN KEY (codMod)REFERENCES MODULE(codMod ) ;

ALTER TABLE RESULTAT ADD(CONSTRAINT f k r e su l t a t examenFOREIGN KEY (codMod , codExam)

88

REFERENCES EXAMEN(codMod , codExam ) ,CONSTRAINT f k r e s u l t a t i n s c r i p t i o nFOREIGN KEY (codMod , numEtud)REFERENCES INSCRIPTION(codMod , numEtud ) ) ;

−− Exercice 3

−− I c i se trouve l e s cons t ra in tes de type CHECK qui n ’ ont pas e t e p lac e es−− au niveau de la t a b l e pour des raisons de l i s i b i l i t e .

ALTER TABLE ETUDIANT ADD(CONSTRAINT c k c i v i l i t eCHECK

(c i v i l i t e IN ( ’Mr ’ , ’Mme’ , ’ Mlle ’ )) ,

CONSTRAINT c k c i v i l i t e n u m s e c uCHECK

(SUBSTR( numsecu , 1 , 1) = ’ 2 ’ OR patronyme IS NULL) ,

CONSTRAINT ck length numsecuCHECK

(l ength ( numsecu ) = 15) ,

CONSTRAINT ck annee numsecu CHECK(to char ( datena i s s , ’ yy ’ ) = substr ( numsecu , 2 , 2))

) ;

−− I l imposs ib l e de l i m i t e r de facon d e c l a r a t i v e l e nombre d ’ e tud iants−− i n s c r i t s a un module .

−− Exercice 4

INSERT INTO ETUDIANT VALUES( (SELECT nvl (MAX(numEtud ) , 0) + 1 FROM ETUDIANT) ,’ Four i e r ’ ,’ Joseph ’ ,t o date ( ’ 21031768 ’ , ’ddmmyyyy ’ ) ,’Mr ’ ,NULL,’ 168031234567890 ’) ;

INSERT INTO MODULE (codMod , nomMod) VALUES((SELECT nvl (MAX(codMod ) , 0) + 1 FROM MODULE) ,’ Maths ’) ;

INSERT INTO INSCRIPTION (codMod , numEtud) VALUES( (SELECT numEtud FROM ETUDIANT WHERE nom = ’ Four ie r ’ ) ,(SELECT codMod FROM MODULE WHERE nomMod = ’ Maths ’ ) ) ;

INSERT INTO EXAMEN VALUES((SELECT codMod FROM MODULE WHERE nomMod = ’ Maths ’ ) ,1 ,t o date ( ’ 02012007 ’ , ’ddmmyyyy ’ )) ;

INSERT INTO RESULTAT VALUES((SELECT codMod FROM MODULE WHERE nomMod = ’ Maths ’ ) ,1 ,(SELECT numEtud FROM ETUDIANT WHERE nom = ’ Four i e r ’ ) ,19) ;

UPDATE RESULTAT SET note = 20wHERE

numEtud = (SELECT numEtud FROM ETUDIANT WHERE nom = ’ Four ie r ’ )AND codMod = (SELECT codMod FROM MODULE WHERE nomMod = ’ Maths ’ )AND codExam = 1 ;

−− Exercice 5

−− requ e te 1

SELECT nomFROM ETUDIANT;

−− requ e te 2

89

SELECT nomFROM ETUDIANTWHERE numEtud IN

(SELECT numEtudFROM INSCRIPTIONWHERE codMod IN

(SELECT codModFROM MODULEWHERE nomMod = ’ Maths ’)

) ;

−− requ e te 3

SELECT nom, prenom ,(SELECT MAX(NOTE)FROM RESULTAT RWHERE R. numEtud = E. numEtudAND codMod =


) AS NOTE DEFINITIVEFROM ETUDIANT E;

−− requ e te 4

SELECT nom, prenomFROM ETUDIANT EWHERE

0 <=(SELECT count (∗ )FROM RESULTAT RWHERE R. numEtud = E. numEtudAND note >= 10AND codMod =


) ;

−− requ e te 5

SELECT nom, prenomFROM ETUDIANT EWHERE

(SELECT count (∗ )FROM RESULTAT RWHERE R. numEtud = E. numEtudAND codMod =


) = 0 ;

−− requ e te 6

CREATE VIEW NOTE MATHS PAR ETU ASSELECT numEtud ,

(SELECT MAX(NOTE)FROM RESULTAT RWHERE R. numEtud = E. numEtudAND codMod =


) AS NOTE MATHSFROM ETUDIANT E;

SELECT nom, prenomFROM ETUDIANTWHERE numEtud IN

(

90

SELECT numEtudFROM NOTE MATHS PAR ETUWHERE NOTE MATHS

=(SELECT MAX(NOTE MATHS)FROM NOTE MATHS PAR ETU)

) ;

−− requ e te 7

CREATE VIEW NOTE MIN MATHS PAR ETU ASSELECT numEtud ,

(SELECT MIN(NOTE)FROM RESULTAT RWHERE R. numEtud = E. numEtudAND codMod =


) AS NOTE MATHSFROM ETUDIANT E;

SELECT nom, prenomFROM ETUDIANTWHERE numEtud IN

(SELECT numEtudFROM NOTE MATHS PAR ETUWHERE NOTE MATHS

=(SELECT MAX(NOTE MATHS)FROM NOTE MIN MATHS PAR ETU)

) ;

−− requ e te 8

CREATE VIEW NOTE PAR ETU MOD ASSELECT numEtud , codMod ,

(SELECT MAX( note )FROM RESULTAT RWHERE R. numEtud = I . numEtudAND R. codMod = I . codMod) AS NOTE DEF

FROM INSCRIPTION I ;

SELECT nomModFROM MODULE MWHERE

(SELECT COUNT(∗ )FROM PREREQUIS PWHERE M. codMod = P. codModAND noteMin >

(SELECT NOTE DEFFROM NOTE PAR ETU MOD NWHERE N. codMod = P. codModPrereqAND N. numEtud =

(SELECT numEtudFROM ETUDIANTWHERE nom = ’ Four ie r ’)

)) = 0

AND M. codMod NOT IN(SELECT codModFROM INSCRIPTIONWHERE numEtud IN

(SELECT numEtudFROM ETUDIANTWHERE nom = ’ Four ie r ’)

) ;

91


−− Exercice 1

DECLAREa NUMBER;b NUMBER;t NUMBER;

BEGINa := 1 ;b := 2 ;DBMS OUTPUT. PUT LINE( ’ a = ’ | | a ) ;DBMS OUTPUT. PUT LINE( ’b = ’ | | b ) ;DBMS OUTPUT. PUT LINE( ’ Let ’ ’ s swap a and b . . . The r e s u l t i s : ’ ) ;t := a ;a := b ;b := t ;DBMS OUTPUT. PUT LINE( ’ a = ’ | | a ) ;DBMS OUTPUT. PUT LINE( ’b = ’ | | b ) ;

END;/

−− Exercice 2

DECLAREa NUMBER;r e s NUMBER;counter NUMBER;

BEGINa := 10 ;r e s := 1 ;counter := a ;WHILE counter > 0 LOOP

re s := r e s ∗ counter ;counter := counter − 1 ;

END LOOP;DBMS OUTPUT. PUT LINE( a | | ’ != ’ | | r e s ) ;

END;/

−− Exercice 3

DECLAREa NUMBER := 48 ;b NUMBER := 84 ;amodb NUMBER;

BEGINDBMS OUTPUT.PUT( ’PGCD( ’ | | a | | ’ , ’ | | b | | ’ ) = ’ ) ;WHILE b > 0 LOOP

amodb := a ;WHILE amodb >= b LOOP

amodb := amodb − b ;END LOOP;a := b ;b := amodb ;

END LOOP;DBMS OUTPUT. PUT LINE( a ) ;

END;/

92

3.11 Tableaux et Structures

SET SERVEROUTPUT ON

−− Tableaux

DECLARETYPE montab IS VARRAY (50) OF INTEGER;t montab ;

BEGINt := montab ( ) ;t . extend ( 2 0 ) ;

−− I n i t i a l i s a t i o nFOR i IN 1 . . 2 0 LOOP

t ( i ) := i ∗ i ;END LOOP;

−− Invers ion de l ’ ordre des e l ementsDECLARE

temp integer ;BEGIN

FOR i IN 1 . . 1 0 LOOPtemp := t ( i ) ;t ( i ) := t (20− i +1);t (20− i +1) := temp ;

END LOOP;END;

−− Aff ichageFOR i IN 1 . . 2 0 LOOP

DBMS OUTPUT. PUT LINE( ’ t ( ’ | |i | | ’ ) = ’ | | t ( i ) ) ;

END LOOP;

−− Tri a b u l l eDECLARE

temp integer ;BEGIN

FOR i IN REVERSE 2 . . 2 0 LOOPFOR j IN 2 . . i LOOP

IF t ( j − 1) > t ( j ) THENtemp := t ( j ) ;t ( j ) := t ( j −1);t ( j−1) := temp ;

END IF ;END LOOP;

END LOOP;END;

−− Aff ichageFOR i IN 1 . . 2 0 LOOP

DBMS OUTPUT. PUT LINE( ’ t ( ’ | |i | | ’ ) = ’ | | t ( i ) ) ;

END LOOP;

−− Recherche par dichotomie de l ’ e l ement 225DECLARE

i n f INTEGER := 1 ;sup INTEGER := 20 ;m INTEGER;X INTEGER := 400 ;

BEGINLOOP

DBMS OUTPUT. PUT LINE( ’ i n f = ’ | | i n f | |’ ; sup = ’ | | sup ) ;

m := ( i n f + sup ) / 2 ;EXIT WHEN

t (m) = X OR i n f = sup ;IF t (m) > X THEN

sup := m−1;ELSE

i n f := m+1;END IF ;

END LOOP;IF t (m) = X THEN

DBMS OUTPUT. PUT LINE(X | |’ e s t dans l e tab leau ’ ) ;

ELSEDBMS OUTPUT. PUT LINE(X | |’ n” e s t pas dans l e tab leau ’ ) ;

END IF ;END;

END;/

93

−− Structures

DECLARE−− Maillon d ’ une l i s t e chaıneeTYPE CELL IS RECORD(

−− Donnee de chaque mail londata INTEGER,−− Indice du mail lon prec edent de la l i s t e ,−− −1 s ’ i l n ’ y en a pasprev ious INTEGER,−− Indice du mail lon suivant de la l i s t e ,−− −1 s ’ i l n ’ y en a pasnext INTEGER

) ;−− Type tab leau contenant l e s mai l lons de la l i s t eTYPE TREE IS VARRAY (19) OF CELL;−− Tableau contenant l e s mai l lons de la l i s t et TREE;−− ind ice du premier el ement de la l i s t ef i r s t integer ;−− ind ice du dernier el ement de la l i s t elast integer ;

BEGINt := TREE( ) ;t . extend ( 1 9 ) ;

−− I n i t i a l i s a t i o nFOR i IN 1 . . 1 9 LOOP

t ( i ) . data := power ( i , 5) mod 19 ;t ( i ) . p rev ious := i −1;t ( i ) . next := i +1;

END LOOP;f i r s t := 1 ;last := 19 ;t ( f i r s t ) . p rev ious := −1;t ( last ) . next := −1;

−− Aff ichageDECLARE

p integer := f i r s t ;BEGIN

WHILE p <> −1 LOOPDBMS OUTPUT. PUT LINE( ’ ( ’ | | p | | ’ , ’ | |

t (p ) . data | | ’ , ’ | |t (p ) . p rev ious | | ’ , ’ | |t (p ) . next | | ’ ) ’ ) ;

p := t (p ) . next ;END LOOP;

END;

−− Invers ion de l ’ ordre des e l ementsDECLARE

temp INTEGER;BEGIN

FOR i IN 1 . . 1 9 LOOPtemp := t ( i ) . p rev ious ;t ( i ) . p rev ious := t ( i ) . next ;t ( i ) . next := temp ;

END LOOP;f i r s t := 19 ;last := 1 ;

END;



WHILE p <> −1 LOOPDBMS OUTPUT. PUT LINE( ’ ( ’ | |

p | | ’ , ’ | |t (p ) . data | | ’ , ’ | |t (p ) . p rev ious | | ’ , ’ | |t (p ) . next | | ’ ) ’ ) ;


END;

−− Tri a b u l l eDECLARE

i integer := last ;j integer ;

BEGINWHILE t ( t ( i ) . p rev ious ) . p rev ious <> −1 LOOP

j := f i r s t ;WHILE i<>j LOOP

IF ( t ( j ) . data > t ( t ( j ) . next ) . data ) THEN

94

−− Echange de j e t t ( j ) . next−− par modi f icat ion du chaınageDECLARE

a f t e r J INTEGER := t ( j ) . next ;b e fo r eJ INTEGER := t ( j ) . p rev ious ;

BEGINt ( j ) . next := t ( a f t e r J ) . next ;t ( a f t e r J ) . next := j ;t ( a f t e r J ) . p rev ious := be fo reJ ;t ( j ) . p rev ious := a f t e r J ;IF t ( j ) . next <> −1 THEN

t ( t ( j ) . next ) . p rev ious := j ;ELSE

last := j ;END IF ;IF t ( a f t e r J ) . p rev ious <> −1 THEN

t ( t ( a f t e r J ) . prev ious ) . next := a f t e r J ;ELSE

f i r s t := a f t e r J ;END IF ;IF a f t e r J = i THEN

i := j ;END IF ;

END;

ELSEj := t ( j ) . next ;

END IF ;END LOOP;i := t ( i ) . p rev ious ;

END LOOP;END;



WHILE p <> −1 LOOPDBMS OUTPUT. PUT LINE( ’ ( ’ | | p | | ’ , ’ | |

t (p ) . data | | ’ , ’ | |t (p ) . p rev ious | | ’ , ’ | |t (p ) . next | | ’ ) ’ ) ;


END;

END;/

95

3.12 Application du PL/SQL et Exceptions

SET SERVEROUTPUT ONSET AUTOCOMMIT OFF

−− Exercice 1

DECLAREunClient PERSONNE%ROWTYPE;numClient PERSONNE. numpers%type ;Y A EU UNE MERDE EXCEPTION;

BEGINFOR numClient IN 1 . . 2 1 LOOP

BEGINSELECT ∗ INTO unCl ient

FROM PERSONNEWHERE numpers = numClient ;

INSERT INTO CLIENT ( numcli , nomcli , prenomcl i )VALUES( unCl ient . numpers ,unCl ient . nom,unCl ient . prenom ) ;


DBMS OUTPUT. PUT LINE(’ Personne n”a l ” i d e n t i f i a n t ’ | |numClient ) ;

WHEN TOO MANY ROWS THENDBMS OUTPUT. PUT LINE(

’ Cette message ne dev ra i t jamais appara ı t r e ! ’ ) ;WHEN DUP VAL ON INDEX THEN

DBMS OUTPUT. PUT LINE(’ Contra inte de c l e v i o l e e ! Message SQL : ’ | |

SQLERRM) ;WHEN OTHERS THEN

RAISE Y A EU UNE MERDE;END;

END LOOP;COMMIT;

EXCEPTIONWHEN Y A EU UNE MERDE THEN

DBMS OUTPUT. PUT LINE( ’SQLCODE = ’ | | SQLCODE) ;DBMS OUTPUT. PUT LINE( ’ I l y a eu une Merde ! ’ ) ;ROLLBACK;

END;/

−− Exercice 2

DECLAREunClient CLIENT%rowtype ;

BEGINSELECT ∗ INTO unCl ientFROM CLIENT WHERE numCli =

(SELECT MAX( numcli )FROM CLIENT) ;

INSERT INTO PERSONNEL VALUES(1 ,unCl ient . nomcli ,unCl ient . prenomcli ,NULL,1254.28) ;

COMMIT;EXCEPTION

WHEN NO DATA FOUND THENDBMS OUTPUT. PUT LINE( ’Aucun c l i e n t ’ ) ;

WHEN DUP VAL ON INDEX THENDBMS OUTPUT. PUT LINE(’ I l y a un gros probleme . . . J”comprends pas c” qui s ” passe ’ ) ;

END;/

−− Exercice 3

DECLAREnumClient CLIENT. numcli%TYPE;tCCL TYPECCL. numtypeccl%TYPE;nto TYPEOPERATION. numtypeoper%TYPE;Y A UN GRO BLEME EXCEPTION;

BEGINSELECT numtypeoper INTO nto

96

FROM TYPEOPERATIONWHERE nomtypeoper = ’ depot e sp e c e s ’ ;

SELECT numtypeccl INTO tCCLFROM TYPECCLWHERE nomtypeCCL = ’Compte courant ’ ;

FOR numClient IN 1 . . 2 1 LOOPBEGIN

INSERT INTO COMPTECLIENT VALUES(numClient ,1 ,tCCL ,SYSDATE,1) ;

INSERT INTO OPERATION VALUES(numClient ,1 ,1 ,nto ,SYSDATE,numClient ∗ 100 ,’ inaugurat ion du compte ’) ;

COMMIT;EXCEPTIONWHEN OTHERS THEN−− Adaptez l e numero du code ,−− chez moi ca donne −2290IF SQLCODE = −2290 THEN

DECLAREt o t a l OPERATION. montantoper%TYPE := numClient ∗ 100 ;t o I n s e r t OPERATION. montantoper%TYPE;cpt NUMBER := 1 ;

BEGINWHILE t o t a l > 0 LOOP

IF t o t a l > 1000 THENt o I n s e r t := 1000 ;

ELSEt o I n s e r t := t o t a l ;

END IF ;INSERT INTO OPERATION VALUES

(numClient ,1 ,(SELECT nvl (MAX( numoper ) , 0) + 1

FROM OPERATIONWHERE numcli = numClientAND numccl = 1

) ,nto ,SYSDATE,to In s e r t ,’ Inaugurat ion du compte ’ | | cpt) ;

t o t a l := t o t a l − t o I n s e r t ;cpt := cpt + 1 ;

END LOOP;EXCEPTION

WHEN OTHERS THENDBMS OUTPUT. PUT LINE( ’MOD( to ta l , 1000) = ’ | | MOD( to ta l , 1 000 ) ) ;DBMS OUTPUT. PUT LINE( ’SQLCODE = ’ | | SQLCODE) ;

DBMS OUTPUT. PUT LINE( ’SQLERRM = ’ | | SQLERRM) ;RAISE Y A UN GRO BLEME;

END;ELSE

DBMS OUTPUT. PUT LINE( ’SQLCODE = ’ | | SQLCODE) ;DBMS OUTPUT. PUT LINE( ’SQLERRM = ’ | | SQLERRM) ;

ROLLBACK;END IF ;END;

END LOOP;EXCEPTION

WHEN NO DATA FOUND THENDBMS OUTPUT. PUT LINE( ’ Pas de donnees ! ’ ) ;

WHEN TOO MANY ROWS THENDBMS OUTPUT. PUT LINE( ’ Trop de donnees ! ’ ) ;

WHEN Y A UN GRO BLEME THENDBMS OUTPUT. PUT LINE( ’ I l y a un gros probleme ! ’ ) ;DBMS OUTPUT. PUT LINE( ’SQLCODE = ’ | | SQLCODE) ;DBMS OUTPUT. PUT LINE( ’SQLERRM = ’ | | SQLERRM) ;


END;/

−− Exercice 4

97

DECLAREnumClient CLIENT. numcli%TYPE := 2 ;numCompteLivret TYPECCL. numtypeCCL%TYPE;nto TYPEOPERATION. numtypeoper%TYPE;montant OPERATION. montantoper%TYPE;Y A UN GRO BLEME EXCEPTION;

BEGINSELECT numtypeoper INTO nto

FROM TYPEOPERATIONWHERE nomtypeoper = ’ virement ’ ;

SELECT numtypeccl INTO numCompteLivretFROM TYPECCLWHERE nomtypeCcl = ’ l i v r e t ’ ;

WHILE numClient <= 21 LOOPBEGIN

montant := 100 ∗ numClient − 500 ;INSERT INTO COMPTECLIENT VALUES

(numClient ,2 ,numCompteLivret ,SYSDATE,1) ;

INSERT INTO OPERATION VALUES(numClient ,1 ,(SELECT nvl (MAX( numoper ) , 0) + 1

FROM OPERATIONWHERE numcli = numClientAND numccl = 1) ,

nto ,SYSDATE,−montant ,’ versement l i v r e t ’) ;



nto ,SYSDATE,montant ,’ versement l i v r e t ’) ;

COMMIT;EXCEPTION

WHEN OTHERS THEN−− idemIF SQLCODE = −2290 THEN

DECLAREt o t a l OPERATION. montantoper%TYPE := montant ;toMove OPERATION. montantoper%TYPE;cpt NUMBER := 1 ;

BEGINWHILE t o t a l > 1000 LOOP

IF t o t a l > 1000 THENtoMove := 1000 ;

ELSEtomove := t o t a l ;

END IF ;INSERT INTO OPERATION VALUES

(numClient ,1 ,(SELECT nvl (MAX( numoper ) , 0) + 1


nto ,SYSDATE,−toMove ,’ versement l i v r e t ’ | | cpt) ;



nto ,

98

SYSDATE,toMove ,’ versement l i v r e t ’ | | cpt) ;

t o t a l := t o t a l − toMove ;cpt := cpt + 1 ;

END LOOP;COMMIT;


RAISE Y A UN GRO BLEME;END;

ELSEDBMS OUTPUT. PUT LINE( ’SQLCODE = ’ | | SQLCODE) ;DBMS OUTPUT. PUT LINE( ’SQLERRM = ’ | | SQLERRM) ;ROLLBACK;

END IF ;END;

COMMIT;numClient := numClient + 2 ;

END LOOP;EXCEPTION

WHEN NO DATA FOUND THENDBMS OUTPUT. PUT LINE( ’ Pas de donnees ! ’ ) ;

WHEN TOO MANY ROWS THENDBMS OUTPUT. PUT LINE( ’ Trop de donnees ! ’ ) ;

WHEN Y A UN GRO BLEME THENDBMS OUTPUT. PUT LINE( ’ I l y a un gros probleme ! ’ ) ;DBMS OUTPUT. PUT LINE( ’SQLCODE = ’ | | SQLCODE) ;DBMS OUTPUT. PUT LINE( ’SQLERRM = ’ | | SQLERRM) ;


END;/

99


−− Exercice 1

CREATE OR REPLACE FUNCTIONbad puis sance (b NUMBER, n NUMBER)RETURN NUMBER IS

BEGINIF (n = 0) THEN

RETURN 1 ;ELSE

RETURN b ∗ bad puis sance (b , n − 1 ) ;END IF ;

END;/

−− Exercice 2

CREATE OR REPLACE FUNCTIONgood pui s sance (b NUMBER, n NUMBER)RETURN NUMBER IS

BEGINIF (n = 0) THEN

RETURN 1 ;END IF ;IF ( MOD(n , 2) = 0 ) THEN

RETURN good pui s sance (b ∗ b , n / 2 ) ;END IF ;RETURN b ∗ good pui s sance (b , n − 1 ) ;

END;/

−− Exercice 3

CREATE OR REPLACE FUNCTIONdemiFreres (A PERSONNE. numpers%type , B PERSONNE. numpers%type )RETURN BOOLEAN IS

rowA PERSONNE%rowtype ;rowB PERSONNE%rowtype ;

BEGINSELECT ∗ INTO rowA FROM PERSONNE WHERE numpers = A;SELECT ∗ INTO rowB FROM PERSONNE WHERE numpers = B;RETURN rowA . pere = rowB . pere OR rowA . mere = rowB . mere ;

END;/

−− Exercice 4

CREATE OR REPLACE FUNCTIONf r e r e s (A PERSONNE. numpers%type , B PERSONNE. numpers%type )RETURN BOOLEAN IS


BEGINSELECT ∗ INTO rowA FROM PERSONNE WHERE numpers = A;SELECT ∗ INTO rowB FROM PERSONNE WHERE numpers = B;RETURN rowA . pere = rowB . pere AND rowA . mere = rowB . mere ;

END;/

CREATE OR REPLACE FUNCTIONcousinsGermains (A PERSONNE. numpers%type , B PERSONNE. numpers%type )RETURN BOOLEAN IS


BEGINSELECT ∗ INTO rowA FROM PERSONNE WHERE numpers = A;SELECT ∗ INTO rowB FROM PERSONNE WHERE numpers = B;RETURN

f r e r e s (rowA . pere , rowB . pere )ORf r e r e s (rowA . pere , rowB . mere )ORf r e r e s (rowA . mere , rowB . pere )ORf r e r e s (rowA . mere , rowB . mere ) ;

END;/

−− Exercice 5

CREATE OR REPLACE PROCEDUREa i e u l (P PERSONNE. numpers%type ) IS

row PERSONNE%rowtype ;nb NUMBER;

BEGINSELECT count (∗ ) INTO NB

FROM PERSONNE

100

WHERE numpers = P;IF (NB = 1) THEN

SELECT ∗ INTO rowFROM PERSONNEWHERE numpers = P;

DBMS OUTPUT. PUT LINE( row .nom ) ;a i e u l ( row . pere ) ;

END IF ;END;/

−− Exercice 6

CREATE OR REPLACE PROCEDUREmecs (P PERSONNE. numpers%type ) IS

row PERSONNE%rowtype ;nb NUMBER;

BEGINSELECT count (∗ ) INTO NB

FROM PERSONNEWHERE numpers = P;

IF (NB = 1) THENSELECT ∗ INTO row

FROM PERSONNEWHERE numpers = P;

SELECT count (∗ ) INTO NBFROM PERSONNEWHERE pere = P;

IF (NB > 0) THENDBMS OUTPUT. PUT LINE( row .nom ) ;

END IF ;mecs ( row . pere ) ;mecs ( row . mere ) ;

END IF ;END;/

−− Exercice 7

CREATE OR REPLACE FUNCTIONascendant (A PERSONNE. numpers%type , B PERSONNE. numpers%type )RETURN BOOLEAN IS

row PERSONNE%rowtype ;BEGIN

SELECT ∗ INTO row FROM PERSONNE WHERE numpers = B;IF ( row . pere = A OR row . mere = A) THEN

RETURN TRUE;END IF ;RETURN ( row . pere IS NOT NULL AND ascendant (A, row . pere ) )

OR( row . mere IS NOT NULL AND ascendant (A, row . mere ) ) ;

END;/

BEGINIF ( ascendant (1 , 8 ) ) THEN

DBMS OUTPUT. PUT LINE( ’OK’ ) ;ELSE

DBMS OUTPUT. PUT LINE( ’ pas OK’ ) ;END IF ;

END;/

−− Exercice 8

CREATE OR REPLACE FUNCTIONfmax (A NUMBER, B NUMBER)RETURN NUMBER IS

BEGINIF (A > B) THEN

RETURN A;ELSE

RETURN B;END IF ;

END;/

CREATE OR REPLACE FUNCTIONecartAscendant (A PERSONNE. numpers%type , B PERSONNE. numpers%type )RETURN NUMBER IS

row PERSONNE%rowtype ;NB NUMBER;

BEGINSELECT ∗ INTO row FROM PERSONNE WHERE numpers = B;IF ( row . pere = A OR row . mere = A) THEN

RETURN 1 ;END IF ;IF ( row . pere IS NULL) THEN

101

NB := −1;ELSE

NB := ecartAscendant (A, row . pere ) ;END IF ;IF ( row . mere IS NULL) THEN

NB := fmax(−1 , NB) ;ELSE

NB := fmax ( ecartAscendant (A, row . pere ) , NB) ;END IF ;IF (NB <> −1) THEN

NB := NB + 1 ;END IF ;RETURN NB;

END;/

CREATE OR REPLACE FUNCTIONeca r t (A PERSONNE. numpers%type , B PERSONNE. numpers%type )RETURN NUMBER IS

row PERSONNE%rowtype ;NB NUMBER;

BEGINRETURN fmax ( ecartAscendant (A, B) , ecartAscendant (B, A) ) ;

END;/

−− Exercice 9

SELECT A. nom, A. prenom , B. nom, B. prenomFROM PERSONNE A, PERSONNE BWHERE ecartAscendant (A. numpers , B. numpers ) =

(SELECT MAX( ec )FROM

(SELECT e ca r t (A. numpers , B. numpers ) AS ecFROM PERSONNE A, PERSONNE B)

) ;

−− Exercice 10

102

3.14 Curseurs

CREATE OR REPLACE PROCEDURE copyFromPersonneToClient ISCURSOR C IS

SELECT ∗FROM PERSONNE;

ROW C%rowtype ;BEGIN

FOR ROW IN C LOOPINSERT INTO CLIENT

( numcli , nomcli , prenomcl i )VALUES

(ROW. numpers , ROW. nom, ROW. prenom ) ;END LOOP;COMMIT;

EXCEPTIONWHEN DUP VAL ON INDEX THEN

DBMS OUTPUT. PUT LINE( ’Copy can be done only once . ’ ) ;END;/

CALL copyFromPersonneToClient ( ) ;

CREATE OR REPLACE PROCEDURE takeCl ientToPersonne l ISRow c l i e n t%rowtype ;

BEGINSELECT ∗ INTO Row

FROM CLIENTWHERE numcli =

(SELECT MAX( numcli )FROM CLIENT ) ;

INSERT INTO PERSONNEL( numpers , nompers , prenompers )

VALUES(Row. numcli , Row. nomcli , Row. prenomcl i ) ;

COMMIT;EXCEPTION

WHEN DUP VAL ON INDEX THENDBMS OUTPUT. PUT LINE( ’ This row has a l ready been imported . ’ ) ;ROLLBACK;

WHEN NO DATA FOUND THENDBMS OUTPUT. PUT LINE( ’ Table CLIENT i s empty . ’ ) ;ROLLBACK;

END;/

CALL takeCl ientToPersonne l ( ) ;

CREATE OR REPLACE PROCEDUREcredi tAccount ( numcl ient CLIENT. numcli%type , value NUMBER) IS

BEGINIF ( value > 100) THEN

cred i tAccount ( numclient , 100 ) ;cred i tAccount ( numclient , value − 100 ) ;

ELSEINSERT INTO OPERATION VALUES

( numclient ,1 ,(SELECT nvl (MAX( numoper ) , 0) + 1

FROM OPERATIONWHERE numcli = numclientAND numccl = 1

) ,(SELECT numtypeoperFROM TYPEOPERATIONWHERE nomtypeoper = ’ virement ’) ,sysdate ,value ,’ cadeau ! ’) ;

END IF ;EXCEPTION

WHEN OTHERS THENIF (SQLCODE = −22900) THEN

DBMS OUTPUT. PUT LINE( ’Too much money at once . ’ ) ;END IF ;

END;/

CREATE OR REPLACE PROCEDUREcreateVirement ( numcl ient CLIENT. numcli%type , value NUMBER) IS

BEGININSERT INTO OPERATION VALUES

( numclient ,1 ,(SELECT nvl (MAX( numoper ) , 0) + 1

103


) ,(SELECT numtypeoperFROM TYPEOPERATIONWHERE nomtypeoper = ’ virement ’) ,sysdate ,−value ,’ cadeau ! ’) ;

INSERT INTO OPERATION VALUES( numclient ,2 ,(SELECT nvl (MAX( numoper ) , 0) + 1


) ,(SELECT numtypeoperFROM TYPEOPERATIONWHERE nomtypeoper = ’ virement ’) ,sysdate ,value ,’ cadeau ! ’) ;


IF (SQLCODE = −22900) THENDBMS OUTPUT. PUT LINE( ’Too much money at once . ’ ) ;

END IF ;END;/

CREATE OR REPLACE PROCEDUREmoveToLivret ( numcl ient CLIENT. numcli%type , value NUMBER) IS

BEGINIF ( value >= 0) THEN

IF ( value > 100) THENmoveToLivret ( numclient , 100 ) ;moveToLivret ( numclient , value − 100 ) ;

ELSEcreateVirement ( numclient , value ) ;

END IF ;END IF ;


IF (SQLCODE = −22900) THENDBMS OUTPUT. PUT LINE( ’Too much money at once . ’ ) ;

END IF ;END;/

CREATE OR REPLACE PROCEDUREopenAccount ( numcl ient CLIENT. numcli%type ) IS

BEGININSERT INTO COMPTECLIENT VALUES

( numclient ,1 ,(SELECT numtypeccl

FROM TYPECCLWHERE nomtypeccl = ’Compte courant ’) ,sysdate ,(SELECT numpersFROM PERSONNELWHERE numpers =

(SELECT MAX( numcli )FROM CLIENT)

)) ;

INSERT INTO COMPTECLIENT VALUES( numclient ,2 ,(SELECT numtypeccl

FROM TYPECCLWHERE nomtypeccl = ’ virement ’) ,sysdate ,(SELECT numpersFROM PERSONNELWHERE numpers =

(SELECT MAX( numcli )FROM CLIENT

104

))) ;

c red i tAccount ( numclient , numcl ient ∗ 100 ) ;moveToLivret ( numclient , numcl ient ∗ 100 − 500 ) ;

EXCEPTIONWHEN DUP VAL ON INDEX THEN

DBMS OUTPUT. PUT LINE( ’ This account has a l ready been opened . ’ ) ;END;/

CREATE OR REPLACE PROCEDURE openAccounts ISCURSOR C IS

SELECT numcli FROM CLIENT;ROW C%rowtype ;

BEGINFOR ROW IN C LOOP

openAccount (ROW. numcli ) ;END LOOP;COMMIT;


DBMS OUTPUT. PUT LINE( ’An e r r o r has occurred . ’ ) ;ROLLBACK;

END;/

CALL openAccounts ( ) ;

CREATE OR REPLACE PROCEDUREaf f i cheDescendance ( numpersonne NUMBER) IS

CURSOR C ISSELECT ∗FROM PERSONNEWHERE pere = numpersonneOR mere = numpersonne ;

ROW C%rowType ;BEGIN

FOR ROW IN C LOOPDBMS OUTPUT. PUT LINE( row .nom | | ’ ’ | | row . prenom ) ;a f f i cheDescendance (ROW. numpers ) ;

END LOOP;END;/

105


−− Exercice 1

CREATE OR REPLACE PROCEDURE a f f i c h e C l i e n t ( unCl ient CLIENT%rowtype ) ISBEGIN

DBMS OUTPUT. PUT LINE( ’ C l i en t ’ | | unCl ient . prenomcl i | | ’ ’ | | unCl ient . nomCli ) ;END;/

CREATE OR REPLACE PROCEDURE aff icheCompte (unCompte COMPTECLIENT%rowtype ) ISBEGIN

DBMS OUTPUT. PUT LINE( ’∗ Compte ’ | | unCompte . numCli | | ’− ’ | | unCompte . numccl ) ;END;/

CREATE OR REPLACE PROCEDURE af f i cheComptesC l i ent s ISCURSOR c l i e n t s IS

SELECT ∗FROM CLIENT;

unCl ient c l i e n t s%rowtype ;CURSOR comptes ( numcl ient CLIENT. numcli%type ) IS

SELECT ∗FROM COMPTECLIENTWHERE numcli = numclient ;

unCompte c l i e n t s%rowtype ;BEGIN

FOR unCl ient IN c l i e n t s LOOPa f f i c h e C l i e n t ( unCl ient ) ;FOR unCompte IN comptes ( unCl ient . numcli ) LOOP

aff icheCompte (unCompte ) ;END LOOP;

END LOOP;END;/

SET SERVEROUTPUT ON SIZE 1000000

c a l l a f f i cheComptesC l i ent s ( ) ;

−− Exercice 2

CREATE OR REPLACE PROCEDURE a f f i ch eOpe ra t i on ( uneOperation OPERATION%rowtype ) ISBEGIN

DBMS OUTPUT. PUT LINE( ’∗ ∗ Operation ’ | | uneOperation . numOper | | ’ , montant : ’ | | uneOperation . montantOper ) ;END;/

CREATE OR REPLACE PROCEDURE af f i cheOperComptesCl ients ISCURSOR c l i e n t s IS

SELECT ∗FROM CLIENT;

unCl ient c l i e n t s%rowtype ;CURSOR comptes ( numcl ient CLIENT. numcli%type ) IS

SELECT ∗FROM COMPTECLIENTWHERE numcli = numclient ;

unCompte c l i e n t s%rowtype ;CURSOR opera t i on s

( numcl ient CLIENT. numcli%type ,numcompte COMPTECLIENT. numccl%type ) IS

SELECT ∗FROM OPERATIONWHERE numcli = numclientAND numccl = numcompte ;

uneOperation ope ra t i on s%rowtype ;BEGIN

FOR unCl ient IN c l i e n t s LOOPa f f i c h e C l i e n t ( unCl ient ) ;FOR unCompte IN comptes ( unCl ient . numcli ) LOOP

aff icheCompte (unCompte ) ;FOR uneOperation IN ope ra t i on s ( unCl ient . numcli , unCompte . numccl ) LOOP

a f f i ch eOpe ra t i on ( uneOperation ) ;END LOOP;

END LOOP;END LOOP;

END;/

c a l l a f f i cheOperComptesCl ients ( ) ;

106

3.16 Triggers

−− I l convient d ’ abord de modif ier quelque peu l ’ organisat ion des−− donnees , on ajoute par exemple dans la t a b l e MODULE l e nombre−− d ’ e tud iants i n s c r i t s


CREATE TABLE ETUDIANT(numEtud number ,nom varchar2 (40 ) ,prenom varchar2 (40 ) ,da t ena i s s date ,c i v i l i t e varchar2 ( 4 ) ,patronyme varchar2 (40 ) ,numsecu varchar2 (15) NOT NULL,moyenne NUMBER DEFAULT NULL) ;

CREATE TABLE MODULE(codMod number ,nomMod varchar2 (15 ) ,ef fecMax number DEFAULT 30 ,e f f e c number default 0 ) ;


CREATE TABLE INSCRIPTION(numEtud number ,codMod number ,date Insc date default sysdate ) ;










ALTER TABLE PREREQUIS ADD(CONSTRAINT fk prerequ i s codmodFOREIGN KEY (codMod)REFERENCES MODULE(codMod ) ,CONSTRAINT f k pre requ i s codmodprereqFOREIGN KEY ( codModPrereq )REFERENCES MODULE(codMod ) ) ;


ALTER TABLE RESULTAT ADD

107

(CONSTRAINT f k r e su l t a t examenFOREIGN KEY (codMod , codExam)REFERENCES EXAMEN(codMod , codExam ) ,CONSTRAINT f k r e s u l t a t i n s c r i p t i o nFOREIGN KEY (codMod , numEtud)REFERENCES INSCRIPTION(codMod , numEtud ) ) ;








) ;

−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−− Contrainte 1 −−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−

CREATE OR REPLACE TRIGGER beforeUpdateFERPrerequisBEFORE UPDATE ON PREREQUISFOR EACH ROWBEGIN

IF ( : new . noteMin < : o ld . noteMin ) THEN: new . noteMin := : o ld . noteMin ;

END IF ;END;/

−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−− Contrainte 2 −−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−

CREATE OR REPLACE PROCEDURE i n c r E f f e c ( module NUMBER) ISBEGIN

UPDATE MODULE SET e f f e c = e f f e c + 1 WHERE codmod = module ;END;/

−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−

CREATE OR REPLACE PROCEDURE dec rE f f e c ( module NUMBER) ISBEGIN

UPDATE MODULE SET e f f e c = e f f e c − 1 WHERE codmod = module ;END;/

−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−

CREATE OR REPLACE TRIGGER BeforeInsertFERModuleBEFORE INSERT ON MODULEFOR EACH ROWBEGIN

: new . e f f e c := 0 ;END;/

−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−

CREATE OR REPLACE TRIGGER a f t e r Inse r tFERInscAFTER INSERT ON INSCRIPTIONFOR EACH ROWBEGIN

i n c r E f f e c ( : new . codmod ) ;END;/

−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−

108

−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−

CREATE OR REPLACE TRIGGER afterDeleteFERInscAFTER DELETE ON INSCRIPTIONFOR EACH ROWBEGIN

dec rE f f e c ( : o ld . codmod ) ;END;/

−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−

CREATE OR REPLACE TRIGGER afterUpdateFERInscAFTER UPDATE ON INSCRIPTIONFOR EACH ROWBEGIN

dec rE f f e c ( : o ld . codmod ) ;i n c r E f f e c ( : new . codmod ) ;

END;/

−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−

DROP VIEW modulesDisponib les ;

CREATE VIEW modulesDisponib les ASSELECT codmodFROM MODULEWHERE e f f e c < effecMax ;

−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−

CREATE OR REPLACE TRIGGER beforeInsertUpdateFERInscBEFORE INSERT OR UPDATE ON INSCRIPTIONFOR EACH ROWDECLARE

nbLignes NUMBER;BEGIN

SELECT count (∗ ) INTO nbLignesFROM modulesDisponib lesWHERE codmod = : new . codmod ;IF ( nbLignes = 0) THEN

RAISE APPLICATION ERROR(−20001 , ’ Plus de p l a c e s d i p o n i b l e s . ’ ) ;END IF ;

END;/

−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−− Contrainte 3 −−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−

DROP VIEW examensPoss ib les ;

CREATE VIEW examensPoss ib les ASSELECT codModFROM MODULE MWHERE

(SELECT COUNT(∗ )FROM INSCRIPTION IWHERE I . codmod = M. codmod) > 0 ;

−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−

CREATE OR REPLACE TRIGGER beforeInsertUpdateFERExamBEFORE INSERT OR UPDATE ON EXAMENFOR EACH ROWDECLARE


SELECT count (∗ ) INTO nbLignesFROM examensPoss ib lesWHERE codMod = : new . codmod ;

IF ( nbLignes = 0) THENRAISE APPLICATION ERROR(−20002 , ’ Pas d” e l e v e dans ce module . ’ ) ;

END IF ;END;/

109

−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−− Contrainte 4 −−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−

DROP VIEW etudiantsExamens ;

CREATE VIEW etudiantsExamens ASSELECT I . numetud , E. codmod , E. codexamFROM INSCRIPTION I , EXAMEN EWHERE I . codmod = E. codmodAND I . date Insc < E. dateExam ;

−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−

CREATE OR REPLACE TRIGGER beforeInsertUpdateFERResultBEFORE INSERT OR UPDATE ON RESULTATFOR EACH ROWDECLARE


SELECT count (∗ ) INTO nbLignesFROM etudiantsExamensWHERE numetud = : new . numetudAND codmod = : new . codmodAND codexam = : new . codexam ;

IF ( nbLignes = 0) THENRAISE APPLICATION ERROR(−20002 , ’Examen a n t e r i e u r a l ” i n s c r i p t i o n dans l e module . ’ ) ;

END IF ;END;/

−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−− Contrainte 5 −−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−

−− On cree une t a b l e temporaire contenant l e s memes va leurs que prerequis ,−− On la met a jour AVANT la t a b l e prerequis pour v e r i f i e r que l ’ i n s e r t i o n−− ne c o n s t r u i t pas de c i r c u i t .

DROP TABLE MIRRORPREREQ;

CREATE TABLE MIRRORPREREQ(codmod NUMBER,codmodprereq NUMBER,noteMin NUMBER) ;

−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−

CREATE OR REPLACE FUNCTIONfindModule ( root number , moduleToFind number)RETURN BOOLEAN

ISCURSOR C IS

SELECT codmodFROM MIRRORPREREQWHERE codmodprereq = root ;

SON C%rowtype ;BEGIN

FOR SON IN C LOOPIF

( son . codmod = moduleToFind ORf indModule ( son . codmod , moduleToFind ) )

THENRETURN TRUE;

END IF ;END LOOP;RETURN FALSE;

END;/

−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−

CREATE OR REPLACE PROCEDUREins e r tMi r r o rPre r eq ( codmodValue NUMBER, codmodprereqValue NUMBER, note NUMBER) IS

BEGININSERT INTO MIRRORPREREQ

(codmod , codmodprereq , noteMin )VALUES

( codmodValue , codmodprereqValue , note ) ;END;/

110

−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−

CREATE OR REPLACE PROCEDUREde l e t eMi r ro rPre r eq ( codmodValue NUMBER, codmodprereqValue NUMBER) IS

BEGINDELETE FROM MIRRORPREREQ

WHERE codmod = codmodValueAND codmodprereq = codmodprereqValue ;

END;/

−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−

CREATE OR REPLACE PROCEDUREupdateMirrorPrereq( codmodValue NUMBER,codmodNewValue NUMBER,codmodprereqValue NUMBER,codmodprereqNewValue NUMBER,newNote NUMBER) IS

BEGINUPDATE MIRRORPREREQ SET

codmod = codmodNewValue ,codmodprereq = codmodprereqNewValue ,noteMin = newNote

WHERE codmod = codmodValueAND codmodprereq = codmodprereqValue ;

END;/

−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−

CREATE OR REPLACE TRIGGER afterDeleteFERPrereqAFTER DELETE ON PREREQUISFOR EACH ROWBEGIN

de l e t eMi r ro rPre r eq ( : o ld . codmod , : o ld . codmodprereq ) ;END;/

−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−

CREATE OR REPLACE TRIGGER beforeInsertUpdateFERPrereqBEFORE INSERT OR UPDATE ON PREREQUISFOR EACH ROW

BEGINIF INSERTING THEN

i n s e r tMi r r o rPre r eq ( : new . codmod , : new . codmodprereq , : new . noteMin ) ;END IF ;IF UPDATING THEN

updateMirrorPrereq ( : o ld . codmod , : new . codmod ,: o ld . codmodprereq , : new . codmodprereq , : new . noteMin ) ;

END IF ;IF ( findModule ( : new . codmod , : new . codmod ) ) THEN

IF INSERTING THENde l e t eMi r ro rPre r eq ( : new . codmod , : new . codmodprereq ) ;

END IF ;IF UPDATING THEN

updateMirrorPrereq ( : new . codmod , : o ld . codmod ,: new . codmodprereq , : o ld . codmodprereq , : o ld . noteMin ) ;

END IF ;RAISE APPLICATION ERROR(−20003 , ’ C i r cu i t dans p r e r equ i s . ’ ) ;

END IF ;END;/

−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−− Contrainte 6 −−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−

CREATE OR REPLACE FUNCTIONc h e c k I n s c r i p t i o n ( etud NUMBER, mod NUMBER)RETURN BOOLEAN

ISCURSOR prereq IS

SELECT noteMin , codmodprereqFROM MIRRORPREREQWHERE codmod = mod ;

p prereq%rowtype ;nbLignes NUMBER;

BEGIN

111

FOR p IN prereq LOOPSELECT count (∗ ) INTO nbLignes

FROM RESULTATWHERE codmod = p . codmodprereqAND numetud = etudAND note < p . noteMin ;

IF ( nbLignes = 0) THENRETURN FALSE;

END IF ;END LOOP;RETURN TRUE;

END;/

−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−

CREATE OR REPLACE TRIGGER beforeInsertUpdateFERInscBEFORE INSERT OR UPDATE ON INSCRIPTIONFOR EACH ROWDECLARE


SELECT count (∗ ) INTO nbLignesFROM modulesDisponib lesWHERE codmod = : new . codmod ;IF ( nbLignes = 0) THEN

RAISE APPLICATION ERROR(−20001 , ’ Plus de p l a c e s d i p o n i b l e s . ’ ) ;END IF ;IF (NOT( c h e c k I n s c r i p t i o n ( : new . numetud , : new . codmod ) ) ) THEN

RAISE APPLICATION ERROR(−20004 , ’ Pr e r equ i s non s a t i s f a i t . ’ ) ;END IF ;

END;/

−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−− Contrainte 7 −−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−

−− La auss i un probleme se pose , on ne peut pas f a i r e de requete sur−− l a t a b l e r e s u l t a t , comme de plus , on t i e n t a prendre pour chaque etudiant−− l e mei l l eure note dans chaque module , on cree une t a b l e temporaire contenant−− l e s notes obetnues par l e s e l e v e s .

DROP TABLE MIRRORRESULT;

CREATE TABLE MIRRORRESULT( numetud NUMBER,codmod NUMBER,codexam NUMBER,note NUMBER,PRIMARY KEY(numetud , codmod , codexam )) ;

−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−

DROP VIEW MEILLEURENOTE;

CREATE VIEW MEILLEURENOTE ASSELECT numetud , codmod , MAX( note ) AS noteMaxFROM MIRRORRESULTGROUP BY numetud , codmod ;

−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−

DROP VIEW NOMBREINSCRIPTIONs;

CREATE VIEW NOMBREINSCRIPTIONS ASSELECT numetud ,

(SELECT COUNT(∗ )FROM INSCRIPTION IWHERE I . numetud = E. numetud) AS n b I n s c r i p t i o n s

FROM ETUDIANT E;

−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−

DROP VIEW NOMBRENOTES;

CREATE VIEW NOMBRENOTES ASSELECT numetud ,

112

(SELECT COUNT(∗ ) AS nbNotesFROM MEILLEURENOTE MWHERE M. numetud = E. numetud) AS nbNotes

FROM ETUDIANT E;

−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−

CREATE OR REPLACE PROCEDUREupdateMoyenne ( etud NUMBER)

ISnbNotes NUMBER;n b I n s c r i p t i o n s NUMBER;

BEGINSELECT nbNotes INTO nbNotes

FROM NOMBRENOTESWHERE numetud = etud ;

SELECT n b I n s c r i p t i o n s INTO nbINsc r ip t i onsFROM NOMBREINSCRIPTIONSWHERE numetud = etud ;

IF ( nbNotes = n b I n s c r i p t i o n s ) THENUPDATE ETUDIANT SET moyenne =

(SELECT AVG( noteMax )FROM MEILLEURENOTEWHERE numetud = etud)

WHERE numetud = etud ;ELSE

UPDATE ETUDIANT SETmoyenne = NULL

WHERE numetud = etud ;END IF ;

END;/

−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−

CREATE OR REPLACE TRIGGER a f ter Inser tFERResu l tAFTER INSERT ON RESULTATFOR EACH ROW

BEGININSERT INTO MIRRORRESULT VALUES( : new . numetud , : new . codmod , : new . codexam , : new . note ) ;updateMoyenne ( : new . numetud ) ;

END;/

−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−

CREATE OR REPLACE TRIGGER afterUpdateFERResultAFTER UPDATE ON RESULTATFOR EACH ROW

BEGINUPDATE MIRRORRESULT SET

numetud = : new . numetud ,codmod = : new . codmod ,codexam = : new . codexam ,note = : new . note

WHERE numetud = : o ld . numetudAND codmod = : o ld . codmodAND codexam = : old . codexam ;

updateMoyenne ( : new . numetud ) ;END;/

−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−

CREATE OR REPLACE TRIGGER afterDeleteFERResultAFTER DELETE ON RESULTATFOR EACH ROWBEGIN

DELETE FROM MIRRORRESULTWHERE numetud = : new . numetudAND codmod = : new . codmodAND codexam = : new . codexam ;

updateMoyenne ( : new . numetud ) ;END;/

−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−− Contrainte 9 −−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−

113

−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−

CREATE OR REPLACE FUNCTIONcheckAl lStudentsRETURN BOOLEAN

ISCURSOR C IS

SELECT numetud , codmodFROM INSCRIPTION ;

e C%rowtype ;BEGIN

FOR e IN C LOOPIF (NOT( c h e c k I n s c r i p t i o n ( e . numetud , e . codmod ) ) ) THEN

RETURN FALSE;END IF ;

END LOOP;RETURN TRUE;

END;/

−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−

CREATE OR REPLACE TRIGGER BeforeUpdateFERModuleBEFORE UPDATE ON MODULEFOR EACH ROWBEGIN

IF ( : new . ef fecmax < : new . e f f e c ) THENRAISE APPLICATION ERROR(−20005 ,

’L e f f e c t i f ne peut e t r e en dessous de ’ | | : new . e f f e c ) ;END IF ;

END;/

−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−− Contrainte 8 −−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−

CREATE OR REPLACE TRIGGER beforeInsertUpdateFERPrereqBEFORE INSERT OR UPDATE ON PREREQUISFOR EACH ROW

BEGINIF INSERTING THEN

i n s e r tMi r r o rPre r eq ( : new . codmod , : new . codmodprereq , : new . noteMin ) ;END IF ;IF UPDATING THEN

updateMirrorPrereq ( : o ld . codmod , : new . codmod ,: o ld . codmodprereq , : new . codmodprereq , : new . noteMin ) ;

END IF ;IF ( findModule ( : new . codmod , : new . codmod ) ) THEN




END IF ;RAISE APPLICATION ERROR(−20003 , ’ C i r cu i t dans p r e r equ i s . ’ ) ;

END IF ;IF (NOT( checkAl lStudents ( ) ) ) THEN




END IF ;RAISE APPLICATION ERROR(−20006 , ’ Imposs ib l e de diminuer c e t t e note . ’ ) ;

END IF ;END;/

−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−− Quelques i n s e r t i o n s pour t e s t e r −−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−

INSERT INTO ETUDIANT VALUES((SELECT nvl (MAX(numEtud ) , 0) + 1 FROM ETUDIANT) ,’ Four i e r ’ ,’ Joseph ’ ,t o date ( ’ 21031768 ’ , ’ddmmyyyy ’ ) ,’Mr ’ ,NULL,’ 168031234567890 ’ ,NULL) ;

114

INSERT INTO MODULE(codMod , nomMod)VALUES((SELECT nvl (MAX(codMod ) , 0) + 1 FROM MODULE) ,’ Maths ’) ;

INSERT INTO INSCRIPTION(codMod , numEtud)VALUES((SELECT numEtud FROM ETUDIANT WHERE nom = ’ Four i e r ’ ) ,(SELECT codMod FROM MODULE WHERE nomMod = ’ Maths ’ )) ;

INSERT INTO EXAMEN VALUES((SELECT codMod FROM MODULE WHERE nomMod = ’ Maths ’ ) ,1 ,t o date ( ’ 02012008 ’ , ’ddmmyyyy ’ )) ;

INSERT INTO RESULTAT VALUES((SELECT codMod FROM MODULE WHERE nomMod = ’ Maths ’ ) ,1 ,(SELECT numEtud FROM ETUDIANT WHERE nom = ’ Four i e r ’ ) ,19 ) ;

UPDATE RESULTAT SETnote = 20wHERE

numEtud = (SELECT numEtud FROM ETUDIANT WHERE nom = ’ Four ie r ’ )AND codMod = (SELECT codMod FROM MODULE WHERE nomMod = ’ Maths ’ )AND codExam = 1 ;

INSERT INTO MODULE VALUES(2 , ’ Algo ’ , 30 , 2 2 ) ;

INSERT INTO PREREQUIS VALUES(1 , 2 , 1 0 ) ;

INSERT INTO PREREQUIS VALUES(2 , 1 , 1 0 ) ;

UPDATE PREREQUIS SET noteMin = 2 ;

INSERT INTO EXAMEN VALUES (2 , 1 , sysdate ) ;

115

3.17 Packages

CREATE OR REPLACE PACKAGE BODY g e s t i o n a r b r e IS

cur so r f e u i l l e s re turn personne%rowtype ISSELECT ∗ FROM PERSONNE;

PROCEDURE ajoutePersonne (nom personne .nom%type ,prenom personne . prenom%type , pere personne . pere%type ,mere personne . mere%type ) IS

BEGININSERT INTO PERSONNE VALUES ((SELECT nvl (MAX( numpers ) , 0) + 1 FROM PERSONNE) ,nom, prenom , pere , mere ) ;

END;

FUNCTION descendDe ( numpers personne . numpers%type ,descendant personne . numpers%type ) RETURN BOOLEAN IS

BEGINIF ( descendant IS NULL) THEN

RETURN FALSE;ELSIF ( descendant = numpers ) THEN

RETURN TRUE;ELSE

DECLAREpers PERSONNE%rowtype ;

BEGINSELECT ∗ INTO pers

FROM PERSONNEWHERE numpers = descendant ;

RETURN descendDe ( numpers , pers . pere )OR descendDe ( numpers , pers . mere ) ;

END;END IF ;

END;

procedure modi f i eParents ( pers personne . numpers%type ,numPere personne . pere%type , numMere personne . mere%type ) IS

BEGINIF ( descendDe ( pers , numPere ) OR descendDe ( pers , numMere ) ) THEN

RAISE CIRCUIT ;END IF ;UPDATE PERSONNE SET pere = numPere , mere = numMereWHERE numPers = pers ;

END;END;/

CALL ges t i on Arbre . modi f i eParents (20 , 14 , 1 5 ) ;

116

3.18 Revisions

−− Preparat i f s . . .

DROP TABLE MIRRORPERSONNE;

CREATE TABLE MIRRORPERSONNE(numpers NUMBERPRIMARY KEY,pere NUMBER,mere NUMBER) ;

CREATE OR REPLACE TRIGGER miseAJourMirrorPersonneBEFORE UPDATE OR INSERT OR DELETE ON PERSONNEFOR EACH ROWBEGIN

IF DELETING OR UPDATING THENDELETE FROM MIRRORPERSONNEWHERE numpers = : o ld . numpers ;


INSERT INTO MIRRORPERSONNE VALUES( : new . numpers , : new . pere , : new . mere ) ;

END IF ;END;/

DROP TABLE MIRRORMARIAGE;

CREATE TABLE MIRRORMARIAGE(nummari NUMBER,numfemme NUMBER,datemariage DATE,da ted ivorce DATE) ;

CREATE OR REPLACE TRIGGER miseAJourMirrorMariageBEFORE UPDATE OR INSERT OR DELETE ON MARIAGEFOR EACH ROWBEGIN

IF DELETING OR UPDATING THENDELETE FROM MIRRORMARIAGEWHERE nummari = : o ld . nummariAND numfemme = : old . numfemmeAND datemariage = : o ld . datemariage ;


INSERT INTO MIRRORMARIAGE VALUES( : new . nummari , : new . numfemme , : new . datemariage , : new . dated ivorce ) ;

END IF ;END;/

−− Contrainte 1

ALTER TABLE PERSONNE ADD CONSTRAINT c k p a r e n t s d i f f e r e n t s CHECK( pere <> mere ) ;

−− Contrainte 2

CREATE OR REPLACE PACKAGE c o n t r a i n t e C i r c u i t ISCIRCUIT EXCEPTION;PROCEDURE v e r i f i e C i r c u i t ( pers personne . numpers%type ) ;FUNCTION descendDe ( numpers personne . numpers%type ,

descendant personne . numpers%type ) RETURN BOOLEAN;END;/

CREATE OR REPLACE TRIGGER v e r i f i e C o n t r a i n t e C i r c u i tAFTER UPDATE OR INSERT ON PERSONNEFOR EACH ROWBEGIN

c o n t r a i n t e C i r c u i t . v e r i f i e C i r c u i t ( : new . numPers ) ;END;/

CREATE OR REPLACE PACKAGE BODY c o n t r a i n t e C i r c u i t IS

FUNCTION descendDe ( numpers personne . numpers%type ,descendant personne . numpers%type ) RETURN BOOLEAN IS

BEGINIF ( descendant IS NULL) THEN

RETURN FALSE;ELSIF ( descendant = numpers ) THEN

RETURN TRUE;ELSE

DECLARE

117

pers MIRRORPERSONNE%rowtype ;BEGIN

SELECT ∗ INTO persFROM MIRRORPERSONNEWHERE numpers = descendant ;

RETURN descendDe ( numpers , pers . pere )OR descendDe ( numpers , pers . mere ) ;

END;END IF ;

END;

PROCEDURE v e r i f i e C i r c u i t ( pers personne . numpers%type ) ISl i g n e mirrorpersonne%rowtype ;

BEGINSELECT ∗ INTO LIGNE

FROM mirrorpersonneWHERE numpers = pers ;

IF ( descendDe ( pers , l i g n e . pere ) OR descendDe ( pers , l i g n e . mere ) ) THENRAISE CIRCUIT ;

END IF ;END;

END;/

−− Contrainte 3

ALTER TABLE MARIAGE ADD CONSTRAINT ck date s mar iage CHECK( dateDivorce IS NULL OR dateMariage <= dateDivorce ) ;

−− Contrainte 4

CREATE OR REPLACE PACKAGE cont ra in te sMar iage s ISmariagesSuperposes EXCEPTION;PROCEDURE ver i f i eMar i ag e sSupe rpo s e s ( nouveauMariage mariage%rowtype ) ;

END cont ra in te sMar iage s ;/

CREATE OR REPLACE TRIGGER v e r i f i e C o n t r a i n t e s M a r i a g e sBEFORE UPDATE OR INSERT ON MARIAGEFOR EACH ROWDECLARE

nouveauMariage MARIAGE%rowtype ;BEGIN

nouveauMariage . numMari := : new . numMari ;nouveauMariage .numFemme := : new .numFemme;nouveauMariage . dateMariage := : new . dateMariage ;nouveauMariage . dateDivorce := : new . dateDivorce ;cont ra in te sMar iage s . v e r i f i eMar i ag e sSupe rpo s e s ( nouveauMariage ) ;

END;/

CREATE OR REPLACE PACKAGE BODY cont ra in te sMar iage s IS

FUNCTION seSuperposent (m1 mirrorMariage%rowtype , m2 mirrorMariage%rowtype ) RETURN BOOLEAN ISBEGIN

IF (m1. nummari <> m2. nummari OR m1. numfemme <> m2. numfemme) THENRETURN FALSE;

END IF ;RETURN NOT(

(m2. dated ivorce IS NOT NULL AND m1. dateMariage <= m2. dateDivorce )OR (m1. dated ivorce IS NOT NULL AND m2. dateMariage <= m1. dateDivorce )) ;

END;

PROCEDURE ver i f i eMar i ag e sSupe rpo s e s ( nouveauMariage mariage%rowtype ) ISCURSOR autresMar iages IS

SELECT ∗ FROM MIRRORMARIAGEWHERE numMari = nouveauMariage . numMariOR numFemme = nouveauMariage .numFemme;

autreMariage autresMar iages%ROWTYPE;BEGIN

FOR autreMariage IN autresMar iages LOOPIF ( seSuperposent ( nouveauMariage , autreMariage ) ) THEN

RAISE mariagesSuperposes ;END IF ;

END LOOP;END;

END cont ra in te sMar iage s ;/

−− Contraintes 5 et 6

CREATE OR REPLACE package cont ra inte sTrans ISt rans EXCEPTION;

PROCEDURE ver i f i ePe r eMere ( nouve l l ePersonne MIRRORPERSONNE%rowtype ) ;PROCEDURE verif ieMariFemme ( nouveauMariage MARIAGE%rowtype ) ;

118

end cont ra inte sTrans ;/

CREATE OR REPLACE TRIGGER pereMereAFTER UPDATE OR INSERT ON PERSONNEFOR EACH ROWDECLARE

nouve l l ePersonne MIRRORPERSONNE%rowtype ;BEGIN

nouve l l ePersonne . numpers := : new . numpers ;nouve l l ePersonne . pere := : new . pere ;nouve l l ePersonne . mere := : new . mere ;cont ra inte sTrans . v e r i f i ePe r eMere ( nouve l l ePersonne ) ;

END;/

CREATE OR REPLACE TRIGGER mariFemmeAFTER UPDATE OR INSERT ON MARIAGEFOR EACH ROWDECLARE


nouveauMariage . numMari := : new . numMari ;nouveauMariage .numFemme := : new .numFemme;nouveauMariage . dateMariage := : new . dateMariage ;nouveauMariage . dateDivorce := : new . dateDivorce ;cont ra inte sTrans . verif ieMariFemme ( nouveauMariage ) ;

END;/

CREATE OR REPLACE package BODY contra inte sTrans IS

PROCEDURE ver i f i ePe r eMere ( nouve l l ePersonne MIRRORPERSONNE%rowtype ) ISnb INT ;

BEGINSELECT COUNT(∗ ) INTO nb

FROM MIRRORPERSONNEWHERE pere = nouve l l ePersonne . mereOR mere = nouve l l ePersonne . pere ;

IF (nb <> 0) THENRAISE TRANS;

END IF ;SELECT COUNT(∗ ) INTO nb

FROM MIRRORMARIAGEWHERE numMari = nouve l l ePersonne . mereOR numFemme = nouve l l ePersonne . pere ;


END IF ;END;

PROCEDURE verif ieMariFemme ( nouveauMariage MARIAGE%rowtype ) ISnb INT ;

BEGINSELECT COUNT(∗ ) INTO nb

FROM MIRRORMARIAGEWHERE numMari = nouveauMariage .numFemmeOR numFemme = nouveauMariage . numMari ;


END IF ;SELECT COUNT(∗ ) INTO nb

FROM MIRRORPERSONNEWHERE pere = nouveauMariage .numFemmeOR mere = nouveauMariage . numMari ;


END IF ;END;

END cont ra inte sTrans ;/

−− Contrainte 7

CREATE OR REPLACE PACKAGE contrainteMariageConsanguin ISMariageConsanguin EXCEPTION;

PROCEDURE ver i f i eMar iageConsangu in ( nouveauMariage MARIAGE%rowtype ) ;END contrainteMariageConsanguin ;/

CREATE OR REPLACE TRIGGER mariageConsanguinAFTER UPDATE OR INSERT ON MARIAGEFOR EACH ROWDECLARE


119

nouveauMariage . numMari := : new . numMari ;nouveauMariage .numFemme := : new .numFemme;nouveauMariage . dateMariage := : new . dateMariage ;nouveauMariage . dateDivorce := : new . dateDivorce ;contrainteMariageConsanguin . ver i f i eMar iageConsangu in ( nouveauMariage ) ;

END;/

CREATE OR REPLACE PACKAGE BODY contrainteMariageConsanguin IS

FUNCTION pere (p PERSONNE. numpers%type ) RETURN PERSONNE. numpers%type ISnumPere PERSONNE. numpers%type ;

BEGINSELECT pere INTO numPereFROM MIRRORPERSONNEWHERE numpers = p ;RETURN numPere ;


RETURN NULL;END;

FUNCTION mere (p PERSONNE. numpers%type ) RETURN PERSONNE. numpers%type ISnumMere PERSONNE. numpers%type ;

BEGINSELECT mere INTO numMereFROM MIRRORPERSONNEWHERE numpers = p ;RETURN numMere ;


RETURN NULL;END;

FUNCTION rechercheAncetreCommun ( a PERSONNE. numpers%type ,b PERSONNE. numpers%type ) RETURN BOOLEAN IS

BEGINIF ( a IS NULL) THEN

RETURN FALSE;ELSE

RETURN ( c o n t r a i n t e C i r c u i t . descendDe (a , b ) ) OR rechercheAncetreCommun ( pere ( a ) , b ) OR rechercheAncetreCommun ( mere ( a ) , b ) ;END IF ;

END;

PROCEDURE ver i f i eMar iageConsangu in ( nouveauMariage MARIAGE%rowtype ) ISBEGIN

IF ( rechercheAncetreCommun ( nouveauMariage . numMari , nouveauMariage .numFemme) ) THENRAISE MariageConsanguin ;

END IF ;END;

END contrainteMariageConsanguin ;/

120

Annexe A

Scripts de creation de bases

A.1 Livraisons Sans contraintes

Attention : Le numero de livraison est une cle secondaire, c’est-a-dire un numero unique etant donne un fournisseur.CREATE TABLE PRODUIT(numprod number ,nomprod varchar2 ( 3 0 ) ) ;

CREATE TABLE FOURNISSEUR( numfou number ,nomfou varchar2 ( 3 0 ) ) ;

CREATE TABLE PROPOSER( numfou number ,numprod number ,p r ix number ) ;

CREATE TABLE LIVRAISON( numfou number ,numli number ,d a t e l i date default sysdate) ;

CREATE TABLE DETAILLIVRAISON( numfou number ,numli number ,numprod number ,qte number ) ;

121

A.2 Modules et prerequis

les modules sont repertories dans une table, et les modules pre-requis pour s’y inscrire (avec la note minimale) setrouvent dans la table prerequis. Une ligne de la table PREREQUIS nous indique que pour s’inscrire dans le modulenumero numMod, il faut avoir eu au moins noteMin au module numModPrereq.CREATE TABLE MODULE(numMod number primary key ,nomMod varchar2 (30)) ;

CREATE TABLE PREREQUIS(numMod number r e f e r e n c e s MODULE(numMod) ,numModPrereq number r e f e r e n c e s MODULE(numMod) ,noteMin number(2 ) DEFAULT 10 NOT NULL ,PRIMARY KEY(numMod, numModPrereq )) ;

INSERT INTO MODULE VALUES (1 , ’ Oracle ’ ) ;INSERT INTO MODULE VALUES (2 , ’C++’ ) ;INSERT INTO MODULE VALUES (3 , ’C ’ ) ;INSERT INTO MODULE VALUES (4 , ’ Algo ’ ) ;INSERT INTO MODULE VALUES (5 , ’ Merise ’ ) ;INSERT INTO MODULE VALUES (6 , ’PL/SQL Oracle ’ ) ;INSERT INTO MODULE VALUES (7 , ’mySQL ’ ) ;

INSERT INTO MODULE VALUES (8 , ’ Algo avancA c©e ’ ) ;

INSERT INTO PREREQUIS (numMod, numModPrereq ) VALUES (1 , 5 ) ;INSERT INTO PREREQUIS (numMod, numModPrereq ) VALUES (2 , 3 ) ;INSERT INTO PREREQUIS VALUES (6 , 1 , 1 2 ) ;INSERT INTO PREREQUIS (numMod, numModPrereq ) VALUES (6 , 5 ) ;INSERT INTO PREREQUIS (numMod, numModPrereq ) VALUES (8 , 5 ) ;INSERT INTO PREREQUIS (numMod, numModPrereq ) VALUES (7 , 5 ) ;

122

A.3 Geometrie

La table INTERVALLE contient des intervalles specifies par leurs bornes inferieure et superieure. Supprimer de latable intervalle tous les intervalles qui n’en sont pas avec une seule instruction.CREATE TABLE INTERVALLE( borneIn f NUMBER,

borneSup NUMBER,PRIMARY KEY ( borneInf , borneSup ) ) ;

CREATE TABLE RECTANGLE( xHautGauche NUMBER,yHautGauche NUMBER,xBasDroit NUMBER,yBasDroit NUMBER,PRIMARY KEY ( xHautGauche , yHautGauche , xBasDroit , yBasDroit ) ) ;

INSERT INTO INTERVALLE VALUES (2 , 5 6 ) ;INSERT INTO INTERVALLE VALUES (12 , 3 0 ) ;INSERT INTO INTERVALLE VALUES (2 , 3 ) ;INSERT INTO INTERVALLE VALUES (12 , 3 ) ;INSERT INTO INTERVALLE VALUES (8 , 2 7 ) ;INSERT INTO INTERVALLE VALUES (34 , 2 6 ) ;INSERT INTO INTERVALLE VALUES (5 , 1 0 ) ;INSERT INTO INTERVALLE VALUES (7 , 3 2 ) ;INSERT INTO INTERVALLE VALUES (0 , 3 0 ) ;INSERT INTO INTERVALLE VALUES (21 , 8 ) ;

INSERT INTO RECTANGLE VALUES (2 , 12 , 5 , 7 ) ;INSERT INTO RECTANGLE VALUES (2 , 12 , 1 , 1 3 ) ;INSERT INTO RECTANGLE VALUES (10 , 13 , 1 , 1 1 ) ;INSERT INTO RECTANGLE VALUES (10 , 13 , 10 , 1 1 ) ;INSERT INTO RECTANGLE VALUES (2 , 7 , 5 , 1 3 ) ;INSERT INTO RECTANGLE VALUES (21 , 73 , 15 , 2 2 ) ;INSERT INTO RECTANGLE VALUES (1 , 2 , 3 , 4 ) ;INSERT INTO RECTANGLE VALUES (1 , 5 , 3 , 2 ) ;INSERT INTO RECTANGLE VALUES (1 , 6 , 3 , 6 ) ;INSERT INTO RECTANGLE VALUES (4 , 2 , 1 , 4 ) ;INSERT INTO RECTANGLE VALUES (2 , 3 , 4 , 0 ) ;INSERT INTO RECTANGLE VALUES (5 , 4 , 2 , 1 ) ;

123

A.4 Livraisons

CREATE TABLE PRODUIT(numprod number ,nomprod varchar2 ( 3 0 ) ) ;

CREATE TABLE FOURNISSEUR( numfou number ,nomfou varchar2 ( 3 0 ) ) ;

CREATE TABLE PROPOSER( numfou number ,numprod number ,p r ix number NOT NULL) ;

CREATE TABLE LIVRAISON( numfou number ,numli number ,d a t e l i date default sysdate) ;

CREATE TABLE DETAILLIVRAISON( numfou number ,numli number ,numprod number ,qte number NOT NULL) ;

alter table produi t add constraint pk produi tPRIMARY KEY (numprod ) ;alter table f o u r n i s s e u r add constraint p k f o u r n i s s e u rPRIMARY KEY ( numfou ) ;alter table proposer add constraint pk proposerPRIMARY KEY ( numfou , numprod ) ;alter table l i v r a i s o n add constraint p k l i v r a i s o nPRIMARY KEY ( numfou , numli ) ;alter table d e t a i l l i v r a i s o n add constraint p k d e t a i l l i v r a i s o nPRIMARY KEY ( numfou , numli , numprod ) ;alter table proposer add constraint f k p r o p o s e r f o u r n i s s e u rFOREIGN KEY ( numfou ) REFERENCES f o u r n i s s e u r ( numfou ) ;alter table proposer add constraint f k p r o p o s e r p r o d u i tFOREIGN KEY (numprod) REFERENCES produi t (numprod ) ;alter table l i v r a i s o n add constraint f k l i v r a i s o nFOREIGN KEY ( numfou ) REFERENCES f o u r n i s s e u r ( numfou ) ;alter table d e t a i l l i v r a i s o n add constraint f k d e t a i l l i v r a i s o nFOREIGN KEY ( numfou , numli ) REFERENCES l i v r a i s o n ( numfou , numli ) ;alter table d e t a i l l i v r a i s o n add constraint f k d e t a i l l i v r a i s o n p r o p o s e rFOREIGN KEY ( numfou , numprod) REFERENCES proposer (numfou , numprod ) ;

INSERT INTO PRODUIT values (1 , ’Roue de s e cour s ’ ) ;

INSERT INTO PRODUIT values (2 , ’PoupA c©e Batman ’ ) ;INSERT INTO PRODUIT values (3 , ’ Cotons t i g e s ’ ) ;INSERT INTO PRODUIT values (4 , ’ Cornichons ’ ) ;

INSERT INTO FOURNISSEUR values (1 , ’ f 1 ’ ) ;INSERT INTO FOURNISSEUR values (2 , ’ f 2 ’ ) ;INSERT INTO FOURNISSEUR values (3 , ’ f 3 ’ ) ;INSERT INTO FOURNISSEUR values (4 , ’ f 4 ’ ) ;

INSERT INTO PROPOSER values (1 , 1 , 200 ) ;INSERT INTO PROPOSER values (1 , 2 , 1 5 ) ;INSERT INTO PROPOSER values (2 , 2 , 1 ) ;INSERT INTO PROPOSER values (3 , 3 , 2 ) ;

INSERT INTO LIVRAISON (numfou , numli ) values (1 , 1 ) ;INSERT INTO LIVRAISON (numfou , numli ) values (1 , 2 ) ;INSERT INTO LIVRAISON (numfou , numli ) values (3 , 1 ) ;

INSERT INTO DETAILLIVRAISON values (3 , 1 , 3 , 1 0 ) ;INSERT INTO DETAILLIVRAISON values (1 , 1 , 1 , 2 5 ) ;INSERT INTO DETAILLIVRAISON values (1 , 1 , 2 , 2 0 ) ;INSERT INTO DETAILLIVRAISON values (1 , 2 , 1 , 1 5 ) ;INSERT INTO DETAILLIVRAISON values (1 , 2 , 2 , 1 7 ) ;

124

A.5 Arbre genealogique

La table PERSONNE, le champ pere contient le numero du pere de la personne, le champ mere contient le numerode la mere de la personne.CREATE TABLE PERSONNE( numpers number PRIMARY KEY,nom varchar2 (30) NOT NULL,prenom varchar2 (30 ) ,pere REFERENCES PERSONNE( numpers ) ,mere REFERENCES PERSONNE( numpers )

) ;

INSERT INTO PERSONNE VALUES (1 , ’ de Montmirail , d i t l e Hardi ’ , ’ Godefroy ’ , NULL, NULL) ;INSERT INTO PERSONNE VALUES (16 , ’ET ’ , NULL, NULL, NULL) ;INSERT INTO PERSONNE VALUES (2 , ’ Le Croquant ’ , ’ Jacqou ’ , 1 , 1 6 ) ;INSERT INTO PERSONNE VALUES (3 , ’La F r i p o u i l l e ’ , ’ J a c q o u i l l e ’ , 1 , 1 6 ) ;INSERT INTO PERSONNE VALUES (4 , ’ Bush ’ , ’ Kate ’ , NULL, NULL) ;INSERT INTO PERSONNE VALUES (13 , ’ Granger ’ , ’ Hermione ’ , NULL, NULL) ;

INSERT INTO PERSONNE VALUES (5 , ’Du FA c©mur ’ , ’MA c©dor ’ , 3 ,4 ) ;

INSERT INTO PERSONNE VALUES (12 , ’ KobalevskaA¯a ’ , ’ S o f i a ’ , NULL, NULL) ;

INSERT INTO PERSONNE VALUES (6 , ’ Rieu ’ , ’AndrA c© ’ , NULL, NULL) ;INSERT INTO PERSONNE VALUES (7 , ’ Bontoutou ’ , ’Rex ’ , 6 , 4 ) ;INSERT INTO PERSONNE VALUES (8 , ’ D i j k s t r a ’ , ’ Edvard ’ , 2 , 1 3 ) ;INSERT INTO PERSONNE VALUES (9 , ’ Le ibn i z ’ , ’ Got t f r i e1d Wilhem ’ , 8 , 1 2 ) ;INSERT INTO PERSONNE VALUES (10 , ’ Bach ’ , ’ Johann Sebas t i en ’ , 5 , 1 2 ) ;INSERT INTO PERSONNE VALUES (17 , ’ Mathieu ’ , ’ M i r e i l l e ’ , NULL, NULL) ;INSERT INTO PERSONNE VALUES (11 , ’ Lemarchal ’ , ’ Gregory ’ , 10 , 1 7 ) ;INSERT INTO PERSONNE VALUES (15 , ’ Socrate ’ , NULL, 3 , 1 3 ) ;INSERT INTO PERSONNE VALUES (19 , ’ Leroy ’ , ’ Nolwen ’ , NULL, NULL) ;INSERT INTO PERSONNE VALUES (20 , ’ B a r t o l i ’ , ’ J e n n i f e r ’ , 9 , 1 9 ) ;INSERT INTO PERSONNE VALUES (21 , ’ Fabian ’ , ’ Lara ’ , 10 , 1 7 ) ;INSERT INTO PERSONNE VALUES (14 , ’ Stone ’ , ’ Sharon ’ , 15 , 2 0 ) ;INSERT INTO PERSONNE VALUES (18 , ’ Frege ’ , ’ E lod ie ’ , 7 , 1 3 ) ;

125

A.6 Comptes bancaires

DROP TABLE OPERATION;DROP TABLE TYPEOPERATION;DROP TABLE COMPTECLIENT;DROP TABLE TYPECCL;DROP TABLE PERSONNEL;DROP TABLE CLIENT;

CREATE TABLE CLIENT( numcli number ,

nomcli varchar2 (30 ) ,prenomcl i varchar2 (30 ) ,ad r e s s e varchar2 (60 ) ,t e l varchar (10)

) ;

CREATE TABLE PERSONNEL( numpers number ,nompers varchar2 (30 ) ,prenompers varchar2 (30 ) ,manager number ,s a l a i r e number

) ;

CREATE TABLE TYPECCL( numtypeccl number ,

nomtypeccl varchar2 (30)) ;

CREATE TABLE COMPTECLIENT( numcli number ,numccl number ,numtypeccl number ,d a t e c c l date default sysdate not null ,numpers number

) ;

CREATE TABLE TYPEOPERATION( numtypeoper number ,

nomtypeoper varchar2 (30)) ;

CREATE TABLE OPERATION( numcli number ,numccl number ,numoper number ,numtypeoper number ,dateoper date default sysdate not null ,montantoper number not null ,l i b e l o p e r varchar2 (30)

) ;

ALTER TABLE CLIENT ADD(CONSTRAINT p k c l i e n t PRIMARY KEY ( numcli ) ,CONSTRAINT ck te l ephone CHECK(LENGTH( t e l )=10)) ;

ALTER TABLE PERSONNEL ADD(CONSTRAINT pk personne l PRIMARY KEY ( numpers ) ,CONSTRAINT c k s a l a i r e CHECK(SALAIRE >= 1254 .28)) ;

ALTER TABLE TYPECCL ADDCONSTRAINT pk typecc l PRIMARY KEY ( numtypeccl ) ;

ALTER TABLE TYPEOPERATION ADDCONSTRAINT pk typeoperat ion PRIMARY KEY ( numtypeoper ) ;

ALTER TABLE COMPTECLIENT ADD(CONSTRAINT pk comptec l i ent

PRIMARY KEY ( numcli , numccl ) ,CONSTRAINT f k c c l t y p e c c l

FOREIGN KEY ( numtypeccl )REFERENCES TYPECCL ( numtypeccl ) ,

CONSTRAINT f k c c l c l i e n tFOREIGN KEY ( numcli )REFERENCES CLIENT ( numcli ) ,

CONSTRAINT f k c c l p e r s o n n e lFOREIGN KEY ( numpers )REFERENCES PERSONNEL ( numpers )

) ;

ALTER TABLE OPERATION ADD(

126

CONSTRAINT pk operat ionPRIMARY KEY ( numcli , numccl , numoper ) ,

CONSTRAINT f k o p e r c c lFOREIGN KEY ( numcli , numoper )REFERENCES COMPTECLIENT ( numcli , numccl ) ,

CONSTRAINT f k ope r codeope rFOREIGN KEY ( numtypeoper )REFERENCES typeoperat ion ( numtypeoper ) ,

CONSTRAINT montant operat ionCHECK( montantoper <> 0)

) ;

INSERT INTO TYPECCL VALUES ((SELECT nvl (MAX( numtypeccl ) , 0) + 1FROM TYPECCL) ,

’Compte courant ’ ) ;


’ l i v r e t ’ ) ;


’PEL ’ ) ;

INSERT INTO TYPEOPERATION VALUES ((SELECT nvl (MAX( numtypeoper ) , 0) + 1FROM TYPEOPERATION) ,

’dA c©pA´t espA¨ces ’ ) ;


’prA c©lA¨vement ’ ) ;


’ virement ’ ) ;


’ r e t r a i t ’ ) ;

127

A.7 Comptes bancaires avec exceptions

DROP TABLE OPERATION;DROP TABLE COMPTECLIENT;DROP TABLE TYPECCL;DROP TABLE TYPEOPERATION;DROP TABLE PERSONNEL;DROP TABLE CLIENT;

CREATE TABLE CLIENT( numcli number ,

nomcli varchar2 (30 ) ,prenomcl i varchar2 (30 ) ,ad r e s s e varchar2 (60 ) ,t e l varchar (10)

) ;

CREATE TABLE PERSONNEL( numpers number ,nompers varchar2 (30 ) ,prenompers varchar2 (30 ) ,manager number ,s a l a i r e number

) ;

CREATE TABLE TYPECCL( numtypeccl number ,

nomtypeccl varchar2 (30)) ;

CREATE TABLE COMPTECLIENT( numcli number ,numccl number ,numtypeccl number ,d a t e c c l date default sysdate not null ,numpers number

) ;

CREATE TABLE TYPEOPERATION( numtypeoper number ,

nomtypeoper varchar2 (30)) ;

CREATE TABLE OPERATION( numcli number ,numccl number ,numoper number ,numtypeoper number ,dateoper date default sysdate not null ,montantoper number not null ,l i b e l o p e r varchar2 (30)

) ;

ALTER TABLE CLIENT ADD(CONSTRAINT p k c l i e n t PRIMARY KEY ( numcli ) ,CONSTRAINT ck te l ephone CHECK(LENGTH( t e l )=10)) ;

ALTER TABLE PERSONNEL ADD(CONSTRAINT pk personne l PRIMARY KEY ( numpers ) ,CONSTRAINT c k s a l a i r e CHECK(SALAIRE >= 1254 .28)) ;

ALTER TABLE TYPECCL ADDCONSTRAINT pk typecc l PRIMARY KEY ( numtypeccl ) ;

ALTER TABLE TYPEOPERATION ADDCONSTRAINT pk typeoperat ion PRIMARY KEY ( numtypeoper ) ;

ALTER TABLE COMPTECLIENT ADD(CONSTRAINT pk comptec l i ent

PRIMARY KEY ( numcli , numccl ) ,CONSTRAINT f k c c l t y p e c c l

FOREIGN KEY ( numtypeccl )REFERENCES TYPECCL ( numtypeccl ) ,

CONSTRAINT f k c c l c l i e n tFOREIGN KEY ( numcli )REFERENCES CLIENT ( numcli ) ,

CONSTRAINT f k c c l p e r s o n n e lFOREIGN KEY ( numpers )REFERENCES PERSONNEL ( numpers )

) ;

ALTER TABLE OPERATION ADD(

128

CONSTRAINT pk operat ionPRIMARY KEY ( numcli , numccl , numoper ) ,

CONSTRAINT f k o p e r c c lFOREIGN KEY ( numcli , numoper )REFERENCES COMPTECLIENT ( numcli , numccl ) ,

CONSTRAINT f k ope r codeope rFOREIGN KEY ( numtypeoper )REFERENCES typeoperat ion ( numtypeoper ) ,

CONSTRAINT montant operat ionCHECK( montantoper <> 0 AND montantoper >= −1000 AND montantoper <= 1000)

) ;


’Compte courant ’ ) ;


’ l i v r e t ’ ) ;


’PEL ’ ) ;


’dA c©pA´t espA¨ces ’ ) ;


’prA c©lA¨vement ’ ) ;


’ virement ’ ) ;


’ r e t r a i t ’ ) ;

129

A.8 Secretariat pedagogique


CREATE TABLE ETUDIANT(numEtud number ,nom varchar2 (40 ) ,prenom varchar2 (40 ) ,da t ena i s s date ,c i v i l i t e varchar2 ( 4 ) ,patronyme varchar2 (40 ) ,numsecu varchar2 (15) NOT NULL) ;

CREATE TABLE MODULE(codMod number ,nomMod varchar2 (15 ) ,ef fecMax number DEFAULT 3 0 ) ;


CREATE TABLE INSCRIPTION(numEtud number ,codMod number ,da te Insc date default sysdate ) ;










ALTER TABLE PREREQUIS ADD(CONSTRAINT fk prerequ i s codmodFOREIGN KEY (codMod)REFERENCES MODULE(codMod ) ,CONSTRAINT f k pre requ i s codmodpre reqFOREIGN KEY ( codModPrereq )REFERENCES MODULE(codMod ) ) ;


ALTER TABLE RESULTAT ADD(CONSTRAINT f k r e su l t a t examenFOREIGN KEY (codMod , codExam)REFERENCES EXAMEN(codMod , codExam ) ,CONSTRAINT f k r e s u l t a t i n s c r i p t i o nFOREIGN KEY (codMod , numEtud)REFERENCES INSCRIPTION(codMod , numEtud ) ) ;

130








) ;

131

A.9 Mariages

CREATE TABLE PERSONNE( numpers number PRIMARY KEY,nom varchar2 (30) NOT NULL,prenom varchar2 (30 ) ,pere REFERENCES PERSONNE( numpers ) ,mere REFERENCES PERSONNE( numpers )

) ;

CREATE TABLE MARIAGE(nummari NUMBER REFERENCES PERSONNE( numpers ) ,numfemme NUMBER REFERENCES PERSONNE( numpers ) ,datemariage DATE DEFAULT SYSDATE,dated ivorce DATE DEFAULT NULL,PRIMARY KEY(nummari , numfemme , dateMariage )) ;

132

oracle_trés bon

Documents