![]() |
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 :
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] |
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[] |
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']) |
La fonction CARDINALITY renseigne sur
le nombre de cellules d'un tableau :
Exemple 6 :
CARDINALITY(ARRAY['Monsieur', 'Madame', 'Mademoiselle']) |
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))) |
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 |
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 |
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 |
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 CREATE TYPE original UNDER oeuvre AS (support VARCHAR(64)) INSTANTIABLE NOT FINAL CREATE TYPE sculpture UNDER objet CREATE TYPE livre UNDER oeuvre |
"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.
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.
CREATE TYPE DISQUE AS (titre VARCHAR(256), date_creation DATE, nombre_plage INTEGER) INSTANTIABLE NOT FINAL REF IS SYSTEM GENERATED |
CREATE TYPE CD UNDER DISQUE AS (editeur VARCHAR(32), numero_serie CHAR(16))) INSTANTIABLE NOT FINAL |
CREATE TABLE T_CD OF CD (REF IS CD_ID SYSTEM_GENERATED) |
CREATE TABLE T_SINGLE_CD OF CD UNDER T_CD ( nombre_plage WITH OPTIONS CONSTRAINT pas_plus_de_deux_titres CHECK (nombre_plage <= 2 )) |
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> )) |
CREATE TRANSLATION TRL_SANS_DIACRITIQUE FOR ISO8BIT TO ISO8BIT FROM MODIFY ( 'à' = 'a', 'b', 'c', 'ç' = 'c', 'd', 'e', 'é' = 'e', 'è' = 'e', 'f', 'g' ... ) |
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. |
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) |
4 Prédicats
4.1
logique de base : IS TRUE...
<predicat> IS [NOT] { TRUE | FALSE | UNKNOWN } |
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 ----------- ---------------- |
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 |
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' |
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', ... |
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> |
('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) |
NOT (('toto', 123, NULL) IS DISTINCT FROM ('toto', 123, NULL)) |
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> } ... ] ) |
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 |
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 ) |
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) |
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) |
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 |
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 |
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 |
Ainsi modifié
notre premier cycle aurait pour résultat
depart
arrivee
debutCycle
traceCycle
|
Et le second cycle :
depart
arrivee
debutCycle
traceCycle
|
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) |
Notre premier cycle
donnerait alors :
depart
arrivee
debutCycle
traceCycle
|
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.
-- 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" !