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)

Seconde partie
 

dernière mise à jour : 

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"
 

1.4 Types abstraits (ADT)

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.

reprendre syntaxe melton t2 p58 ###

En fait ce qui est intéressant dans ce jeu de poupées russes, c'est que le type abstrait repose sur une définition locale ou inscrite dans un schema (donc un objet local ou persitant de la base). Cette syntaxe est donc plus simple qu'il n'y parait au premier abord, car pour créer un type abstrait, par exemple une adresse, il suffit d'écrire :
Exemple 24 :

CREATE TYPE ADT_ADRESSE ...

Ou nos trois petits points vont décrire la structure de ce type abstrait !

REMARQUE : le type abstrait pourra en définitive être public, privé ou protégé. Hélas ceci n'entre pas dans la norme SQL:1999, mais dans la future norme SQL:2003 !

1.4.1 Les attributs d'un type abstrait :

On décrite donc une structure de type abstrait au niveau des attributs, d'une manière similaire à la liste des colonnes d'une table.
Exemple 25 :

CREATE TYPE ADT_ADRESSE AS  ADR_LIGNE1       VARCHAR(38),  ADR_LIGNE2       VARCHAR(38),  ADR_LIGNE3       VARCHAR(38),  ADR_CODE_POSTAL  CHAR(5),  ADR_VILLE        CHAR(32)  NOT FINAL;

Bien entendu on peut insérer un type abstrait dans un autre type abstrait...
Exemple 26 :

CREATE TYPE ADT_PERSONNE AS  PRS_NOM             CHAR(32),  PRS_PRENOM          VARCHAR(32),  PRS_DATE_NAISSANCE  DATE,  PRS_SEXE            CHAR(1),  PRS_ADRESSE         ADT_ADRESSE  NOT FINAL  METHOD PRS_AGE() RETURNS FLOAT;

Dans cet exemple de types abstraits "gigogne", on trouve une adresse dans la définition du type abstrait de la personne !

1.4.2 Les méthodes d'un type abstrait :

Pour implémenter une méthode applicable à un attribut, SQL:1999 se sert de la notion "d'attribut virtuel". Cela nécessite à nouveau l'application d'une fonction qui sera greffée sur le type abstrait. Ainsi, pour donner l'âge de notre personne, nous pouvons faire :
Exemple 27 :

CREATE METHOD AGE() FOR ADT_PERSONNE RETURNS FLOAT SELF AS RESULT LANGUAGE SQL DETERMINISTIC CONTAINS SQL RETURN NULL ON NULL INPUT BEGIN    DECLARE age float    SET age = CAST(INTERVAL DAY (CURRENT_DATE - ADT_PERSONNE.PSR_DATE_NAISSANCE) AS FLOAT) / 365.2422    RETURN age END

-- ou encore, plus simple : CREATE METHOD AGE() FOR ADT_PERSONNE RETURNS FLOAT SELF AS RESULT LANGUAGE SQL DETERMINISTIC CONTAINS SQL RETURN NULL ON NULL INPUT RETURN CAST(INTERVAL DAY (CURRENT_DATE - ADT_PERSONNE.PSR_DATE_NAISSANCE) AS FLOAT) / 365.2422

Bien entendu vous pouvez implémenter une fonction qui va "donner" un âge précis à une personne, en fait, recalculer sa date de naissance en fonction de lâge passé en paramètre !

1.4.3 Constructeur et destructeur :

A la manière de ce qui se fait dans les langages à objet, vous devez spécifier un constructeur et un destructeur pour pouvoir utiliser un type abstrait comme objet de stockage (site) votre base de données...
Voici les exemples de constructeurs et destructeurs associés au type abstrait ADT_PERSONNE :
Exemple 28 : constructeur  ADT_PERSONNE

CONSTRUCTOR FUNCTION ADT_PERSONNE_CREATOR             RETURNS ADT_PERSONNE BEGIN    DECLARE p ADT_PERSONNE    SET p.PRS_NOM = NULL    SET p.PRS_PRENOM = NULL    SET p.PRS_DATE_NAISSANCE = NULL    SET p.PRS_SEXE = NULL    SET p.PRS_ADRESSE = NULL    RETURN p END

CONSTRUCTOR FUNCTION ADT_PERSONNE_CREATOR (nom            CHAR(32),                                            prenom         VARCHAR(32),                                            date_naissance DATE,                                            sexe           CHAR(1),                                            adresse        ADT_ADRESSE)             RETURNS ADT_PERSONNE BEGIN    DECLARE p ADT_PERSONNE    SET p.PRS_NOM = nom    SET p.PRS_PRENOM = prenom    SET p.PRS_DATE_NAISSANCE = date_naissance    SET p.PRS_SEXE = sexe    SET p.PRS_ADRESSE = adresse    RETURN p END

Les deux syntaxes sont possible. La première créer une instance libre (une coquille vide en quelque sorte) qu'il faudra ensuite assignée, l'autre créé et assigne les valeurs passées en argument de la fonction de création.

Pour ce qui est du destructeur, le mécanisme est très simple : il s'agit aussi d'une fonction qui à la particularité, comme toute fonction SQL de renvoyer une valeur : en fait l'adresse vide de l'ADT détruite !
Exemple 29 : destructeur  ADT_PERSONNE

DESTRUCTOR FUNCTION ADT_PERSONNE_DESTRUCTOR (p ADT_PERSONNE) RETURNS ADT_PERSONNE BEGIN    DESTROY p     RETURN p END END FUNCTION

1.4.4 Assignation et lecture des données d'un type abstrait :

On peut lire à la manière d'un appel de fonction les données contenues dans un ADT :

Exemple 30 :

DECLARE une_personne ADT_PERSONNE DECLARE le_nom CHAR(32) SET le_nom = PRS_NOM(une_personne)

On peut assigner une valeur à l'aide d'un "muteur" de données :
Exemple 31 :

PRS_NOM(une_personne, 'KENNEDY')

Cette syntaxe étant réservée à certains ordres SQL comme l'UPDATE.

Enfin, la lecture peut s'effectuer à l'aide d'une notation doublement pointée (afin de ne pas introduire de confusion avec la notation pointée des alias de tables, et des type ROW et ARRAY) :
Exemple 32 :

DECLARE une_personne ADT_PERSONNE DECLARE le_nom CHAR(32) SET le_nom = une_personne..PRS_NOM

IMPORTANT : bien entendu ces exemples ne sont valables que si la syntaxe du langage procédural de votre SGBDR est calqué sur la norme ce qui est loin d'être le cas actuellement des principaux langages des SGBDR commerciaux comme PL/SQL, Transact SQL, PL/pgSQL, etc...

Voici pour terminer un nouvel exemple de la définition, la création et l'utilisation d'un type abstrait. Il s'agit de calculer des distances et des temps moyens de transport entre différentes villes dont le référentiel de données est une table T_VILLE_VIL. Notre utilisateur est un voyageur de commerce bien entendu :
Exemple 33 :

-- la table des villes CREATE TABLE T_VILLE_VIL (VIL_ID INTEGER NOT NULL PRIMARY KEY,   VIL_NOM VARCHAR(128) NOT NULL,  VIL_LONGITUDE FLOAT NOT NULL,  VIL_LATITUDE NOT NULL)

-- les données de la table des villes INSERT INTO T_VILLE_VIL VALUES (1, 'PARIS', 2.33, 48.87) INSERT INTO T_VILLE_VIL VALUES (2, 'MARSEILLE', 5.4, 43.3)

-- un type abstrait pour calculer les distances et le temps de transport CREATE TYPE ADT_ETAPE_ETP AS ETP_VILLE_DEPART_ID INTEGER, ETP_VILLE_ARRIVEE_ID INTEGER , FUNCTION ETP_DISTANCE (e1 ADT_ETAPE_ETP, e2 ADT_ETAPE_ETP) RETURNS FLOAT , FUNCTION ETP_AVG_TEMPS (e1 ADT_ETAPE_ETP, e2 ADT_ETAPE_ETP,                       mode_transport VARCHAR(16))                      RETURNS INTERVAL HOUR TO MINUTE;

-- son intinéraire est stocké dans une table CREATE TABLE T_INTINERAIRE_ITN (ITN_ETAPE ADT_ETAPE_ETP)

A ce niveau notons que nous avons déclaré les fonctions ETP_DISTANCE et ETP_AVG_TEMPS, permettant de calculer la distance entre deux villes et la temps de transport moyen en fonction du mode de transport passé en paramètre.

Il va nous falloir maintenant créer les corps des deux méthodes. Le paragraphe consacré au SQL Dynamique nous montrera comment procéder. Dans le principe les algorithmes de ces deux méthodes seront basées sur la principe suivant :

-- calcul de la distance, méthode ETP_DISTANCE en Km suivant un arc de grand cercle (orthodromique) : 117.9 x acos[cos(LatA) x cos(LatB) x cos(LongB-LongA)+sin(LatA) x sin(LatB)]

-- calcul du temps moyen de transport suivant moyen : ETP_DISTANCE * VTS_COEFF

-- ou coefficient est obtenu d'une table TR_VITESSE_VTS dont la sctruture est la suivante : CREATE TR_VITESSE_VTS (VTS_MODE_TRANSPORT VARCHAR(16) NOT NULL PRIMARY KEY,  VTS_VITESSE_KMH    INTEGER NOT NULL) -- avec par exemple les valeurs suivantes INSERT INTO TR_VITESSE_VTS VALUES ('AVION REACTION', 900) INSERT INTO TR_VITESSE_VTS VALUES ('AVION HELICE', 500) INSERT INTO TR_VITESSE_VTS VALUES ('RAIL TGV', 250) INSERT INTO TR_VITESSE_VTS VALUES ('VOITURE', 80)

Exemple 34 :

Encore une fois nous détaillerons les éléments des routines (fonction, méthode, procedure) au chapitre consacré aux fonctions.

1.4.5  CAST

Bien entendu un type absatrit, comme un type distinct peut être doté de transtypages spécifiques. Voir la syntaxe en 1.2.1 et les exemples 18 et 19.

1.4.6 ORDERING

Lors que l'on travaille avec des structures de données complexes, il est intéressant de définir en outre le schéma d'ordonnancement des données. Pour cela SQL:1999 propose de créer de toute pièce la logique de séquencement des données.

La syntaxe d'un ordre de création d'ordonnancement est la suivante :

Dès lors toute comparaison, comme l'usage des clauses DISTINCT et ORDER BY devient possible !

• EQUALS retourne Vrai si les instances sont égales et Faux dans tous les autres cas (même si présence de NULL)
• RELATIVE WITH retourne 0 en cas d'égalité, ou un nombre positif ou négatif en cas de supériorité ou d'infériorité.
• HASH WITH retourne un ordre absolu positionnel de l'instance spécifiée.

Voici un exemple pour notre type Adresse :
Exemple 35 :

On procède à la concaténation des colonnes de l'adresse en commençant par le code, postal, puis la ville et enfin, le moins important, les données des lignes transtypées en longueur fixe.
Exemple 36 :

 
4.3 Les routines

Ce sont les routine procédurales que l'utilisateur peut créer en tant qu'objet de la base de données. Soit il s'agit de méthodes (donc propre à un ADT) soit il s'agit de "simples" fonctions définies librement (donc indépendante d'un objet de la base, mais pouvant utiliser tout objet de cell-ci) ou encore de procedure (les fameuses procédures stockées existant déjà dans les anciennes normes concernant les PSM - Persistent Storage Module)..

Voici donc un gros morceau nouveau dans SQL, aussi épais que celui consacré aux UDT auquel d'ailleurs il se réfère.
Notons que la norme SQL 1999 permet de définir des fonctions comme objet de bases de données, qu'elles soient écrite en SQL ou dans un langage hôte comme C, Ada, Cobol, FORTRAN...

Decouper en trois : PROCEDURE / FONCTION psuis METHODE ###

PROCEDURE : Syntaxe SQL:1999 :

Une procédure ne retourne pas de valeur.

FUNCTION (UDF) : Syntaxe SQL:1999 :

Une fonction retourne une valeur.

METHODE (relative à un ADT) : Syntaxe SQL:1999 :

1) déclaration au sein d'un ADT :

2) création de la méthode :

Il est important de remarquer que toute routine est composée de deux parties bien distinctes :
la partie caractéristique "<routine characteristics>" qui décrit la routin et que l'on pourrait apeller la "définition" ou "l'interface" de la routine
la partie corps "<routine body>" qui contient le code du programme et que l'on pourrait apeller "l'implémentation" ou "codage".

Exemple 42 :

4.3.1 Retour d'une fonction (ou méthode)

La clause RETURNS permet de définir le type retourné. Notez le "s" final de cette clause. Pour retourner une valeur, il suffit d'utiliser le mot clef RETURN (cette fois ci sans "s").

L'ajout de la spécification RETURN NULL ON NULL INPUT indique qu'en cas de présence d'un paramètre NULL, la routine renvoie immédiatement le marqueur NULL. C'est important dans le cas de routines écrite dans un langage hôte (C, Pascal, FORTRAN...) car aucun de ces langage ne permet de gérer facilement des marqueurs NULL dans les variables. En revanche, si l'on spécifie CALL ON NULL INPUT, la routine, même si elle est externe, sera exécutée si un quelconque des paramètres est NULL.

Exemple 43 :

4.3.2 Paramètres

La liste des paramètres d'une routine figure entre parenthèse. Chaque paramètre doit possèder un nom SQL unique dans la liste. Chaque paramètre doit être typé. Il est possible de spécifier le mode de passage du paramètre. Les choix possibles sont IN, OUT ouINOUT. Par défaut c'est le IN qui est utilisé.

PARAMTER STYLE permet de définir si les paramètres sont des paramètres SQL ###

Exemple 44 :

Gulutzan p477+ 535

Penser à CREATE TRANSFORM, NEW, TREAT, SPECIFITYPE ###

6.3 analyse multidimensionelle : GROUPING, ROLLUP et CUBE

SET FUNCTION p142 Melton EVERY ANY SOME (nouveau !)

MELTON 326 à 338,  GULU chapitre 33p 631

8 Privilèges
Roles, GRANT, REVOKE, SET ROLE 474
ADMIN 492
 

9 Schema p 718
 
 

10 SQL dynamique

Déclencheurs3 95
TRANSACTION 511
Modification dans les curseurs dont l'ordre 453 
Fonctions CLI Melton 663, Gulu 751

11 Après SQL:1999...
 

11.1 SQL:2003...
SET, LIST, MULTISET S271 (Fortier 140)
SEQUENCE T176, IDENTITY T174 (Gulutzan) 

F312 MERGE statement  -  -  **

T071 BIGINT data type ** ** -

T621 CEILING(n)|CEIL(n) * * *
T621 FLOOR(n) ** ** **
T621 LN(n) ** * **
T621 EXP(m,n) ** ** **
T621 POWER(m,n) ** ** **
T621 SQRT(n) ** ** **
T621 STTDEV_POP, STDDEV_SAMP * * *
T621 VAR_POP, VAR_SAMP *

PRIVATE, PUBLIC, PROTECTED

11 .2 déclinaisons

SQL RT, SQL MM
XQL / OQL
 

• Jim Melton : Advanced SQL:1999 - Morgan Kauffman 2003
• Jim Melton, Alan R. Simon : SQL:1999, Understanding Reational Language Components - Morgan Kauffman 2002
• Peter Gulutzan, Trudy Peltzer : SQL-99 Complete, Really - R&D books 1999
• Paul Fortier : SQL 3, Implementing the SQL Foundation Standard - Mc Graw Hill 1999