ANNEXE TD et TP Syntaxe Oracle 9i

Université Paul Sabatier – Master 1 Informatique Module Bases de données orientées objet
TD/TP Bases de données objet-relationnelles 1 Isabelle Ferrané
Bases de Données orientées Objet
ANNEXE TD et TP
Syntaxe Oracle 9i
Université Paul Sabatier – Master 1 Informatique Module Bases de données orientées objet
TD/TP Bases de données objet-relationnelles 2 Isabelle Ferrané
I- Type d’objets............................................................................................................................... 3
1- Création d’un type d’objets ............................................................................................ 3
2- Suppression d’un type d’objets .................................................................................... 3
3- Modification d’un type d’objets : deux requêtes possibles........................... 3
II- Table objet-relationnelle..................................................................................................... 4
1- Création d’une table objet-relationnelle.................................................................. 4
2- Suppression d’une table objet-relationnelle ......................................................... 5
3- Modification de la définition d’une table objet-relationnelle ....................... 5
4- Ajout d’une instance dans une table objet-relationnelle : INSERT........... 5
5- Mise à jour d’une table objet-relationnelle : UPDATE / DELETE ................. 6
6- Consultation d’ une table objet-relationnelle : SELECT.................................. 6
III- Méthode : définition et activation. ............................................................................... 7
1- Spécification d’une méthode de type MEMBER.................................................. 7
2- Corps d’une méthode et corps du type................................................................... 8
3- Suppression du corps du type ...................................................................................... 8
4- Activation d’une Méthode. .............................................................................................. 9
IV- Collections de données ...................................................................................................... 9
1- Collections de type tableau : VARRAY ................................................................... 9
2- Collection de type table ................................................................................................. 10
3- Manipulation des collections en PL/SQL............................................................... 10
V- Procédures PL/SQL pour la manipulation d’objets .............................................. 11
1- Bloc PL/SQL anonyme ..................................................................................................... 11
2- Manipulation le résultat d’une requête dans un bloc PL/SQL.................... 12
3- Affichage de résultats dans un bloc PL/SQL....................................................... 13
VI- Table Objet-relationnelle : Complément ................................................................. 14
1) Portée d’une référence................................................................................................... 14
2) Table imbriquée.................................................................................................................. 14
3) Manipulation d’une table imbriquée ........................................................................ 15
4) Mise à jour dans une table imbriquée désignée par THE............................ 15
5) Consultation d’une table imbriquée. ....................................................................... 16
VII- Héritage sous Oracle 9i : Types et sous-types .................................................. 17
1) Types « spécialisables » et « non spécialisables » ....................................... 17
2) Types et méthodes non instanciables ................................................................... 17
3) Définition d’un sous-type ............................................................................................... 18
4) Surcharge d’une méthode héritée............................................................................ 18
VIII- Vues Objet-relationnelles............................................................................................. 19
1) Définition d’une vue classique (rappel) ................................................................. 19
2) Définition d’une vue objet-relationnelle ................................................................ 19
IX- Références bibliographiques......................................................................................... 21

Université Paul Sabatier – Master 1 Informatique Module Bases de données orientées objet
TD/TP Bases de données objet-relationnelles 3 Isabelle Ferrané
I- Type d’objets
1
11
1-
--
- Création
Création Création
Création d’un type d’objets
d’un type d’objetsd’un type d’objets
d’un type d’objets
CREATE TYPE
nom_type
AS
OBJECT
( att
1
type_att
1
,…, att
n
type_att
n
[ , spécification_méthode
1
, … , spécification_méthode
m
] )
avec att
i
: nom de l’attribut
type_att
i
: type de l’attribut (= type de base ou type d’objets déjà créé)
spécification_méthode
j
: spécification ou signature de la méthode
2
22
2-
--
- Suppression d’un
Suppression d’un Suppression d’un
Suppression d’un type d’objets
type d’objetstype d’objets
type d’objets
DROP TYPE
nom_type
[FORCE]
Lorsqu’il s’agit d’un type incomplet par exemple, il est nécessaire d’utiliser l’option FORCE
pour forcer la suppression.
3
33
3-
--
- Modification d’un type d’objets
Modification d’un type d’objets Modification d’un type d’objets
Modification d’un type d’objets : deux requêtes possibles
: deux requêtes possibles: deux requêtes possibles
: deux requêtes possibles
A- Modification des attributs et des méthodes initialement définies
CREATE OR REPLACE TYPE
nom_type
AS
OBJECT
(att
1
type_att
1
,
…,
att
n
type_att
n
[, specification_methode
1
, …
specification_methode
m
]
)
Université Paul Sabatier – Master 1 Informatique Module Bases de données orientées objet
TD/TP Bases de données objet-relationnelles 4 Isabelle Ferrané
B- Ajout d’une nouvelle méthode
ALTER TYPE
nom_type
REPLACE AS
OBJECT
(att
1
type_att
1
,
…,
att
n
type_att
n
,
specification_methode
1
,
…,
specification_methode
m,
specification_nouvelle_methode
m+1 ,
[ …,
specification_nouvelle_methode
m+j
]
)
Recopie des définitions des attributs et des méthodes déjà définies
Spécifications des nouvelles méthodes à ajouter
II- Table objet-relationnelle.
1
11
1-
--
- Création d’une table objet
Création d’une table objet Création d’une table objet
Création d’une table objet-
--
-relationnelle
relationnellerelationnelle
relationnelle
CREATE TABLE
nom_table
OF
nom_type
[ ( spécification_contrainte
1
[, …, spécification_contrainte
n
] ) ]
avec nom_type : nom du type d’objets préalablement défini
spécification_contrainte
i
: spécification de la contrainte liée à la table ou à un attribut
particulier. Celle-ci est de la forme : constraint nom_contrainte type_contrainte
et avec type_contrainte :
PRIMARY KEY (
liste attributs clé primaire
)
FOREIGN KEY (liste attributs clé étrangère)
REFERENCES table_ref (liste attributs clé primaire)
CHECK (condition à vérifier)
attribut NOT NULL
…
Seules Les instances d’objets répertoriées dans une table relationnelle-objet disposent d’un
OID.

Université Paul Sabatier – Master 1 Informatique Module Bases de données orientées objet
TD/TP Bases de données objet-relationnelles 5 Isabelle Ferrané
2
22
2-
--
- Suppression d’une table objet
Suppression d’une table objet Suppression d’une table objet
Suppression d’une table objet-
--
-relationnelle
relationnellerelationnelle
relationnelle
DROP TABLE
nom_table
3
33
3-
--
- Modification de la définition d’une table objet
Modification de la définition d’une table objet Modification de la définition d’une table objet
Modification de la définition d’une table objet-
--
-relationnelle
relationnellerelationnelle
relationnelle
ALTER TABLE ne peut s’appliquer qu’ à une table relationnelle. Modifier la définition d’une
table relationnelle-objet revient à modifier le type d’objets sur lequel elle est définie (CREATE
OR REPLACE TYPE).
4
44
4-
--
- Ajout d’une instance dans
Ajout d’une instance dans Ajout d’une instance dans
Ajout d’une instance dans u
uu
une table objet
ne table objetne table objet
ne table objet-
--
-relationnelle
relationnellerelationnelle
relationnelle : INSERT
: INSERT : INSERT
: INSERT
A- Insertion d’un nouveau tuple
INSERT INTO
nom_table
[( sous_liste_attributs ) ]
VALUES
( val
1
, …, val
k
)
avec val
i
: valeur simple, NULL, ou bien nouvelle instance d’un type d’objet générée par
l’appel au consructeur du type nom_type_objet(liste_valeurs_attributs_objet)
INSERT INTO
nom_table_objet_relationnelle
VALUES(
val
1
, …, val
k
)
Ou
INSERT INTO nom_table_objet_relationnelle
VALUES(
nom_type_objet(val
1
, …, val
k
) )
Si la table dans laquelle on ajoute un tuple est une table objet-relationnelle, on peut faire
aussi intervenir le constructeur associé au type d’objets dont on veut créer une instance.
Toute instance d’une table objet-relationnelle dispose d’un OID (identifiant interne ou
référence), il est donc accessible directement via cette référence.
B- Insertion de nouvelles instances d’objet à partir de données déjà existantes
avec val
i
= valeur atomique, nouvelle instance de type d’objets (utilisation du
constructeur associé) ou bien sous-requête ramenant la valeur souhaitée (référence par
exemple) :
val
i
= (select donnée from table …) *
INSERT INTO nom_table
VALUES ( val
1
, …val
N
) ;
Université Paul Sabatier – Master 1 Informatique Module Bases de données orientées objet
TD/TP Bases de données objet-relationnelles 6 Isabelle Ferrané
ou bien la totalité des valeurs figurent dans la base de données, et dans ce cas on
remplace VALUES par la sous-requête chargée de ramener toutes les valeurs.
5
55
5-
--
- Mise à jour
Mise à jour Mise à jour
Mise à jour d’une table objet
d’une table objetd’une table objet
d’une table objet-
--
-relationnelle
relationnellerelationnelle
relationnelle :
: :
: UPDATE / DELETE
UPDATE / DELETEUPDATE / DELETE
UPDATE / DELETE
Si l’attribut à modifier est un n-uplet il faut utiliser le constructeur correspondant
type_objet(…)
Si la valeur est une référence il faut aller la chercher à la’aide d’une requête imbriquée
(SELECT REF(a) FROM …)
Pour supprimer les instances d’objet répondant si il y a lieu à la condition posée
6
66
6-
--
- Consultation d’
Consultation d’Consultation d’
Consultation d’ une table objet
une table objet une table objet
une table objet-
--
-relationnelle
relationnellerelationnelle
relationnelle : SELECT
: SELECT: SELECT
: SELECT
A- Obtenir l’OID d’un objet (= sa référence REF)
SELECT REF(
alias_objet
)
FROM nom_table alias_objet
WHERE
condition_restriction_selection_objet
B- Accéder à un objet
SELECT VALUE(
alias_objet
)
FROM nom_table alias_objet
WHERE
condition_restriction_selection_objet
C- Déréférencer un objet (= obtenir sa valeur à partir de sa référence)
SELECT DEREF(REF_objet)
FROM nom_table alias_objet
WHERE condition_restriction_selection_objet
INSERT INTO nom_table
( SELECT données FROM table(s) WHERE condition(s))
UPDATE nom_table
SET nom_att = nouvelle_valeur
[ WHERE condition(s)]
DELETE FROM nom_table
[ WHERE condition(s)]

Université Paul Sabatier – Master 1 Informatique Module Bases de données orientées objet
TD/TP Bases de données objet-relationnelles 7 Isabelle Ferrané
D- Accéder à un attribut complexe de type n-uplet
Utilisation de la notation pointée pour accéder aux différents champs d’un attribut de type n-
uplet. Si les attributs sont des attributs de premier niveau, l’alias est facultatif, sinon il doit
être utilisé.
SELECT
alias_objet
.att_simple,
alias_objet.att_n_uplet.att_simple,
FROM nom_table alias_objet
WHERE
condition_restriction_selection_objet
III- Méthode : définition et activation.
Avant d’être utilisée, une méthode doit avoir été définie. La définition d’une méthode
comprend la spécification (ou signature) et le corps (code correspondant).
1
11
1-
--
- Spécification d’une méth
Spécification d’une méth Spécification d’une méth
Spécification d’une méthode de type MEMBER.
ode de type MEMBER.ode de type MEMBER.
ode de type MEMBER.
La spécification d’une méthode peut être donnée lors de la création du type (CREATE
TYPE, CREATE OR REPLACE TYPE) ou bien ultérieurement avec la commande ALTER
TYPE.
A- Spécification d’une méthode de type fonction
MEMBER FUNCTION
nom_methode
[(
paramètres_formels
)]
RETURN type_élément_retour, [ directive_associée ]
B- Spécification d’une méthode de type procédure
MEMBER PROCEDURE
nom_methode
[(
paramètres_formels
)]
,[
directive_associée
]
Afin de pouvoir appeler la méthode dans une requête SQL il est nécessaire d’indiquer
explicitement que la méthode ne modifie ni la base de données ni les paquetages (directive
PRAGMA)
C- Directive associée concernant les effets de la méthode sur la base de données ou
sur un paquetage
PRAGMA RESTRICT_REFERENCES(
nom_methode, R1[, R2]
)
avec R1 et R2 tq : R1 et R2 ∈
∈∈
∈ { WNDS , RNDS, WNPS, RNPS } et R1 ≠
≠≠
≠ R2
WNDS ( Writes No Database State ) : ne modifie pas l’état de la BD
RNDS ( Reads No Database State) : n’interroge pas la BD
Université Paul Sabatier – Master 1 Informatique Module Bases de données orientées objet
TD/TP Bases de données objet-relationnelles 8 Isabelle Ferrané
WNPS ( Writes No Package State ) : ne modifie pas les variables des paquetages
RNPS (Reads No Package State ) : ne lit pas les variables des paquetages
2
22
2-
--
- Corps d’une
Corps d’une Corps d’une
Corps d’une méthode et corps du type
méthode et corps du typeméthode et corps du type
méthode et corps du type
A- Définition des corps des méthodes associés au corps du type
CREATE [OR REPLACE ] TYPE BODY
nom_type
IS
spécification_méthode
1
IS
[Declarations_variables_curseurs_et_exceptions ]
BEGIN
Instructions_PL/SQL_ou_requêtes_SQL
[ EXCEPTION Traitement_des_erreurs ]
END nom_méthode
1
;
[spécification_méthode
2
IS
…
END nom_méthode
2
;
…
]
END
;
Dans le corps d’une méthode de type MEMBER, les données de l’objet pour lequel la
méthode est activée sont désignées par le nom de l’attribut concerné préfixé par le
paramètre implicite SELF :
SELF.
nom_attribut
Cette expression peut aussi bien apparaître dans une instruction (affectation, appel de
fonction ou de procédure, …) que dans une requête SQL.
3
33
3-
--
- Suppression du corps
Suppression du corps Suppression du corps
Suppression du corps du type
du typedu type
du type
Suppression du corps d’un type d’objets
DROP TYPE BODY
nom_type
Les spécifications des méthodes sont conservées. CREATE OR REPLACE TYPE permet de
supprimer la spécification d’une méthode.

Université Paul Sabatier – Master 1 Informatique Module Bases de données orientées objet
TD/TP Bases de données objet-relationnelles 9 Isabelle Ferrané
4
44
4-
--
- Activation d’une Méthode.
Activation d’une Méthode.Activation d’une Méthode.
Activation d’une Méthode.
A- Le constructeur d’objet
Chaque type d’objets dispose d’une méthode définie implicitement par le système et dont
le rôle est de créer une nouvelle instance de ce type d’objets. Il s’agit d’une fonction
appelée constructeur.
Construction d’une nouvelle instance du type nom_type
nom_type
(
val_att
1
, …, val_att
n
)
B- Les méthodes de types MEMBER
objet_receveur
.
nom_méthode
()
objet_receveur.nom_methode(paramètres_effectifs)
Si la méthode est définie comme une fonction, alors elle peut être appelée aussi bien
dans une instruction (affectation, appel de fonction ou de procédure, …) que dans une
requête SQL, à condition que la méthode n’ait pas d’effets sur le contenu de la base de
données (cf. directive PRAGMA).
Ce n’est pas le cas d’une méthode définie comme une procédure. Elle ne peut pas être
utilisée dans une requête SQL puisque par définition, elle ne renvoie pas de valeur.
IV- Collections de données
Une collection correspond à un ensemble d’éléments de même type. L’ordre des
éléments peut être significatif auquel cas on parle généralement de liste.
Sous Oracle, on dispose de tableaux de taille limitée ou de tables.
1
11
1-
--
- Collections de type tableau
Collections de type tableau Collections de type tableau
Collections de type tableau : VARRAY
: VARRAY: VARRAY
: VARRAY
A- Définition d’un type tableau d’éléments
CREATE TYPE
nom_type_tableau
AS VARRAY(
taille) OF nom_type_élément
avec nom_type_élément : type de base ORACLE ou bien type défini par le concepteur.
Université Paul Sabatier – Master 1 Informatique Module Bases de données orientées objet
TD/TP Bases de données objet-relationnelles 10 Isabelle Ferrané
Le type tableau ainsi défini peut être utilisé comme :
- type d’un attribut dans la définition d’un type d’objet utilisé ensuite pour définir
une table objet-relationnelle ;
- type d’une donnée (variable, paramètre ou valeur de retour d’une fonction) dans
un sous-programme PL/SQL.
Dans les deux premiers cas, chaque tuple ou instance de type disposera d’un attribut
auquel sera associé un ensemble limité d’éléments. Ces éléments seront directement
stockés dans le tuple correspondant.
B- Définition d’un type d’objet comportant un attribut de type tableau
CREATE TYPE
nom_type_objet
AS
OBJECT
( … , att_tableau nom_type_tableau, …)
Remarque : On ne peut pas créer d’index sur un attribut de type tableau.
2
22
2-
--
- Collection de type table
Collection de type table Collection de type table
Collection de type table
Une table est une collection non ordonnée d’éléments de même type. La taille d’une
telle collection n’est pas limitée.
La déclaration d’un attribut correspondant à une table passe par la définition d’un type
table.
Définition d’un type table
CREATE TYPE
nom_type_table
AS TABLE OF
nom_type_élément
Le type table ainsi créé peut être utilisé comme :
- type d’un attribut dans la définition d’une table relationnelle ;
- type d’un attribut dans la définition d’un type d’objet utilisé ensuite pour définir
une table objet-relationnelle ;
- type d’une donnée (variable, paramètre ou valeur de retour d’une fonction) dans
un sous-programme PL/SQL
Dans les deux premiers cas, cela revient à créer une table ayant un attribut de type
ensemble de n-uplets. Celui-ci correspond alors à une table imbriquée (NESTED TABLE).
3
33
3-
--
- Manipulation des collections en PL/SQL
Manipulation des collections en PL/SQL Manipulation des collections en PL/SQL
Manipulation des collections en PL/SQL
Avant d’être utilisée une variable de type collection doit être déclarée et instanciée
(utilisation du constructeur associé au type utilisé).
var_ens type_collection := type_collection() ;