Les Bases de Données Objet-relationnelle (Section 2 : Les collections)

Ce cours traite des collections dans les bases de données objet-relationnelles, un sujet clé du module sur les bases de données avancées. Il s'adresse aux étudiants de première année MP2L et couvre la création, le stockage, la mise à jour, l'interrogation et la gestion de collections multiniveaux en SQL3.

Types et stockage des collections

Les collections en SQL3 permettent de regrouper plusieurs éléments dans un même attribut. Deux principaux types de collections sont distingués :

• Tables imbriquées : ce sont des collections non ordonnées et non limitées en taille. Elles sont stockées dans une table séparée, ce qui permet de les manipuler directement avec SQL. Pour créer une table imbriquée, on définit un type TABLE. Par exemple :

CREATE TYPE TAB_COURS AS
TABLE OF COURS_t
/

• Tableaux prédimensionnés ou associatifs (VARRAY) : ce sont des collections ordonnées et limitées en taille, stockées dans un attribut BLOB de la table. La création d'un type VARRAY se fait ainsi :

CREATE TYPE TAB_DIPLOMES AS
VARRAY(10) OF VARCHAR(20)
/

Le stockage d'une collection peut se faire dans une table relationnelle classique ou dans une table objet. Par exemple, pour une table relationnelle :

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(IdPers)
)
NESTED TABLE ListeCours
STORE AS table_imbriquee_cours;

Pour une table objet, on crée d'abord un type objet, puis la table correspondante :

CREATE TYPE ENSEIGNANT_t AS OBJECT (
  IdPers NUMBER(5),
  EtCiV ETAT_CIVIL_t,
  Adr ADRESSE_t,
  Statut VARCHAR(20),
  ListeCours TAB_COURS
)
/ 

CREATE TABLE les_enseignants OF ENSEIGNANT_t
(CONSTRAINT pk_ens PRIMARY KEY(IdPers))
NESTED TABLE ListeCours
STORE AS table_imbriquee_cours;

Il faut alors créer deux conteneurs d’objets : un pour les objets de la table de premier niveau et un autre pour les objets de la table imbriquée. La clause STORE AS précise le nom de la table physique externe utilisée pour stocker les éléments de la collection. Cette table imbriquée n'est pas directement modifiable par les commandes DML classiques (Update, Delete, Insert), sauf pour les requêtes de sélection (Select). Le nom de cette sous-table est souvent caché aux développeurs et n'est pas utilisé directement pour les mises à jour.

Mise à jour d’une collection

La mise à jour des collections dans une base objet-relationnelle peut concerner l'ajout, la modification ou la suppression d'éléments dans les collections imbriquées.

Ajout d'un élément parent avec ses collections

On peut insérer simultanément une ligne dans la table principale et plusieurs lignes dans la table imbriquée. Par exemple :

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')
  )
);

Ajout dans une collection existante

Pour ajouter un élément à une table imbriquée associée à une ligne existante, il faut d'abord sélectionner la collection concernée, puis insérer l'élément :

INSERT INTO TABLE (
  SELECT ListeCours FROM les_enseignants WHERE IdPers = 1000
)
VALUES (COURS_t(3, 'UNIX'));

Modification d’éléments dans une collection

La modification suit une logique similaire à l’ajout, en utilisant une instruction UPDATE sur la table imbriquée :

UPDATE TABLE (
  SELECT ListeCours FROM les_enseignants WHERE IdPers = 1000
) t
SET t.Libelle = 'WEB'
WHERE t.Libelle = 'Web';

On peut aussi modifier un élément entier de la collection :

UPDATE TABLE (
  SELECT ListeCours FROM les_enseignants WHERE IdPers = 1000
) t
SET VALUE(t) = COURS_t(10, 'Bases de données')
WHERE t.IdCours = 1;

Suppression d’éléments dans une collection

La suppression d’un élément dans une table imbriquée se fait par :

DELETE FROM TABLE (
  SELECT ListeCours FROM les_enseignants WHERE IdPers = 1000
) t
WHERE t.IdCours = 10;

Note importante : la clause TABLE ne peut pas être utilisée avec les tableaux VARRAY, qui ne sont modifiables directement qu’avec PL/SQL.

Interrogation d’une collection

Pour interroger des collections imbriquées, on utilise souvent des requêtes de désemboîtement, qui ressemblent à des pseudo-jointures :

SELECT e.EtCiV.Nom, e.EtCiV.Prenom, e.Statut, c.Libelle
FROM les_enseignants e,
TABLE(e.ListeCours) c;

Il est aussi possible d’extraire directement une collection :

SELECT c.Libelle FROM TABLE (
  SELECT ListeCours FROM les_enseignants WHERE IdPers = 1000
) c;

Une autre technique avancée est l’utilisation de curseurs imbriqués, qui permettent de récupérer pour chaque enseignant la liste de ses cours sous forme de curseur :

SELECT e.EtCiV.Nom, e.EtCiV.Prenom,
CURSOR (
  SELECT c.Libelle FROM TABLE(e.ListeCours) c
)
FROM les_enseignants e;

Ce résultat ne peut pas être assimilé directement à une table classique, car il contient des curseurs imbriqués.

Collections de références

Les collections peuvent également contenir des références à des objets. La création de types de références se fait en deux étapes :

CREATE TYPE REF_COURS_t AS
OBJECT(RefCours REF COURS)
/ 

CREATE TYPE TAB_COURS AS
TABLE OF REF_COURS_t
/

La structure des tables ne change pas dans ce cas.

Collections multiniveaux

Les collections peuvent être imbriquées à plusieurs niveaux, ce qui permet de modéliser des structures complexes. Par exemple, on peut définir des notes associées à des épreuves, elles-mêmes associées à des étudiants :

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(
  IdPers NUMBER(5),
  EtCiV ETAT_CIVIL_t,
  Adr ADRESSE_t,
  Resultats TAB_EPREUVES
)
/

La création de la table des étudiants avec ces collections multiniveaux se fait ainsi :

CREATE TABLE les_etudiants OF ETUDIANT_t
(CONSTRAINT pk_etu PRIMARY KEY(IdPers))
NESTED TABLE Resultats STORE AS ti1_res
(NESTED TABLE Notes STORE AS ti2_notes);

Points clés

• Les collections en SQL3 se divisent en tables imbriquées (non ordonnées, taille illimitée) et VARRAY (ordonnés, taille limitée).
• Les tables imbriquées sont stockées dans des tables séparées et manipulables via SQL, tandis que les VARRAY sont stockés dans des attributs BLOB.
• La mise à jour des collections imbriquées nécessite l’utilisation de la clause TABLE pour cibler la collection.
• Les collections peuvent être interrogées par désemboîtement ou curseurs imbriqués pour extraire les données.
• Les collections peuvent contenir des références à des objets, facilitant la gestion des liens entre entités.
• Les collections multiniveaux permettent de modéliser des structures complexes avec plusieurs niveaux d’imbrication.