Cours informatique bases de données Oracle
...
Création des types
-- type de tableau de chaînes de caractères
CREATE OR REPLACE TYPE Type_VARRAY_VARCHAR AS VARRAY(25) OF VARCHAR(70) ;
-- type des prénoms
CREATE OR REPLACE TYPE Type_Prenoms AS VARRAY(5) OF VARCHAR(30) ; -- doublons possibl
-- type de téléphone
CREATE OR REPLACE TYPE Type_Telephone AS OBJECT (
IndicatifPays CHAR(6) , -- cf. recommandation UIT-T E.164 (1/2/2004)
Telephone CHAR(9) ) ; -- le n° sans le 1er chiffre (zéro)
-- type des téléphones
CREATE OR REPLACE TYPE Type_Telephones AS TABLE OF Type_Telephone ;
-- type d'adresse
CREATE OR REPLACE TYPE Type_Adresse AS OBJECT (
Ligne1 VARCHAR(80) ,
Ligne2 VARCHAR(80) ,
Ligne3 VARCHAR(80) ,
CodePostal VARCHAR(5) ,
Ville VARCHAR(25) ,
SiteWeb URITYPE ,
MEMBER FUNCTION DepartAdresse RETURN VARCHAR ,
PRAGMA RESTRICT_REFERENCES(DepartAdresse,WNDS) ) ; -- ne met pas à jour la BD
-- type d'immatriculation de voiture
CREATE OR REPLACE TYPE Type_ImmatVoiture AS OBJECT (
Chiffres NUMBER(4) ,
Lettres VARCHAR(3) ,
Depart VARCHAR(3) ,
MEMBER FUNCTION ConcatNoImmat(separ IN VARCHAR) RETURN VARCHAR ,
PRAGMA RESTRICT_REFERENCES(ConcatNoImmat,WNDS) ) ;
-- type d'immatriculations de voitures
CREATE OR REPLACE TYPE Type_ImmatVoitures AS TABLE OF Type_ImmatVoiture ;
-- type de voiture
CREATE OR REPLACE TYPE Type_Voiture AS OBJECT (
NoImmat Type_ImmatVoiture ,
Couleur VARCHAR(10) ) ;
-- type des voitures
CREATE OR REPLACE TYPE Type_Voitures AS TABLE OF Type_Voiture ;
-- type de diplôme
CREATE OR REPLACE TYPE Type_Diplome AS OBJECT (
IntitAbrege CHAR(5) ,
IntitComplet VARCHAR(80) ) ;
-- type de diplôme obtenu
CREATE OR REPLACE TYPE Type_DiplomeObtenu AS OBJECT (
DiplomeObtenu REF Type_Diplome , -- pointeur logique vers 1 objet d’1 table objet
Annee NUMBER(4) ) ;
-- type des diplômes obtenus
CREATE OR REPLACE TYPE Type_DiplomesObtenus AS TABLE OF Type_DiplomeObtenu ;
-- type de personne
CREATE OR REPLACE TYPE Type_Personne AS OBJECT (
IdPersonne NUMBER(2) ,
NomPersonne VARCHAR(30) ,
PrenomsPersonne Type_Prenoms ,
TelephonesPersonne Type_Telephones ,
AdressePersonne Type_Adresse ,
ORDER MEMBER FUNCTION Type_Personne_Compare(p IN Type_Personne) RETURN INTEGER ,
PRAGMA RESTRICT_REFERENCES(Type_Personne_Compare,WNDS) ,
MEMBER FUNCTION NomPrenomPersonne RETURN VARCHAR ,
PRAGMA RESTRICT_REFERENCES(NomPrenomPersonne,WNDS) ,
MEMBER FUNCTION InitialePrenomPersonne RETURN VARCHAR ,
PRAGMA RESTRICT_REFERENCES(InitialePrenomPersonne,WNDS) ,
MEMBER PROCEDURE PrenomPersonne_Insert(p IN VARCHAR) ,
MEMBER PROCEDURE PrenomPersonne_Update(p_old IN VARCHAR,p_new IN VARCHAR) ,
MEMBER PROCEDURE PrenomPersonne_Delete(p IN VARCHAR)
)
NOT FINAL ;
-- type d'étudiant
CREATE OR REPLACE TYPE Type_Etudiant UNDER Type_Personne ( -- héritage de type
DepartNaissEtu VARCHAR(3) ,
PseudoEtu BLOB ,
PhotoEtu ORDSYS.ORDIMAGE ,
SignPhotoEtu ORDSYS.ORDIMAGESIGNATURE , -- pour les comparaisons des images
CVEtu XMLTYPE ,
VoituresPossedees Type_Voitures ,
DiplomesObtenus Type_DiplomesObtenus ,
MEMBER FUNCTION EstGirondin RETURN NUMBER ,
PRAGMA RESTRICT_REFERENCES(EstGirondin,WNDS) ,
MEMBER PROCEDURE DepartNaissEtu_Update(d IN VARCHAR)
)
INSTANTIABLE
FINAL ;
Création des tables et des contraintes d’intégrité
Les contraintes d’intégrité implantées ici sont : clés primaires, référentielles, d’unicité, existentielles, de valeurs par
défaut, etc.
-- table des départements
CREATE TABLE Departements (
CodeDepartement VARCHAR(3) NOT NULL ,
NomDepartement VARCHAR(25) NOT NULL ,
CONSTRAINT ClePrimaire_Departements PRIMARY KEY ( CodeDepartement ) ,
CONSTRAINT Unicite_NomDepartement UNIQUE ( NomDepartement ) ) ;
-- table des diplômes
CREATE TABLE Diplomes OF Type_Diplome (
CONSTRAINT ClePrimaire_Diplomes PRIMARY KEY ( IntitAbrege ) ,
CONSTRAINT Existe_IntitAbrege CHECK ( IntitAbrege IS NOT NULL ) ,
CONSTRAINT Unicite_IntitComplet UNIQUE ( IntitComplet ) ,
CONSTRAINT Existe_IntitComplet CHECK ( IntitComplet IS NOT NULL ) ) ;
-- table des étudiants
CREATE TABLE Etudiants OF Type_Etudiant (
CONSTRAINT ClePrimaire_Etudiants PRIMARY KEY ( IdPersonne ) ,
CONSTRAINT Contrainte_IdPersonnePositif CHECK ( IdPersonne > 0 ) ,
CONSTRAINT Existe_IdPersonne IdPersonne NOT NULL ,
CONSTRAINT Existe_NomPersonne NomPersonne NOT NULL ,
CONSTRAINT Existe_Ligne1AdrPersonne
CHECK ( AdressePersonne.Ligne1 IS NOT NULL ) ,
CONSTRAINT Existe_CodePostalAdrPersonne
CHECK ( AdressePersonne.CodePostal IS NOT NULL ) ,
CONSTRAINT Existe_VilleAdrPersonne CHECK ( AdressePersonne.Ville IS NOT NULL ) ,
CONSTRAINT Ref_Etudiants_Departements
FOREIGN KEY ( DepartNaissEtu ) REFERENCES Departements ( CodeDepartement ) ,
CONSTRAINT Existe_DepartNaissEtu DepartNaissEtu NOT NULL
)
NESTED TABLE TelephonesPersonne STORE AS Table_TelephonesPersonne ,
XMLTYPE COLUMN CVEtu STORE AS BINARY XML
XMLSCHEMA "fiche_philosophe.xsd" ELEMENT "Philosophe" ,
NESTED TABLE VoituresPossedees STORE AS Table_VoituresPossedees ,
NESTED TABLE DiplomesObtenus STORE AS Table_DiplomesObtenus ;
ALTER TABLE Etudiants MODIFY (
CONSTRAINT Defaut_DepartNaissEtu DepartNaissEtu DEFAULT '33' ) ;
ALTER TABLE Table_VoituresPossedees ADD (
CONSTRAINT Unicite_NoImmat
UNIQUE ( NoImmat.Chiffres , NoImmat.Lettres , NoImmat.Depart ) ,
CONSTRAINT Existe_NoImmatChiffres CHECK ( NoImmat.Chiffres IS NOT NULL ) ,
CONSTRAINT Contrainte_NoImmatChiffresBorn
CHECK ( NoImmat.Chiffres BETWEEN 1 AND 9999 ) ,
CONSTRAINT Existe_NoImmatLettres CHECK ( NoImmat.Lettres IS NOT NULL ) ,
CONSTRAINT Contrainte_NoImmatLettresMajus
CHECK ( NoImmat.Lettres = UPPER(NoImmat.Lettres) ) ,
CONSTRAINT Contrainte_ListeCouleurs
CHECK ( Couleur IS NULL OR Couleur IN ( 'rouge' , 'jaune' , 'orange' ) ) ) ;
--ALTER TABLE Table_VoituresPossedees DISABLE CONSTRAINT Unicite_NoImmat ;
--ALTER TABLE Table_VoituresPossedees ENABLE CONSTRAINT Unicite_NoImmat ;
--ALTER TABLE Table_DiplomesObtenus ADD ( SCOPE FOR ( DiplomeObtenu ) IS Diplomes ) ;
ON Table_DiplomesObtenus ( NESTED_TABLE_ID , Annee ) ;
-- table des informations spatiales sur les étudiants
CREATE TABLE Etudiants_Geo (
IdPersonne NUMBER(2) PRIMARY KEY NOT NULL REFERENCES Etudiants ( IdPersonne ) ,
PosGeogEtu MDSYS.SDO_GEOMETRY ,
FigGeomEtu MDSYS.SDO_GEOMETRY ) ;
Création des métadonnées pour Oracle Spatial
INSERT INTO USER_SDO_GEOM_METADATA VALUES (
'ETUDIANTS_GEO' , -- nom de la table contenant l'attribut MDSYS.SDO_GEOMETRY 'FIGGEOMETU' , -- nom de l'attribut MDSYS.SDO_GEOMETRY : figure géométrique étds MDSYS.SDO_DIM_ARRAY ( -- grille [0;20]*[0;10] avec une tolérance au 100ème près
,
NULL ) ; COMMIT ;
Création des index (dont les index spatiaux)
-- index sur le nom des étudiants
CREATE INDEX Index_NomEtu ON Etudiants ( NomPersonne ASC ) ;
-- index QuadRree sur la figure géométrique des étudiants
--CREATE INDEX Index_QuadTree_FigGeomEtu ON Etudiants_Geo ( FigGeomEtu )
-- INDEXTYPE IS MDSYS.SPATIAL_INDEX PARAMETERS('sdo_level=4') ;
-- ou ('sdo_numtiles=100')
-- index R-tree sur la figure géométrique des étudiants
CREATE INDEX Index_RTree_FigGeomEtu ON Etudiants_Geo ( FigGeomEtu )
INDEXTYPE IS MDSYS.SPATIAL_INDEX ; -- ('sdo_index_dims=2') par défaut
Création des vues
-- vue sur les voitures des étudiants
CREATE OR REPLACE VIEW Vue_VoituresEtudiants AS
SELECT V.NoImmat.Chiffres AS NoImmatChiffres ,
V.NoImmat.Lettres AS NoImmatLettres ,
V.NoImmat.Depart AS NoImmatDepart , V.Couleur ,
IdPersonne , NomPersonne , DepartNaissEtu
FROM Etudiants E , TABLE ( SELECT VoituresPossedees
FROM Etudiants EVP
WHERE EVP.IdPersonne = E.IdPersonne ) V ;
Création des corps de types
-- corps de type d'adresse
CREATE OR REPLACE TYPE BODY Type_Adresse IS
-- calcul du département de l'adresse à partir du code postal
MEMBER FUNCTION DepartAdresse
RETURN VARCHAR
IS
BEGIN
IF SUBSTR(SELF.CodePostal,1,2) = '97' THEN
ELSE
RETURN(SUBSTR(SELF.CodePostal,1,2)) ; -- métropole et Corse
END IF ;
END ;
END ;
-- corps de type d'immatriculation de voiture
CREATE OR REPLACE TYPE BODY Type_ImmatVoiture IS
-- concatène le numéro d'immatriculation de la voiture
MEMBER FUNCTION ConcatNoImmat (
separ IN VARCHAR ) -- séparateur
RETURN VARCHAR
IS
c CHAR(1) ; -- caractère de séparation
BEGIN
IF separ IS NULL OR LENGTH(separ) = 0 THEN
c := ' ' ; -- un espace par défaut
ELSE
c := SUBSTR(separ,1,1) ; -- 1er caractère chaîne non vide en paramètre
END IF ;
RETURN LPAD(CAST(SELF.Chiffres AS VARCHAR),4,'0') || c ||
UPPER(TRIM(SELF.Lettres)) || c || SELF.Depart ;
END ;
END ;
-- corps de type de personne
CREATE OR REPLACE TYPE BODY Type_Personne IS
-- comparaison de deux personnes
ORDER MEMBER FUNCTION Type_Personne_Compare (
p IN Type_Personne ) -- personne à comparer
RETURN INTEGER
IS
BEGIN
IF SELF.IdPersonne = p.IdPersonne THEN
RETURN 0 ; -- égalité entre les deux personnes
ELSIF SELF.IdPersonne < p.IdPersonne THEN
RETURN -1 ; -- "personne courante" plus petite que celle en paramètre
ELSE
RETURN +1 ; -- "personne courante" plus grande que celle en paramètre
END IF ;
END ;
-- nom et 1er des prénoms de la personne
MEMBER FUNCTION NomPrenomPersonne
RETURN VARCHAR
IS
BEGIN
IF SELF.PrenomsPersonne.COUNT = 0 THEN
RETURN(TRIM(SELF.NomPersonne)) ; -- que le nom d’une personne sans prénom
ELSE
RETURN(TRIM(SELF.NomPersonne) || ' ' || TRIM(SELF.PrenomsPersonne(1))) ;
END IF ;
END ;
-- initiale du 1er des prénoms de la personne
MEMBER FUNCTION InitialePrenomPersonne
RETURN VARCHAR
IS
ip VARCHAR(45) ; -- initiale du prénom
p VARCHAR(30) ; -- prénom
i INTEGER ; -- indice de p
BEGIN
IF SELF.PrenomsPersonne.COUNT = 0 THEN
ip := NULL ; -- personne sans prénom
ELSE
p := TRIM(SELF.PrenomsPersonne(1)) ;
IF p IS NULL OR LENGTH(p) = 0 THEN
ip := NULL ; -- personne sans réellement de prénom
ELSE
i := 2 ;
WHILE i < LENGTH(p) LOOP
IF SUBSTR(p,i,1) IN ( '-' , ' ' ) AND
UPPER(SUBSTR(p,i+1,1)) BETWEEN 'A' AND 'Z' THEN
ip := ip || SUBSTR(p,i,2) || '.' ; -- composé ou multiple
i := i + 2 ;
ELSE
i := i + 1 ;
END IF ;
END LOOP ;
END IF ;
END IF ;
RETURN(ip) ;
END ;
-- insertion d'un prénom de la personne
MEMBER PROCEDURE PrenomPersonne_Insert (
p IN VARCHAR ) -- prénom à insérer
IS
BEGIN
IF SELF.PrenomsPersonne.COUNT < SELF.PrenomsPersonne.LIMIT THEN
SELF.PrenomsPersonne.EXTEND() ; -- ajoute un prénom NULL
SELF.PrenomsPersonne(SELF.PrenomsPersonne.COUNT) := TRIM(p) ; --l’affecte
END IF ;
END ;
-- modification d'un prénom de la personne
MEMBER PROCEDURE PrenomPersonne_Update (
p_old IN VARCHAR , -- ancien prénom à modifier
p_new IN VARCHAR ) -- nouveau prénom à modifier
IS
i INTEGER ; -- indice des prénoms
BEGIN
FOR i IN 1..SELF.PrenomsPersonne.COUNT LOOP
IF TRIM(SELF.PrenomsPersonne(i)) = TRIM(p_old) THEN
SELF.PrenomsPersonne(i) := TRIM(p_new) ; -- modifie le prénom
END IF ;
END LOOP ;
END ;
-- suppression (de toutes les occurrences) d'un prénom de la personne
MEMBER PROCEDURE PrenomPersonne_Delete (
p IN VARCHAR ) -- prénom à supprimer
IS
i INTEGER ; -- indice des prénoms
BEGIN
i := SELF.PrenomsPersonne.FIRST ;
WHILE i <= SELF.PrenomsPersonne.LAST LOOP
IF TRIM(SELF.PrenomsPersonne(i)) = TRIM(p) THEN
SELF.PrenomsPersonne(i) :=
SELF.PrenomsPersonne(SELF.PrenomsPersonne.LAST) ;
-- remplace le prénom à supprimer par le dernier prénom
SELF.PrenomsPersonne.TRIM(1) ; -- supprime le dernier prénom
ELSE
i := i + 1 ;
END IF ;
END LOOP ;
END ;
END ;
-- corps de type d'étudiant
CREATE OR REPLACE TYPE BODY Type_Etudiant IS
-- indique si l'étudiant est girondin
MEMBER FUNCTION EstGirondin
RETURN NUMBER
IS
BEGIN
IF SELF.DepartNaissEtu = '33' THEN
RETURN(1) ; -- est girondin
ELSE
RETURN(0) ; -- n'est pas girondin
END IF ;
END ;
MEMBER PROCEDURE DepartNaissEtu_Update (
d IN VARCHAR ) -- département de naissance à modifier
IS
BEGIN
UPDATE Etudiants E
SET E.DepartNaissEtu = d
WHERE VALUE(E) = SELF ; -- requiert Type_Personne_Compare()
END ;
END ;
Création des déclencheurs
-- déclenchement avant l'insertion ou la modification d'étudiants
CREATE OR REPLACE TRIGGER Declen_AvInsertUpdate_Etudiant
BEFORE
INSERT OR UPDATE
ON Etudiants
FOR EACH ROW
DECLARE
ivp INTEGER ; -- indice des voitures possédées par l'étudiant
ido INTEGER ; -- indice des diplômes obtenus par l'étudiant
jdo INTEGER ; -- indice des diplômes obtenus par l'étudiant
BEGIN
-- contrainte que le début du code postal doit correspondre à un département
IF EstCodeDepartement(:NEW.AdressePersonne.DepartAdresse()) = 0 THEN
RAISE_APPLICATION_ERROR(-20001,'Contrainte d"intégrité référentielle
Etudiants.AdressePersonne.CodePostal /
Departements.CodeDepartement violée pour '||
:NEW.AdressePersonne.DepartAdresse()) ;
END IF ;
-- contraintes sur les voitures possédées
-- (valeurs par défaut et contrainte d'intégrité référentielle du département de
-- l'immatriculation sur les départements)
IF :NEW.VoituresPossedees IS NOT NULL AND
:NEW.VoituresPossedees.FIRST IS NOT NULL THEN
FOR ivp IN :NEW.VoituresPossedees.FIRST..:NEW.VoituresPossedees.LAST LOOP
IF :NEW.VoituresPossedees.EXISTS(ivp) THEN
-- CONSTRAINT Defaut_NoImmatDepart Depart DEFAULT '33'
IF :NEW.VoituresPossedees(ivp).NoImmat.Depart IS NULL THEN
:NEW.VoituresPossedees(ivp).NoImmat.Depart := '33' ;
END IF ;
-- CONSTRAINT Ref_ImmatVoiture_Departements FOREIGN KEY
-- ( Depart ) REFERENCES Departements ( CodeDepartement )
IF EstCodeDepartement(:NEW.VoituresPossedees(ivp).NoImmat.Depart) = 0
THEN
RAISE_APPLICATION_ERROR(-20002,'Contrainte d"intégrité
référentielle Etudiants.
VoituresPossedees[].NoImmat.Depart /
Departements.CodeDepartement violée
pour '||:NEW.VoituresPossedees(ivp).
CAST(ivp AS VARCHAR)||')') ;
END IF ;
-- CONSTRAINT Defaut_Couleur CHECK ( Couleur DEFAULT 'rouge' )
IF :NEW.VoituresPossedees(ivp).Couleur IS NULL THEN
:NEW.VoituresPossedees(ivp).Couleur := 'rouge' ;
END IF ;
END IF ;
END LOOP ;
END IF ;
-- contrainte d'unicité sur les diplômes obtenus par les étudiants
IF :NEW.DiplomesObtenus IS NOT NULL AND :NEW.DiplomesObtenus.FIRST IS NOT NULL
THEN
FOR ido IN :NEW.DiplomesObtenus.FIRST..:NEW.DiplomesObtenus.LAST-1 LOOP
IF :NEW.DiplomesObtenus.EXISTS(ido) THEN
FOR jdo IN ido+1..:NEW.DiplomesObtenus.LAST LOOP
IF :NEW.DiplomesObtenus.EXISTS(jdo) THEN
-- CONSTRAINT Unicite_IdPersonne_DiplomeObtenu
-- UNIQUE ( IdPersonne , DiplomeObtenu )
IF :NEW.DiplomesObtenus(ido).DiplomeObtenu =
:NEW.DiplomesObtenus(jdo).DiplomeObtenu THEN
RAISE_APPLICATION_ERROR(-20003,'Contrainte d"unicité (
Etudiants.IdPersonne , Etudiants.
DiplomesObtenus.DiplomeObtenu )
violée pour (diplôme n° '||
CAST(ido AS VARCHAR)||' et '||
CAST(jdo AS VARCHAR)||')') ;
END IF ;
END IF ;
END LOOP ;
END IF ;
END LOOP ;
END IF ;
END ;
-- déclenchement avant l'insertion ou la modification des informations spatiales étds
CREATE OR REPLACE TRIGGER Declen_AvInsertUpdate_Etu_Geo
BEFORE
INSERT OR UPDATE
ON Etudiants_Geo
FOR EACH ROW
DECLARE
c NUMBER ; -- nb. cas d'incompatibilité figure géométrique avec métadonnées
BEGIN
-- posi° géographique, SRID 8307 (WGS 84) : -90≤latitude≤90 et -180≤longitude≤180
IF :NEW.PosGeogEtu.SDO_SRID = 8307 THEN
IF :NEW.PosGeogEtu.SDO_POINT.X < -90 OR :NEW.PosGeogEtu.SDO_POINT.X > 90 THEN
RAISE_APPLICATION_ERROR(-20004,'La latitude, dans la référence spatiale '
||:NEW.PosGeogEtu.SDO_SRID||', '||
:NEW.PosGeogEtu.SDO_POINT.X||
' doit être comprise entre -90° et +90°') ;
ELSIF :NEW.PosGeogEtu.SDO_POINT.Y < -180 OR
:NEW.PosGeogEtu.SDO_POINT.Y > 180 THEN
RAISE_APPLICATION_ERROR(-20005,
'La longitude, dans la référence spatiale '||
:NEW.PosGeogEtu.SDO_POINT.Y||
' doit être comprise entre -180° et +180°') ;
END IF ;
END IF ;
-- la figure géométrique doit être compatible avec les métadonnées
SELECT COUNT(*)
INTO c
FROM USER_SDO_GEOM_METADATA M
WHERE M.TABLE_NAME = 'ETUDIANTS_GEO' AND M.COLUMN_NAME = 'FIGGEOMETU' AND
SDO_GEOM.VALIDATE_GEOMETRY_WITH_CONTEXT(:NEW.FigGeomEtu,M.DIMINFO) <>
'TRUE' ;
IF c > 0 THEN
RAISE_APPLICATION_ERROR(-20006,'La figure géométrique n"est pas correcte ou
ne respecte pas les contraintes définies sur
l"attribut (dans USER_SDO_GEOM_METADATA)') ;
END IF ;
END ;
Création des fonctions et procédures
-- indique si un code du département en paramètre est trouvé parmi les départements
CREATE OR REPLACE FUNCTION EstCodeDepartement (
cd IN VARCHAR ) -- code du département à rechercher
RETURN NUMBER
IS
trouv NUMBER(1) ; -- le code du département est trouvé parmi les départements
BEGIN
SELECT COUNT(*) INTO trouv FROM Departements WHERE CodeDepartement = cd ;
IF trouv = 0 THEN
RETURN(0) ; -- n'est pas un département
ELSE
RETURN(1) ; -- est un département
END IF ;
END ;
-- insère (et valide définitivement) le pseudonyme des étudiants
CREATE OR REPLACE PROCEDURE InserePseudoEtudiants (
idp IN Etudiants.IdPersonne%TYPE , -- identifiant de la personne
ficpseudo IN VARCHAR ) -- fichier contenant le pseudonyme à insérer
IS
pseudo BLOB ; -- pseudonyme de l'étudiant
f BFILE ; -- pointeur de fichier en entrée
psd PLS_INTEGER := 1 ; -- position source de départ
pdd PLS_INTEGER := 1 ; -- position destination de départ
nr VARCHAR(15) ; -- nom du répertoire où doivent se trouver les informations
cr VARCHAR2(4000) ; -- chemin du répertoire où doivent se trouver informations
BEGIN
nr := UPPER('Rep_Etudiants') ;
IF ficpseudo IS NOT NULL THEN
f := BFILENAME(nr,ficpseudo) ;
IF DBMS_LOB.FILEEXISTS(f) = 1 THEN
SELECT PseudoEtu
INTO pseudo
FROM Etudiants
WHERE IdPersonne = idp
FOR UPDATE ;
DBMS_LOB.LOADBLOBFROMFILE(pseudo,f,DBMS_LOB.LOBMAXSIZE,psd,pdd) ;
DBMS_LOB.FILECLOSE(f) ;
UPDATE Etudiants SET PseudoEtu = pseudo WHERE IdPersonne = idp ;
COMMIT ;
DBMS_OUTPUT.PUT_LINE('Pseudonyme inséré') ;
ELSE
SELECT DIRECTORY_PATH
INTO cr
FROM ALL_DIRECTORIES
WHERE DIRECTORY_NAME = nr ;
DBMS_OUTPUT.PUT_LINE('Le fichier '||ficpseudo||
' n"existe pas dans le répertoire '||cr) ;
END IF ;
ELSE
DBMS_OUTPUT.PUT_LINE('Aucun fichier n"a été précisé') ;
END IF ;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('Aucun étudiant n"a le n° '||CAST(idp AS VARCHAR)) ;
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR(-20000,'Erreur non gérée de message '||SQLERRM||
' et de code '||CAST(SQLCODE AS VARCHAR)) ;
END ;
-- importe localement les photographies des étudiants dans la base de données
CREATE OR REPLACE PROCEDURE ImportePhotosEtudiants
IS
photo ORDSYS.ORDIMAGE ; -- photographie de l'étudiant à importer
ctx RAW(4000) := NULL ; -- contexte
BEGIN
FOR Curs_Etd IN ( SELECT IdPersonne , PhotoEtu FROM Etudiants ) LOOP
IF NOT Curs_Etd.PhotoEtu.ISLOCAL THEN
SELECT PhotoEtu
INTO photo
FROM Etudiants E
WHERE E.IdPersonne = Curs_Etd.IdPersonne
FOR UPDATE ;
photo.IMPORTFROM(ctx,photo.SOURCE.SRCTYPE,photo.SOURCE.SRCLOCATION,
photo.SOURCE.SRCNAME) ;
UPDATE Etudiants E
SET E.PhotoEtu = photo
WHERE E.IdPersonne = Curs_Etd.IdPersonne ;
END IF;
END LOOP ;
COMMIT ;
DBMS_OUTPUT.PUT_LINE('Photographies importées (localement) dans la base de données') ;
END ;
-- affecte les propriétés des photographies des étudiants et génère leurs signatures
CREATE OR REPLACE PROCEDURE AffectePropPhotosEtudiants
IS
photo ORDSYS.ORDIMAGE ; -- photographie de étudiant dont recherche les propriétés
signphoto ORDSYS.ORDIMAGESIGNATURE ; -- signature de la photographie de étudiant
BEGIN
FOR Curs_Etd IN ( SELECT IdPersonne , SignPhotoEtu , PhotoEtu FROM Etudiants )
LOOP
SELECT PhotoEtu , SignPhotoEtu
INTO photo , signphoto
WHERE E.IdPersonne = Curs_Etd.IdPersonne
FOR UPDATE ;
photo.SETPROPERTIES() ;
signphoto.GENERATESIGNATURE(photo) ;
UPDATE Etudiants E
SET E.PhotoEtu = photo
WHERE E.IdPersonne = Curs_Etd.IdPersonne ;
END LOOP ;
COMMIT ;
DBMS_OUTPUT.PUT_LINE('Propriétés des photographies affectées et signatures
générées') ;
END ;
-- compare deux à deux les signatures des photographies des étudiants
-- N. B. : signature = (couleur, texture, forme) chaque zone image + fond image
-- N. B. : score = valeur de comparaison de 2 images de 0 (du + similaire) à 100
CREATE OR REPLACE PROCEDURE CompareSignPhotosEtudiants (
d IN FLOAT ) -- distance (entre 0 et 100) maximale entre deux scores
IS
c VARCHAR(50) ; -- commande évaluation score comparaison photographies étudiants
BEGIN
c := 'color=0.0,texture=0.5,shape=1.0,location=0.0' ;
-- scores des comparaisons deux à deux des photographies des étudiants
DBMS_OUTPUT.PUT_LINE('Les scores des comparaisons deux à deux des photographies des étudiants sont les suivants (avec '||c||') :') ;
FOR Curs_2Etds IN ( SELECT E1.IdPersonne IdPersonne1 ,
E1.SignPhotoEtu SignPhotoEtu1 ,
E2.IdPersonne IdPersonne2 ,
E2.SignPhotoEtu SignPhotoEtu2
FROM Etudiants E1 , Etudiants E2
WHERE E1.IdPersonne < E2.IdPersonne
ORDER BY E1.IdPersonne , E2.IdPersonne ) LOOP
DBMS_OUTPUT.PUT_LINE(' > '||CAST(ORDSYS.ORDIMAGESIGNATURE.EVALUATESCORE(
Curs_2Etds.SignPhotoEtu1,Curs_2Etds.SignPhotoEtu2,c) AS
VARCHAR)||' % entre les étudiants n° '||CAST(
Curs_2Etds.IdPersonne1 AS VARCHAR)||' et '||
CAST(Curs_2Etds.IdPersonne2 AS VARCHAR)) ;
END LOOP ;
-- les photographies des étudiants les plus similaires
DBMS_OUTPUT.PUT_LINE('Les photographies des étudiants les plus similaires sont les suivantes (avec '||c||' et distantes de '||
CAST(d AS VARCHAR)||'% au maximum) :') ;
FOR Curs_2Etds IN ( SELECT E1.IdPersonne IdPersonne1 ,
E2.IdPersonne IdPersonne2 ,
ORDSYS.ORDIMAGESIGNATURE.EVALUATESCORE(
FROM Etudiants E1 , Etudiants E2
WHERE E1.IdPersonne < E2.IdPersonne AND ORDSYS.IMGSIMILAR( E1.SignPhotoEtu,E2.SignPhotoEtu,c,d) = 1
ORDER BY Score , E1.IdPersonne , E2.IdPersonne ) LOOP DBMS_OUTPUT.PUT_LINE(' > '||CAST(Curs_2Etds.Score AS VARCHAR)|| ' % entre les étudiants n° '||
CAST(Curs_2Etds.IdPersonne1 AS VARCHAR)||
' et '||CAST(Curs_2Etds.IdPersonne2 AS VARCHAR)) ;
END LOOP ;
END ;
Création des paquetages (et corps de paquetages)
Deux paquetages sont ici présentés : le premier illustre la surcharge, les exceptions et les curseurs gérés par FOR tandis que le second vérifie des contraintes d'intégrité a posteriori.
Illustration de la surcharge, des exceptions et des curseurs gérés par FOR
-- affiche le nombre de diplômes obtenus par les étudiants
-- soit total, soit d'une année, soit d'un diplôme et d'une année
CREATE OR REPLACE PACKAGE NbDiplObtEtd IS
PROCEDURE NombreDiplomes ;
PROCEDURE NombreDiplomes (
a IN NUMBER ) ;
PROCEDURE NombreDiplomes (
iad IN Diplomes.IntitAbrege%TYPE ,
a IN NUMBER ) ;
END ;
-- corps du paquetage du nombre de diplômes obtenus par les étudiants
CREATE OR REPLACE PACKAGE BODY NbDiplObtEtd IS
-- nombre total de diplômes obtenus par les étudiants
PROCEDURE NombreDiplomes
IS
nbd INTEGER ; -- nombre de diplômes obtenus par les étudiants
BEGIN
SELECT SUM(CARDINALITY(DiplomesObtenus)) INTO nbd FROM Etudiants ;
DBMS_OUTPUT.PUT_LINE('Nombre de diplômes obtenus par les étudiants : '||
CAST(nbd AS VARCHAR)) ;
END ;
-- nombre diplômes obtenus par étudiants pour 1 année, en interrompant la liste
PROCEDURE NombreDiplomes (
a IN NUMBER ) -- année d'obtention d'un diplôme
IS
nbd INTEGER ; -- nombre de diplômes obtenus par les étudiants
nbdmax INTEGER ; -- nombre diplômes obtenus par étudiants maximum à afficher
idp Etudiants.IdPersonne%TYPE ; -- identifiant de la personne
iad Diplomes.IntitAbrege%TYPE ; -- intitulé abrégé du diplôme
CURSOR Curs_DO IS
FROM Etudiants E , TABLE ( SELECT DiplomesObtenus
FROM Etudiants EDO
WHERE EDO.IdPersonne = E.IdPersonne ) EDO
WHERE EDO.Annee = a
ORDER BY E.IdPersonne ;
Exception_ArretAffichage EXCEPTION ;
BEGIN
nbdmax := 2 ;
nbd := 0 ;
OPEN Curs_DO ;
LOOP
FETCH Curs_DO INTO idp , iad ;
EXIT WHEN Curs_DO%NOTFOUND ;
nbd := nbd + 1 ;
DBMS_OUTPUT.PUT_LINE(CAST(nbd AS VARCHAR)||' : diplôme intitulé '||iad||
' obtenu l"année '||CAST(a AS VARCHAR)||
' par l"étudiant n° '||CAST(idp AS VARCHAR)) ;
IF nbd >= nbdmax THEN
CLOSE Curs_DO ;
RAISE Exception_ArretAffichage ; -- on génère l'exception ad hoc
END IF ;
END LOOP ;
CLOSE Curs_DO ;
IF nbd = 0 THEN
DBMS_OUTPUT.PUT_LINE('Aucun diplôme n"a été obtenu l"année '||
CAST(a AS VARCHAR)||' par les étudiants') ;
ELSE
DBMS_OUTPUT.PUT_LINE('Nombre de diplômes obtenus l"année '||
CAST(a AS VARCHAR)||
' par les étudiants : '||CAST(nbd AS VARCHAR)) ;
END IF ;
-- produit automatiquement exception NO_DATA_FOUND (aucune ligne retournable)
SELECT IdPersonne INTO idp FROM Etudiants WHERE 0 = 1 ;
EXCEPTION
WHEN Exception_ArretAffichage THEN
DBMS_OUTPUT.PUT_LINE('L"affichage de la liste a été interrompu suite
au '||CAST(nbdmax AS VARCHAR)||'ème étudiant') ;
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('Aucun étudiant ne peut vérifier le prédicat
(toujours faux i. e. contradiction)') ;
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR(-20000,'Erreur non gérée de message '||SQLERRM||
' et de code '||CAST(SQLCODE AS VARCHAR)) ;
END ;
-- nombre de diplômes obtenus par les étudiants pour un diplôme et une année
PROCEDURE NombreDiplomes (
iad IN Diplomes.IntitAbrege%TYPE , -- intitulé abrégé du diplôme
a IN NUMBER ) -- année d'obtention d'un diplôme
IS
nbd INTEGER ; -- nombre de diplômes obtenus par les étudiants
CURSOR Curs_DO IS
SELECT E.IdPersonne
FROM Etudiants E , TABLE ( SELECT DiplomesObtenus
FROM Etudiants EDO
WHERE EDO.IdPersonne = E.IdPersonne ) EDO
ORDER BY E.IdPersonne ;
BEGIN
nbd := 0 ;
FOR Enreg_DO IN Curs_DO LOOP -- curseur tq ouverture, lectures, fermeture
nbd := nbd + 1 ;
DBMS_OUTPUT.PUT_LINE(CAST(Curs_DO%ROWCOUNT AS VARCHAR)||
' : diplôme intitulé '||iad||
' obtenu l"année '||CAST(a AS VARCHAR)||
' par l"étudiant n° '||
CAST(Enreg_DO.IdPersonne AS VARCHAR)) ;
END LOOP ;
DBMS_OUTPUT.PUT_LINE('Nombre de diplômes intitulé '||iad||
' obtenus l"année '||CAST(a AS VARCHAR)||
' par les étudiants : '||CAST(nbd AS VARCHAR)) ;
END ;
END ;
Vérification des contraintes d'intégrité a posteriori
-- contraintes d'intégrité violées
CREATE OR REPLACE PACKAGE CIviolees IS PROCEDURE Defaut_NoImmatDepart_violee ; PROCEDURE Defaut_Couleur_violee ;
PROCEDURE Ref_ImmatVoiture_Dpts_violee ; PROCEDURE Ref_Etudiants_Dipl_violee ;
PROCEDURE Unicite_IdPersonne_Dipl_violee ;
END ;
-- corps du paquetage des contraintes d'intégrité violées
CREATE OR REPLACE PACKAGE BODY CIviolees IS
-- affiche les données violant la contrainte d'intégrité de valeur par défaut des
-- départements du numéro d'immatriculation des voitures possédées par les
-- étudiants : CONSTRAINT Defaut_NoImmatDepart NoImmat.Depart DEFAULT '33'
PROCEDURE Defaut_NoImmatDepart_violee IS
idp Etudiants.IdPersonne%TYPE ; -- identifiant de la personne
noic NUMBER(4) ; -- Etudiants.VoituresPossedees.NoImmat.Chiffres%TYPE ;
noil VARCHAR(3) ; -- Etudiants.VoituresPossedees.NoImmat.Lettres%TYPE ;
CURSOR Curs_VPsansDpt IS
SELECT IdPersonne , EVP.NoImmat.Chiffres , EVP.NoImmat.Lettres
FROM Etudiants E , TABLE ( SELECT VoituresPossedees
FROM Etudiants EVP
WHERE EVP.IdPersonne = E.IdPersonne ) EVP
WHERE EVP.NoImmat.Depart IS NULL ;
BEGIN
OPEN Curs_VPsansDpt ;
LOOP
FETCH Curs_VPsansDpt INTO idp , noic , noil ;
EXIT WHEN Curs_VPsansDpt%NOTFOUND ;
DBMS_OUTPUT.PUT_LINE('Pas de valeur pour le département du numéro
d"immatriculation de la voiture '||
CAST(noic AS VARCHAR)||' '||noil||
CAST(idp AS VARCHAR)) ;
END LOOP ;
CLOSE Curs_VPsansDpt ;
END ;
-- affiche les données violant la contrainte d'intégrité de valeur par défaut des
-- départements du numéro d'immatriculation des voitures possédées par les
-- étudiants : CONSTRAINT Defaut_Couleur Couleur DEFAULT 'rouge'
PROCEDURE Defaut_Couleur_violee IS
idp Etudiants.IdPersonne%TYPE ; -- identifiant de la personne
noi VARCHAR(12) ; -- numéro d'immatriculation
CURSOR Curs_VPsansCouleur IS
SELECT IdPersonne , EVP.NoImmat.ConcatNoImmat('_')
FROM Etudiants E , TABLE ( SELECT VoituresPossedees
FROM Etudiants EVP
WHERE EVP.IdPersonne = E.IdPersonne ) EVP
WHERE EVP.Couleur IS NULL ;
BEGIN
OPEN Curs_VPsansCouleur ;
LOOP
FETCH Curs_VPsansCouleur INTO idp , noi ;
EXIT WHEN Curs_VPsansCouleur%NOTFOUND ;
DBMS_OUTPUT.PUT_LINE('Pas de valeur pour la couleur de la voiture de
numéro d"immatriculation '||noi||' possédée par
l"étudiant n° '||CAST(idp AS VARCHAR)) ;
END LOOP ;
CLOSE Curs_VPsansCouleur ;
END ;
-- affiche les données violant la contrainte d'intégrité référentielle des
-- départements du numéro d'immatriculation des voitures possédées par les
-- étudiants : CONSTRAINT Ref_ImmatVoiture_Departements FOREIGN KEY ( Depart )
-- REFERENCES Departements ( CodeDepartement )
PROCEDURE Ref_ImmatVoiture_Dpts_violee IS
idp Etudiants.IdPersonne%TYPE ; -- identifiant de la personne
noi VARCHAR(12) ; -- numéro d'immatriculation
CURSOR Curs_pb_refDptVP IS
SELECT IdPersonne , EVP.NoImmat.ConcatNoImmat('_')
FROM Etudiants E ,
TABLE ( SELECT VoituresPossedees
FROM Etudiants EVP
WHERE EVP.IdPersonne = E.IdPersonne ) EVP ,
Departements D
WHERE EVP.NoImmat.Depart = D.CodeDepartement (+) AND – jointure externe
D.CodeDepartement IS NULL ;
BEGIN
OPEN Curs_pb_refDptVP ;
LOOP
FETCH Curs_pb_refDptVP INTO idp , noi ;
EXIT WHEN Curs_pb_refDptVP%NOTFOUND ;
DBMS_OUTPUT.PUT_LINE('Le département de la voiture de numéro
l"étudiant n° '||CAST(idp AS VARCHAR)||
' ne référence pas un département') ;
END LOOP ;
CLOSE Curs_pb_refDptVP ;
END ;
-- affiche les données violant la contrainte d'intégrité référentielle des
-- diplômes obtenus par les étudiants : CONSTRAINT Ref_Etudiants_Diplomes
-- FOREIGN KEY ( DiplomeObtenu ) REFERENCES Diplomes
PROCEDURE Ref_Etudiants_Dipl_violee IS
idp Etudiants.IdPersonne%TYPE ; -- identifiant de la personne
ado NUMBER(4) ; -- année d'obtention du diplôme
CURSOR Curs_pb_refIntitDO IS
SELECT IdPersonne , EDO.Annee
FROM Etudiants E , TABLE ( SELECT DiplomesObtenus
FROM Etudiants EDO
WHERE EDO.IdPersonne = E.IdPersonne ) EDO
WHERE EDO.DiplomeObtenu IS DANGLING OR EDO.DiplomeObtenu IS NULL ;
BEGIN
OPEN Curs_pb_refIntitDO ;
LOOP
FETCH Curs_pb_refIntitDO INTO idp , ado ;
EXIT WHEN Curs_pb_refIntitDO%NOTFOUND ;
DBMS_OUTPUT.PUT_LINE('Pas de valeur pour la référence à l"intitulé
abrégé du diplôme de l"année '||
CAST(ado AS VARCHAR)||' de l"étudiant n° '||
CAST(idp AS VARCHAR)) ;
END LOOP ;
CLOSE Curs_pb_refIntitDO ;
END ;
-- affiche les données violant la contrainte d'unicité des diplômes obtenus par
-- les étudiants : CONSTRAINT Unicite_IdPersonne_DiplomeObtenu UNIQUE
-- ( IdPersonne , DiplomeObtenu )
PROCEDURE Unicite_IdPersonne_Dipl_violee IS
idp Etudiants.IdPersonne%TYPE ; -- identifiant de la personne
iad Diplomes.IntitAbrege%TYPE ; -- intitulé abrégé du diplôme
nba NUMBER(2) ; -- nombre années différentes pour une personne et un diplôme
CURSOR Curs_pb_UniqIdpDO IS
SELECT IdPersonne , DEREF(EDO.DiplomeObtenu).IntitAbrege , COUNT(*)
FROM Etudiants E , TABLE ( SELECT DiplomesObtenus
FROM Etudiants EDO
WHERE EDO.IdPersonne = E.IdPersonne ) EDO
WHERE EDO.DiplomeObtenu IS NOT DANGLING AND -- référence pendante suite à
-- suppress. objet référencé
EDO.DiplomeObtenu IS NOT NULL
GROUP BY IdPersonne , DEREF(EDO.DiplomeObtenu).IntitAbrege
HAVING COUNT(*) >= 2 ;
BEGIN
LOOP
FETCH Curs_pb_UniqIdpDO INTO idp , iad , nba ;
EXIT WHEN Curs_pb_UniqIdpDO%NOTFOUND ;
DBMS_OUTPUT.PUT_LINE('Il y a '||CAST(nba AS VARCHAR)||' années
différentes pour la référence à l"intitulé abrégé
du diplôme de l"année '||iad||
' de l"étudiant n° '||CAST(idp AS VARCHAR)) ;
