Oracle/SQL

De Wiki-noulot
Sauter à la navigation Sauter à la recherche

Oracle

Def

VLDB = Very Large Databases 
SGA = System Global Area
      Zone de mémoire à l'intérieur de la mémoire principale utilisée
      pour stocker des données pour un accès rapide. Oracle utilise le
      pool partagé pour allouer de la mémoire SGA en commun, les 
      procédures SQL et PL/SQL.
LOB = Objet Binaire Large (Fichier ex: photo, scan...)
DBMS = data bas manage system (programe fonctionel "Pakage" (ex: Sats))
Extent = Groupe de block contigus 
Segment = Ensemble d'extent > Objet
Table Space = Espace de stockage des données
Grid Computing = Ecriture en RAM
Shemat = Objet utilisateur
OFA = oracle flexible architecture
flash_recovery_area = repertoire "Corbeille" (Purge par def toutes les 15 min)
Dictionnaire de données = 
Explain = utilitaire "d'explication" de la requette (execute la requette)

Soft

SQL Developer (gratuit)
Data Modeler (gratuit)

Commandes de base

Connection a une base

connect "user"/"PW"

Connection avec utilisateur du groupe DBA

connect / as sysdba

Changement PW sys

alter user sys identified by "PW";

Arret Base

shutdown immediate

Démarrage Base

Startup

Afficher les tablespaces

desc dba_tablespaces

Creation de tablespace

CREATE SMALLFILE TABLESPACE "T_STAGE" DATAFILE 'C:\Stage\t_stage01.dbf'
SIZE 100M AUTOEXTEND ON NEXT 10M MAXSIZE 150M LOGGING EXTENT MANAGEMENT 
LOCAL SEGMENT SPACE MANAGEMENT AUTO

Creation Table

Create Table "NOM"

Contrainte

CONSTRAINT "nom contrante" "Type contrainte"

Type Contrainte

PRIMARY KEY 	: Clé Primaire
FOREIGN KEY 	: Clé étrangère
UNIQUE 	: Unicité de valeur
NOT NULL	: La colonne est obligatoirement renseignée
CHECK 		: Contrôle d’une règle de gestion

Select

select "valeur" from "nom base"

Jointure

select ename, dname from emp, det where emp.deptno= det.deptno;
SQL Joins

Afficher catalog

select * from cat;

Descrition objet

desc "nom objet";

Créer un Role

CREAT ROLE "R_STAGE" NOT IDENTIFIED
GRANT CREATE PROCEDURE TO "R_STAGE"
GRANT CREATE SEQUENCE TO "R_STAGE"
GRANT CREATE SESSION TO "R_STAGE"
GRANT CREATE SYNONYM TO "R_STAGE"
GRANT CREATE TABLE TO "R_STAGE"
GRANT CREATE TABLESPACE TO "R_STAGE"
GRANT CREATE TRIGGER TO "R_STAGE"
GRANT CREATE TYPE TO "R_STAGE"
GRANT CREATE USER TO "R_STAGE"
GRANT CREATE VIEW TO "R_STAGE"

Créer un utilisateur avec Role

CREATE USER "Nom User" PROFILE "DEFAULT" IDENTIFIED BY "Password"  
DEFAULT TABLESPACE "TableSpace def" ACCOUNT UNLOCK

Attribuer un role a un utilisateur

GRANT "role" TO "nom user"

Modification Quota user

ALTER USER "SEBASTIEN" QUOTA 50 M ON "TBS_STAGE"

Consultation des données

SELECT empno, ename, job, sal
FROM   emp
WHERE  sal > 4500;
     EMPNO ENAME      JOB              SAL
---------- ---------- --------- ----------
      7839 KING       PRESIDENT       5000

Insertion de lignes

insert into dept 
values (50,’FORMATION’,’BIARRITZ’);

Modification des lignes

update emp
set sal = sal * 1.1 
WHERE sal > 4500

Suppression de lignes

delete from "nom table"
where "valeur dans collone" = 345

Supprimer une table

DROP TABLE "Nom table";

Supprimer une sequance

DROP SEQUENCE seq_frn;

Creation d'une sequance

 CREATE SEQUENCE seq_frn
 INCREMENT BY 2
 START WITH 1
 MAXVALUE 999
 NOCYCLE;

Insertion d'une donnée dans une table

insert into "Table" (Collone, ..., ...)
values (Val1, Val2, ...); #avec ' ' si AlphaNum, sans ' ' si num, null si vide

Trouver le DIRECTORY pour export

select * from dba_directories;


Afficher tous les shéma d'une base

select username from dba_users;

export de shema

expdp user/pwd DUMPFILE="nom_fichier_dump" DIRECTORY="SAV_DIR" SCHEMAS="nom du schema","nom du schema" LOGFILE="fichier_log"


SQL

==La commande SELECT==Vous trouverez des compléments d'information sur le sujet aux pages 53 à 127 de l'ouvrage "SQL", collection "La Référence", Campus Press éditeur.Le SELECT est la commande de base du SQL destinée à extraire des données d’une base ou calculer de nouvelles données à partir d’existantes...Voici la syntaxe générale d'une commande SELECT :SELECT [DISTINCT ou ALL] * ou liste de colonnesFROM nom de table ou de la vue[WHERE prédicats][GROUP BY ordre des groupes][HAVING condition][ORDER BY ] liste de colonnesNOTA : dans cette syntaxe, les mots clef du SQL sont en gras, les paramètres en minuscule et entre crochets on trouve les parties optionnellesEn fait l'ordre SQL SELECT est composé de 6 clauses dont 4 sont optionnelles.Clauses de l'ordre SELECT :SELECTSpécification des colonnes du résultatFROMSpécification des tables sur lesquelles porte l'ordreWHEREFiltre portant sur les données (conditions à remplir pour que les lignes soient présentes dans le résultat)GROUP BYDéfinition d'un groupe (sous ensemble)HAVINGFiltre portant sur les résultats (conditions de regroupement des lignes)ORDER BYTri des données du résultatNOTA : La plupart du temps, la difficulté réside dans la compréhension de la différence entre le filtre WHERE et le filtre HAVING. Disons plus pragmatiquement que le filtre WHERE permet de filtrer les données des tables tandis que le filtre HAVING permet de filtrer les données du résultat...REMARQUE : pour spécifier une valeur littérale il faut l'entourer de guillemets simples.Un premier exemple basique :Exemple 1SELECT CLI_NOM, CLI_PRENOMFROM T_CLIENTWHERE TIT_CODE = 'M.'CLI_NOM CLI_PRENOM------- ----------DUPONT AlainMARTIN MarcBOUVIER AlainDUBOIS PaulDREYFUS JeanFAURE AlainPAUL MarcelDUVAL ArsènePHILIPPE AndréCHABAUD DanielBAILLY Jean-François...Permet de trouver les noms et prénoms des clients dont le titre est ‘M.’ (monsieur).NOTA : comme tous les paramètres à prendre sous forme de littéraux doivent être exprimés entourés d'apostrophes (simple côtes), il faut dédoubler un tel caractère s'il s'avère présent dans la chaîne utilisé.==L'opérateur * (étoile)==Le caractère * (étoile) récupère toutes les colonnes de la table précisée dans la clause FROM de la requête.Juste après le mot clef SELECT, on précise les colonnes de la table qui doivent être présentées dans la réponse.L’utilisation du caractère étoile ramène toutes les colonnes de la table dans la réponse. Dans le cas contraire il faut expressément nommer chacune des colonnes et les séparer par des virgules.Exemple 2SELECT *FROM T_CLIENTWHERE TIT_CODE = 'M.'CLI_ID TIT_CODE CLI_NOM CLI_PRENOM CLI_ENSEIGNE------- -------- -------- ---------- ------------ 1 M. DUPONT Alain NULL 2 M. MARTIN Marc Transports MARTIN & fils 3 M. BOUVIER Alain NULL 4 M. DUBOIS Paul NULL 5 M. DREYFUS Jean NULL 6 M. FAURE Alain Boulangerie du marché 11 M. PAUL Marcel Cie Internationale des Mach... 12 M. DUVAL Arsène NULL 13 M. PHILIPPE André NULL 16 M. CHABAUD Daniel NULL...Notons tout de suite la présence à plusieurs reprises du mot clef "NULL" dans la cologne CLI_ENSEIGNE. Non il ne s'agit pas d'une enseigne particulière, mais simplement de l'absence d'information. Nous verrons que l'absence d'information, c'est le marquer "NULL" qui différe de la chaîne de caractère vierge ("") ou encore du zéro.==L'opérateur DISTINCT (ou ALL)==Lorsque le moteur construit la réponse, il rapatrie toutes les lignes correspondantes, généralement dans l’ordre ou il les trouve, même si ces dernières sont en double, c'est à dire qu'il récupère toutes les lignes (ALL par défaut). C’est pourquoi il est souvent nécessaire d’utiliser le mot clef DISTINCT qui permet d‘éliminer les doublons dans la réponse.Exemples 3 et 4SELECT CLI_PRENOMFROM T_CLIENTWHERE TIT_CODE = 'M.'CLI_PRENOM----------AlainMarcAlainPaulJeanAlainMarcelArsèneAndréDaniel...SELECT distinct CLI_PRENOMFROM T_CLIENTWHERE TIT_CODE = 'M.'CLI_PRENOM----------AlainAlexandreAndréArnaudArsèneBernardChristianChristopheDanielDenis...==L'opérateur AS==Vous pouvez rajouter autant de colonnes que vous le désirez en utilisant le mot clef AS.En principe l’opérateur AS sert à donner un nom à de nouvelles colonnes créées par la requête.Exemple 5SELECT CLI_NOM as NOM, 'homme' as SEXEFROM T_CLIENTWHERE TIT_CODE = 'M.'NOM SEXE------- -----DUPONT hommeMARTIN hommeBOUVIER hommeDUBOIS hommeDREYFUS hommeFAURE hommePAUL hommeDUVAL hommePHILIPPE hommeCHABAUD homme...==Opérateur de concaténation==L'opérateur || (double barre verticale) permet de concaténer des champs de type caractères.Exemple 6SELECT TIT_CODE || ' ' || CLI_PRENOM || ' ' || CLI_NOM as NOMFROM T_CLIENTNOM-----------------------M. Alain DUPONTM. Marc MARTINM. Alain BOUVIERM. Paul DUBOISM. Jean DREYFUSM. Alain FAUREM. Paul LACOMBEMelle. Evelyne DUHAMELMme. Martine BOYERM. Martin MARTIN...Néanmoins on trouve dans certains SGBDR le + comme opérateur de concaténation, comme la fonction CONCAT.==Opérateurs mathématiques de base==On, peut utiliser les opérateurs mathématiques de base pour combiner différentes colonnes(,+,-, *, /,).Exemple 7SELECT CHB_ID, TRF_CHB_PRIX * 1.206 AS TARIF_TTCFROM TJ_TRF_CHBWHERE TRF_DATE_DEBUT = '2001-01-01'CHB_ID TARIF_TTC------ --------- 1 424,51 2 482,40 3 617,47 4 424,51 5 463,10 6 482,40 7 424,51 8 540,29 9 482,40 10 617,47...==Particularité du "FROM"==Il est possible de surnommer une table dans la clause FROM, dans ce cas, la syntaxe de la partie FROM de la commande SELECT est la suivante :FROM nom_de_table ou nom_de_la_vue surnomNous verrons dans quel cas ce renom est nécessaire ou obligatoire.NOTA : certains auteurs préfèrent utiliser le mot d'alias que nous rejetons car il indique souvent un autre concept, ou de synonyme, que nous acceptons de manière timorée...==Utilisation du caractère double quote (guillemet)==Lorsqu’un nom d’un élément d’une base de données (table, colonne par exemple) est identique à un mot clef du SQL, il convient de l’entourer de guillemets (double quote). En principe, les mots réservés du SQL sont déconseillés pour nommer des objets du modèle physique de données...Imaginons une table de nom JOIN, composée des champs suivants :NOM SELECT DATE NOT------- ------ ------- ---DURAND Oui 1999-11-12 FDUVAL Non 1998-01-17 MExemple 8 : nous désirons sélectionner les colonnes SELECT et DATE lorsque la colonne NOT vaut F...SELECT SELECT, DATEFROM JOINWHERE NOT = 'F'ERREUR !SELECT "SELECT", "DATE"FROM "JOIN"WHERE "NOT" = 'F'Correct : on entoure les mots clefs du SQL par des doubles côtesCela est aussi nécessaire lorsque le nom (d'une colonne ou d'une table) est composé de caractères particuliers tels que les blancs ou autres, ce qui est à éviter.REMARQUE : les noms des identifiants d'objet de base de données doivent être écrit dans le jeux de caractères restreint suivant :[A..Z] + [a..z] + [0..9] + [ _ ].Ils ne doivent pas commencer par un chiffre et sont insensibles à la casse (indifférence entre majuscule et minuscule).=La clause ORDER BY=Vous trouverez des compléments d'information sur le sujet aux pages 66 à 70 de l'ouvrage "SQL", collection "La Référence", Campus Press éditeur.ORDER BY colonne1 | 1 [ASC ou DESC ] [, colonne2 | 2 [ASC ou DESC ] ...Cette clause permet de définir le tri des colonnes de la réponse, soit en précisant le nom littéral de la colonne, soit en précisant son n° d'ordre dans l'énumération qui suit le mot clef SELECT.ASC spécifie l’ordre ascendant et DESC l’ordre descendant du tri. ASC ou DESC peut être omis, dans ce cas c'est l'ordre ascendant qui est utilisé par défaut.Bien que la clause ORDER BY ne soit pas nécessaire, il est souvent utile de trier la réponse en fonction des colonnes. En revanche le temps de réponse s'en ressent souvent.Pour spécifier l'ordre de tri, on doit placer les noms des colonnes séparées par des virgules juste après le mot clef "ORDER BY", dans l'ordre voulu.. On peut aussi utiliser le rang de chaque colonne dans l'ordre spécifié dans la clause SELECT.Attention : le tri est un tri interne, il ne faut donc placer dans cette clause que les noms des colonnes présentées dans la clause SELECT.Souvent, le fait de placer DISTINCT suffit, en général, à établir un tri puisque le moteur doit se livrer à une comparaison des lignes mais ce mécanisme n'est pas garanti car ce tri s’effectue dans un ordre non contrôlable qui peut varier d’un serveur à l’autre.Exemple 9SELECT CLI_NOM, CLI_PRENOMFROM T_CLIENTORDER BY CLI_NOM, CLI_PRENOMouSELECT CLI_NOM, CLI_PRENOMFROM T_CLIENTORDER BY 1, 2CLI_NOM CLI_PRENOM-------- ----------AIACH AlexandreALBERT ChristianAUZENAT MichelBACQUE MichelBAILLY Jean-FrançoisBAVEREL FrédéricBEAUNEE PierreBENATTAR BernardBENATTAR PierreBENZAQUI Joël...REMARQUE : les marqueurs NULL sont situés en premier dans l'ordre ainsi établi.NOTA : Un problème, qui n’est pas résolu, est de pouvoir choisir l’ordre des colonnes de la réponse. Sur certains serveurs cela peut être obtenu en plaçant les noms des colonnes à obtenir dans l’ordre où l’on veut les voir apparaître dans la clause SELECT, mais cette possibilité n'est jamais garantie...ATTENTION : la clause ORDER BY est la dernière clause de tout ordre SQL et ne doit figurer qu'une seule fois dans le SELECT, même s'i l existe des requêtes imbriquées ou un jeu de requêtes ensemblistes.=La clause WHERE=Vous trouverez des compléments d'information sur le sujet aux pages 62 à 165 de l'ouvrage "SQL", collection "La Référence", Campus Press éditeur.WHERE prédicatsLe prédicat doit contenir n’importe quelle expression logique renvoyant une valeur vrai.Ainsi, une requête aussi stupide que la suivante, est supposée fonctionner :Exemple 10SELECT CLI_NOMFROM T_CLIENTWHERE 1=1CLI_NOM------- DUPONTMARTINBOUVIERDUBOISDREYFUSFAURELACOMBEDUHAMELBOYERMARTIN...Attention : la plupart des SGBDR ne comportent pas de colonne de type booléen. Une requête comme la première risque d'échouer.Exemple 11SELECT *FROM TJ_CHB_PLN_CLIWHERE CHB_PLN_CLI_OCCUPEERREUR !bien que CHB_PLN_CLI_OCCUPE puisse être du booléen, la plupart des compilateurs SQL n'accepte pas ce test direct.SELECT *FROM TJ_CHB_PLN_CLIWHERE CHB_PLN_CLI_OCCUPE = TrueCORRECT ...Mais sur certains compilateur SQL il faut faire : CHB_PLN_CLI_OCCUPE = 'True' (littéral).Si le type booléen n'existe pas, alors il faut faire CHB_PLN_CLI_OCCUPE = 1 si l'on a choisi de définir les booléens comme INTEGER(1) avec 0 et 1Pour palier au manque de booléen, on utilise soit un littéral (True/False, Vrai/Faux, Oui/Non), soit un numérique avec les valeurs 0 (Faux) et 1 (Vrai). L'avantage des valeurs numériques est que le calcul logique est comparable aux divisions et additions...opérateur ETFAUX VRAIFAUX FAUX FAUXVRAI FAUX VRAIsimilitude entre le ET et la multiplicationmultiplication0 10 0 01 0 1, <> 0opérateur OUFAUX VRAIFAUX FAUX VRAIVRAI VRAI VRAIsimilitude entre le OU et l'additionaddition0 10 0 1, <> 01 1, <> 0 2, <> 0==Opérateurs de comparaison==Dans la clause WHERE, vous disposez de différents opérateurs de comparaisons logiques :WHERE valeur1 [NOT et] = ou < ou <= ou > ou >= ou <>valeur2 [OR ou AND ...]Exemple 12SELECT CLI_NOM, CLI_PRENOMFROM T_CLIENTWHERE CLI_NOM >= 'A' AND CLI_NOM <'E'ouSELECT CLI_NOM, CLI_PRENOMFROM T_CLIENTWHERE (CLI_NOM >= 'A') AND (CLI_NOM <'E')plus lisible !CLI_NOM CLI_PRENOM------- ----------DUPONT AlainBOUVIER AlainDUBOIS PaulDREYFUS JeanDUHAMEL EvelyneBOYER MartineDUVAL ArsèneDAUMIER AmélieCHABAUD DanielBAILLY Jean-François...Ici on obtient tous les noms et prénoms des clients dont le nom commence par les lettres A, B, C ou D.Attention : dans certains moteurs de requête SQL l’opérateur « différent de » (<>) s’écrit !===Opérateur IN==L'opérateur IN permet de rechercher si une valeur se trouve dans un ensemble donné, quel que soit le type des valeurs de référence spécifiées (alpha, numérique, date…). Bien entendu, il est possible d’inverser le fonctionnement de l’opérateur IN en lui adjoignant l’opérateur NOT.Exemple 13SELECT TIT_CODE, CLI_NOM, CLI_PRENOM FROM T_CLIENTWHERE TIT_CODE IN ('Mme.', 'Melle.')TIT_CODE CLI_NOM CLI_PRENOM-------- ---------- ----------Mme. BOYER MartineMme. GALLACIER NoëlleMme. HESS LucetteMme. LETERRIER MoniqueMme. MARTINET CarmenMme. DAVID JacquelineMme. MOURGUES JacquelineMme. ZAMPIERO AnnickMme. ROURE Marie-LouiseMme. DE CONINCK Patricia...On recherche les clients de sexe féminin, basés sur le code titre.Le contenu de la parenthèse peut être remplacé par le resultat d'une requête possédant une colonne unique. Dans ce cas on parle de requêtes imbriquées, ce que nous verrons plus loin.==Opérateur BETWEEN==L'opérateur BETWEEN permet de rechercher si une valeur se trouve dans un intervalle donné, quel que soit le type des valeurs de référence spécifiées (alpha, numérique, date...).Ainsi, la requête vue dans l'exemple 12 peut s'écrire :Exemple 14SELECT CLI_NOM, CLI_PRENOMFROM T_CLIENTWHERE CLI_NOM BETWEEN 'A' AND 'E'CLI_NOM CLI_PRENOM------- ----------DUPONT AlainBOUVIER AlainDUBOIS PaulDREYFUS JeanDUHAMEL EvelyneBOYER MartineDUVAL ArsèneDAUMIER AmélieCHABAUD DanielBAILLY Jean-François...NOTA : les opérateurs IN et BETWEEN sont très pratiques dans le cas où l’on désire effectuer des requêtes où l’utilisateur peut saisir une liste de choix multiples (IN) ou une plage de valeur (BETWEEN).==Opérateur LIKE==L'opérateur LIKE permet d’effectuer une comparaison partielle. Il est surtout employé avec les colonnes contenant des données de type alpha. Il utilise les jokers % et _ (‘pour cent’ et ‘blanc souligné’). Le joker % remplace n'importe quelle chaîne de caractères, y compris la chaîne vide. Le blanc souligné remplace un et un seul caractère.Exemple 15SELECT CLI_NOM, CLI_PRENOMFROM T_CLIENTWHERE CLI_NOM LIKE 'B%'CLI_NOM CLI_PRENOM------- ----------BOUVIER AlainBOYER MartineBAILLY Jean-FrançoisBOUCHET MichelBEAUNEE PierreBERGER Jean-PierreBOURA AndréBENZAQUI JoëlBAVEREL FrédéricBERTRAND Christophe...On recherche les clients dont le nom commence par B.Mais si vos données sont susceptibles de contenir un des deux caractères joker, alors il faut recourir à une séquence d’échappement, à l’aide du mot clef ESCAPE...Cherchons les clients dont l'enseigne contient au moins un caractère blanc souligné :Exemple 16SELECT *FROM T_CLIENTWHERE CLI_ENSEIGNE LIKE '%_%'CLI_ID TIT_CODE CLI_NOM CLI_PRENOM CLI_ENSEIGNE------ -------- ---------- ------------- ------------------------------ 2 M. MARTIN Marc Transports MARTIN & fils 6 M. FAURE Alain Boulangerie du marché 10 M. MARTIN Martin HERMAREX IMPORT_EXPORT 11 M. PAUL Marcel Cie Internationale des Mach... 17 M. BAILLY Jean-François Entreprise DUPONT CHAUFFAGE 24 M. CHTCHEPINE Dominique HOTEL *** DE LA GARE 26 M. GARREAU Paul IBM Corp. 34 Mme. GALLACIER Noëlle Transports GALLACIER 42 Mme. LETERRIER Monique SA AROMAX ENTREVONT 49 M. COULOMB Renaud Cabinet COULOMN et CALEMANTSELECT *FROM T_CLIENTWHERE CLI_ENSEIGNE LIKE '%#_%' ESCAPE '#'CLI_ID TIT_CODE CLI_NOM CLI_PRENOM CLI_ENSEIGNE------ -------- ---------- ------------- ------------------------------ 10 M. MARTIN Martin HERMAREX IMPORT_EXPORTPour traiter ce cas, on défini « # » comme caractère d’échappement. Le caractère qui suit ce caractère d’échappement est donc interprété comme un caractère et non comme un joker.NOTA : l’opérateur LIKE effectue une recherche en tenant compte de la différence entre lettres majuscules et minuscules. Si vous voulez effectuer une recherche en ne tenant aucunement compte de la différence entre majuscules et minuscules, il convient d’utiliser les opérateurs LOWER et UPPER (voir ci dessous). Mais la plupart du temps, l'utilisation du like dans un SGBDR donné ignore la casse.==Résumé des opérateurs pour les prédicats de la clause WHERE==Voici une tableau résumant les principaux opérateurs utilisés pour la construction des prédicats :opérateurs de comparaisons = <> < <= > >=connecteurs logiques {OR | AND}opérateur de négation NOTparenthèses ( ... )opérateurs mathématiques + - * /comparaison logique IS [NOT] {TRUE | FALSE | UNKNOWN}comparaison avec valeur IS [NOT] NULLintervalle valeur BETWEEN borne_basse AND borne_hautecomparaison partielle de chaîne de caractères valeur LIKE motif [ESCAPE echappement]comparaison à une liste de valeur valeur [NOT] IN (liste)=Fonctions diverses=Vous trouverez des compléments d'information sur le sujet aux pages 71 à 107 de l'ouvrage "SQL", collection "La Référence", Campus Press éditeur.==Trantypage à l'aide de la fonction CAST==Il permet de changer le type de données d'une colonne afin d’effectuer une comparaison de données de type hétérogène par exemple entre un champ contenant des données numériques et un champ contenant des données de type chaîne de caractères...Sa syntaxe est CAST(colonne AS nouveau type).Exemple 17SELECT CHB_ID, CHB_NUMERO, CHB_POSTE_TELFROM T_CHAMBREWHERE CAST(CHB_POSTE_TEL AS INTEGER) / 10 > CHB_NUMEROCHB_ID CHB_NUMERO CHB_POSTE_TEL------ ---------- ------------- 1 1 101 2 2 102 3 3 103 4 4 104 5 5 105 6 6 106 7 7 107 8 8 108 9 9 109 10 10 110L’opérateur CAST permet de transtyper les valeurs contenues dans une colonne. Bien entendu il faut qu'un type de donnée puisse être converti dans un autre type (compatibilité de types) afin que la réponse ne soit pas entaché d'erreurs ou d'omissions.Exemple 18SELECT ADR_VILLE, CAST(ADR_CP AS INTEGER) + 1FROM T_ADRESSEADR_VILLE ADR_CP ADR_CP + 1------------ ------ ----------VERSAILLES 78000 78001MONTMAIZIN 11254 11255PARIS 75015 75016VERGNOLLES CEDEX 452 84524 84525MARSEILLE 13002 13003PARIS 75012 75013BONNEUIL CEDEX 94152 94153PARIS 75012 75013PARIS 75014 75015PARIS 75017 75018...==Mise en majuscule / Minuscule==Les opérateurs LOWER et UPPER permettent de mettre en majuscule ou en minuscule des chaînes de caractères dans les requêtes.Exemple 19SELECT upper(CLI_PRENOM), lower(CLI_NOM)FROM T_CLIENTCLI_NOM CLI_PRENOM------- ----------ALAIN dupontMARC martinALAIN bouvierPAUL duboisJEAN dreyfusALAIN faurePAUL lacombeEVELYNE duhamelMARTINE boyerMARTIN martin...NOTA : pour effectuer une recherche en ne tenant aucunement compte de la différence entre majuscules et minuscules, il faut utiliser l’opérateur UPPER (ou lower mais attention à la transformation des accents !) :Exemple 20SELECT *FROM T_CLIENTwhere upper(CLI_PRENOM) = upper(CLI_NOM)CLI_ID TIT_CODE CLI_NOM CLI_PRENOM CLI_ENSEIGNE------ -------- ------- ---------- ---------------------- 10 M. MARTIN Martin HERMAREX IMPORT_EXPORTNOTA : certains SGBDR permettent de paramétrer l'activation de la recherche systématique des chaînes de caractères sans tenir compte de la casse. Sur d'autres, le paramétrage permet de confondre les lettres accentuées ou non...==Supprimer les blancs (ou tout autre caractères)==La fonction TRIM permet de supprimer en tête ou en queue (ou les deux) le blanc ou tout autre caractère spécifié.TRIM ([LEADING ou TRAILING ou BOTH] [caractère] FROM nom de colonne)LEADING : suppression en têteTRAILING : suppression en queueBOTH : suppression en tête et en queueDans notre table téléphone, nous voulons supprimer le zéro de tête des n° afin de pouvoir les communiquer aux étrangers qui n’ont pas besoin de composer ce chiffre (ils doivent simplement composer le 00 33 suivi du numéro à 9 chiffres).Exemple 21SELECT TEL_NUMERO, '00~33 ' || TRIM(LEADING '0' FROM TEL_NUMERO) AS TEL_INTERNATIONAL FROM T_TELEPHONETEL_NUMERO TEL_INTERNATIONAL-------------- -----------------01-45-42-56-63 00~33 1-45-42-56-6301-44-28-52-52 00~33 1-44-28-52-5201-44-28-52-50 00~33 1-44-28-52-5006-11-86-78-89 00~33 6-11-86-78-8902-41-58-89-52 00~33 2-41-58-89-5201-51-58-52-50 00~33 1-51-58-52-5001-54-11-43-21 00~33 1-54-11-43-2106-55-41-42-95 00~33 6-55-41-42-9501-48-98-92-21 00~33 1-48-98-92-2101-44-22-56-21 00~33 1-44-22-56-21...NOTA : certains serveurs SQL proposent différentes fonctions comme LTRIM et RTRIM pour une suppression des blancs en tête ou en queue.==Extraire une sous chaîne==La fonction SUBSTRING permet d’extraire une sous chaîne d’une chaîne de caractère. Elle a besoin de l’ordre du premier caractère et du nombre de caractères sur lequel elle doit opérer.SUBSTRING ( nom de colonne FROM n TO m)Extrait la sous chaîne de nom de colonne en commençant à n sur m caractères.Exemple 22SELECT CLI_NOM, CLI_PRENOM, SUBSTRING(CLI_PRENOM FROM 1 FOR 1) || SUBSTRING(CLI_NOM FROM 1 FOR 1) AS INITIALESFROM T_CLIENTCLI_NOM CLI_PRENOM INITIALES------- ---------- ---------DUPONT Alain ADMARTIN Marc MMBOUVIER Alain ABDUBOIS Paul PDDREYFUS Jean JDFAURE Alain AFLACOMBE Paul PLDUHAMEL Evelyne EDBOYER Martine MBMARTIN Martin MM...Cet exemple construit les initiales des clients à partir des colonnes CLI_NOM et CLI_PRENOM_CLI.Attention, certains SGBDR utilisent la fonction SUBSTR.==Opérateur de traitement des dates=====Extraire un paramètre temporel d'une date===L’opérateur EXTRACT permet d’extraire depuis une date, le jour le mois ou l’année…EXTRACT ( YEAR ou MONTH ou DAY FROM nom de colonne )Dans la table des réservation on recherche l'identifiant des chambres ayant été réservées au cours du mois de mai de n'importe quelle année et pour 3 personnes.Exemple 23SELECT distinct CHB_IDFROM TJ_CHB_PLN_CLIWHERE EXTRACT(MONTH FROM PLN_JOUR) = 5 AND CHB_PLN_CLI_RESERVE = 1 AND CHB_PLN_CLI_NB_PERS = 3CHB_ID------ 1 5 6 8 11 12 16 17 18 20NOTA : il est dommage de constater que la fonction EXTRACT du standard SQL, souvent fort utile, est rarement présente dans les moteurs de bases de données. Ni Access, ni Oracle, ni Sybase, ni SQL Server en sont dotés. Seul le middleware BDE de Borland Inprise Corel permet d'exploiter pleinement cette fonction avec les SGBDR Paradox, dBase, FoxPro, InterBase, MSSQL, Sybase, Informix, DB2, Oracle.Cependant il est courant de trouver des fonctions s'en approchant : Exemple DATEPART dans SQL Server.===Heure et date courante===L’heure courante, la date courante et le combiné date/heure courant peuvent être obtenu à l’aide des fonctions CURRENT_DATE, CURRENT_TIME et CURRENT_TIMESTAMP.Exemple 24SELECT distinct CHB_IDFROM TJ_CHB_PLN_CLIWHERE (CHB_PLN_CLI_RESERVE = 1) AND PLN_JOUR BETWEEN CURRENT_DATE and CURRENT_DATE + 14 AND CHB_PLN_CLI_NB_PERS = 3attention, le résultat de cette requête varie en fonction de la date à laquelle vous l'exécutez !CHB_ID------ 1 5 6 8 11 12 16 17 18 20Cette requête renvoie les chambres réservées pour 3 personnes entre la date du jour et pour les deux semaines à venir.Attention : la plupart des SGBDR n'acceptent pas encore cette version normalisée des fonctions de recherche de temps courant. Voici les fonctions spécifiques aux différents serveurs SQL :Oracle SYSDATE()Sybase GETDATE()SQL Server GETDATE()Access NOW()MySQL NOW()Paradox (QBE) TODAY==Opérateurs statistiques==Il est possible de réaliser des comptages statistiques sur les colonnes, à l'aide des opérateurs AVG (moyenne), MAX (maximum), MIN (minimum), SUM (total), COUNT (nombre). On les appelent aussi fonctions d'aggrégations.Exemple 25SELECT AVG(TRF_CHB_PRIX) as MOYENNE, MAX(TRF_CHB_PRIX) as MAXI, MIN(TRF_CHB_PRIX) as MINI, SUM(TRF_CHB_PRIX) as TOTAL, COUNT(TRF_CHB_PRIX) as NOMBRE FROM TJ_TRF_CHB WHERE TRF_DATE_DEBUT = '2001-01-01'MOYENNE MAXI MINI TOTAL NOMBRE-------- -------- -------- ---------- ------406,74 F 512,00 F 352,00 F 7 728,00 F 19Cette requête calcule la moyenne, le montant maximum, minimum, la totalisation et le nombre des tarifs de chambre pour la date de debut du premier janvier 2001.On peut s'étonner que les opérateurs statistiques VARIANCE ou STDDEV (écart type) soient rarement présents dans les SGBDR car il s'agit de fonctions statistiques qui possèdent une grande utilité. Mais la norme SQL 92 ne les a pas retenu. Cependant, ils existent notamment dans Oracle. Cela est d'autant plus étonnant que les techniques modernes du DataWareHouse et en particulier le DataMining, utilisent ces fonctions dans le cadres de calculs statistiques d'hypercubes (techniques OLAP et ROLAP).ATTENTION : nous verrons que l'utilisation des fonctions statistiques nécessite la plupart du temps la mise en place d'une clause de groupage, afin de déterminé quel est le sous ensemble cible d'agrégation pour les calculs.==Autres fonctions normalisées==BIT_LENGTH Taille d'une colonne de type BIT ou BIT VARYING (nombre de bits)CHAR_LENGTH Taille d'une colonne de type caractère (nombre de caractères)OCTET_LENGTH Taille d'une colonne de type caractère (nombre d'octets)CURRENT_DATE Date en coursCURRENT_TIME Heure en coursCURRENT_TIMESTAMP Date et heure en coursCONVERT Conversion paramétrèe d'une chaîne de caractèresPOSITION Position d'une chaîne de caractères dans une sous chaîneTRANSLATE Traduction d'une chaîne de caractères dans un format spécifié==Autres opérateurs mathématiques (non normalisés)==Les opérateurs ci dessous peuvent être implémentés dans différents moteurs.ABS valeur absolueMOD moduloSIGN signeSQRT racine carréeCEIL plus petit entierFLOOR plus grand entierROUND arrondiTRUNC tronquéEXP exponentielleLN logarithme népérienLOG logarithme décimalPOWER puissanceCOS cosinusCOSH cosinus hyperboliqueSIN sinusSINH sinus hyperboliqueTAN tangenteTANH tangente hyperboliquePI constante PiCertains sont rarement implémentés du fait que les SGBDR sont axés sur l’informatique de gestion, la collecte et le traitement d’informations et non le calcul mathématique.Attention : le nom de certains de ces opérateurs peut différer d’un SGBDR à l’autre.==Autres opérateurs de traitement des chaînes de caractères (non normalisés)==CONCAT concaténation : équivalent du || - Nota : utiliser de préférence || plus standard. Le + entre colonne alphanumérique peut aussi souvent être utilisé comme opérateur de concaténation, préférez de toutes façons ||INITCAP initiales en lettres capitalesLPAD complément ou troncature à n position à gaucheLTRIM / RTRIM suppression en tête/queue d’une chaîneREPLACE remplacementRPAD complément ou troncature à n position à droiteSOUNDEXcode de consonnance – Attention : phonétique souvent anglaiseINSTR Position d’une chaîne dans une sous chaîneLENGTH longueur de la chaîneTO_CHAR numérique sous forme littérale – Attention : souvent en anglaisASCII code ASCII d'un caractèreCHR caractère dont le code ASCII est donnéREVERSE Inverse l'ordre des caractères d'une châineFLIP Pivote les parties droite et gauche d'une chaîne par rapport au n° du caractère servant de pivot.Attention : le nom de certains de ces opérateurs peut différer d’un SGBDR à l’autre.==Autres opérateurs sur les valeurs temporelles (non normalisés)==ADD_MONTH, ADD_DAY, ADD_YEAR ... ajoute des mois, des jours des années à une dateLAST_DAY renvoie le n° du dernier jour d’un mois d’une dateMONTH_BETWEEN nombre de mois entre deux datesNEXT_DAY date du prochain jour d’un nom donnéTO_CHAR date sous forme littérale – Attention : souvent en anglaisDATE_DIFF différence entre deux dates==Opérateurs d'ensemble (non normalisés)==GREATEST la plus grande des valeurs d’un ensembleLEAST la plus petite des valeurs d’un ensembleDECODE renvoi la valeur situé en n-ième position=Traitement des "valeurs" nulles=Vous trouverez des compléments d'information sur le sujet aux pages 115 à 120 de l'ouvrage "SQL", collection "La Référence", Campus Press éditeur.NOTA : le NULL n'est pas à proprement parler une valeur, mais bien l'absence de valeur, c'est pourquoi nous parlerons de marqueur NULL et non de valeur NULL.Le marqueur NULL pose une quantité de problèmes et nous allons dans ce paragraphe soulever un coin du voile, que nous traiterons un peu plus tard dans le cas général de la recherche des occurrences d’inexistence.==Le null n'est ni la chaîne vide, ni le zéro==NULL n'est pas une valeur. C'est un marqueur. Par conséquent le marqueur NULL ne peut jamais être comparé a une valeur.Recherchons les clients qui n'ont pas d'enseigne.Exemple 26SELECT CLI_ID, CLI_NOMFROM T_CLIENTWHERE CLI_ENSEIGNE = CLI_ID CLI_NOM------- --------...La réponse doit produire une table vide !Pour controurner ce problème il faut :soit penser à enregistrer une chaîne de caractère vide lors de l'insertion des données dans la tablesoit la clause WHERE avec un opérateur spécialisé dans le traitement des valeurs nullesLe problème des « null » a été bien décrit dans le livre de Joe Celko intitulé « SQL avancé » publié par Thomson International Publishing. Joe Celko pose la question en ces termes : l’absence d’information est-elle due à son ignorance ou à son impertinence ? Pourquoi donc ne pas faire de différence entre la couleur du toit d’une voiture qui n’est pas connue, et la couleur du toit d’une moto qui n’est pas applicable… Certains logiciens de l’algèbre relationnel sont même allés plus loin en proposant différentes valeurs pour gérer les différents cas, en distinguant des cas très différents : le « null », le « inconnu » et le « inapplicable »...Il y a donc un véritable dilemme à utiliser des requêtes en se basant sur des critères d’absence de valeur et il faut toujours faire très attention aux clauses qui utilisent des références aux valeurs nulles, suivant ce que l’on veut obtenir. D’autant plus que les NULL se propagent dans les calculs.Voici un extrait de la table T_LIGNE_FACTURELIF_ID FAC_ID LIF_QTE LIF_REMISE_POURCENT LIF_REMISE_MONTANT LIF_MONTANT LIF_TAUX_TVA-------- -------- --------- ------------------- ------------------ ----------- ------------ 1 1 1,00 15,00 NULL 320,00 F 18,60 2 3 1,00 NULL 50,00 F 250,00 F 18,60 3 3 1,00 NULL 50,00 F 320,00 F 18,60 4 3 1,00 NULL 50,00 F 240,00 F 18,60 5 5 1,00 NULL NULL 320,00 F 18,60 6 5 1,00 NULL NULL 220,00 F 18,60 7 7 1,00 NULL NULL 220,00 F 18,60 8 7 1,00 NULL NULL 250,00 F 18,60 9 7 1,00 NULL NULL 320,00 F 18,60 10 7 1,00 NULL NULL 270,00 F 18,60...Nous voulons calculer le montant total de chacune des lignes de cette table, pour une facture donnée.La requête pour FAC_ID = 3 est la suivante :Exemple 27SELECT FAC_ID, sum (LIF_QTE * (LIF_MONTANT - LIF_REMISE_MONTANT) * (1 - LIF_REMISE_POURCENT / 100)) AS TOTAL_FAC, sum((LIF_QTE * (LIF_MONTANT - LIF_REMISE_MONTANT) * (1 - LIF_REMISE_POURCENT / 100)) * (LIF_TAUX_TVA / (100 + LIF_TAUX_TVA))) AS TOTAL_TAXESFROM T_LIGNE_FACTUREWHERE FAC_ID = 3GROUP BY FAC_IDFAC_ID TOTAL_FAC TOTAL_TAXES------ --------- ----------- 3 NULL NULLOn constate que pour les lignes qui n'ont pas de valeurs renseignées dans les colonnes LIF_REMISE_POURCENT, LIF_REMISE_MONTANT, le résultat du calcul donne la valeur « null » qui se traduit à l'affichage par... rien !NOTA : en général, pour se sortir de ce mauvais pas, on peut, lors de la création de la base de données, obliger tous les champs de type numérique (réels ou entiers) a ne pas accepter la valeur nulle et prendre par défaut la valeur zéro...Attention : l’arithmétique des nuls est assez particulière... Souvenez vous toujours que les NULL se propagent. Cela est vrai pour les numériques, les dates mais aussi pour les chaînes de caractères. Ainsi SQL opère une distinction entre une chaîne de caractère vide et un champ non renseigné. Dans le cas de la concaténation d’une colonne NULL et d’une colonne proprement renseigné, la valeur renvoyée sera NULL !!!==Opérateurs de traitement des marqueurs NULL==La norme SQL 2 (1992) spécifie une comparaison et différents opérateurs sur les marqueurs NULL :IS NULL / IS NOT NULL : teste si la colonne est vide ou non vide.COALESCE() qui recherche la première valeur non vide dans un ensembleNULLIF NULLifie une colonne en fonction d'une valeur donnéeCOALESCE ( valeur1, valeur2 [, valeur3] ... )NULLIF ( nom_de_colonne, valeur)expression IS [NOT] NULLNOTA : ISNULL (en un seul mot) est une autre fonction de branchement que l'on rencontre parfois (renvoi une valeur si la valeur est nulle). Dans la même veine, NVL ou VALUE sont des expressions équivalentes à COALESCE que l'on rencontre sur certains SGBDR.La requête précédente s'exprime, à l'aide de l'opérateur ISNULL :Exemple 28SELECT FAC_ID, sum (LIF_QTE * (LIF_MONTANT - ISNULL(LIF_REMISE_MONTANT, 0)) * (1 - ISNULL(LIF_REMISE_POURCENT, 0) / 100)) AS TOTAL_FAC, sum((LIF_QTE * (LIF_MONTANT - ISNULL(LIF_REMISE_MONTANT, 0)) * (1 - ISNULL(LIF_REMISE_POURCENT, 0) / 100)) * (LIF_TAUX_TVA / (100 + LIF_TAUX_TVA))) AS TOTAL_TAXESFROM T_LIGNE_FACTUREWHERE FAC_ID = 3GROUP BY FAC_IDFAC_ID TOTAL_FAC TOTAL_TAXES------ --------- ----------- 3 810.0 127.03NOTA : En règle générale, dès que l'on traite des colonnes contenant des valeurs monétaires ou numériques, il est bon de faire en sorte que la colonne soit obligatoire et que par défaut elle soit renseignée à zéro.Sinon, il faudra faire un usage systématique des fonctions NULLIF ou COALESCE dans tous les calculs et cela grèvera les performances d'exécution !=Négation de valeurs=C'est l'opérateur NOT qui réalise la négation de valeurs et inverse la valeur logique d'un prédicat.L'opérateur NOT peut être combiné avec la plupart des opérateurs de comparaison. Mais il devient très intéressant lorqu'il est combiné aux opérateurs IN, BETWEEN, LIKE et NULLRecherchons par exemple toutes les chambres permettant de recevoir au moins 3 personnes, ne comportant pas le chiffre 4 (chiffre de la mort au japon) ni les chambres portant les n° 7 et 13 pour un client particulièrement superstitieux...Exemple 29SELECT CHB_ID, CHB_NUMERO, CHB_COUCHAGEFROM T_CHAMBREWHERE NOT (CAST(CHB_NUMERO AS VARCHAR(10)) LIKE '%4%') AND CHB_NUMERO NOT IN ('7', '13') AND CHB_COUCHAGE >= 3CHB_ID CHB_NUMERO CHB_COUCHAGE ----------- ---------- ------------ 1 1 35 5 36 6 58 8 311 11 312 12 315 16 316 17 517 18 319 20 3Nous verrons que le NOT IN est particulièrement précieux dans les requêtes imbriquées, c'est à dire les requêes multitables.Nous voulons maintenant le nom des clients qui ne commence pas par 'DU' :Exemple 30SELECT CLI_NOM FROM T_CLIENTWHERE CLI_NOM NOT LIKE 'DU%'-------------------------------- MARTIN BOUVIER DREYFUS FAURE LACOMBE BOYER MARTIN PAUL PHILIPPE PIERRELAYE ...=Les branchements dans le SQL=Vous trouverez des compléments d'information sur le sujet aux pages 120 à 123 de l'ouvrage "SQL", collection "La Référence", Campus Press éditeur.SQL possède un branchement à la manière des IF et autres structures de test des langages procéduraux.Mais il convient de ne l'utiliser qu'a bon escient, c'est à dire aussi peu souvent que possible, beaucoup de cas ouvant être traités soit par le COALESCE soit par des requêtes avec des opérations ensemblistes de type UNION. En effet les performances se dégradent très vite lors de l'usage du CASE à cause de l'impossibilité d'effcetuer des traitements par "paquets".La structure CASE du SQL comprend deux syntaxes différentes. Le CASE pour branchement sur les valeurs d'une expression et le CASE généralisé.==CASE sur expression==Dans ce cas, la syntaxe est la suivante :CASE expression WHEN valeur1 THEN expression1 [WHEN valeur2 THEN expression2] ... [ELSE expression_défaut]ENDExemple 31SELECT CHB_NUMERO, CASE CHB_ETAGE WHEN 'RDC' THEN 0 WHEN '1er' THEN 1 WHEN '2e' THEN 2 END AS ETAGE, CHB_COUCHAGEFROM T_CHAMBREORDER BY ETAGE, CHB_COUCHAGECHB_NUMERO ETAGE CHB_COUCHAGE ---------- ----------- ------------ 2 0 23 0 24 0 21 0 37 1 29 1 210 1 25 1 38 1 311 1 312 1 36 1 514 2 215 2 219 2 221 2 216 2 318 2 320 2 317 2 5==CASE généralisé==L'expression disparait au profit de différents prédicats.CASE WHEN condition1 THEN expression1 [WHEN condition2 THEN expression2] ... [ELSE expression_défaut]ENDExemple 32SELECT CHB_NUMERO, CASE WHEN CHB_ETAGE = 'RDC' THEN 0 WHEN CHB_ETAGE = '1er' THEN 1 WHEN CHB_ETAGE = '2e' THEN 2 END AS ETAGE, CHB_COUCHAGEFROM T_CHAMBREORDER BY ETAGE, CHB_COUCHAGECHB_NUMERO ETAGE CHB_COUCHAGE ---------- ----------- ------------ 2 0 23 0 24 0 21 0 37 1 29 1 210 1 25 1 38 1 311 1 312 1 36 1 514 2 215 2 219 2 221 2 216 2 318 2 320 2 317 2 5Qui donne le même résultat !ATTENTION : tous les SGBDR ne supportent pas ces deux syntaxes.NOTA : dans les deux cas il est possible de remplacer le IF d'un langage procédural :CASE WHEN condition1 THEN expression1 [ELSE expression_défaut]ENDCASE expression WHEN valeur1 THEN expression1 [ELSE expression_défaut]END=Le constructeur de lignes valuées (ROW VALUE CONSTRUCTOR)=Nous voici devant un élément peu connu du SQL car rarement implanté. Rendons hommage à Oracle car il est l'un des rares SGBDR à l'implémenter. Cette construction peut vous tirer d'embarras pour bien des cas en simplifiant drastiquement vos requêtes.L'idée consiste à dire que différentes colonnes devant correspondre à différents critères comparées à l'aide d'un même opérateur de comparaison peuvent être évaluées simultanément.Il est plus facile de comprendre ce concept à l'aide d'un exemple.Un utilisateur créé une table T_EVENEMENT dans laquelle il a quatre colonnes indiquant l'heure, la minute, la seconde de survenance de l'événement et la nature de l'événement :CREATE table T_EVENEMENT(EVT_HEURE int NOT NULL, EVT_MINUTE smallint NULL, EVT_SECONDE smallint NULL, EVT_EVENEMENT varchar(64) NOT NULL, CONSTRAINT CKC_EVT_HEURE CHECK (EVT_HEURE >= 0), CONSTRAINT CKC_EVT_MINUTE CHECK (EVT_MINUTE BETWEEN 0 AND 59), CONSTRAINT CKC_EVT_SECONDE CHECK (EVT_SECONDE BETWEEN 0 AND 59))INSERT INTO T_EVENEMENT VALUES (3, 12, 25, 'Surtension')INSERT INTO T_EVENEMENT VALUES (3, 2, 48, 'Surchauffe')INSERT INTO T_EVENEMENT VALUES (3, 15, 0, 'Arrêt')INSERT INTO T_EVENEMENT VALUES (4, 0, 0, 'Incendie')INSERT INTO T_EVENEMENT VALUES (2, 58, 59, 'Démarrage')INSERT INTO T_EVENEMENT VALUES (4, 22, 33, 'Explosion')EVT_HEURE EVT_MINUTE EVT_SECONDE EVT_EVENEMENT ----------- ---------- ----------- --------------2 58 59 Démarrage3 2 48 Surchauffe3 12 25 Surtension3 15 0 Arrêt4 0 0 Incendie4 22 33 ExplosionLa question est : quel sont les événements survenus après 3h 10 ?Exemple 33 : intuitivement, nous sommes amenés à écrire la requête suivante :SELECT *FROM T_EVENEMENTWHERE EVT_HEURE >3 AND EVT_MINUTE >10EVT_HEURE EVT_MINUTE EVT_SECONDE EVT_EVENEMENT----------- ---------- ----------- --------------4 22 33 ExplosionMais elle oublie sciement trois événements survenus avant 3h10 ! Comment est-ce possible ?En fait l'événement qui s'est produit à 4h 0 n'est pas pris en compte du fait de la première ligne de la clause WHERE !EVT_HEURE > 3 AND EVT_MINUTE > 10tout simplement parce que 0 minute est bien inférieur à 10 minutes ...Or 4h 0 est bien supérieur à 3h 10... il faut donc supprimer la partie AND EVT_MINUTE > 10 de la clause WHERELa bonne construction est donc :Exemple 34SELECT *FROM T_EVENEMENTWHERE EVT_HEURE > 3 OR EVT_HEURE = 3 AND EVT_MINUTE > 10EVT_HEURE EVT_MINUTE EVT_SECONDE EVT_EVENEMENT----------- ---------- ----------- -------------3 12 25 Surtension3 15 0 Arrêt4 0 0 Incendie4 22 33 ExplosionD'où l'idée d'implémenter une construction capable d'évaluer globalement une telle opération. Cette construction est le ROW VALUE CONSTRUCTOR que j'ai traduit par constructeur de lignes valuées...Avec un tel outil, la requête précédente s'exprime plus simplement :Exemple 35SELECT *FROM T_EVENEMENTWHERE (EVT_HEURE, EVT_MINUTE) > (3, 10)EVT_HEURE EVT_MINUTE EVT_SECONDE EVT_EVENEMENT----------- ---------- ----------- -------------3 12 25 Surtension3 15 0 Arrêt4 0 0 Incendie4 22 33 ExplosionLa syntaxe d'une telle expression utilisant un ROW VALUE CONSTRUCTOR est :( <constructeur de lignes valuées> ) <comparateur> ( <constructeur de lignes valuées> )Le constructeur de lignes valuées pouvant être : une liste de colonnes, une liste de valeurs, une liste combinant colonnes et valeurs ou encore une sous requête :(colonne1, colonne2, colonne3)(colonne1, valeur1, valeur2, colonne2)(valeur1, valeur2)SELECT * FROM Matable à condition de ne retourner qu'une seule ligneetc...L'équivalent entre cette construction et celle n'utilisant pas le ROW VALUE CONSTRUCTOR, peut être développée comme suit.Supposons que le premier constructeur de lignes valuées comporte des colonnes de tables de 1 à n et que le second constructeur de ligne valuées comporte des valeurs de 1 à n, c'est à dire :(colonne1, colonne2, ... colonneN) <comparateur> (valeur1, valeur2, ... valeurN)alors cette comparaison est équivalente à :colonne1 <comparateur> valeur1OR colonne1 = valeur1 AND colonne2 <comparateur> valeur2OR colonne1 = valeur1 AND colonne2 = valeur2 AND colonne3 <comparateur> valeur3...OR colonne1 = valeur1 AND colonne2 = valeur2 AND ... AND colonneN <comparateur> valeurNToujours basé sur notre jeu d'essais, voici un exemple plus complet :: cherchons tous les événements survenus après 3h12'30" :Exemple 36SELECT *FROM T_EVENEMENTWHERE (EVT_HEURE, EVT_MINUTE, EVT_SECONDE) > (3, 12, 30)EVT_HEURE EVT_MINUTE EVT_SECONDE EVT_EVENEMENT----------- ---------- ----------- -------------3 15 0 Arrêt4 0 0 Incendie4 22 33 ExplosionExemple 37 : et sa construction logiquement équivalente :SELECT *FROM T_EVENEMENTWHERE EVT_HEURE > 3 OR EVT_HEURE = 3 AND EVT_MINUTE > 12 OR EVT_HEURE = 3 AND EVT_MINUTE = 12 AND EVT_SECONDE > 30EVT_HEURE EVT_MINUTE EVT_SECONDE EVT_EVENEMENT----------- ---------- ----------- -------------3 15 0 Arrêt4 0 0 Incendie4 22 33 ExplosionEn fait le ROW VALUE CONSTRUCTOR agit, dans les recherches, un peu comme si l'on visualisait les données de manière fractaleLe plus grossier est traité, puis on s'interrese à quelques données plus fine une fois le grossier fixé, et ainsi de suite en fonction du niveau fractal que l'on s'est fixé.NOTA : l'utilisation du ROW VALUE CONSTRUCTOR avec un critère de comparaison d'égalité donne parfois des résultats surprenants, qui peuvent défier la logique. Il faut se méfier d'un recours systématique à une telle construction.Exemple 38 : quels sont les événement survenus à 3h 12 (en utilisant le ROW VALUE CONSTRUCTOR)SELECT *FROM T_EVENEMENTWHERE (EVT_HEURE, EVT_MINUTE, EVT_SECONDE) = (3, 12)EVT_HEURE EVT_MINUTE EVT_SECONDE EVT_EVENEMENT----------- ---------- ----------- ---------------3 12 25 Surtension3 2 48 Surchauffe3 15 0 ArrêtCe qui offre peut d'intérêt il faut bien le dire !La construction logique équivalente étant :Exemple 39SELECT *FROM T_EVENEMENTWHERE EVT_HEURE = 3 OR EVT_HEURE = 3 AND EVT_MINUTE = 12EVT_HEURE EVT_MINUTE EVT_SECONDE EVT_EVENEMENT----------- ---------- ----------- ---------------3 12 25 Surtension3 2 48 Surchauffe3 15 0 ArrêtENFIN, notez que le ROW VALUE CONSTRUCTOR possède un intérêt immense, celui de permettre l'insertion multiple (plusieurs lignes de données) au sein du même ordre INSERT !Exemple 40 : insertion multiple avec le ROW VALUE CONSTRUCTORINSERT INTO T_EVENEMENT (EVT_HEURE, EVT_MINUTE, EVT_SECONDE, EVT_EVENEMENTVALUES ((4, 37, 21, 'Catastrophe'), (4, 44, 18, 'Cataclisme') , (5, 21, 1, 'Destruction finale planète terre'))ATTENTION : seul ORACLE et quelques SGBDR plus expérimentaux que professionnels (OCELOT par exemple) utilisent une telle technique !=Résumé=Voici les différences entre les moteurs des bases de données :Fonctions \ SGBDR Paradox 9 Access 2000 Sybase Adaptive SQL Server 7 Oracle 8|| (concaténation) Oui Non (&) Non (+) Non (+) OuiCAST Oui Non Non (CONVERT) Oui Non (TO_CHAR / TO_NUM / TO_DATE)LIKE / ESCAPE / % / _ Oui/Oui/Oui/Oui Oui/Non/Non/Non [1] Oui/Oui/Oui/Oui Oui/Oui/Oui/Oui Oui/Oui/Oui/OuiTRIM Oui Non (LTRIM, RTRIM) Non (LTRIM, RTRIM) Non (LTRIM, RTRIM) Non (LTRIM, RTRIM)SUBSTRING Oui Non (MID) Non (ODBC SUBSTRING) Non (ODBC SUBSTRING) Non (SUBSTR)LOWER / UPPER Oui/Oui Non (UCASE) / Non (LCASE) Oui/Oui Oui/Oui Oui/OuiEXTRACT Oui Non Non (DATEPART) Non (DATEPART) NonCURRENT_DATE / CURRENT_TIME / CURRENT_TIMESTAMP Non/Non/Non (TODAY, NOW en QBE) Non (NOW) Non (GETDATE) Non (GETDATE() / CURRENT_TIMESTAMP) Non (SYSDATE)AVG / MAX / MIN / SUM / COUNT Oui/Oui/Oui/Oui Oui/Oui/Oui/Oui Oui/Oui/Oui/Oui Oui/Oui/Oui/Oui Oui/Oui/Oui/OuiVARIANCE / STDEV Non/Non Non/Non Non/Non Non/Non Oui/OuiABS / MOD / SIGN / SQRT / PI Non/Non/Non/Non/Non Oui /Non/ Non (SGN) / Non (SQR) / Non Oui/Non (%)/Oui/Oui/Oui Oui/Non (%)/Oui/Oui/Oui Oui/Oui/Oui/Oui/NonCEIL / FLOOR / ROUND / TRUNC Non/Non/Non/Non Non/Non (INT)/Non/Non Non/Oui/Oui/Non Non/Oui/Oui/Non Oui/Oui/Oui/OuiEXP / LN / LOG / POWER Non/Non/Non/Non Oui/Non/Oui/Non Oui/Non/Oui/Oui Oui/Non/Oui/Oui Oui/Oui/Non (LOG (M, n))/OuiCOS / SIN / TAN Non/Non/Non Oui/Oui/Oui Oui/Oui/Oui Oui/Oui/Oui Oui/Oui/OuiCOSH / SINH / TANH Non/Non/Non Non/Non/Non Non/Non/Non Non/Non/Non Oui/Oui/Oui [2]INITCAP / LPAD / RPAD / REPLACE Non/Non/Non/Non Non/Non/Non/Non Non/Non/Non/Non Non/Non/Non/Oui Oui/Oui/Oui/OuiSOUNDEX / TRANSLATE / INSTR / LENGTH Non (COMME dans QBE) / Non / Non / Non Non / Non / Non (LOCATE) / Non Oui / Non (REPLACE) / Non (PATINDEX) / Non (LEN) Oui / Non (REPLACE) / Non (PATINDEX) / Non (LEN) Oui / Oui / Non (LOCATE) / NonTO_CHAR pour numérique / pour date Non/Non Non/Non Non (CONVERT) / Non (CONVERT) Non (CONVERT) / Non (CONVERT) Non (auto [3])/ NonASCII / CHR Non/Non Non/Non Oui / Non (CHAR) Oui / Non (CHAR) Oui/OuiADD_MONTH / MONTH_BETWEEN Non/Non Non (DATEADD) / Non (DATEDIFF) Non (DATEADD) / Non (DATEDIFF) Non (DATEADD) / Non (DATEDIFF) Oui/OuiLAST_DAY / NEXT_DAY Non/Non Non/Non Non/Non Non/Non Oui/OuiGREATEST / LEAST / DECODE Non/Non/Non Non/Non/Non Non/Non/Non Non/Non/Non Oui/Oui/NonIS NULL / COALESCE / NULLIF Oui/Non/Non Oui / Non / Non (ISNULL) Oui / Non / Non (ISNULL) Oui/Oui/Oui Oui / Non / Non (NVL)ROW VALUE CONSTRUCTOR Non Non Non Non Oui