ATTENTION : le contenu des ces pages web est soumis aux dispositions légales françaises et internationales en matière de droits d'auteurs et de copyright.

LE SQL de A à Z

La nouvelle norme SQL:1999 (SQL 3)

La nouvelle norme SQL 3 aussi réréfencée par l'ISO sous le nom de code SQL:1999 possède de nombreux atout pour faire de SQL un langage d'une puissance sans équivalente dans le monde du relationnel objet. Bien peu de SGBDR sont encore capable d'implémenter les fonctionnalités essentielles de cette norme, chaque éditeur tend à rattraper son retard. C'est pourquoi, afin de ne pas être en reste, cet article vous propose de découvrir l'essentiel des différentes facettes des nouveautés introduites par SQL 3... 


Première partie
 

dernière mise à jour : 

Courriel
Par Frédéric BROUARD
 

NOTA : La structure de la base de données exemple, ainsi qu'une version des principales bases utilisées sont disponibles dans la page "La base de données exemple"
 

Avant propos

CLUSTER :
SQL:1999 propose dorénavant la notion de CLUSTER, un nouvel objet, au plus haut de la hiérarchie des objets d'un ensemble de serveurs de bases de données relationnel. Cette notion de cluster se place avant la notion de CATALOG (collections de bases de données) qui elle même est antérieure à la notion de SCHEMA (une base de données).
Elle est censée représentée un ensemble de collection de bases de données par exemple un ensemble de machines physique regroupé sous un nom générique. Elle est donc très proche de la notion de CLUSTER "système" et ce nom n'a donc pas été choisit au hasard !

 

1 Les nouveaux types 

1.1 Types définis 


1.1.1 Les "LOBs" 

SQL:1999 définit 3 nouveau types d'objet destiné à du stockage volumineux (LOB = Large OBject) :
CLOB (Character) et NCLOB (National Character) pour des caractères en ASCII ou en UNICODE et BLOB (Binary) pour du stockage binaire. On doit en principe préciser la longueur de stockage et l'on peut utiliser les abréviations K, M, G pour, respectivement Kilo (1024), Mega (1024²) et Giga (10243).

Exemple 1 :
PRS_PHOTO BLOB (100K)


1.1.2 Booléen 

Ce grand absent des versions antérieures a été rajouté, mais il fonctionne en logique trois état : True, False et NULL (valant UNKNOWN dans le calcul d'un prédicat).


1.1.3 Les tableaux 

En fait la norme SQL:1999 fait état du type "collection" plus que du type "tableau" (ARRAY). Le type ARRAY, n'est pas à proprement parler un type, mais un constructeur de type (type constructor) puisque l'on doit décrire sa structure.

Exemple 2 :
PRS_ADRESSE CHAR(38) ARRAY [4]
Construit une colonne de nom PRS_ADRESSE contenant 4 cellules de 38 caractères.

NOTA : Il n'est pas possible de construire des tableaux multidimensionnels ni des tableaux de tableau.

REMARQUE : un tableau de n élément se comporte comme un VARCHAR, c'est à dire que ses cellules sont créées au fur et à mesure de l'assignation. Autrement dit, si vous ne renseignez qu'une seule ligne du tableau, les autres les lignes ne sont pas lisibles et renvoient par convention la valeur NULL. Enfin, la première cellule porte toujours l'indice 1 (et non zéro comme dans certains langages).

Certaines opérations sur les tableaux sont intéressantes : outre la lecture, la modification et l'écriture dans les cellules, vous pouvez concaténer des tableaux et obtenir la cardinalité d'un tableau (c'est à dire le nombre de cellules), mais aussi assigner toutes les cellules d'un tableau d'un seul coup, à l'aide de la technique du contructeur de tableau valué (array value constructor)...

Exemple 3 :
ARRAY['Monsieur', 'Madame', 'Mademoiselle']

Enfin, on peut spécifier un tableau vide par :
Exemple 4 :
ARRAY[]
dont l'intérêt est de préparer une colonne à recevoir les cellules d'un autre tableau par concaténation...

La concaténation de tableaux s'obtient à l'aide de la fonction CONCATENATE :
Exemple 5 :
CONCATENATE('Monseigneur', ARRAY['Monsieur', 'Madame', 'Mademoiselle'])
CONCATENATE('Monseigneur' WITH ARRAY['Monsieur', 'Madame', 'Mademoiselle'])
Rajoute en fin de tableau la cellule 4 contenant la valeur 'Monseigneur'.

La fonction CARDINALITY renseigne sur le nombre de cellules d'un tableau :
Exemple 6 :
CARDINALITY(ARRAY['Monsieur', 'Madame', 'Mademoiselle'])
donne : 3

Bien entendu, on peut utiliser la fonction CAST pour forcer un transatypage vers un tableau.

Voici maintenant comment on manipule des valeurs dans une colonne de type tableau :
Exemple 7 :
CREATE TABLE T_PERSONNE_PRS
(PRS_ID  INTEGER NOT NULL PRIMARY KEY,
 PRS_NOM CHAR(32),
 PRS_ADR CHAR(38) ARRAY[3])
INSERT INTO T_PERSONNE_PRS (PRS_ID, PRS_NOM, PRS_ADR)
VALUES (1, 'DUPONT', ARRAY['1 rue de la liberté', 'bâtiment D', NULL])
UPDATE T_PERSONNE_PRS
SET PRS_ADR[3] = 'BP 484'
SELECT PRS_ADR[1]
FROM T_PERSONNE_PRS


1.1.4 Ligne 

SQL:1999 propose un type ligne, permettant de définir un sous ensemble composite de données un peu à la manière des "record" de certains langages comme Pascal.
La syntaxe de la définition d'une ligne commence par le nom de type ROW :
ROW (<col1> [,<col2>...])
Coln ::= nom type [reference] [COLLATE collation] 

Exemple 8 :
CREATE TABLE T_PERSONNE_PRS
(PRS_ID  INTEGER NOT NULL PRIMARY KEY,
 PRS_NOM CHAR(32),
 PRS_ADR ROW (LIGNE CHAR(38) ARRAY[3],
              VILLE CHAR(32),
              CODE_POSTAL CHAR(5)))
Définit une colonne de nom PRS_ADR de type ligne contenant un tableau de 3 lignes d'adresse, suivit d'une colonne contenant la ville et d'une colonne contenant le code postal.

Pour assigner des valeurs à une ligne, on peut utiliser la technique du constructeur de ligne valuées (row value constructor) :
Exemple 9 :
ROW (ARRAY['1 rue de la liberté', 'bâtiment D', NULL], 'PARIS', '75015')

Les comparaisons de lignes peuvent s'effectuer globalement :
Exemple 10 :
...
WHERE PRS_ADR = ROW((ARRAY['1 rue de la liberté', 'bâtiment D', 'BP 484'], 'PARIS', '75015')

On peut aussi utiliser une notation pointée pour accéder à une colonne d'une ligne :
Exemple 11 :
...
WHERE PRS_ADR.LIGNE[3] = 'BP 484'


1.1.5 Soustables et supertable 

SQL:1999 permet de définir des tables héritées de tables. La table racine est dite Supertable et les tables héritées sont dites sous tables.
Voici un exemple de supertable et de soustable :

Exemple 12 :

-- la super table
CREATE TABLE T_PERSONNE_PRS
(PRS_ID INTEGER NOT NULL PRIMARY KEY,
 PRS_NOM CHAR(32) NOT NULL,
 PRS_PRENOM VARCHAR(32),
 PRS_DATE_NAISSANCE DATE)
-- une sous table
CREATE TABLE T_EMPLOYEE_EMP UNDER T_PERSONNE_PRS
(EMP_MATRICULE CHAR(8) NOT NULL)
-- une autre sous table
CREATE TABLE T_CLIENT_CLI UNDER T_PERSONNE_PRS
(CLI_REMISE_NEGOCIEE FLOAT)


1.1.6 Ambiguité de notation SQL

Comme nous venons de le voir pour les tableaux ARRAY et les lignes ROW, il est nécessaire d'utiliser une notation pointée pour obtenir la données finale qui se situé dans la cellule du tableau ou dans la colonne de la ligne. Cependant, cette notation pointée était jusqu'ici utilisée pour le surnommage des tables via la notion d'alias. Dès lors des ambiguité de notation peuvent se produire.

En voici un exemple :
Exemple 13 :
CREATE TABLE T1
(C2 ROW (T2 CHAR(8),
         C1 DATE))

CREATE TABLE C2
(T2 DATE)
SELECT C2.T2
FROM T1, C2

L'exécution de la requête va provoquer une erreur SQL car il n'est pas possible de savoir si C2.T2 se réfère à la table C2 ou à la colonne C2.T2 de la table T1 !

Rassurez-vous, la norme SQL permet de lever cette ambiguité en utilisant une notation parenthèsée :  
Exemple 14 :
SELECT (C2).T2
FROM T1, C2
Cette requête récupère la colonne T2 de la table C2 avec une jointure croisée (produit cartésien ou CROSS JOIN) des tables T1 et C2.

En conclusion, pour lever toute ambiguité, il convient de spécifier la table cible entre parenthèses. Le mieux est bien évidemment de choisir soigneusement les noms des objets, notamment en appliquant une charte interne de développement telle que décrite dans cette page : http://sqlpro.developpez.com/Methode/SGBDR_nom.html
 

1.1.7 Référence 

Un type référence (REF) est une colonne qui pointe vers une ligne d'une autre table. Cela permet d'automatiser le principe des tables de référence.  Ce point est développé plus loin.


1.1.8 Pointeur (LOCATOR) 

Afin de faciliter la migration de données de très fort volume la norme SQL:1999 a prévu l'utilisation d'un attribut pointeur (LOCATOR) que toute colonne peut possèder, permettant de stocker les données côté client, un peu à la manière de ce que fait un navigateur Web en plaçant les ressources sur le poste client afin de minimiser les flux sur le réseau. Mais le mécanisme n'est pas automatique et c'est au développeur de monter les ressources avant toute requête SQL utilisant un tel pointeur.

Voici un exemple de déclaration de table utilisant un locator pour stocker les images des personnes côté client :

Exemple 15 :
CREATE TABLE T_PERSONNE_PRS
(PRS_ID  INTEGER NOT NULL PRIMARY KEY,
 PRS_NOM CHAR(32),
 PRS_ADR ROW (LIGNE CHAR(38) ARRAY[3],
              VILLE CHAR(32),
              CODE_POSTAL CHAR(5)),
 PRS_PHOTO BLOB (100K) LOCATOR)


1.1.9 Site 

Le mot SITE a été ajouté au référentiel des mots réservé de SQL et concerne "tout endroit occupé par une instance de la valeur d'un type de données spécifié". En pratique il s'agit de regrouper sous un terme générique tout objet du schema entreprosant des données (colonne de table, paramètre, variable, procédure stockée, structure de type...)
 

1.1.10 Les futurs types SQL:2003 (?)

Bien qu'ils aient été discutés lors de la norme SQL:1999, les types SET, MULTISET et LIST, n'ont, en définitive, pas été encore adoptés.
SET est une collection de différentes instances d'un même type. MULTISET basé sur le SET permet d'avoir des instances identiques. LIST est une collection de différentes instances ordonnées d'un même type.


1.2 Les types "utilisateurs" ou UDT (User Data Type)

SQL:1999 introduit la possibilité de définir trois formes de nouveaux types : les types distincts (typage fort), les types hérités et les types abstraits.


1.2.1 Types DISTINCT 

Un type DISTINCT est un type bâtit depuis un type ordinaire de SQL comme le type INTEGER, avec cette particularité que, même dérivé d'un type de base comme INTEGER il ne peut être combiné dans des opérations de calcul ou de comparaisons qu'avec des types de même nature ou à l'aide d'opération de transtypage. En un mot il s'agit d'un typage fort.

NOTA : le type DISTINCT n'a rien à voir avec la notion de domaine qui correspond à un type de base associé à un nombre indéterminé de règles.  Voir : http://sqlpro.developpez.com/SQL_AZ_7.html#SCHEMA54 .

Il s'agit de créer de toutes pièces de nouveaux types de données ce qui n'était pas possible dans la notion de domaine qui couvrait un type existant restreint à des règles de validation.

La syntaxe pour exprimer un  type DISTINCT est :
CREATE DISTINCT TYPE <nom_type>
       AS <type_sql>
       FINAL

Le mot clef FNAL indique que ce type est un type instanciable et peut donc être utilisé dans la définition d'une colonne de table. Il est obligatoire...

Exemple 16 :
CREATE DISTINCT TYPE MONNAIE_FRANC AS DECIMAL(16,2) FINAL
CREATE DISTINCT TYPE MONNAIE_EURO AS DECIMAL(16,2) FINAL 

Dès lors l'utilisateur dispose d'un typage fort lié au type de colonne de la table. On utilise un tel type, comme un type SQL habituel dans la définition d'une table.
Exemple 17 :
CREATE TABLE T_COMPTABILITE_CPT
(CPT_ID                 INTEGER        NOT NULL PRIMARY KEY,
 CPT_LIBELLE_ECRITURE   VARCHAR(32)    NOT NULL,
 CPT_DATE               DATE NOT       NULL DEFAULT CURRENT_DATE,
 CPT_DEBIT_FRANC        MONNAIE_FRANC  NOT NULL DEFAULT 0 CHECK VALUE >=0,
 CPT_DEBIT_EURO         MONNAIE_EURO   NOT NULL DEFAULT 0 CHECK VALUE >=0,
 CPT_CREDIT_FRANC       MONNAIE_FRANC  NOT NULL DEFAULT 0 CHECK VALUE >=0,
 CPT_CREDIT_EURO        MONNAIE_EURO   NOT NULL DEFAULT 0 CHECK VALUE >=0)

Désormais toute comparaison et opération entre types distinct différents est impossible.
Ainsi la requête suivante :
Exemple 18 :
SELECT *
FROM   T_COMPTABILITE_CPT
WHERE  CPT_DEBIT_FRANC * 6.55957 = CPT_DEBIT_EURO
va générer une erreur et cela afin de ne pas mélanger les serviettes et les torchons !
En effet on ne peut comparer que ce qui est comparable. L'adition de litre d'essence avec des heures de vol n'ayant aucun sens, la comparaison directes de valeurs financières de différentes monnaies n'a pas non plus de sens... à moins que vous ne spécifiez dans votre requête une conversion explicite...

Exemple 19 :

SELECT *
FROM   T_COMPTABILITE_CPT
WHERE  CAST(CPT_DEBIT_FRANC AS FLOAT) * 6.55957 = CAST(CPT_DEBIT_EURO AS FLOAT)

Nous verrons qu'un type utilisateur peut être construite avec des méthodes (type abstrait), dont certaines peuvent servir à définir des opérations de transtypage. Ce point sera discuté dans la seconde partie de la présente étude...


1.2.2 Type hérité

Un type hérité est un type qui prend pour référence un type parent et lui ajoute ses propres éléments. Il s'agit de l'héritage classique que l'on trouve dans la plupart des langages de programmation actuels, connus sous le terme générique "langage objet".

NOTA : SQL:1999, ne supporte que l'héritage simple.

Voici,  un exemple de type hérité, de "super type" et de "sous type" :
Exemple 20 :

CREATE TYPE oeuvre
AS (titre VARCHAR(256),
    date_creation DATE))
NOT INSTANTIABLE
NOT FINAL

CREATE TYPE original UNDER oeuvre
INSTANTIABLE
NOT FINAL

CREATE TYPE objet UNDER oeuvre
AS (longueur FLOAT,
    largeur  FLOAT,
    hauteur  FLOAT,
    poids    FLOAT)
NOT INSTANTIABLE
NOT FINAL

CREATE TYPE peinture UNDER objet
AS (support VARCHAR(64))
INSTANTIABLE
NOT FINAL

CREATE TYPE sculpture UNDER objet
AS (matière VARCHAR(32))
INSTANTIABLE
FINAL

CREATE TYPE livre UNDER oeuvre
AS (date_premiere_edition DATE,
    langue varchar(32))
INSTANTIABLE
FINAL      

"oeuvre" est un super type. C'est l'ancètre de tous les types que nous allons dériver. Notons que "oeuvre" n'est pas instantiable. C'est  une coquille qui sert  de "moule" pour réaliser nos autres types...

Ainsi "original" est  un type instantiable, c'est à dire que l'on peut s'en servir pour y placer une oeuvre dotée des seules attributs titre et date_creation.

"objet" est à nouveau un titre non instanciable dans le sens ou il sert de moule complémentaire à oeuvre, en lui adjoignant de nouvelles caractéristiques.

"peinture" est un type intanciable dont les caractéritiques sont, en définitive : titre, date_creation, longueur, largeur, hauteur, poids et support.

De même "sculpture" est aussi un type intanciable dont les caractéritiques sont, en définitive : titre, date_creation, longueur, largeur, hauteur, poids et matière. Notez qu'il ne peut être repris pour créer un nouveau sous type du fait du mot clef FINAL.

Enfin "livre" est un type instanciable construit  à partir du type générique oeuvre. Il possède donc les caractéritiques suivantes : titre, date_creation, date_premiere_edition, langue. Notez, comme dans le cas de "sculpture" qu'il ne peut être repris pour créer un nouveau sous type du fait du mot clef FINAL.

INSTANTIABLE signifie donc que le type peut servir à la défintion d'une variable comme d'une colonne de table. Dans le cas contraire (NOT INSTANTIABLE) il ne peut en aucun cas être utilisé pour y recevoir des valeurs et ne sert qu'à la défintion d'autres types (en un sens il s'agit d'un objet abstrait).

"original", "objet", "peinture", "sculpture" et "livre" sont des sous types.

Voici une syntaxe de base pour la définition d'un type SQL :
CREATE TYPE <nom_type> [ UNDER nom_super_type ]
   [ <external java type clause> ]
   [ AS représentation ]
   [ [ NOT ] INSTANTIABLE ]
   [ [ NOT ] FINAL ]

[ <external java type clause> ] permet d'utiliser un type d'après les types du langage Java;
[ AS représentation ] définition de la liste des attributs et de leurs types;
[ [ NOT ] INSTANTIABLE ] peut (ou ne peut) être utilisé pour la définition d'une colonne (objet abstrait);
[ [ NOT ] FINAL ] ne peut pas (ou peut) être repris pour la définition d'un sous type.


1.2.3 Types abstraits (ADT)

Nous aborderons les types abstraits dans la deuxième partie de cette étude. En effet il semble intéressant d'aborder à la fois les types abstraits et le code procédural permettant de créer des méthodes attachées aux tyapes abstrait. Notons cependant qu'avec les types abstraits nous entrons vraiment dans un logique proche de l'implémentation objet. En effet un type abstrait peut être construit sous la forme d'une structure de données (attributs) et possèder des routines qui lui sont applicables (méthodes). Le type abstrait peut aussi être construit par héritage.


1.3 Tables "type"

Il est possible de créer une table à partir d'un type utilisateur :

CREATE TABLE <nom_table>
   OF <non_type_utilisateur>
   [ UNDER nom_super_type ]
   [ <clause_de_soustable> ]
   [ <liste_élément_table> ]
   <clause_de_soustable> ::=
      UNDER <super_table>
     <clause_de_soustable> ::=
         <nom_table>
   <liste_élément_table> ::=
      ( <élément_table> [ { , <élément_table> } ... ]
      <élément_table> ::=
         <définition_contrainte_table>
         | <specification_colonne_auto_référencée>
         | <option_colonne>
         <specification_colonne_auto_référencée> ::=
            REF IS <nom_colonne_auto_reference> <generation_reference>
            <nom_colonne_auto_reference> ::=
               <nom_colonne>
            <generation_reference> ::=
               SYSTEM GENERATED
               | USER GENERATED
               | DERIVED
         <option_colonne> ::=
            <nom_colonne> WITH OPTIONS <liste_option_colonne>
            <liste_option_colonne> ::=
                [ <clause_scope> ]
                [ <clause_defaut> ]
                [ <definition_contrante_colonne> ]
                [ <clause_collation> ]

Un exemple va permettre de mieux comprendre l'utilité de ce type et sa syntaxe.

Supposons que nous voulons créer un type disque (contenant des chansons)
Exemple :
CREATE TYPE DISQUE
AS (titre VARCHAR(256),
    date_creation DATE,
    nombre_plage INTEGER)
INSTANTIABLE
NOT FINAL
REF IS SYSTEM GENERATED
Comme ce type va servir à générer des sous type il est indispensable de préciser comment la référence à une ligne de ce type est générée. Ici c'est le SGBDR qui s'occupe de générer la valeur de référence, le "pointeur" en quelque sortes (REF IS SYSTEM GENERATED).

A partir de ce type nous pouvons créer le type CD :
Exemple :
CREATE TYPE CD UNDER DISQUE
AS (editeur VARCHAR(32),
    numero_serie CHAR(16)))
INSTANTIABLE
NOT FINAL

Dès lors notre CD comporte 5 colonnes : celles du disque et celles propre au CD.
Maintenant, voyons comment créer des tables à partir de ces types :
Exemple :
CREATE TABLE T_CD OF CD
(REF IS CD_ID SYSTEM_GENERATED)

Nous étudierons plus tard ce qu'est la notion de référence mais notez que ce mécanisme permet de créer une colonne particulière dont la valeur "transparente" est unique et peut servir de référence à la manière d'un pointeur. Dans le présent cas, la colonne CD_ID de type REF possède une valeur génére par le SGBDR...

Créons maintenant une table permettant de spécifier des CD "courts" (des "single" comportant généralement deux titres !) :
Exemple :
CREATE TABLE T_SINGLE_CD OF CD
UNDER T_CD
( nombre_plage WITH OPTIONS
  CONSTRAINT pas_plus_de_deux_titres CHECK (nombre_plage <= 2 ))
Cette nouvelle table emprunte sa structure à la table T_CD depuis le type CD et ajoute une contrainte àa la colonnes nombre_plage.

T_CD est une table "type". Notons qu'à ce stade une table "type" ne peut qu'être ancètre, jamais sous table et doit toujours comporter  une déclaration de référence.

2 Jeux de caractères et collations  

Lors de la création d'une base, d'une table ou d'une colonne de table, il est possible de préciser le jeu de caractères et la collation. De plus la notion de "coercitivité", c'est à dire les contraintes portant sur les conversions, concaténation et autres opérations spécifiques aux chaînes de caractères.

Ainsi, la défintion d'une colonne de données contenant des caractères répond à la syntaxe suivante :

<col name> { CHAR | VARCHAR | NCHAR | NVARCHAR | CLOB | NCLOB } (lenght)
   [ CHARACTER SET <char set> ]
   [ COLLATE <collation> ]
   <char set> :: =
      SQL_CHARACTER
      | LATIN1
      | ISO8BIT
      | CODEPAGE_0437
      | ...
   <collation> :: =
      FRENCH
      | RUSSIAN_GEORGIAN
      | EBCDIC
      | ISO8859_1
      | ...

Bien entendu la liste des jeux de caractères, comme celle des collations est limitée dans la norme SQL et ne correspond pas forcément à ce que les éditeurs de SGBDR proposent. De plus une collation donnée,  n'est disponible en général que pour un jeu de caractères spécifié !

Si votre SGBDR le permet, utilisez les vues d'informations de schema pour retrouver les jeux et collations que vous pouvez spécifier. Les vues sont :
   INFORMATION_SCHEMA.CHARACTER_SETS
   INFORMATION_SCHEMA.COLLATIONS


2.1 jeux de caractères

Il est possible de créer son propre jeu de caractères basé sur un jeu donné à l'aide de l'ordre SQL :

CREATE CHARACTER SET <schema>.<char new name> [ AS ] GET <predefined char set>

Bien entendu un tel jeu de caractère est créé pour le schema entier (la base de données).

Il est possible de supprimer un jeu de caractère créé à l'aide d'un ordre DROP CHARACTER SET.


2.2 Collations

La collation est l'ordre dans lequel les caractères doivent être spécifié.

On peut créer une collation à l'aide de l'ordre SQL :

<collation definition> ::=
    CREATE COLLATION <collation name> FOR
        <character set specification>
      FROM <collation source>
        [ <pad attribute> ]
   <pad attribute> ::=
       NO PAD
       | PAD SPACE

Une collation est crée d'après une autre collation en y ajoutant le paramètre "pad attribute" qui spécifie si les comparaisons entre chaines de longueurs différentes (par exemple 2 colonnes de type VARCHAR(16) et CHAR(8) contenant les valeurs 'toto' et 'toto    ') doivent être considérées comme identiques ou non.


2.3 Translation

Une translation définit la manière de passer d'une collation à une autre afin de comparer des chaines basées sur des jeux ou des collations différentes.

Bien entendu votre SGBDR possède des translations prédéfinies s'ils supporte les jeux et collations afin d'effectuer le travail implicitement. Mais vous pouvez spécifier une translation particulière. Dans ce cas il faut la créer à l'aide de l'ordre SQL :

<translation definition> ::=
    CREATE TRANSLATION <translation name>
      FOR <source character set specification>
      TO <target character set specification>
      FROM <translation source>
   <source character set specification> ::=
      <character set specification>
   <target character set specification> ::=
      <character set specification>
   <translation source> ::=
      <translation specification>
    | <translation routine>
      <translation specification> ::=
         <external translation>
            | IDENTITY
            | <schema translation name>
            | <internal translation source>
         <external translation> ::=
            EXTERNAL ( ' <external translation name> ' )
            <external translation name> ::=
               <standard translation name>
               | <implementation-defined translation name>
               <standard translation name> ::=
                  <translation name>
               <implementation-defined translation name> ::=
                  <translation name>
         <schema translation name> ::=
            <translation name>
         <internal translation source> ::=
            ( <translation options> )
            <translation options> ::=
               <translation option> [ { , <translation option> } ... ]
               <translation option> ::=
                  USING ( <translation basis> )
                  | MODIFY ( <translation modifiers>)
                  <translation basis> ::=
                     <translation definition> [ { * <translation definition> } ... ]
                  <translation modifiers> ::=
                     <translation modifier> [ { , <translation modifier> } ... ]
                     <translation modifier> ::=
                        <collating chars> = <collating chars>
      <translation routine> ::=
      ROUTINE ( <implementation-defined routine name> ( <params> ))

Autrement dit, on peut spécifier une collation, soit par une liste odonnées des caractères avec la possibilité de définir des équivalnces soit en utilisant une ROUTINE c'est à dire une fonction.

Exemple 21 :

CREATE TRANSLATION TRL_SANS_DIACRITIQUE
   FOR ISO8BIT
   TO  ISO8BIT
   FROM MODIFY ( 'à' = 'a', 'b', 'c', 'ç' = 'c', 'd', 'e', 'é' = 'e', 'è' = 'e', 'f', 'g'
                ... )

Ici nous créons de toutes pièces une translation basé sur le fait que des caractères diacritiques (accents, cédille...) sont considérés comme strictement équivalent à la lettre "nue" !

Néanmoins, pour passer d'une collation à l'autre dans le cadre des prédicats SQL, on peut utiliser la précision de collation afin que la translation sous jacente puisse s'opérer.

Exemple 22 :

CREATE TABLE T_DIFFERENT_COLLATIONS_DCS
(DCS_COL1 VARCHAR(16) COLLATE SQL_LATIN1,
 DCS_COL2 VARCHAR(16) COLLATE SQL_EBCDIC)

INSERT INTO T_DIFFERENT_COLLATIONS_DCS VALUES ('toto', 'toto')

SELECT 1 AS REPONSE
FROM T_DIFFERENT_COLLATIONS_DCS
WHERE DCS_COL1 = DCS_COL2
Serveur : Msg 446, Niveau 16, État 9, Ligne 1
Impossible de résoudre le classement en conflit de l'opération equal to.

Le serveur n'est pas capable d'interpréter les deux données comme étant équivalentes. En effet les données binaires sont différentes et le serveur informe que cette opération n'est pas effectué du fait de cette différence. Pour résoudre ce conflit, on peut préciser la collation en usage à utiliser pour une translation implicite :

Exemple 23 :

SELECT 1 AS REPONSE
FROM T_DIFFERENT_COLLATIONS_DCS
WHERE DCS_COL1 = DCS_COL2 COLLATE SQL_LATIN1
REPONSE          
-----------
1 


2.4 Coercitivité

La coercivité est une notion qui définit si les chaînes de caractère passée en argument lors d'opération de chaîne est interprétable vis à vis des transformations ou comparaisons opérées. Ainsi une expression sera jugée "coercible" (coertion implicite) ou non, voire "coercitible", c'est à dire pouvant devenir coercible si application d'une translation (coertion explicite).

Nous ne nous étendrons pas sur ce sujet qui n'offre pas de grande utilité en pratique que celui de guider l'utilisateur dans le choix de son jeu de caractères et collation au regard des autres possibilité et le guide dans la rédaction de ses requêtes.


NOTA : pour compléter votre culture sur le sujet, se reportet à : une question de caractères... 

3 Fonctions

3.1 nouvelles


3.1.1 OVERLAY 

Overlay est une fonction opérant sur des chaînes de caractères et proposant le remplacement de caractères à un offset et sur une longueur précise. Sa syntaxe est :
OVERLAY ( donnée_cible SIMILAR donnée_source FROM offset FOR longueur)

Exemple 24 :
SELECT OVERLAY ('WEST SIDE STORY' PLACING 'BRONXVILLE' FROM 6 FOR 4)
FROM ...
WEST BRONXVILLE STORY

Elle permet le remplacement de chaînes de caractères. Utilisée conjointement avec POSITION, CHARACTER_LENGTH et SUBSTRING, elle permet des manipulations comme le  REPLACE ou le  TRANSLATE de certains SGBDR (Sybase, SQL Server, Oracle, notamment)


3.1.2 CARDINALITY 

Nous l'avons vue en 1.1.3. Elle permet d'obtenir la cardinalité, c'est à dire le nombre de cellules d'un tableau.


3.1.3 ABS et MOD 

Permettent respectivement d'obtenir la valeur absolue et le modulo


3.1.4 LOCALTIME et LOCALTIMESTAMP 

Sont des fonctions non déterministes permettant d'obtenir l'heure locale et la date/heure locale lorsqu'une données fait référence à un fuseau horaire (TIME WITH TIME ZONE  ou  TIMESTAMP WITH TIME ZONE).


3.1.5 INTERVAL 

Opère sur des données temporelles et retourne une durée jamais négative quelque soit le sens des paramètres passés. En un sens elle est strictement équivalente à : ABS(date | time | timestamp -  date | time | timestamp)


3.2 Modifiée 

A la fonction SUBSTRING a été rajouté le mot clef SIMILAR proposant une extension de recherche comparative, très vaste, reposant sur la technique des expressions régulières d'UNIX.
La syntaxe de cette nouvelle forme est :
SUBSTRING ( donnée_source SIMILAR modèle ESCAPE caractère_échappement)

Nous étudierons le fonctionement de SIMILAR au chapitre consacré aux prédicats.


4 Prédicats

4.1 logique de base : IS TRUE...

Le prédicat :

<predicat> IS [NOT] { TRUE | FALSE | UNKNOWN }

a été rajouté à SQL 3. Il permet de rendre une logique binaire à une logique qui était jusqu'à présent ternaire !
En effet, lors de comparaisons avec absence de valeur, le prédicat prend la valeur UNKNOWN qui, n'étant ni vrai ni faux, ne prend jamais en compte les données absentes.

Exemple 25 :
CREATE TABLE T_TEST_UNKNOWN_TUK
(TUK_ID INT,
 TUK_NOM VARCHAR(16))		 INSERT INTO T_TEST_UNKNOWN_TUK VALUES (1, 'toto')
INSERT INTO T_TEST_UNKNOWN_TUK VALUES (2, NULL)
SELECT *
FROM   T_TEST_UNKNOWN_TUK
WHERE  TUK_NOM = 'toto'		 SELECT *
FROM   T_TEST_UNKNOWN_TUK
WHERE  TUK_NOM <> 'toto'
TUK_ID      TUK_NOM         
----------- ----------------
1           toto
 TUK_ID      TUK_NOM         
----------- ----------------

Comme nous le voyons dans cet exemple, le complément du sous ensemble TUK_NOM = 'toto' n'est pas récupére à l'aide du filtre TUK_NOM <> 'toto' du fait de la présence des valeurs NULL.

Pour y remédier :
Exemple 26 :
SELECT *
FROM   T_TEST_UNKNOWN_TUK
WHERE  TUK_NOM = 'toto' IS TRUE
 SELECT *
FROM   T_TEST_UNKNOWN_TUK
WHERE  TUK_NOM = 'toto' IS NOT TRUE
TUK_ID      TUK_NOM         
----------- ----------------
1           toto
 TUK_ID      TUK_NOM         
----------- ----------------
2           NULL


4.2 comparaison de chaînes : IS SIMILAR TO   

En complément  au LIKE, SQL:1999 propose SIMILAR, un prédicat de comparaison de chaînes de caractères basé sur le modèle des expressions régulières. Loin de moi l'idée de vous faire un cours sur le sujet, car même s'il s'avère relativement complexe, il existe de bon bouquins sur le sujet, comme celui de Jeffreay E. F. Friedl "Maîtrise des Expressions régulières" chez O'Reilly.

Mais voici quelques éléments ...

Tout d'abord la syntaxe :

<string_value> IS [ NOT ] SIMILAR TO <pattern value> [ ESCAPE <escape value> ]
   <pattern value> ::=
      <string value>
      | <regular expression>
      <regular expression> ::=
         <regular term>
         | <regular expression> <vertical bar> <regular term>
         <regular term> ::=
            <regular factor>
            | <regular term> <regular factor>
            <regular factor> ::=
               <regular primary>
               | <regular primary> *
               | <regular primary> +
               <regular primary> ::=
                  <character specifier>
                  | %
                  | <regular character set>
                  | ( <regular expression> )
                  <character specifier> ::=
                     <non-escaped character>
                     | <escaped character>
                  <regular character set> ::=
                     <underscore>
                     | ( <character enumeration> ... )
                     | ( ^ <character enumeration> ... )
                     | ( : <regular charset id> : )
                     <character enumeration> ::=
                        <character specifier>
                        | <character specifier> - <character specifier>
                     <regular charset id> ::=
                         ALPHA
                         | UPPER
                         | LOWER
                         | DIGIT
                         | ALNUM

Quelques exemples pour mieux comprendre :
Exemple 27 :
SELECT CLI_ID, CLI_NOM
FROM   T_CLIENT
WHERE  CLI_NOM IS SIMILAR TO '(DUPONT)|(DUVAL)'
 SELECT CLI_ID, CLI_NOM
FROM   T_CLIENT
WHERE  CLI_NOM IS SIMILAR TO '(DUPON[DT])|(DUVAL)'
CLI_ID      CLI_NOM                          
----------- --------------------------------
1           DUPONT                         
12          DUVAL                            		 CLI_ID      CLI_NOM                          
----------- --------------------------------
1           DUPONT                         
12          DUVAL  
103         DUPOND                
-- Recherche les clients nommés DUPONT ou DUVAL
-- équivalent à :
 -- Recherche les clients nommés DUPONT ou DUPOND ou DUVAL
-- équivalent à :
SELECT CLI_ID, CLI_NOM
FROM   T_CLIENT
WHERE  CLI_NOM = 'DUPONT'
  OR   CLI_NOM = 'DUVAL'

 SELECT CLI_ID, CLI_NOM
FROM   T_CLIENT
WHERE  CLI_NOM = 'DUPOND'
   OR  CLI_NOM = 'DUPONT'
   OR  CLI_NOM = 'DUVAL'

Exemple 28 :    
SELECT CLI_ID, CLI_PRENOM
FROM   T_CLIENT
WHERE  CLI_PRENOM IS SIMILAR TO '%[éëèê]%'		 SELECT CLI_ID, CLI_PRENOM
FROM   T_CLIENT
WHERE  CLI_PRENOM IS SIMILAR TO'[:ALPHA:]+[e]'
CLI_ID      CLI_PRENOM               
----------- -------------------------
12          Arsène
13          André
15          Amélie
19          Gérard
30          Hervé
34          Noëlle
40          Gérard
48          René
55          Gérard
71          André
76          Joël
84          Frédéric
85          Régis
92          Michèle
 CLI_ID      CLI_PRENOM               
----------- -------------------------
8           Evelyne
9           Martine
12          Arsène
15          Amélie
24          Dominique
34          Noëlle
35          Dominique
36          Pierre
39          Lucette
41          Philippe
42          Monique
51          Jacqueline
56          Jacqueline
61          Pierre
72          Philippe
74          Nathalie
83          Colette
87          Christophe
90          Christophe
91          Pierre
92          Michèle
95          Alexandre
96          Fabrice
-- les prenoms avec au moins un 'e' accentué
-- requête équivalente à :
 -- les prénoms ne contenant que des lettres et se terminant par un 'e' (*)
-- équivalence possible, mais requête très longue !
SELECT CLI_ID, CLI_PRENOM
FROM   T_CLIENT
WHERE  CLI_PRENOM LIKE '%é%'
   OR  CLI_PRENOM LIKE '%ë%'
   OR  CLI_PRENOM LIKE '%è%'
   OR  CLI_PRENOM LIKE '%ê%'		 SELECT CLI_ID, CLI_PRENOM
FROM   T_CLIENT
WHERE  CLI_PRENOM LIKE '%e'
  AND  COALESCE(SUBSTRING(CLI_PRENOM FROM 1 FOR 1), ' ')
       IN ( 'A', 'a', 'B', 'b', 'c', ...
  AND  COALESCE(SUBSTRING(CLI_PRENOM FROM 2 FOR 1), ' ')
       IN ( 'A', 'a', 'B', 'b', 'c', ...
  AND  COALESCE(SUBSTRING(CLI_PRENOM FROM 3 FOR 1), ' ')
       IN ( 'A', 'a', 'B', 'b', 'c', ...
  ...
  AND  COALESCE(SUBSTRING(CLI_PRENOM FROM 25 FOR 1), ' ')
       IN ( 'A', 'a', 'B', 'b', 'c', ...

(*) Sont donc éliminé, les prenoms contenant un caractère tel que le trait d'union ou se terminant par une autre lettre que "e".


4.3 comparaison de lignes valuées : IS DISTINCT FROM 

Pour compléter la logique de calculs des lignes valuées (ROW VALUE) dont  on trouvera quelques explications ici (constructeur de ligne valuée), SQL:1999 propose de pouvoir comparer des lignes valuées, notamment en présence de valeurs absentes.

Explications : les comparaions suivantes, donnent lieu aux résultats ci dessous :
Exemple 29 :

('toto', 123, CAST('2003-10-21' AS DATE)) <> ('toto', 123, CAST('2003-10-21' AS DATE)) FALSE
('toto', 123, NULL) <> ('toto', 123, CAST('2003-10-21' AS DATE)) UNKNOWN
('toto', 123, NULL) <> ('toto', 123, NULL) UNKNOWN

On aurais pu croire que le troisième exemple donne vrai, mais il faut se souvenir que les marqueurs NULL se propagent dans les calculs et les comparaisons et donnent oinévitablement des valeurs inconnes, car on ne peut comparer ni calculer ce qui n'est pas. Pour contourner ce problème, SQL:1999 introduit un nouveau comparatif traitant ces absences de valeurs dans le cas de la comparaison des lignes valuées. C'est le prédicat IS DISTINCT FROM, dont la syntaxe est :

<row value 1>  IS DISTINCT FROM <row value 2>

Exemple 30 :
('toto', 123, CAST('2003-10-21' AS DATE)) IS DISTINCT FROM ('toto', 123, CAST('2003-10-21' AS DATE)) FALSE
('toto', 123, NULL) IS DISTINCT FROM ('toto', 123, CAST('2003-10-21' AS DATE)) UNKNOWN
('toto', 123, NULL) IS DISTINCT FROM ('toto', 123, NULL) FALSE

Un marqueur NULL ne saurait être comparée à une valeur précise. C'est pourquoi la ligne 2 ressort à inconnue.
Les spécificités du prédicat IS DISTINCT FROM font que la comparaison pour une correspondance de colonnes dont les valeurs sont absentes de part et d'autre est ignorée et le calcul de la comparaion s'opère sur les autres colonnes. C'est dans cette optique que la 3eme ligne ressort à FALSE !

Le seul inconvénient de ce prédicat, c'est que la norme SQL a oublié d'introduire en son sein, un opérateur de négation.
Ainsi l'écriture de :
Exemple 31 :

('toto', 123, NULL) IS NOT DISTINCT FROM ('toto', 123, NULL)

Est illégale. Il faut écrire :
Exemple 32 :
 
NOT (('toto', 123, NULL) IS DISTINCT FROM ('toto', 123, NULL))

Et il faut ben avouer que c'est un peu lourd !
 

4.4 comparaison de type : IS OF 

En complément aux nouveaux types il était nécessaire de pouvoir comparer le type d'une donnée par rapport à un autre type ou un ensemble de types utilisateur.

La syntaxe de ce prédicat est la suivante :

<udt value expression> IS [ NOT ] OF ( [ ONLY ] <udt name1> [ { , <udt name2> } ... ] )

Ce prédicat permet de comparer un UDT dont on fournit la valeur à un ensemble de types. Au moins un des types doit correspondre pour que le prédicat soit vrai.
ONLY spécifie si la comparaison doit porter


4.5 Quantificateur : FOR ALL, FOR SOME

Fortier 369###


 
 

5 Expression dans les requêtes

5.1 Clause WITH et récursion

La clause WITH permet de spécifier une expression de table (par exemple un SELECT) qui va être repris dans le corps de l'ordre SQL.
La syntaxe de cette clause est :

<with clause> ::=
   WITH [ RECURSIVE ] <with list>
   <with list> ::=
       <with list element> [ { ,  <with list element> } ... ]
       <with list element> ::=
          <query name> [ ( <with column list> ) ]
          AS ( <query expression> )
          [ <search or cycle clause> ]
         <search or cycle clause> ::=
            <search clause>
            | <cycle clause>
            | <search clause> <cycle clause>
            <search clause> ::=
               SEARCH <recursive search order> SET <sequence column>
               <recursive search order> ::=
                  DEPTH FIRTS BY <sort specification list>
                  | BREADTH FIRST BY <sort specification list>
            <cycle clause> ::=
               CYCLE <cycle column list>
               SET <cycle mark column> TO <cycle mark value> DEFAULT <non cycle mark value>
               USING <path column>
               <cycle column list> ::=
                  <cycle column> [ { , <cycle column> } ... ]
<query name> nom que vous donnez à l'expression de requête
<with column list>, <cycle mark column>, <path column>, <cycle column>, <sequence column> colonne 
<cycle mark value>, <non cycle mark value> valeur


5.1.1 Utilisation sans récusion pour simplification d'expression de sous requêtes

Cette syntaxe peut être utilisée en dehors de la récursion pour simplifier l'expression d'une requête.
Exemple :
 
SELECT TRF_CHB_PRIX, C.CHB_ID, CHB_NUMERO, CHB_COUCHAGE
FROM   T_TARIF T
       INNER JOIN TJ_TRF_CHB TC
             ON T.TRF_DATE_DEBUT = TC.TRF_DATE_DEBUT
       INNER JOIN T_CHAMBRE C
             ON TC.CHB_ID = C.CHB_ID
WHERE  T.TRF_DATE_DEBUT = '2001-01-01'
  AND  TRF_CHB_PRIX BETWEEN (SELECT  AVG(TRF_CHB_PRIX)
                             FROM   T_TARIF T
                                    INNER JOIN TJ_TRF_CHB TC
                                          ON T.TRF_DATE_DEBUT = TC.TRF_DATE_DEBUT
                                    INNER JOIN T_CHAMBRE C
                                          ON TC.CHB_ID = C.CHB_ID
                             WHERE  T.TRF_DATE_DEBUT = '2001-01-01'
                               AND  CHB_COUCHAGE = 2
                             )
                     AND    (SELECT AVG(TRF_CHB_PRIX)
                             FROM   T_TARIF T
                                    INNER JOIN TJ_TRF_CHB TC
                                          ON T.TRF_DATE_DEBUT = TC.TRF_DATE_DEBUT
                                    INNER JOIN T_CHAMBRE C
                                          ON TC.CHB_ID = C.CHB_ID
                             WHERE  T.TRF_DATE_DEBUT = '2001-01-01'
                               AND  CHB_COUCHAGE = 3
                             )

Cette requête recherche les chambre du tarif aplicable au debut 2001 pour lesquelles le prix est situé entre la moyenne des prix des chambres à deux places et la moyenne des chambres à trois places.

Voici maintenant cette requête simplifiée par l'utilisation de la clause WITH :
WITHtarifs (moyenne, couchage)
AS
   (SELECT AVG(TRF_CHB_PRIX), CHB_COUCHAGE
    FROM   T_TARIF T
           INNER JOIN TJ_TRF_CHB TC
                 ON T.TRF_DATE_DEBUT = TC.TRF_DATE_DEBUT
           INNER JOIN T_CHAMBRE C
                 ON TC.CHB_ID = C.CHB_ID
    WHERE  T.TRF_DATE_DEBUT = '2001-01-01'
    GROUP BY CHB_COUCHAGE)
SELECT TRF_CHB_PRIX, C.CHB_ID, CHB_NUMERO, CHB_COUCHAGE
FROM   T_TARIF T
       INNER JOIN TJ_TRF_CHB TC
             ON T.TRF_DATE_DEBUT = TC.TRF_DATE_DEBUT
       INNER JOIN T_CHAMBRE C
             ON TC.CHB_ID = C.CHB_ID
WHERE  T.TRF_DATE_DEBUT = '2001-01-01'
  AND  TRF_CHB_PRIX BETWEEN (SELECT  moyenne
                             FROM    tarifs
                             WHERE   couchage = 2)
                    AND     (SELECT  moyenne
                             FROM    tarifs
                             WHERE   couchage = 3)

Comme on le voit, la requête (en bleu) a été considérée comme une table temporaire de nom "tarifs" (en vert) dotée de colonnes "moyenne" et "couchage" et utilisé en sous requête dans le corps de la requête principale (en rouge).

L'intérêt de cette première syntaxe est la réutilisation d'une requête présente plusieurs fois en tant que sous requête au sein d'une requête principale.

5.1.2 Récursion

Nous allons maintenant voir comment fonctionne la récursivité au sein de SQL...
Pour cela construisons notre jeu d'essais :

CREATE TABLE T_VOLS_VLS
(VLS_ID       INTEGER NOT NULL PRIMARY KEY,
 VLS_REF      CHAR(6) NOT NULL,
 VLS_DEPART   VARCHAR(16),
 VLS_ARRIVEE  VARCHAR(16))		 INSERT INTO T_VOLS_VLS VALUES (1, 'AF714', 'PARIS', 'MARSEILLE')
INSERT INTO T_VOLS_VLS VALUES (2, 'AL908', 'PARIS', 'LYON')
INSERT INTO T_VOLS_VLS VALUES (3, 'AF321', 'PARIS', 'BORDEAUX')
INSERT INTO T_VOLS_VLS VALUES (4, 'AF978', 'BORDEAUX', 'MARSEILLE')
INSERT INTO T_VOLS_VLS VALUES (5, 'AL478', 'BORDEAUX', 'NICE')
INSERT INTO T_VOLS_VLS VALUES (6, 'AL974', 'NICE', 'LYON')
INSERT INTO T_VOLS_VLS VALUES (7, 'AL451', 'BORDEAUX', 'LILLE')
INSERT INTO T_VOLS_VLS VALUES (8, 'AF158', 'MARSEILLE', 'LYON')
INSERT INTO T_VOLS_VLS VALUES (9, 'AF159', 'MARSEILLE', 'BORDEAUX')
INSERT INTO T_VOLS_VLS VALUES (10, 'AF189', 'LYON', 'NICE')
INSERT INTO T_VOLS_VLS VALUES (11, 'AL458', 'NICE', 'PARIS')
INSERT INTO T_VOLS_VLS VALUES (12, 'AL117', 'MARSEILLE', 'PARIS')
INSERT INTO T_VOLS_VLS VALUES (13, 'AL444', 'BORDEAUX', 'LYON')
INSERT INTO T_VOLS_VLS VALUES (14, 'AL400',  'BORDEAUX', 'NANTES')
INSERT INTO T_VOLS_VLS VALUES (15, 'AF601',  'LYON', 'NANTES')

Le but est de trouver s'il est possible det se rendre, par exemple de PARIS à NANTES ou bien de NICE à BORDEAUX...

WITH RECURSIVE trajet (depart, arrivee)
AS (SELECT VLS_DEPART, VLS_ARRIVEE
    FROM   T_VOLS_VLS
    UNION  ALL
    SELECT debut.depart, suite.VLS_ARRIVEE
    FROM   trajet debut
           INNER JOIN T_VOLS_VLS suite
                 ON debut.arrive = suite.VLS_DEPART)
SELECT *
FROM   trajet
WHERE  depart = 'NICE'
   AND arrivee = 'BORDEAUX'

En fait le travail de circulation dans l'arbre se fait dans la clause WITH RECURSIVE. L'expression principale de requête n'est là que pour filtrer les résultats.
Lors du premier cycle, les données sont les suivantes :

depart           arrivee          
---------------- ----------------
BORDEAUX         BORDEAUX
BORDEAUX         LILLE
BORDEAUX         LYON
BORDEAUX         LYON
BORDEAUX         LYON
BORDEAUX         MARSEILLE
BORDEAUX         NANTES
BORDEAUX         NANTES
BORDEAUX         NICE
BORDEAUX         NICE
BORDEAUX         PARIS
BORDEAUX         PARIS
LYON             LYON
LYON             NANTES
LYON             NICE
LYON             PARIS
MARSEILLE        BORDEAUX
MARSEILLE        BORDEAUX
MARSEILLE        LILLE
MARSEILLE        LYON
MARSEILLE        LYON
MARSEILLE        LYON
MARSEILLE        MARSEILLE
MARSEILLE        MARSEILLE
MARSEILLE        NANTES
MARSEILLE        NANTES
MARSEILLE        NICE
MARSEILLE        NICE
MARSEILLE        PARIS
NICE             BORDEAUX
NICE             LYON
NICE             LYON
NICE             MARSEILLE
NICE             NANTES
NICE             NICE
NICE             PARIS
PARIS            BORDEAUX
PARIS            BORDEAUX
PARIS            LILLE
PARIS            LYON
PARIS            LYON
PARIS            LYON
PARIS            MARSEILLE
PARIS            MARSEILLE
PARIS            NANTES
PARIS            NANTES
PARIS            NICE
PARIS            NICE
PARIS            PARIS

Nous avons en fait tous les trajets possibles directs ou avec une escale. A notez que si nous avions utilisé UNION au lieu de UNION ALL nous aurions eût moins de résultats, en fait 31 au lieu de 49, cela aurait signifié que nous nous fichons de savoir quelle est la ville étpae de l'escale !
Lors du second cycle les données sont :

depart           arrivee          
---------------- ----------------
BORDEAUX         BORDEAUX
BORDEAUX         BORDEAUX
BORDEAUX         BORDEAUX
BORDEAUX         LILLE
BORDEAUX         LILLE
BORDEAUX         LYON
BORDEAUX         LYON
BORDEAUX         LYON
BORDEAUX         LYON
BORDEAUX         LYON
BORDEAUX         LYON
BORDEAUX         LYON
BORDEAUX         MARSEILLE
BORDEAUX         MARSEILLE
BORDEAUX         MARSEILLE
BORDEAUX         MARSEILLE
BORDEAUX         NANTES
BORDEAUX         NANTES
BORDEAUX         NANTES
BORDEAUX         NANTES
BORDEAUX         NANTES
BORDEAUX         NICE
BORDEAUX         NICE
BORDEAUX         NICE
BORDEAUX         NICE
BORDEAUX         NICE
BORDEAUX         PARIS
BORDEAUX         PARIS
BORDEAUX         PARIS
LYON             BORDEAUX
LYON             LYON
LYON             LYON
LYON             MARSEILLE
LYON             NANTES
LYON             NANTES
LYON             NICE
LYON             NICE
LYON             PARIS
MARSEILLE        BORDEAUX
MARSEILLE        BORDEAUX
MARSEILLE        BORDEAUX
MARSEILLE        BORDEAUX
MARSEILLE        LILLE
MARSEILLE        LILLE
MARSEILLE        LYON
MARSEILLE        LYON
MARSEILLE        LYON
MARSEILLE        LYON
MARSEILLE        LYON
MARSEILLE        LYON
MARSEILLE        LYON
MARSEILLE        LYON
MARSEILLE        MARSEILLE
MARSEILLE        MARSEILLE
MARSEILLE        MARSEILLE
MARSEILLE        NANTES
MARSEILLE        NANTES
MARSEILLE        NANTES
MARSEILLE        NANTES
MARSEILLE        NANTES
MARSEILLE        NICE
MARSEILLE        NICE
MARSEILLE        NICE
MARSEILLE        NICE
MARSEILLE        NICE
MARSEILLE        PARIS
MARSEILLE        PARIS
MARSEILLE        PARIS
MARSEILLE        PARIS
MARSEILLE        PARIS
NICE             BORDEAUX
NICE             BORDEAUX
NICE             LILLE
NICE             LYON
NICE             LYON
NICE             LYON
NICE             LYON
NICE             LYON
NICE             MARSEILLE
NICE             MARSEILLE
NICE             NANTES
NICE             NANTES
NICE             NANTES
NICE             NICE
NICE             NICE
NICE             NICE
NICE             PARIS
NICE             PARIS
NICE             PARIS
PARIS            BORDEAUX
PARIS            BORDEAUX
PARIS            BORDEAUX
PARIS            BORDEAUX
PARIS            LILLE
PARIS            LILLE
PARIS            LYON
PARIS            LYON
PARIS            LYON
PARIS            LYON
PARIS            LYON
PARIS            LYON
PARIS            LYON
PARIS            LYON
PARIS            MARSEILLE
PARIS            MARSEILLE
PARIS            MARSEILLE
PARIS            MARSEILLE
PARIS            NANTES
PARIS            NANTES
PARIS            NANTES
PARIS            NANTES
PARIS            NANTES
PARIS            NICE
PARIS            NICE
PARIS            NICE
PARIS            NICE
PARIS            NICE
PARIS            PARIS
PARIS            PARIS
PARIS            PARIS
PARIS            PARIS

C'est à dire tous les trajets directs ou avec 1 ou 2 escales...

5.1.3 Clause de CYCLE

La clause de cycle permet d'ajouter des colonnes d'informations afin de savoir comment se comporte la "ré entrance" des données... Il faut lui spécifier  la ou les colonnes ciblées pour leur réutilisation.

<cycle mark column> est le nom d'une colonne qui va contenir l'information indiquant que la ré entrance à eût lieu ou non. Cette colonne particulière est prédéfinie de type CHAR(1). On aura soin alors de définir les marqueurs <cycle mark value> et <non cycle mark value> par exemple avec 'O' et 'N' ou encore '1' et '0'. Si le cycle n'est pas entamé, cette colonne sera dotée de la valeur N (non) ou 0 (zéro), et si le cycle est entamé, alors elle prendra la valeur O (oui) ou 1 (un).


Enfin <path column> est une colonne qui va contenir le cheminement ayant conduit à l'ensemble des cycles. Elle est d'un type très particulier : c'est un tableau (ARRAY) dont la cardinalité est considérée comme "suffisament grande" (!) pour s'accomoder du nombre de lignes du résultat. L'élément du tableau est lui même de type 'ligne' (ROW) avec une colonne pour chaque colonne de la <cycle column list>

Dans notre cas, cette clause pourrait s'écrire de la sorte :

CYCLE VLS_DEPART
      SET  debutCycle '1'   DEFAULT '0'
      USING traceCycle

Ainsi modifié notre premier cycle aurait pour résultat

depart           arrivee          debutCycle  traceCycle                           
---------------- ---------------- ----------- ------------------------------------
BORDEAUX         BORDEAUX         1           [BORDEAUX, MARSEILLE]
BORDEAUX         LILLE            0           [BORDEAUX]
BORDEAUX         LYON             0           [BORDEAUX, MARSEILLE]
BORDEAUX         LYON             0           [BORDEAUX, NICE]
BORDEAUX         LYON             0           [BORDEAUX]
BORDEAUX         MARSEILLE        0           [BORDEAUX]
BORDEAUX         NANTES           0           [BORDEAUX, LYON]
BORDEAUX         NANTES           0           [BORDEAUX]
BORDEAUX         NICE             0           [BORDEAUX, LYON]
BORDEAUX         NICE             0           [BORDEAUX]
BORDEAUX         PARIS            0           [BORDEAUX, MARSEILLE]
BORDEAUX         PARIS            0           [BORDEAUX, NICE]
LYON             LYON             1           [LYON, NICE]
LYON             NANTES           0           [LYON]
LYON             NICE             0           [LYON]
LYON             PARIS            0           [LYON, NICE]
MARSEILLE        BORDEAUX         0           [MARSEILLE, PARIS]
MARSEILLE        BORDEAUX         0           [MARSEILLE]
MARSEILLE        LILLE            0           [MARSEILLE, BORDEAUX]
MARSEILLE        LYON             0           [MARSEILLE, BORDEAUX]
MARSEILLE        LYON             0           [MARSEILLE, PARIS]
MARSEILLE        LYON             0           [MARSEILLE]
MARSEILLE        MARSEILLE        1           [MARSEILLE, BORDEAUX]
MARSEILLE        MARSEILLE        1           [MARSEILLE, PARIS]
MARSEILLE        NANTES           0           [MARSEILLE, BORDEAUX]
MARSEILLE        NANTES           0           [MARSEILLE, LYON]
MARSEILLE        NICE             0           [MARSEILLE, BORDEAUX]
MARSEILLE        NICE             0           [MARSEILLE, LYON]
MARSEILLE        PARIS            0           [MARSEILLE]
NICE             BORDEAUX         0           [NICE, PARIS]
NICE             LYON             0           [NICE, PARIS]
NICE             LYON             0           [NICE]
NICE             MARSEILLE        0           [NICE, PARIS]
NICE             NANTES           0           [NICE, LYON]
NICE             NICE             1           [NICE, LYON]
NICE             PARIS            0           [NICE]
PARIS            BORDEAUX         0           [PARIS, MARSEILLE]
PARIS            BORDEAUX         0           [PARIS]
PARIS            LILLE            0           [PARIS, BORDEAUX]
PARIS            LYON             0           [PARIS, BORDEAUX]
PARIS            LYON             0           [PARIS, MARSEILLE]
PARIS            LYON             0           [PARIS]
PARIS            MARSEILLE        0           [PARIS, BORDEAUX]
PARIS            MARSEILLE        0           [PARIS]
PARIS            NANTES           0           [PARIS, BORDEAUX]
PARIS            NANTES           0           [PARIS, LYON]
PARIS            NICE             0           [PARIS, BORDEAUX]
PARIS            NICE             0           [PARIS, LYON]
PARIS            PARIS            1           [PARIS, MARSEILLE]

Et le second cycle :

depart           arrivee          debutCycle  traceCycle                                             
---------------- ---------------- ----------- ------------------------------------------------------
BORDEAUX         BORDEAUX         1           [BORDEAUX, MARSEILLE, PARIS]
BORDEAUX         BORDEAUX         1           [BORDEAUX, MARSEILLE]
BORDEAUX         BORDEAUX         1           [BORDEAUX, NICE, PARIS]
BORDEAUX         LILLE            1           [BORDEAUX, MARSEILLE, BORDEAUX]
BORDEAUX         LILLE            0           [BORDEAUX]
BORDEAUX         LYON             1           [BORDEAUX, LYON, NICE]
BORDEAUX         LYON             0           [BORDEAUX, MARSEILLE, BORDEAUX]
BORDEAUX         LYON             0           [BORDEAUX, MARSEILLE, PARIS]
BORDEAUX         LYON             0           [BORDEAUX, MARSEILLE]
BORDEAUX         LYON             0           [BORDEAUX, NICE, PARIS]
BORDEAUX         LYON             0           [BORDEAUX, NICE]
BORDEAUX         LYON             0           [BORDEAUX]
BORDEAUX         MARSEILLE        1           [BORDEAUX, MARSEILLE, BORDEAUX]
BORDEAUX         MARSEILLE        1           [BORDEAUX, MARSEILLE, PARIS]
BORDEAUX         MARSEILLE        0           [BORDEAUX, NICE, PARIS]
BORDEAUX         MARSEILLE        0           [BORDEAUX]
BORDEAUX         NANTES           0           [BORDEAUX, LYON]
BORDEAUX         NANTES           1           [BORDEAUX, MARSEILLE, BORDEAUX]
BORDEAUX         NANTES           0           [BORDEAUX, MARSEILLE, LYON]
BORDEAUX         NANTES           0           [BORDEAUX, NICE, LYON]
BORDEAUX         NANTES           0           [BORDEAUX]
BORDEAUX         NICE             0           [BORDEAUX, LYON]
BORDEAUX         NICE             1           [BORDEAUX, MARSEILLE, BORDEAUX]
BORDEAUX         NICE             0           [BORDEAUX, MARSEILLE, LYON]
BORDEAUX         NICE             1           [BORDEAUX, NICE, LYON]
BORDEAUX         NICE             0           [BORDEAUX]
BORDEAUX         PARIS            0           [BORDEAUX, LYON, NICE]
BORDEAUX         PARIS            0           [BORDEAUX, MARSEILLE]
BORDEAUX         PARIS            0           [BORDEAUX, NICE]
LYON             BORDEAUX         0           [LYON, NICE, PARIS]
LYON             LYON             1           [LYON, NICE, PARIS]
LYON             LYON             1           [LYON, NICE]
LYON             MARSEILLE        0           [LYON, NICE, PARIS]
LYON             NANTES           1           [LYON, NICE, LYON]
LYON             NANTES           0           [LYON]
LYON             NICE             1           [LYON, NICE, LYON]
LYON             NICE             0           [LYON]
LYON             PARIS            0           [LYON, NICE]
MARSEILLE        BORDEAUX         1           [MARSEILLE, BORDEAUX, MARSEILLE]
MARSEILLE        BORDEAUX         1           [MARSEILLE, PARIS, MARSEILLE]
MARSEILLE        BORDEAUX         0           [MARSEILLE, PARIS]
MARSEILLE        BORDEAUX         0           [MARSEILLE]
MARSEILLE        LILLE            0           [MARSEILLE, BORDEAUX]
MARSEILLE        LILLE            0           [MARSEILLE, PARIS, BORDEAUX]
MARSEILLE        LYON             1           [MARSEILLE, BORDEAUX, MARSEILLE]
MARSEILLE        LYON             0           [MARSEILLE, BORDEAUX, NICE]
MARSEILLE        LYON             0           [MARSEILLE, BORDEAUX]
MARSEILLE        LYON             1           [MARSEILLE, LYON, NICE]
MARSEILLE        LYON             0           [MARSEILLE, PARIS, BORDEAUX]
MARSEILLE        LYON             1           [MARSEILLE, PARIS, MARSEILLE]
MARSEILLE        LYON             0           [MARSEILLE, PARIS]
MARSEILLE        LYON             0           [MARSEILLE]
MARSEILLE        MARSEILLE        1           [MARSEILLE, BORDEAUX]
MARSEILLE        MARSEILLE        1           [MARSEILLE, PARIS, BORDEAUX]
MARSEILLE        MARSEILLE        1           [MARSEILLE, PARIS]
MARSEILLE        NANTES           0           [MARSEILLE, BORDEAUX, LYON]
MARSEILLE        NANTES           0           [MARSEILLE, BORDEAUX]
MARSEILLE        NANTES           0           [MARSEILLE, LYON]
MARSEILLE        NANTES           0           [MARSEILLE, PARIS, BORDEAUX]
MARSEILLE        NANTES           0           [MARSEILLE, PARIS, LYON]
MARSEILLE        NICE             0           [MARSEILLE, BORDEAUX, LYON]
MARSEILLE        NICE             0           [MARSEILLE, BORDEAUX]
MARSEILLE        NICE             0           [MARSEILLE, LYON]
MARSEILLE        NICE             0           [MARSEILLE, PARIS, BORDEAUX]
MARSEILLE        NICE             0           [MARSEILLE, PARIS, LYON]
MARSEILLE        PARIS            1           [MARSEILLE, BORDEAUX, MARSEILLE]
MARSEILLE        PARIS            0           [MARSEILLE, BORDEAUX, NICE]
MARSEILLE        PARIS            0           [MARSEILLE, LYON, NICE]
MARSEILLE        PARIS            1           [MARSEILLE, PARIS, MARSEILLE]
MARSEILLE        PARIS            0           [MARSEILLE]
NICE             BORDEAUX         0           [NICE, PARIS, MARSEILLE]
NICE             BORDEAUX         0           [NICE, PARIS]
NICE             LILLE            0           [NICE, PARIS, BORDEAUX]
NICE             LYON             1           [NICE, LYON, NICE]
NICE             LYON             0           [NICE, PARIS, BORDEAUX]
NICE             LYON             0           [NICE, PARIS, MARSEILLE]
NICE             LYON             0           [NICE, PARIS]
NICE             LYON             0           [NICE]
NICE             MARSEILLE        0           [NICE, PARIS, BORDEAUX]
NICE             MARSEILLE        0           [NICE, PARIS]
NICE             NANTES           0           [NICE, LYON]
NICE             NANTES           0           [NICE, PARIS, BORDEAUX]
NICE             NANTES           0           [NICE, PARIS, LYON]
NICE             NICE             1           [NICE, LYON]
NICE             NICE             1           [NICE, PARIS, BORDEAUX]
NICE             NICE             1           [NICE, PARIS, LYON]
NICE             PARIS            1           [NICE, LYON, NICE]
NICE             PARIS            1           [NICE, PARIS, MARSEILLE]
NICE             PARIS            0           [NICE]
PARIS            BORDEAUX         1           [PARIS, BORDEAUX, MARSEILLE]
PARIS            BORDEAUX         1           [PARIS, MARSEILLE, PARIS]
PARIS            BORDEAUX         0           [PARIS, MARSEILLE]
PARIS            BORDEAUX         0           [PARIS]
PARIS            LILLE            0           [PARIS, BORDEAUX]
PARIS            LILLE            0           [PARIS, MARSEILLE, BORDEAUX]
PARIS            LYON             0           [PARIS, BORDEAUX, MARSEILLE]
PARIS            LYON             0           [PARIS, BORDEAUX, NICE]
PARIS            LYON             0           [PARIS, BORDEAUX]
PARIS            LYON             1           [PARIS, LYON, NICE]
PARIS            LYON             0           [PARIS, MARSEILLE, BORDEAUX]
PARIS            LYON             1           [PARIS, MARSEILLE, PARIS]
PARIS            LYON             0           [PARIS, MARSEILLE]
PARIS            LYON             0           [PARIS]
PARIS            MARSEILLE        0           [PARIS, BORDEAUX]
PARIS            MARSEILLE        1           [PARIS, MARSEILLE, BORDEAUX]
PARIS            MARSEILLE        1           [PARIS, MARSEILLE, PARIS]
PARIS            MARSEILLE        0           [PARIS]
PARIS            NANTES           0           [PARIS, BORDEAUX, LYON]
PARIS            NANTES           0           [PARIS, BORDEAUX]
PARIS            NANTES           0           [PARIS, LYON]
PARIS            NANTES           0           [PARIS, MARSEILLE, BORDEAUX]
PARIS            NANTES           0           [PARIS, MARSEILLE, LYON]
PARIS            NICE             0           [PARIS, BORDEAUX, LYON]
PARIS            NICE             0           [PARIS, BORDEAUX]
PARIS            NICE             0           [PARIS, LYON]
PARIS            NICE             0           [PARIS, MARSEILLE, BORDEAUX]
PARIS            NICE             0           [PARIS, MARSEILLE, LYON]
PARIS            PARIS            1           [PARIS, BORDEAUX, MARSEILLE]
PARIS            PARIS            1           [PARIS, BORDEAUX, NICE]
PARIS            PARIS            1           [PARIS, LYON, NICE]
PARIS            PARIS            1           [PARIS, MARSEILLE]

Et là on se dit que c'est bien agréable la récursion avec SQL et que l'on aimerait pouvoir choisir ses escales !

5.1.4 Parcours en profondeur ou en largeur

Pour les experts en recursivité et parcours d'arbres, SQL permet de définir comment explorer l'arbre :

Ou <sort specification list> est  une liste nommée des colonnes dont on veut spécifier l'ordre.
De plus cette clause doit se terminer par la définition du nom  de la séquence de colonnes spécifiée, précisé par le mot clef SET.

Par exemple, en utilisant la parcours en largeur, nous aurions tous les trajets en commençant par ceux pour lesquels il y a le moins d'escales...

SEARCH BREADTH FIRST BY VLS_DEPART, VLS_ARRIVEE

       SET etape

Il convient de reprendre le nom de la séquence en temps que spécification de tri. Voici la requête complète :

WITH RECURSIVE trajet (depart, arrivee)
AS (SELECT VLS_DEPART, VLS_ARRIVEE
    FROM   T_VOLS_VLS
    UNION  ALL
    SELECT debut.depart, suite.VLS_ARRIVEE
    FROM   trajet debut
           INNER JOIN T_VOLS_VLS suite
                 ON debut.arrive = suite.VLS_DEPART)
SEARCH BREADTH FIRST BY VLS_DEPART, VLS_ARRIVEE
       SET etape
CYCLE VLS_DEPART
      SET  debutCycle '1'   DEFAULT '0'
      USING traceCycle
SELECT *
FROM   trajet
WHERE  depart = 'NICE'
  AND  arrivee = 'BORDEAUX'
ORDER  BY etape

Notre premier cycle donnerait alors :

depart           arrivee          debutCycle  traceCycle                           
---------------- ---------------- ----------- ------------------------------------
PARIS            MARSEILLE        0           [PARIS]
PARIS            LYON             0           [PARIS]
PARIS            BORDEAUX         0           [PARIS]
BORDEAUX         MARSEILLE        0           [BORDEAUX]
BORDEAUX         NICE             0           [BORDEAUX]
NICE             LYON             0           [NICE]
BORDEAUX         LILLE            0           [BORDEAUX]
MARSEILLE        LYON             0           [MARSEILLE]
MARSEILLE        BORDEAUX         0           [MARSEILLE]
LYON             NICE             0           [LYON]
NICE             PARIS            0           [NICE]
MARSEILLE        PARIS            0           [MARSEILLE]
BORDEAUX         LYON             0           [BORDEAUX]
BORDEAUX         NANTES           0           [BORDEAUX]
LYON             NANTES           0           [LYON]
PARIS            LYON             0           [PARIS, MARSEILLE]
PARIS            BORDEAUX         0           [PARIS, MARSEILLE]
PARIS            PARIS            1           [PARIS, MARSEILLE]
PARIS            NICE             0           [PARIS, LYON]
PARIS            NANTES           0           [PARIS, LYON]
PARIS            LYON             0           [PARIS, BORDEAUX]
PARIS            NANTES           0           [PARIS, BORDEAUX]
PARIS            MARSEILLE        0           [PARIS, BORDEAUX]
PARIS            NICE             0           [PARIS, BORDEAUX]
PARIS            LILLE            0           [PARIS, BORDEAUX]
BORDEAUX         LYON             0           [BORDEAUX, MARSEILLE]
BORDEAUX         BORDEAUX         1           [BORDEAUX, MARSEILLE]
BORDEAUX         PARIS            0           [BORDEAUX, MARSEILLE]
BORDEAUX         LYON             0           [BORDEAUX, NICE]
BORDEAUX         PARIS            0           [BORDEAUX, NICE]
NICE             NICE             1           [NICE, LYON]
NICE             NANTES           0           [NICE, LYON]
MARSEILLE        NICE             0           [MARSEILLE, LYON]
MARSEILLE        NANTES           0           [MARSEILLE, LYON]
MARSEILLE        LYON             0           [MARSEILLE, BORDEAUX]
MARSEILLE        NANTES           0           [MARSEILLE, BORDEAUX]
MARSEILLE        MARSEILLE        1           [MARSEILLE, BORDEAUX]
MARSEILLE        NICE             0           [MARSEILLE, BORDEAUX]
MARSEILLE        LILLE            0           [MARSEILLE, BORDEAUX]
LYON             LYON             1           [LYON, NICE]
LYON             PARIS            0           [LYON, NICE]
NICE             MARSEILLE        0           [NICE, PARIS]
NICE             LYON             0           [NICE, PARIS]
NICE             BORDEAUX         0           [NICE, PARIS]
MARSEILLE        MARSEILLE        1           [MARSEILLE, PARIS]
MARSEILLE        LYON             0           [MARSEILLE, PARIS]
MARSEILLE        BORDEAUX         0           [MARSEILLE, PARIS]
BORDEAUX         NICE             0           [BORDEAUX, LYON]
BORDEAUX         NANTES           0           [BORDEAUX, LYON]
Cela serait d'ailleurs bien plus pratique pour choisir un trajet avec un minimum d'escales...

...

6 Références

MELTON p321  et suivante ###

Un type référence (REF) est un pointeur vers une ligne d'une autre table. Ce type peut être utilisé dans tous les "sites" SQL, c'est à dire aussi bien dans la définition de colonne d'une table, d'attribut de type structuré, de variable SQL ou de paramètre de fonction ou de procédure stockée.

Voyons tout d'abord une syntaxe de base de déclaration d'un type référence :

<spécification_type_ref> ::=
   <user_defined_representation>
   | <derived_representation>
   | <system_generated_representation>
   <user_defined_representation> ::=
      REF USING <type_prédéfini>
   <derived_representation> ::=
      REF FROM <liste_attributs>
   <system_generated_representation> ::=
      REF IS SYSTEM GENERATED
      <liste_attributs> ::=
         ( <nom_attribut> [ { , <nom_attribut> ] ... ]
  REF ( <udt name> )
      [ SCOPE <table_name> [ <reference scope check> ] ]

   <reference scope check> ::= 
      REFERENCES ARE [NOT] CHECKED
      [ ON DELETE { CASCADE | SET NULL | SET DEFAULT | RESTRICT | NO ACTION ] }

revoir melton p 60 puis 134 et suivantes

NOTA : nous avons déjà vu une partie de cette syntaxe dans le paragraphe 1.3

La syntaxe pour la déclaration d'une référence est la suivante :

REF ( <udt name> )
   [ SCOPE <table_name> [ <reference scope check> ] ]
   <reference scope check> ::= 
      REFERENCES ARE [NOT] CHECKED
      [ ON DELETE { CASCADE | SET NULL | SET DEFAULT | RESTRICT | NO ACTION ] }

Il existe une syntaxe particulière pour la déclaration d'une supertable de niveau racine (table "type"), c'est à dire une supertable ancètre de toute sous table, c'est à dire une table ancètre n'ayant pas été créée par héritage. Dans ce cas on parle d'auto référence puisque cette colonne ne fait que référencer la ligne de la table dans laquelle est est incluse, un peu à la manière d'une clef unique :

<spécification_colonne_autoréférencée> ::=
   REF IS <nom_colonne_autoréférencée> <génération_référence>
   <nom_colonne_autoréférencée> ::=
      <nom_colonne>
   <génération_référence> ::=
      SYSTEM GENERATED
      | USER GENERATED
      | DERIVED

Il est important de comprendre que si vous voulez définir vous même le contenu de cette colonne, via l'option USER GENERATED, il est nécessaire que la valeur soit unique et non réutilisable. En effet contrairement à l'intégrité référentielle, la référence peut être supprimé sans que le pointeur sur cette référence soit lui aussi supprimé. Le mieux étant d'utiliser l'option SYSTEM GENERATED afin de s'assurer que la valeur soit toujours unique pendant toute la durée de vie de la table.

Pour les tables "type"

On pourrait
Une référence porte sur une colonne et est un pointeur vers une valeur situé dans une autre table. Le type sous jacent à une référence ne peut être qu'un type utilisateur.
Contrairement à l'intégrité référentielle, la valeur n'est pas contenue dans la table qui comporte la colonne référencée.
Une référence se définie lors de la création de la table. La table ainsi référencée doit exister. Ainsi, la référence doit être considéré comme l'équivalent d'un type lors de la création de la table.

La syntaxe de définition d'une référence est la suivante :

<reference type> ::=
   REF ( <udt name> )
      [ SCOPE <table_name> [ <reference scope check> ] ] 
   <reference scope check> ::= 
      REFERENCES ARE [NOT] CHECKED
      [ ON DELETE { CASCADE | SET NULL | SET DEFAULT | RESTRICT | NO ACTION ] }

Nota : il n'est pas possible de trantyper une référence via l'opérateur CAST, sauf à ce qu'une définition de trantsypage ait été introduite lors de la création du type abstrait sous jacent à la référence (voir l'exemple 19).

Exemple 37 :

-- création des UDT
CREATE TYPE UDT_CP_VILLE
AS
   CODE_POSTAL CHAR(5) NOT NULL,
   VILLE VARCHAR(32) NOT NULL
   NOT FINAL

CREATE TYPE UDT_PAYS
AS
   CODE_PAYS VARCHAR(2) DEFAULT 'F' NOT NULL,
   NOM_PAYS  VARCHAR(64) NOT NULL
   NOT FINAL
-- création des tables de référence
CREATE TABLE TR_CODE_POSTAL_CPL
(CPL UDT_CP_VILLE)

CREATE TABLE TR_PAYS_PAY
(PAY UDT_PAYS)
-- création de la table adresse utilisant les références
CREATE TABLE T_ADRESSE_ADR
(ADR_ID         INTEGER NOT NULL PRIMARY KEY,
 ADR_LIGNE1     VARCHAR(38),
 ADR_LIGNE2     VARCHAR(38),
 ADR_LIGNE3     VARCHAR(38),
 ADR_CP_VILLE   REF UDT_CP_VILLE SCOPE TR_CODE_POSTAL_CPL
                   REFERENCES ARE CHECKED
                   ON DELETE SET NULL,
 ADR_PAYS       REF UDT_PAYS SCOPE TR_PAYS_PAY
                   REFERENCES ARE NOT CHECKED
                   ON DELETE SET DEFAULT)
-- dès lors, on peut remplir les tables de référence avec des valeurs pré établies
INSERT INTO TR_PAYS_PAY (PAY..CODE_PAYS, PAY..NOM_PAYS) VALUES ('F', 'France')
INSERT INTO TR_PAYS_PAY (PAY..CODE_PAYS, PAY..NOM_PAYS) VALUES ('US', 'États Unis d''Amérique')

INSERT INTO TR_CODE_POSTAL_CPL (CPL..CODE_POSTAL, CPL..VILLE) VALUES ('75001', 'Paris, 1er arrondissement')
INSERT INTO TR_CODE_POSTAL_CPL (CPL..CODE_POSTAL, CPL..VILLE) VALUES ('06000', 'Nice')

-- et pour insérer une adresse ...
INSERT INTO T_ADRESSE_ADR
       VALUES (423, '1 rue de la Paix', NULL, NULL,
               (SELECT REF(CPL)
                FROM   TR_CODE_POSTAL_CPL CPL
                WHERE  CPL.CODE_POSTAL = '06000'),
               (SELECT REF(PAY)
                FROM   TR_PAYS_PAY PAY
                WHERE  PAY.CODE_PAY = 'F'))

REF est un opérateur qui renvoit l'adresse de la ligne de la table référencée.

Pour obtenir la valeur d'une ligne référencée, il faut utiliser l'opérateur DEREF. Dans le cas contraire on obtiendrait simplement la valeur du pointeur, ce qui ne présente par d'intérêt !
Exemple 38 :
-- obtention des données
SELECT ADR_ID, DEREF(ADR.ADR_CP_VILLE)
FROM   T_ADRESSE_ADR ADR
WHERE  ADR_ID = 423
ADR_ID          ADR_CP_VILLE..CODE_POSTAL ADR_CP_VILLE..VILLE
--------------- ------------------------- -----------------------------------------------------------------
423             06000                     NICE
-- obtention du pointeur
SELECT ADR_ID, ADR.ADR_CP_VILLE
FROM   T_ADRESSE_ADR ADR
WHERE  ADR_ID = 423
ADR_ID          ADR_CP_VILLE
--------------- ----------------
423             51A0245154F3C681

Pour déréférencer une colonne, il faut utiliser la syntaxe suivante :
<dereference operation> ::=
   reference argument -> <attribute name>
   <attribute name> ::=
      <column name> | *

NOTA : le fonctionnement de la référence n'est pas encore clairement définit dans SQL:1999. En effet il faudrait pour cela définir les fonctions REF et DEREF. Il semble que la syntaxe hésite encore entre la fonction et une clause de référence qui prendrait la forme "REF IS" !



Pour compléter votre lecture, voici les ouvrages compulsés pour mettre au point cette page :