Introduction au PL/SQL Oracle

Alexandre Mesl e 17 octobre 2011

Table des mati`res e

1 Notes de cours 1.1 Introduction au PL/SQL . . . . . . . . . . . . . . . . . 1.1.1 PL/SQL . . . . . . . . . . . . . . . . . . . . . . 1.1.2 Blocs . . . . . . . . . . . . . . . . . . . . . . . 1.1.3 Achage . . . . . . . . . . . . . . . . . . . . . 1.1.4 Variables . . . . . . . . . . . . . . . . . . . . . 1.1.5 Traitements conditionnels . . . . . . . . . . . . 1.1.6 Traitements rptitifs . . . . . . . . . . . . . . e e 1.2 Tableaux et structures . . . . . . . . . . . . . . . . . . 1.2.1 Tableaux . . . . . . . . . . . . . . . . . . . . . 1.2.2 Structures . . . . . . . . . . . . . . . . . . . . . 1.3 Utilisation du PL/SQL . . . . . . . . . . . . . . . . . . 1.3.1 Aectation . . . . . . . . . . . . . . . . . . . . 1.3.2 Tables et structures . . . . . . . . . . . . . . . 1.3.3 Transactions . . . . . . . . . . . . . . . . . . . 1.4 Exceptions . . . . . . . . . . . . . . . . . . . . . . . . 1.4.1 Rattraper une exception . . . . . . . . . . . . . 1.4.2 Exceptions prdnies . . . . . . . . . . . . . . e e 1.4.3 Codes derreur . . . . . . . . . . . . . . . . . . 1.4.4 Dclarer et lancer ses propres exceptions . . . . e 1.5 Sous-programmes . . . . . . . . . . . . . . . . . . . . . 1.5.1 Procdures . . . . . . . . . . . . . . . . . . . . e 1.5.2 Fonctions . . . . . . . . . . . . . . . . . . . . . 1.6 Curseurs . . . . . . . . . . . . . . . . . . . . . . . . . . 1.6.1 Introduction . . . . . . . . . . . . . . . . . . . 1.6.2 Les curseurs . . . . . . . . . . . . . . . . . . . . 1.7 Curseurs parametrs . . . . . . . . . . . . . . . . . . . e 1.7.1 Introduction . . . . . . . . . . . . . . . . . . . 1.7.2 Dnition . . . . . . . . . . . . . . . . . . . . . e 1.7.3 Dclaration . . . . . . . . . . . . . . . . . . . . e 1.7.4 Ouverture . . . . . . . . . . . . . . . . . . . . . 1.7.5 Lecture dune ligne, fermeture . . . . . . . . . . 1.7.6 Boucle pour . . . . . . . . . . . . . . . . . . . . 1.7.7 Exemple rcapitulatif . . . . . . . . . . . . . . e 1.8 Triggers . . . . . . . . . . . . . . . . . . . . . . . . . . 1.8.1 Principe . . . . . . . . . . . . . . . . . . . . . . 1.8.2 Classication . . . . . . . . . . . . . . . . . . . 1.8.3 Cration . . . . . . . . . . . . . . . . . . . . . . e 1.8.4 Acc`s aux lignes en cours de modication . . . e 1.8.5 Contourner le probl`me des tables en mutation e 1.9 Packages . . . . . . . . . . . . . . . . . . . . . . . . . . 1.9.1 Principe . . . . . . . . . . . . . . . . . . . . . . 1.9.2 Spcication . . . . . . . . . . . . . . . . . . . e 1.9.3 Corps . . . . . . . . . . . . . . . . . . . . . . . 3 3 3 3 3 3 4 4 5 5 6 8 8 8 9 10 10 11 11 11 13 13 13 15 15 15 17 17 17 17 17 17 18 18 19 19 19 19 20 22 25 25 25 25

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

2 Exercices 2.1 Introduction au PL/SQL . 2.2 Tableaux et Structures . . 2.3 Utilisation PL/SQL . . . 2.4 Exceptions . . . . . . . . 2.5 Sous-programmes . . . . . 2.6 Curseurs . . . . . . . . . . 2.7 Curseurs parametrs . . . e 2.8 Triggers . . . . . . . . . . 2.9 Packages . . . . . . . . . . 2.10 Rvisions . . . . . . . . . e

. . . . . . . . . .

. . . . . . . . . .

. . . . . . . . . .

. . . . . . . . . .

. . . . . . . . . .

. . . . . . . . . .

. . . . . . . . . .

. . . . . . . . . .

. . . . . . . . . .

. . . . . . . . . .

. . . . . . . . . .

. . . . . . . . . .

. . . . . . . . . .

. . . . . . . . . .

. . . . . . . . . .

. . . . . . . . . .

. . . . . . . . . .

. . . . . . . . . .

. . . . . . . . . .

. . . . . . . . . .

. . . . . . . . . .

. . . . . . . . . .

. . . . . . . . . .

. . . . . . . . . .

. . . . . . . . . .

. . . . . . . . . .

. . . . . . . . . .

. . . . . . . . . .

. . . . . . . . . .

. . . . . . . . . .

. . . . . . . . . .

. . . . . . . . . .

. . . . . . . . . .

. . . . . . . . . .

. . . . . . . . . .

. . . . . . . . . .

. . . . . . . . . .

. . . . . . . . . .

. . . . . . . . . .

. . . . . . . . . .

. . . . . . . . . .

. . . . . . . . . .

. . . . . . . . . .

27 27 28 30 31 32 33 34 35 36 37 38 38 39 42 46 49 52 53 62 63 67 67 68 69 70 71 72 74 76 78

3 Corrigs e 3.1 Introduction au PL/SQL . . . . . . . . 3.2 Tableaux et Structures . . . . . . . . . 3.3 Application du PL/SQL et Exceptions 3.4 Sous-programmes . . . . . . . . . . . . 3.5 Curseurs . . . . . . . . . . . . . . . . . 3.6 Curseurs paramtrs . . . . . . . . . . e e 3.7 Triggers . . . . . . . . . . . . . . . . . 3.8 Packages . . . . . . . . . . . . . . . . . 3.9 Rvisions . . . . . . . . . . . . . . . . e A Scripts de cration de bases e A.1 Livraisons Sans contraintes . . . . A.2 Modules et prerequis . . . . . . . . A.3 Gomtrie . . . . . . . . . . . . . . e e A.4 Livraisons . . . . . . . . . . . . . . A.5 Arbre gnalogique . . . . . . . . . e e A.6 Comptes bancaires . . . . . . . . . A.7 Comptes bancaires avec exceptions A.8 Secrtariat pdagogique . . . . . . e e A.9 Mariages . . . . . . . . . . . . . . .

. . . . . . . . .

. . . . . . . . .

. . . . . . . . .

. . . . . . . . .

. . . . . . . . .

. . . . . . . . .

. . . . . . . . .

. . . . . . . . .

. . . . . . . . .

. . . . . . . . .

. . . . . . . . .

. . . . . . . . .

. . . . . . . . .

. . . . . . . . .

. . . . . . . . .

. . . . . . . . .

. . . . . . . . .

. . . . . . . . .

. . . . . . . . .

. . . . . . . . .

. . . . . . . . .

. . . . . . . . .

. . . . . . . . .

. . . . . . . . .

. . . . . . . . .

. . . . . . . . .

. . . . . . . . .

. . . . . . . . .

. . . . . . . . .

. . . . . . . . .

. . . . . . . . .

. . . . . . . . .

. . . . . . . . .

. . . . . . . . .

. . . . . . . . .

. . . . . . . . .

. . . . . . . . .

. . . . . . . . .

. . . . . . . . .

. . . . . . . . .

. . . . . . . . .

. . . . . . . . .

. . . . . . . . .

. . . . . . . . .

. . . . . . . . .

. . . . . . . . .

. . . . . . . . .

. . . . . . . . .

. . . . . . . . .

. . . . . . . . .

. . . . . . . . .

. . . . . . . . .

. . . . . . . . .

. . . . . . . . .

. . . . . . . . .

. . . . . . . . .

. . . . . . . . .

. . . . . . . . .

. . . . . . . . .

. . . . . . . . .

. . . . . . . . .

. . . . . . . . .

. . . . . . . . .

. . . . . . . . .

. . . . . . . . .

. . . . . . . . .

. . . . . . . . .

. . . . . . . . .

. . . . . . . . .

. . . . . . . . .

. . . . . . . . .

. . . . . . . . .

. . . . . . . . .

. . . . . . . . .

Chapitre 1

Notes de cours
1.1
1.1.1

Introduction au PL/SQL
PL/SQL

Le PL de PL/SQL signie Procedural Language. Il sagit dune extension procdurale du SQL permettant deectuer e des traitements complexes sur une base de donnes. Les possibilits oertes sont les mmes quavec des langages e e e impratifs (instructions en squence) classiques. e e Ecrivez-le dans un diteur dont vous copierez le contenu dans SQL+. Un script crit en PL/SQL se termine obligae e toirement par un /, sinon SQL+ ne linterpr`te pas. Sil contient des erreurs de compilation, il est possible dacher les e messages derreur avec la commande SQL+ : SHOW ERRORS.

1.1.2

Blocs

Tout code crit dans un langage procdural est form de blocs. Chaque bloc comprend une section de dclaration e e e e de variables, et un ensemble dinstructions dans lequel les variables dclares sont visibles. e e 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.1.3

Achage

Pour acher le contenu dune variable, les procdures DBMS OUTPUT.PUT() et DBMS OUTPUT.PUT LINE() prennent e en argument une valeur ` acher ou une variable dont la valeur est ` acher. Par dfaut, les fonctions dachage a a e sont desactives. Il convient, ` moins que vous ne vouliez rien voir sacher, de les activer avec la commande SQL+ e a SET SERVEROUTPUT ON.

1.1.4

Variables

Une variable se dclare de la sorte : e nom type [ : = initialisation ] ; Linitisation est optionnelle. Nous utiliserons les mmes types primitifs que dans les tables. Par exemple : e SET SERVEROUTPUT ON DECLARE c varchar2 ( 1 5 ) := H e l l o World ! ; BEGIN DBMS_OUTPUT . PUT_LINE ( c ) ; END; / Les aectations se font avec la syntaxe variable := valeur ; 3

1.1.5

Traitements conditionnels

Le IF et le CASE fonctionnent de la mme faon que dans les autres langages impratifs : e c e IF / c o n d i t i o n 1 / THEN / i n s t r u c t i o n s 1 / ELSE / i n s t r u c t i o n s 2 / END IF ; voire IF / c o n d i t i o n 1 / THEN / i n s t r u c t i o n s 1 / ELSIF / c o n d i t i o n 2 / / i n s t r u c t i o n s 2 / ELSE / i n s t r u c t i o n s 3 / END IF ; Les conditions sont les mmes quen SQL. Le switch du langage C simplmente en PL/SQL de la faon suivante : e e c CASE / v a r i a b l e / W E / v a l e u r 1 / THEN HN / i n s t r u c t i o n s 1 / W E / v a l e u r 2 / THEN HN / i n s t r u c t i o n s 2 / ... W E / v a l e u r n / THEN HN / 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 f a u t / e END CASE;

1.1.6
LOOP

Traitements rptitifs e e

LOOP ... END LOOP ; permet dimplmenter les boucles e / i n s t r u c t i o n s / END LOOP ; Linstruction EXIT WHEN permet de quitter une boucle. LOOP / i n s t r u c t i o n s / EXIT W E / c o n d i t i o n / ; HN END LOOP ; La boucle FOR existe aussi en PL/SQL : FOR / v a r i a b l e / IN / i n f / . . / sup / LOOP / i n s t r u c t i o n s / END LOOP ; Ainsi que la boucle WHILE : WHILE / c o n d i t i o n / LOOP / i n s t r u c t i o n s / END LOOP ; Est-il possible, en bidouillant, dimplmenter une boucle DO ... WHILE ? e

1.2
1.2.1

Tableaux et structures
Tableaux

Cration dun type tableau e Les types tableau doivent tre dnis explicitement par une dclaration de la forme e e e TYPE / t y p e / IS VARRAY ( / t a i l l e / ) OF / t y p e E l e m e n t s / ; type est le nom du type tableau cre par cette instruction e taille est le nombre maximal dlments quil est possible de placer dans le tableau. ee typeElements est le type des lments qui vont tre stocks dans le tableau, il peut sagir de nimporte quel ee e e type. Par exemple, crons un type tableau de nombres indic de 1 ` 10, que nous appelerons numberTab e e a TYPE numberTab IS VARRAY ( 1 0 ) OF N M E U B R;

Dclaration dun tableau e Dornavant, le type dun tableau peut tre utilis au mme titre que NUMBER ou VARCHAR2. Par exemple, dclarons e e e e e un tableau appel t de type numberTab, e DECLARE TYPE numberTab IS VARRAY ( 1 0 ) OF N M E U B R; t numberTab ; BEGIN / i n s t r u c t i o n s / END; /

Allocation dun tableau La cration dun type tableau met ` disposition un constructeur du mme nom que le type cr. Cette fonction e a e ee rserve de lespace mmoire pour ce tableau et retourne ladresse mmoire de la zone rserve, il sagit dune sorte de e e e e e malloc. Si, par exemple, un type tableau numtab a t cre, la fonction numtab() retourne une tableau vide. ee e DECLARE TYPE numberTab IS VARRAY ( 1 0 ) OF N M E U B R; t numberTab ; BEGIN t := 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 dutiliser le tableau... Dimensionnement dun tableau Le tableau retourn par le constructeur est vide. Il convient ensuite de rserver de lespace pour stocker les lments e e ee quil va contenir. On utilise pour cela la mthode EXTEND(). EXTEND sinvoque en utilisant la notation pointe. Par e e exemple, DECLARE TYPE numberTab IS VARRAY ( 1 0 ) OF N M E U B R; t numberTab ; BEGIN 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 dutiliser les lments du tableau t(1), t(2), t(3) et t(4). ee Il nest pas possible dtendre un tableau ` une taille suprieure ` celle spcie lors de la cration du type tableau e a e a e e e associ. e Utilisation dun tableau On acc`de, en lecture et en criture, au i-`me lment dune variable tabulaire nomm T avec linstruction T(i). e e e ee e Les lments sont indics ` partir de 1. ee e a Eectuons, par exemple, une permutation circulaire vers la droite des lments du tableau t. ee DECLARE TYPE numberTab IS VARRAY ( 1 0 ) OF N M E U B R; t numberTab ; i number ; k number ; BEGIN t := 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.2.2

Structures

Un structure est un type regroupant plusieurs types. Une variable de type structur contient plusieurs variables, e ces variables sappellent aussi des champs. Cration dun type structur e e On dnit un type structur de la sorte : e e TYPE / nomType / IS RECORD ( / l i s t e d e s champs / ); nomType est le nom du type structur construit avec la syntaxe prcdente. La liste suit la mme syntaxe que la e e e e liste des colonnes dune table dans un CREATE TABLE. Par exemple, construisons le type point (dans IR2 ), TYPE point IS RECORD ( abscisse N M E , U BR ordonnee N M E U BR ); Notez bien que les types servant ` dnir un type structur peuvent tre quelconques : variables scalaires, tableaux, a e e e structures, etc. Dclaration dune variable de type structur e e point est maintenant un type, il devient donc possible de crer des variables de type point, la r`gle est toujours la e e mme pour dclarer des variables en PL/SQL, par exemple e e 6

p point ; permet de dclarer une variable p de type point. e Utilisation dune variable de type structur e Pour accder ` un champ dune variable de type structur, en lecture ou en criture, on utilise la notation pointe : e a e e e v.c est le champ appel c de la variable structur appele v. Par exemple, e e e DECLARE TYPE point IS RECORD ( abscisse N M E , U BR ordonnee N M E U BR ); p point ; BEGIN p . abscisse := 1 ; p . ordonnee := 3 ; DBMS_OUTPUT . PUT_LINE ( p . a b s c i s s e = | | p . abscisse | | and p . ordonnee = | | p . ordonnee ) ; END; / Le script ci-dessous cre le type point, puis cre une variable t de type point, et enn aecte aux champs abscisse e e et ordonnee du point p les valeurs 1 et 3.

1.3

Utilisation du PL/SQL

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

1.3.1

Aectation

On place dans une variable le rsultat dune requte en utilisant le mot-cl INTO. Les instructions e e e SELECT champ_1 , . . . , champ_n INTO v_1 , . . . , v_n F O ... R M aecte aux variables v 1, ..., v n les valeurs retournes par la requte. Par exemple e e DECLARE num N M E U B R; nom V R H R 3 0 ) := Poup e Batman ; A C A 2( e BEGIN SELECT numprod INTO num F O PRODUIT R M W E E nomprod = nom ; HR DBMS_OUTPUT . PUT_LINE ( L a r t i c l e | | nom | | a pour numro | | num ) ; e END; / Prtez attention au fait que la requte doit retourner une et une une seule ligne, sinon, une erreur se produit ` e e a lexcution. e

1.3.2

Tables et structures

Si vous ne tenez pas ` vous prendre la tte pour choisir le type de chaque variable, demandez-vous ce que vous a e allez mettre dedans ! Si vous tenez ` y mettre une valeur qui se trouve dans une colonne dune table, il est possible de a vous rfrer directement au type de cette colonne avec le type nomTable.nomColonne%type. Par exemple, ee DECLARE num PRODUIT . numprod%type ; nom PRODUIT . nomprod%type := Poup e Batman ; e BEGIN SELECT numprod INTO num F O PRODUIT R M W E E nomprod = nom ; HR DBMS_OUTPUT . PUT_LINE ( L a r t i c l e | | nom | | a pour numro | | num ) ; e END; / Pour aller plus loin, il est mme possible de dclarer une structure pour reprsenter une ligne dune table, le type e e e porte alors le nom suivant : nomTable%rowtype. DECLARE nom PRODUIT . nomprod%type := Poup e Batman ; e ligne PRODUIT%rowtype ; BEGIN SELECT INTO ligne F O PRODUIT R M W E E nomprod = nom ; HR DBMS_OUTPUT . PUT_LINE ( L a r t i c l e | | ligne . nomprod | | a pour numro | | ligne . numprod ) ; e END; /

1.3.3

Transactions

Un des mcanismes les plus puissants des SGBD rcents rside dans le syst`me des transactions. Une transaction e e e e est un ensemble doprations atomiques, cest-`-dire indivisible. Nous considrerons quun ensemble doprations est e a e e indivisible si une excution partielle de ces instructions poserait des probl`mes dintgrit dans la base de donnes. e e e e e Par exemple, dans le cas dune base de donnes de gestion de comptes en banque, un virement dun compte ` un autre e a se fait en deux temps : crditer un compte dune somme s, et dbiter un autre de la mme somme s. Si une erreur e e e survient pendant la deuxi`me opration, et que la transaction est interrompue, le virement est incomplet et le patron e e va vous assassiner. Il convient donc de disposer dun mcanisme permettant de se protger de ce genre de dsagrment. Plutt que e e e e o se casser la tte ` tester les erreurs ` chaque tape et ` balancer des instructions permettant de revenir en arri`re, e a a e a e nous allons utiliser les instructions COMMIT et ROLLBACK. Voici le squelette dun exemple : / i n s t r u c t i o n s / IF / e r r e u r / THEN ROLLBACK; ELSE C M I O MT; END; Le ROLLBACK annule toutes les modications faites depuis le dbut de la transaction (donc depuis le prcdent e e e COMMIT), COMMIT les enregistre dnitivement dans la base de donnes. e e La variable denvironnement AUTOCOMMIT, qui peut tre positionne ` ON ou ` OFF permet dactiver la gestion des e e a a transactions. Si elle est positionne ` ON, chaque instruction a des rpercussions immdiates dans la base, sinon, les e a e e modications ne sont eectives quune fois quun COMMIT a t excut. ee e e

1.4

Exceptions

Le mcanisme des exceptions est implment dans la plupart des langages rcent, notament orients objet. Cette e e e e e faon de programmer a quelques avantages immdiats : c e obliger les programmeurs ` traiter les erreurs : combien de fois votre prof de C a hurl en vous suppliant a e de vrier les valeurs retournes par un malloc, ou un fopen ? La plupart des compilateurs des langages ` e e a exceptions (notamment java) ne compilent que si pour chaque erreur potentielle, vous avez prpar un bloc de e e code (ventuellement vide...) pour la traiter. Le but est de vous assurer que vous navez pas oubli derreur. e e Rattraper les erreurs en cours dexcution : Si vous programmez un syst`me de scurit de centrale e e e e nuclaire ou un pilote automatique pour laviation civile, une erreur de mmoire qui vous acherait lcran e e e bleu de windows, ou le message Envoyer le rapport derreur ?, ou plus simplement le fameux Segmentation fault produirait un eet des plus mauvais. Certaines erreurs dxecution sont rattrapables, autrement dit, il est e possible de rsoudre le probl`me sans interrompre le programme. e e Ecrire le traitement des erreurs ` part : Pour des raisons abilit, de lisibilit, il a t considr que a e e ee ee mlanger le code normal et le traitement des erreurs tait un style de programmation perfectible... Dans les e e langages ` exception, les erreurs sont traites ` part. a e a

1.4.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 / BEGIN / i n s t r u c t i o n s / EXCEPTION / t r a i t e m e n t d e s e r r e u r s / END; Une exception est une erreur type, elle porte un nom, au mme titre quune variable a une identicateur, par e exemple GLUBARF. Lorsque dans les instructions, lerreur GLUBARF se produit, le code du BEGIN sinterrompt et le code de la section EXCEPTION est lanc. On dit aussi que quand une exception est leve (raised) (on dit aussi jete e e e (thrown)), on la rattrape (catch) dans le bloc EXCEPTION. La section EXCEPTION a la forme suivante : EXCEPTION W E E1 THEN HN / t r a i t e m e n t W E E2 THEN HN / t r a i t e m e n t W E E3 THEN HN / t r a i t e m e n t W E OTHERS THEN HN / t r a i t e m e n t END;

/ / / /

On num`re les erreurs les plus pertinentes en utilisant leur nom et en consacrant ` chacune delle un traitement e e a particulier pour rattraper (ou propager) lerreur. Quand un bloc est trait, les WHEN suivants ne sont pas valus. e e e OTHERS est lexception par dfaut, OTHERS est toujours vri, sauf si un cas prcdent a t vri. Dans lexemple e e e e e ee e e 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 / C M I O MT; EXCEPTION W E GLUBARF THEN HN ROLLBACK; DBMS_OUTPUT . PUT_LINE ( GLUBARF e x c e p t i o n r a i s e d ! ) ; W E OTHERS THEN HN DBMS_OUTPUT . PUT_LINE ( SQLCODE = | | SQLCODE ) ; DBMS_OUTPUT . PUT_LINE ( SQLERRM = | | SQLERRM ) ; 10

END; Les deux variables globales SQLCODE et SQLERRM contiennent respectivement le code derreur Oracle et un message derreur correspondant ` la derni`re exception leve. Chaque exception a donc, en plus dun nom, un code et un a e e message.

1.4.2

Exceptions prdnies e e

Bon nombre dexceptions sont prdnies par Oracle, par exemple e e NO DATA FOUND est leve quand la requte dune instruction de la forme SELECT ... INTO ... ne retourne e e aucune ligne e e TOO MANY ROWS est leve quand la requte dune instruction de la forme SELECT ... INTO ... retourne plusieurs lignes DUP VAL ON INDEX est leve si une insertion (ou une modication) est refuse ` cause dune contrainte dunicit. e e a e On peut enrichir notre exemple de la sorte : DECLARE num N M E U B R; nom V R H R 3 0 ) := Poup e Batman ; A C A 2( e BEGIN SELECT numprod INTO num F O PRODUIT R M W E E nomprod = nom ; HR DBMS_OUTPUT . PUT_LINE ( L a r t i c l e | | nom | | a pour numro | | num ) ; e EXCEPTION W E NO_DATA_FOUND THEN HN DBMS_OUTPUT . PUT_LINE ( Aucun a r t i c l e ne p o r t e l e nom | | nom ) ; W E TOO_MANY_ROWS THEN HN DBMS_OUTPUT . PUT_LINE ( P l u s i e u r s a r t i c l e s p o r t e n t l e nom | | nom ) ; W E OTHERS THEN HN DBMS_OUTPUT . PUT_LINE ( I l y a un g r o s p r o b l` m e . . . ) ; e END; / SELECT numprod INTO num... l`ve une exception si la requte renvoie un nombre de lignes dirent de 1. e e e

1.4.3

Codes derreur

Je vous encore menti, certaines exceptions nont pas de nom. Elle ont seulement un code derreur, il est conseill e de se reporter ` la documentation pour les obtenir. On les traite de la faon suivante a c EXCEPTION W E OTHERS THEN HN IF SQLCODE = CODE1 THEN / t r a i t e m e n t / ELSIF SQLCODE = CODE2 THEN / t r a i t e m e n t / ELSE DBMS_OUTPUT . PUT_LINE ( J v o i s pas c que ca peut et re . . . ) ; END; Cest souvent le cas lors de violation de contraintes.

1.4.4

Dclarer et lancer ses propres exceptions e

Exception est un type, on dclare donc les exceptions dans une section DECLARE. Une exception se lance avec e linstruction RAISE. Par exemple,

11

DECLARE GLUBARF EXCEPTION; BEGIN RAISE GLUBARF ; EXCEPTION W E GLUBARF THEN HN DBMS_OUTPUT . PUT_LINE ( g l u b a r f r a i s e d . ) ; END; /

12

1.5
1.5.1

Sous-programmes
Procdures e

Syntaxe On dnit une procdure de la sorte e e CREATE OR REPLACE PROCEDURE / nom / ( / p a r a m e t r e s / ) IS / d e c l a r a t i o n d e s 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; les param`tres sont une simple liste de couples nom type. Par exemple, la procedure suivante ache un compte ` e a rebours. CREATE OR REPLACE PROCEDURE compteARebours ( n N M E U B R) IS BEGIN IF n >= 0 THEN DBMS_OUTPUT . PUT_LINE ( n ) ; compteARebours ( n 1 ) ; END IF ; END;

Invocation En PL/SQL, une procdure sinvoque tout simplement avec son nom. Mais sous SQL+, on doit utiliser le mot-cl e e CALL. Par exemple, on invoque le compte ` rebours sous SQL+ avec la commande CALL compteARebours(20). a Passage de param`tres e Oracle permet le passage de param`tres par rfrence. Il existe trois types de passage de param`tres : e ee e IN : passage par valeur OUT : aucune valeur passe, sert de valeur de retour e IN OUT : passage de param`tre par rfrence e ee Par dfaut, le passage de param`tre se fait de type IN. e e CREATE OR REPLACE PROCEDURE incr ( val IN OUT N M E U B R) IS BEGIN val := val + 1 ; END;

1.5.2
Syntaxe

Fonctions

On cre une nouvelle fonction de la faon suivante : e c CREATE OR REPLACE FUNCTION / nom / ( / p a r a m e t r e s / ) RETURN / t y p e / IS / d e c l a r a t i o n d e s 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; Linstruction RETURN sert ` retourner une valeur. Par exemple, a CREATE OR REPLACE FUNCTION module ( a N M E , b N M E U BR U B R) RETURN N M E IS U BR BEGIN IF a < b THEN RETURN a ; ELSE 13

RETURN module ( a b , b ) ; END IF ; END;

Invocation Tout comme les procdures, linvocation des fonctions ne pose aucun probl`me en PL/SQL, par contre, sous SQL+, e e cest quelque peu particulier. On passe par une pseudo-table nomme DUAL de la faon suivante : e c SELECT module ( 2 1 , 1 2 ) F O DUAL ; R M

Passage de param`tres e Les param`tres sont toujours passs avec le type IN. e e

14

1.6
1.6.1

Curseurs
Introduction

Les instructions de type SELECT ... INTO ... manquent de souplesse, elles ne fontionnent que sur des requtes e retourant une et une seule valeur. Ne serait-il pas intressant de pouvoir placer dans des variables le rsultat dune e e requte retournant plusieurs lignes ? A mditer... e e

1.6.2

Les curseurs

Un curseur est un objet contenant le rsultat dune requte (0, 1 ou plusieurs lignes). e e dclaration e Un curseur se dclare dans une section DECLARE : e CURSOR / nomcurseur / IS / r e q u t e / ; e Par exemple, si on tient ` rcuprer tous les employs de la table EMP, on dclare le curseur suivant. a e e e e CURSOR emp_cur IS SELECT F O EMP ; R M Ouverture Lors de louverture dun curseur, la requte du curseur est value, et le curseur contient toutes les donnes e e e e retournes par la requte. On ouvre un curseur dans une section BEGIN : e e OPEN / nomcurseur / ; Par exemmple, DECLARE CURSOR emp_cur IS SELECT F O EMP ; R M BEGIN OPEN emp_cur ; / U t i l i s a t i o n du c u r s e u r / END;

Lecture dune ligne Une fois ouvert, le curseur contient toutes les lignes du rsultat de la requte On les rcup`re une par une en e e e e utilisant le mot-cl FETCH : e FETCH / nom curseur / INTO / l i s t e v a r i a b l e s / ;

La liste de variables peut tre remplace par une structure de type nom curseur%ROWTYPE. Si la lecture de la ligne e e choue, parce quil ny a plus de ligne ` lire, lattribut %NOTFOUND prend la valeur vrai. e a DECLARE CURSOR emp_cur IS SELECT F O EMP ; R M ligne emp_cur%rowtype BEGIN OPEN emp_cur ; LOOP FETCH emp_cur INTO ligne ; EXIT W E emp_cur%NOTFOUND ; HN DBMS_OUTPUT . PUT_LINE ( ligne . ename ) ; END LOOP ; / . . . / END;

15

Fermeture Apr`s utilisation, il convient de fermer le curseur. e CLOSE / nomcurseur / ; Compltons notre exemple, e DECLARE CURSOR emp_cur IS SELECT F O EMP ; R M ligne emp_cur%rowtype ; BEGIN OPEN emp_cur ; LOOP FETCH emp_cur INTO ligne ; EXIT W E emp_cur%NOTFOUND ; HN DBMS_OUTPUT . PUT_LINE ( ligne . ename ) ; END LOOP ; CLOSE emp_cur ; END; / Le programme ci-dessus peut aussi scrire e DECLARE CURSOR emp_cur IS SELECT F O EMP ; R M ligne emp_cur%rowtype ; BEGIN OPEN emp_cur ; FETCH emp_cur INTO ligne ; WHILE emp_cur%FOUND LOOP DBMS_OUTPUT . PUT_LINE ( ligne . ename ) ; FETCH emp_cur INTO ligne ; END LOOP ; CLOSE emp_cur ; END;

Boucle FOR Il existe une boucle FOR se chargeant de louverture, de la lecture des lignes du curseur et de sa fermeture, FOR ligne IN emp_cur LOOP / Traitement / END LOOP ; Par exemple, DECLARE CURSOR emp_cur IS SELECT F O EMP ; R M ligne emp_cur%rowtype ; BEGIN FOR ligne IN emp_cur LOOP DBMS_OUTPUT . PUT_LINE ( ligne . ename ) ; END LOOP ; END; /

16

1.7
1.7.1

Curseurs parametrs e
Introduction

A votre avis, le code suivant est-il valide ? DECLARE N M E n := 1 4 ; U BR BEGIN DECLARE CURSOR C IS SELECT F O PERSONNE R M W E E numpers >= n ; HR ROW C%rowType ; BEGIN FOR ROW IN C LOOP DBMS_OUTPUT . PUT_LINE ( ROW . numpers ) ; END LOOP ; END; END; / Rponse : non. La requte dun curseur ne peut pas contenir de variables dont les valeurs ne sont pas xes. e e e Pourquoi ? Parce que les valeurs des ces sont susceptibles de changer entre la dclaration du curseur et son ouverture. e Le rem`de est un curseur paramtr. e e e

1.7.2

Dnition e

Un curseur paramtr est un curseur dont la requte contient des variables dont les valeurs ne seront xes qu` e e e e a louverture.

1.7.3

Dclaration e

On prcise la liste des noms et des type des param`tres entre parenth`ses apr`s le nom du curseur : e e e e CURSOR / nom / ( / l i s t e d e s p a r a m` t r e s / ) IS e / r e q u t e / e Par exemple, creons une requte qui, pour une personne donne, nous donne la liste des noms et prnoms de ses e e e e enfants : CURSOR enfants ( numparent N M E U B R) IS SELECT F O PERSONNE R M W E E pere = numparent HR OR mere = numparent ;

1.7.4

Ouverture

On ouvre un curseur paramtr en passant en param`tre les valeurs des variables : e e e OPEN / nom / ( / l i s t e d e s p a r a m` t r e s / ) e Par exemple, OPEN enfants ( 1 ) ;

1.7.5

Lecture dune ligne, fermeture

la lecture dune ligne suit les mmes r`gles quavec un curseur non paramtr. e e e e 17

1.7.6

Boucle pour

La boucle pour se charge de louverture, il convient donc de placer les param`tre dans lentte de la boucle, e e FOR / v a r i a b l e / IN / nom / ( / l i s t e p a r a m` t r e s / ) LOOP e / i n s t r u c t i o n s / END LOOP ; Par exemple, FOR e IN enfants ( 1 ) LOOP DBMS_OUTPUT . PUT_LINE ( e . nompers | | END LOOP ; | | e . prenompers ) ;

1.7.7

Exemple rcapitulatif e
CURSOR parent IS SELECT F O PERSONNE ; R M p parent%rowtype ; CURSOR enfants ( numparent N M E U B R) IS SELECT F O PERSONNE R M W E E pere = numparent HR OR mere = numparent ; e enfants%rowtype ;

DECLARE

BEGIN FOR p IN parent LOOP DBMS_OUTPUT . PUT_LINE ( Les e n f a n t s de | | p . prenom | | | | p . nom | | s o n t : ) ; FOR e IN enfants ( p . numpers ) LOOP DBMS_OUTPUT . PUT_LINE ( | | e . prenom | | | | e . nom ); END LOOP ; END LOOP ; END; /

18

1.8
1.8.1

Triggers
Principe

Un trigger est une procdure stocke qui se lance automatiquement lorsquun vnement se produit. Par vnement, e e e e e e on entend dans ce cours toute modication des donnes se trouvant dans les tables. On sen sert pour contrler ou e o appliquer des contraintes quil est impossible de formuler de faon dclarative. c e

1.8.2

Classication

Type dvnement e e Lors de la cration dun trigger, il convient de prciser quel est le type dvnement qui le dclenche. Nous raliserons e e e e e e dans ce cours des triggers pour les vnements suivants : e e INSERT DELETE UPDATE Moment de lxecution e On prcise aussi si le trigger doit tre xecut avant (BEFORE) ou apr`s (AFTER) lvnement. e e e e e e e Evnements non atomiques e Lors que lon fait un DELETE ..., il y a une seule instruction, mais plusieurs lignes sont aectes. Le trigger doit-il e tre excut pour chaque ligne aecte (FOR EACH ROW), ou seulement une fois pour toute linstruction (STATEMENT) ? e e e e un FOR EACH ROW TRIGGER est excut ` chaque fois quune ligne est aecte. e ea e un STATEMENT TRIGGER est xecute ` chaque fois quune instruction est lance. e e a e

1.8.3
Syntaxe

Cration e

On dclare un trigger avec linstruction suivante : e CREATE OR REPLACE TRIGGER nomtrigger [ BEFORE | AFTER ] [INSERT | DELETE | UPDATE] ON nomtable [ FOR EACH ROW | ] DECLARE / d e c l a r a t i o n s / BEGIN / i n s t r u c t i o n s / END; Par exemple, SQL> 2 3 4 5 6 CREATE OR REPLACE TRIGGER pasDeDeleteDansClient BEFORE DELETE ON CLIENT BEGIN RAISE_APPLICATION_ERROR ( 20555 , Va t e f a i r e . . . ) ; END; /

D clencheur cr . e ee SQL> SELECT C U T( ) O N 2 F O CLIENT ; R M C U T( ) O N 21 SQL> DELETE F O CLIENT ; R M 19

DELETE F O CLIENT R M ERREUR ` la ligne 1 : a ORA 20555: Va te faire . . . ORA 06512: ` SCOTT.PASDEDELETEDANSCLIENT , ligne 2 a ORA 04088: erreur lors d ex cution du d clencheur SCOTT.PASDEDELETEDANSCLIENT e e

SQL> SELECT C U T( ) O N 2 F O CLIENT ; R M C U T( ) O N 21 Linstruction RAISE APPLICATION ERROR(code, message) l`ve une exception sans nom portant un code code et e un message derreur message. Vous remarquez que comme lerreur a t leve avant la suppression, les donnes sont ee e e toujours prsentes dans la table CLIENT. Le trigger a contrl une r`gle, et comme elle ntait pas respecte, il a lanc e oe e e e e une erreur. Combinaisons dvnements e e Il est possible, en sparant les types dvnement par le mot-cl OR, de dnir un trigger dclench par plusieurs e e e e e e e vnements. Les variables boolennes INSERTING, UPDATING et DELETING permettent didentier lvnement qui a e e e e e dclench le trigger. e e CREATE OR REPLACE TRIGGER afficheEvenement BEFORE INSERT OR UPDATE OR DELETE ON CLIENT FOR EACH ROW BEGIN IF INSERTING THEN DBMS_OUTPUT . PUT_LINE ( I n s e r t i o n dans CLIENT ) ; ELSIF UPDATING THEN DBMS_OUTPUT . PUT_LINE ( Mise a j o u r dans CLIENT ) ; ELSE DBMS_OUTPUT . PUT_LINE ( S u p p r e s s i o n dans CLIENT ) ; END IF ; END;

1.8.4

Acc`s aux lignes en cours de modication e

Dans les FOR EACH ROW triggers, il est possible avant la modication de chaque ligne, de lire lancienne ligne et la nouvelle ligne par lintermdiaire des deux variables structures :old et :new. Par exemple le trigger suivant empche e e e de diminuer un salaire : CREATE OR REPLACE TRIGGER pasDeBaisseDeSalaire BEFORE UPDATE ON EMP FOR EACH ROW BEGIN IF ( : old . sal > : new . sal ) THEN RAISE_APPLICATION_ERROR ( 20567 , Pas de b a i s s e de s a l a i r e ! ) ; END IF ; END;

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

20

SQL> 2 3 4 5 6 7 8 9

CREATE OR REPLACE TRIGGER beforeStatement BEFORE UPDATE ON CLIENT DECLARE NB N M E U B R; BEGIN SELECT C U T( ) INTO NB O N F O CLIENT ; R M END; /

D clencheur cr . e ee SQL> SQL> 2 3 4 5 6 7 8 9

CREATE OR REPLACE TRIGGER afterStatement AFTER UPDATE ON CLIENT DECLARE NB N M E U B R; BEGIN SELECT C U T( ) INTO NB O N F O CLIENT ; R M END; /

D clencheur cr . e ee SQL> SQL> UPDATE CLIENT SET nomcli = nomcli ; 21 ligne ( s ) mise ( s ) ` jour . a SQL> SQL> 2 3 4 5 6 7 8 9 10

CREATE OR REPLACE TRIGGER beforeForEachRow BEFORE UPDATE ON CLIENT FOR EACH ROW DECLARE NB N M E U B R; BEGIN SELECT C U T( ) INTO NB O N F O CLIENT ; R M END; /

D clencheur cr . e ee SQL> SQL> UPDATE CLIENT SET nomcli = nomcli ; UPDATE CLIENT SET nomcli = nomcli ERREUR ` la ligne 1 : a ORA 04091: la table SCOTT . CLIENT est en mutation ; le d clencheur ou la e fonction ne peut la voir ORA 06512: ` SCOTT.BEFOREFOREACHROW , ligne 4 a ORA 04088: erreur lors d ex cution du d clencheur SCOTT.BEFOREFOREACHROW e e

SQL> DROP TRIGGER beforeForEachRow ; D clencheur supprim . e e

21

SQL> SQL> SQL> 2 3 4 5 6 7 8 9 10

CREATE OR REPLACE TRIGGER afterForEachRow AFTER UPDATE ON CLIENT FOR EACH ROW DECLARE NB N M E U B R; BEGIN SELECT C U T( ) INTO NB O N F O CLIENT ; R M END; /

D clencheur cr . e ee SQL> SQL> UPDATE CLIENT SET nomcli = nomcli ; UPDATE CLIENT SET nomcli = nomcli ERREUR ` la ligne 1 : a ORA 04091: la table SCOTT . CLIENT est en mutation ; le d clencheur ou la e fonction ne peut la voir ORA 06512: ` SCOTT.AFTERFOREACHROW , ligne 4 a ORA 04088: erreur lors d ex cution du d clencheur SCOTT.AFTERFOREACHROW e e

1.8.5

Contourner le probl`me des tables en mutation e

Il existe plusieurs faons de contourner ce probl`me : c e Utiliser un STATEMENT trigger. Comme on ne sait pas quelles lignes ont t modies, on est oblig de toutes ee e e les traiter. Cette approche prsente donc un inconvnient majeur : elle nous am`ne ` eectuer de nombreux e e e a traitements inutiles. En ayant des donnes redondantes. Il sut que les donnes servant ` la vrication se trouvent dans une autre e e a e table que celle en mutation. Cette mthode a pour inconvnient la mmoire occupe et la quantit de code ` crire e e e e e ae pour maintenir la cohrence des donnes. Dans la plupart des cas, cette solution est malgr tout la meilleure. e e e Colonnes supplmentaires e Par exemple, si lon souhaite empcher un client davoir plus de 10 comptes en banque, une solution est de placer e dans la table client une colonne contenant le nombre de comptes. ALTER TABLE CLIENT A D nbComptes number ; D UPDATE CLIENT SET nbComptes = 0 ; Une fois cette table cre, il convient de sassurer que les donnes de la colonne nbComptes contient toujours les e e bonnes valeurs. On le fait avec plusieurs sous-programmes : CREATE OR REPLACE TRIGGER metAJourNbComptes AFTER INSERT OR UPDATE OR DELETE ON COMPTECLIENT BEGIN UPDATE CLIENT SET nbComptes = ( SELECT C U T( ) O N F O COMPTECLIENT CC R M W E E CC . numCli = numCli HR ); END; / CREATE OR REPLACE TRIGGER verifieNbComptes

22

BEFORE INSERT ON COMPTECLIENT FOR EACH ROW DECLARE nbComptes N M E U B R; BEGIN SELECT nbComptes INTO nbComptes F O CLIENT R M W E E numCli = : new . numcli ; HR IF ( nbComptes >= 1 0 ) THEN RAISE_APPLICATION_ERROR ( 20556 , Ce c l i e n t a d e j a t r o p de comptes ) ; END IF ; END; / On peut aner en remplaant metAJourNbComptes par plusieurs sous-programmes : c CREATE OR REPLACE TRIGGER initialiseNbComptes BEFORE INSERT ON CLIENT FOR EACH ROW BEGIN : new . nbComptes := 0 ; END; / CREATE OR REPLACE TRIGGER metAJourNbComptes AFTER INSERT OR UPDATE OR DELETE ON COMPTECLIENT FOR EACH ROW BEGIN IF DELETING OR UPDATING THEN UPDATE CLIENT SET nbComptes = nbComptes 1 W E E numcli = : old . numcli ; HR END IF ; IF INSERTING OR UPDATING THEN UPDATE CLIENT SET nbComptes = nbComptes + 1 W E E numcli = : new . numcli ; HR END IF ; END; /

Tables supplmentaires e Si lon souhaite par exemple empcher les circuits dans la table PERSONNE, il est ncessaire de faire un parcours e e de graphe. Ce qui ncessite des SELECT dans la table en cours de mutation. La seule solution est dans ce cas davoir e une table miroir qui contient les colonnes cls primaire et trang`res de cette table, et de sen servir pour dtecter les e e e e circuits. CREATE TABLE MIRRORPERSONNE ( numpers N M E PRIMARY KEY, U BR pere N M E , U BR mere N M E U BR ); Nous allons ensuite procder de mme, en rpercutant chaque opration de PERSONNE sur MIRRORPERSONNE. e e e e CREATE OR REPLACE TRIGGER miseAJourMirrorPersonne BEFORE UPDATE OR INSERT OR DELETE ON PERSONNE FOR EACH ROW BEGIN IF DELETING OR UPDATING THEN 23

DELETE F O MIRRORPERSONNE R M W E E numpers = : old . numpers ; HR END IF ; IF INSERTING OR UPDATING THEN INSERT INTO MIRRORPERSONNE VALUES ( : new . numpers , : new . pere , : new . mere ) ; END IF ; END; / Une fois cela fait, il sut de rechercher si une personne insre est une descendante delle mme dans MIRRORPERSONNE. ee e CREATE OR REPLACE FUNCTION trouveCircuit ( current N M E , toFind N M E U BR U B R) RETURN BOOLEAN IS numPere N M E U B R; numMere N M E U B R; BEGIN IF ( current IS NULL) THEN RETURN FALSE; END IF ; SELECT pere , mere INTO numPere , numMere F O MIRRORPERSONNE R M W E E numPers = current ; HR RETURN ( numPere = toFind OR numMere = toFind OR trouveCircuit ( numPere , toFind ) OR trouveCircuit ( numMere , toFind ) ) ; END; / CREATE OR REPLACE TRIGGER verifieCircuit AFTER UPDATE OR INSERT ON PERSONNE FOR EACH ROW BEGIN IF ( trouveCircuit ( : new . numPers , : new . numPers ) ) THEN RAISE_APPLICATION_ERROR ( 20557 , C i r c u i t dans l a r b r e g n a l o g i q u e . ) ; e e END IF ; END; /

24

1.9
1.9.1

Packages
Principe

Un package est un ensemble de sous-programmes et de variables form par e Une spcication : dclaration de variables et de sous-programmes e e Un corps : implmentation des sous-programmes e Tout ce qui se trouve dans la spcication doit se trouver dans le corps, mais la rciproque est fausse. Un package e e satisfait les points suivants : encapsulation : certains traitements sont masqus, seule la spcication du package est visible. Cela a pour e e avantage de simplier la tche de celui qui va utiliser le package. a modularit : il est possible de dvelopper sparment les diverses parties de lapplication. le dveloppement e e e e e devient ainsi un assemblage de package. Ces deux aspects fournissent une souplesse certaine au niveau du dveloppement : il est possible de modier le e corps dun package sans changer sa spcication, donc sans modier le fonctionnement de lapplication. e

1.9.2

Spcication e

La syntaxe permettant de crer lentte est la suivante : e e CREATE OR REPLACE PACKAGE nompackage IS / declarations / END nomPackage ; / Par exemple, CREATE OR REPLACE PACKAGE compteur IS procedure reset ; function nextValue return number ; END compteur ; /

1.9.3

Corps

La syntaxe permettant de crer le corps est la suivante : e CREATE OR REPLACE PACKAGE BODY nompackage IS / implementation / END nomPackage ; / Par exemple, CREATE OR REPLACE PACKAGE BODY compteur IS cpt N M E U B R := 0 ; PROCEDURE reset IS BEGIN cpt := 0 ; END; FUNCTION nextValue RETURN N M E IS U BR BEGIN cpt := cpt + 1 ; RETURN cpt 1 ; END; END compteur ; 25

/ On peut utiliser un package depuis nimporte quel script PL/SQL : DECLARE nb N M E U B R; BEGIN FOR nb IN 4 . . 2 0 LOOP DBMS_OUTPUT . PUT_LINE ( COMPTEUR . nextValue ( ) ) ; END LOOP ; COMPTEUR . RESET ( ) ; FOR nb IN REVERSE 0 . . 1 0 LOOP DBMS_OUTPUT . PUT_LINE ( COMPTEUR . nextValue ( ) ) ; END LOOP ; END; /

26

Chapitre 2

Exercices
2.1 Introduction au PL/SQL

Exercice 1
Ecrivez un programme aectant les valeurs 1 et 2 ` deux variables a et b, puis permutant les valeurs de ces deux a variables.

Exercice 2
Ecrivez un programme plaant la valeur 10 dans une variable a, puis achant la factorielle de a. c

Exercice 3
Ecrivez un programme plaant les valeurs 48 et 84 dans deux variables a et b puis achant le pgcd de a et b. c

27

2.2

Tableaux et Structures

Exercice 1
1. Crez un type tableau pouvant contenir jusqu` 50 entiers. e a 2. Crez une variable de ce type , faites une allocation dynamique et dimensionnez ce tableau ` 20 emplacements. e a 3. Placez dans ce tableau la liste des 20 premiers carrs parfaits : 1, 4, 9, 16, 25, . . . e 4. Inversez lordre des lments du tableau ee 5. Achez le tableau.

Exercice 2
Triez le tableau prcdent avec la mthode du tri ` bulle. e e e a

Exercice 3
Recherchez, par dichotomie, si llment 225 se trouve dans le tableau. ee

Exercice 4
On implmente des listes cha ees avec des tableaux de la sorte, e n SET SERVEROUTPUT ON DECLARE -- Maillon dune liste cha^ne e TYPE CELL IS RECORD ( -- Donne de chaque maillon e data INTEGER, -- Indice du maillon prcdent de la liste, e e -- -1 sil ny en a pas previous INTEGER, -- Indice du maillon suivant de la liste, -- -1 sil ny en a pas next INTEGER ); -- Type tableau contenant les maillons de la liste TYPE TREE IS VARRAY (19) OF CELL; -- Tableau contenant les maillons de la liste t TREE; -- indice du premier lment de la liste e e first integer; -- indice du dernier lment de la liste e e last integer; BEGIN t := TREE(); t.extend(19); -- Initialisation FOR i IN 1..19 LOOP t(i).data := power(i, 5) mod 19 ; t(i).previous := i-1; t(i).next := i+1; END LOOP; first := 1; last := 19; t(first).previous := -1; t(last).next := -1;

28

-- Affichage DECLARE p integer := first; BEGIN WHILE p <> -1 LOOP DBMS_OUTPUT.PUT_LINE(( || p || , || t(p).data || , || t(p).previous || , || t(p).next || )); p := t(p).next; END LOOP; END; /* Ecrivez la suite vous-m^me... */ e END; / Inversez lordre des lments de la liste, sans changer les indices des maillons (seulement en modiant le cha ee nage).

Exercice 5
Utilisez le tri ` bulle pour remettre les lments dans lordre. Les indications sont les mmes : ne dplacez pas les a ee e e maillons, vous navez le droit de toucher quau cha nage. Bon courage, laspirine nest pas fournie.

29

2.3

Utilisation PL/SQL

Nous travaillerons sur les donnes A.6 et A.5. e Vous noublierez 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 squence de nombres de 1 ` 21. e a Utilisez une boucle dans laquelle vous placerez une requte pour recopier les couples nom/prnom de la table personne e e dans la table CLIENT.

Exercice 2
Ecrivez un script rcuprant le client de cl primaire la plus leve, et injectant ce client dans la table PERSONNEL. e e e e e

Exercice 3
Ouvrez un compte courant pour chaque personne, eectuez un dpt en esp`ce gal ` numpers 100 euros. e o e e a

Exercice 4
Ouvrez un livret pour chaque personne ayant un numpers pair, faites un virement de leur compte courant vers ce livret de sorte quil ne reste plus que 500 sur leur compte.

30

2.4

Exceptions

Nous utiliserons les donnes de A.7 et A.5 e Vous tes invits ` modier le code de la sance prcdente. Chaque fois quun SELECT ... INTO ... sera eectu, e e a e e e e vous rattraperez les exceptions NO DATA FOUND et TOO MANY ROWS. A chaque insertion, vous ratrapperez lexception DUP VAL ON INDEX.

Exercice 1
Faites de sorte que les scripts important les donnes des tables CLIENT ne puissent tre excuts quune seule fois. e e e e

Exercice 2
Les scripts remplissant la table Operation ne fonctionneront pas aujourdhui... Mme sil fonctionnaient la derni`re e e fois. Trouvez les codes derreurs des exceptions leves par ces scripts, rattrapez-les de la faon la plus approprie qui e c e soit.

31

2.5

Sous-programmes

Exercice 1
Ecrire une fonction rcursive retournant bn , avec n entier positif ou nul. e

Exercice 2
Amliorer la fonction prcdente en utilisant le fait que e e e bn = (b2 ) 2 si n est pair. Pour les questions suivantes, utilisez les donnes de A.5. e
n

Exercice 3
Ecrire une fonction demi-freres prenant deux numros de personnes en param`tre et retournant vrai si et seulement e e si ces deux personnes ont un parent en commun.

Exercice 4
Ecrire une fonction cousins germains prenant deux numros de personnes en param`tre et retournant vrai si et e e seulement si ces deux deux individus sont cousins germains.

Exercice 5
Ecrire une procdure rcursive achant le nom de la personne dont le numro est pass en param`tre et se rappellant e e e e e rcursivement sur le p`re de cette personne. Faites de sorte ` ne pas utiliser dexceptions. e e a

Exercice 6
Ecrire une procdure rcursive achant les noms des ascendants de sexe masculin de la personne dont le numro e e e est pass en param`tre. e e

Exercice 7
Ecrire une fonction rcursive prenant deux numros de personne A et B et retournant vrai si A est un ascendant e e de B.

Exercice 8
Ecrire une fonction prenant en param`tre deux numros de personne A et B et retournant, si lun est un ascendant e e de lautre, le nombre de gnrations les sparant, 1 si lun nest pas un ascendant de lautre. e e e

Exercice 9
Prparez un verre daspirine et crivez une requte retournant le(s) couples(s) personnes spares par le plus de e e e e e gnrations. e e

Exercice 10
Reprendre le code du tp prcdent, le dcouper en sous-programmes de la faon la moins inintelligente possible. e e e c Bon courage.

32

2.6

Curseurs

Exercice 1
Refaites les exercices de 2.3 en utilisant les curseurs.

Exercice 2
En utlisant les donnees A.5, ecrivez une fonction achant toute la descendance dune personne.

33

2.7

Curseurs parametrs e

Lintrt de ces exercices tant de vous familiariser avec les curseurs paramtrs, vous ferez en sorte de ne pas ee e e e contourner leur usage. Nous utiliserons les donnes de A.6 e

Exercice 1
Ecrire une procdure qui ache tous les clients, et pour chaque client, la liste des comptes. e

Exercice 2
Ecrire une procdure qui ache tous les clients, et pour chaque client, la liste des comptes, et pour chacun de ces e comptes, lhistorique des oprations. e

34

2.8

Triggers

Implmentez les contraintes suivantes dans les donnes de les donnes de A.8. Vous ferez des sous-programmes e e e tenant sur une page, et ne contenant pas plus de trois niveaux dimbrication. Vous rpertorierez les numros derreurs e e que vous aecterez a chaque leve dexception. ` e 1. Il ne doit pas tre possible de modier la note min dans la table prerequis. e 2. Dans un module, il ne doit pas y avoir plus de effecMax l`ves inscrits. ee 3. On ne peut crer un examen pour un module que sil y a des l`ves inscrits dans ce module. e ee 4. Un l`ve ne peut passer un examen que si sa date dinscription est antrieure ` la date de lexamen. ee e a 5. Il ne doit pas y avoir de circuit dans la table prerequis (il existe une faon de la vrier en PL/SQL, mais c e comme vous ne la connaissez pas, faites un parcours en profondeur du graphe des pr-requis) e 6. Un l`ve sinscrivant ` un module doit avoir eu au moins la note min ` tous les modules pr-requis. ee a a e 7. Ajouter dans tudiant un champ moyenne, celui-ci contiendra la moyenne de chaque tudiant sil a pass les e e e examens de tous les modules dans lesquels il est inscrit. 8. Revenez sur la premi`re contrainte : il ne doit tre possible de modier une note min dans la table prerequis que e e sil nexiste pas dl`ve dont une inscription serait invalide. ee e 9. Il ne doit tre possible de modier effecMax que si des tudiants ne se retrouvent pas avec une inscription e e invalide. e Libre ` vous par la suite de trouver dautres contraintes et de les implmenter. a e

35

2.9

Packages

Exercice 1
Lancez deux sessions simultanment sur le mme serveur et invoquez les sous-programmes du package compteur e e depuis chacune des sessions. Que remarquez-vous ?

Exercice 2
Implmenter le corps du package suivant (utilisez les donnes de A.5). e e
CREATE OR R E P L A C E P A C K A G E g e s t i o n _ a r b r e I S c i r c u i t exception ; c u r s o r f e u i l l e s r e t u r n p e r s o n n e%r o w t y p e ; p r o c e d u r e a j o u t e P e r s o n n e ( n o m p e r s o n n e . n o m%t y p e , p r e n o m p e r s o n n e . p r e n o m%t y p e , p e r e p e r s o n n e . p e r e%t y p e , m e r e p e r s o n n e . m e r e%t y p e ) ; p r o c e d u r e m o d i f i e P a r e n t s ( p e r s p e r s o n n e . n u m p e r s%t y p e , n u m P e r e p e r s o n n e . p e r e%t y p e , n u m M e r e p e r s o n n e . m e r e%t y p e ) ;

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

36

2.10

Rvisions e

Implmentez les contraintes suivantes dans les donnes de A.9. e e 1. Les parents dune mme personne sont des personnes direntes. e e 2. Larbre gnalogique ne contient pas de circuit. e e 3. Les dates de divorce sont ultrieures aux dates de mariage. e 4. Une mme personne ne peut pas tre marie ` plusieurs personnes simultanment. e e e a e 5. Personne ne peut tre p`re dune personne et m`re dune autre. e e e 6. Un mari ne peut pas tre m`re et une femme ne peut pas tre p`re. e e e e 7. Deux personnes ayant du sang en commun ne peuvent se marier.

37

Chapitre 3

Corrigs e
3.1
DECLARE a N ME U B R; b N ME U B R; t N ME U B R; BEGIN a := 1 ; b := 2 ; DBMS_OUTPUT DBMS_OUTPUT DBMS_OUTPUT t := a ; a := b ; b := t ; DBMS_OUTPUT DBMS_OUTPUT END; / E x e r c i c e 2 DECLARE a N ME U B R; res N M E U B R; counter N M E U B R; BEGIN a := 1 0 ; r e s := 1 ; c o u n t e r := a ; WHILE counter > 0 LOOP r e s := r e s c o u n t e r ; c o u n t e r := c o u n t e r 1 ; END L O O P ; D B M S _ O U T P U T . P U T _ L I N E ( a | | != | | r e s ) ; END; / E x e r c i c e 3 DECLARE a N M E := 4 8 ; U BR b N M E := 8 4 ; U BR amodb N M E U B R; BEGIN D B M S _ O U T P U T . P U T ( PGCD( | | a | | , | | b | | ) = ) ; WHILE b > 0 LOOP a m o d b := a ; W H I L E a m o d b >= b L O O P a m o d b := a m o d b b ; END L O O P ; a := b ; b := a m o d b ; END L O O P ; DBMS_OUTPUT . PUT_LINE ( a ) ; END; / . PUT_LINE ( a = | | a ) ; . PUT_LINE ( b = | | b ) ; . P U T _ L I N E ( Let s swap a and b . . . The r e s u l t

Introduction au PL/SQL

E x e r c i c e 1

is : );

. PUT_LINE ( a = | | a ) ; . PUT_LINE ( b = | | b ) ;

38

3.2

Tableaux et Structures

SET S E R V E R O U T P U T O N Tableaux DECLARE T Y P E m o n t a b I S V A R R A Y ( 5 0 ) O F INTEGER ; t montab ; BEGIN t := m o n t a b ( ) ; t . extend ( 2 0 ) ; I n i t i a l i s a t i o n F O R i IN 1 . . 2 0 L O O P t ( i ) := i i ; END L O O P ; I n v e r s i o n de l o r d r e d e s l m e n t s e e DECLARE temp i n t e g e r ; BEGIN F O R i IN 1 . . 1 0 L O O P t e m p := t ( i ) ; t ( i ) := t (20 i + 1 ) ; t (20 i +1) := t e m p ; END L O O P ; END; A f f i c h a g e F O R i IN 1 . . 2 0 L O O P DBMS_OUTPUT . PUT_LINE ( t ( | | i || ) = || t(i )); END L O O P ; Tri ` b u l l e a DECLARE temp i n t e g e r ; BEGIN F O R i IN R E V E R S E 2 . . 2 0 L O O P F O R j IN 2 . . i L O O P IF t ( j 1) > t ( j ) T E H N t e m p := t ( j ) ; t ( j ) := t ( j 1 ) ; t ( j 1) := t e m p ; END I F ; END L O O P ; END L O O P ; END; A f f i c h a g e F O R i IN 1 . . 2 0 L O O P DBMS_OUTPUT . PUT_LINE ( t ( | | i || ) = || t(i )); END L O O P ; Recherche par d i c h o t o m i e de l l m e n t 225 e e DECLARE i n f INTEGER := 1 ; s u p INTEGER := 2 0 ; m INTEGER ; X INTEGER := 4 0 0 ; BEGIN LOOP DBMS_OUTPUT . PUT_LINE ( i n f = | | inf ; sup = | | s u p ) ; m := ( i n f + s u p ) / 2 ; EXIT W E HN t ( m ) = X OR i n f = s u p ; IF t ( m ) > X T E H N s u p := m 1; ELSE i n f := m +1; END I F ; END L O O P ; IF t ( m ) = X T E H N DBMS_OUTPUT . PUT_LINE ( X | | e s t dans l e t a b l e a u ) ; ELSE DBMS_OUTPUT . PUT_LINE ( X | | n e s t pas dans l e t a b l e a u ) ; END I F ; END; END; /

||

39

 S t r u c t u r e s DECLARE M a i l l o n d une l i s t e c h an e e TYPE CELL IS RECORD ( Donne de chaque m a i l l o n e d a t a INTEGER, I n d i c e du m a i l l o n p r c d e n t de l a l i s t e , e e 1 s i l n y en a pas p r e v i o u s INTEGER, I n d i c e du m a i l l o n s u i v a n t de l a l i s t e , 1 s i l n y en a pas next INTEGER ); Type t a b l e a u c o n t e n a n t l e s m a i l l o n s de l a l i s t e TYPE TREE IS VARRAY ( 1 9 ) OF CELL ; Tableau c o n t e n a n t l e s m a i l l o n s de l a l i s t e t TREE ; i n d i c e du premier l m e n t de l a l i s t e e e first integer ; i n d i c e du d e r n i e r l m e n t de l a l i s t e e e last integer ; BEGIN t := T R E E ( ) ; t . extend ( 1 9 ) ; I n i t i a l i s a t i o n F O R i IN 1 . . 1 9 L O O P t ( i ) . d a t a := p o w e r ( i , 5 ) m o d 19 ; t ( i ) . p r e v i o u s := i 1; t ( i ) . next := i +1; END L O O P ; f i r s t := 1 ; l a s t := 1 9 ; t ( f i r s t ) . p r e v i o u s := 1; t ( l a s t ) . next := 1; A f f i c h a g e DECLARE p i n t e g e r := f i r s t ; BEGIN W H I L E p <> 1 L O O P DBMS_OUTPUT . PUT_LINE ( ( | | p | | t ( p ) . data | | , | | t ( p ) . previous | | , | | t ( p ) . next | | ) ) ; p := t ( p ) . next ; END L O O P ; END; I n v e r s i o n de l o r d r e d e s l m e n t s e e DECLARE t e m p INTEGER ; BEGIN F O R i IN 1 . . 1 9 L O O P t e m p := t ( i ) . p r e v i o u s ; t ( i ) . p r e v i o u s := t ( i ) . next ; t ( i ) . next := t e m p ; END L O O P ; f i r s t := 1 9 ; l a s t := 1 ; END; A f f i c h a g e DECLARE p i n t e g e r := f i r s t ; BEGIN W H I L E p <> 1 L O O P DBMS_OUTPUT . PUT_LINE ( ( | | p || , || t ( p ) . data | | , | | t ( p ) . previous | | , t ( p ) . next | | ) ) ; p := t ( p ) . next ; END L O O P ; END;

||

||

Tri ` b u l l e a DECLARE i i n t e g e r := l a s t ; j integer ; BEGIN W H I L E t ( t ( i ) . p r e v i o u s ) . p r e v i o u s <> 1 L O O P j := f i r s t ; W H I L E i< >j L O O P I F ( t ( j ) . d a t a > t ( t ( j ) . next ) . d a t a ) T E H N

40

 Echange de j e t t ( j ) . n e x t par m o d i f i c a t i o n du c h an a g e DECLARE a f t e r J INTEGER := t ( j ) . next ; b e f o r e J INTEGER := t ( j ) . p r e v i o u s ; BEGIN t ( 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 r e v i o u s := b e f o r e J ; t ( j ) . p r e v i o u s := a f t e r J ; I F t ( j ) . next <> 1 T E H N t ( t ( j ) . next ) . p r e v i o u s := j ; ELSE l a s t := j ; END I F ; I F t ( a f t e r J ) . p r e v i o u s <> 1 T E H N t ( t ( a f t e r J ) . p r e v i o u s ) . next := a f t e r J ; ELSE f i r s t := a f t e r J ; END I F ; IF afterJ = i T E H N i := j ; END I F ; END; ELSE j := t ( j ) . next ; END I F ; END L O O P ; i := t ( i ) . p r e v i o u s ; END L O O P ; END; A f f i c h a g e DECLARE p i n t e g e r := f i r s t ; BEGIN W H I L E p <> 1 L O O P DBMS_OUTPUT . PUT_LINE ( ( | | p | | t ( p ) . data | | , | | t ( p ) . previous | | , | | t ( p ) . next | | ) ) ; p := t ( p ) . next ; END L O O P ; END; END; /

||

41

3.3

Application du PL/SQL et Exceptions

SET S E R V E R O U T P U T O N SET A U T O C O M M I T O F F E x e r c i c e 1 DECLARE u n C l i e n t P E R S O N N E%R O W T Y P E ; n u m C l i e n t P E R S O N N E . n u m p e r s%t y p e ; Y _ A _ E U _ U N E _ M E R D E EXCEPTION; BEGIN F O R n u m C l i e n t IN 1 . . 2 1 L O O P BEGIN SELECT INTO u n C l i e n t F O R M PERSONNE W E E numpers = numClient ; HR INSERT INTO C L I E N T ( n u m c l i , n o m c l i , p r e n o m c l i ) VALUES ( unClient . numpers , u n C l i e n t . nom , unClient . prenom ) ; EXCEPTION W E NO_DATA_FOUND T E HN H N DBMS_OUTPUT . PUT_LINE ( Personne na l i d e n t i f i a n t | | numClient ) ; W E TOO_MANY_ROWS T E HN H N DBMS_OUTPUT . PUT_LINE ( C e t t e message ne d e v r a i t j a m a i s a p p a r at r e ! ) ; W E DUP_VAL_ON_INDEX T E HN H N DBMS_OUTPUT . PUT_LINE ( C o n t r a i n t e de c l v i o l e ! Message SQL : | | e e SQLERRM ) ; W E OTHERS T E HN H N RAISE Y_A_EU_UNE_MERDE ; END; END L O O P ; C M I O MT; EXCEPTION W E Y_A_EU_UNE_MERDE T E HN H N D B M S _ O U T P U T . P U T _ L I N E ( SQLCODE = | | S Q L C O D E ) ; D B M S _ O U T P U T . P U T _ L I N E ( I l y a eu une Merde ! ) ; R LB C O L A K; END; / E x e r c i c e 2 DECLARE u n C l i e n t C L I E N T%r o w t y p e ; BEGIN SELECT INTO u n C l i e n t F O R M CLIENT W E E numCli = HR ( SELECT M X( n u m c l i ) A F O R M CLIENT ); INSERT INTO P E R S O N N E L VALUES ( 1, unClient . nomcli , unClient . prenomcli , NULL, 1254.28 ); C M I O MT; EXCEPTION W E NO_DATA_FOUND T E HN H N D B M S _ O U T P U T . P U T _ L I N E ( Aucun c l i e n t ) ; W E DUP_VAL_ON_INDEX T E HN H N DBMS_OUTPUT . PUT_LINE ( I l y a un g r o s probl` me . . . J comprends pas c q u i s p a s s e ) ; e END; / E x e r c i c e 3

DECLARE n u m C l i e n t C L I E N T . n u m c l i%T Y P E ; t C C L T Y P E C C L . n u m t y p e c c l%T Y P E ; n t o T Y P E O P E R A T I O N . n u m t y p e o p e r%T Y P E ; Y _ A _ U N _ G R O _ B L E M E EXCEPTION; BEGIN SELECT n u m t y p e o p e r INTO n t o

42

F O R M TYPEOPERATION W E E n o m t y p e o p e r = d p t e s p` c e s ; HR e o e SELECT n u m t y p e c c l INTO t C C L F O R M TYPECCL W E E n o m t y p e C C L = Compte c o u r a n t ; HR F O R n u m C l i e n t IN 1 . . 2 1 L O O P BEGIN INSERT INTO C O M P T E C L I E N T VALUES ( numClient , 1, tCCL , SYSDATE , 1 ); INSERT INTO O P E R A T I O N VALUES ( numClient , 1, 1, nto , SYSDATE , numClient 100 , i n a u g u r a t i o n du compte ); C M I O MT; EXCEPTION W E OTHERS T E HN H N Adaptez l e numro du code , e c h e z moi a donne 2290 c I F S Q L C O D E = 2290 T E H N DECLARE t o t a l O P E R A T I O N . m o n t a n t o p e r%T Y P E := n u m C l i e n t 1 0 0 ; t o I n s e r t O P E R A T I O N . m o n t a n t o p e r%T Y P E ; c p t N M E := 1 ; U BR BEGIN WHILE total > 0 LOOP I F t o t a l > 1000 T E H N t o I n s e r t := 1 0 0 0 ; ELSE t o I n s e r t := t o t a l ; END I F ; INSERT INTO O P E R A T I O N VALUES ( numClient , 1, (SELECT n v l (M X( n u m o p e r ) , 0 ) + 1 A F O R M OPERATION W E E numcli = numClient HR A D numccl = 1 N ), nto , SYSDATE , toInsert , I n a u g u r a t i o n du compte | | c p t ); t o t a l := t o t a l t o I n s e r t ; c p t := c p t + 1 ; END L O O P ; EXCEPTION W E OTHERS T E HN H N D B M S _ O U T P U T . P U T _ L I N E ( MOD( t o t a l , 1000) = | | M O D ( t o t a l , D B M S _ O U T P U T . P U T _ L I N E ( SQLCODE = | | S Q L C O D E ) ; D B M S _ O U T P U T . P U T _ L I N E ( SQLERRM = | | S Q L E R R M ) ; RAISE Y_A_UN_GRO_BLEME ; END; ELSE D B M S _ O U T P U T . P U T _ L I N E ( SQLCODE = | | S Q L C O D E ) ; D B M S _ O U T P U T . P U T _ L I N E ( SQLERRM = | | S Q L E R R M ) ; R LB C O L A K; END I F ; END; END L O O P ; EXCEPTION W E NO_DATA_FOUND T E HN H N D B M S _ O U T P U T . P U T _ L I N E ( Pas de donn es ! ) ; e W E TOO_MANY_ROWS T E HN H N D B M S _ O U T P U T . P U T _ L I N E ( Trop de donn es ! ) ; e W E Y_A_UN_GRO_BLEME T E HN H N D B M S _ O U T P U T . P U T _ L I N E ( I l y a un g r o s probl` me ! ) ; e D B M S _ O U T P U T . P U T _ L I N E ( SQLCODE = | | S Q L C O D E ) ; D B M S _ O U T P U T . P U T _ L I N E ( SQLERRM = | | S Q L E R R M ) ; W E OTHERS T E HN H N D B M S _ O U T P U T . P U T _ L I N E ( SQLCODE = | | S Q L C O D E ) ; D B M S _ O U T P U T . P U T _ L I N E ( SQLERRM = | | S Q L E R R M ) ; END; / E x e r c i c e 4

1000));

43

DECLARE n u m C l i e n t C L I E N T . n u m c l i%T Y P E := 2 ; n u m C o m p t e L i v r e t T Y P E C C L . n u m t y p e C C L%T Y P E ; n t o T Y P E O P E R A T I O N . n u m t y p e o p e r%T Y P E ; m o n t a n t O P E R A T I O N . m o n t a n t o p e r%T Y P E ; Y _ A _ U N _ G R O _ B L E M E EXCEPTION; BEGIN SELECT n u m t y p e o p e r INTO n t o F O R M TYPEOPERATION W E E n o m t y p e o p e r = virement ; HR SELECT n u m t y p e c c l INTO n u m C o m p t e L i v r e t F O R M TYPECCL W E E nomtypeCcl = l i v r e t ; HR W H I L E n u m C l i e n t <= 21 L O O P BEGIN m o n t a n t := 100 n u m C l i e n t 5 0 0 ; INSERT INTO C O M P T E C L I E N T VALUES ( numClient , 2, numCompteLivret , SYSDATE , 1 ); INSERT INTO O P E R A T I O N VALUES ( numClient , 1, (SELECT n v l (M X( n u m o p e r ) , 0 ) + 1 A F O R M OPERATION W E E numcli = numClient HR A D numccl = 1) , N nto , SYSDATE , m o n t a n t , versement l i v r e t ); INSERT INTO O P E R A T I O N VALUES ( numClient , 2, (SELECT n v l (M X( n u m o p e r ) , 0 ) + 1 A F O R M OPERATION W E E numcli = numClient HR A D numccl = 2) , N nto , SYSDATE , montant , versement l i v r e t ); C M I O MT; EXCEPTION W E OTHERS T E HN H N idem I F S Q L C O D E = 2290 T E H N DECLARE t o t a l O P E R A T I O N . m o n t a n t o p e r%T Y P E := m o n t a n t ; t o M o v e O P E R A T I O N . m o n t a n t o p e r%T Y P E ; c p t N M E := 1 ; U BR BEGIN W H I L E t o t a l > 1000 L O O P I F t o t a l > 1000 T E H N t o M o v e := 1 0 0 0 ; ELSE t o m o v e := t o t a l ; END I F ; INSERT INTO O P E R A T I O N VALUES ( numClient , 1, (SELECT n v l (M X( n u m o p e r ) , 0 ) + 1 A F O R M OPERATION W E E numcli = numClient HR A D numccl = 1) , N nto , SYSDATE , t o M o v e , versement l i v r e t | | c p t ); INSERT INTO O P E R A T I O N VALUES ( numClient , 2, (SELECT n v l (M X( n u m o p e r ) , 0 ) + 1 A F O R M OPERATION W E E numcli = numClient HR A D numccl = 2) , N nto ,

44

SYSDATE , toMove , versement l i v r e t ); t o t a l := t o t a l t o M o v e ; c p t := c p t + 1 ; END L O O P ; C M I O MT; EXCEPTION W E OTHERS T E HN H N RAISE Y_A_UN_GRO_BLEME ; END; ELSE D B M S _ O U T P U T . P U T _ L I N E ( SQLCODE = D B M S _ O U T P U T . P U T _ L I N E ( SQLERRM = R LB C O L A K; END I F ; END; C M I O MT; n u m C l i e n t := n u m C l i e n t + 2 ; END L O O P ; EXCEPTION W E NO_DATA_FOUND T E HN H N D B M S _ O U T P U T . P U T _ L I N E ( Pas de donn es ! ) ; e W E TOO_MANY_ROWS T E HN H N D B M S _ O U T P U T . P U T _ L I N E ( Trop de donn es ! ) ; e W E Y_A_UN_GRO_BLEME T E HN H N D B M S _ O U T P U T . P U T _ L I N E ( I l y a un g r o s probl` me ! ) ; e D B M S _ O U T P U T . P U T _ L I N E ( SQLCODE = | | S Q L C O D E ) ; D B M S _ O U T P U T . P U T _ L I N E ( SQLERRM = | | S Q L E R R M ) ; W E OTHERS T E HN H N D B M S _ O U T P U T . P U T _ L I N E ( SQLCODE = | | S Q L C O D E ) ; D B M S _ O U T P U T . P U T _ L I N E ( SQLERRM = | | S Q L E R R M ) ; END; / || ||

||

cpt

SQLCODE ) ; SQLERRM ) ;

45

3.4

Sous-programmes

E x e r c i c e 1 CREATE OR R E P L A C E F U N C T I O N bad_puissance ( b N M E , n N M E U BR U B R) RETURN N M E IS U BR BEGIN IF ( n = 0) T E H N RETURN 1; ELSE RETURN b bad_puissance ( b , n 1 ) ; END I F ; END; / E x e r c i c e 2 CREATE OR R E P L A C E F U N C T I O N good_puissance ( b N M E , n N M E U BR U B R) RETURN N M E IS U BR BEGIN IF ( n = 0) T E H N RETURN 1; END I F ; IF ( MOD ( n , 2) = 0 ) T E H N RETURN good_puissance ( b b , n / 2 ) ; END I F ; RETURN b good_puissance ( b , n 1 ) ; END; / E x e r c i c e 3 CREATE OR R E P L A C E F U N C T I O N d e m i F r e r e s ( A P E R S O N N E . n u m p e r s%t y p e , B P E R S O N N E . n u m p e r s%t y p e ) RETURN B O O L E A N IS r o w A P E R S O N N E%r o w t y p e ; r o w B P E R S O N N E%r o w t y p e ; BEGIN SELECT INTO r o w A F O R M PERSONNE W E E numpers = A ; HR SELECT INTO r o w B F O R M PERSONNE W E E numpers = B ; HR R E T U R N r o w A . p e r e = r o w B . p e r e OR r o w A . m e r e = r o w B . m e r e ; END; / E x e r c i c e 4 CREATE OR R E P L A C E F U N C T I O N f r e r e s ( A P E R S O N N E . n u m p e r s%t y p e , B P E R S O N N E . n u m p e r s%t y p e ) RETURN B O O L E A N IS r o w A P E R S O N N E%r o w t y p e ; r o w B P E R S O N N E%r o w t y p e ; BEGIN SELECT INTO r o w A F O R M PERSONNE W E E numpers = A ; HR SELECT INTO r o w B F O R M PERSONNE W E E numpers = B ; HR RETURN rowA . pere = rowB . pere A D rowA . mere = rowB . mere ; N END; / CREATE OR R E P L A C E F U N C T I O N c o u s i n s G e r m a i n s ( A P E R S O N N E . n u m p e r s%t y p e , B P E R S O N N E . n u m p e r s%t y p e ) RETURN B O O L E A N IS r o w A P E R S O N N E%r o w t y p e ; r o w B P E R S O N N E%r o w t y p e ; BEGIN SELECT INTO r o w A F O R M PERSONNE W E E numpers = A ; HR SELECT INTO r o w B F O R M PERSONNE W E E numpers = B ; HR RETURN freres ( rowA . pere , rowB . pere ) OR freres ( rowA . pere , rowB . mere ) OR freres ( rowA . mere , rowB . pere ) OR freres ( rowA . mere , rowB . mere ) ; END; / E x e r c i c e 5 CREATE OR R E P L A C E P R O C E D U R E a i e u l ( P P E R S O N N E . n u m p e r s%t y p e ) I S r o w P E R S O N N E%r o w t y p e ; nb N M E U B R; BEGIN SELECT count ( ) INTO N B F O R M PERSONNE

46

W E E numpers = P ; HR IF ( NB = 1) T E H N SELECT INTO r o w F O R M PERSONNE W E E numpers = P ; HR DBMS_OUTPUT . PUT_LINE ( row . nom ) ; aieul ( row . pere ) ; END I F ; END; / E x e r c i c e 6 CREATE OR R E P L A C E P R O C E D U R E m e c s ( P P E R S O N N E . n u m p e r s%t y p e ) I S r o w P E R S O N N E%r o w t y p e ; nb N M E U B R; BEGIN SELECT count ( ) INTO N B F O R M PERSONNE W E E numpers = P ; HR IF ( NB = 1) T E H N SELECT INTO r o w F O R M PERSONNE W E E numpers = P ; HR SELECT count ( ) INTO N B F O R M PERSONNE W E E pere = P ; HR IF ( NB > 0) T E H N DBMS_OUTPUT . PUT_LINE ( row . nom ) ; END I F ; mecs ( row . pere ) ; mecs ( row . mere ) ; END I F ; END; / E x e r c i c e 7 CREATE OR R E P L A C E F U N C T I O N a s c e n d a n t ( A P E R S O N N E . n u m p e r s%t y p e , B P E R S O N N E . n u m p e r s%t y p e ) RETURN B O O L E A N IS r o w P E R S O N N E%r o w t y p e ; BEGIN SELECT INTO r o w F O R M PERSONNE W E E numpers = B ; HR I F ( r o w . p e r e = A OR r o w . m e r e = A ) T E H N R E T U R N TRUE; END I F ; R E T U R N ( r o w . p e r e I S N T NULL A D a s c e n d a n t ( A , r o w . p e r e ) ) O N OR ( r o w . m e r e I S N T NULL A D a s c e n d a n t ( A , r o w . m e r e ) ) ; O N END; / BEGIN IF ( a s c e n d a n t ( 1 , 8 ) ) T E H N D B M S _ O U T P U T . P U T _ L I N E ( OK ) ; ELSE D B M S _ O U T P U T . P U T _ L I N E ( pas OK ) ; END I F ; END; / E x e r c i c e 8 CREATE OR R E P L A C E F U N C T I O N fmax ( A N M E , B N M E U BR U B R) RETURN N M E IS U BR BEGIN IF ( A > B ) T E H N RETURN A ; ELSE RETURN B ; END I F ; END; / CREATE OR R E P L A C E F U N C T I O N e c a r t A s c e n d a n t ( A P E R S O N N E . n u m p e r s%t y p e , B P E R S O N N E . n u m p e r s%t y p e ) RETURN N M E IS U BR r o w P E R S O N N E%r o w t y p e ; NB N M E U B R; BEGIN SELECT INTO r o w F O R M PERSONNE W E E numpers = B ; HR I F ( r o w . p e r e = A OR r o w . m e r e = A ) T E H N RETURN 1; END I F ; I F ( r o w . p e r e I S NULL) T E H N

47

N B := 1; ELSE N B := e c a r t A s c e n d a n t ( A , r o w . p e r e ) ; END I F ; I F ( r o w . m e r e I S NULL) T E H N N B := f m a x ( 1 , N B ) ; ELSE N B := f m a x ( e c a r t A s c e n d a n t ( A , r o w . p e r e ) , N B ) ; END I F ; I F ( N B <> 1) T E H N N B := N B + 1 ; END I F ; RETURN NB ; END; / CREATE OR R E P L A C E F U N C T I O N e c a r t ( A P E R S O N N E . n u m p e r s%t y p e , B P E R S O N N E . n u m p e r s%t y p e ) RETURN N M E IS U BR r o w P E R S O N N E%r o w t y p e ; NB N M E U B R; BEGIN RETURN fmax ( ecartAscendant ( A , B ) , ecartAscendant ( B , A ) ) ; END; / E x e r c i c e 9 SELECT A . nom , A . p r e n o m , B . nom , B . p r e n o m F O R M PERSONNE A , PERSONNE B W E E ecartAscendant ( A . numpers , B . numpers ) = HR ( SELECT M X( e c ) A F O R M ( SELECT e c a r t ( A . n u m p e r s , B . n u m p e r s ) AS e c F O R M PERSONNE A , PERSONNE B ) ); E x e r c i c e 10

48

3.5

Curseurs

CREATE OR R E P L A C E P R O C E D U R E c o p y F r o m P e r s o n n e T o C l i e n t I S CURSOR C IS SELECT F O R M PERSONNE ; R O W C%r o w t y p e ; BEGIN F O R R O W IN C L O O P INSERT INTO C L I E N T ( numcli , nomcli , prenomcli ) VALUES ( R O W . n u m p e r s , R O W . nom , R O W . p r e n o m ) ; END L O O P ; C M I O MT; EXCEPTION W E DUP_VAL_ON_INDEX T E HN H N D B M S _ O U T P U T . P U T _ L I N E ( Copy can be done o n l y once . ) ; END; / CALL copyFromPersonneToClient ( ) ; CREATE OR R E P L A C E P R O C E D U R E t a k e C l i e n t T o P e r s o n n e l I S R o w c l i e n t%r o w t y p e ; BEGIN SELECT INTO R o w F O R M CLIENT W E E numcli = HR ( SELECT M X( n u m c l i ) A F O R M CLIENT ) ; INSERT INTO P E R S O N N E L ( numpers , nompers , prenompers ) VALUES ( Row . numcli , Row . nomcli , Row . prenomcli ) ; C M I O MT; EXCEPTION W E DUP_VAL_ON_INDEX T E HN H N D B M S _ O U T P U T . P U T _ L I N E ( This row has a l r e a d y been imported . ) ; R LB C O L A K; W E NO_DATA_FOUND T E HN H N D B M S _ O U T P U T . P U T _ L I N E ( Table CLIENT i s empty . ) ; R LB C O L A K; END; / CALL takeClientToPersonnel ( ) ; CREATE OR R E P L A C E P R O C E D U R E c r e d i t A c c o u n t ( n u m c l i e n t C L I E N T . n u m c l i%t y p e , value N M E U B R) I S BEGIN I F ( value > 1 0 0 ) T E H N creditAccount ( numclient , 100); c r e d i t A c c o u n t ( n u m c l i e n t , value 1 0 0 ) ; ELSE INSERT INTO O P E R A T I O N VALUES ( numclient , 1, (SELECT n v l (M X( n u m o p e r ) , 0 ) + 1 A F O R M OPERATION W E E numcli = numclient HR A D numccl = 1 N ), (SELECT n u m t y p e o p e r F O R M TYPEOPERATION W E E n o m t y p e o p e r = virement HR ), sysdate , value , cadeau ! ); END I F ; EXCEPTION W E OTHERS T E HN H N I F ( S Q L C O D E = 22900) T E H N D B M S _ O U T P U T . P U T _ L I N E ( Too much money a t once . ) ; END I F ; END; / CREATE OR R E P L A C E P R O C E D U R E c r e a t e V i r e m e n t ( n u m c l i e n t C L I E N T . n u m c l i%t y p e , value N M E U B R) I S BEGIN INSERT INTO O P E R A T I O N VALUES ( numclient , 1, (SELECT n v l (M X( n u m o p e r ) , 0 ) + 1 A

49

F O R M OPERATION W E E numcli = numclient HR A D numccl = 1 N ), (SELECT n u m t y p e o p e r F O R M TYPEOPERATION W E E n o m t y p e o p e r = virement HR ), sysdate , value , cadeau ! ); INSERT INTO O P E R A T I O N VALUES ( numclient , 2, (SELECT n v l (M X( n u m o p e r ) , 0 ) + 1 A F O R M OPERATION W E E numcli = numclient HR A D numccl = 1 N ), (SELECT n u m t y p e o p e r F O R M TYPEOPERATION W E E n o m t y p e o p e r = virement HR ), sysdate , value , cadeau ! ); EXCEPTION W E OTHERS T E HN H N I F ( S Q L C O D E = 22900) T E H N D B M S _ O U T P U T . P U T _ L I N E ( Too much money a t once . ) ; END I F ; END; / CREATE OR R E P L A C E P R O C E D U R E m o v e T o L i v r e t ( n u m c l i e n t C L I E N T . n u m c l i%t y p e , value N M E U B R) I S BEGIN I F ( value >= 0 ) T E H N I F ( value > 1 0 0 ) T E H N moveToLivret ( numclient , 100); m o v e T o L i v r e t ( n u m c l i e n t , value 1 0 0 ) ; ELSE c r e a t e V i r e m e n t ( n u m c l i e n t , value ) ; END I F ; END I F ; EXCEPTION W E OTHERS T E HN H N I F ( S Q L C O D E = 22900) T E H N D B M S _ O U T P U T . P U T _ L I N E ( Too much money a t once . ) ; END I F ; END; / CREATE OR R E P L A C E P R O C E D U R E o p e n A c c o u n t ( n u m c l i e n t C L I E N T . n u m c l i%t y p e ) I S BEGIN INSERT INTO C O M P T E C L I E N T VALUES ( numclient , 1, (SELECT n u m t y p e c c l F O R M TYPECCL W E E n o m t y p e c c l = Compte c o u r a n t HR ), sysdate , (SELECT n u m p e r s F O R M PERSONNEL W E E numpers = HR ( SELECT M X( n u m c l i ) A F O R M CLIENT ) ) ); INSERT INTO C O M P T E C L I E N T VALUES ( numclient , 2, (SELECT n u m t y p e c c l F O R M TYPECCL W E E n o m t y p e c c l = virement HR ), sysdate , (SELECT n u m p e r s F O R M PERSONNEL W E E numpers = HR ( SELECT M X( n u m c l i ) A F O R M CLIENT

50

) ) ); creditAccount ( numclient , numclient 100); m o v e T o L i v r e t ( n u m c l i e n t , n u m c l i e n t 100 5 0 0 ) ; EXCEPTION W E DUP_VAL_ON_INDEX T E HN H N D B M S _ O U T P U T . P U T _ L I N E ( This ac co un t has a l r e a d y been opened . ) ; END; / CREATE OR R E P L A C E P R O C E D U R E o p e n A c c o u n t s I S CURSOR C IS SELECT n u m c l i F O R M CLIENT ; R O W C%r o w t y p e ; BEGIN F O R R O W IN C L O O P openAccount ( ROW . numcli ) ; END L O O P ; C M I O MT; EXCEPTION W E OTHERS T E HN H N D B M S _ O U T P U T . P U T _ L I N E ( An e r r o r has o c c u r r e d . ) ; R LB C O L A K; END; / CALL openAccounts ( ) ; CREATE OR R E P L A C E P R O C E D U R E afficheDescendance ( numpersonne N M E U B R) I S CURSOR C IS SELECT F O R M PERSONNE W E E pere = numpersonne HR OR m e r e = n u m p e r s o n n e ; R O W C%r o w T y p e ; BEGIN F O R R O W IN C L O O P DBMS_OUTPUT . PUT_LINE ( row . nom | | | | row . prenom ) ; afficheDescendance ( ROW . numpers ) ; END L O O P ; END; /

51

3.6

Curseurs paramtrs e e

E x e r c i c e 1 CREATE OR R E P L A C E P R O C E D U R E a f f i c h e C l i e n t ( u n C l i e n t C L I E N T%r o w t y p e ) I S BEGIN DBMS_OUTPUT . PUT_LINE ( Client | | unClient . prenomcli | | | | END; /

unClient . nomCli ) ;

CREATE OR R E P L A C E P R O C E D U R E a f f i c h e C o m p t e ( u n C o m p t e C O M P T E C L I E N T%r o w t y p e ) I S BEGIN D B M S _ O U T P U T . P U T _ L I N E ( Compte | | u n C o m p t e . n u m C l i | | | | u n C o m p t e . n u m c c l ) ; END; / CREATE OR R E P L A C E P R O C E D U R E a f f i c h e C o m p t e s C l i e n t s I S CURSOR c l i e n t s IS SELECT F O R M CLIENT ; u n C l i e n t c l i e n t s%r o w t y p e ; C U R S O R c o m p t e s ( n u m c l i e n t C L I E N T . n u m c l i%t y p e ) I S SELECT F O R M COMPTECLIENT W E E numcli = numclient ; HR u n C o m p t e c l i e n t s%r o w t y p e ; BEGIN F O R u n C l i e n t IN c l i e n t s L O O P afficheClient ( unClient ) ; F O R u n C o m p t e IN c o m p t e s ( u n C l i e n t . n u m c l i ) L O O P afficheCompte ( unCompte ) ; END L O O P ; END L O O P ; END; / SET S E R V E R O U T P U T O SIZE 1 00 00 0 0 N call afficheComptesClients ( ) ; E x e r c i c e 2 CREATE OR R E P L A C E P R O C E D U R E a f f i c h e O p e r a t i o n ( u n e O p e r a t i o n O P E R A T I O N%r o w t y p e ) I S BEGIN D B M S _ O U T P U T . P U T _ L I N E ( Operation | | u n e O p e r a t i o n . n u m O p e r | | , montant : END; /

||

uneOperation . montantOper )

CREATE OR R E P L A C E P R O C E D U R E a f f i c h e O p e r C o m p t e s C l i e n t s I S CURSOR c l i e n t s IS SELECT F O R M CLIENT ; u n C l i e n t c l i e n t s%r o w t y p e ; C U R S O R c o m p t e s ( n u m c l i e n t C L I E N T . n u m c l i%t y p e ) I S SELECT F O R M COMPTECLIENT W E E numcli = numclient ; HR u n C o m p t e c l i e n t s%r o w t y p e ; CURSOR operations ( n u m c l i e n t C L I E N T . n u m c l i%t y p e , n u m c o m p t e C O M P T E C L I E N T . n u m c c l%t y p e ) I S SELECT F O R M OPERATION W E E numcli = numclient HR A D numccl = numcompte ; N u n e O p e r a t i o n o p e r a t i o n s%r o w t y p e ; BEGIN F O R u n C l i e n t IN c l i e n t s L O O P afficheClient ( unClient ) ; F O R u n C o m p t e IN c o m p t e s ( u n C l i e n t . n u m c l i ) L O O P afficheCompte ( unCompte ) ; F O R u n e O p e r a t i o n IN o p e r a t i o n s ( u n C l i e n t . n u m c l i , u n C o m p t e . n u m c c l ) L O O P afficheOperation ( uneOperation ) ; END L O O P ; END L O O P ; END L O O P ; END; / call afficheOperComptesClients ( ) ;

52

3.7

Triggers

I l c o n v i e n t d abord de m o d i f i e r q u e l q u e peu l o r g a n i s a t i o n d e s donnees , on a j o u t e par exemple dans l a t a b l e MODULE l e nombre d e t u d i a n t s i n s c r i t s D O R P D O R P D O R P D O R P D O R P D O R P TABLE TABLE TABLE TABLE TABLE TABLE RESULTAT ; EXAMEN ; PREREQUIS ; INSCRIPTION ; MODULE ; ETUDIANT ;

CREATE TABLE E T U D I A N T ( n u m E t u d number , n o m varchar2 ( 4 0 ) , p r e n o m varchar2 ( 4 0 ) , d a t e n a i s s date , c i v i l i t e varchar2 ( 4 ) , p a t r o n y m e varchar2 ( 4 0 ) , n u m s e c u varchar2 ( 1 5 ) N T NULL, O m o y e n n e N M E DEFAULT NULL) ; U BR CREATE TABLE M O D U L E ( c o d M o d number , n o m M o d varchar2 ( 1 5 ) , e f f e c M a x number DEFAULT 3 0 , e f f e c number d e f a u l t 0 ) ; CREATE TABLE E X A M E N ( c o d M o d number , c o d E x a m number , dateExam date ) ; CREATE TABLE I N S C R I P T I O N ( n u m E t u d number , c o d M o d number , dateInsc date default sysdate ) ; CREATE TABLE P R E R E Q U I S ( c o d M o d number , c o d M o d P r e r e q number , n o t e M i n number ( 4 , 2 ) N T NULL) ; O CREATE TABLE R E S U L T A T ( c o d M o d number , c o d E x a m number , n u m E t u d number , n o t e number ( 4 , 2 ) ) ; ALTER TABLE E T U D I A N T A D D CONSTRAINT p k _ e t u d i a n t P I A Y KEY ( n u m E t u d ) ; RM R ALTER TABLE M O D U L E A D D CONSTRAINT p k _ m o d u l e P I A Y KEY ( c o d M o d ) ; RM R ALTER TABLE E X A M E N A D D CONSTRAINT p k _ e x a m e n P I A Y KEY ( c o d M o d , c o d E x a m ) ; RM R ALTER TABLE P R E R E Q U I S A D D CONSTRAINT p k _ p r e r e q u i s P I A Y KEY ( c o d M o d , c o d M o d P r e r e q ) ; RM R ALTER TABLE I N S C R I P T I O N A D D CONSTRAINT p k _ i n s c r i p t i o n P I A Y KEY ( c o d M o d , n u m E t u d ) ; RM R ALTER TABLE R E S U L T A T A D D CONSTRAINT p k _ r e s u l t a t P I A Y KEY ( c o d M o d , n u m E t u d , c o d E x a m ) ; RM R ALTER TABLE I N S C R I P T I O N A D D (CONSTRAINT f k _ i n s c r i p t i o n _ e t u d i a n t FOREIGN KEY ( n u m E t u d ) REFERENCES ETUDIANT ( numEtud ) , CONSTRAINT f k _ i n s c r i p t i o n _ m o d u l e FOREIGN KEY ( c o d M o d ) REFERENCES MODULE ( codMod ) ) ; ALTER TABLE P R E R E Q U I S A D D (CONSTRAINT f k _ p r e r e q u i s _ c o d m o d FOREIGN KEY ( c o d M o d ) REFERENCES MODULE ( codMod ) , CONSTRAINT f k _ p r e r e q u i s _ c o d m o d p r e r e q FOREIGN KEY ( c o d M o d P r e r e q ) REFERENCES MODULE ( codMod ) ) ; ALTER TABLE E X A M E N A D D CONSTRAINT f k _ e x a m e n FOREIGN KEY ( c o d M o d ) REFERENCES MODULE ( codMod ) ; ALTER TABLE R E S U L T A T A D D

53

(CONSTRAINT f k _ r e s u l t a t _ e x a m e n FOREIGN KEY ( c o d M o d , c o d E x a m ) 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 n FOREIGN KEY ( c o d M o d , n u m E t u d ) REFERENCES INSCRIPTION ( codMod , numEtud ) ) ; ALTER TABLE E T U D I A N T A D D (CONSTRAINT c k _ c i v i l i t e C E K H C ( c i v i l i t e IN ( Mr , Mme , M l l e ) ), CONSTRAINT c k _ c i v i l i t e _ n u m s e c u C E K H C ( SUBSTR( n u m s e c u , 1 , 1 ) = 2 OR p a t r o n y m e I S NULL ), CONSTRAINT c k _ l e n g t h _ n u m s e c u C E K H C ( l e n g t h ( n u m s e c u ) = 15 ), CONSTRAINT c k _ a n n e e _ n u m s e c u C E K H C ( t o _ c h a r ( d a t e n a i s s , yy ) = substr ( n u m s e c u , 2 , 2 ) ) ); C o n t r a i n t e 1 CREATE OR R E P L A C E TRIGGER b e f o r e U p d a t e F E R P r e r e q u i s BEFORE U D T O PREREQUIS P A E N FOR EACH ROW BEGIN IF ( : new . n o t e M i n < : old . n o t e M i n ) T E H N : n e w . n o t e M i n := : o l d . n o t e M i n ; END I F ; END; / C o n t r a i n t e 2 CREATE OR R E P L A C E P R O C E D U R E i n c r E f f e c ( m o d u l e N M E U B R) I S BEGIN U D T M O D U L E SET e f f e c = e f f e c + 1 W E E c o d m o d = m o d u l e ; P A E HR END; / CREATE OR R E P L A C E P R O C E D U R E d e c r E f f e c ( m o d u l e N M E U B R) I S BEGIN U D T M O D U L E SET e f f e c = e f f e c 1 W E E c o d m o d = m o d u l e ; P A E HR END; / CREATE OR R E P L A C E TRIGGER B e f o r e I n s e r t F E R M o d u l e B E F O R E INSERT O M O D U L E N FOR EACH ROW BEGIN : n e w . e f f e c := 0 ; END; / CREATE OR R E P L A C E TRIGGER a f t e r I n s e r t F E R I n s c A F T E R INSERT O I N S C R I P T I O N N FOR EACH ROW BEGIN incrEffec ( : new . codmod ) ; END; /

54

 CREATE OR R E P L A C E TRIGGER a f t e r D e l e t e F E R I n s c A F T E R DELETE O I N S C R I P T I O N N FOR EACH ROW BEGIN decrEffec ( : old . codmod ) ; END; / CREATE OR R E P L A C E TRIGGER a f t e r U p d a t e F E R I n s c AFTER U D T O INSCRIPTION P A E N FOR EACH ROW BEGIN decrEffec ( : old . codmod ) ; incrEffec ( : new . codmod ) ; END; / D O VIEW m o d u l e s D i s p o n i b l e s ; R P CREATE VIEW m o d u l e s D i s p o n i b l e s AS SELECT c o d m o d F O R M MODULE W E E effec < effecMax ; HR CREATE OR R E P L A C E TRIGGER b e f o r e I n s e r t U p d a t e F E R I n s c B E F O R E INSERT OR U D T O I N S C R I P T I O N P A E N FOR EACH ROW DECLARE nbLignes N M E U B R; BEGIN SELECT count ( ) INTO n b L i g n e s F O R M modulesDisponibles W E E codmod = : new . codmod ; HR IF ( n b L i g n e s = 0) T E H N R A I S E _ A P P L I C A T I O N _ E R R O R ( 20001 , P l u s de p l a c e s d i p o n i b l e s . ) ; END I F ; END; / C o n t r a i n t e 3 D O VIEW e x a m e n s P o s s i b l e s ; R P CREATE VIEW e x a m e n s P o s s i b l e s AS SELECT c o d M o d F O R M MODULE M W E E HR ( SELECT C U T( ) O N F O R M INSCRIPTION I W E E I . codmod = M . codmod HR ) > 0 ; CREATE OR R E P L A C E TRIGGER b e f o r e I n s e r t U p d a t e F E R E x a m B E F O R E INSERT OR U D T O E X A M E N P A E N FOR EACH ROW DECLARE nbLignes N M E U B R; BEGIN SELECT count ( ) INTO n b L i g n e s F O R M examensPossibles W E E codMod = : new . codmod ; HR IF ( n b L i g n e s = 0) T E H N R A I S E _ A P P L I C A T I O N _ E R R O R ( 20002 , Pas d l ` v e dans ce module . ) ; e e END I F ; END; /

55

 C o n t r a i n t e 4 D O VIEW e t u d i a n t s E x a m e n s ; R P CREATE VIEW e t u d i a n t s E x a m e n s AS SELECT I . n u m e t u d , E . c o d m o d , E . c o d e x a m F O R M INSCRIPTION I , EXAMEN E W E E I . codmod = E . codmod HR A D I . dateInsc < E . dateExam ; N CREATE OR R E P L A C E TRIGGER b e f o r e I n s e r t U p d a t e F E R R e s u l t B E F O R E INSERT OR U D T O R E S U L T A T P A E N FOR EACH ROW DECLARE nbLignes N M E U B R; BEGIN SELECT count ( ) INTO n b L i g n e s F O R M etudiantsExamens W E E numetud = : new . numetud HR A D codmod = : new . codmod N A D codexam = : new . codexam ; N IF ( n b L i g n e s = 0) T E H N R A I S E _ A P P L I C A T I O N _ E R R O R ( 20002 , Examen a n t r i e u r ` l i n s c r i p t i o n dans l e module . ) ; e a END I F ; END; / C o n t r a i n t e 5 On c r e une t a b l e t e m p o r a i r e c o n t e n a n t l e s mmes v a l e u r s que p r e r e q u i s , e e On l a met ` j o u r AVANT l a t a b l e p r e r e q u i s pour v r i f i e r que l i n s e r t i o n a e ne c o n s t r u i t pas de c i r c u i t . D O TABLE M I R R O R P R E R E Q ; R P CREATE TABLE M I R R O R P R E R E Q ( codmod N M E , U BR codmodprereq N M E , U BR noteMin N M E ; U B R)

CREATE OR R E P L A C E F U N C T I O N f i n d M o d u l e ( r o o t number , m o d u l e T o F i n d number) RETURN BOOLEAN IS CURSOR C IS SELECT c o d m o d F O R M MIRRORPREREQ W E E codmodprereq = root ; HR S O N C%r o w t y p e ; BEGIN F O R S O N IN C L O O P IF ( s o n . c o d m o d = m o d u l e T o F i n d OR findModule ( son . codmod , moduleToFind ) ) T E H N R E T U R N TRUE; END I F ; END L O O P ; R E T U R N FALSE; END; / CREATE OR R E P L A C E P R O C E D U R E insertMirrorPrereq ( codmodValue N M E , codmodprereqValue N M E , note N M E U BR U BR U B R) I S BEGIN INSERT INTO M I R R O R P R E R E Q ( codmod , codmodprereq , noteMin ) VALUES ( codmodValue , codmodprereqValue , note ) ; END; /

56

 CREATE OR R E P L A C E P R O C E D U R E deleteMirrorPrereq ( codmodValue N M E , codmodprereqValue N M E U BR U B R) I S BEGIN DELETE F O R M MIRRORPREREQ W E E codmod = codmodValue HR A D codmodprereq = codmodprereqValue ; N END; / CREATE OR R E P L A C E P R O C E D U R E updateMirrorPrereq ( codmodValue N M E , U BR codmodNewValue N M E , U BR codmodprereqValue N M E , U BR codmodprereqNewValue N M E , U BR newNote N M E U B R) I S BEGIN U D T M I R R O R P R E R E Q SET P A E codmod = codmodNewValue , codmodprereq = codmodprereqNewValue , noteMin = newNote W E E codmod = codmodValue HR A D codmodprereq = codmodprereqValue ; N END; / CREATE OR R E P L A C E TRIGGER a f t e r D e l e t e F E R P r e r e q A F T E R DELETE O P R E R E Q U I S N FOR EACH ROW BEGIN deleteMirrorPrereq ( : old . codmod , : old . codmodprereq ) ; END; / CREATE OR R E P L A C E TRIGGER b e f o r e I n s e r t U p d a t e F E R P r e r e q B E F O R E INSERT OR U D T O P R E R E Q U I S P A E N FOR EACH ROW BEGIN IF I N S E R T I N G T E H N insertMirrorPrereq ( : new . codmod , : new . codmodprereq , : new . noteMin ) ; END I F ; IF U P D A T I N G T E H N updateMirrorPrereq ( : old . codmod , : new . codmod , : old . codmodprereq , : new . codmodprereq , : new . noteMin ) ; END I F ; IF ( f i n d M o d u l e ( : new . codmod , : new . codmod ) ) T E H N IF I N S E R T I N G T E H N deleteMirrorPrereq ( : new . codmod , : new . codmodprereq ) ; END I F ; IF U P D A T I N G T E H N updateMirrorPrereq ( : new . codmod , : old . codmod , : new . codmodprereq , : old . codmodprereq , : old . noteMin ) ; END I F ; R A I S E _ A P P L I C A T I O N _ E R R O R ( 20003 , C i r c u i t dans p r e r e q u i s . ) ; END I F ; END; / C o n t r a i n t e 6 CREATE OR R E P L A C E F U N C T I O N checkInscription ( etud N M E , mod N M E U BR U B R) RETURN BOOLEAN IS CURSOR prereq IS SELECT n o t e M i n , c o d m o d p r e r e q F O R M MIRRORPREREQ W E E codmod = mod ; HR p p r e r e q%r o w t y p e ; nbLignes N M E U B R; BEGIN

57

F O R p IN p r e r e q L O O P SELECT count ( ) INTO n b L i g n e s F O R M RESULTAT W E E codmod = p . codmodprereq HR A D numetud = etud N A D note < p . noteMin ; N IF ( n b L i g n e s = 0) T E H N R E T U R N FALSE; END I F ; END L O O P ; R E T U R N TRUE; END; / CREATE OR R E P L A C E TRIGGER b e f o r e I n s e r t U p d a t e F E R I n s c B E F O R E INSERT OR U D T O I N S C R I P T I O N P A E N FOR EACH ROW DECLARE nbLignes N M E U B R; BEGIN SELECT count ( ) INTO n b L i g n e s F O R M modulesDisponibles W E E codmod = : new . codmod ; HR IF ( n b L i g n e s = 0) T E H N R A I S E _ A P P L I C A T I O N _ E R R O R ( 20001 , P l u s de p l a c e s d i p o n i b l e s . ) ; END I F ; I F (N T( c h e c k I n s c r i p t i o n ( : n e w . n u m e t u d , : n e w . c o d m o d ) ) ) T E O H N R A I S E _ A P P L I C A T I O N _ E R R O R ( 20004 , P r r e q u i s non s a t i s f a i t . ) ; e END I F ; END; / C o n t r a i n t e 7 La la le les a u s s i un probleme s e pose , on ne p e u t pas f a i r e de r e q u e t e s u r t a b l e r e s u l t a t , comme de p l u s , on t i e n t ` p r e n d r e pour chaque e t u d i a n t a m e i l l e u r e n o t e dans chaque module , on c r e e une t a b l e t e m p o r a i r e c o n t e n a n t n o t e s o b e t n u e s par l e s e l e v e s .

D O TABLE M I R R O R R E S U L T ; R P CREATE TABLE M I R R O R R E S U L T ( numetud N M E , U BR codmod N M E , U BR codexam N M E , U BR note N M E , U BR P I A Y KEY( n u m e t u d , c o d m o d , c o d e x a m ) RM R ); D O VIEW M E I L L E U R E N O T E ; R P CREATE VIEW M E I L L E U R E N O T E AS SELECT n u m e t u d , c o d m o d , M X( n o t e ) AS n o t e M a x A F O R M MIRRORRESULT G O P BY n u m e t u d , c o d m o d ; R U D O VIEW N O M B R E I N S C R I P T I O N s ; R P CREATE VIEW N O M B R E I N S C R I P T I O N S AS SELECT n u m e t u d , ( SELECT C U T( ) O N F O R M INSCRIPTION I W E E I . numetud = E . numetud HR ) AS n b I n s c r i p t i o n s F O R M ETUDIANT E ; D O VIEW N O M B R E N O T E S ; R P CREATE VIEW N O M B R E N O T E S AS SELECT n u m e t u d ,

58

(SELECT C U T( ) AS n b N o t e s O N F O R M MEILLEURENOTE M W E E M . numetud = E . numetud HR ) AS n b N o t e s F O R M ETUDIANT E ; CREATE OR R E P L A C E P R O C E D U R E updateMoyenne ( etud N M E U B R) IS nbNotes N M E U B R; nbInscriptions N M E U B R; BEGIN SELECT n b N o t e s INTO n b N o t e s F O R M NOMBRENOTES W E E numetud = etud ; HR SELECT n b I n s c r i p t i o n s INTO n b I N s c r i p t i o n s F O R M NOMBREINSCRIPTIONS W E E numetud = etud ; HR IF ( n b N o t e s = n b I n s c r i p t i o n s ) T E H N U D T E T U D I A N T SET m o y e n n e = P A E (SELECT A G( n o t e M a x ) V F O R M MEILLEURENOTE W E E numetud = etud HR ) W E E numetud = etud ; HR ELSE U D T E T U D I A N T SET P A E m o y e n n e = NULL W E E numetud = etud ; HR END I F ; END; / CREATE OR R E P L A C E TRIGGER a f t e r I n s e r t F E R R e s u l t A F T E R INSERT O R E S U L T A T N FOR EACH ROW BEGIN INSERT INTO M I R R O R R E S U L T VALUES ( : new . numetud , : new . codmod , : new . codexam , updateMoyenne ( : new . numetud ) ; END; /

: new . note ) ;

CREATE OR R E P L A C E TRIGGER a f t e r U p d a t e F E R R e s u l t AFTER U D T O RESULTAT P A E N FOR EACH ROW BEGIN U D T M I R R O R R E S U L T SET P A E numetud = : new . numetud , codmod = : new . codmod , codexam = : new . codexam , note = : new . note W E E numetud = : old . numetud HR A D codmod = : old . codmod N A D codexam = : old . codexam ; N updateMoyenne ( : new . numetud ) ; END; / CREATE OR R E P L A C E TRIGGER a f t e r D e l e t e F E R R e s u l t A F T E R DELETE O R E S U L T A T N FOR EACH ROW BEGIN DELETE F O R M MIRRORRESULT W E E numetud = : new . numetud HR A D codmod = : new . codmod N A D codexam = : new . codexam ; N updateMoyenne ( : new . numetud ) ; END; / C o n t r a i n t e 9

59

 CREATE OR R E P L A C E F U N C T I O N checkAllStudents RETURN BOOLEAN IS CURSOR C IS SELECT n u m e t u d , c o d m o d F O R M INSCRIPTION ; e C%r o w t y p e ; BEGIN F O R e IN C L O O P I F (N T( c h e c k I n s c r i p t i o n ( e . n u m e t u d , e . c o d m o d ) ) ) T E O H N R E T U R N FALSE; END I F ; END L O O P ; R E T U R N TRUE; END; / CREATE OR R E P L A C E TRIGGER B e f o r e U p d a t e F E R M o d u l e BEFORE U D T O MODULE P A E N FOR EACH ROW BEGIN IF ( : new . e f f e c m a x < : new . effec ) T E H N R A I S E _ A P P L I C A T I O N _ E R R O R ( 20005 , L e f f e c t i f ne p e u t t r e en d e s s o u s de e END I F ; END; / C o n t r a i n t e 8

||

: new . effec ) ;

CREATE OR R E P L A C E TRIGGER b e f o r e I n s e r t U p d a t e F E R P r e r e q B E F O R E INSERT OR U D T O P R E R E Q U I S P A E N FOR EACH ROW BEGIN IF I N S E R T I N G T E H N insertMirrorPrereq ( : new . codmod , : new . codmodprereq , : new . noteMin ) ; END I F ; IF U P D A T I N G T E H N updateMirrorPrereq ( : old . codmod , : new . codmod , : old . codmodprereq , : new . codmodprereq , : new . noteMin ) ; END I F ; IF ( f i n d M o d u l e ( : new . codmod , : new . codmod ) ) T E H N IF I N S E R T I N G T E H N deleteMirrorPrereq ( : new . codmod , : new . codmodprereq ) ; END I F ; IF U P D A T I N G T E H N updateMirrorPrereq ( : new . codmod , : old . codmod , : new . codmodprereq , : old . codmodprereq , : old . noteMin ) ; END I F ; R A I S E _ A P P L I C A T I O N _ E R R O R ( 20003 , C i r c u i t dans p r e r e q u i s . ) ; END I F ; I F (N T( c h e c k A l l S t u d e n t s ( ) ) ) T E O H N IF I N S E R T I N G T E H N deleteMirrorPrereq ( : new . codmod , : new . codmodprereq ) ; END I F ; IF U P D A T I N G T E H N updateMirrorPrereq ( : new . codmod , : old . codmod , : new . codmodprereq , : old . codmodprereq , : old . noteMin ) ; END I F ; R A I S E _ A P P L I C A T I O N _ E R R O R ( 20006 , I m p o s s i b l e de diminuer c e t t e n o t e . ) ; END I F ; END; / Quelques i n s e r t i o n s pour t e s t e r INSERT INTO E T U D I A N T VALUES ( (SELECT n v l (M X( n u m E t u d ) , 0 ) + 1 F O A R M ETUDIANT ) , Fourier , Joseph , t o _ d a t e ( 21031768 , ddmmyyyy ) , Mr , NULL, 168031234567890 , NULL );

60

INSERT INTO M O D U L E ( codMod , nomMod ) VALUES ( (SELECT n v l (M X( c o d M o d ) , 0 ) + 1 F O A R M MODULE ) , Maths ); INSERT INTO I N S C R I P T I O N ( codMod , numEtud ) VALUES ( (SELECT n u m E t u d F O R M ETUDIANT W E E nom = Fourier ) , HR (SELECT c o d M o d F O R M M O D U L E W E E n o m M o d = Maths ) HR ); INSERT INTO E X A M E N VALUES ( (SELECT c o d M o d F O R M M O D U L E W E E n o m M o d = Maths ) , HR 1, t o _ d a t e ( 02012008 , ddmmyyyy ) ); INSERT INTO R E S U L T A T VALUES ( (SELECT c o d M o d F O R M M O D U L E W E E n o m M o d = Maths ) , HR 1, (SELECT n u m E t u d F O R M ETUDIANT W E E nom = Fourier ) , HR 19 ); U D T R E S U L T A T SET P A E n o t e = 20 wHERE n u m E t u d = (SELECT n u m E t u d F O R M ETUDIANT W E E nom = Fourier ) HR A D c o d M o d = (SELECT c o d M o d F O N R M M O D U L E W E E n o m M o d = Maths ) HR A D codExam = 1; N INSERT INTO M O D U L E VALUES ( 2 , Algo , 3 0 , 2 2 ) ; INSERT INTO P R E R E Q U I S VALUES (1 , 2 , 10); INSERT INTO P R E R E Q U I S VALUES (2 , 1 , 10); U D T P R E R E Q U I S SET n o t e M i n = 2 ; P A E INSERT INTO E X A M E N VALUES ( 2 , 1 , s y s d a t e ) ;

61

3.8

Packages
c u r s o r f e u i l l e s r e t u r n p e r s o n n e%r o w t y p e I S SELECT F O R M PERSONNE ; P R O C E D U R E a j o u t e P e r s o n n e ( n o m p e r s o n n e . n o m%t y p e , p r e n o m p e r s o n n e . p r e n o m%t y p e , p e r e p e r s o n n e . p e r e%t y p e , m e r e p e r s o n n e . m e r e%t y p e ) I S BEGIN INSERT INTO P E R S O N N E VALUES ( (SELECT n v l (M X( n u m p e r s ) , 0 ) + 1 F O A R M PERSONNE ) , nom , p r e n o m , p e r e , m e r e ) ; END; F U N C T I O N d e s c e n d D e ( n u m p e r s p e r s o n n e . n u m p e r s%t y p e , d e s c e n d a n t p e r s o n n e . n u m p e r s%t y p e ) R E T U R N B O O L E A N I S BEGIN I F ( d e s c e n d a n t I S NULL) T E H N R E T U R N FALSE; ELSIF ( descendant = numpers ) T E H N R E T U R N TRUE; ELSE DECLARE p e r s P E R S O N N E%r o w t y p e ; BEGIN SELECT INTO p e r s F O R M PERSONNE W E E numpers = descendant ; HR RETURN descendDe ( numpers , pers . pere ) OR d e s c e n d D e ( n u m p e r s , p e r s . m e r e ) ; END; END I F ; END; p r o c e d u r e m o d i f i e P a r e n t s ( p e r s p e r s o n n e . n u m p e r s%t y p e , n u m P e r e p e r s o n n e . p e r e%t y p e , n u m M e r e p e r s o n n e . m e r e%t y p e ) I S BEGIN I F ( d e s c e n d D e ( p e r s , n u m P e r e ) OR d e s c e n d D e ( p e r s , n u m M e r e ) ) T E H N RAISE CIRCUIT ; END I F ; U D T P E R S O N N E SET p e r e = n u m P e r e , m e r e = n u m M e r e P A E W E E numPers = pers ; HR END;

CREATE OR R E P L A C E P A C K A G E B O D Y g e s t i o n _ a r b r e I S

END; / CALL gestion_Arbre . modifieParents (20 , 14 , 1 5 ) ;

62

3.9

Rvisions e

P r e p a r a t i f s . . . D O TABLE M I R R O R P E R S O N N E ; R P CREATE TABLE M I R R O R P E R S O N N E ( n u m p e r s N M E P I A Y KEY, U B R RM R pere N M E , U BR mere N M E U BR ); CREATE OR R E P L A C E TRIGGER m i s e A J o u r M i r r o r P e r s o n n e B E F O R E U D T OR INSERT OR DELETE O P E R S O N N E P A E N FOR EACH ROW BEGIN I F D E L E T I N G OR U P D A T I N G T E H N DELETE F O R M MIRRORPERSONNE W E E numpers = : old . numpers ; HR END I F ; I F I N S E R T I N G OR U P D A T I N G T E H N INSERT INTO M I R R O R P E R S O N N E VALUES ( : new . numpers , : new . pere , : new . mere ) ; END I F ; END; / D O TABLE M I R R O R M A R I A G E ; R P CREATE TABLE M I R R O R M A R I A G E ( nummari N M E , U BR numfemme N M E , U BR d a t e m a r i a g e DATE, d a t e d i v o r c e DATE ); CREATE OR R E P L A C E TRIGGER m i s e A J o u r M i r r o r M a r i a g e B E F O R E U D T OR INSERT OR DELETE O M A R I A G E P A E N FOR EACH ROW BEGIN I F D E L E T I N G OR U P D A T I N G T E H N DELETE F O R M MIRRORMARIAGE W E E nummari = : old . nummari HR A D numfemme = : old . numfemme N A D datemariage = : old . datemariage ; N END I F ; I F I N S E R T I N G OR U P D A T I N G T E H N INSERT INTO M I R R O R M A R I A G E VALUES ( : new . nummari , : new . numfemme , : new . datemariage , END I F ; END; / C o n t r a i n t e 1 ALTER TABLE P E R S O N N E A D CONSTRAINT c k _ p a r e n t s _ d i f f e r e n t s C E K( p e r e <> m e r e ) ; D H C C o n t r a i n t e 2 CREATE OR R E P L A C E P A C K A G E c o n t r a i n t e C i r c u i t I S C I R C U I T EXCEPTION; P R O C E D U R E v e r i f i e C i r c u i t ( p e r s p e r s o n n e . n u m p e r s%t y p e ) ; F U N C T I O N d e s c e n d D e ( n u m p e r s p e r s o n n e . n u m p e r s%t y p e , d e s c e n d a n t p e r s o n n e . n u m p e r s%t y p e ) R E T U R N B O O L E A N ; END; / CREATE OR R E P L A C E TRIGGER v e r i f i e C o n t r a i n t e C i r c u i t A F T E R U D T OR INSERT O P E R S O N N E P A E N FOR EACH ROW BEGIN contrainteCircuit . verifieCircuit ( : new . numPers ) ; END; / CREATE OR R E P L A C E P A C K A G E B O D Y c o n t r a i n t e C i r c u i t I S F U N C T I O N d e s c e n d D e ( n u m p e r s p e r s o n n e . n u m p e r s%t y p e , d e s c e n d a n t p e r s o n n e . n u m p e r s%t y p e ) R E T U R N B O O L E A N I S BEGIN I F ( d e s c e n d a n t I S NULL) T E H N R E T U R N FALSE; ELSIF ( descendant = numpers ) T E H N R E T U R N TRUE; ELSE DECLARE

: new . datedivorce ) ;

63

p e r s M I R R O R P E R S O N N E%r o w t y p e ; BEGIN SELECT INTO p e r s F O R M MIRRORPERSONNE W E E numpers = descendant ; HR RETURN descendDe ( numpers , pers . pere ) OR d e s c e n d D e ( n u m p e r s , p e r s . m e r e ) ; END; END I F ; END; P R O C E D U R E v e r i f i e C i r c u i t ( p e r s p e r s o n n e . n u m p e r s%t y p e ) I S l i g n e m i r r o r p e r s o n n e%r o w t y p e ; BEGIN SELECT INTO L I G N E F O R M mirrorpersonne W E E numpers = pers ; HR I F ( d e s c e n d D e ( p e r s , l i g n e . p e r e ) OR d e s c e n d D e ( p e r s , l i g n e . m e r e ) ) T E H N RAISE CIRCUIT ; END I F ; END; END; / C o n t r a i n t e 3 ALTER TABLE M A R I A G E A D CONSTRAINT c k _ d a t e s _ m a r i a g e C E K( d a t e D i v o r c e I S NULL OR d a t e M a r i a g e <= D H C C o n t r a i n t e 4 CREATE OR R E P L A C E P A C K A G E c o n t r a i n t e s M a r i a g e s I S m a r i a g e s S u p e r p o s e s EXCEPTION; P R O C E D U R E v e r i f i e M a r i a g e s S u p e r p o s e s ( n o u v e a u M a r i a g e m a r i a g e%r o w t y p e ) ; END c o n t r a i n t e s M a r i a g e s ; / CREATE OR R E P L A C E TRIGGER v e r i f i e C o n t r a i n t e s M a r i a g e s B E F O R E U D T OR INSERT O M A R I A G E P A E N FOR EACH ROW DECLARE n o u v e a u M a r i a g e M A R I A G E%r o w t y p e ; BEGIN n o u v e a u M a r i a g e . n u m M a r i := : n e w . n u m M a r i ; n o u v e a u M a r i a g e . n u m F e m m e := : n e w . n u m F e m m e ; n o u v e a u M a r i a g e . d a t e M a r i a g e := : n e w . d a t e M a r i a g e ; n o u v e a u M a r i a g e . d a t e D i v o r c e := : n e w . d a t e D i v o r c e ; contraintesMariages . verifieMariagesSuperposes ( nouveauMariage ) ; END; / CREATE OR R E P L A C E P A C K A G E B O D Y c o n t r a i n t e s M a r i a g e s I S F U N C T I O N s e S u p e r p o s e n t ( m 1 m i r r o r M a r i a g e%r o w t y p e , m 2 m i r r o r M a r i a g e%r o w t y p e ) R E T U R N B O O L E A N I S BEGIN I F ( m 1 . n u m m a r i <> m 2 . n u m m a r i OR m 1 . n u m f e m m e <> m 2 . n u m f e m m e ) T E H N R E T U R N FALSE; END I F ; R E T U R N N T( O ( m 2 . d a t e d i v o r c e I S N T NULL A D m 1 . d a t e M a r i a g e <= m 2 . d a t e D i v o r c e ) O N OR ( m 1 . d a t e d i v o r c e I S N T NULL A D m 2 . d a t e M a r i a g e <= m 1 . d a t e D i v o r c e ) O N ); END; P R O C E D U R E v e r i f i e M a r i a g e s S u p e r p o s e s ( n o u v e a u M a r i a g e m a r i a g e%r o w t y p e ) I S CURSOR a u t r e s M a r i a g e s IS SELECT F O R M MIRRORMARIAGE W E E numMari = nouveauMariage . numMari HR OR n u m F e m m e = n o u v e a u M a r i a g e . n u m F e m m e ; a u t r e M a r i a g e a u t r e s M a r i a g e s%R O W T Y P E ; BEGIN F O R a u t r e M a r i a g e IN a u t r e s M a r i a g e s L O O P IF ( s e S u p e r p o s e n t ( n o u v e a u M a r i a g e , a u t r e M a r i a g e ) ) T E H N RAISE mariagesSuperposes ; END I F ; END L O O P ; END; END c o n t r a i n t e s M a r i a g e s ; / C o n t r a i n t e s 5 e t 6 CREATE OR R E P L A C E p a c k a g e c o n t r a i n t e s T r a n s I S t r a n s EXCEPTION; P R O C E D U R E v e r i f i e P e r e M e r e ( n o u v e l l e P e r s o n n e M I R R O R P E R S O N N E%r o w t y p e ) ; P R O C E D U R E v e r i f i e M a r i F e m m e ( n o u v e a u M a r i a g e M A R I A G E%r o w t y p e ) ; dateDivorce ) ;

64

end c o n t r a i n t e s T r a n s ; / CREATE OR R E P L A C E TRIGGER p e r e M e r e A F T E R U D T OR INSERT O P E R S O N N E P A E N FOR EACH ROW DECLARE n o u v e l l e P e r s o n n e M I R R O R P E R S O N N E%r o w t y p e ; BEGIN n o u v e l l e P e r s o n n e . n u m p e r s := : n e w . n u m p e r s ; n o u v e l l e P e r s o n n e . p e r e := : n e w . p e r e ; n o u v e l l e P e r s o n n e . m e r e := : n e w . m e r e ; contraintesTrans . verifiePereMere ( nouvellePersonne ) ; END; / CREATE OR R E P L A C E TRIGGER m a r i F e m m e A F T E R U D T OR INSERT O M A R I A G E P A E N FOR EACH ROW DECLARE n o u v e a u M a r i a g e M A R I A G E%r o w t y p e ; BEGIN n o u v e a u M a r i a g e . n u m M a r i := : n e w . n u m M a r i ; n o u v e a u M a r i a g e . n u m F e m m e := : n e w . n u m F e m m e ; n o u v e a u M a r i a g e . d a t e M a r i a g e := : n e w . d a t e M a r i a g e ; n o u v e a u M a r i a g e . d a t e D i v o r c e := : n e w . d a t e D i v o r c e ; contraintesTrans . verifieMariFemme ( nouveauMariage ) ; END; / CREATE OR R E P L A C E p a c k a g e B O D Y c o n t r a i n t e s T r a n s I S P R O C E D U R E v e r i f i e P e r e M e r e ( n o u v e l l e P e r s o n n e M I R R O R P E R S O N N E%r o w t y p e ) I S n b INT ; BEGIN SELECT C U T( ) INTO n b O N F O R M MIRRORPERSONNE W E E pere = nouvellePersonne . mere HR OR m e r e = n o u v e l l e P e r s o n n e . p e r e ; I F ( n b <> 0 ) T E H N RAISE TRANS ; END I F ; SELECT C U T( ) INTO n b O N F O R M MIRRORMARIAGE W E E numMari = nouvellePersonne . mere HR OR n u m F e m m e = n o u v e l l e P e r s o n n e . p e r e ; I F ( n b <> 0 ) T E H N RAISE TRANS ; END I F ; END; P R O C E D U R E v e r i f i e M a r i F e m m e ( n o u v e a u M a r i a g e M A R I A G E%r o w t y p e ) I S n b INT ; BEGIN SELECT C U T( ) INTO n b O N F O R M MIRRORMARIAGE W E E numMari = nouveauMariage . numFemme HR OR n u m F e m m e = n o u v e a u M a r i a g e . n u m M a r i ; I F ( n b <> 0 ) T E H N RAISE TRANS ; END I F ; SELECT C U T( ) INTO n b O N F O R M MIRRORPERSONNE W E E pere = nouveauMariage . numFemme HR OR m e r e = n o u v e a u M a r i a g e . n u m M a r i ; I F ( n b <> 0 ) T E H N RAISE TRANS ; END I F ; END;

END c o n t r a i n t e s T r a n s ; / C o n t r a i n t e 7 CREATE OR R E P L A C E P A C K A G E c o n t r a i n t e M a r i a g e C o n s a n g u i n I S M a r i a g e C o n s a n g u i n EXCEPTION; P R O C E D U R E v e r i f i e M a r i a g e C o n s a n g u i n ( n o u v e a u M a r i a g e M A R I A G E%r o w t y p e ) ; END c o n t r a i n t e M a r i a g e C o n s a n g u i n ; / CREATE OR R E P L A C E TRIGGER m a r i a g e C o n s a n g u i n A F T E R U D T OR INSERT O M A R I A G E P A E N FOR EACH ROW DECLARE n o u v e a u M a r i a g e M A R I A G E%r o w t y p e ; BEGIN

65

n o u v e a u M a r i a g e . n u m M a r i := : n e w . n u m M a r i ; n o u v e a u M a r i a g e . n u m F e m m e := : n e w . n u m F e m m e ; n o u v e a u M a r i a g e . d a t e M a r i a g e := : n e w . d a t e M a r i a g e ; n o u v e a u M a r i a g e . d a t e D i v o r c e := : n e w . d a t e D i v o r c e ; contrainteMariageConsanguin . verifieMariageConsanguin ( nouveauMariage ) ; END; / CREATE OR R E P L A C E P A C K A G E B O D Y c o n t r a i n t e M a r i a g e C o n s a n g u i n I S F U N C T I O N p e r e ( p P E R S O N N E . n u m p e r s%t y p e ) R E T U R N P E R S O N N E . n u m p e r s%t y p e I S n u m P e r e P E R S O N N E . n u m p e r s%t y p e ; BEGIN SELECT p e r e INTO n u m P e r e F O R M MIRRORPERSONNE W E E numpers = p ; HR RETURN numPere ; EXCEPTION W E NO_DATA_FOUND T E HN H N R E T U R N NULL; END; F U N C T I O N m e r e ( p P E R S O N N E . n u m p e r s%t y p e ) R E T U R N P E R S O N N E . n u m p e r s%t y p e I S n u m M e r e P E R S O N N E . n u m p e r s%t y p e ; BEGIN SELECT m e r e INTO n u m M e r e F O R M MIRRORPERSONNE W E E numpers = p ; HR RETURN numMere ; EXCEPTION W E NO_DATA_FOUND T E HN H N R E T U R N NULL; END;

F U N C T I O N r e c h e r c h e A n c e t r e C o m m u n ( a P E R S O N N E . n u m p e r s%t y p e , b P E R S O N N E . n u m p e r s%t y p e ) R E T U R N B O O L E A N I S BEGIN I F ( a I S NULL) T E H N R E T U R N FALSE; ELSE R E T U R N ( c o n t r a i n t e C i r c u i t . d e s c e n d D e ( a , b ) ) OR r e c h e r c h e A n c e t r e C o m m u n ( p e r e ( a ) , b ) OR r e c h e r c h e A n c e t END I F ; END; P R O C E D U R E v e r i f i e M a r i a g e C o n s a n g u i n ( n o u v e a u M a r i a g e M A R I A G E%r o w t y p e ) I S BEGIN IF ( r e c h e r c h e A n c e t r e C o m m u n ( n o u v e a u M a r i a g e . numMari , n o u v e a u M a r i a g e . n u m F e m m e ) ) T E H N RAISE MariageConsanguin ; END I F ; END; END c o n t r a i n t e M a r i a g e C o n s a n g u i n ; /

66

Annexe A

Scripts de cration de bases e

A.1 Livraisons Sans contraintes

Attention : Le numro de livraison est une cl secondaire, cest-`-dire un numro unique tant donn un fournisseur. e e a e e e
CREATE TABLE P R O D U I T ( n u m p r o d number , n o m p r o d varchar2 ( 3 0 ) ) ; CREATE TABLE F O U R N I S S E U R ( n u m f o u number , n o m f o u varchar2 ( 3 0 ) ) ; CREATE TABLE P R O P O S E R ( n u m f o u number , n u m p r o d number , p r i x number ) ; CREATE TABLE L I V R A I S O N ( n u m f o u number , n u m l i number , dateli date default sysdate ); CREATE TABLE D E T A I L L I V R A I S O N ( n u m f o u number , n u m l i number , n u m p r o d number , q t e number ) ;

67

A.2

Modules et prerequis

les modules sont rpertoris dans une table, et les modules pr-requis pour sy inscrire (avec la note minimale) se e e e trouvent dans la table prerequis. Une ligne de la table PREREQUIS nous indique que pour sinscrire dans le module numro numMod, il faut avoir eu au moins noteMin au module numModPrereq. e
CREATE TABLE M O D U L E ( n u m M o d number primary key , n o m M o d varchar2 ( 3 0 ) ); CREATE TABLE P R E R E Q U I S ( n u m M o d number r e f e r e n c e s M O D U L E ( n u m M o d ) , n u m M o d P r e r e q number r e f e r e n c e s M O D U L E ( n u m M o d ) , n o t e M i n number ( 2 ) DEFAULT 10 N T NULL , O P I A Y KEY( n u m M o d , n u m M o d P r e r e q ) RM R ); INSERT INSERT INSERT INSERT INSERT INSERT INSERT INSERT INSERT INSERT INSERT INSERT INSERT INSERT INTO INTO INTO INTO INTO INTO INTO INTO INTO INTO INTO INTO INTO INTO MODULE MODULE MODULE MODULE MODULE MODULE MODULE MODULE VALUES VALUES VALUES VALUES VALUES VALUES VALUES VALUES (1 , (2 , (3 , (4 , (5 , (6 , (7 , (8 , Oracle ) ; C ++ ) ; C ) ; Algo ) ; Merise ) ; PL/SQL O r a c l e ) ; mySQL ) ; Algo avancA c e ) ; , numModPrereq ) , numModPrereq ) (6 , 1 , 12); , numModPrereq ) , numModPrereq ) , numModPrereq ) VALUES ( 1 , VALUES ( 2 , VALUES ( 6 , VALUES ( 8 , VALUES ( 7 , 5); 3); 5); 5); 5);

PREREQUIS PREREQUIS PREREQUIS PREREQUIS PREREQUIS PREREQUIS

( numMod ( numMod VALUES ( numMod ( numMod ( numMod

68

A.3

Gomtrie e e

La table INTERVALLE contient des intervalles spcis par leurs bornes infrieure et suprieure. Supprimer de la e e e e table intervalle tous les intervalles qui nen sont pas avec une seule instruction.
CREATE TABLE I N T E R V A L L E ( borneInf N M E , U BR borneSup N M E , U BR P I A Y KEY ( b o r n e I n f , b o r n e S u p ) ) ; RM R CREATE TABLE R E C T A N G L E ( xHautGauche N M E , U BR yHautGauche N M E , U BR xBasDroit N M E , U BR yBasDroit N M E , U BR P I A Y KEY ( x H a u t G a u c h e , y H a u t G a u c h e , x B a s D r o i t , RM R INSERT INSERT INSERT INSERT INSERT INSERT INSERT INSERT INSERT INSERT INSERT INSERT INSERT INSERT INSERT INSERT INSERT INSERT INSERT INSERT INSERT INSERT INTO INTO INTO INTO INTO INTO INTO INTO INTO INTO INTO INTO INTO INTO INTO INTO INTO INTO INTO INTO INTO INTO INTERVALLE INTERVALLE INTERVALLE INTERVALLE INTERVALLE INTERVALLE INTERVALLE INTERVALLE INTERVALLE INTERVALLE RECTANGLE RECTANGLE RECTANGLE RECTANGLE RECTANGLE RECTANGLE RECTANGLE RECTANGLE RECTANGLE RECTANGLE RECTANGLE RECTANGLE VALUES VALUES VALUES VALUES VALUES VALUES VALUES VALUES VALUES VALUES (2 , (12 , (2 , (12 , (8 , (34 , (5 , (7 , (0 , (21 , 56); 30); 3); 3); 27); 26); 10); 32); 30); 8);

yBasDroit ) ) ;

VALUES VALUES VALUES VALUES VALUES VALUES VALUES VALUES VALUES VALUES VALUES VALUES

(2 , 12 , 5 , 7 ) ; (2 , 12 , 1 , 1 3 ) ; (10 , 13 , 1 , 1 1 ) ; (10 , 13 , 10 , 1 1 ) ; (2 , 7 , 5 , 13); (21 , 73 , 15 , 2 2 ) ; (1 , 2 , 3 , 4); (1 , 5 , 3 , 2); (1 , 6 , 3 , 6); (4 , 2 , 1 , 4); (2 , 3 , 4 , 0); (5 , 4 , 2 , 1);

69

A.4

Livraisons

CREATE TABLE P R O D U I T ( n u m p r o d number , n o m p r o d varchar2 ( 3 0 ) ) ; CREATE TABLE F O U R N I S S E U R ( n u m f o u number , n o m f o u varchar2 ( 3 0 ) ) ; CREATE TABLE P R O P O S E R ( n u m f o u number , n u m p r o d number , p r i x number N T NULL) ; O CREATE TABLE L I V R A I S O N ( n u m f o u number , n u m l i number , dateli date default sysdate ); CREATE TABLE D E T A I L L I V R A I S O N ( n u m f o u number , n u m l i number , n u m p r o d number , q t e number N T NULL) ; O a l t e r table P I A Y KEY RM R a l t e r table P I A Y KEY RM R a l t e r table P I A Y KEY RM R a l t e r table P I A Y KEY RM R a l t e r table P I A Y KEY RM R a l t e r table FOREIGN KEY a l t e r table FOREIGN KEY a l t e r table FOREIGN KEY a l t e r table FOREIGN KEY a l t e r table FOREIGN KEY INSERT INSERT INSERT INSERT INSERT INSERT INSERT INSERT INSERT INSERT INSERT INSERT INTO INTO INTO INTO INTO INTO INTO INTO INTO INTO INTO INTO p r o d u i t add c o n s t r a i n t p k _ p r o d u i t ( numprod ) ; f o u r n i s s e u r add c o n s t r a i n t p k _ f o u r n i s s e u r ( numfou ) ; p r o p o s e r add c o n s t r a i n t p k _ p r o p o s e r ( numfou , numprod ) ; l i v r a i s o n add c o n s t r a i n t p k _ l i v r a i s o n ( numfou , numli ) ; d e t a i l l i v r a i s o n add c o n s t r a i n t p k _ d e t a i l _ l i v r a i s o n ( numfou , numli , numprod ) ; p r o p o s e r add c o n s t r a i n t f k _ p r o p o s e r _ f o u r n i s s e u r ( numfou ) REFERENCES fournisseur ( numfou ) ; p r o p o s e r add c o n s t r a i n t f k _ p r o p o s e r _ p r o d u i t ( numprod ) REFERENCES produit ( numprod ) ; l i v r a i s o n add c o n s t r a i n t f k _ l i v r a i s o n ( numfou ) REFERENCES fournisseur ( numfou ) ; d e t a i l l i v r a i s o n add c o n s t r a i n t f k _ d e t a i l _ l i v r a i s o n ( numfou , numli ) REFERENCES livraison ( numfou , numli ) ; d e t a i l l i v r a i s o n add c o n s t r a i n t f k _ d e t a i l _ l i v r a i s o n _ p r o p o s e r ( numfou , numprod ) REFERENCES proposer ( numfou , numprod ) ; PRODUIT PRODUIT PRODUIT PRODUIT values values values values (1 , (2 , (3 , (4 , Roue de s e c o u r s ) ; PoupA c e Batman ) ; Cotons t i g e s ) ; Cornichons ) ; (1 , (2 , (3 , (4 , 1, 2, 2, 3, f1 f2 f3 f4 ); ); ); );

FOURNISSEUR FOURNISSEUR FOURNISSEUR FOURNISSEUR PROPOSER PROPOSER PROPOSER PROPOSER

values values values values (1 , (1 , (2 , (3 ,

values values values values

200); 15); 1); 2); 1); 2); 1);

INSERT INTO L I V R A I S O N ( n u m f o u , n u m l i ) values ( 1 , INSERT INTO L I V R A I S O N ( n u m f o u , n u m l i ) values ( 1 , INSERT INTO L I V R A I S O N ( n u m f o u , n u m l i ) values ( 3 , INSERT INSERT INSERT INSERT INSERT INTO INTO INTO INTO INTO DETAILLIVRAISON DETAILLIVRAISON DETAILLIVRAISON DETAILLIVRAISON DETAILLIVRAISON values values values values values (3 , (1 , (1 , (1 , (1 , 1, 1, 1, 2, 2, 3, 1, 2, 1, 2, 10); 25); 20); 15); 17);

70

A.5

Arbre gnalogique e e

La table PERSONNE, le champ pere contient le numro du p`re de la personne, le champ mere contient le numro e e e de la m`re de la personne. e
CREATE TABLE P E R S O N N E ( n u m p e r s number P I A Y KEY, RM R n o m varchar2 ( 3 0 ) N T NULL, O p r e n o m varchar2 ( 3 0 ) , pere REFERENCES PERSONNE ( numpers ) , mere REFERENCES PERSONNE ( numpers ) ); INSERT INSERT INSERT INSERT INSERT INSERT INSERT INSERT INSERT INSERT INSERT INSERT INSERT INSERT INSERT INSERT INSERT INSERT INSERT INSERT INSERT INTO INTO INTO INTO INTO INTO INTO INTO INTO INTO INTO INTO INTO INTO INTO INTO INTO INTO INTO INTO INTO PERSONNE PERSONNE PERSONNE PERSONNE PERSONNE PERSONNE PERSONNE PERSONNE PERSONNE PERSONNE PERSONNE PERSONNE PERSONNE PERSONNE PERSONNE PERSONNE PERSONNE PERSONNE PERSONNE PERSONNE PERSONNE VALUES VALUES VALUES VALUES VALUES VALUES VALUES VALUES VALUES VALUES VALUES VALUES VALUES VALUES VALUES VALUES VALUES VALUES VALUES VALUES VALUES (1 , (16 , (2 , (3 , (4 , (13 , (5 , (12 , (6 , (7 , (8 , (9 , (10 , (17 , (11 , (15 , (19 , (20 , (21 , (14 , (18 , de Montmirail , d i t l e Hardi , Godefroy , NULL, NULL) ; ET , NULL, NULL, NULL) ; Le Croquant , Jacqou , 1 , 1 6 ) ; La F r i p o u i l l e , J a c q o u i l l e , 1 , 1 6 ) ; Bush , Kate , NULL, NULL) ; Granger , Hermione , NULL, NULL) ; Du FA c mur , MA c dor , 3 , 4 ) ; Kobalevska Aa , S o f i a , NULL, NULL) ; Rieu , AndrA c , NULL, NULL) ; Bontoutou , Rex , 6 , 4 ) ; D i j k s t r a , Edvard , 2 , 1 3 ) ; L e i b n i z , G o t t f r i e 1 d Wilhem , 8 , 1 2 ) ; Bach , Johann S e b a s t i e n , 5 , 1 2 ) ; Mathieu , M i r e i l l e , NULL, NULL) ; Lemarchal , Gregory , 1 0 , 1 7 ) ; S o c r a t e , NULL, 3 , 1 3 ) ; Leroy , Nolwen , NULL, NULL) ; Bartoli , Jennifer , 9 , 19); Fabian , Lara , 1 0 , 1 7 ) ; Stone , Sharon , 1 5 , 2 0 ) ; Frege , E l o d i e , 7 , 1 3 ) ;

71

A.6
D O R P D O R P D O R P D O R P D O R P D O R P

Comptes bancaires
OPERATION ; TYPEOPERATION ; COMPTECLIENT ; TYPECCL ; PERSONNEL ; CLIENT ;

TABLE TABLE TABLE TABLE TABLE TABLE

CREATE TABLE C L I E N T ( n u m c l i number , n o m c l i varchar2 ( 3 0 ) , p r e n o m c l i varchar2 ( 3 0 ) , a d r e s s e varchar2 ( 6 0 ) , tel varchar (10) ); CREATE TABLE P E R S O N N E L ( n u m p e r s number , n o m p e r s varchar2 ( 3 0 ) , p r e n o m p e r s varchar2 ( 3 0 ) , m a n a g e r number , s a l a i r e number ); CREATE TABLE T Y P E C C L ( n u m t y p e c c l number , n o m t y p e c c l varchar2 ( 3 0 ) ); CREATE TABLE C O M P T E C L I E N T ( n u m c l i number , n u m c c l number , n u m t y p e c c l number , d a t e c c l d a t e d e f a u l t s y s d a t e not null , n u m p e r s number ); CREATE TABLE T Y P E O P E R A T I O N ( n u m t y p e o p e r number , n o m t y p e o p e r varchar2 ( 3 0 ) ); CREATE TABLE O P E R A T I O N ( n u m c l i number , n u m c c l number , n u m o p e r number , n u m t y p e o p e r number , d a t e o p e r d a t e d e f a u l t s y s d a t e not null , m o n t a n t o p e r number not null , l i b e l o p e r varchar2 ( 3 0 ) ); ALTER TABLE C L I E N T A D D ( CONSTRAINT p k _ c l i e n t P I A Y KEY ( n u m c l i ) , RM R CONSTRAINT c k _ t e l e p h o n e C E K( L E N G T H ( t e l )=10) H C ); ALTER TABLE P E R S O N N E L A D D ( CONSTRAINT p k _ p e r s o n n e l P I A Y KEY ( n u m p e r s ) , RM R CONSTRAINT c k _ s a l a i r e C E K( S A L A I R E >= 1 2 5 4 . 2 8 ) H C ); ALTER TABLE T Y P E C C L A D D CONSTRAINT p k _ t y p e c c l P I A Y KEY ( n u m t y p e c c l ) ; RM R ALTER TABLE T Y P E O P E R A T I O N A D D CONSTRAINT p k _ t y p e o p e r a t i o n P I A Y KEY ( n u m t y p e o p e r ) ; RM R ALTER TABLE C O M P T E C L I E N T A D D ( CONSTRAINT p k _ c o m p t e c l i e n t P I A Y KEY ( n u m c l i , n u m c c l ) , RM R CONSTRAINT f k _ c c l _ t y p e c c l FOREIGN KEY ( n u m t y p e c c l ) REFERENCES TYPECCL ( numtypeccl ) , CONSTRAINT f k _ c c l _ c l i e n t FOREIGN KEY ( n u m c l i ) REFERENCES CLIENT ( numcli ) , CONSTRAINT f k _ c c l _ p e r s o n n e l FOREIGN KEY ( n u m p e r s ) REFERENCES PERSONNEL ( numpers ) ); ALTER TABLE O P E R A T I O N A D D (

72

CONSTRAINT p k _ o p e r a t i o n P I A Y KEY ( n u m c l i , n u m c c l , n u m o p e r ) , RM R CONSTRAINT f k _ o p e r _ c c l FOREIGN KEY ( n u m c l i , n u m o p e r ) REFERENCES COMPTECLIENT ( numcli , numccl ) , CONSTRAINT f k _ o p e r _ c o d e o p e r FOREIGN KEY ( n u m t y p e o p e r ) REFERENCES typeoperation ( numtypeoper ) , CONSTRAINT m o n t a n t _ o p e r a t i o n C E K( m o n t a n t o p e r <> 0 ) H C ); INSERT INTO T Y P E C C L VALUES ( (SELECT n v l (M X( n u m t y p e c c l ) , 0 ) + 1 A F O R M TYPECCL ), Compte c o u r a n t ) ; INSERT INTO T Y P E C C L VALUES ( (SELECT n v l (M X( n u m t y p e c c l ) , 0 ) + 1 A F O R M TYPECCL ), livret ); INSERT INTO T Y P E C C L VALUES ( (SELECT n v l (M X( n u m t y p e c c l ) , 0 ) + 1 A F O R M TYPECCL ), PEL ) ; INSERT INTO T Y P E O P E R A T I O N VALUES ( (SELECT n v l (M X( n u m t y p e o p e r ) , 0 ) + 1 A F O R M TYPEOPERATION ), dA c pAt esp Aces ) ; INSERT INTO T Y P E O P E R A T I O N VALUES ( (SELECT n v l (M X( n u m t y p e o p e r ) , 0 ) + 1 A F O R M TYPEOPERATION ), prA c lAvement ) ; INSERT INTO T Y P E O P E R A T I O N VALUES ( (SELECT n v l (M X( n u m t y p e o p e r ) , 0 ) + 1 A F O R M TYPEOPERATION ), virement ) ; INSERT INTO T Y P E O P E R A T I O N VALUES ( (SELECT n v l (M X( n u m t y p e o p e r ) , 0 ) + 1 A F O R M TYPEOPERATION ), retrait );

73

A.7
D O R P D O R P D O R P D O R P D O R P D O R P

Comptes bancaires avec exceptions

OPERATION ; COMPTECLIENT ; TYPECCL ; TYPEOPERATION ; PERSONNEL ; CLIENT ;

TABLE TABLE TABLE TABLE TABLE TABLE

CREATE TABLE C L I E N T ( n u m c l i number , n o m c l i varchar2 ( 3 0 ) , p r e n o m c l i varchar2 ( 3 0 ) , a d r e s s e varchar2 ( 6 0 ) , tel varchar (10) ); CREATE TABLE P E R S O N N E L ( n u m p e r s number , n o m p e r s varchar2 ( 3 0 ) , p r e n o m p e r s varchar2 ( 3 0 ) , m a n a g e r number , s a l a i r e number ); CREATE TABLE T Y P E C C L ( n u m t y p e c c l number , n o m t y p e c c l varchar2 ( 3 0 ) ); CREATE TABLE C O M P T E C L I E N T ( n u m c l i number , n u m c c l number , n u m t y p e c c l number , d a t e c c l d a t e d e f a u l t s y s d a t e not null , n u m p e r s number ); CREATE TABLE T Y P E O P E R A T I O N ( n u m t y p e o p e r number , n o m t y p e o p e r varchar2 ( 3 0 ) ); CREATE TABLE O P E R A T I O N ( n u m c l i number , n u m c c l number , n u m o p e r number , n u m t y p e o p e r number , d a t e o p e r d a t e d e f a u l t s y s d a t e not null , m o n t a n t o p e r number not null , l i b e l o p e r varchar2 ( 3 0 ) ); ALTER TABLE C L I E N T A D D ( CONSTRAINT p k _ c l i e n t P I A Y KEY ( n u m c l i ) , RM R CONSTRAINT c k _ t e l e p h o n e C E K( L E N G T H ( t e l )=10) H C ); ALTER TABLE P E R S O N N E L A D D ( CONSTRAINT p k _ p e r s o n n e l P I A Y KEY ( n u m p e r s ) , RM R CONSTRAINT c k _ s a l a i r e C E K( S A L A I R E >= 1 2 5 4 . 2 8 ) H C ); ALTER TABLE T Y P E C C L A D D CONSTRAINT p k _ t y p e c c l P I A Y KEY ( n u m t y p e c c l ) ; RM R ALTER TABLE T Y P E O P E R A T I O N A D D CONSTRAINT p k _ t y p e o p e r a t i o n P I A Y KEY ( n u m t y p e o p e r ) ; RM R ALTER TABLE C O M P T E C L I E N T A D D ( CONSTRAINT p k _ c o m p t e c l i e n t P I A Y KEY ( n u m c l i , n u m c c l ) , RM R CONSTRAINT f k _ c c l _ t y p e c c l FOREIGN KEY ( n u m t y p e c c l ) REFERENCES TYPECCL ( numtypeccl ) , CONSTRAINT f k _ c c l _ c l i e n t FOREIGN KEY ( n u m c l i ) REFERENCES CLIENT ( numcli ) , CONSTRAINT f k _ c c l _ p e r s o n n e l FOREIGN KEY ( n u m p e r s ) REFERENCES PERSONNEL ( numpers ) ); ALTER TABLE O P E R A T I O N A D D (

74

CONSTRAINT p k _ o p e r a t i o n P I A Y KEY ( n u m c l i , n u m c c l , n u m o p e r ) , RM R CONSTRAINT f k _ o p e r _ c c l FOREIGN KEY ( n u m c l i , n u m o p e r ) REFERENCES COMPTECLIENT ( numcli , numccl ) , CONSTRAINT f k _ o p e r _ c o d e o p e r FOREIGN KEY ( n u m t y p e o p e r ) REFERENCES typeoperation ( numtypeoper ) , CONSTRAINT m o n t a n t _ o p e r a t i o n C E K( m o n t a n t o p e r <> 0 A D m o n t a n t o p e r >= 1000 A D m o n t a n t o p e r <= 1 0 0 0 ) H C N N ); INSERT INTO T Y P E C C L VALUES ( (SELECT n v l (M X( n u m t y p e c c l ) , 0 ) + 1 A F O R M TYPECCL ), Compte c o u r a n t ) ; INSERT INTO T Y P E C C L VALUES ( (SELECT n v l (M X( n u m t y p e c c l ) , 0 ) + 1 A F O R M TYPECCL ), livret ); INSERT INTO T Y P E C C L VALUES ( (SELECT n v l (M X( n u m t y p e c c l ) , 0 ) + 1 A F O R M TYPECCL ), PEL ) ; INSERT INTO T Y P E O P E R A T I O N VALUES ( (SELECT n v l (M X( n u m t y p e o p e r ) , 0 ) + 1 A F O R M TYPEOPERATION ), dA c pAt esp Aces ) ; INSERT INTO T Y P E O P E R A T I O N VALUES ( (SELECT n v l (M X( n u m t y p e o p e r ) , 0 ) + 1 A F O R M TYPEOPERATION ), prA c lAvement ) ; INSERT INTO T Y P E O P E R A T I O N VALUES ( (SELECT n v l (M X( n u m t y p e o p e r ) , 0 ) + 1 A F O R M TYPEOPERATION ), virement ) ; INSERT INTO T Y P E O P E R A T I O N VALUES ( (SELECT n v l (M X( n u m t y p e o p e r ) , 0 ) + 1 A F O R M TYPEOPERATION ), retrait );

75

A.8
D O R P D O R P D O R P D O R P D O R P D O R P

Secrtariat pdagogique e e
RESULTAT ; EXAMEN ; PREREQUIS ; INSCRIPTION ; MODULE ; ETUDIANT ;

TABLE TABLE TABLE TABLE TABLE TABLE

CREATE TABLE E T U D I A N T ( n u m E t u d number , n o m varchar2 ( 4 0 ) , p r e n o m varchar2 ( 4 0 ) , d a t e n a i s s date , c i v i l i t e varchar2 ( 4 ) , p a t r o n y m e varchar2 ( 4 0 ) , n u m s e c u varchar2 ( 1 5 ) N T NULL) ; O CREATE TABLE M O D U L E ( c o d M o d number , n o m M o d varchar2 ( 1 5 ) , e f f e c M a x number DEFAULT 3 0 ) ; CREATE TABLE E X A M E N ( c o d M o d number , c o d E x a m number , dateExam date ) ; CREATE TABLE I N S C R I P T I O N ( n u m E t u d number , c o d M o d number , dateInsc date default sysdate ) ; CREATE TABLE P R E R E Q U I S ( c o d M o d number , c o d M o d P r e r e q number , n o t e M i n number ( 4 , 2 ) N T NULL) ; O CREATE TABLE R E S U L T A T ( c o d M o d number , c o d E x a m number , n u m E t u d number , n o t e number ( 4 , 2 ) ) ; ALTER TABLE E T U D I A N T A D D CONSTRAINT p k _ e t u d i a n t P I A Y KEY ( n u m E t u d ) ; RM R ALTER TABLE M O D U L E A D D CONSTRAINT p k _ m o d u l e P I A Y KEY ( c o d M o d ) ; RM R ALTER TABLE E X A M E N A D D CONSTRAINT p k _ e x a m e n P I A Y KEY ( c o d M o d , c o d E x a m ) ; RM R ALTER TABLE P R E R E Q U I S A D D CONSTRAINT p k _ p r e r e q u i s P I A Y KEY ( c o d M o d , c o d M o d P r e r e q ) ; RM R ALTER TABLE I N S C R I P T I O N A D D CONSTRAINT p k _ i n s c r i p t i o n P I A Y KEY ( c o d M o d , n u m E t u d ) ; RM R ALTER TABLE R E S U L T A T A D D CONSTRAINT p k _ r e s u l t a t P I A Y KEY ( c o d M o d , n u m E t u d , c o d E x a m ) ; RM R ALTER TABLE I N S C R I P T I O N A D D (CONSTRAINT f k _ i n s c r i p t i o n _ e t u d i a n t FOREIGN KEY ( n u m E t u d ) REFERENCES ETUDIANT ( numEtud ) , CONSTRAINT f k _ i n s c r i p t i o n _ m o d u l e FOREIGN KEY ( c o d M o d ) REFERENCES MODULE ( codMod ) ) ; ALTER TABLE P R E R E Q U I S A D D (CONSTRAINT f k _ p r e r e q u i s _ c o d m o d FOREIGN KEY ( c o d M o d ) REFERENCES MODULE ( codMod ) , CONSTRAINT f k _ p r e r e q u i s _ c o d m o d p r e r e q FOREIGN KEY ( c o d M o d P r e r e q ) REFERENCES MODULE ( codMod ) ) ; ALTER TABLE E X A M E N A D D CONSTRAINT f k _ e x a m e n FOREIGN KEY ( c o d M o d ) REFERENCES MODULE ( codMod ) ; ALTER TABLE R E S U L T A T A D D (CONSTRAINT f k _ r e s u l t a t _ e x a m e n FOREIGN KEY ( c o d M o d , c o d E x a m ) 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 n FOREIGN KEY ( c o d M o d , n u m E t u d ) REFERENCES INSCRIPTION ( codMod , numEtud ) ) ;

76

ALTER TABLE E T U D I A N T A D D (CONSTRAINT c k _ c i v i l i t e C E K H C ( c i v i l i t e IN ( Mr , Mme , M l l e ) ), CONSTRAINT c k _ c i v i l i t e _ n u m s e c u C E K H C ( SUBSTR( n u m s e c u , 1 , 1 ) = 2 OR p a t r o n y m e I S NULL ), CONSTRAINT c k _ l e n g t h _ n u m s e c u C E K H C ( l e n g t h ( n u m s e c u ) = 15 ), CONSTRAINT c k _ a n n e e _ n u m s e c u C E K H C ( t o _ c h a r ( d a t e n a i s s , yy ) = substr ( n u m s e c u , 2 , 2 ) ) );

77

A.9

Mariages

CREATE TABLE P E R S O N N E ( n u m p e r s number P I A Y KEY, RM R n o m varchar2 ( 3 0 ) N T NULL, O p r e n o m varchar2 ( 3 0 ) , pere REFERENCES PERSONNE ( numpers ) , mere REFERENCES PERSONNE ( numpers ) ); CREATE TABLE M A R I A G E ( nummari N M E REFERENCES PERSONNE ( numpers ) , U BR numfemme N M E REFERENCES PERSONNE ( numpers ) , U BR d a t e m a r i a g e DATE DEFAULT S Y S D A T E , d a t e d i v o r c e DATE DEFAULT NULL, P I A Y KEY( n u m m a r i , n u m f e m m e , d a t e M a r i a g e ) RM R );

78