oracle[1]

Cours Oracle

Alexandre Mesle

4 octobre 2008

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 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 61.1.4 SQL+ et iSQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7

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 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 9

1.3 Requetes mono-tabulaires . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 101.3.1 Complements sur SELECT . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 101.3.2 Instruction WHERE . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 101.3.3 Conditions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 101.3.4 Suppression . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 121.3.5 Mise a jour . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 12

1.4 Requetes multi-tabulaires . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 131.4.1 Principe . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 131.4.2 Produit cartesien . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 141.4.3 Jointure . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 151.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 . . . . . . . . . . . . . . . . . . . . . . . . . . 251.7.3 Sous requetes non correlees renvoyant une table . . . . . . . . . . . . . . . . . . . . 251.7.4 Sous requetes correlees . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 27

1.8 Complements sur les types . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 291.8.1 Types numeriques . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 291.8.2 Types chaine de caracteres . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 301.8.3 Types date . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 301.8.4 La fonction inclassable . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 311.8.5 Contraintes CHECK . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 31

1.9 Noyau imperatif du PL/SQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 33

1

1.9.1 PL/SQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 331.9.2 Blocs . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 331.9.3 Affichage . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 331.9.4 Variables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 331.9.5 Traitements conditionnels . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 331.9.6 Traitements repetitifs . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 34

1.10 Tableaux et structures . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 351.10.1 Tableaux . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 351.10.2 Structures . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 36

1.11 Applications du PL/SQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 381.11.1 Affectation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 381.11.2 Tables et structures . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 381.11.3 Transactions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 39

1.12 Exceptions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 401.12.1 Rattraper une exception . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 401.12.2 Exceptions predefinies . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 411.12.3 Codes d’erreur . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 411.12.4 Declarer et lancer ses propres exceptions . . . . . . . . . . . . . . . . . . . . . . . . 42

1.13 Sous-programmes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 431.13.1 Procedures . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 431.13.2 Fonctions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 43

1.14 Curseurs . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 451.14.1 Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 451.14.2 Les curseurs . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 45

1.15 Curseurs parametres . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 481.15.1 Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 481.15.2 Definition . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 481.15.3 Declaration . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 481.15.4 Ouverture . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 481.15.5 Lecture d’une ligne, fermeture . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 491.15.6 Boucle pour . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 491.15.7 Exemple recapitulatif . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 49

1.16 Triggers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 501.16.1 Principe . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 501.16.2 Classification . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 501.16.3 Creation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 50

2 Exercices 542.1 Contraintes declaratives . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 542.2 Requetes monotabulaires . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 562.3 Requetes multitabulaires . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 592.4 Agregation de donnees . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 632.5 Vues . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 662.6 Requetes imbriquees . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 672.7 Complements sur les types . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 682.8 Revisions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 692.9 Examen Type . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 702.10 Introduction au PL/SQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 722.11 Tableaux et Structures . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 732.12 Applications du PL/SQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 752.13 Exceptions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 792.14 Sous-programmes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 832.15 Curseurs . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 852.16 Curseurs parametres . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 862.17 Triggers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 87

2

3 Corriges 903.1 Contraintes declaratives . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 903.2 Requetes monotabulaires . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 933.3 Requetes multitabulaires . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 953.4 Agregation de donnees . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 973.5 Vues . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 983.6 Requetes imbriquees . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 993.7 Complements sur les types . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1023.8 Revisions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1033.9 Examen Type . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1043.10 Introduction au PL/SQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1093.11 Tableaux et Structures . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1103.12 Application du PL/SQL et Exceptions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1133.13 Sous-programmes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1173.14 Curseurs . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1203.15 Curseurs parametres . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1233.16 Triggers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 124

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 langageSQL.

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 SQL change tres peu. Ce cours est surtout un cours de SQL.

1.1.2 Organisation relationnelle des donnees

– Les donnes sont stockees dans des tables.

4

– 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 :

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 lesdonnees suivantes :

– 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

5

#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 :– DDL : Data definition language– DML : Data manipulation language– DQL : Data query language– DCL : Data control language

A cela s’ajoute le PL/SQL. Celui-ci permet de gerer presque toutes les contraintes et de maintenir lacoherence 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 ’ )

6

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 :

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– unique

On 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 serarefusee par Oracle.

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 lavaleur nd est 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.

8

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 >

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 Requetes mono-tabulaires

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 colonnescolonne1 a colonnen. Il est possible de supprimer les lignes en double a l’aide du mot-cle DISTINCT. Parexemple :

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 commecritere decroissant. Par exemple :

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 ferontpartie du resultat de la requete.

1.3.3 Conditions

Comparaison

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

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

10

Negation

La negation d’une condition s’obtient a l’aide de NOT. Par exemple, il est possible de re-ecrire les conditionsci-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)

Connecteurs logiques

De meme, vous avez a votre disposition tous les connecteurs logiques binaires : AND, OR. Ainsi, les deuxconditions suivantes 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 valeurqu’il soit ! 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 conditionssuivantes sont 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 suivantessont equivalentes

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

11

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, lacondition

SALARY LIKE ’ %000000000 ’

sera verifiee si SALARY se termine par 000000000. Le caractere % peut remplacer dans le litteral n’importeque suite, vide ou non, de caracteres ; il a le meme role que * en DOS et en SHELL. Le caractereremplace un et un seul caractere 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 !

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 valeurvaleuri. Par exemple,

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 memel’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 Requetes multi-tabulaires

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/SQLOracle’, il nous 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−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−

13

OracleMerise

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

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 , produi 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, cela a relativement peu de sens.

14

1.4.3 Jointure

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

SQL> SELECT ∗ FROM proposer , produi 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 Batman3 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 enutilisant le fait que numprod est une cle etrangere dans proposer. Comme la colonne numprod apparaitdeux fois dans la requete, il est necessaire de la prefixer par le nom de la table de sorte que chaque colonnepuisse etre designee de facon non ambigue. Si on veut mettre face a face les noms des produits et les nomsdes 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 ellememe. Par exemple, en reprenant la table intervalle,

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

BORNEINF BORNESUP

15

−−−−−−−−−− −−−−−−−−−−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 ;

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 pour les boeufs

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 pour les boss

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.

Que s’est-il passe ? Qu’est-ce donc que cette requete esoterique ? Reponse : Le cours d’aujourd’hui.

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 nonNULL.

– MAX(col) : retourne la plus grande des valeurs col sur toutes les lignes dont le champ col est nonNULL.

– MIN(col) : retourne la plus petite des valeurs col sur toutes les lignes dont le champ col est nonNULL.

– 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

17


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


Les requetes suivantes recuperent le nom du fournisseur proposant l’article ’Poupee Batman’ au prix lemoins 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 requeteimbriquee. Mais cela sera pour un cours ulterieur.

Complements sur COUNT

On recupere le nombre de ligne retournees par une requete en utilisant COUNT(*). Par exemple, si onsouhaite connaı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


18

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 tablePROPOSER, on procede de la facon suivante :

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 compta-bilise plusieurs fois.

1.5.2 Groupage

L’instruction GROUP BY

Les operation d’agregation considerees jusqu’a maintenant portent sur la totalite des lignes retourneespar les requetes, l’instruction GROUP BY permet de former des paquets a l’interieur desquels les donneesseront agregees. Cette instruction 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 exemplenous souhaitons 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


19

L’instruction HAVING

Supposons que de la requete precedente, nous ne souhaitions garder que les lignes pour lesquelles lavaleur NB PRODUITS PROPOSES est egale a 1. Ajouter une condition dans WHERE serait inutile, le filtrageoccasionne par WHERE est effectue avant l’agregation. Il nous faudrait une instruction pour n’inclure quedes groupes de donnees repondant certains criteres. L’instruction utilisee pour ce faire est HAVING. Sonutilisation 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 ) = 16 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 livraisonsconfondues),

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 vuesont tout le temps a jour. Si vous modifiez les donnees d’une des tables sur lesquelles est calculee la vue,alors les modifications sont 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 fournis-seurs qui le 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 dontvous disposez pour le moment ne vous permettront pas de formuler une requete affichant les noms desproduits n’ayant aucun fournisseur. Creons une vue pour ne pas avoir a se farcir la requete chaque foisque nous aurons besoin de ces informations :

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;

NOMPROD NB FOURNISSEURS−−−−−−−−−−−−−−−−−−−−−−−−−−−−−− −−−−−−−−−−−−−−−Cornichons 0

21

Cotons t i g e s 1Roue de s e cour s 1Poupee Batman 2


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

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;

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


Maintenant, nous souhaitons voir s’afficher, pour tout i, le nombre de produits proposes par exactementi fournisseurs.

SQL> SET head o f fSQL> SELECT ’ I l y a ’ | | COUNT(NOMPROD) | | ’ p rodu i 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 requeteimbriquee peut 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 place soit comme une colonne supplementaire, soit comme une valeur servant a evaluer desconditions (WHERE ou HAVING).

Colonne fictive

On peut ajouter une colonne dans une requete, et choisir comme valeurs pour cette colonne le resultatd’une requete. Ce type de requete est souvent une alternative a GROUP BY. Par exemple, la requete suivantenous 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, declaronsd’abord une vue 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 :

23

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−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−f 1

La requete SELECT MAX(NB PROD) FROM NB PROD PAR FOU est evaluee avant, et son resultat lui est sub-stitue dans l’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 .

24

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 cetteliste a l’aide de l’operateur IN. On peut s’en servir comme une alternative aux jointures, par exemple,reecrivons la requete de la section precedente. La requete suivante nous renvoie le nombre de produitsproposes par les fournisseurs proposant le plus 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 :

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’est donc par hasard qu’elle ne retourne qu’une ligne. Le numero du fournisseur proposant le plus deproduits est donc le 1. 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 requetesuivante renvoie une table.

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

25

6 FROM FOURNISSEUR F;

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

2110

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

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

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

2

Ce type de requete est une alternative aux vues. Recuperons maintenant les noms des fournisseurs pro-posant le plus 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−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−

26

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 principale

Par exemple, la requete suivante renvoie le nombre de produits livres pour chaque fournisseur. Elle contientune 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

Cette meme requete, une fois evaluee, peut server de requete non correlee si on souhaite connaıtre lesnoms de ces fournisseurs :

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

27

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 ?

28

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 chiffresapres la virgule.

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−−−−−−−−−−

29

10 ,210

,0121 ,01

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 avecla fonction 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 :

30

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−−−−−−−−−− −−−−−−−−−− −−−−−−−−

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 les lignes de la table prisesune par une. La syntaxe est :

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

Par exemple,

31

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 parexemple).

32

1.9 Noyau imperatif du PL/SQL

1.9.1 PL/SQL

Le PL de PL/SQL signifie Procedural Language. Il s’agit d’une extension procedurale du SQL permettantd’effectuer des traitements complexes sur une base de donnees. Les possibilites offertes sont les memesqu’avec des langages imperatifs (instructions en sequence) classiques.Ecrivez-le dans un editeur dont vous copierez le contenu dans SQL+. Un script ecrit en PL/SQL se termineobligatoirement par un /, sinon SQL+ ne l’interprete pas. S’il contient des erreurs de compilation, il estpossible d’afficher les messages 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 dedeclaration de 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 OUPUT.PUT() et DBMS OUPUT.PUT LINE()prennent en argument une valeur a afficher ou une variable dont la valeur est a afficher. Par defaut, lesfonctions d’affichage sont desactivees. Il convient, a moins que vous ne vouliez rien voir s’afficher, de lesactiver 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

voire

33

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 ∗/

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 faconsuivante :

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

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

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;

Il est possible, en bidouillant d’implementer la boucle DO ... WHILE...

34

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,declarons un 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. Cettefonction reserve de l’espace memoire pour ce tableau et retourne l’adresse memoire de la zone reservee,il s’agit d’une sorte de malloc. 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 stockerles elements qu’il va contenir. On utilise pour cela la methode EXTEND(). EXTEND s’invoque en utilisantla notation pointee. Par exemple,


BEGIN

35

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

END;/

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 lacreation du type tableau associe.

Utilisation d’un tableau

On accede, en lecture et en ecriture, au i-eme element d’une variable tabulaire nomme T avec l’instructionT(i). Les elements sont indices a partir de 1.Effectuons, par exemple, une permutation circulaire vers la droite des elements du tableau t.


TYPE 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 plusieursvariables, 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 syntaxeque la liste des colonnes d’une table dans un CREATE TABLE. Par exemple, construisons le type point(dans IR2),

36

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 esttoujours la meme pour declarer des variables en PL/SQL, par exemple

p po int ;

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 notationpointee : v.c est le champ appele c de la variable structure appelee v. Par exemple,


TYPE 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 champsabscisse et ordonnee du point p les valeurs 1 et 3.

37

1.11 Applications 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 INTO v 1 , . . . , champ n INTO v nFROM . . .

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


num 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 seproduit a l’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 quevous allez mettre dedans ! Si vous tenez a y mettre une valeur qui se trouve dans une colonne d’une table, ilest possible de vous referer directement au type de cette colonne avec le type nomTable.nomColonne%type.Par exemple,


num 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 type porte alors le nom suivant : nomTable%rowtype.


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

BEGINSELECT ∗ INTO l i g n e

FROM PRODUIT

38

WHERE nomprod = nom ;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;/

1.11.3 Transactions

Un des mecanismes les plus puissants des SGBD recent reside dans le systeme des transactions. Untransaction est un ensemble d’operations “atomiques”, c’est-a-dire indivisible. Nous considererons qu’unensemble d’operations est indivisible si une execution partielle de ces instructions poserait des problemesd’integrite dans la base de donnees. Par exemple, dans le cas d’une base de donnees de gestion de comptesen banque, un virement d’un compte a un autre se fait en deux temps : crediter un compte d’une sommes, et debiter un autre de la meme somme s. Si une erreur survient pendant la deuxieme operation, et quela transaction est interrompue, le virement est incomplet et le patron va vous assasiner.

Il convient donc de disposer d’un mecanisme permettant de se proteger de ce genre de desagrement.Plutot que se casser la tete a tester les erreurs a chaque etape et a balancer des instructions permettantde “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 leprecedent COMMIT), 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’activerla gestion des transactions. Si elle est positionnee a ON, chaque instruction a des repercussions immediatesdans la base, sinon, les modifications ne sont effectives qu’une fois qu’un COMMIT a ete execute.

39

1.12 Exceptions

Le mecanisme des exceptions est implemente dans la plupart des langages recent, notament orientesobjet. Cette facon de programmer a quelques avantages immediats :

– obliger les programmeurs a traiter les erreurs : combien de fois votre prof de C a hurleen vous suppliant de verifier les valeurs retournees par un malloc, ou un fopen ? La plupart descompilateurs des langages a exceptions (notamment java) ne compilent que si pour chaque erreurpotentielle, vous avez prepare un bloc de code (eventuellement vide...) pour la traiter. Le but estde vous assurer que vous n’avez pas oublie d’erreur.

– Rattraper les erreurs en cours d’execution : Si vous programmez un systeme de securitede centrale nucleaire ou un pilote automatique pour l’aviation civile, une erreur de memoire quivous afficherait l’ecran bleu de windows, ou le message “Envoyer le rapport d’erreur ?”, ou plussimplement le fameux “Segmentation fault” produirait un effet des plus mauvais. Certaines erreursd’execution sont rattrapables, autrement dit, il est possible de resoudre le probleme sans interromprele programme.

– Ecrire le traitement des erreurs a part : Pour des raisons fiabilite, de lisibilite, il a eteconsidere que melanger le code “normal” et le traitement des erreurs etait un style de programmationperfectible... Dans les langages 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 identifica-teur, par exemple GLUBARF. Lorsque dans les instructions, l’erreur GLUBARF se produit, le code du BEGINs’interrompt et le code de la section EXCEPTION est lance. On dit aussi que quand une exception estlevee (raised) (on dit aussi jetee (thrown)), on la rattrape (catch) dans le bloc EXCEPTION. La sectionEXCEPTION 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 untraitement particulier pour rattraper (ou propager) l’erreur. Quand un bloc est traite, les WHEN suivantsne sont pas evalues. OTHERS est l’exception par defaut, OTHERS est toujours verifie, sauf si un cas precedenta ete verifie. Dans l’exemple suivant :

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;

40

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;

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

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 :


num 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 de1.

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 estconseille de se reporter a la documentation pour les obtenir. On les traite de la facon suivante

EXCEPTIONWHEN OTHERS THEN

IF SQLCODE = CODE1 THEN

41

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

/∗ t r a i t e m e n t ∗/ELSE

DBMS 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 selance avec l’instruction RAISE. Par exemple,


GLUBARF EXCEPTION;BEGIN

RAISE GLUBARF;EXCEPTION

WHEN GLUBARF THENDBMS OUTPUT. PUT LINE( ’ g l u b a r f r a i s e d . ’ ) ;

END;/

42

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 uncompte a rebours.

CREATE OR REPLACE PROCEDURE compteARebours ( cpt NUMBER) ISBEGIN

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

END IF ;END;

Invocation

En PL/SQL, une procedure s’invoque tout simplement avec son nom. Mais sous SQL+, on doit utili-ser le mot-cle CALL. Par exemple, on invoque le compte a rebours sous SQL+ avec la commande CALLcompteARebours(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 reference

Par 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,

43

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

IF a < b THENRETURN a ;

ELSERETURN module ( a − b , b ) ;

END IF ;END;

Invocation

Tout comme les procedures, l’invocation des fonctions ne pose aucun probleme en PL/SQL, par contre, sousSQL+, 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.

44

1.14 Curseurs

1.14.1 Introduction

Les instructions de type SELECT ... INTO ... manquent de souplesse, elles ne fontionnent que sur desrequetes retourant une et une seule valeur. Ne serait-il pas interessant de pouvoir placer dans des variablesle resultat d’une requete 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 unee utilisant 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 lecturede la ligne echoue, 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 ) ;

45

END LOOP;/∗ . . . ∗/

END;

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

46

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

END;/

47

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 pasfixees. Pourquoi ? Parce que les valeurs des ces sont susceptibles de changer entre la declaration du curseuret 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 serontfixees qu’a l’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 prenomsde ses enfants :

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 ) ;

48

1.15.5 Lecture d’une ligne, fermeture

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

1.15.6 Boucle pour

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

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%rowtype ;BEGIN

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

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

DBMS OUTPUT. PUT LINE( ’ ’ | | e . nompers| | ’ ’ | | e . prenompers ) ;

END LOOP;END LOOP;

END;

49

1.16 Triggers

1.16.1 Principe

Un trigger est une procedure stockee qui se lance automatiquement lorsqu’un evenement se produit. Parevenement, on entend dans ce cours toute modification des donnees se trouvant dans les tables. On s’ensert pour controler ou appliquer 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 realiserons dans 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. Letrigger doit-il etre execute pour chaque ligne affectee (FOR EACH ROW), ou seulement une fois pour toutel’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 | ]

/∗ 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;

50

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

21

SQL> DELETE FROM CLIENT;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 codecode et un message d’erreur message. Vous remarquer que comme l’erreur a ete levee avant la suppression,les donnees sont toujours presentes dans la table CLIENT. Le trigger a controle une regle, et comme etten’etait pas respectee, il a lance une erreur.

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’ancienneligne et la nouvelle ligne par l’intermediaire des deux variables structurees old et new.

Attention !

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

SQL> CREATE OR REPLACE TRIGGER beforeStatement2 BEFORE UPDATE ON CLIENT3 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;

51

9 /

Declencheur c r e e .

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>SQL> CREATE OR REPLACE TRIGGER afterForEachRow

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

10 /


SQL>

52

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’

53

Chapitre 2

Exercices


Soit le script de creation de table suivant :

CREATE TABLE PRODUIT(numprod number,nomprod varchar2(30));

CREATE TABLE FOURNISSEUR(numfou number,nomfou varchar2(30));

CREATE TABLE PROPOSER(numfou number,numprod number,prix number);

CREATE TABLE LIVRAISON(numfou number,numli number,dateli date default sysdate);

CREATE TABLE DETAILLIVRAISON(numfou number,numli number,numprod number,qte number);

Le numero de livraison est une cle secondaire, c’est-a-dire un numero unique pour un fournisseur donne.

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 le script de facon a ce que cela se fasse.

54

Exercice 3

Faites de meme pour les cles etrangeres, sans oublier qu’il ne peut figurer dans une livraison que desproduits proposes 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

2.2 Requetes monotabulaires

Nous souhaitons gerer un secretatiat pedagogique, les modules sont repertories dans une table, et lesmodules pre-requis pour s’y inscrire (avec la note minimale) se trouvent dans la table prerequis. Uneligne de la table PREREQUIS nous indique que pour s’inscrire dans le module numero numMod, il faut avoireu au moins noteMin au module numModPrereq.

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

CREATE TABLE PREREQUIS(numMod number references MODULE(numMod),numModPrereq number references 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, 12);INSERT INTO PREREQUIS (numMod, numModPrereq) VALUES (6, 5);INSERT INTO PREREQUIS (numMod, numModPrereq) VALUES (8, 5);INSERT INTO PREREQUIS (numMod, numModPrereq) VALUES (7, 5);

Les sorties generees par les deux premieres questions sont donnees. Apres, ca sera a vous de verifier si lesresultats 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).

56

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 necessairespour aller en ’PL/SQL Oracle’ d’un point.

Exercice 5

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

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 definit pour les questions suivantes les tables

CREATE TABLE INTERVALLE(borneInf 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, 56);INSERT INTO INTERVALLE VALUES (12, 30);INSERT INTO INTERVALLE VALUES (2, 3);INSERT INTO INTERVALLE VALUES (12, 3);INSERT INTO INTERVALLE VALUES (8, 27);INSERT INTO INTERVALLE VALUES (34, 26);INSERT INTO INTERVALLE VALUES (5, 10);INSERT INTO INTERVALLE VALUES (7, 32);

57

INSERT INTO INTERVALLE VALUES (0, 30);INSERT INTO INTERVALLE VALUES (21, 8);

INSERT INTO RECTANGLE VALUES (2, 12, 5, 7);INSERT INTO RECTANGLE VALUES (2, 12, 1, 13);INSERT INTO RECTANGLE VALUES (10, 13, 1, 11);INSERT INTO RECTANGLE VALUES (10, 13, 10, 11);INSERT INTO RECTANGLE VALUES (2, 7, 5, 13);INSERT INTO RECTANGLE VALUES (21, 73, 15, 22);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);

La table INTERVALLE contient des intervalles specifies par leurs bornes inferieure et superieure. Supprimerde la table intervalle tous les intervalles qui n’en sont pas avec une seule instruction.

Exercice 9

La table RECTANGLE contient des rectangles specifies par les coordonnees de deux sommets diametralementopposes, leurs aretes sont paralleles aux axes. Certains rectangles ont des coordonnees erronnees, c’est-adire que soit ils sont des segments, 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 tousles intervalles 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’apas un point 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 dexBasDroit et/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 cepoint.

Exercice 15

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

58

2.3 Requetes multitabulaires

Reprenons pour ce tp la base de donees du tp sur les contraintes :



CREATE TABLE PROPOSER(numfou number,numprod number,prix number NOT NULL);


CREATE TABLE DETAILLIVRAISON(numfou number,numli number,numprod number,qte number NOT NULL);

alter table produit add constraint pk_produitPRIMARY KEY (numprod);alter table fournisseur add constraint pk_fournisseurPRIMARY KEY (numfou);alter table proposer add constraint pk_proposerPRIMARY KEY (numfou, numprod);alter table livraison add constraint pk_livraisonPRIMARY KEY (numfou, numli);alter table detaillivraison add constraint pk_detail_livraisonPRIMARY KEY (numfou, numli, numprod);alter table proposer add constraint fk_proposer_fournisseurFOREIGN KEY (numfou) REFERENCES fournisseur (numfou);alter table proposer add constraint fk_proposer_produitFOREIGN KEY (numprod) REFERENCES produit (numprod);alter table livraison add constraint fk_livraisonFOREIGN KEY (numfou) REFERENCES fournisseur (numfou);alter table detaillivraison add constraint fk_detail_livraisonFOREIGN KEY (numfou, numli) REFERENCES livraison (numfou, numli);alter table detaillivraison add constraint fk_detail_livraison_proposerFOREIGN KEY (numfou, numprod) REFERENCES proposer (numfou, numprod);

INSERT INTO PRODUIT values (1, ’Roue de secours’);INSERT INTO PRODUIT values (2, ’Poupee Batman’);INSERT INTO PRODUIT values (3, ’Cotons tiges’);INSERT INTO PRODUIT values (4, ’Cornichons’);

59

INSERT INTO FOURNISSEUR values (1, ’f1’);INSERT INTO FOURNISSEUR values (2, ’f2’);INSERT INTO FOURNISSEUR values (3, ’f3’);INSERT INTO FOURNISSEUR values (4, ’f4’);

INSERT INTO PROPOSER values (1, 1, 200);INSERT INTO PROPOSER values (1, 2, 15);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, 10);INSERT INTO DETAILLIVRAISON values (1, 1, 1, 25);INSERT INTO DETAILLIVRAISON values (1, 1, 2, 20);INSERT INTO DETAILLIVRAISON values (1, 2, 1, 15);INSERT INTO DETAILLIVRAISON values (1, 2, 2, 17);

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.

60

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 leproduit ’Poupee Batman’.

Exercice 8

Pour les exercices suivants, nous travaillerons sur les donnees suivantes,

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, dit le Hardi’, ’Godefroy’, NULL, NULL);INSERT INTO PERSONNE VALUES (16, ’ET’, NULL, NULL, NULL);INSERT INTO PERSONNE VALUES (2, ’Le Croquant’, ’Jacqou’, 1, 16);INSERT INTO PERSONNE VALUES (3, ’La Fripouille’, ’Jacqouille’, 1, 16);INSERT INTO PERSONNE VALUES (4, ’Bush’, ’Kate’, NULL, NULL);INSERT INTO PERSONNE VALUES (13, ’Granger’, ’Hermione’, NULL, NULL);INSERT INTO PERSONNE VALUES (5, ’Du Femur’, ’Medor’, 3,4 );INSERT INTO PERSONNE VALUES (12, ’Kobalevskaıa’, ’Sofia’, NULL, NULL);INSERT INTO PERSONNE VALUES (6, ’Rieu’, ’Andre’, NULL, NULL);INSERT INTO PERSONNE VALUES (7, ’Bontoutou’, ’Rex’, 6, 4);INSERT INTO PERSONNE VALUES (8, ’Dijkstra’, ’Edvard’, 2, 13);INSERT INTO PERSONNE VALUES (9, ’Leibniz’, ’Gottfrie1d Wilhem’, 8, 12);INSERT INTO PERSONNE VALUES (10, ’Bach’, ’Johann Sebastien’, 5, 12);INSERT INTO PERSONNE VALUES (17, ’Mathieu’, ’Mireille’, NULL, NULL);INSERT INTO PERSONNE VALUES (11, ’Lemarchal’, ’Gregory’, 10, 17);INSERT INTO PERSONNE VALUES (15, ’Socrate’, NULL, 3, 13);INSERT INTO PERSONNE VALUES (19, ’Leroy’, ’Nolwen’, NULL, NULL);INSERT INTO PERSONNE VALUES (20, ’Bartoli’, ’Jennifer’, 9, 19);INSERT INTO PERSONNE VALUES (21, ’Fabian’, ’Lara’, 10, 17);INSERT INTO PERSONNE VALUES (14, ’Stone’, ’Sharon’, 15, 20);INSERT INTO PERSONNE VALUES (18, ’Frege’, ’Elodie’, 7, 13);

Le champ pere contient le numero du pere de la personne, le champ mere contient le numero de la merede la personne. Dans les questions ou il vous est demande de formuler des requetes retournant plusieurspersonne, il faut qu’il y ait une ligne par personne. Afficher les noms et prenoms des enfants de SofiaKobalevskaıa.

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

61

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.

62


Nous utiliserons pour ce tp les donnees suivantes :



CREATE TABLE PROPOSER(numfou number,numprod number,prix number NOT NULL);


CREATE TABLE DETAILLIVRAISON(numfou number,numli number,numprod number,qte number NOT NULL);

alter table produit add constraint pk_produitPRIMARY KEY (numprod);alter table fournisseur add constraint pk_fournisseurPRIMARY KEY (numfou);alter table proposer add constraint pk_proposerPRIMARY KEY (numfou, numprod);alter table livraison add constraint pk_livraisonPRIMARY KEY (numfou, numli);alter table detaillivraison add constraint pk_detail_livraisonPRIMARY KEY (numfou, numli, numprod);alter table proposer add constraint fk_proposer_fournisseurFOREIGN KEY (numfou) REFERENCES fournisseur (numfou);alter table proposer add constraint fk_proposer_produitFOREIGN KEY (numprod) REFERENCES produit (numprod);alter table livraison add constraint fk_livraisonFOREIGN KEY (numfou) REFERENCES fournisseur (numfou);alter table detaillivraison add constraint fk_detail_livraisonFOREIGN KEY (numfou, numli) REFERENCES livraison (numfou, numli);alter table detaillivraison add constraint fk_detail_livraison_proposerFOREIGN KEY (numfou, numprod) REFERENCES proposer (numfou, numprod);

INSERT INTO PRODUIT values (1, ’Roue de secours’);INSERT INTO PRODUIT values (2, ’Poupee Batman’);INSERT INTO PRODUIT values (3, ’Cotons tiges’);INSERT INTO PRODUIT values (4, ’Cornichons’);

63

INSERT INTO FOURNISSEUR values (1, ’f1’);INSERT INTO FOURNISSEUR values (2, ’f2’);INSERT INTO FOURNISSEUR values (3, ’f3’);INSERT INTO FOURNISSEUR values (4, ’f4’);

INSERT INTO PROPOSER values (1, 1, 200);INSERT INTO PROPOSER values (1, 2, 15);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, 10);INSERT INTO DETAILLIVRAISON values (1, 1, 1, 25);INSERT INTO DETAILLIVRAISON values (1, 1, 2, 20);INSERT INTO DETAILLIVRAISON values (1, 2, 1, 15);INSERT INTO DETAILLIVRAISON values (1, 2, 2, 17);

Amusez-vous bien !

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 produitslivres.

Exercice 8

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

64

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.

65

2.5 Vues

Nous utiliserons pour ce tp les memes donnees que dans le tp precedent. N’hesitez pas, pour tester vosrequetes, a inserer d’autres donnees dans la base.

Exercice 1

Creez une vue affichant pour chaque produit ayant deja ete livre le numero du produit et la somme desquantites livrees (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 nombre de produits distincts, on entend sans tenir compte de la quantite.

66


Nous utiliserons pour ce tp les donnees du tp precedant le tp precedent. Presque tous les stratagemes sontautorises, vous pouvez utiliser des fonctions d’agregation, des vues, et des requetes imbriquees partoutsauf dans le FROM (ca sera l’objet du cours suivant). 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 fournis-seur n’en propose 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 plusgrande quantite cumulee de produits p.

Exercice 8

¡Afficher le nombre de produits proposes par les fournisseurs proposant le moins de produits. Norma-lement, un 0 devrait 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 quantitecumulee).

67


Nous utiliserons pour ce tp les memes donnees que dans le tp precedent.

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 ace 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 majusculeet a ne comporter ensuite sur des minuscules. Si l’ajout d’une des contraintes est refusee, demandez vouspourquoi et faites le necessaire (une commande UPDATE sera la bienvenue...) pour y remedier.

68

2.8 Revisions

Nous utiliserons pour ce tp les memes donnees que dans le tp precedent.

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 lesderniers a avoir effectue une livraison.

69

2.9 Examen Type

Soit les donnees suivantes :– ETUDIANT(numEtud, nom, prenom, datenaiss, civilite, patronyme, numsecu)

Repertorie les etudiants. Un numero de secu comporte 15 chiffres.– MODULE(codMod, nomMod, effecMax)

Repertorie les modules– EXAMEN(codMod, codeExam, dateExam)

CodeExam numerote les examens a l’interieur de chaque module.– INSCRIPTION(numEtud, codMmod, dateInsc)

Inscription d’un eleve a un module.– RESULTAT(numEtud, codMmod, CodeExam, note)

Resultat de l’etudiant numEtud a l’examen (codMod, codeExam).– PREREQUIS(codMod, codeModPrereq, noteMin)

Pour s’inscrire au module codMod il est necessaire d’avoir eu au moins la note noteMin au modulenumero codeModPrereq.

Exercice 1 - CREATE TABLE

Ecrire les CREATE TABLE correspondant a ces donnees, sans les contraintes. Choisissez les types judicieu-sement.

Exercice 2 - Contraintes de cle

1. Localiser les cles primaires, les declarer avec des ordres ALTER TABLE.2. Faire de meme avec les cles etrangeres.

Exercice 3 - Contraintes d’attributs

Comment modifier le script de creation de tables pour

1. forcer la saisie de noteMin dans la table PREREQUIS.2. forcer la saisie du numero de securite sociale3. mettre l’effectif maximum d’un Module a 30 par defaut.4. obliger la civilite a etre une des trois valeurs Mr, Mme, Mlle

5. empecher la saisie d’un patronyme si l’etudiant n’est pas de sexe feminin.6. verifier si le numero de securite sociale comporte bien 15 chiffres7. verifier que le premier chiffre du numero de securite sociale est est 1 si l’etudiant est de sexe masculin,

2 s’il est de sexe feminin.8. verifier si les deuxieme et troisieme chiffres correspondent bien a l’annee de naissance.9. Est-il possible de controler avec des contraintes declaratives si l’effectif maximum dans un module

n’est pas depasse.

Exercice 4 - Manipulation des donnees

On ne sait pas si la base de donnees est vide ou non. Vous placerez des sous-requetes dans les ordressuivants pour vous assurer que les modifications ou insertions ne seront pas refuses.

1. Inserer un eleve appele JosephFourier.2. Creer un module Maths

3. Inscrire JosephFourier en Maths.4. Creer un examen de Maths le deux janvier 2006.5. Donner a JosephFourier un 19 a cet examen.6. Modifier la note precedente pour l’amener a 20.

70

Exercice 5 - Requetes

Vous n’hesiterez pas a ajouter des donnees pour tester la validite de vos requetes. Si un etudiant a euplusieurs notes a un meme module, sa note definitive est la plus elevee.

1. Liste des noms des etudiants.

2. Liste des noms des etudiants inscrits en maths.

3. Liste des noms des etudiants avec leur note (definitive) de maths.

4. Liste des noms des etudiants ayant valide leur examen de maths.

5. Liste des noms des etudiants n’ayant pas passe leur examen de maths.

6. Le(s) nom(s) de(s) (l’)etudiant(s) ayant eu la meilleure note en maths.

7. Le(s) nom(s) de(s) (l’)etudiant(s) dont la note la plus basse est la plus elevee.

8. Les noms des modules dans lesquels peut s’inscrire JosephFourier.

71

2.10 Introduction au PL/SQL

Exercice 1

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

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 aet b.

72

2.11 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 tableau5. 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;

73

first := 1;last := 19;t(first).previous := -1;t(last).next := -1;

-- 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 lechaınage).

Exercice 5

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

74

2.12 Applications du PL/SQL

Nous travaillerons sur les donnees suivantes :

DROP TABLE COMPTECLIENT;DROP TABLE OPERATION;DROP TABLE TYPECCL;DROP TABLE TYPEOPERATION;DROP TABLE PERSONNEL;DROP TABLE CLIENT;

CREATE TABLE CLIENT(numcli number,nomcli varchar2(30),prenomcli varchar2(30),adresse varchar2(60),tel varchar(10)

);

CREATE TABLE PERSONNEL(numpers number,nompers varchar2(30),prenompers varchar2(30),manager number,salaire number

);

CREATE TABLE TYPECCL(numtypeccl number,nomtypeccl varchar2(30)

);

CREATE TABLE COMPTECLIENT(numcli number,numccl number,numtypeccl number,dateccl 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,libeloper varchar2(30)

);

75

ALTER TABLE CLIENT ADD(CONSTRAINT pk_client PRIMARY KEY (numcli),CONSTRAINT ck_telephone CHECK(LENGTH(tel)=10));

ALTER TABLE PERSONNEL ADD(CONSTRAINT pk_personnel PRIMARY KEY (numpers),CONSTRAINT ck_salaire CHECK(SALAIRE >= 1254.28));

ALTER TABLE TYPECCL ADDCONSTRAINT pk_typeccl PRIMARY KEY (numtypeccl);

ALTER TABLE TYPEOPERATION ADDCONSTRAINT pk_typeoperation PRIMARY KEY (numtypeoper);

ALTER TABLE COMPTECLIENT ADD(CONSTRAINT pk_compteclient

PRIMARY KEY (numcli, numccl),CONSTRAINT fk_ccl_typeccl

FOREIGN KEY (numtypeccl)REFERENCES TYPECCL (numtypeccl),REFERENCES CLIENT (numcli),

CONSTRAINT fk_ccl_clientFOREIGN KEY (numcli)

CONSTRAINT fk_ccl_personnelFOREIGN KEY (numpers)REFERENCES PERSONNEL (numpers)

);



FOREIGN KEY (numtypeccl)REFERENCES TYPECCL (numtypeccl),

CONSTRAINT fk_ccl_clientFOREIGN KEY (numcli)REFERENCES CLIENT (numcli),


);

ALTER TABLE OPERATION ADD(CONSTRAINT pk_operation

PRIMARY KEY (numcli, numccl, numoper),CONSTRAINT fk_oper_cclFOREIGN KEY (numcli, numoper)

76

REFERENCES COMPTECLIENT (numcli, numccl),CONSTRAINT fk_oper_codeoper

FOREIGN KEY (numtypeoper)REFERENCES typeoperation (numtypeoper),

CONSTRAINT montant_operationCHECK(montantoper <> 0)

);

INSERT INTO TYPECCL VALUES ((SELECT nvl(MAX(numtypeccl), 0) + 1FROM TYPECCL),’Compte courant’);

INSERT INTO TYPECCL VALUES ((SELECT nvl(MAX(numtypeccl), 0) + 1FROM TYPECCL),’livret’);

INSERT INTO TYPECCL VALUES ((SELECT nvl(MAX(numtypeccl), 0) + 1FROM TYPECCL),’PEL’);

INSERT INTO TYPEOPERATION VALUES ((SELECT nvl(MAX(numtypeoper), 0) + 1FROM TYPEOPERATION),’depot especes’);

INSERT INTO TYPEOPERATION VALUES ((SELECT nvl(MAX(numtypeoper), 0) + 1FROM TYPEOPERATION),’prelevement’);

INSERT INTO TYPEOPERATION VALUES ((SELECT nvl(MAX(numtypeoper), 0) + 1FROM TYPEOPERATION),’virement’);

INSERT INTO TYPEOPERATION VALUES ((SELECT nvl(MAX(numtypeoper), 0) + 1FROM TYPEOPERATION),’retrait’);

DROP TABLE PERSONNE;

CREATE TABLE PERSONNE(numpers number PRIMARY KEY,

77

nom varchar2(30) NOT NULL,prenom varchar2(30),pere REFERENCES PERSONNE(numpers),mere REFERENCES PERSONNE(numpers)

);

INSERT INTO PERSONNE VALUES (1, ’de Montmirail, dit le Hardi’, ’Godefroy’, NULL, NULL);INSERT INTO PERSONNE VALUES (16, ’Canat de Chizy’, ’Edith’, NULL, NULL);INSERT INTO PERSONNE VALUES (2, ’Le Croquant’, ’Jacqou’, 1, 16);INSERT INTO PERSONNE VALUES (3, ’La Fripouille’, ’Jacqouille’, 1, 16);INSERT INTO PERSONNE VALUES (4, ’Bush’, ’Kate’, NULL, NULL);INSERT INTO PERSONNE VALUES (13, ’Granger’, ’Hermione’, NULL, NULL);INSERT INTO PERSONNE VALUES (5, ’Du Femur’, ’Medor’, 3,4 );INSERT INTO PERSONNE VALUES (12, ’Kobalevskaıa’, ’Sofia’, NULL, NULL);INSERT INTO PERSONNE VALUES (6, ’Rieu’, ’Andre’, NULL, NULL);INSERT INTO PERSONNE VALUES (7, ’Bontoutou’, ’Rex’, 6, 4);INSERT INTO PERSONNE VALUES (8, ’Dijkstra’, ’Edvard’, 2, 13);INSERT INTO PERSONNE VALUES (9, ’Leibniz’, ’Gottfrie1d Wilhem’, 8, 12);INSERT INTO PERSONNE VALUES (10, ’Bach’, ’Johann Sebastien’, 5, 12);INSERT INTO PERSONNE VALUES (17, ’Mathieu’, ’Mireille’, NULL, NULL);INSERT INTO PERSONNE VALUES (11, ’Lemarchal’, ’Gregory’, 10, 17);INSERT INTO PERSONNE VALUES (15, ’Socrate’, NULL, 3, 13);INSERT INTO PERSONNE VALUES (19, ’Leroy’, ’Nolwen’, NULL, NULL);INSERT INTO PERSONNE VALUES (20, ’Bartoli’, ’Jennifer’, 9, 19);INSERT INTO PERSONNE VALUES (21, ’Fabian’, ’Lara’, 10, 17);INSERT INTO PERSONNE VALUES (14, ’Stone’, ’Sharon’, 15, 20);INSERT INTO PERSONNE VALUES (18, ’Frege’, ’Elodie’, 7, 13);

COMMIT;

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 de1 a 21. Utilisez une boucle dans laquelle vous placerez une requete pour recopier les couples nom/prenomde la table personne dans la table CLIENT.

Exercice 2

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

Exercice 3

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

Exercice 4

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

78

2.13 Exceptions

Nous utiliserons les donnees suivantes, meme si cela annihile le travail de la seance precedente...

DROP TABLE COMPTECLIENT;DROP TABLE OPERATION;DROP TABLE TYPECCL;DROP TABLE TYPEOPERATION;DROP TABLE PERSONNEL;DROP TABLE CLIENT;

CREATE TABLE CLIENT(numcli number,nomcli varchar2(30),prenomcli varchar2(30),adresse varchar2(60),tel varchar(10)

);

CREATE TABLE PERSONNEL(numpers number,nompers varchar2(30),prenompers varchar2(30),manager number,salaire number

);

CREATE TABLE TYPECCL(numtypeccl number,nomtypeccl varchar2(30)

);

CREATE TABLE COMPTECLIENT(numcli number,numccl number,numtypeccl number,dateccl 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,libeloper varchar2(30)

);

79

ALTER TABLE CLIENT ADD(CONSTRAINT pk_client PRIMARY KEY (numcli),CONSTRAINT ck_telephone CHECK(LENGTH(tel)=10));

ALTER TABLE PERSONNEL ADD(CONSTRAINT pk_personnel PRIMARY KEY (numpers),CONSTRAINT ck_salaire CHECK(SALAIRE >= 1254.28));

ALTER TABLE TYPECCL ADDCONSTRAINT pk_typeccl PRIMARY KEY (numtypeccl);

ALTER TABLE TYPEOPERATION ADDCONSTRAINT pk_typeoperation PRIMARY KEY (numtypeoper);



FOREIGN KEY (numtypeccl)REFERENCES TYPECCL (numtypeccl),

CONSTRAINT fk_ccl_clientFOREIGN KEY (numcli)REFERENCES CLIENT (numcli),


);

ALTER TABLE OPERATION ADD(CONSTRAINT pk_operation

PRIMARY KEY (numcli, numccl, numoper),CONSTRAINT fk_oper_cclFOREIGN KEY (numcli, numoper)REFERENCES COMPTECLIENT (numcli, numccl),

CONSTRAINT fk_oper_codeoperFOREIGN KEY (numtypeoper)REFERENCES typeoperation (numtypeoper),

CONSTRAINT montant_operationCHECK(montantoper <> 0 AND montantoper >= -1000 AND montantoper <= 1000)

);

INSERT INTO TYPECCL VALUES ((SELECT nvl(MAX(numtypeccl), 0) + 1FROM TYPECCL),’Compte courant’);

INSERT INTO TYPECCL VALUES (

80

(SELECT nvl(MAX(numtypeccl), 0) + 1FROM TYPECCL),’livret’);

INSERT INTO TYPECCL VALUES ((SELECT nvl(MAX(numtypeccl), 0) + 1FROM TYPECCL),’PEL’);

INSERT INTO TYPEOPERATION VALUES ((SELECT nvl(MAX(numtypeoper), 0) + 1FROM TYPEOPERATION),’depot especes’);

INSERT INTO TYPEOPERATION VALUES ((SELECT nvl(MAX(numtypeoper), 0) + 1FROM TYPEOPERATION),’prelevement’);

INSERT INTO TYPEOPERATION VALUES ((SELECT nvl(MAX(numtypeoper), 0) + 1FROM TYPEOPERATION),’virement’);

INSERT INTO TYPEOPERATION VALUES ((SELECT nvl(MAX(numtypeoper), 0) + 1FROM TYPEOPERATION),’retrait’);



);

INSERT INTO PERSONNE VALUES (1, ’de Montmirail, dit le Hardi’, ’Godefroy’, NULL, NULL);INSERT INTO PERSONNE VALUES (16, ’Canat de Chizy’, ’Edith’, NULL, NULL);INSERT INTO PERSONNE VALUES (2, ’Le Croquant’, ’Jacqou’, 1, 16);INSERT INTO PERSONNE VALUES (3, ’La Fripouille’, ’Jacqouille’, 1, 16);INSERT INTO PERSONNE VALUES (4, ’Bush’, ’Kate’, NULL, NULL);INSERT INTO PERSONNE VALUES (13, ’Granger’, ’Hermione’, NULL, NULL);INSERT INTO PERSONNE VALUES (5, ’Du Femur’, ’Medor’, 3,4 );INSERT INTO PERSONNE VALUES (12, ’Kobalevskaıa’, ’Sofia’, NULL, NULL);INSERT INTO PERSONNE VALUES (6, ’Rieu’, ’Andre’, NULL, NULL);

81

INSERT INTO PERSONNE VALUES (7, ’Bontoutou’, ’Rex’, 6, 4);INSERT INTO PERSONNE VALUES (8, ’Dijkstra’, ’Edvard’, 2, 13);INSERT INTO PERSONNE VALUES (9, ’Leibniz’, ’Gottfrie1d Wilhem’, 8, 12);INSERT INTO PERSONNE VALUES (10, ’Bach’, ’Johann Sebastien’, 5, 12);INSERT INTO PERSONNE VALUES (17, ’Mathieu’, ’Mireille’, NULL, NULL);INSERT INTO PERSONNE VALUES (11, ’Lemarchal’, ’Gregory’, 10, 17);INSERT INTO PERSONNE VALUES (15, ’Socrate’, NULL, 3, 13);INSERT INTO PERSONNE VALUES (19, ’Leroy’, ’Nolwen’, NULL, NULL);INSERT INTO PERSONNE VALUES (20, ’Bartoli’, ’Jennifer’, 9, 19);INSERT INTO PERSONNE VALUES (21, ’Fabian’, ’Lara’, 10, 17);INSERT INTO PERSONNE VALUES (14, ’Stone’, ’Sharon’, 15, 20);INSERT INTO PERSONNE VALUES (18, ’Frege’, ’Elodie’, 7, 13);

COMMIT;

Vous 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, vousratrapperez l’exception DUP VAL ON INDEX.

Exercice 1

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

Exercice 2

Les scripts remplissant la table Operation ne fonctionneront pas aujourd’hui... Meme s’il fonctionnaientla derniere fois. Trouvez les codes d’erreurs des exceptions levees par ces scripts, rattrapez-les de la faconla plus appropriee qui soit.

82


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 ce code :



);

INSERT INTO PERSONNE VALUES (1, ’de Montmirail, dit le Hardi’, ’Godefroy’, NULL, NULL);INSERT INTO PERSONNE VALUES (16, ’Canat de Chizy’, ’Edith’, NULL, NULL);INSERT INTO PERSONNE VALUES (2, ’Le Croquant’, ’Jacqou’, 1, 16);INSERT INTO PERSONNE VALUES (3, ’La Fripouille’, ’Jacqouille’, 1, 16);INSERT INTO PERSONNE VALUES (4, ’Bush’, ’Kate’, NULL, NULL);INSERT INTO PERSONNE VALUES (13, ’Granger’, ’Hermione’, NULL, NULL);INSERT INTO PERSONNE VALUES (5, ’Du Femur’, ’Medor’, 3,4 );INSERT INTO PERSONNE VALUES (12, ’Kobalevskaıa’, ’Sofia’, NULL, NULL);INSERT INTO PERSONNE VALUES (6, ’Rieu’, ’Andre’, NULL, NULL);INSERT INTO PERSONNE VALUES (7, ’Bontoutou’, ’Rex’, 6, 4);INSERT INTO PERSONNE VALUES (8, ’Dijkstra’, ’Edvard’, 2, 13);INSERT INTO PERSONNE VALUES (9, ’Leibniz’, ’Gottfrie1d Wilhem’, 8, 12);INSERT INTO PERSONNE VALUES (10, ’Bach’, ’Johann Sebastien’, 5, 12);INSERT INTO PERSONNE VALUES (17, ’Mathieu’, ’Mireille’, NULL, NULL);INSERT INTO PERSONNE VALUES (11, ’Lemarchal’, ’Gregory’, 10, 17);INSERT INTO PERSONNE VALUES (15, ’Socrate’, NULL, 3, 13);INSERT INTO PERSONNE VALUES (19, ’Leroy’, ’Nolwen’, NULL, NULL);INSERT INTO PERSONNE VALUES (20, ’Bartoli’, ’Jennifer’, 9, 19);INSERT INTO PERSONNE VALUES (21, ’Fabian’, ’Lara’, 10, 17);INSERT INTO PERSONNE VALUES (14, ’Stone’, ’Sharon’, 15, 20);INSERT INTO PERSONNE VALUES (18, ’Frege’, ’Elodie’, 7, 13);

COMMIT;

Exercice 3

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

83

Exercice 4

Ecrire une fonction cousins germains prenant deux numeros de personnes en parametre et retournant vraisi et seulement 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 etse rappellant recursivement 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 lenumero est passe en parametre.

Exercice 7

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

Exercice 8

Ecrire une fonction prenant en parametre deux numeros de personne A et B et retournant, si l’un est unascendant de 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 leplus de generations.

Exercice 10

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

84

2.15 Curseurs

Exercice 1

Refaites le td application du PL/SQL en utilisant les curseurs.

Exercice 2

En utlisant la table PERSONNE du tp precedent, ecrivez une fonction affichant toute la descendance d’unepersonne.

85


L’interet de ce tp etant de vous familiariser avec les curseurs parametres, vous ferez en sorte de ne pascontourner leur usage.

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 chacunde ces comptes, l’historique des operations.

86

2.17 Triggers

Etant donnee la base suivante :

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

CREATE TABLE ETUDIANT(numEtud number,nom varchar2(40),prenom varchar2(40),datenaiss date,civilite varchar2(4),patronyme varchar2(40),numsecu varchar2(15) NOT NULL);

CREATE TABLE MODULE(codMod number,nomMod varchar2(15),effecMax number DEFAULT 30);

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

CREATE TABLE INSCRIPTION(numEtud number,codMod number,dateInsc 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));

ALTER TABLE ETUDIANT ADDCONSTRAINT pk_etudiantPRIMARY KEY (numEtud);ALTER TABLE MODULE ADDCONSTRAINT pk_modulePRIMARY KEY (codMod);ALTER TABLE EXAMEN ADDCONSTRAINT pk_examenPRIMARY KEY (codMod, codExam);

87

ALTER TABLE PREREQUIS ADDCONSTRAINT pk_prerequisPRIMARY KEY (codMod, codModPrereq);ALTER TABLE INSCRIPTION ADDCONSTRAINT pk_inscriptionPRIMARY KEY (codMod, numEtud);ALTER TABLE RESULTAT ADDCONSTRAINT pk_resultatPRIMARY KEY (codMod, numEtud, codExam);

ALTER TABLE INSCRIPTION ADD(CONSTRAINT fk_inscription_etudiantFOREIGN KEY (numEtud)REFERENCES ETUDIANT(numEtud),CONSTRAINT fk_inscription_moduleFOREIGN KEY (codMod)REFERENCES MODULE(codMod));ALTER TABLE PREREQUIS ADD(CONSTRAINT fk_prerequis_codmodFOREIGN KEY (codMod)REFERENCES MODULE(codMod),CONSTRAINT fk_prerequis_codmodprereqFOREIGN KEY (codModPrereq)REFERENCES MODULE(codMod));ALTER TABLE EXAMEN ADDCONSTRAINT fk_examenFOREIGN KEY (codMod)REFERENCES MODULE(codMod);ALTER TABLE RESULTAT ADD(CONSTRAINT fk_resultat_examenFOREIGN KEY (codMod, codExam)REFERENCES EXAMEN(codMod, codExam),CONSTRAINT fk_resultat_inscriptionFOREIGN KEY (codMod, numEtud)REFERENCES INSCRIPTION(codMod,numEtud));

ALTER TABLE ETUDIANT ADD(CONSTRAINT ck_civiliteCHECK(civilite IN (’Mr’, ’Mme’, ’Mlle’)),CONSTRAINT ck_civilite_numsecuCHECK(SUBSTR(numsecu, 1, 1) = ’2’ OR patronyme IS NULL),CONSTRAINT ck_length_numsecuCHECK(length(numsecu) = 15),CONSTRAINT ck_annee_numsecu CHECK(

88

to_char(datenaiss, ’yy’) = substr(numsecu, 2, 2)));

Implementez les contraintes suivantes. Vous ferez des sous-programme tenant sur une page, et ne contenantpas plus de trois niveaux d’imbrication. Vous repertorierez les numeros d’erreurs que vous affecterez achaque 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,mais comme 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 apasse les examens 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 tableprerequis que s’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 uneinscription invalidee.

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

89

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 ) ;

90

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 ) ;

−− 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−− et 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 )

91

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 t

FOREIGN 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 ) ;

92

3.2 Requetes monotabulaires

−− 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 e

93

WHERE 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 ∗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 ) ;

94

3.3 Requetes multitabulaires

−− 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 dWHERE nomfou = ’ f3 ’AND f . numfou = d . numfouAND d . numprod = p . numprod ;

−− 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, enf . prenomFROM personne s f , personne enfWHERE en f . mere = s f . numpersAND s f . prenom = ’ So 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 j

95

WHERE 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;

−− 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 )

)) ;

96


−− 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 ) ;

97

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 ;

98


−− 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

99

))

)FROM FOURNISSEUR F;

−− Exercice 7

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

100

(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 ;

101


−− 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 ) ) ;

102

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)

) ;

103

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 ) ) ;

104

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)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 e s−− 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 ie r ’ ) ,19) ;

105

UPDATE RESULTAT SET note = 20wHERE

numEtud = (SELECT numEtud FROM ETUDIANT WHERE nom = ’ Four i e 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

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 =


106

) = 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

(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(∗ )

107

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 ’)

) ;

108

3.10 Introduction au PL/SQL

−− 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;/

109

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 ’ ) ;

ELSE

110

DBMS OUTPUT. PUT LINE(X | |’ n” e s t pas dans l e tab leau ’ ) ;

END IF ;END;

END;/

−− 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 | | ’ ) ’ ) ;

111


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

−− 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 ) . p rev 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;/

112

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 ) ;

EXCEPTIONWHEN NO DATA FOUND THEN

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

113

DECLAREnumClient CLIENT. numcli%TYPE;tCCL TYPECCL. numtypeccl%TYPE;nto TYPEOPERATION. numtypeoper%TYPE;Y A UN GRO BLEME EXCEPTION;

BEGINSELECT numtypeoper INTO nto

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

114

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

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 ;

115

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 ,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;/

116


−− 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

117

CREATE OR REPLACE PROCEDUREa i e u l (P PERSONNE. numpers%type ) IS

row PERSONNE%rowtype ;nb NUMBER;

BEGINSELECT count (∗ ) INTO NB

FROM PERSONNEWHERE numpers = P;

IF (NB = 1) THENSELECT ∗ INTO row


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


IF (NB = 1) THENSELECT ∗ INTO row


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;

118

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

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

119

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;/

120

CREATE OR REPLACE PROCEDUREcreateVirement ( numcl ient CLIENT. numcli%type , value NUMBER) IS

BEGININSERT INTO OPERATION VALUES

( numclient ,1 ,(SELECT nvl (MAX( numoper ) , 0) + 1


) ,(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)

)

121

) ;INSERT INTO COMPTECLIENT VALUES

( numclient ,2 ,(SELECT numtypeccl

FROM TYPECCLWHERE nomtypeccl = ’ virement ’) ,sysdate ,(SELECT numpersFROM PERSONNELWHERE numpers =

(SELECT MAX( numcli )FROM CLIENT)

)) ;

cred i tAccount ( numclient , numclient ∗ 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;/

122


−− 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 ( ) ;

123

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

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

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 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 ) ) ;

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 req

124

FOREIGN 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)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 ) ) ;

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))

) ;

−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−− 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;

125

/

−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−

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;/

−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−

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(∗ )

126

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;/

−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−− 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

127

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;/

−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−

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;/

−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−

128

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;

BEGINFOR p IN prereq LOOP

SELECT count (∗ ) INTO nbLignesFROM 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

129

−− 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 ,

(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 ;

130

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 −−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−

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 MODULE

131

FOR 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) ;

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

132

((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 ie r ’ ) ,19 ) ;

UPDATE RESULTAT SETnote = 20wHERE

numEtud = (SELECT numEtud FROM ETUDIANT WHERE nom = ’ Four i e 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 ) ;

133

oracle[1]

Documents

types e

donnese e

dclaration e

procdures e

types numriques e

exceptions prdniese

sous requtes correlese

jointures rexives e