Base de données avancées

Chapitre 3 : SGBD OR Oracle

Dr Konan Marcellin BROU

[email protected]

2O23-2024

Sommaire
 Introduction
 Création de TAD
 Les pointeurs
 Table imbriquée
 Tableau de taille
prédimenssionée
 Interrogation
 Les méthodes
 Héritage
 Vues et triggers
 Bibliographie

INP-HB/K. M. BROU BD avancées : SGBD Oracle OR 2

1
 Sommaire
 Objectifs :
 Comprendre les concepts de base
des BD objets dans Oracle
 Savoir créer une BD Objet
relationnel avec Oracle
 Savoir utiliser une BD Objet
relationnel avec Oracle

INP-HB/K. M. BROU BD avancées : SGBD Oracle OR 3

I. Introduction
 1.1. Historique des SGBD  Séparation de la description des
données des programmes
 SGBD hiérarchique (1960) d’application
 Proche des systèmes de gestion  Langages d’accès navigationnels
de fichiers. (déplacement dans des structures
 Les données sont classées de type graphe pour accéder à
hiérarchiquement. une information).
 Modèle le plus anciens  Impossible d’interroger une BD
 Particulièrement adapté aux sans savoir où était l'information
organisations à structure recherchée (on "naviguait") et
arborescente. sans écrire de programmes.
 Il présente toutefois des risques
d'anomalies lors d'opérations de
stockage, en raison de sa
structure relativement rigide :
 la suppression d'un « nœud »
engendre la suppression des
données descendantes.
INP-HB/K. M. BROU BD avancées : BD objets 4

2
 I. Introduction
 Exemple :
278
BROU
Enseignant Konan
Yakro

Cours
UV21 UV12
Algo BD
Etudiant 3 4

1 2
TOTO FROTO
Ali Mankou
Yakro Abidjan

INP-HB/K. M. BROU BD avancées : BD objets 5

I. Introduction
 SGBD réseau (1960)  Exemple :
 CODASYL (Conference on Data
Systems Languages)
Etudiant Enseignant
 Conférence sur les langages de
systèmes de traitement de
données.
Cours
 À la différence du modèle
hiérarchique, la structure n'est
pas forcément en arborescence
dans le sens descendant, rendant
possible la relation plusieurs à
plusieurs.

INP-HB/K. M. BROU BD avancées : BD objets 6

3
 I. Introduction
 SGBD relationnel ou SQL (1970)  Langages assertionnels basés sur
 Basé sur la théorie des
la logique des prédicats
permettant de manipuler les
relations. données sans dire comment les
 Organise les données sous forme accéder (par opposition aux
de tables. langage navigationnel).
 Modèle le plus utilisé aujourd’hui
 Relativement facile à gérer et
mettre en œuvre
 Assure une faible redondance et
une cohérence élevée des
données.
 Enrichir le SGBD externe afin de
faciliter l’accès aux données

INP-HB/K. M. BROU BD avancées : BD objets 7

I. Introduction
 Exemple :

Etudiant Professeur
numEt nomEt adresseEt numPr nomPr adressePr
1 TOTO Yakro 1 BROU Yakro
2 FROTO Abidjan 2 GOORE Abidjan
3 Mankou Yakro

Suit Cours
numEt NumCo note numCo libellé numPr
1 UV1 15 UV1 BD 1
1 UV2 12 UV2 Réseau

INP-HB/K. M. BROU BD avancées : BD objets 8

4
 I. Introduction
 SGBD orienté objet (1986)  Objet informatique :
 Issu des langages de

Interface
programmation orientés objet. Méthode Classe/Objet
Méthode
 Un objet regroupe en une seule
Méthode
entité les données et les fonctions Données
de traitement de ces données. Méthode
Méthode
 Bénéficie à la fois de la puissance
de modélisation des modèles
objets et de stockage d'un SGBD  BDOO : hiérarchie de tables OR
pour gérer des structures de reliées par des pointeurs.
données complexes.

INP-HB/K. M. BROU BD avancées : BD objets 9

I. Introduction
 Exemple 1 : attribut structuré  Exemple 2 : attribut de type REF
Etudiant
Commande Client
numEt bac adresse numCom dateCom @estPassee numCl nom
norue rue ville codePostal 1 13/12/06 5 Toto
2 2/12/06 NULL 2 Fatou
3 2/12/06 4 Mankou

 L’attribut adresse est structuré  L’attribut estPassee est un

 Interdit par la 1FN pointeur qui contient l’adresse
d’un client donné.
 La commande numéro 1 a été
passée par le client numéro 4.

INP-HB/K. M. BROU BD avancées : BD objets 10

10

5
 I. Introduction
 SGBD orienté document  Exemple :

 Un SGBD orienté document classe

les données en tables avec des <?xml version="1.0" ?>
documents permettant var courses = { <root>
d'enregistrer les informations. "fruits": [ <fruits>
{ "orange": 3, <item>
 Ce modèle est notamment "mangues": 4, <orange>3</orange>
approprié pour des donnés "pommes": null <mangues>4</mangues>
semi-structurées, c'est-à-dire }, <pommes></pommes>
ne suivant pas une structure { "panier": true },
</item>
fixe. ],
<item>
<panier>true</panier>
 Documents XML ou JSON. "legumes": </item>
{ "patates": "amandine", </fruits>
"figues": "de barbarie", <legumes>
"poireaux": false <patates>amandine</patates>
} <figues>de barbarie</figues>
<poireaux>false</poireaux>
}; </legumes>
</root>
JSON
XML
INP-HB/K. M. BROU BD avancées : BD objets 11

11

I. Introduction
 1.2. Limites des BD  Normalisation
relationnelles  Accroissement du nombre de
relations
 Naissance en 1970
 Problème de performance s’il y a
 Simplicité du modèle de données beaucoup de relations
 Structures de données tabulaires  problème des jointures
à deux dimensions (relation)
 Opérations séparées des données
 Langage déclaratif SQL (non
procédural)  Procédures stockées non
 Interfaçage avec un langage de intégrées dans le modèle
programmation procédural  Mauvais support des applications
 Dialogue indirect entre la BD et le non standards
langage
 CAO, FAO, BD Géographiques, BD
 Gestion des données complexes techniques
structurées
 Introduction du type BLOB
(Binary Large OBjects).

INP-HB/K. M. BROU BD avancées : BD objets 12

12

6
 I. Introduction
 1.3. Pourquoi les BD Objet- Police
relationnels Conducteurs
1, n concerne 1, n police
nom nom
 Nécessité de conserver la age adresse
compatibilité avec l'existant
1, n
 SGBD relationnel Accidents
1, 1
 Applications client-serveur code subit
rapport
 Nécessité de supporter des photo
données complexes
 Textuelles, géométriques,  En relationnel : 4 tables
géographiques, audiovisuelles,  Conducteur(nom, age)
soniques, multimédias  Police(police, nom, adresse)
 Exemple : modélisation d’une  Concerne(#nom, #police)
police d’assurance  Accident(code, rapport, photo,
#police)

INP-HB/K. M. BROU BD avancées : BD objets 13

13

I. Introduction
 En objet-relationnel : 1 seule
table

Police Nom Adresse Conducteur Accidents

24 Froto Yakro Nom Age Code Rapport Photo

Toto 45
Fatou 17 134

219

037

25 Papis Daloa

INP-HB/K. M. BROU BD avancées : BD objets 14

14

7
 I. Introduction
 Approche d’évolution des SGBD
 Mapping Objet-SGBD Relationnels
 Construire une nouvelle
technologie de BD : SGBDOO
 SGBD Orienté Objet
 Langage de Programmation
Orienté Objet persistant
 SGBD Objet-Relationnel (SGBD-
OR)
 Modèle Relationnel + extension
orientées objet

INP-HB/K. M. BROU BD avancées : BD objets 15

15

I. Introduction
 1.4. Mapping Objet-SGBD  Couche d'accès aux données
 Couche plus basse d'une
Relationnels architecture applicative
 ORM : Object Relational Mapping  Permet d'interfacer le code métier
 But : établir une correspondance avec une source de données.
entre :  Intérêt : pouvoir changer de BD
 une table de la BDR ; en n'ayant besoin de ne modifier
 et une classe du modèle objet. que la couche d'accès.
 Persistance
Paradigme relationnel Paradigme objet  Un objet (instance d'une classe)
SGBDR
Wrapper Client est persistant si l'état des
BDR objet attributs de cet objet est conservé
dans les lignes de table d'une
 Wrapper client Orienté Objet BD.
(Wrapping Objet) Un objet Java est persistant,
c'est-à-dire a sa place dans une
 Langage de Programmation table d'une BD, s'il est instance
Orienté Objet + SGBDR d'une classe Java mappée.
INP-HB/K. M. BROU BD avancées : BD objets 16

16

8
 I. Introduction
 Mapping  On stocke l'état d'un objet dans
 Le mapping d'une classe permet, une BD.
entre autres, de savoir comment  Exemple : classe Client
la table correspondante devra  possède 3 attributs numCl, nom et
être implantée dans le SGBD adresse
associé.  on associe cette classe à une table
 Opérations CRUD : qui possède 3 colonnes : numCl,
nom et adresse.
 Create (INSERT) ;
 Read (SELECT) ;
Client
 Update (UPDATE) ; numCl API de BD
 Delete (DELETE). Nom (JDBC, SQLJ)
adresse
Table Client

INP-HB/K. M. BROU BD avancées : BD objets 17

17

I. Introduction
Classe Client
numCl:Integer
Nom:String
Adresse;String
Instance
numCl:1 numCl nom adresse
Nom:Toto 1 Toto Yakro
Adresse;Yakro 2 Froto Yakro
3 Mankou Abidjan
Table Client
BD relationnelle

 Décomposition de chaque objet

en une suite de variables.
 La valeur de ces variables sont
stockées dans une ou plusieurs
tables.
INP-HB/K. M. BROU BD avancées : BD objets 18

18

9
 I. Introduction
 Forces :  Framework ORM Java
 Utilisation de systèmes existants  Hibernate
 Développement rapide  Enterprise JavaBeans Entity
d'application OO exploitant les Beans
données (business objects)
 Faiblesses :  Java Data Objects
 Cohabitation de deux paradigmes:  Castor
 Serveur de BDR (Schéma  TopLink
Relationnel)  Spring DAO
 Client : C++ ou Java (Schéma
objet)
 Cf cours Hibernate
 Pas d'intégration au SGBDR
 Pas de maintien de l'intégrité
 Procédures stockées
 Solution limitée
 Vendeurs : Ardent, Persistence
Software, Ontologic, …
INP-HB/K. M. BROU BD avancées : BD objets 19

19

I. Introduction
 1.5. SGBD Orienté Objets  Objectifs des SGBDOO :
 SGBDOO  Offrir aux langages de
programmation orientés objets le
 Créés pour gérer des structures stockage permanent et le partage
de données complexes (SDC) : entre plusieurs utilisateurs.
 SGBDOO = LOO + BD  Offrir aux BD des types des SDC et
extensibles.
 Permettre la représentation de
structures complexes et/ou à
taille variable.

INP-HB/K. M. BROU BD avancées : BD objets 20

20

10
 I. Introduction
 Avantages des SGBDO :  L'identification des objets permet
 Le schéma d'une BD objet est plus de supprimer les clés artificielles
facile à comprendre que celui souvent introduites pour atteindre
d'une BD relationnelle : la 3FN.
 Il contient plus de sémantique ;  Donc la simplification du schéma.
 Il est plus proche des entités  L'encapsulation et l'abstraction du
réelles. modèle objet permette de mieux
séparer les BD de leurs
 L'héritage permet de mieux
applications.
structurer le schéma et de
factoriser les propriétés  Notion d'interface.
communes.  Normalisé : ODMG
 La création de ses propres Types  Groupe de normalisation des
de Données Abstraits (TAD) SGBDOO.
permet une représentation plus
directe du domaine.

INP-HB/K. M. BROU BD avancées : BD objets 21

21

I. Introduction
 Inconvénients des SGBDOO :  Gestion de la concurrence
(transactions) plus difficile à
 Inertie de l’existant : très mettre en œuvre.
nombreuses BD relationnelles  Interdépendance forte des objets
utilisées ; entre eux.
 Pas de normalisation des SGBDO ;  Gestion des pannes.
 Complexité des systèmes
 Moins de souplesse pour s’adapter (problème de fiabilité).
à plusieurs applications ;  Problème de compatibilité avec
 Peu d’informaticiens formés aux les SGBDR classiques.
SGBDOO.
 Gestion de la persistance et de la
coexistence des objets :
 en MC pour leur manipulation
applicative
 et sur disque pour leur
persistance

INP-HB/K. M. BROU BD avancées : BD objets 22

22

11
 I. Introduction
 Remarques :
 Les SGBDO apporte des concepts
fondamentaux pour l'évolution
des BD
 mais leur réalité est encore en
grande partie du domaine de la
recherche.
 Ils apportent donc une innovation
 sur des aspects que les SGBDR ne
savent pas faire
 mais sans être au même niveau
sur ce que les SGBDR savent bien
faire.
 Liens pour SGBDO ou SGBDOR
 http://www.service-
architecture.com/products/object
-oriented_databases.html

INP-HB/K. M. BROU BD avancées : BD objets 23

23

I. Introduction
 1.6. SGBD Objet-Relationnel  Objectifs des SGBDOR :
 SGBDOR  Gérer des données complexes
 Ou SGBD Relationnel-Objet (temps, géo-référencement,
(SGBDRO) multimédia, types utilisateurs,
etc.) ;
 Nés du double constat :
 Rapprocher le modèle logique du
 Avantages des SGBDOO
modèle conceptuel
 Insuffisances des SGBDOO pour
répondre aux exigences de  Exemples de SGBDOR :
l'industrie des BD classiques.  Oracle, PostgreSQL, Informix,
 Introduire dans les SGBDR les Sybase, DB2
concepts des SGBDOO
 Plutôt que de concevoir de
nouveaux SGBD.

INP-HB/K. M. BROU BD avancées : BD objets 24

24

12
 I. Introduction
 Propriétés TIPS et RICE  Propriétés TIPS (Miranda)
Langage de
 T : Transaction
Modèle
relationnel programmation  I : Intégrité
de Codd objet  P : Persistance
Propriétés
 S : Structuration
Propriétés
TIPS RICE
Modèle OR
(Objet-
Relationnel)
Propriétés
TIPS+RICE

INP-HB/K. M. BROU BD avancées : BD objets 25

25

I. Introduction
 Propriétés RICE (Miranda)  Encapsulation : boîte noire avec
 Réutilisation : finalité du des méthodes de manipulation
paradigme objet (héritage,  FUNCTION et PROCEDURE
généralité, composition, associées au TAD
polymorphisme)  Niveau d’encapsulation : public,
 Héritage Multiple (clause UNDER) protected, private
 Polymorphisme (surcharge) et
Template (généricité) Objet
 Identité : identifier un objet de Polymorphisme
manière unique
Relationnel
 ROW ID
 Complexité : définition de objets Types Domaine
complexes et/ou fortement utilisateurs Table Collection
structurés Attribut
Clé
 ADT Abstract Data Type (Object Référence
ADT et Value ADT) Méthode Identifiant
 Collections : SET, LIST, MULTISET
 Opérateurs : VALUE, REF et Héritage
DEREF

INP-HB/K. M. BROU BD avancées : BD objets 26

26

13
 I. Introduction
 Quelques vendeurs de SGBDOR
 Actian Corporation (Versant
Object Database, FastObjects, and
db4o)
 db4objects, Inc. (db4o - database
for objects - open source) -
acquired by Versant Corporation
and subsequent acquired by
Actian Corporation
 NeoDatis (NeoDatis ODB)
 ObjectDB Software (ObjectDB for
Java/JDO)
 Objectivity, Inc. (Objectivity/DB)
 Oracle Corporation (Oracle
Berkeley DB)
 Orient Technologies (OrientDB)
 The Ozone Database Project
(ozone - open source)
 Versata (ObjectStore)

INP-HB/K. M. BROU BD avancées : BD objets 27

27

I. Introduction
 1.7. SQL3 (ou SQL99)  Collections :
 Etend SQL2 avec des  Tables imbriquées : collection non
ordonnée et non limitée
fonctionnalités objets et autres : d’éléments de même type
 De nouveaux types de données : (NESTED TABLE).
 Large Object (CLOB et BLOB),  Tableaux de taille
Boolean, Array, Row prédimensionnée : collection
 De TAD ordonnée et limitée d’éléments de
 Type de données créé par le même type (VARRAY).
concepteur d'un schéma OR
 Encapsule les données et les
opérations sur ces données.
 Concept identique à celui de
classe d'objets
 Identification des objets et
références
 OID : pour chaque tuple d’une
table OR (REF et DEREF)

INP-HB/K. M. BROU BD avancées : BD objets 28

28

14
 I. Introduction
 1.8. Evolution de SQL  SQL:2011
 SQL:2003  a ajouté les données temporelles
et quelques améliorations aux
 Introduit des fonctions liées à fonctions de fenêtre et à la clause
XML pour permettre FETCH.
l'interopérabilité entre les BD et
les technologies XML.  SQL:2016
 SQL:2006  A ajouté la correspondance des
motifs de ligne et les fonctions de
 Précise davantage la manière table polymorphes, ainsi que le
d'utiliser SQL avec XML. support JSON.
 Pas une révision de l'ensemble de  Dans les années 2010, JSON a
la norme SQL, mais uniquement remplacé XML comme format
de la partie qui traite de commun d'échange de données ;
l'interopérabilité SQL-XML.
 Les applications Internet
 SQL:2008 modernes utilisent JSON au lieu
 a légalisé l'utilisation de ORDER de XML comme format de
BY en dehors des définitions de données.
curseur.
 A ajouté les déclencheurs
INSTEAD OF, l'instruction
TRUNCATE et la clause FETCH.
INP-HB/K. M. BROU BD avancées : BD objets 29

29

I. Introduction
 Le mouvement NoSQL émergent a
également popularisé JSON ;
 les BD documentaires stockent
des fichiers JSON, et les magasins
de valeurs clés sont compatibles
avec le format JSON.
 La norme SQL a ajouté la prise en
charge de JSON pour permettre
l'interopérabilité avec les
applications modernes et les
nouveaux types de BD.
 SQL:2019.
 Norme SQL actuelle
 A ajouté la partie qui définit la
prise en charge des tableaux
multidimensionnels dans SQL.

INP-HB/K. M. BROU BD avancées : BD objets 30

30

15
 I. Introduction
 Exercice 1 : c) La représentation de données
 Quelles assertions expriment des complexes conduit à une
fragmentation de l'information
limites des SGBDR qui justifient
en de multiples relations et à des
le modèle relationnel-objet ? chutes de performance.
a) Le principe d'atomicité des d) Le modèle relationnel ne permet
attributs d'une relation (1FN) pas d'exécuter correctement des
empêche de disposer de transactions en réseau.
propriétés structurant plusieurs
e) Il n'est pas possible de créer des
valeurs dans un type complexe.
types de données
b) La séparation des données et des personnalisées.
traitements empêche
f) Il est impossible d'exécuter des
l'intégration des méthodes au
instructions SQL2 à partir d'un
modèle.
langage objet tel que Java, alors
que c'est possible avec SQL3.
g) L'héritage n'est pas
représentable directement.
INP-HB/K. M. BROU BD avancées : BD objets 31

31

I. Introduction
 Exercice 2 :
 Quelles fonctionnalités peuvent
être réalisées en relationnel-
objet mais pas en relationnel.
a) Associer des méthodes aux
tables
b) Mettre plusieurs valeurs dans un
enregistrement
c) Référencer un enregistrement
depuis un autre enregistrement
d) Définir ses propres types de
données

INP-HB/K. M. BROU BD avancées : BD objets 32

32

16
 I. Introduction
 Exercice 3 :  Notons que le modèle relationnel
permet une imbrication à
 Des services employant des plusieurs niveaux, par exemple,
employés et enregistrant des Motif pourrait correspondre à une
dépenses pourront directement table pour chaque dépense.
être représentés par une seule  Question :
table externe imbriquant des  Modéliser ce service en donnant
tables internes : une extension de la table Service.
 SERVICES (N° INT, CHEF On s’inspirera de la modélisation
VARCHAR, ADRESSE VARCHAR, d’une police d’assurance (slide
{EMPLOYÉS (NOM, AGE)}, 12).
{DÉPENSES (NDEP INT, MONTANT
INT, MOTIF VARCHAR)})
 Employés correspond à une table
imbriquée (ensemble de tuples)
de schéma (Nom, Age) pour
chaque service, et de même,
Dépenses correspond à une table
imbriquée pour chaque service.

INP-HB/K. M. BROU BD avancées : BD objets 33

33

II. Création de TAD

 2.1. Les types de données
 Deux sortes de types :
 Types personnalisés
 Types intégrés

 Création de TAD avec le type

personnalisé TYPE

INP-HB/K. M. BROU BD avancées : SGBD Oracle OR 34

34

17
 II. Création de TAD
Création des tables
 2.2. Création de TAD et de 

tables OR CREATE TABLE nomTablePrincipale OF nomType

(PRIMARY KEY(attribut1),
 Syntaxe générale
attribut2 NOT NULL, UNIQUE (attribut3)
CREATE  Création desAS
TYPE nomType TAD
OBJECT FOREIGN KEY (attribut4) REFERENCES ...
(nomAttribut1 typeAttribut1
);
...
MEMBER FUNCTION nomFonction1 (parametre1
IN|OUT typeParametre1, ...)  Remarque :
RETURN typeFonction1  Pour être "héritable" , un TAD
...) [NOT FINAL]; doit être déclaré avec la clause
optionnelle NOT FINAL.
CREATE TYPE BODY nomType IS
MEMBER FUNCTION nomFonction1 (...) RETURN
typeFonction1 IS
BEGIN
...
END;
/
INP-HB/K. M. BROU BD avancées : SGBD Oracle OR 35

35

II. Création de TAD

 Création d’un TAD  Création d’une table OR
 Syntaxe :  Syntaxe :
CREATE [OR REPLACE] TYPE <nomType> AS OBJECT CREATE TABLE <nomTable> OF <nomType>
(nomVar1 type1, nomVar2 type2, ...); [(contrainte1, contrainte2, ...)];

 avec :
 typei : types standard ou TAD.
 Un type ne contient pas de
contraintes.
 OR REPLACE : recrée un type
sans qu’il soit nécessaire de le
détruire au paravent.
 Il est impossible de stocker des
objets dans un TAD, il faut
nécessairement déclarer une table
OR rattachée à ce type.

INP-HB/K. M. BROU BD avancées : SGBD Oracle OR 36

36

18
 II. Création de TAD
 Ajout d’un attribut dans un TAD  Suppression d’une méthode
 Syntaxe : ALTER TYPE nomDuTAD
DROP MEMBER FUNCTION|PROCEDURE
ALTER TYPE nomDuTAD
nomDeMthode[(arguments)]
ADD ATTRIBUTE nomDeAttribut typeDeAttribut
[RETURN typeDeRetour] cascade;
cascade;

 cascade propage aux tables déjà

construites à partir du TAD  Suppression du corps des
méthode
 Ajout d’une méthode dans un
TAD DROP TYPE BODY nomDuTAD;
 Syntaxe :
ALTER TYPE nomDuTAD
ADD MEMBER FUNCTION|PROCEDURE
nomDeMthode[(arguments)]
[RETURN typeDeRetour] cascade;

INP-HB/K. M. BROU BD avancées : SGBD Oracle OR 37

37

II. Création de TAD

Création de la table
 2.3. TAD utilisé par une table 

CREATE TABLE Adresse OF adresseType;

adresseType

norue rue ville codePostal

Adresse
norue rue ville codePostal

 adresseType.sql
 Création du TAD
CREATE TYPE adresseType AS OBJECT
(norue NUMBER,
rue VARCHAR2(30),
ville VARCHAR2(20),
codePostal VARCHAR2(10)
)
/
INP-HB/K. M. BROU BD avancées : SGBD Oracle OR 38

38

19
 II. Création de TAD
 Structure d’un TAD ou d’une  Insertion
table OR  adresseInsert.sql
 Commande DESC Commentaire
INSERT INTO adresse SQL2
SQL> DESC AdresseType
SQL> DESC Adresse VALUES(12,’rue 12’, ‘Abidjan’, 1234);
INSERT INTO adresse VALUES SQL3, utilisation du
(adresseType(21,’rue du commerce’, constructeur du type
‘Abidjan’, 1234));

 Interrogation
SQL> SELECT * FROM Adresse;

INP-HB/K. M. BROU BD avancées : SGBD Oracle OR 39

39

II. Création de TAD

 Modification
 adresseModif.sql
Requête : modification Commentaire
UPDATE Adresse SQL2
SET rue='Rue 12 barree'
WHERE norue=12;
UPDATE Adresse a SQL3
SET a.rue='Rue 12 barree’
WHERE a.norue=12;

 Suppression
 adresseSuppr.sql
Requête : suppression Commentaire
DELETE FROM Adresse WHERE norue=12; SQL2

INP-HB/K. M. BROU BD avancées : SGBD Oracle OR 40

40

20
 II. Création de TAD
clientType.sql
 2.4. TAD utilisé par un autre 

TAD Création du TAD

CREATE OR REPLACE TYPE clientType AS OBJECT
 TAD utilisé comme type d’un (numCl NUMBER,
attribut nom VARCHAR2(20),
adr adresseType
clientType
);
numCl nom adr
norue rue ville codePostal
Création de la table
Client
CREATE TABLE Client OF clientType
numCl nom adr
(CONSTRAINT client_numCl_PK PRIMARY
norue rue ville codePostal KEY (numCl));

INP-HB/K. M. BROU BD avancées : SGBD Oracle OR 41

41

II. Création de TAD

 Insertion  Interrogation
 clientInsert.sql
 SQL3 SELECT cl.numCl, cl.nom, cl.adr.norue, cl.adr.rue
FROM Client cl;
INSERT INTO Client VALUES
(5, 'Toto’, adresseType(21,'rue du commerce',
'Abidjan’, '1234'));

INSERT INTO Client VALUES

(2, 'Fatou', adresseType(7,'rue kakredou’,
'Menekre', '34'));

INP-HB/K. M. BROU BD avancées : SGBD Oracle OR 42

42

21
 II. Création de TAD
 Modification  Suppression
 clientModif.sql  clientSuppr.sql
 En SQL3  SQL3
UPDATE Client e DELETE
SET e.nom='Toto Ali’ FROM Client e
WHERE e.numCl=5; WHERE e.numCl=5;

DELETE
 SQL3, on utilise l'attribut adr du FROM Client e
type adresseType
WHERE e.adr.norue=7;
UPDATE Client e
SET e.adr.rue='Rue 12 barree’
WHERE e.adr.norue=21;

INP-HB/K. M. BROU BD avancées : SGBD Oracle OR 43

43

II. Création de TAD

 2.5. Vues du dictionnaire de  Exemple :
données SQL> SELECT TYPE_NAME
Vues Explication FROM USER_TYPES;
TABS, USER_TABLES Décrivent les tables
USER_TYPES ou ALL_TYPES Décrivent les TAD
USER_TYPE_ATTRS ou Décrivent les attributs des TAD
ALL_TYPE_ATTRS
USER_CONSTRAINTS décrit les contraintes par table
USER_CONS_COLUMNS Décrit les contraintes par
colonne
USER_COLL_TYPES Décrit les collections
USER_DEPENDENCIES ou Permettent de retrouver les
ALL_DEPENDENCIES dépendances entre TAD via les
attributs REF
USER_TYPE_METHOD ou Décrivent les méthodes des TAD
ALL_TYPE_METHOD
USER_OBJECT_TABLES ou Décrivent les tables objets-
ALL_OBJECT_TABLES relationnelles
INP-HB/K. M. BROU BD avancées : SGBD Oracle OR 44

44

22
 II. Création de TAD
 2.6. Visualiser les erreurs
 Méthode 1 :
SQL> show errors

 Méthode 2 :
SQL> select *
from user_errors;

INP-HB/K. M. BROU BD avancées : SGBD Oracle OR 45

45

II. Création de TAD

 2.7. Suppression de TAD  Option FORCE : pour supprimer
 La création ou la suppression les types incomplets
d’un TAD existant donne un  Exemple :
message d’erreurs. SQL> DROP TYPE clientType FORCE;

 Exemple :
SQL> DROP TYPE clientType;

 Oracle ne peut modifier la

structure d’un type existant.
 Il faut donc supprimer d’abord
les tables basées sur le TAD
SQL> DROP TABLE Client;
SQL> DROP TYPE clientType;

INP-HB/K. M. BROU BD avancées : SGBD Oracle OR 46

46

23
 II. Création de TAD
 2.8. TAD type d’un attribut
d’une table relationnel
 employeType.sql
Création de la table
CREATE TABLE Employe
(numE NUMBER, nom VARCHAR2(15), adr adresseType)
/

 Structure de la table  Mis à jour :

 idem Client
SQL> DESC Employe

INP-HB/K. M. BROU BD avancées : SGBD Oracle OR 47

47

II. Création de TAD

 Remarque :  La requête suivante est correcte :
 Une table qui n’est pas liée à un SQL> SELECT REF (c) FROM Client c;
TAD n’est pas une table OR
 Ses enregistrements ne sont pas
des objets car ils n’ont pas d’OID.
 La directive REF (alias) extrait un
OID.
 La requête suivante génère une
erreur
SQL> SELECT REF (e) FROM employe e;

INP-HB/K. M. BROU BD avancées : SGBD Oracle OR 48

48

24
 II. Création de TAD
Modification du TAD
 2.9. Ajout d’un attribut à un 

TAD déjà crée ALTER TYPE clientType

ADD ATTRIBUTE prenom VARCHAR2(15)
 ClientTypeAlter.sql CASCADE;
 Structure de la table Client avant
 Structure de la table Client après

INP-HB/K. M. BROU BD avancées : SGBD Oracle OR 49

49

II. Création de TAD

 2.10. Héritage  avec
 Seul l’héritage de type simple est Option Explication

autorisé INSTANTIABLE Pour les types qui peuvent être

instanciés, ils possèdent un
 Pas d’héritage multiple constructeur (option par défaut).
Insertion de ligne dans la table
 Syntaxe autorisée.
NOT INSTANTIABLE Pour les types qui ne peuvent être
CREATE TYPE nomType UNDER nomSurtype instanciés.
(colonne1 type1, … Impossible d’insérer une ligne
methode1 (paramètres1), …) dans la table.
[[NOT] INSTANTIABLE]
FINAL Pour les types "feuilles" de la
[[NOT] FINAL] ;
hiérarchie d’héritage qui ne peuvent
pas être redéfinis (option par
défaut).
NOT FINAL Pour les types qui peuvent être
redéfinis (héritage)

INP-HB/K. M. BROU BD avancées : SGBD Oracle OR 50

50

25
 II. Création de TAD
 Exemple :  Création des TAD (heritage.sql)
 Graphe d’héritage CREATE OR REPLACE TYPE adresseType AS OBJECT
(norue NUMBER,
Personne numP nom adresse rue VARCHAR2(30),
ville VARCHAR2(20),
codePostal VARCHAR2(10)
Client numP nom adresse compte )
/

numP nom adresse compte remise

ClientSpecial

INP-HB/K. M. BROU BD avancées : SGBD Oracle OR 51

51

II. Création de TAD

 Création de la table
CREATE OR REPLACE TYPE personneType AS OBJECT
(numP NUMBER,
CREATE TABLE Personne OF personneType
nom VARCHAR2(20),
(CONSTRAINT personne_numP_PK PRIMARY KEY (numP))
adr adresseType
/
) NOT FINAL
CREATE TABLE Client OF clientType
/
(CONSTRAINT client_numP_PK PRIMARY KEY (numP))
CREATE OR REPLACE TYPE clientType UNDER personneType
/
(compte NUMBER)
CREATE TABLE ClientSpecial OF clientSpecialType
NOT FINAL
(CONSTRAINT clientSpec_numP_PK PRIMARY KEY (numP))
/
/
CREATE OR REPLACE TYPE clientSpecialType UNDER clientType
(remise NUMBER)
/

INP-HB/K. M. BROU BD avancées : SGBD Oracle OR 52

52

26
 II. Création de TAD
 Insertion  Interrogation

INSERT INTO Personne VALUES SELECT p.numP, p.nom, p.adr.norue, p.adr.rue

(1, 'Toto', adresseType(21,'rue du commerce', 'Abidjan', '1234')); FROM Personne p;

INSERT INTO Personne VALUES SELECT cl.numP, cl.nom, cl.adr.norue, cl.adr.rue,

(2,'Fatou', adresseType(7,'rue kakredou', 'Menekre', '34')); cl.compte
FROM Client cl;
INSERT INTO Client VALUES
(3, 'Froto', adresseType(3,'rue SDF', 'Dakar', '834'),70004); SELECT cl.numP, cl.nom, cl.adr.norue, cl.adr.rue,
cl.compte, remise
INSERT INTO ClientSpecial VALUES FROM ClientSpecial cl
(4,'Mankou', adresseType(17,'rue kakredou', 'Menekre', /
'9034'),80005,20)
/

INP-HB/K. M. BROU BD avancées : SGBD Oracle OR 53

53

II. Création de TAD

 Substitution de type  Principe d’héritage dans les
 Capacité de pouvoir substituer un langages objets.
objet d’un type par un objet d’un  Les objets d’un type sont
sous-type. substituables par des objets d’un
 appelée en Oracle sous-type.
"Substitutability".  Une colonne pourra contenir des
 Conséquence du polymorphisme objets d’un type ou de ses sous-
dans les langages à objet types
 Une référence d’un type est
substituable par une référence sur
ses sous-types.
 Les éléments d’une collection d’un
type pourra contenir des objets de
ses sous-types.

INP-HB/K. M. BROU BD avancées : SGBD Oracle OR 54

54

27
 II. Création de TAD
 Exemple
CREATE TYPE serviceType AS OBJECT
 Substitution du type (nomS VARCHAR(10),
personneType avec le type responsable personneType
);
clientType
CREATE TABLE service of serviceType
(CONSTRAINT servive_nomS_PK PRIMARY KEY (nomS));

INSERT INTO Service VALUES

('DFRMI', personneType(1, 'Toto',
adresseType(21,'rue du commerce',
'Abidjan','1234')));

INSERT INTO Service VALUES

('DFRGME', clientType(3, 'Froto', adresseType(3,'rue
SDF', 'Dakar', '834'),70004))
/

INP-HB/K. M. BROU BD avancées : SGBD Oracle OR 55

55

II. Création de TAD

 Type non instantiable  Erreur lors d’insertion d’objet
 Par défaut les types sont dans la table
instantiable INSERT INTO Personne VALUES
 Ils sont pourvus d’un (1, 'Toto', adresseType(21,'rue du commerce',
constructeur implicite ou explicite 'Abidjan', '1234'));
 Intérêt : idem classe abstraite
 Exemple :

CREATE OR REPLACE TYPE personneType AS OBJECT

(numP NUMBER,
nom VARCHAR2(20),
adr adresseType
) NOT INSTANTIABLE NOT FINAL
/
CREATE TABLE Personne OF personneType
(CONSTRAINT personne_numP_PK PRIMARY KEY
(numP))
/
INP-HB/K. M. BROU BD avancées : SGBD Oracle OR 56

56

28
 II. Création de TAD
 Modification de la directive FINAL
 Syntaxe :
ALTER TYPE nomDuType FINAL | NOT
FINAL CASCADE;

 Modification de la directive
INSTANCIBLE
 Syntaxe :

ALTER TYPE nomDuType INSTANCIBLE

| NOT INSTANCIBLE CASCADE;

INP-HB/K. M. BROU BD avancées : SGBD Oracle OR 57

57

III. Les références

 3.1. Présentation Pseudo- Explication
 Crées par les attributs de type colonne
REF. ROWID Contient l’adresse physique de chaque
 Pseudo-colonnes ROWID et OID : enregistrement d’une BD.
Chaque objet (table, type, objet des
OID tables…) a un identifiant unique (OID).
Il est utilisé pour construire les
attributs de type REF et les adresses
des enregistrements des tables
imbriquées.
Chaque enregistrement d’une table OR
REF possède un OID via une pseudo-
colonne de type REF.
Chaque REF est composée d’une partie
de l’OID, des informations de la table
relationnelle qui l’héberge et
éventuellement du ROWID.

INP-HB/K. M. BROU BD avancées : SGBD Oracle OR 58

58

29
 III. Les références
 3.2. Référence simple  Création du TAD
 Exemple : CREATE OR REPLACE TYPE commandeType AS OBJECT
 Une commande est passée par un (numCom NUMBER,
seul client. dateCom DATE,
 Chaque commande pointe vers son estPassee REF clientType
client. )
 refSimple.sql /
 Création de la table
Commande Client
1..* 1 CREATE TABLE commande OF commandeType
numCl
numCom (CONSTRAINT commande_numCom_PK
nom
dateCom estPassee PRIMARY KEY (numCom));
adr

 Structure de la table

Commande Client
estPassee numCl
numCom nom
dateCom adr

INP-HB/K. M. BROU BD avancées : SGBD Oracle OR 59

59

III. Les références

 Insertion  La commande INSERT suivie de
 refSimpleInsert.sql SELECT REF stocke un objet dans
une table en le rattachant à une
INSERT INTO Client VALUES autre table
(4, 'Mankou', adresseType(12,'rue Tranquile', 'Dakar',  Méthode 2 :
'134'), 'Lago');
INSERT INTO Commande
INSERT INTO Commande VALUES(2, '13/12/06', NULL);
SELECT 4, '2/12/06', REF(a)
INSERT INTO Commande(numCom,dateCom)
FROM Client a
VALUES(3, '12/12/06');
WHERE a.numCl=4;
 Méthode 1 :
Commande
INSERT INTO Commande Client
numCom dateCom @estPassee
VALUES (1, '2/12/06’, ( numCl nom
1 2/12/06
SELECT REF(a)
2 2/12/06 NULL 5 Toto
FROM Client a
3 2/12/06 2 Fatou
WHERE a.numCl=4)
); 4 Mankou

INP-HB/K. M. BROU BD avancées : SGBD Oracle OR 60

60

30
 III. Les références
 Interrogation  Toutes les commandes
 SQL> SELECT * FROM Commande;
SELECT co.estPassee.numCl,
numCom dateCom @estPassee co.estPassee.nom, numCom, dateCom
1 2/12/06 0000220208A7F7FC5C97014F09AB8 FROM Commande co;
B75C861A5BFE923D0E7DD00CA433
A918F6BB13CAFE5F2

 Jointure entre les tables

Commande et Client
 Seuls les clients ayant passés une
commande
SELECT numCl, nom, numCom, dateCom
FROM Client cl, Commande co
WHERE co.estPassee = REF(cl);

INP-HB/K. M. BROU BD avancées : SGBD Oracle OR 61

61

III. Les références

 Adresse physique des
enregistrements
SELECT ROWID
FROM Client;

INP-HB/K. M. BROU BD avancées : SGBD Oracle OR 62

62

31
 III. Les références
 Modification
 refSimpleModif.sql
 Affectation de pointeur
 Affecter le client de numéro 2 à la
commande de numéro 3

UPDATE Commande co
SET co.estPassee =
(SELECT REF(cl) FROM Client cl WHERE cl.numCl=2)
WHERE co.numCom=3;

SELECT co.estPassee.numCl, co.estPassee.nom,

numCom, dateCom
FROM Commande co;

INP-HB/K. M. BROU BD avancées : SGBD Oracle OR 63

63

III. Les références

 Le Commande de numéro 3 qui
était passée par le client de
numéro 2 est passée maintenant
par le client de numéro 5

UPDATE Commande co
SET co.estPassee =
(SELECT REF(cl) FROM Client cl WHERE cl.numCl=5)
WHERE co.numCom=3;

SELECT co.estPassee.numCl, co.estPassee.nom,

numCom, dateCom
FROM Commande co;

INP-HB/K. M. BROU BD avancées : SGBD Oracle OR 64

64

32
 III. Les références
 Suppression
 refSimpleSuppr.sql

DELETE FROM Commande co

WHERE co.estPassee IS NULL ;

INP-HB/K. M. BROU BD avancées : SGBD Oracle OR 65

65

III. Les références

 3.3. Référence inverse  Création du TAD
 On suppose qu’un client ne passe ALTER TYPE clientType
qu’une seule commande. ADD ATTRIBUTE
 On crée d’abord un type vide passe REF commandeType CASCADE
 Le TAD clientType est modifié car /
il existe déjà
Client
 refInverse.sql
numCl nom @passe

5 Toto
2 Fatou
4 Mankou

Commande
numCom dateCom @estPassee
1 2/12/06
2 2/12/06 NULL
3 2/12/06
INP-HB/K. M. BROU BD avancées : SGBD Oracle OR 66

66

33
 III. Les références
 Le client 5 passe la commande 3
SELECT numCl, nom, cl.passe.numCom,
UPDATE Client cl
cl.passe.dateCom
SET cl.passe =
FROM Client cl
(SELECT REF(co)
/
FROM Commande co
WHERE co.numCom=3
)
WHERE cl.numCl=5;

 Le client 4 passe la commande 1

UPDATE Client cl
SET cl.passe =
(SELECT REF(co)
FROM Commande co
WHERE co.numCom=1
)
WHERE cl.numCl=4;

INP-HB/K. M. BROU BD avancées : SGBD Oracle OR 67

67

III. Les références

 3.4. Référence logique  Création de la table
 Implantées à l’aide des attributs CREATE TABLE commande OF commandeType
de clé étrangers (CONSTRAINT commande_numCom_PK
 FOREIGN KEY PRIMARY KEY (numCom),
 refLogique.sql CONSTRAINT comClient_numCl_FK
 Création du TAD FOREIGN KEY (numCl) REFERENCES
Client(numCl))
CREATE OR REPLACE TYPE commandeType AS /
OBJECT
 Remarque :
(numCom NUMBER,
dateCom DATE,
 Pas aussi efficace qu’un pointeur
numCl NUMBER
)
/

INP-HB/K. M. BROU BD avancées : SGBD Oracle OR 68

68

34
 III. Les références
 3.5. Clause SCOPE IS  Création du TAD
 Limite la portée d’un attribut de CREATE OR REPLACE TYPE commandeType AS
type REF OBJECT
(numCom NUMBER,
 Un TAD permet de créer plusieurs
tables ayant la même structure. dateCom DATE,
 "SCOPE IS" limite la portée d’un estPassee REF clientType
attribut de type REF à une table )
particulière parmi toutes les /
tables issues du TAD :
 permet d’améliorer l’intégrité  Création de la table
référentielle, nécessite moins CREATE TABLE commande OF commandeType
d’espace de stockage et améliore (CONSTRAINT commande_numCom_PK
les accès. PRIMARY KEY (numCom), estPassee SCOPE
 Exemple : IS Client);
 refSCOPE.sql
 Remarque :
 Les objets de la table Commande  SCOPE IS Client indique que
sont relies aux objets de la table estPassee référencera une ligne de la
Client. table Client
 et pas une ligne d’une autre table créée
à partir du type clientType
INP-HB/K. M. BROU BD avancées : SGBD Oracle OR 69

69

III. Les références

 3.6. Clause OIDINDEX
 Crée un index sur l’OID des
objets de la table.
 Permet d’améliorer les
performances sur les requêtes
d’interrogation qui utilisent les
attributs de type REF via des
jointures implicites.
 clientTypeIndex.sql
 Création des tables
CREATE TABLE Client OF clientType
(CONSTRAINT client_numCl_PK PRIMARY KEY (numCl))
OIDINDEX OIDClient;

CREATE TABLE commande OF commandeType

(CONSTRAINT commande_numCom_PK PRIMARY KEY
(numCom), estPassee SCOPE IS Client);

INP-HB/K. M. BROU BD avancées : SGBD Oracle OR 70

70

35
 III. Les références
 3.7. Clause WITH ROWID CREATE TABLE Client OF clientType
 Concerne un attribut de type REF (CONSTRAINT client_numCl_PK PRIMARY KEY (numCl))
 Stocke le ROWID avec l’OID dans OIDINDEX OIDClient;
l’attribut de type REF.
 Permet d’améliorer les CREATE TABLE commande OF commandeType
performances de (CONSTRAINT commande_numCom_PK PRIMARY KEY
déréférencement, mais utilise (numCom), estPassee WITH ROWID SCOPE IS
davantage d’espace mémoire. Client);
 refROWID.sql
 Création des tables

INP-HB/K. M. BROU BD avancées : SGBD Oracle OR 71

71

III. Les références

 TD
 TD1
 TD2

INP-HB/K. M. BROU BD avancées : SGBD Oracle OR 72

72

36
 IV. Tables imbriquées
 4.1. Présentation
 Table imbriquée ou NESTED
TABLE
 Collection non ordonnée et non
limitée d’éléments de même
type.
 Contredit la 1FN

INP-HB/K. M. BROU BD avancées : SGBD Oracle OR 73

73

IV. Tables imbriquées

Extension de la table Departement
 4.2. Table imbriquée dans 

une table OR Departement

 Exemple : noDept budget {employes}

noEmp nom age
 Table OR Département contenant D1 250000
la table imbriquée employés. D2 850000
1 Toto 35
 Association de type 1:n D3 500000 2 Lagoh 45
 Solution 1 3 Fatou 28

Departement 1 1..* Employe

noDept
noEmp  Type TABLE
nom Permet de créer la table imbriquée
budget 
age

Departement Employe

noDept noEmp
budget employes nom
age

INP-HB/K. M. BROU BD avancées : SGBD Oracle OR 74

74

37
 IV. Tables imbriquées
 NESTEDTABLE0R.sql  Création de la table
 Création du TAD
CREATE TABLE Departement OF deptType
CREATE OR REPLACE TYPE empType AS OBJECT (CONSTRAINT Dept_PK PRIMARY KEY (noDept))
(noEmp NUMBER, NESTED TABLE employes STORE AS tabEmp;
nom VARCHAR2(30),
age NUMBER
)  La clause NESTED TABLE sert à
/ définir une table imbriquée
CREATE OR REPLACE TYPE empsType AS TABLE  La clause STORE AS permet de
OF empType nommer la structure interne qui
/ stocke les enregistrements de la
CREATE OR REPLACE TYPE DeptType AS OBJECT table imbriquée.
(noDept VARCHAR2(13),
budget NUMBER,
employes empsType
)
/

INP-HB/K. M. BROU BD avancées : SGBD Oracle OR 75

75

IV. Tables imbriquées

 Insertion  Initialisation de la NESTED TABLE
 NESTEDTABLE0RInsert.sql avec le constructeur explicite
 Pas d’initialisation de la NESTED
TABLE INSERT INTO Departement VALUES('D3',5000000,
INSERT INTO Departement(noDept, budget) empsType(empType(1, 'Toto', 35),
VALUES('D1',2500000); empType(2, 'Lagoh', 45),
empType(3, 'Fatou', 28)
 Plus possible de modifier la )
NESTED TABLE. );

 Initialisation de la NESTED TABLE

avec le constructeur par défaut Departement
noDept budget {employes}
INSERT INTO Departement VALUES('D2',8500000, noEmp nom age
empsType()); D1 250000
D2 850000
1 Toto 35
D3 500000 2 Lagoh 45
3 Fatou 28

INP-HB/K. M. BROU BD avancées : SGBD Oracle OR 76

76

38
 IV. Tables imbriquées
 Sélection SELECT d.noDept,d.budget,e.noEmp,e. nom, e.age
SELECT * FROM Departement d, TABLE(d.employes) e;
FROM Departement ;

INP-HB/K. M. BROU BD avancées : SGBD Oracle OR 77

77

IV. Tables imbriquées

 Insertion dans la nested table
 NESTEDSTABLEORInsert.sql Departement
 Commande INSERT INTO THE noDept budget {employes}
(SELECT…) noEmp nom age
D1 250000
 Insert un enregistrement dans la D2 850000 5 Froto 47
NESTED TABLE désignée par THE. 6 Mankou 37
 Le SELECT qui vient après le THE 1 Toto 35
doit retourner un seul D3 500000 2 Lagoh 45
enregistrement sinon il y a une 3 Fatou 28
erreur.
INSERT INTO THE (SELECT d.employes
FROM Departement d
WHERE d.noDept='D2')
VALUES(5, 'Froto',47);

INSERT INTO THE (SELECT d.employes

FROM Departement d
WHERE d.noDept='D2')
VALUES(6, 'Mankou',37);
INP-HB/K. M. BROU BD avancées : SGBD Oracle OR 78

78

39
 IV. Tables imbriquées
 Interrogation  Sélectionner toutes les lignes
 Sélectionner uniquement les lignes
de la table imbrique SELECT d.noDept, d.budget, e.noEmp, e.nom
 On utilise le mot clé THE qui FROM Departement d, TABLE(d.employes) e;
désigne la table imbriquée

SELECT e.noEmp, e.nom

FROM THE
(SELECT d.employes
FROM Departement d
WHERE d.noDept='D3'
) e;

INP-HB/K. M. BROU BD avancées : SGBD Oracle OR 79

79

IV. Tables imbriquées

 Modification  Visualisation
 Exemple 1 : le critère ne porte pas
sur la NESTED TABLE SELECT d.noDept, d.budget, e.noEmp, e.nom
 NESTEDTABLEORModif.sql FROM Departement d, TABLE(d.employes) e;
 Augmenter de 10% les
départements dont le budget ne
dépasse pas 6000000
UPDATE Departement d
SET d.budget = d.budget*1.1
WHERE d.budget<6000000;

INP-HB/K. M. BROU BD avancées : SGBD Oracle OR 80

80

40
 IV. Tables imbriquées
 Exemple 2 : Critère porte sur la  Visualisation
NESTED TABLE
 Augmenter de 10% les SELECT d.noDept, d.budget, e.noEmp, e.nom
départements qui ont au moins un FROM Departement d, TABLE(d.employes) e;
employé de moins de 40 ans

UPDATE Departement d1
SET d1.budget = d1.budget*1.1
WHERE EXISTS
(SELECT *
FROM THE (SELECT d2.employes
FROM Departement d2
WHERE d1.noDept=d2.noDept
)e
WHERE e.age<40
);

INP-HB/K. M. BROU BD avancées : SGBD Oracle OR 81

81

IV. Tables imbriquées

 Modification dans la NESTED  Visualisation
TABLE
SELECT d.noDept, d.budget, e.noEmp, e.nom, e.age
 Exemple : Modification de l’âge de
FROM Departement d, TABLE(d.employes) e
l’employé de numéro 2 du
département D3 WHERE noDept='D3';

UPDATE THE (SELECT d.employes

FROM Departement d
WHERE d.noDept='D3'
)e
SET e.age = 50 WHERE e.noEmp=2;

INP-HB/K. M. BROU BD avancées : SGBD Oracle OR 82

82

41
 IV. Tables imbriquées
 Modification Suppression DELETE FROM Departement d1
WHERE EXISTS
 (NESTEDTABLE0Rsuppr.sql)
(SELECT *
 Exemple 1 : Suppression dans la FROM THE (SELECT d2.employes
table principale, ceci entraîne FROM Departement d2
également la suppression de la
WHERE d1.noDept=d2.noDept
table imbriquée si elle existe.
)e
DELETE FROM Departement d WHERE e.nom='Toto';
WHERE d.noDept='D1'; );

 Exemple 2 : Suppression du  Exemple 3 : Suppression dans la

département qui emploie une table imbriquée, supprimer
personne de nom Toto. l’employé de numéro 5 du
département D2
DELETE THE (SELECT d.employes
FROM Departement d
WHERE d.noDept='D2'
)e
WHERE e.noEmp=5;

INP-HB/K. M. BROU BD avancées : SGBD Oracle OR 83

83

IV. Tables imbriquées

Création du TAD
 4.3. Table imbriquée dans 

CREATE OR REPLACE TYPE empType AS OBJECT

une table relationnelle (noEmp NUMBER,
 Exemple : Table relationnelle nom VARCHAR2(30), age NUMBER)

Département contenant la table /

CREATE OR REPLACE TYPE empsType AS
imbriquée employes. TABLE OF empType
 NESTEDTABLErel.sql /

DepartementR  Création de la table

noDep budget {employes} CREATE TABLE DepartementR
(noDept VARCHAR2(13),
noEmp nom age
CONSTRAINT Dept_PK PRIMARY KEY (noDept),
budget NUMBER, employes empsType )
NESTED TABLE employes STORE AS
tabEmp;

 MAJ : idem table imbriquée dans

une table OR

INP-HB/K. M. BROU BD avancées : SGBD Oracle OR 84

84

42
 III. Tables imbriquées
 Exercice Departement Employe
 On voudrait que la suppression noDept noEmp
d’un département n’entraine la budget 1 1..*
nom
age
suppression de tous les employés
de ce département.
 On utilisera la solution 3 de
l’association N-N avec propriété,
sauf qu’ici l’association n’aura pas
de propriété Employe
 Il faut créer une table pour les Departement
employés noEmp
noDept nom
 Créer une liste de pointeur vers les budget age
employés dans la table employes
Departement contient
refEmploye

INP-HB/K. M. BROU BD avancées : SGBD Oracle OR 85

85

IV. Tables imbriquées

 Extension des tables  Travail à faire
Employe
 Créer le TAD empType
Departement
noEmp nom age
 Créer le TAD contientType
noDept budget {employes}
 Créer le TAD deptType
D1 250000 1 Toto 35  Créer les tables
D2 2 Lagoh 45  Ajouter les employés
850000 3 Fatou 28  Ajouter Le département D1 sans
4 Froto 47 initialiser la table imbriquée
D3 500000
5 Mankou 37  Ajouter Le département D2 en
appelant le constructeur par défaut
pour initialiser la table imbriquée
avec ses employés.
 Ajouter Le département D3 en
appelant le constructeur explicite
pour initialiser la table imbriquée.
 Modifier le département D2 pour
ajouter ses employées
 Modifier le nom de l’employé Lagoh
pas Gnamien
INP-HB/K. M. BROU BD avancées : SGBD Oracle OR 86

86

43
 IV. Tables imbriquées
 4.4. Plusieurs tables  NESTEDTABLE20R.sql
CREATE OR REPLACE TYPE empType AS OBJECT
imbriquées (noEmp NUMBER, nom VARCHAR2(30), age
 On peut définir une table NUMBER);
relationnelle ou OR qui contient CREATE OR REPLACE TYPE empsType AS TABLE OF
plusieurs tables imbriquées. empType;
CREATE OR REPLACE TYPE burType AS OBJECT
 Exemple :
(noBur NUMBER);
 Soit la table Departement qui
contient les deux tables imbriquées CREATE OR REPLACE TYPE burxType AS TABLE OF
burType;
Bureaux et Employes.
CREATE OR REPLACE TYPE Dept2Type AS OBJECT
Departement (noDept VARCHAR2(13), budget NUMBER,
noDep budget {bureaux} {employes} bureaux burxType,
employes empsType);
numB noEmp nom age
CREATE TABLE Departement2 OF dept2Type
(CONSTRAINT Dept_PK PRIMARY KEY (noDept))
NESTED TABLE bureaux STORE AS tabBur,
NESTED TABLE employes STORE AS tabEmp2;
 Créez les requêtes d’insertion, de
INP-HB/K. M. BROU BD avancées : SGBD Oracle OR modification et de suppression. 87

87

IV. Tables imbriquées

 TD
 TD3 : Association 1-N
 TD4 : Association N-N sans
propriétés
 TD5 : Association N-N sans
propriétés
 TD6 : Association N-N avec
propriétés
 TD7 : Association N-Aire
 TD8 : Association réflexive
 TD9 : Agrégation
 TD10 : Composition

INP-HB/K. M. BROU BD avancées : SGBD Oracle OR 88

88

44
 V. Tableaux de taille prédimensionnée
 5.1. Présentation  Exemple :
 Stockage de trois numéros de
 Tableaux de taille téléphone maximum par client.
prédimensionnées ou VARRAY
 Varing ARRAY Client
 Collection ordonnée et limitée numCl nom adr tel
d’éléments de même type. norue rue ville codePostal
 Comme un tableau à une
dimension classique.
 Remarque :
 Si on connaît le nombre d’éléments
maximum d’une table imbriquée,
on peut utiliser un VARRAY à la
place d’une table imbriquée.

INP-HB/K. M. BROU BD avancées : SGBD Oracle OR 89

89

V. Tableaux de taille prédimensionnée

 VARRAYType.sql  Création de la table
 Création du TAD
CREATE TABLE Client OF clientType
CREATE OR REPLACE TYPE telType AS OBJECT (CONSTRAINT client_numCl_PK PRIMARY KEY (numCl));
(numTel VARCHAR2(20))
/
CREATE OR REPLACE TYPE telsType AS
VARRAY(3) OF telType
/
CREATE OR REPLACE TYPE clientType AS OBJECT
(numCl NUMBER,
nom VARCHAR2(20),
adr adresseType,
tels telsType
)
/

INP-HB/K. M. BROU BD avancées : SGBD Oracle OR 90

90

45
 V. Tableaux de taille prédimensionnée
 Insertion  Insertion d’enregistrements dans
le VARRAY (utilisation du
 VARRAYInsert.sql constructeur explicite). Un numéro
 Insertion avec initialisation à vide de téléphone, un champ NULL ne
de le VARRAY (utilisation du pourra plus être modifié par
constructeur par défaut), aucun programme.
numéro de téléphone INSERT INTO Client VALUES(3,'Froto',
INSERT INTO Client VALUES adresseType(621, 'Kakredou','Menecre',534),
(5,'Toto',adresseType(21,'rue du telsType(telType(30641212), NULL,
commerce','Abidjan','1234'), telsType()); NULL));

 Insertion d’enregistrements dans  Insertion d’enregistrements dans

le VARRAY (utilisation du le VARRAY (utilisation de PL/SQL),
constructeur explicite), trois Insertion de 2 numéros de
numéros de téléphone téléphone pour le client de num 5.
DECLARE
INSERT INTO Client VALUES(2,'Fatou',
newTel telsType := telsType(telType(30641212),
adresseType(7,'rue kakredou', 'Menekre', '34'),
telType(30642525),NULL);
telsType(telType(30641212),telType(306416
18), telType(30641581))); BEGIN
UPDATE Client SET tels = newTel WHERE numCl = 5;
END;
INP-HB/K. M. BROU BD avancées : SGBD Oracle OR 91

91

V. Tableaux de taille prédimensionnée

 Interrogation
 VARRAYSel.sql
 Utilisation de PL/SQL
SET SERVEROUTPUT ON
DECLARE
CURSOR curCl IS SELECT * FROM Client;
newTels telsType ;
cnom VARCHAR2(15);
BEGIN
FOR recCl IN curCl
LOOP
cnom :=recCl.nom ;
newTels := recCl.tels;
DBMS_OUTPUT.PUT_LINE(cnom||' '||
newTels(1).numTel||' '||newTels(2).numTe
l||' '||newTels(3).numTel);
END LOOP ;
END;
/
INP-HB/K. M. BROU BD avancées : SGBD Oracle OR 92

92

46
 V. Tableaux de taille prédimensionnée
 Modification
 VARRAYModif.sql
 Utilisation de PL/SQL DECLARE
newTels telsType ;
 Modification d’enregistrements BEGIN
dans le VARRAY SELECT tels INTO newTels
 Modification du deuxième numéro FROM Client
de téléphone du client de numéro WHERE numCl=5;
5. newTels(2).numTel := 07846367;
UPDATE Client
SET tels = newTels
WHERE numCl = 5;
END;
/

INP-HB/K. M. BROU BD avancées : SGBD Oracle OR 93

93

V. Tableaux de taille prédimensionnée

 Suppression  Suppression de tous les
numéros de téléphone du client
 VARRAYSuppr.sql de numéro 5.
 Utilisation de PL/SQL
DECLARE
 Suppression d’enregistrements
newTels telsType ;
dans le VARRAY
BEGIN
 Suppression du 2e numéro de
SELECT tels INTO newTels
téléphone du client de numéro
FROM Client WHERE numCl=5;
5.
newTels.DELETE;
UPDATE Client SET tels = newTels WHERE numCl = 5;
DECLARE
END;
newTels telsType ;
/
BEGIN
SELECT tels INTO newTels FROM Client WHERE numCl=5;
newTels(2).numTel := NULL;
UPDATE Client SET tels = newTels WHERE numCl = 5;
END;
/

INP-HB/K. M. BROU BD avancées : SGBD Oracle OR 94

94

47
 V. Tableaux de taille prédimensionnée
 5.2. Fonctions de MAJ de
collections
Fonction Description Fonction Description
EXISTS(x) Retourne TRUE si le xe élément de la collection EXTEND Ajoute/insère un élément NULL dans la collection
existe EXTEND(x) Ajoute/insère x éléments NULL dans la collection
COUNT Retourne le nombre d’élément de la collection EXTEND(x,y) Ajoute/insère x copies du ye éléments dans la
LIMIT Retourne le nombre maximum d’éléments collection
d’un VARRAY DELETE Vide/Supprime tous les éléments de la collection
FIRST Retourne le premier indice de l’élément de la DELETE(x) Réservé aux NESTED TABLE, supprime le xe
collection éléments de la collection.
LAST Retourne le dernier indice de l’élément de la DELETE(x,y) Réservé aux NESTED TABLE, supprime les
collection éléments d’indice x à y de la collection

PRIOR(x) Retourne l’élément avant le xe élément de la

collection
NEXT(x) Retourne l’élément après le xe élément de la
collection
TRIM(x) supprime x éléments à partir de la fin de la
collection
INP-HB/K. M. BROU BD avancées : SGBD Oracle OR 95

95

V. Tableaux de taille prédimensionnée

 Exemples : VARRAYFonction.sql  Nombre d’élément dans la NESTED
TABLE employes du département
 Le VARRAY tels a-t-il un deuxième D3 ?
élément pour le client de numéro
3?
SET SERVEROUTPUT ON
DECLARE
SET SERVEROUTPUT ON
newEmps empsType;
DECLARE
nb NUMBER;
newTels telsType;
BEGIN
BEGIN
SELECT employes INTO newEmps
SELECT tels INTO newTels
FROM Departement
FROM Client
WHERE noDept = 'D1';
WHERE numCl = 3;
nb := newEmps.COUNT;
IF newTels.EXISTS(2) THEN
DBMS_OUTPUT.PUT_LINE('il y a ' || nb || ' elts');
DBMS_OUTPUT.PUT_LINE('elt 2 existe');
END;
ELSE
/
DBMS_OUTPUT.PUT_LINE('elt 2 n''existe pas');
END IF;
END;
/
INP-HB/K. M. BROU BD avancées : SGBD Oracle OR 96

96

48
 V. Tableaux de taille prédimensionnée
 Ajout de 2 copies du 1er elt de la  Supprime les elts d'indice 4 à 5 de
NESTED TABLE employes de la la NESTED TABLE employes de la
table Departement table Departement

DECLARE DECLARE
newEmps empsType; newEmps empsType;
BEGIN BEGIN
SELECT employes INTO newEmps SELECT employes INTO newEmps
FROM Departement FROM Departement
WHERE noDept = 'D3'; WHERE noDept = 'D3';
newEmps.EXTEND(2,1); newEmps.DELETE(4,5);
UPDATE Departement UPDATE Departement
SET employes = newEmps SET employes = newEmps
WHERE noDept = 'D3'; WHERE noDept = 'D3';
END; END;
/ /

INP-HB/K. M. BROU BD avancées : SGBD Oracle OR 97

97

V. Tableaux de taille prédimensionnée

 Exercice
 Suite du TD 3
 Ajouter deux numéros de
téléphones à un étudiant

INP-HB/K. M. BROU BD avancées : SGBD Oracle OR 98

98

49
 VI. Interrogation
 6.1. TAD utilisé par une table  Exemple 3 :
 Utilisation d’un alias
 Exemple 1 :
SELECT numCl, nom, adr.*
SELECT numCl, nom, c.adr.noRue, c.adr.ville
FROM Client;
FROM Client c ;
 adr.* affiche toutes les colonnes de SELECT numCl, nom, c.adr.noRue, c.adr.ville
adr
FROM Client c
 Exemple 2 : WHERE c. adr.noRue=21;
SELECT numCl, nom, adr.noRue, adr.ville
FROM Client ;

 Génère une erreur du type :

ERREUR à la ligne 1 :
ORA-00904: Nom de colonne non
valide
 Il faut utilise un alias pour éviter
cette erreur

INP-HB/K. M. BROU BD avancées : SGBD Oracle OR 99

99

VI. Interrogation
 6.2. Table avec pointeur  Fonction VALUE()
 s’applique à tout type et restitue
Commande Client en particulier les valeurs des
numCom dateCom @estPassee numCl nom pointeurs.
1 13/12/06 5 Toto SELECT VALUE(c)
2 2/12/06 NULL 2 Fatou FROM Commande c
3 2/12/06 4 Mankou WHERE c.estPassee.numCl=5;

 Commandes du client de numéro 5  Déréférencement : fonction

SELECT numCom, dateCom, c.estPassee.nom DEREF()
FROM Commande c  s’applique à un pointeur et extrait
WHERE c.estPassee.numCl=5; le contenu du TAD à l’adresse du
pointeur.
 L’adresse du client en hexa SELECT DEREF(c.estPassee)
FROM Commande c
SELECT *
WHERE c.estPassee.nom='Toto';
FROM Commande c
WHERE c.estPassee.nom='Toto';
INP-HB/K. M. BROU BD avancées : SGBD Oracle OR 100

100

50
 VI. Interrogation
 6.3. SELECT dans une table  La requête qui suit le mot clé THE
doit comporter une clause WHERE
imbriquée désignant un seul objet
Departement ERREUR à la ligne 1 :
noDep budget {employes} ORA-01427: Sous-interrogation
noEmp nom age ramenant un enregistrement de plus
d'une ligne
 Mot clé THE SELECT e.noEmp, e.nom
 Désigne la table imbriquée FROM THE (SELECT d.employes
employes FROM Departement d
)e;
SELECT e.noEmp, e.nom
FROM THE (SELECT d.employes
FROM Departement d  Solution : utiliser un curseur
WHERE d.noDept='D3' imbriqué (NESTED CURSOR)
) e;

INP-HB/K. M. BROU BD avancées : SGBD Oracle OR 101

101

VI. Interrogation
 NESTED CURSOR
 Utilisé quand la requête qui suit
 le mot clé THE ramène plusieurs
objets.
 La clause CURSOR désigne le
curseur associé à la requête
SELECT.
 La clause TABLE() permet de
traiter une table imbriquée comme
une table relationnelle.
SELECT CURSOR
(SELECT e.noEmp, e.nom
FROM TABLE(Employes) e
)
FROM Departement d;

INP-HB/K. M. BROU BD avancées : SGBD Oracle OR 102

102

51
 VII. Les méthodes
 7.1. Présentation Client
Commande
 3 sortes de méthodes : numCl
 MEMBER : méthode d’instance nom 1 estPassee * numCom
adresse dateCom
 STATIC : méthode de classe
nbCommande()
 CONSTUCTOR : constructeur
1
 Exemple :
1
 Nombre de commandes d’un client
Adresse
numero
rue
Ville
codepostal

INP-HB/K. M. BROU BD avancées : SGBD Oracle OR 103

103

VII. Les méthodes

Création de la table
 7.2. Méthode MEMBER 

FUNCTION CREATE TABLE Client OF clientType

(CONSTRAINT client_numCl_PK PRIMARY KEY
 Déclaration de la fonction (numCl));
 Une fonction qui renvoie le nombre
de commandes d’un client.
 clientMethodeF.sql
 Nombre de commande du client
 Création du TAD numéro 5
CREATE OR REPLACE TYPE clientType AS OBJECT  Approche classique
(numCl NUMBER,
nom VARCHAR2(20), SELECT COUNT(co.numCom) "nb commande"
adr adresseType, FROM Client cl, commande co
MEMBER FUNCTION nbCommande RETURN NUMBER WHERE cl.numCl = co.estPassee.numCl
) AND cl.numCl=5;
/

INP-HB/K. M. BROU BD avancées : SGBD Oracle OR 104

104

52
 VII. Les méthodes
 Corps de la fonction  Remarques :
 Nombre de commandes d’un  Toutes les méthodes sont de type
client. public, pas de type privé.
 Type privé ou public possible
CREATE OR REPLACE TYPE BODY clientType AS
dans les paquetages.
MEMBER FUNCTION nbCommande RETURN NUMBER IS
 Droit à un utilisateur pour
nbCom NUMBER; manipuler un TAD.
BEGIN
 commande GRANT EXECUTE
SELECT COUNT(co.numCom) INTO nbCom nomDuTAD TO nomUser
FROM Client cl, commande co  Mais pas l’accès à certaines
WHERE cl.numCl = co.estPassee.numCl AND méthodes.
cl.numCl=SELF.numCl;
RETURN nbCom;
END nbCommande;
END;
/

 SELF.numCl représente numCl de

l’instance de la table Client qui
appelle la méthode.
INP-HB/K. M. BROU BD avancées : SGBD Oracle OR 105

105

VII. Les méthodes

 Appel d’une fonction  Vue du dictionnaire de donnée
 Visualisation des commandes
Option Explication
USER_METHOD_PARAMS Décrivent les paramètres des
ALL_METHOD_PARAMS méthodes
USER_METHOD_RESULTS Décrivent les résultats des
ALL_METHOD_RESULTS méthodes
USER_TYPES_METHODS Décrivent les méthodes d’un
ALL_TYPE_METHODS type

INP-HB/K. M. BROU BD avancées : SGBD Oracle OR 106

106

53
 VII. Les méthodes
 Appel d’une méthode fonction SET SERVEROUTPUT ON
dans un programme PL/SQL DECLARE
cmdNbCl clientType; --création de linstance
 Fonction VALUE() : renvoie la
nb NUMBER;
valeur d’un TAD
BEGIN
 Le client 5 a 3 commandes SELECT VALUE(c) INTO cmdNbCl –initialisation de l’instance
 VALUE(c) extrait une instance FROM Client c
WHERE c.numCl = 5;
nb :=cmdNbCl.nbCommande(); --appel de la méthode
DBMS_OUTPUT.PUT_LINE('Le client 5 a ' || nb || '
commandes');
END;
/

INP-HB/K. M. BROU BD avancées : SGBD Oracle OR 107

107

VII. Les méthodes

 Appel d’une méthode fonction  Exécution
dans une procédure PL/SQL SQL> EXECUTE nombreCommande(5)
 Appel d'une méthode dans un
programme PL/SQL  Le client 5 a 3 commandes

SET SERVEROUT ON
CREATE OR REPLACE PROCEDURE
nombreCommande(numC IN NUMBER) IS
nb NUMBER;
cl clientType;
BEGIN
cl := clientType(numC,'', NULL); --instanciation
nb := cl.nbCommande();
DBMS_OUTPUT.PUT_LINE('Le client ' || numC || ' a ' ||
nb || ' commandes');
END;
/

INP-HB/K. M. BROU BD avancées : SGBD Oracle OR 108

108

54
 VII. Les méthodes
 Appel d’une méthode fonction  Ajout d’une méthode à un TAD
dans une requête  Date de la commande d’un client
SELECT c.nbCommande() "nombre de commandes" donné
FROM Client c ALTER TYPE clientType
WHERE c.numCl=5; ADD MEMBER FUNCTION montantCom(numC IN
NUMBER) RETURN NUMBER CASCADE;

ALTER TYPE clientType

ADD MEMBER FUNCTION dateCom(numC IN
NUMBER) return DATE CASCADE;

 Dans le corps de clientType, il

faut redéfinir le corps de toutes
les méthodes.

INP-HB/K. M. BROU BD avancées : SGBD Oracle OR 109

109

VII. Les méthodes

CREATE OR REPLACE TYPE BODY clientType AS
MEMBER FUNCTION nbCommande RETURN NUMBER MEMBER FUNCTION dateCom(numC IN NUMBER)
IS RETURN DATE IS
nbCom NUMBER; dateC DATE;
BEGIN BEGIN
SELECT COUNT(co.numCom) INTO nbCom SELECT co.dateCom INTO dateC
FROM Client cl, commande co FROM Client cl, Commande co
WHERE cl.numCl = co.estPassee.numCl AND WHERE co.numCom=numC AND cl.numCl =
co.estPassee.numCl AND cl.numCl=SELF.numCl;
cl.numCl=SELF.numCl;
RETURN dateC;
RETURN nbCom;
END dateCom;
END nbCommande;
END;
MEMBER FUNCTION montantCom(numC IN NUMBER)
RETURN NUMBER IS
montant NUMBER;
BEGIN
montant := -1;
RETURN montant;
END montantCom;

INP-HB/K. M. BROU BD avancées : SGBD Oracle OR 110

110

55
 VII. Les méthodes
SELECT c.montantCom(2) "Montant de la commande 1"  Suppression de méthode
FROM Client c
WHERE c.numCl=2; ALTER TYPE clientType
DROP MEMBER FUNCTION montantCom (numC IN NUMBER)
RETURN NUMBER CASCADE
SELECT c.dateCom(2) "date de la commande 2"
FROM Client c WHERE c.numCl=2;

INP-HB/K. M. BROU BD avancées : SGBD Oracle OR 111

111

VII. Les méthodes

 Exercice 1 :  Travail à faire
 transfComplet.sql 1. Faire le schéma navigationnel
2. Donner le code SQL de création des types abstraits et des
tables objets-relationnels
Client 1..* Produit
Commande 3. Donner le code SQL d’insertions d’objets dans les tables
numCl 1 passe 1..* numCom 1..* numProd 4. Donner le code SQL des requêtes suivantes :
nom dateCom designation • Liste des clients
prixU • Liste des commandes regroupées par clients
qteStock • Liste des produits
LigneCom • Liste des produits qui ont été commandés au moins une
qteCom fois
• Liste des produits commandés par client et par
commande
5. Ajout de méthode : modifier le TAD client afin d’ajouter les
méthodes suivantes :
• Nombre de commande d’un client donné
• Calcul du montant de la commande d’un client donné

INP-HB/K. M. BROU BD avancées : SGBD Oracle OR 112

112

56
 VII. Les méthodes
 Schéma navigationnel à utiliser  Extensions des tables

Commande Client numNom nom

1 Toto
Client numCom 2 Fatou
estPassee
dateCom 3 Mankou
numCl contient
nom LigneCom
qteCom
Commande {contient}
numCom dateCom @estPassee qteCom refProd
refProd 1 12/12/01 16
2 1/1/02
Produit 8 31/1/08 12
32
numProd
designation
prixU Produit numProd designation prixU qteStock
qteStock 1 vis 100 80
2 boulon 150 65
 Remarque 3 pointe 420 125
4 lime 345 180
 La boucle FOR de type curseur
ne fonctionne pas avec la nested
table.
 Utiliser OPEN FETCH CLOSE
INP-HB/K. M. BROU BD avancées : SGBD Oracle OR 113

113

VII. Les méthodes

 7.3. Méthode MEMBER
PROCEDURE CREATE TABLE Client OF clientType
(CONSTRAINT client_numCl_PK PRIMARY KEY
 Création du TAD et des tables (numCl));
 clientMethodeP.sql
CREATE TABLE commande OF commandeType
DROP TABLE Commande; (CONSTRAINT commande_numCom_PK PRIMARY KEY
DROP TABLE Client; (numCom));
DROP TYPE clientType FORCE;

CREATE OR REPLACE TYPE clientType AS OBJECT

(numCl NUMBER,
nom VARCHAR2(20),
adr adresseType,
MEMBER FUNCTION nbCommande RETURN NUMBER,
MEMBER PROCEDURE modifDateCom(numC
NUMBER, nouvDate IN DATE)
);

INP-HB/K. M. BROU BD avancées : SGBD Oracle OR 114

114

57
 VII. Les méthodes
 Création du corps de la
procédure MEMBER PROCEDURE modifDateCom (numC NUMBER,
nouvDate IN DATE) IS
CREATE OR REPLACE TYPE BODY clientType AS nbCom NUMBER;
MEMBER FUNCTION nbCommande RETURN NUMBER IS BEGIN
nbCom NUMBER; UPDATE Commande c
BEGIN SET c.dateCom = nouvDate
SELECT COUNT(co.numCom) INTO nbCom WHERE co.numCom = numC AND
FROM Client cl, commande co c.estPassee.numCl=SELF.numCl;
WHERE cl.numCl = co.estPassee.numCl AND END modifDateCom;
cl.numCl=SELF.numCl; END;
RETURN nbCom; /
END nbCommande;

INP-HB/K. M. BROU BD avancées : SGBD Oracle OR 115

115

VII. Les méthodes

 Insertion des données

INSERT INTO Commande VALUES(2, '13/12/06', NULL); UPDATE Commande co

SET co.estPassee =
INSERT INTO Commande(numCom,dateCom)
(SELECT REF(cl)
VALUES(3, '12/12/06');
FROM Client cl
INSERT INTO Commande WHERE cl.numCl=2
SELECT 1, '12/12/06', REF(a) )
FROM Client a WHERE co.numCom=2;
WHERE a.numCl=5;
SELECT numCom, dateCom,
UPDATE Commande co co.estPassee.numCl,
SET co.estPassee = co.estPassee.nom
(SELECT REF(cl) FROM Commande co
FROM Client cl /
WHERE cl.numCl=5
)
WHERE co.numCom=3;

INP-HB/K. M. BROU BD avancées : SGBD Oracle OR 116

116

58
 VII. Les méthodes
 Appel d’une procédure :
 Fonction VALUE() : renvoie la
valeur d’un TAD
SELECT numCom, dateCom, co.estPassee.numCl, SELECT numCom, dateCom,
co.estPassee.nom co.estPassee.numCl,
FROM Commande co co.estPassee.nom
/ FROM Commande co
Rem modification de la dateCom de la commande 1 du /
client de numéro 2
DECLARE Remarque :
comModifDate clientType; On ne peut pas appeler une
BEGIN procédure dans une requête
SELECT VALUE(c) INTO comModifDate
FROM Client c Surcharge possible des
WHERE c.numCl = 2; méthodes
comModifDate.modifDateCom(1, '31/12/06');
END;
/

INP-HB/K. M. BROU BD avancées : SGBD Oracle OR 117

117

VII. Les méthodes

 Suppression de procédure

ALTER TYPE clientType

DROP MEMBER PROCEDURE modifDateCom(numC
NUMBER, nouvDate IN DATE) CASCADE

INP-HB/K. M. BROU BD avancées : SGBD Oracle OR 118

118

59
 VII. Les méthodes
 7.4. Méthode STATIC  Toute action de la méthode
s’applique à toutes les instances
 Static
 Exemple :
 Indique que la méthode doit être
exécutée comme méthode de  Une méthode qui affecte la
classe et non appliquée à un même date de commande à
objet. tous les commande.
 chaque instance ne nécessite pas
sa propre implémentation de la
méthode.
 Contrairement aux méthodes
normales, les méthodes static ne
dépendent pas des instances de
leur classe.
 Pas possible d’utiliser le
paramètre SELF

INP-HB/K. M. BROU BD avancées : SGBD Oracle OR 119

119

VII. Les méthodes

 Déclaration  Corps de la méthode
 Syntaxe
CREATE OR REPLACE TYPE BODY commandeType AS
 STATIC PROCEDURE | STATIC PROCEDURE imposeDateCom(nouvDate IN DATE) IS
FUNCTION nbCom NUMBER;
nomMethode(parametre) BEGIN
UPDATE Commande c
 comMethodeS.sql SET c.dateCom = nouvDate;
END imposeDateCom;
ALTER TYPE commandeType END;
ADD STATIC PROCEDURE /
imposeDateCom(nouvDate IN DATE) CASCADE;

INP-HB/K. M. BROU BD avancées : SGBD Oracle OR 120

120

60
 VII. Les méthodes
 Appel de la méthode  Avant appel
 Syntaxe
 nomDuType.nomMethode(par
ametre);

DECLARE
BEGIN
commandeType.imposeDateCom('31/04/2011');
END;
/  Après appel

INP-HB/K. M. BROU BD avancées : SGBD Oracle OR 121

121

VII. Les méthodes

 7.5. Méthode CONSTRUCTOR CREATE OR REPLACE TYPE clientType AS OBJECT
(numCl NUMBER,
 Permet la création d’instances nom VARCHAR2(20),
adr adresseType,
d’objets non persistants MEMBER FUNCTION nbCommande RETURN NUMBER,
 Pas stockée dans une table MEMBER PROCEDURE modifDateCom(nouvDate IN DATE),
CONSTRUCTOR FUNCTION clientType RETURN SELF AS
 Utilisée dans un programme RESULT,
 Déclaration CONSTRUCTOR FUNCTION clientType (m IN NUMBER, n IN
VARCHAR2) RETURN SELF AS RESULT
 clientMethodeC.sql )
/

INP-HB/K. M. BROU BD avancées : SGBD Oracle OR 122

122

61
 VII. Les méthodes
 Corps des méthodes
CREATE OR REPLACE TYPE BODY clientType AS
…
CONSTRUCTOR FUNCTION clientType RETURN SELF AS RESULT IS
BEGIN
SELF.numCl := 0;
SELF.nom := '';
SELF.adr := adresseType(12,'rue SDF', 'Dakar','134');
RETURN;
END;

CONSTRUCTOR FUNCTION clientType(m IN NUMBER, n IN VARCHAR2)

RETURN SELF AS RESULT IS
BEGIN
SELF.numCl := m;
SELF.nom := n;
SELF.adr := adresseType(22,'rue Tranquile', 'Dakar','222');
RETURN;
END;
END;
/
INP-HB/K. M. BROU BD avancées : SGBD Oracle OR 123

123

VII. Les méthodes

 Appel d’un constructeur

DECLARE
newcl1 clientType;
newcl2 clientType;
newcl3 clientType;
BEGIN
newcl1 := new clientType();
newcl2 := new clientType(10,'Papinou');
newcl3 := new clientType(13,'Froto',adresseType(12,'rue SDF', 'Dakar','134'));
DBMS_OUTPUT.PUT_LINE(newcl1.numCl||' '||newcl1.nom|| ' '||newcl1.adr.ville);
DBMS_OUTPUT.PUT_LINE(newcl2.numCl||' '||newcl2.nom|| ' '||newcl2.adr.ville);
DBMS_OUTPUT.PUT_LINE(newcl3.numCl||' '||newcl3.nom|| ' '||newcl3.adr.ville);
END;
/

INP-HB/K. M. BROU BD avancées : SGBD Oracle OR 124

124

62
 VII. Les méthodes
 Remarque CREATE OR REPLACE TYPE clientType AS OBJECT
 L’appel du constructeur ayant le (numCl NUMBER,
même nombre d’argument que le nom VARCHAR2(20),
adr adresseType,
TAD génère une erreur. MEMBER FUNCTION nbCommande RETURN NUMBER,
 Par défaut un tel constructeur MEMBER PROCEDURE modifDateCom(nouvDate IN DATE),
CONSTRUCTOR FUNCTION clientType RETURN SELF AS
existe RESULT,
CONSTRUCTOR FUNCTION clientType (m IN NUMBER, n IN
VARCHAR2) RETURN SELF AS RESULT
CONSTRUCTOR FUNCTION clientType (m IN NUMBER, n
IN VARCHAR2, a adresseType) RETURN SELF AS
RESULT
)

INP-HB/K. M. BROU BD avancées : SGBD Oracle OR 125

125

VII. Les méthodes

 7.6. Surcharge  Déclaration
 Tout comme les constructeurs,  clientMethodeS.sql
les méthodes MEMBER et STATIC CREATE OR REPLACE TYPE clientType AS OBJECT
(numCl NUMBER,
peuvent être surchargées. nom VARCHAR2(20),
 Exemple : deux méthodes affiche() adr adresseType,
MEMBER FUNCTION nbCommande RETURN NUMBER,
 Affiche les informations sur les MEMBER PROCEDURE modifDateCom(nouvDate IN DATE),
STATIC PROCEDURE affiche,
clients (numCl, nom) et sur leurs
STATIC PROCEDURE affiche(dCom IN DATE),
commandes (numCom et CONSTRUCTOR FUNCTION clientType RETURN SELF AS RESULT,
dateCom) CONSTRUCTOR FUNCTION clientType (m IN NUMBER, n IN
VARCHAR2) RETURN SELF AS RESULT
 Affiche les informations sur les );
clients (numCl, nom) et sur leurs
commandes passées à une date
donnée (numCom et dateCom)

INP-HB/K. M. BROU BD avancées : SGBD Oracle OR 126

126

63
 VII. Les méthodes
 Corps des méthodes
STATIC PROCEDURE affiche(dCom IN DATE) IS
CREATE OR REPLACE TYPE BODY clientType AS CURSOR curCl IS
… SELECT numCom, dateCom, co.estPassee.numCl
STATIC PROCEDURE affiche IS AS numCl, co.estPassee.nom AS nom
CURSOR curCl IS FROM Commande co
SELECT numCom, dateCom, co.estPassee.numCl WHERE dateCom = dCom;
AS numCl, co.estPassee.nom AS nom BEGIN
FROM Commande co; FOR rCl IN curCl
BEGIN LOOP
FOR rCl IN curCl DBMS_OUTPUT.PUT_LINE(rCl.numCl||'
LOOP '||rCl.nom||' '||rCl.numCom||' '||rCl.dateCom) ;
DBMS_OUTPUT.PUT_LINE(rCl.numCl||' END LOOP ;
'||rCl.nom||' '||rCl.numCom||' '||rCl.dateCom) ; END affiche;
END LOOP ; END;
END affiche; /

INP-HB/K. M. BROU BD avancées : SGBD Oracle OR 127

127

VII. Les méthodes

 Appel des méthodes

DECLARE
BEGIN
clientType.affiche;
END;
/

DECLARE
BEGIN

clientType.affiche('12/12/2006');
END;
/

INP-HB/K. M. BROU BD avancées : SGBD Oracle OR 128

128

64
 VII. Les méthodes
 7.7. Redéfinition de méthodesCREATE OR REPLACE TYPE personneType AS OBJECT
(numP NUMBER,
 Surcharge.sql nom VARCHAR2(20),
Personne adr adresseType,
numP STATIC PROCEDURE affiche
nom ) NOT FINAL;
adresse CREATE OR REPLACE TYPE clientType UNDER personneType
affiche() (compte NUMBER,
STATIC PROCEDURE affiche
) NOT FINAL;
CREATE OR REPLACE TYPE clientSpecialType UNDER clientType
Client (remise NUMBER);
compte
affiche() CREATE TABLE Personne OF personneType
(CONSTRAINT personne_numP_PK PRIMARY KEY (numP));
CREATE TABLE Client OF clientType
(CONSTRAINT client_numP_PK PRIMARY KEY (numP));
CREATE TABLE ClientSpecial OF clientSpecialType
ClientSecial
(CONSTRAINT clientSpec_numP_PK PRIMARY KEY (numP))
remise
/

INP-HB/K. M. BROU BD avancées : SGBD Oracle OR 129

129

VII. Les méthodes

 Corps de méthodes
CREATE OR REPLACE TYPE BODY clientType AS
CREATE OR REPLACE TYPE BODY personneType AS STATIC PROCEDURE affiche IS
STATIC PROCEDURE affiche IS CURSOR curP IS
CURSOR curP IS SELECT cl.numP, cl.nom, cl.adr.norue AS norue,
SELECT p.numP, p.nom, p.adr.norue AS norue, cl.adr.rue AS rue, cl.compte
p.adr.rue AS rue FROM Client cl;
FROM Personne p; BEGIN
BEGIN FOR rC IN curP
FOR rP IN curP LOOP
LOOP DBMS_OUTPUT.PUT_LINE(rC.numP||' '||rC.nom||'
DBMS_OUTPUT.PUT_LINE(rP.numP||' '||rP.nom||' '||rC.norue||' '||rC.rue||' '||rC.compte);
'||rP.norue||' '||rP.rue); END LOOP;
END LOOP; END affiche;
END affiche; END;
END; /
/

INP-HB/K. M. BROU BD avancées : SGBD Oracle OR 130

130

65
 VII. Les méthodes
 Appel
Requête Commentaire
DECLARE
BEGIN
personneType.affiche;
END;
/

DECLARE
BEGIN
clientType.affiche;
END;
/

DECLARE
BEGIN
clientSpecialType.affiche;
END;
/

Méthode affiche non redéfinie

INP-HB/K. M. BROU BD avancées : SGBD Oracle OR 131

131

VII. Les méthodes

 TD
 TD11 : Méthodes
 TD12 : Méthodes

INP-HB/K. M. BROU BD avancées : SGBD Oracle OR 132

132

66
 VIII. Vues et Triggers
 8.1. Création de Vue objet-  Principe :
relationnelle  Définir un type de données
(CREATE TYPE…) qui formera la
 Créée à partir structure de la vue ;
 d’une ou plusieurs tables objet-  Écrire la requête (AS SELECT…)
relationnelles ; qui décrit les objets de la vue ;
 d’une ou plusieurs tables  Spécifier un identifiant (OID)
relationnelles ; basé sur un attribut de la vue
 d’une ou plusieurs vues objet- pour pouvoir référencer chaque
relationnelles. objet de la vue (REFs). Se servir
de préférence d’une clé primaire ;
 Programmer des déclencheurs
INSTEAD OF (optionnel) si la vue
doit être modifiée.

INP-HB/K. M. BROU BD avancées : SGBD Oracle OR 133

133

VIII. Vues et Triggers

 Vue simple
INSERT INTO Client VALUES (1,'Toto',21,'rue du
 Vue mono-table alimentée par commerce','Abidjan','1234','Côte d''Ivoire’);
une table relationnelle.
INSERT INTO Client VALUES (2,'Fatou',7,'rue
 Client d’Abidjan (vue.sql) kakredou','Abidjan','34','Côte d''Ivoire’);
 Table source
INSERT INTO Client VALUES (3,'Froto',52,'rue SDF','Saint-
CREATE TABLE Client Louis','756','Sénégal’);
(numCl NUMBER, nom VARCHAR2(20),
norue NUMBER, rue VARCHAR2(15), INSERT INTO Client VALUES (4,'Pape',90,'rue
ville VARCHAR2(15), codePostal NUMBER, kakredou','Dakar','999','Sénégal');
pays VARCHAR2(15), COMMIT
CONSTRAINT client_numCl_PK PRIMARY KEY (numCl) /
) SELECT cl.numCl,cl.nom,cl.rue,cl.ville FROM Client cl
/ /

INP-HB/K. M. BROU BD avancées : SGBD Oracle OR 134

134

67
 VIII. Vues et Triggers
 Création de la vue
CREATE OR REPLACE TYPE adresseType AS OBJECT
(norue NUMBER, rue VARCHAR2(15),
ville VARCHAR2(15), codePostal VARCHAR2(10)
)
/
CREATE OR REPLACE TYPE clientAbjType AS OBJECT
(numCl NUMBER, nom VARCHAR2(20),
adresse adresseType,  Clause WITH OBJECT IDENTIFIER
pays VARCHAR2(15)  Permet d’attribuer à chaque
)
/
objet de la vue a un OID
CREATE OR REPLACE VIEW vueClientAbj OF ClientAbjType SELECT REF(cl) FROM vueClientAbj cl;
WITH OBJECT IDENTIFIER (numCl)
AS SELECT cl.numCl, cl.nom,
adresseType(cl.norue,cl.rue,cl.ville,cl.codePostal),cl.pays
FROM Client cl
WHERE cl.ville='Abidjan'
/
SELECT cl.numCl, cl.nom, cl.adresse.rue, cl.adresse.ville
FROM vueClientAbj cl
/
INP-HB/K. M. BROU BD avancées : SGBD Oracle OR 135

135

VIII. Vues et Triggers

 8.2. Modification d’une vue  Restrictions imposées par Oracle
 Certaines vues ne peuvent être sur la requête de la vue afin que
mise à jour par les instructions : celle-ci soit modifiable :
INSERT, UPDATE, DELETE  pas d’opérateurs ensemblistes ;
 pas de fonction d’agrégation ;
 Modification de la table source.
 pas de clause group by ou order
 Possible si correspondance
by ;
biunivoque entre les lignes de la
vue et celle de la table source.  pas de sous-requête ;
 pas de collection dans un select
(objet-relationnel).
 Si la vue comporte une jointure,
les instructions DML sont très
restreintes et ne peuvent
concerner qu’une seule table de
base.

INP-HB/K. M. BROU BD avancées : SGBD Oracle OR 136

136

68
 VIII. Vues et Triggers
 Exemple
 La vue vueClientAbj est
modifiable
INSERT INTO vueClientAbjVALUES
(5, 'Baba', adresseType(21,'rue SDF', 'Saint-Louis', '1234'));
SELECT cl.numCl, cl.nom, cl.adr.norue, cl.adr.rue FROM Client cl;

INP-HB/K. M. BROU BD avancées : SGBD Oracle OR 137

137

VIII. Vues et Triggers

 8.3. Trigger INSTEAD OF  Les triggers instead of sont des
 Offre à l'utilisateur un triggers ligne
contournement à l’impossibilité  i.e. lors d’unemise à jour ils
disposent du contenu d’origine du
de mise à jour des données d'une
nuplet courant de la vue (old) et
vue. du nouveau contenu de ce nuplet
 L'utilisateur peut effectuer ainsi (new) mais celui-ci n’est pas
une requête INSERT ou UPDATE modifiable par le trigger.
ou DELETE sur un objet de type
 Pas de before et after dans les
vue.
triggers instead of.
 Cette mise à jour se répercute sur
les tables sources

INP-HB/K. M. BROU BD avancées : SGBD Oracle OR 138

138

69
 VIII. Vues et Triggers
 Exemple
 Imposer que lors d’une
insertion dans la vue, la ville
soit Yakro.
Rem ne marche
CREATE OR REPLACE TRIGGER trigVueClientYakro
INSTEAD OF INSERT ON vueClientAbj FOR EACH
ROW
BEGIN
INSERT INTO Client
VALUES(:new.numCl,:new.nom,:new.adresse.norue,:
new.adresse.rue,'Yakro',:new.adresse.codePostal,:ne
w.pays)
END;
/

INP-HB/K. M. BROU BD avancées : SGBD Oracle OR 139

139

Bibliographie
 Livres :  "Cours d’introduction Modèles
 "Objet-relationnel sous Oracle8, d’interactions pour le client
Modélisation avec UML", serveur et exemples
Christian Soutou, Eyrolles. d’architectures les implantant",
Gérard Florin, Conservatoire
 " Les bases de données Oracle National des Arts et Métiers,
8i Développement Administration Laboratoire CEDRIC
Optimisation ", Roger Chapuis,
DUNOD  "Oracle 10g sous Windows",
Giles Briard, Edition Eyrolles
 " Client-Serveur, moteur SQL, 2006
middleware et architectures
parallèles ", Serge MIRANDA et  "Programmation Objet avec
Anne RUOLS, Editions Eyrolles. Oracle, Techniques et pratiques",
2e édition, Christian SOUTOU,
 "Oracle 11g Administration", Edition Vuibert.
Olivier HEURTEL, Edition ENI.

INP-HB/K. M. BROU BD avancées : SGBD Oracle OR 140

140

70
 Bibliographie
 Supports Oracle Formation :
 Programmer avec PL/SQL
 Le langage SQL & l’outil SQL*Plus

INP-HB/K. M. BROU BD avancées : SGBD Oracle OR 141

141

Bibliographie
 Webographie :  UML
 http://lifc.univ-  http://www.epi.asso.fr/revue/ar
fcomte.fr/~lasalle/OracleV7/pag ticles/a0509b.htm
e1.htm de Marie-France Lasalle :
[email protected]
 http://www.hds.utc.fr/~crozatst
/ftp/nf17/7.sgbdro.pdf#search=
%22h%C3%A9ritage%2BSQL3
%22
 http://www2.lifl.fr/~durif/bdd/
coursBD/html/developpement.ht
ml
 "Les aspects objet-relationnels,
d’Oracle (de la V8 à la 11g)",
Christian Soutou,
http://www.soutou.net/christian

INP-HB/K. M. BROU BD avancées : SGBD Oracle OR 142

142

71