CONSORTIUM INTERNATIONAL E-MIAGE

MODULE C215

BASES DE DONNEES AVANCEES

UF1 – Modèles objet et objet-relationnel

Session 5 – SQL3

MASTER METHODES INFORMATIQUES APPLIQUEES A LA GESTION DES ENTREPRISES

30 juillet 2012

Gérard-Michel Cochard
 Session 5 : SQL3

Ch1.4 : SQL3
Introduction
La normalisation du langage de requête SQL est pilotée par le groupe ISO/IEC JTC1/SC 21/WG3 DBL.
Les travaux de ce groupe sont résumés ci-dessous :

SQL2 : 1 document de référence

SQL3 : 9 composants :
SQL/Framework : cadre général
SQL/Foundation : fondements essentiels
SQL/CLI : interface client
SQL/PSM : procédures stockées
SQL/Bindings : intégration dans les langages classiques
SQL/Transaction : gestion des transactions
SQL/Temporal : gestion du temps
SQL/MED : accès aux données externes
SQL/OBJ : intégration aux langages objet

Cette normalisation est issue d'un riche historique rappelé ici pour mémoire :

SEQUEL 1 (1974) de System R (IBM)

SEQUEL 2 (1977)
SQL1 (ANSI : 1986 ; ISO : 1987 ; révision en 1989 ; tests de validation effectués par le NIST -
National Institute of Standards and Technology)
SQL2 (ou SQL92, 1992) : 600 pages de spécification
SQL3 (ou SQL99, 1999) : 1500 pages de spécification
SQL4 : travaux démarrés en 2001 (!)

Les SGBD qui suivent les prescriptions de SQL3 sont appelés SGBD "Objet-Relationnel". Nous n'avons
C215 - Bases de données avancées | 30/07/2012

pas d'autres définition, en effet, de ce qu'est un SGBD Objet-Relationnel. Il faut toutefois remarquer que
si l'on s'en tient strictement à cette définition, il n'existe pratiquement pas de SGBD Objet-Relationnel !.
Mais il existe des SGBD s'en rapprochant : Oracle = 8-9+, Informix ≥ 9+ notamment. Les principes
généraux de SQL3 sont les suivants :

• SQL3 veut incorporer l'objet tout en gardant les acquis du modèle relationnel (et de SQL2).
Notamment le concept de relation (table) reste central.
• SQL3 s'appuie sur des données fortement typées (type = attributs + méthodes). Le processus
de création d'éléments d'une base de données comporte 3 étapes : création des types
nécessaires ; création d'éléments conformément à ces types ; insertion de ces éléments dans
des tables existantes (si ces éléments sont des objets, ils deviennent alors automatiquement
persistants).
• SQL3 reprend tous les acquis de SQL2 et notamment les types prédéfinis (considérés comme
atomiques) : DECIMAL, FLOAT, INT, … et aussi les types des objets "grands" comme LOB
(Long OBject) comportant CLOB (LOB de type caractère), BLOB (LOB de type binaire : une
image par exemple), BFILE (fichier).
• SQL3 permet (nouveauté !) la création de types par les utilisateurs. Il en existe 2 catégories : le
type DISTINCT, et le TAD (Type Abstrait de Données, en anglais ADT) de nature complexe.

1
On suppose dans tout ce qui suit que le lecteur a une bonne connaissance de SQL2.

Les types de données définis par les utilisateurs

Dans SQL2, pour le modèle relationnel, les types de données sont prédéfinis : CHAR, VARCHAR,
NUMBER, DATE, ....

Dans SQL3, pour le modèle objet-relationnel, des types nouveaux peuvent être définis par les
utilisateurs : le type DISTINCT et les TAD. Les TAD se subdivisent en TAD littéral et TAD objet.

Type DISTINCT

Ce type permet une personnalisation des données. Ainsi, un prix d'achat et un prix de vente peuvent
s'exprimer par le type prédéfini DOUBLE PRECISION. Mais si l'on veut caractériser ces deux entités
(et les "distinguer") , on peut "redéfinir" DOUBLE PRECISION en créant deux "sous-types" :

CREATE DISTINCT TYPE T_pachat AS DOUBLE PRECISION;

CREATE DISTINCT TYPE T_ pvente AS DOUBLE PRECISION;
CREATE TABLE Produit1 (
numP VARCHAR(10),
pa T_ pachat
pv T_ pvente
);

Dans l'écriture précédente, des conventions sont implicitement respectées : les noms des tables
débute par une lettre majuscule et un type est déclaré avec un préfixe "T_" et son nom débute avec
une lettre minuscule. Un tuple, de même que son schéma, est défini entre parenthèses.

TAD littéral

Les TAD littéraux portent sur les colonnes des tables relationnelles. On peut regrouper par exemple
deux colonnes (dont les champs correspondent à des types différents) en définissant un type pour ce
regroupement. C215 - Bases de données avancées | 30/07/2012

exemple : En reprenant l'exemple précédent, où la table Produit1 possède 3 champs (3 colonnes), on

peut regrouper les deux champs pa et pv en définissant un nouveau type :

CREATE TYPE T_deuxPrix (

achat T_pachat,
vente T_pvente );
CREATE TABLE Produit2 (
numP VARCHAR(10),
prix T_deuxPrix );
SELECT [Link], [Link] FROM Produit2 p WHERE [Link] > 100;

TAD objet

2
 Contrairement aux TAD précédents, les TAD objet concernent les lignes des tables, c'est à dire les
tuples. Le mot clé correspondant est ROW.

exemple :

CREATE TYPE T_adresse (num VARCHAR(6), voie VARCHAR(40), ville VARCHAR(30), codepostal
INT);
CREATE ROW TYPE T_personne (numSS INT, nom VARCHAR(30), prenom
VARCHAR(30), adresse T_adresse);
CREATE TABLE Personne OF T_personne(PRIMARY KEY numSS);
SELECT [Link], [Link], [Link] FROM Personne p WHERE [Link]=80170;

Cette sorte de TAD est appelée "objet" dans la mesure où les lignes d'une table peuvent être
considérées comme des objets et peuvent (voir plus loin) être référencées comme une entité
indépendante, la référence jouant, en quelque sorte le rôle d'un identifiant d'objet mais n'est toutefois
pas identique à un OID.

Les tables
Les tables restent centrales dans SQL3. Tout ce qui concerne les tables en SQL2 est valable en
SQL3 (c'est la moindre des choses !).

On peut toujours créer des tables relationnelles classiques ou des tables relationnelles en NF2 ou
encore des tables d'objets.

• tables relationnelles classiques

exemple :

CREATE TABLE Personne ( nom VARCHAR(30), prenom VARCHAR(30), telephone NUMBER) ;

• tables relationnelles en NF2

Elles peuvent être créées en utilisant des constructeurs de type comme ROW() ou LIST()
C215 - Bases de données avancées | 30/07/2012

exemple :

CREATE TYPE T_adresse(num VARCHAR(5), voie VARCHAR(30), ville VARCHAR(30), codepostal

INT);
CREATE TABLE Personne ( numero INT, adresse T_adresse, identité ROW(nom VARCHAR(30),
prenom VARCHAR(30)), postes LIST(ROW(bureau INT, tel VARCHAR(10))) PRIMARY KEY
numero);

• tables d'objets

Bien que le mot objet n'existe pas dans la syntaxe SQL3, il est implicitement présent dans le modèle
lorsque l'on utilise le mot clé OF.

exemple :

3
CREATE ROW TYPE T_personne
(nom VARCHAR(30), prenom VARCHAR(30), telephone NUMBER) ;
CREATE TABLE Personne OF T_personne (PRIMARY KEY nom) ;

Dans ce cas, toute instance de la table possède un OID connu du système.

Les constructeurs de type

Comme leur nom l'indiquent, ils servent à construire des types donnés. Nous en avons vu un exemple
précédemment avec ROW( ) et LIST( ). Il existe trois catégories de constructeurs de types :

• le constructeur ROW qui permet de regrouper un nombre fixe de champs de types différents :
le type obtenu est donc un tuple.
• les constructeurs de collection : SET, LIST, MULTISET qui permettent de regrouper plusieurs
éléments de même type
• le constructeur REF qui permet de définir des référence (pointeurs) vers des objets.

constructeur ROW

Il est assez naturel d'emploi et est équivalent à la déclaration ROW TYPE sauf que ROW() s'utilise à
l'intérieur de la création d'une table :

exemple :

CREATE ROW TYPE T_adresse (

num VARCHAR(5), voie VARCHAR(30), ville VARCHAR(30), codepostal INT);
CREATE ROW TYPE T_identite( nom VARCHAR(30), prenom VARCHAR(30));
CREATE TYPE T_personne ( numero INT, identite T_identite, adresse T_ adress );

est équivalent à

CREATE TYPE T_personne (

Numero INT,
Identite ROW(
nom VARCHAR(30),
prenom VARCHAR(30)), C215 - Bases de données avancées | 30/07/2012
adresse ROW(
num VARCHAR(5),
voie VARCHAR(30),
ville VARCHAR(30),
codepostal INT)
);

Le constructeur peut s'utiliser lors d'une instanciation.

exemple : reprenons l'exemple précédent

CREATE TABLE Personne OF T_personne;

INSERT INTO Personne VALUES ('1234',
ROW(
'DUPONT', 'Aristide'),
ROW('13','bd Saint-Michel', 'PARIS', '75005')
);

4
 Dans les requêtes, il faudra utiliser la notation "." pour sélectionner des champs particuliers :

SELECT [Link], [Link] FROM Personne p WHERE [Link] = "PARIS';

constructeurs de collections

Une collection contient des éléments de même type (contrairement au ROW). SQL3 définit 3
constructeurs de collection :

IN et NOT IN permettent de savoir si un éléments figure ou non dans une collection. CARDINALITY
donne simplement le nombre d'éléments d'une collection.

exemple :

SELECT [Link], [Link] FROM Annuaire p WHERE 'Sudoku' IN [Link];

SELECT [Link], [Link], CARDINALITY([Link]) FROM Annuaire p WHERE
'Sudoku' IN [Link];

Constructeur de référence

Rappelons qu'un élément est considéré comme un objet dès qu'il est placé dans une table avec OF.
Un objet possède un oid (identifiant) unique qui est généré automatiquement par le système.
Rappelons aussi qu'il est immuable, non réutilisable après disparition de l'objet correspondant.

Dans SQL3, l'oid d'un objet de type T est référencé par REF(T). Contrairement au modèle O-O, une
référence à un objet peut être affichée dans le modèle O-R. Une référence peut être déclarée dans un
C215 - Bases de données avancées | 30/07/2012

type ou dans une table.

exemple : supposons deux types définis a priori, un type personne et un type T_equipe (on suppose
qu'une personne du type T_personne correspond à une seule équipe du type T_equipe. On désire
relier ces deux types par une référence :

CREATE OR REPLACE BODY TYPE T_personne (

5
 nom VARCHAR(30),
prenom VARCHAR(30),
affectation REF(T_equipe)
);

On peut aussi utiliser le constructeur REF( ) directement dans la création d'une table.

exemple :

CREATE TABLE binome (

personne1 REF(T_personne),
personne2 REF(T_personne)
);

Considérons maintenant les deux requêtes suivantes :

SELECT [Link] FROM Personne p WHERE [Link] = 'Dupont';

SELECT [Link]->nomeq FROM Personne p WHERE [Link] = "Dupont';

La première requête fournira seulement l'adresse de l'équipe correspondant à la personne 'Dupont' ce

qui n'est pas en général très utile.

La seconde requête fournira le nom de l'équipe à laquelle est affecté la personne 'Dupont'. Elle est
équivalente à :

SELECT [Link] FROM Personne p, Equipe e

WHERE [Link] = 'Dupont'
AND REF(e) = [Link];

Méthodes
Nous avons signalé que SQL3 est basé sur un typage fort, c'est à dire que le type inclut les attributs
mais aussi les méthodes. Comment SQL3 définit-il les méthodes ?

Il faut distinguer la déclaration de la définition. La définition s'effectue à la création du type selon la

syntaxe évidente :

CREATE TYPE T_nomtype (attributs, déclaration ou définition des méthodes); C215 - Bases de données avancées | 30/07/2012

La définition des méthodes peut se faire lors de la création du type (CREATE) comme la syntaxe
précédente le montre ou lorsque l'on modifie le type (REPLACE). La définition d'une méthode
nécessite un langage de programmation qui peut être interne( c'est le cas de PL/SQL dans Oracle) ou
externe (C ou C++ pu Java).

exemple : le type T_personne contient la méthode T_personne( ) qui ajoute une personne (c'est un
constructeur de type !).

CREATE TYPE T_personne (

nom VARCHAR(30),
prenom VARCHAR(30),
FUNCTION T_personne( :nom VARCHAR(30), :prenom VARCHAR(30))
RETURNS T_personne;
p T_personne; /* déclaration locale */
BEGIN
:p := T_personne( ); /* constructeur */
[Link] := :nom;

6
 [Link] := :prenom;
RETURN :p;
END;);

La méthode T_personne( ) est ici définie complètement dans le type. On pourrait seulement la
déclarer :

CREATE TYPE T_personne (

nom VARCHAR(30),
prenom VARCHAR(30),
FUNCTION T_personne ( :nom VARCHAR(30), :prenom VARCHAR(30) RETURNS
T_personne); );

Evidemment il faudra définir, dans ce dernier cas la fonction T_personne( ) ailleurs, c'est à dire hors
du type T_personne.

SQL3 fournit aussi un certain nombre de fonctions utilitaires comme des fonctions de comparaison et
des fonctions de conversion que nous n'explicitons pas ici.

Héritage
On peut considérer deux types d'héritages : l'héritage de type et l'héritage de table. L'héritage de type
permet de définir des sous-types avec le mot clé UNDER.

exemple :

CREATE ROW TYPE T_personne (

nom VARCHAR(30),
prenom VARCHAR(30)
);
CREATE ROW TYPE T_professeur UNDER T_personne (
categorie VARCHAR(10),
discipline VARCHAR(20)
);
CREATE ROW TYPE T_etudiant UNDER T_personne (
C215 - Bases de données avancées | 30/07/2012

diplôme VARCHAR(10),
numero INTEGER
);

On notera qu'il suffit d'ajouter les propriétés nouvelles. L'héritage de table s'effectue également avec
le mot clé UNDER

exemple :

CREATE TABLE Personne (

nom VARCHAR(30),
prenom VARCHAR(30)
);
CREATE TABLE Professeur UNDER Personne (
categorie VARCHAR(10),
discipline VARCHAR(20)
);
CREATE TABLE Etudiant UNDER Personne (

7
 diplôme VARCHAR(10),
numero INTEGER
);

Au t re s nouv eau t é s d e SQ L 3

SQL3 a introduit les déclencheurs ou triggers. Ils étaient d'ailleurs déjà employés dans les SGBD du
commerce mais non encore normalisés dans SQL. Les triggers seront étudiés dans le chapitre 4.

SQL/Transaction apporte aussi des nouveautés concernant la gestion des transactions. Les
transactions sont étudiées également dans le chapitre 4.

SQL3/PSM (PSM signifie Persistent Stored Modules) est un langae de programmation "intégré" à SQL.
Dans SQL "ordinaire", on ne peut déclarer de variable, assigner une valeur à une variable, faire des
boucles de programmation, utiliser des structures conditionnelles. PSM le permet. Les modules PSM
sont comme les objects d'une base de données : ils sont persistants, ils possèdent un schéma et sont
stockés dans la base. De plus les modules PSM peuvent contenir des procédures SQL.

PSM permet la création de fonctions, de procédures et de modules contenant des fonctions et des
procédures.

Une fonction est créée avec :

CREATE FUNCTION nom_fonction (liste de paramètres) RETURNS type_de_la_valeur_de_retour

BEGIN
Liste de définitions de variables
Bloc d'instructions
END

On peut supprimer une fonction avec

DROP FUNCTION nom_fonction

Une procédure est créée avec

C215 - Bases de données avancées | 30/07/2012
CREATE PROCEDURE nom_procedure(liste de paramètres)
BEGIN
Liste de définitions de variables
Bloc d'instructions
END

Dans la liste de paramètres, ceux-ci sont précédés de IN pour les données envoyées à la procédure
ou de OUT pour les résultats donnés par la procédure. On peut aussi avoir INOUT pour des variables
en entrée comme en sortie.

On peut supprimer une procédure avec

DROP PROCEDURE nom_procedure

Oracle utilise une variante de PSM appelée PL/SQL que nous examinerons dans la section suivante.