Cours complet pour apprendre les systèmes de gestion de bases de données

Le relationnel permet tout d'abord une modélisation des données adaptée à la gestion à l'aide de tables dont les colonnes prennent valeurs dans des domaines alphanumériques. Le concept de table dont les lignes constituent les enregistrements successifs est simple, bien adapté pour représenter par exemple des employés, des services ou des paiements. Avec SQL2, les domaines se sont diversifiés. Les dates, les monnaies, le temps et les intervalles de temps sont parfaitement supportés.

Dans les années 80, les SGBD relationnels se sont centrés sur le transactionnel (On Line Transaction Processing - OLTP) et sont devenus très performants dans ce contexte. Les années 90 ont vu le développement du décisionnel (On Line Analysis Processing - OLAP). Le relationnel s'est montré capable d'intégrer la présentation multidimensionnelle des données nécessaire à l'OLAP. En effet, il est simple de coder un cube 3D en table, trois colonnes mémorisant les valeurs d'une dimension et la quatrième la grandeur analysée.

En bref, grâce au langage assertionnel puissant que constitue le standard universel SQL2, à sa bonne adaptation aux architectures client-serveur de données, à sa théorie bien assise aussi bien pour la conception des bases (normalisation), l'optimisation de requêtes (algèbre, réécriture, modèle de coûts) et à la gestion de transactions (concurrence, fiabilité) intégrée, le relationnel s'est imposé dans l'industrie au cours des années 80.

Le relationnel présente cependant des faiblesses qui justifient son extension vers l'objet. Tout d'abord, les règles de modélisation imposées pour structurer proprement les données s'avèrent trop restrictives.

L'absence de pointeurs visibles par l'utilisateur est très pénalisante. Dans les architectures modernes de machine où la mémoire à accès direct est importante, il devient intéressant de chaîner directement des données. Le parcours de références est rapide et permet d'éviter les jointures par valeurs du relationnel. Des enregistrements volumineux représentant de gros objets peuvent aussi être stockés une seule fois et pointés par plusieurs lignes de tables : le partage référentiel d'objets devient nécessaire. Par exemple, une image sera stockée une seule fois, mais pointée par deux tuples, un dans la table Employés, l'autre dans la table Clients. Passer par une clé d'identification (par exemple, le numéro de sécurité sociale) nécessite un accès par jointure, opération lourde et coûteuse. En clair, il est temps de réhabiliter les pointeurs sous la forme d'identifiants d'objets invariants au sein du relationnel.

Le non-support de domaines composés imposé par la première forme normale de Codd est inadapté aux objets complexes, par exemple aux documents structurés. L'introduction de champs binaires ou caractères longs (Binary Large Object - BLOB, Character Large Object - CLOB) et plus généralement de champs longs (Large Object - LOB) est insuffisante.

Les objets longs présentent deux inconvénients majeurs : ils ne sont pas structurés et ne supportent pas les recherches associatives. La seule possibilité est de les lire séquentiellement. Ils sont donc particulièrement insuffisants pour le stockage intelligent de documents structurés, où l'on souhaite accéder par exemple à une section sans faire défiler tout le document. En clair, il faut pouvoir lever la contrainte d'atomicité des domaines, ne serait-ce que pour stocker des attributs composés (par exemple l'adresse composée d'une rue, d'un code postal et d'une ville) ou multivalués (par exemple les points d'une ligne).

La non-intégration des opérations dans le modèle relationnel constitue un manque. Celui-ci résulte d'une approche traditionnelle où l'on sépare les traitements des données. Certes, les procédures stockées ont été introduites dans le relationnel pour les besoins des architectures client-serveur, mais celles-ci restent séparées des données. Elles ne sont pas intégrées dans le modèle. Il est par exemple impossible de spécifier des attributs cachés de l'utilisateur, accessibles seulement via des opérations manipulant ces attributs privés.

Tout ceci conduit à un mauvais support des applications non standard telles que la CAO, la CFAO, les BD géographiques et les BD techniques par les SGBD relationnels. En effet, ces applications gèrent des objets à structures complexes, composés d'éléments chaînés souvent représentés par des graphes. Le relationnel pur est finalement mal adapté. Il en va de même pour le support d'objets multimédia que l'on souhaite pouvoir rechercher par le contenu, par exemple des photos que l'on souhaite retrouver à partir d'un portrait robot.

Les modèles objet sont issus des langages objet. Ils apportent des concepts nouveaux importants qui répondent aux manques du relationnel, comme l'identité d'objets, l'encapsulation, l'héritage et le polymorphisme.

L'identité d'objet permet l'introduction de pointeurs invariants pour chaîner les objets entre eux. Ces possibilités de chaînage rapide sont importantes pour les applications nécessitant le support de graphes d'objets. On pourra ainsi parcourir rapidement des suites d'associations, par navigation dans la base. Par exemple, passer d'un composé à ses composants puis à la description de ces composants nécessitera simplement deux parcours de pointeurs, et non plus des jointures longues et coûteuses.

L'encapsulation des données permet d'isoler certaines données dites privées par des opérations et de présenter des interfaces stables aux utilisateurs. Ceci facilite l'évolution des structures de données qui peuvent changer sans modifier les interfaces publiques seules visibles des utilisateurs. Au lieu d'offrir des données directement accessibles aux utilisateurs, le SGBD pourra offrir des services cachant ces données, beaucoup plus stables et complets. Ceux-ci pourront plus facilement rester invariants au fur et à mesure de l'évolution de la base de données.

L'héritage d'opérations et de structures facilite la réutilisation des types de données. Il permet plus généralement l'adaptation de composants à son application. Les composants pourront être des tables, mais aussi des types de données abstraits, c'est-à-dire un groupe de fonctions encapsulant des données cachées. Il sera d'ailleurs possible de définir des opérations abstraites, qui pourront, grâce au polymorphisme, porter le même nom, mais s'appliquer sur des objets différents avec pour chaque type d'objet un code différent. Cela simplifie la vie du développeur d'applications qui n'a plus qu'à se soucier d'opérations abstraites sans regarder les détails d'implémentation sur chaque type d'objet.

L'apport essentiel de l'objet-relationnel est sans doute le support d'objets complexes au sein du modèle. Ceci n'est pas inhérent à l'objet, mais plutôt hérité des premières extensions tendant à faire disparaître la première forme normale du relationnel. On parle alors de base de données en non première forme normale (NF²).

Un cas particulier intéressant est le modèle relationnel imbriqué [Scholl86].

EXEMPLE

Des services employant des employés et enregistrant des dépenses pourront directement être représentés par une seule table externe imbriquant des tables internes :

SERVICES (N° INT, CHEF VARCHAR, ADRESSE VARCHAR, {EMPLOYÉS (NOM, AGE)}, {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. Notons que le modèle relationnel permet une imbrication à plusieurs niveaux, par exemple, Motif pourrait correspondre à une table pour chaque dépense.

Le multimédia, particulièrement la géométrie, a nécessité d'introduire des attributs multivalués plus généraux. Comme l'objet, l'objet-relationnel offre des collections génériques prédéfinies telles que des listes, ensembles, tableaux qui peuvent être imbriquées pour représenter des objets très compliqués. Par exemple, on pourra définir une ligne comme une liste de points, chaque point étant défini par une abscisse et une ordonnée. Une région pourra être définie par une liste de lignes fermée. Ces possibilités sont essentielles pour la représentation de structures complexes. Celles-ci peuvent être cachées par des fonctions offertes aux utilisateurs afin d'en simplifier la vision. Ainsi, objets complexes et encapsulation seront souvent couplés.

Les types utilisateur permettent la définition de types extensibles utilisables comme des domaines, supportant l'encapsulation.

Le système de type du SGBD devient donc extensible et n'est plus limité aux types alphanumériques de base, comme avec le relationnel pur et SQL2. On pourra, par exemple, définir des types texte, image, point, ligne, etc. Les types de données définissables par l'utilisateur sont appelés types abstraits (ADT) en SQL3. La notion de type abstrait fait référence au fait que l'implémentation est spécifique de l'environnement : seule l'interface d'un type utilisateur est visible.

Les SGBD objet-relationnels offrent différents types de collections, tels que le tableau dynamique, la liste, l'ensemble, la table, etc. Le patron LISTE(X) pourra par exemple être instancié pour définir des lignes sous forme de listes de points : LIGNE LISTE(POINT).

Les références sont les identifiants d'objets (OID) dans le modèle objet-relationnel. Elles sont en théorie des adresses logiques invariantes qui permettent de chaîner directement les objets entre eux, sans passer par des valeurs nécessitant des jointures.

L'héritage de type permet donc de définir un sous-type d'un type SQL ou d'un type utilisateur. Une table correspondant à un type sans opération, l'objet-relationnel permet aussi l'héritage de table.

En résumé, le modèle objet-relationnel conserve donc les notions de base du relationnel (domaine, table, attribut, clé et contrainte référentielle) auxquelles il ajoute les concepts de type utilisateur avec structure éventuellement privée et opérations encapsulant, de collections supportant des attributs multivalués (structure, liste, tableau, ensemble, etc.), d'héritage sur relations et types et d'identifiants d'objets permettant les références intertables. Les domaines peuvent dorénavant être des types utilisateurs. La figure XIII.4 illustre tous ces concepts. Deux vues sont possibles : la vue relationnelle dans laquelle les relations sont des relations entre objets (voir figure XIII.5), chaque colonne étant en fait valuée par un objet ; la vue objet (voir figure XIII.6) où une ligne d'une table peut correspondre à un objet simple (un tuple) ou complexe (un tuple avec des attributs complexes).

Au-delà du modèle, il est nécessaire d'étendre le langage de manipulation de données pour supporter les nouvelles avancées du langage de définition. SQL3 va donc comporter un langage de requêtes étendu avec notamment les appels d'opérations en résultat et en qualification, la surcharge des opérateurs de base de SQL (par exemple l'addition), le support automatique de l'héritage, le parcours de références, la constitution de tables imbriquées en résultat, etc. Nous étudierons ces différentes fonctionnalités à l'aide d'exemples.

Au-delà du groupe ANSI nord-américain très actif, la normalisation est conduite par un groupe international dénommé ISO/IEC JTC1/SC 21/WG3 DBL, DBL signifiant Database Language. Les pays actifs sont essentiellement l'Australie, le Brésil, le Canada, la France, l'Allemagne, le Japon, la Corée, la Hollande, le Royaume-Uni et bien sûr les États-Unis. L'essentiel du travail est bien préparé au niveau du groupe ANSI X3H2 (http://www.ansi.org) dont Jim Melton est le président. Ce groupe a déjà réalisé le standard SQL2 présenté dans la partie relationnelle. Les standards acceptés étaient contrôlés aux États-Unis par le NIST (http://ncsl.nist.gov) qui définissait des programmes de tests de conformité. Ceux-ci existent partiellement pour SQL2, mais pas pour SQL3. Depuis 1996, le Congrès américain a supprimé les crédits du NIST, si bien qu'il n'y a plus de programmes de validation espérés…

Alors que SQL2 était un unique document [SQL92], SQL3 a été divisé en composants pour en faciliter la lisibilité et la manipulation, l'ensemble faisant plus de 1 500 pages. Les composants considérés sont brièvement décrits dans le tableau représenté figure XIII.7.

Le planning de SQL3 a été décalé a plusieurs reprises, ces décalages témoignant de la difficulté et de l'ampleur de la tâche. Les parties 1 à 7 de SQL sont à l'état de brouillons internationaux (Draft International Standard) depuis fin 1997. Ils devraient devenir de véritables standards fin 1998 ou en 1999. Les deux derniers sont prévus pour l'an 2000.

Au-delà de SQL3, d'autres composants sont prévus tels SQL/MM pour la spécification de types utilisateurs multimédia et SQL/RDA pour la spécification du protocole RDA de transfert de données entre client et serveur. On parle déjà de SQL4, sans doute pour après l'an 2000.

Le composant 2 contient l'essentiel du langage SQL3, en particulier les fonctionnalités de base pour définir les autres composants (Basic SQL/CLI capabilities, Basic SQL/PSM capabilities), les déclencheurs (Triggers), les types utilisateurs appelés types abstraits (Abstract Data Types) et les fonctionnalités orientées objet que nous allons étudier en détail ci-dessous. Un mot sur les déclencheurs : bien que partie intégrante du relationnel étudiée en tant que telle au chapitre VIII, les déclencheurs ne sont normalisés que dans SQL3. Il s'agit là de combler une lacune de la normalisation.

XVIII-5-1-1. Principes▲

La première nouveauté de SQL3 est l'apparition d'une commande CREATE TYPE. Au-delà des types classiques (numériques, caractères, date) de SQL, il devient possible de créer des types dépendant de l'application, multimédia par exemple (voir figure XIII.8).

Une instance d'un type peut être un objet ou une valeur. Un objet possède alors un OID et peut être référencé. Une valeur peut simplement être copiée dans une ligne d'une table. Un type possède en général des colonnes, soit directement visibles, soit cachées et accessibles seulement par des fonctions encapsulant le type. Les opérateurs arithmétiques de SQL (+, *, -, /) peuvent être redéfinis au niveau d'un type. Par exemple, on redéfinira l'addition pour les nombres complexes. Enfin, un type peut posséder des clauses de comparaison (=, <) permettant d'ordonner les valeurs et peut être représenté sous la forme d'un autre type, une chaîne de caractères par exemple.

XVIII-5-1-2. Syntaxe▲

CREATE [DISTINCT] TYPE <NOM ADT> [<OPTION OID>] [<CLAUSE SOUS-TYPE>]
[AS] (<CORPS DE L'ADT>)

CREATE DISTINCT TYPE EURO AS (DECIMAL(9,2))

<OPTION OID> ::= WITH OID VISIBLE

<CLAUSE SOUS-TYPE> ::= UNDER <SUPERTYPE>[,<SUPERTYPE>]…

<CLAUSES MEMBRES> ::=
                      < DÉFINITION DE COLONNE >
        |             < CLAUSE ÉGALE >
        |             < CLAUSE INFÉRIEURE>
        |             < CLAUSE CAST >
        |             < DÉCLARATION DE FONCTION >
        |             < DÉCLARATION D'OPÉRATEUR >

<DÉCLARATION DE FONCTION> ::=
                              [<FUNCTION TYPE>] FUNCTION <FUNCTION NAME> <PARAMETER LIST>
                              RETURNS <FUNCTION RESULTS>
                              { <SQL PROCEDURE> | <FILE NAME> } END FUNCTION

<FUNCTION TYPE> ::= CONSTRUCTOR | ACTOR | DESTRUCTOR

XVIII-5-1-3. Quelques exemples▲

CREATE PROCEDURE AUGMENTER (E EMPLOYÉ, MONTANT MONEY)
{ UPDATE EMPLOYÉ SET SALAIRE = SALAIRE + MONTANT WHERE EMPLOYÉ.OID = E}
END PROCEDURE

SQL3 supporte la construction d'objets complexes par le biais de types collection paramétrés. Ces types génériques, appelés patrons de collections, doivent être fournis comme une bibliothèque avec le compilateur SQL3. Celui-ci doit assurer la généricité et en particulier la possibilité de déclarer des collections de types d'objets ou de valeurs quelconques. Chaque patron de collection fournit des interfaces spécifiques pour accéder aux éléments de la collection.

Tout environnement SQL3 doit offrir les patrons de base SET(T), MULTISET(T) et LIST(T). Un multiset, encore appelé bag, se comporte comme un ensemble, mais permet de gérer des doubles. Par exemple, il est possible de définir un type personne avec une liste de prénoms et un ensemble de téléphones comme suit :

CREATE TYPE PERSONNE WITH OID VISIBLE
(NSS INT, NOM VARCHAR, PRÉNOMS LIST(VARCHAR), TEL SET(PHONE)).

Il est aussi possible de créer un ensemble de personnes :

CREATE TYPE PERSONNES (CONTENU SET (PERSONNE))

ou un ensemble d'identifiants de personnes :

CREATE TYPE RPERSONNES (CONTENU SET(REF(PERSONNE))).

Au-delà des types de collections SET, LIST et MULTISET, SQL3 propose des types additionnels multiples : piles (stack), files (queue), tableaux dynamiques (varray), tableaux insérables (iarray) pour gérer des textes par exemple. Un tableau dynamique peut grandir par la fin, alors qu'un tableau insérable peut aussi grandir par insertion à partir d'une position intermédiaire (il peut donc grandir par le milieu). Ces types de collections sont seulement optionnels : ils ne sont pas intégrés dans le langage de base, mais peuvent être ajoutés.

De manière générale, il est possible de référencer des objets via des OID mémorisés comme valeurs d'attributs. De tels attributs sont déclarés références (REF) comme dans l'exemple ci-dessous :

CREATE TYPE VOITURE (NUMÉRO CHAR(9), COULEUR VARCHAR, PROPRIÉTAIRE REF(PERSONNE))

Les tables SQL restent inchangées, à ceci près que le domaine d'un attribut peut maintenant être un type créé par la commande CREATE TYPE. De plus, des fonctions peuvent être définies au niveau d'une table pour encapsuler les tuples vus comme des objets. Aussi, l'héritage de table qui permet de réutiliser une définition de table est possible.

Par exemple, la table représentée figure XIII.5 sera simplement créée par la commande :

CREATE TABLE ACCIDENTS (ACCIDENT INT, RAPPORT TEXT, PHOTO IMAGE)

en supposant définis les types TEXT (texte libre) et IMAGE, qui sont généralement fournis avec un SGBD objet-relationnel, comme nous le verrons plus loin.

La table de la figure XIII.6 pourra être définie à partir des mêmes types avec en plus :

CREATE TYPE CONDUCTEUR (CONDUCTEUR VARCHAR, AGE INT)
CREATE TYPE ACCIDENT (ACCIDENT INT, RAPPORT TEXT, PHOTO IMAGE)

par la commande :

CREATE TABLE POLICE (NPOLICE INT, NOM VARCHAR, ADRESSE ADRESSE,
CONDUCTEURS SET(CONDUCTEUR), ACCIDENTS LIST(ACCIDENT)).

SQL3 donne aussi des facilités pour passer d'un type à une table de tuples de ce type et réciproquement, d'une table au type correspondant.

Par exemple, en utilisant le type EMPLOYÉ défini ci-dessus, il est possible de définir simplement une table d'employés par la commande :

CREATE TABLE EMPLOYÉS OF EMPLOYÉ ;

En définissant une table VINS, il sera possible de définir le type VIN composé des mêmes attributs comme suit :

CREATE TABLE VINS(NV INT, CRU VARCHAR, DEGRÉ FLOAT(5.2))
OF NEW TYPE VIN ;

Comme mentionné, l'héritage de table qui recopie la structure est possible par la clause :

CREATE TABLE <TABLE> UNDER <TABLE> [WITH (LISTE D'ATTRIBUTS TYPÉS)];

La liste des attributs typés permet d'ajouter les attributs spécifiques à la sous-table. Par exemple, une table de vins millésimés pourra être créée par :

CREATE TABLE VINSMILL UNDER VINS WITH (MILLÉSIME INT, QUALITÉ VARCHAR);

Le langage de requêtes est étendu afin de supporter les constructions nouvelles du modèle. L'extension essentielle consiste à permettre l'appel de fonctions dans les termes SQL, qui peuvent figurer dans les expressions de valeurs sélectionnées ou dans les prédicats de recherche. Une fonction s'applique à l'aide de la notation parenthésée sur des termes du type des arguments spécifiés.

Considérons par exemple la table d'employés contenant des instances du type EMPLOYÉ défini ci-dessus. La requête suivante retrouve le nom et l'âge des employés de moins de 35 ans :

SELECT E.NOM, AGE(E)
FROM EMPLOYÉS E
WHERE AGE(E) < 35;

La notation pointée appliquée au premier argument est aussi utilisable pour invoquer les fonctions :

SELECT E.NOM, E..AGE()
FROM EMPLOYÉS E
WHERE E..AGE() < 35;

Il s'agit d'un artifice syntaxique, la dernière version utilisant d'ailleurs la double notation pointée (..) pour les fonctions et attributs composés, la notation pointée simple (.) étant réservée au SQL de base (accès à une colonne usuelle de tuple).

Au-delà des fonctions, SQL3 permet aussi l'accès aux attributs composés par la notation pointée. Supposons par exemple une table d'employés localisés définie comme suit :

CREATE TABLE EMPLOYÉSLOC UNDER EMPLOYÉS WITH (ADRESS ADRESSE)

La requête suivante permet de retrouver le nom et le jour de repos des employés des Bouches-du-Rhône habitant Marseille :

SELECT NOM, REPOS
FROM EMPLOYÉSLOC E
WHERE DEPT(E.ADRESS) = "BOUCHES DU RHONE"
      AND E.ADRESS..VILLE = "MARSEILLE";

Notons dans la requête ci-dessus l'usage de fonctions pour extraire le département à partir du code postal et l'usage de la notation pointée pour extraire un champ composé. Nous préférerons utiliser partout la notation pointée ; il faut cependant distinguer fonction et accès à attribut par la présence de parenthèses.

Afin d'illustrer plus complètement, supposons définis un type point et une fonction distance entre deux points comme suit :

TYPE POINT (ABSCISSE X, ORDONNÉE Y,
FUNCTION DISTANCE(P1 POINT, P2 POINT) RETURN (REAL));

Considérons une implémentation des employés par la table :

EMPLOYÉS (MUNEMP INT, NOM VARCHAR, ADRESS ADRESSE, POSITION POINT);

La requête suivante recherche alors les nom et adresse de tous les employés à moins de 100 unités de distance (par exemple le mètre) de l'employé Georges :

SELECT E.NOM, E.ADRESS
FROM EMPLOYÉS G, EMPLOYÉS E
WHERE G.NAME = "GEORGES" AND DISTANCE(G.POSITION,E.POSITION) < 100.

Supposons de plus défini le type CERCLE comme suit :

TYPE CERCLE (CENTRE POINT, RAYON REAL,
CONSTRUCTOR FUNCTION CERCLE(C POINT, R REAL) RETURN (CERCLE))

Ajoutons une fonction booléenne CONTIENT au type point :

CREATE FUNCTION CONTIENT (P POINT, C CERCLE)
    { CODE DE LA FONCTION } RETURN (BOOLEAN)
END FUNCTION.

La question suivante retrouve les employés dont la position est contenue dans un cercle de rayon 100 autour de l'employé Georges :

SELECT E.NAME, E.ADRESS
FROM EMPLOYÉS G, EMPLOYÉS E
WHERE EMPLOYÉS.NOM = "GEORGES" AND
      CONTIENT(E.POSITION, CERCLE(G.POSITION,1));

Les deux requêtes précédentes sont de fait équivalentes et génèrent les mêmes réponses.

SQL3 permet aussi de traverser les associations représentées par des attributs de type référence.

Ces attributs peuvent être considérés comme du type particulier référence, sur lequel il est possible d'appliquer les fonctions Ref pour obtenir la valeur de l'OID et DeRef pour obtenir l'objet pointé. Afin de simplifier l'écriture, DeRef peut être remplacée par la notation →. Celle-ci permet donc les parcours de chemins. On peut ainsi écrire des requêtes mélangeant la notation simple point (accès à un attribut), double point (accès à un attribut ADT composé) et flèche (parcours de chemins). Espérons que la notation pointée simple pourra être utilisée partout comme une amélioration syntaxique !

Considérons le type VOITURE déjà défini ci-dessus comme suit :

CREATE TYPE VOITURE (NUMÉRO CHAR(9), COULEUR VARCHAR,
PROPRIÉTAIRE REF(PERSONNE));

Créons une table de voitures :

CREATE TABLE VOITURES OF TYPE VOITURE;

La requête suivante recherche les noms des propriétaires de voitures rouges habitant Paris :

SELECT V.PROPRIETAIRE→NOM
FROM VOITURES V
WHERE V.COULEUR = "ROUGE" AND V.PROPRIETAIRE→ADRESSE..VILLE = "PARIS";

SQL3 permet de multiples notations abrégées. En particulier, il est possible d'éviter de répéter des préfixes de chemins avec la notation pointée suivie de parenthèses. Par exemple, la requête suivante recherche les numéros des voitures dont le propriétaire habite les Champs-Élysées à Paris et a pour prénom Georges :

SELECT V.NUMÉRO
FROM VOITURES V
WHERE V.PROPRIÉTAIRE→(ADRESSE..(VILLE = "PARIS"
      AND RUE = "CHAMPS-ÉLYSÉES") AND PRÉNOM = "GEORGES");

Les collections de base sont donc les ensembles, listes et sacs. Ces constructeurs de collections peuvent être appliqués sur tout type déjà défini. Les collections sont rendues permanentes en qualité d'attributs de tables. La construction TABLE est proposée pour transformer une collection en table et l'utiliser derrière un FROM comme une véritable table. Toute collection peut être utilisée à la place d'une table précédée de ce mot clé TABLE. Par exemple, supposons l'ajout d'une colonne PASSETEMPS à la table des personnes valuée par un ensemble de chaînes de caractères :

ALTER TABLE PERSONNES ADD COLUMN PASSETEMPS SET(VARCHAR);

La requête suivante retrouve les références des personnes ayant pour passe-temps le vélo :

SELECT REF(P)
FROM PERSONNES P
WHERE "VÉLO" IN
      SELECT *
      FROM TABLE (P.PASSETEMPS);

Plus complexe, la requête suivante recherche les numéros des polices d'assurance dont un accident contient un rapport avec le mot clé « Pont de l'Alma » :

SELECT P.NPOLICE
FROM POLICE P, TABLE P.ACCIDENTS A, TABLE A.RAPPORT..KEYWORDS M
WHERE M = "PONT DE L'ALMA";

Cette requête suppose la disponibilité de la fonction KEYWORDS sur le type TEXT du rapport qui délivre une liste de mots clés. Nous tablons tout d'abord la liste des accidents, puis la liste des mots clés du rapport de chaque accident. Ceci donne donc une sorte d'expression de chemins multivalués. SQL3 est donc très puissant !

L'héritage de tables est pris en compte au niveau des requêtes. Ainsi, lorsqu'une table possède des sous-tables, la recherche dans la table retrouve toutes les lignes qui qualifient au critère de recherche, aussi bien dans la table que dans les sous-tables.

Par exemple, considérons la table BUVEURS définie comme suit :

CREATE TABLE BUVEURS UNDER PERSONNES WITH ETAT ENUM(NORMAL,IVRE);

La recherche des personnes de prénom Georges par la requête :

SELECT NOM, PRÉNOM, ADRESSE
FROM PERSONNES
WHERE PRÉNOM = "GEORGES"

retournera à la fois les personnes et les buveurs de prénom Georges.

SQL/PSM (Persistent Store Modules) est un langage de programmation de modules persistants et stockés dans la base de données [Melton98]. Les modules sont créés et détruits par des instructions spéciales CREATE MODULE et DROP MODULE. Un module se compose de procédures et/ou de fonctions. Il est possible de créer directement des procédures (CREATE PROCEDURE) ou des fonctions (CREATE FUNCTION) non contenues dans un module. Un module est associé à un schéma de base de données sur lequel peuvent être définies des autorisations et des tables temporaires. Il est aussi possible de définir des curseurs partagés entre les procédures et fonctions composantes.

SQL/PSM parachève SQL pour en faire un langage complet. Cependant, il est possible d'écrire des procédures stockées en pur SQL ou dans un langage pour lequel l'intégration avec SQL est définie (ADA, C, COBOL, FORTRAN, PASCAL, PL/I et MUMPS). Pour ces langages, PSM permet de définir les paramètres d'entrée et de retour des procédures (mots clés IN pour les paramètres d'entrée et OUT pour ceux de sortie) ou des fonctions (clause RETURN <type>). Ces procédures sont en général invoquées depuis des programmes hôtes par des ordres EXEC SQL plus ou moins spécifiques du langage hôte, ou directement depuis d'autres procédures PSM par des ordres CALL <procédure> ou des appels directs de fonctions.

L'intérêt essentiel de SQL/PSM est de fournir un langage de programmation homogène avec SQL et manipulant les mêmes types de données. Ce langage comporte des déclarations de variables, des assignations, des conditionnels CASE, IF, des boucles LOOP, REPEAT, WHILE, FOR et des traitements d'erreurs et exceptions SIGNAL, RESIGNAL.

Une déclaration de variable est analogue à une déclaration de colonne en SQL :

<DECLARATION DE VARIABLE> ::= DECLARE <VARIABLE> <TYPE> [DEFAULT <VALEUR>]

Par exemple, une déclaration possible est :

DECLARE PRIX INT DEFAULT 0;

Une assignation s'effectue par une clause SET suivie d'une expression de valeur conforme à SQL, ou par l'assignation du résultat d'une requête dans une variable. Par exemple :

DECLARE MOYENNE DECIMAL(7.2)
SELECT AVG(SALAIRE) INTO MOYENNE FROM EMPLOYÉS

assigne le résultat de la requête à la variable MOYENNE.

L'ordre CASE permet de tester différents cas de valeurs d'une expression de valeur en paramètre du CASE (expression de valeur1) ou de plusieurs expressions de valeurs booléennes :

<ORDRE CASE> ::=
CASE [<EXPRESSION DE VALEUR1>]
    WHEN <EXPRESSION DE VALEUR2 > THEN <ORDRE SQL>;
    [WHEN <EXPRESSION DE VALEUR> THEN <ORDRE SQL>;]…
    ELSE <ORDRE SQL>;
END CASE

Par exemple :

CASE MOYENNE
    WHEN <100 THEN CALL PROC1;
    WHEN = 100 THEN CALL PROC2;
    ELSE CALL PROC3;
END CASE

permet de traiter différents cas de la variable MOYENNE.

Les boucles LOOP, REPEAT et WHILE sont des boucles de calculs classiques en mémoire, qui ne font pas directement appel à la base. La boucle FOR permet au contraire de parcourir le résultat d'une requête par le biais d'un curseur. Sa syntaxe simplifiée est la suivante :

<ORDRE FOR> ::=
[<ÉTIQUETTE> :]FOR <NOM DE BOUCLE>
  AS [<NOM DE CURSEUR> CURSEUR FOR] <SPECIFICATION DE CURSEUR>
  DO
    <ORDRE SQL>
END FOR <ÉTIQUETTE>;

L'étiquette permet de référencer la boucle FOR. Le nom de boucle sert de qualifiant pour les colonnes de la table virtuelle correspondant au curseur, afin de les distinguer si nécessaire. Le nom de curseur est optionnel pour permettre la réutilisation du curseur. Une définition de curseur est classique en SQL : il s'agit en général d'une requête.

Voici la syntaxe des constructions essentielles du langage et un exemple de procédure et de fonction. Nous travaillons sur la base des vins composée des tables :

VINS (NV INT, CRU VARCHAR, MILL INT, DEGRÉ FLOAT(5.2))
COMMANDES (NCO INT, NV INT, DAT DATE, QUANTITÉ INT)

Une procédure pour calculer la variance des quantités commandées d'un cru donné en paramètre peut être définie comme suit :

CREATE PROCEDURE (IN C VARCHAR, OUT VARIANCE DECIMAL(10.2))
BEGIN
    DECLARE MOYENNE DECIMAL(10.2);
    VARIANCE = 0;
    SELECT AVG(QUANTITE) INTO MOYENNE
    FROM COMMANDES C, VINS V
    WHERE V.NV = C.NV AND V.CRU = C;
    BOUCLE1:
      FOR M AS SELECT NCO, QUANTITÉ
      FROM COMMANDES C, VINS V
      WHERE V.NV = C.NV AND V.CRU = C
      DO
        SET VARIANCE = VARIANCE+(M.QUANTITE - MOYENNE)**2
    END FOR BOUCLE1;
END

Tout SQL est bien sûr intégré à PSM. Il serait possible d'utiliser un curseur et une boucle WHILE avec un FETCH dans la boucle à la place de la boucle FOR.

Pour montrer la complétude du langage, nous définissons la fonction factorielle comme suit :

CREATE FUNCTION FACTORIELLE (N INT)
RETURNS INT
DETERMINISTIC
BEGIN
    DECLARE FAC INT DEFAULT 1;
    DECLARE I INT;
    SET I = N;
    WHILE I > 1 DO
        FAC = FAC*I;
        I = I-1;
    END WHILE;
    RETURN FAC;
END

D'autres types de boucles peuvent bien sûr être utilisés pour calculer cette fonction [Melton98].

SQL/PSM est un standard international dans sa version SQL2. Il a été intégré en 1996 au standard SQL2. Une version plus complète est en cours de spécification pour SQL3. Cette version présentera l'avantage de pouvoir utiliser les types abstraits et les collections de SQL3 dans le langage de programmation. SQL/PSM est assez proche des langages de quatrième génération de systèmes tels qu'ORACLE, INFORMIX ou SYBASE. Malheureusement, aucun système n'implémente actuellement exactement PSM.