Institut Supérieur d’Informatique - Université Tunis El Manar
Module 3 : Les Bases
de Données Objet-
relationnelle (Section 2
: Les collections)
Riadh ZAAFRANI
Octobre 2020
1ère année MP2L
1
Objectifs du cours
◼ A la fin de cours, vous serez en mesure de :
➢ Créer des types de collections en SQL3
➢ Stocker des collections en SQL3
➢ Interroger et mettre à jour une collection en
SQL3
➢ Créer des collections multiniveeaux en SQL3
2
1
1
2
Plan
◼Types et stockage de collections
◼Mise à jour d’une collection
◼Interrogation d’une collection
◼Collections multiniveaux
3
Tables imbriquées
▪ Définition : collection non-ordonnée et
non limitée en taille.
▪ Stockage : dans une table séparée
(peuvent donc être manipulées par SQL)
▪ Création d'un type TABLE
▪ Ex. CREATE TYPE TAB_COURS AS
TABLE OF COURS_t
/
4
3
4
2
Tableaux prédimensionnés ou associatifs
▪ Définition : collection ordonnée et
limitée en taille
▪ Stockage : dans un attribut BLOB de
la table
▪ Création d'un type VARRAY
▪ Ex. CREATE TYPE TAB_DIPLOMES AS
VARRAY(10) OF VARCHAR(20)
/
5
5
Stockage : Table relationnelle
Advertisement
▪ Exemple
▪ CREATE TABLE les_enseignants (
IdPers NUMBER(5),
EtCiV ETAT_CIVIL_t,
Adr ADRESSE_t,
Statut VARCHAR(20),
ListeCours TAB_COURS,
CONSTRAINT pk_ens PRIMARY
KEY(ldPers))
NESTED TABLE ListeCours
STORE AS table_imbriquee_cours;
6
6
3
Stockage : Table objet
▪ Type
▪ Ex. CREATE TYPE ENSEIGNANT_t AS OBJECT (
IdPers NUMBER(5), EtCiV ETAT_CIVIL_t,
Adr ADRESSE_t, Statut VARCHAR(20),
ListeCours TAB_COURS)
/
▪ Table
▪ Ex. CREATE TABLE les_enseignants OF ENSEIGNANT_t
(CONSTRAINT pk_ens PRIMARY KEY(ldPers))
NESTED TABLE ListeCours
STORE AS table_imbriquee_cours;
7
7
Stockage : Table objet
◼ 2 containeurs d’objets à créer :
➢ un pour les objets de la table de 1er niveau
➢ un 2e pour les objets de la table imbriquée
◼ Création de la table typée Les_Enseignants contenant la table imbriquée:
◼ CREATE TABLE Les_Enseignants of enseignant_t(
CONSTRAINT pk_ens PRIMARY KEY(ldPers))
NESTED TABLE ListeCours STORE AS Table_imbriquee_cours;
◼ La clause STORE AS précise le nom de la nouvelle table physique externe, soit
Table_imbriquee_cours utilisée pour ranger les éléments de l'ensemble de type
TAB_COURS.
◼ Cette table «Table_imbriquee_cours» n'est pas accessible directement par le DML
: Update, Delete et Insert, sauf pour le Select.
◼ Attention: le nom de cette sous-table n’est pas nécessairement diffusé auprès des
développeurs et donc ne sera pas utilisé dans la mise à jour de la sous-table.
8
8
4
Plan
◼Types et stockage de collections
◼Mise à jour d’une collection
◼Interrogation d’une collection
◼Collections multiniveaux
Advertisement
9
9
Mises à jour
▪ Ajout d'un élément parent
▪ insérer au même temps une ligne dans la table
maître et plusieurs lignes dans la table
imbriquée
▪ Ex. INSERT INTO les_enseignants VALUES (1000,
ETAT_CIVIL_t('Ben Abdallah', 'Salem', '15/10/1982'),
ADRESSE_t('5 av. Madrid', '1000', 'Tunis'), 'MA',
TAB_COURS(COURS_t(1, 'BD'),
COURS_t(2, Web')));
10
10
5
Mises à jour
▪ Ajout dans la collection
▪ Problème : ajouter une ligne à une table imbriquée
associée à une ligne de la table maître
▪ Préalable : sélectionner la ligne (et la colonne) de
▪
la table maître concernée par une requête
INSERT INTO TABLE (requête) VALUES(…);
▪ Ex. lNSERT INTO TABLE
(SELECT ListeCours FROM les_enseignants
WHERE IdPers = 1000)
VALUES (COURS_t(3, 'UNIX'));
11
11
Modification
▪ Problème : identique à celle de l'ajout
▪ Instruction UPDATE adaptée
▪
UPDATE TABLE (requête) SET … WHERE …;
▪ Ex.
UPDATE TABLE
(SELECT ListeCours FROM les_enseignants
WHERE IdPers = 1000) t
SET t.Libelle = 'WEB'
WHERE t.Libelle = 'Web';
▪ Modification d'éléments
▪ Ex. UPDATE TABLE
(SELECT ListeCours FROM les_enseignants
WHERE IdPers = 1000) t
SET VALUE(t) = COURS_t(10, 'Bases de données‘)
WHERE t.ldCours = 1;
12
12
6
Mises à jour
▪ Suppression
Advertisement
▪ Ex. DELETE FROM TABLE
(SELECT ListeCours FROM les_enseignants
WHERE IdPers = 1000) t
WHERE t.ldCours = 10;
▪ Note : La clause TABLE n'est pas utilisable
avec les tableaux VARRAY, qui ne peuvent
être directement modifiés qu'avec PL/SQL.
13
14
Plan
◼Types et stockage de collections
◼Mise à jour d’une collection
◼Interrogation d’une collection
◼Collections multiniveaux
13
14
7
Interrogation
▪ Requêtes de dèsemboîtement (pseudojointure)
▪ - Ex. SELECT e.EtCiv.Nom, e.EtCiv.Prenom,
e.Statut, c.Libelle
FROM les_enseignants e,
TABLE(e.ListeCours) c;
▪ Extraction d'une collection
▪ - Ex. SELECT c.Libelle FROM TABLE
(SELECT ListeCours
FROM les_enseignants
WHERE IdPers = 1000) c;
15
15
Interrogation
▪ Curseur imbriqué
▪ - Ex. SELECT e.EtCiv.Nom, e.EtCiv.Prenom,
CURSOR(SELECT c.Libelle
FROM TABLE (ListeCours) c)
FROM les_enseignants e;
▪ Liste nom, prénom, puis tous les cours de
cet enseignant, puis enseignant suivant, etc.
▪ Résultat non assimilable dans une table.
16
16
8
Collection de références
▪ Types (2 étapes)
▪ - Ex. CREATE TYPE REF_COURS_t AS
OBJECT(RefCours REF COURS)
/
▪ - Ex. CREATE TYPE TAB_COURS AS
TABLE OF REF_COURS_t
/
▪ Table : pas de changement
Advertisement
17
18
Plan
◼Types et stockage de collections
◼Mise à jour d’une collection
◼Interrogation d’une collection
◼Collections multiniveaux
17
18
9
Collections multiniveaux
▪ Exemple de création des types
▪ CREATE TYPE TAB_NOTES AS
TABLE OF NUMBER(4,1)
/
▪ CREATE TYPE EPREUVE_t AS
OBJECT(RefCours REF COURS2, Notes TAB_NOTES)
/
▪ CREATE TYPE TAB_EPREUVES AS TABLE OF EPREUVE_t
/
▪ CREATE TYPE ETUDIANT_t AS
OBJECT (ldPers NUMBER(5), EtCIV ETAT_CIVIL_t,
Adr ADRESSE_t, Resultats TAB_EPREUVES)
/
19
Collections multiniveaux
▪ Exemple de création de table
▪ CREATE TABLE les_etudiants OF ETUDIANT_t
(CONSTRAINT pk_etu PRIMARY KEY(ldPers))
NESTED TABLE Resultats STORE AS ti1_res
(NESTED TABLE Notes STORE AS ti2_notes);
19
20
20
10
Institut Supérieur d’Informatique - Université Tunis El Manar
Module 3 : Les Bases
de Données Objet-
relationnelle (Section 2
: Les collections)
Riadh ZAAFRANI
Octobre 2020
1ère année MP2L
Merci pour votre attention
21
21
11