Plan du site  
français  English
pixel
pixel

Articles - Étudiants SUPINFO

Chapitre 02 - Ordres basiques

Récupération de données en utilisant l’ordre SELECT

Qu’est-ce que SELECT

Introduction

L’ordre SELECT récupère les informations de la base de données. L’ordre SELECT offre :

  • Projection : Permet de choisir les colonnes d’une la table retournées par une requête. Choisissez autant de colonnes que nécessaire.

  • Sélection : Permet de choisir les lignes d’une table retournées par une requête. Différents critères peuvent être utilisés pour restreindre les lignes retournées.

  • Jointure : Permet de combiner des données stockées dans différentes tables en spécifiant des liens entre ces tables. Les jointures SQL sont abordées plus loin dans le cursus.

Écriture des requêtes SQL

Voici la syntaxe d’une requête SELECT simple :

SELECT *|{[DISTINCT] column|expression [alias],...} 
FROM   table;

Dans sa forme la plus simple, la requête SELECT doit inclure les éléments suivants :

  • Une clause SELECT, qui spécifie les colonnes à afficher

  • Une clause FROM, qui identifie la table contenant les colonnes renseignées dans la clause SELECT

Syntaxe :

  • SELECT : est une liste de une ou plusieurs colonnes

  • * : sélectionne toutes les colonnes

  • DISTINCT : supprime les doublons

  • column|expression : sélectionne les colonnes nommées ou l’expression

  • alias : modifie l’en-tête de la colonne

  • FROM table : spécifie la table contenant les colonnes

[Note]

A travers ce cours, le mot mot-clé, clause et requête sont utilisés comme suit :

  • Un mot-clé se réfère à un élément SQL. Par exemple, SELECT et FROM sont des mots-clés.

  • Une clause est une partie de la requête SQL. Par exemple, SELECT employee id, last_name est une clause.

  • Une requête est une combinaison de deux ou plusieurs clauses. Par exemple SELECT * FROM employees est une requête SQL.

Sélection de toutes les colonnes et de tous les enregistrements

SELECT * 
FROM   departments;

Vous pouvez afficher toutes les colonnes de la table en faisant suivre le mot-clé SELECT par un astérisque (*).

Dans l’exemple, la table DEPARTMENTS contient quatre colonnes : DEPARTMENT_ID, DEPARTMENT_NAME, MANAGER_ID et LOCATION_ID. La table contient huit enregistrements, un pour chaque département.

Vous pouvez également afficher toutes les colonnes de la table en les spécifiant toutes après le mot-clé SELECT. La requête SQL suivante (comme dans l’exemple précédent) affiche toutes les colonnes et toutes les lignes de la table DEPARTMENTS :

SELECT department_id, department_name, manager_id, location_id
FROM   departments;

Sélection de colonnes spécifiques

Vous pouvez utiliser la requête SELECT pour afficher certaines colonnes de la table en spécifiant les noms des colonnes, séparées par des virgules. L’exemple suivant affiche tous les identifiants de département et les identifiants de l’emplacement de département de la table DEPARTMENTS.

Dans la clause SELECT, spécifiez les colonnes que vous voulez, dans l’ordre dans lesquels vous voulez qu’elles apparaissent. Par exemple, pour afficher les identifiants de l’emplacement de département avant les identifiants de département, vous utiliserez la requête suivante :

SELECT location_id, department_id
FROM   departments;

Règles d’écriture des requêtes SQL

En utilisant les règles suivantes, vous pouvez construire des requêtes SQL valides qui sont aussi faciles à lire qu’à modifier :

  • Les requêtes SQL ne sont pas sensibles à la casse (sauf contre indication).

  • Les requêtes SQL peuvent être écrites sur une ou plusieurs lignes.

  • Les mots-clés ne peuvent être divisés sur plusieurs lignes ou abrégés.

  • Les clauses sont généralement placées sur des lignes différentes pour améliorer lisibilité et faciliter la modification.

  • Les indentations sont conseillées pour rendre le code plus lisible.

  • En générale, les mots-clés sont écrites en majuscule ; tous les autres mots, comme le nom d’une table ou d’une colonne, doivent sont écrites en minuscule.

Exécuter une requête SQL :

  • En utilisant iSQL*Plus, cliquez sur le bouton Execute pour lancer une ou plusieurs commandes présentes dans la fenêtre d’édition.

  • En utilisant SQL*Plus, terminez la requête SQL par un point-virgule et appuyez sur Entrée pour lancer la commande.

Titres des colonnes par défauts

Dans SQL*Plus (client lourd), les titres des colonnes contenant des chaines de caractères et des dates sont alignés à gauche et les titres des colonnes contenant des nombres sont alignés à gauche. Dans les deux cas les titres des colonnes sont affichés en majuscule.

Dans iSQL*Plus (client web), les titres des colonnes sont affichés en majuscule et centrés :

SELECT last_name, hire_date, salary
FROM   employees;

Vous pouvez changer le titre de ces colonnes en utilisant un alias. Les alias seront expliqués plus loin dans ce cours.

Opérateurs arithmétiques

Vous pouvez avoir besoin de modifier la manière dont les données sont affichées, faire des calculs ou avoir les données modifiées à l’affichage avant de les appliquer sur les tables. Tout cela est possible en utilisant les expressions arithmétiques. Une expression arithmétique peut contenir les noms de colonne, les constantes numériques et les opérateurs arithmétiques.

Opérateurs arithmétiques

Le tableau suivant montre les opérateurs arithmétiques disponibles en SQL. Vous pouvez utiliser ces opérateurs dans n’importe quelle clause d’une requête SQL (excepté la clause FROM).

Opérateur Description
+ Ajoute
- Soustrait
* Multiplie
/ Divise
[Note]

Avec les types de données DATE et TIMESTAMP, vous ne pouvez utiliser que l’opérateur d’addition et de soustraction.

Utilisation des opérateurs arithmétiques

L’exemple suivant utilise l’opérateur d’addition pour calculer une hausse du salaire de $300 pour tous les employés. La colonne SALARY+300 est affichée.

SELECT last_name, salary, salary + 300
FROM   employees;

Sachez que la colonne SALARY+300 n’est pas une nouvelle colonne de la table EMPLOYEES, ce n’est que pour l’affichage. Par défaut, le nom de la nouvelle colonne vient du calcul qui l’a généré, dans cet exemple SALARY+300.

[Note]

Le serveur Oracle ignore les espaces avant et après un opérateur arithmétique.

Priorité des opérateurs

Si une expression arithmétique contient plus d’un opérateur, la multiplication et la division sont évaluées en premier. Si les opérateurs ont la même priorité dans une expression, l’évaluation se fait de gauche à droite.

Vous pouvez utiliser des parenthèses pour forcer une expression à être évaluée en premier.

Règles de priorité :

  • La multiplication et la division sont prioritaires à l’addition et à la soustraction.

  • Les opérateurs qui ont la même priorité sont évalués de gauche à droite.

  • Les parenthèses sont utilisées pour modifier la priorité des opérateurs ou pour améliorer la lisibilité.

L’exemple ci-dessous affiche le nom, le salaire et la compensation annuelle des employées. On calcule la compensation annuelle en multipliant le salaire par 12, puis on y ajoute un bonus de $100. Remarquez que la multiplication est effectuée avant l’addition.

SELECT last_name, salary, 12*salary+100
FROM   employees;
[Note]

Utilisez des parenthèses sans modifier l’ordre des priorités pour améliorer la lisibilité.

Par exemple, l’expression de l’exemple précédent aurait pu être écrite de la façon suivante : (12*salary)+100

Utilisation des parenthèses

Vous pouvez modifier les règles de priorité en utilisant des parenthèses pour spécifier l’ordre que vous désirez.

L’exemple suivant affiche le nom, le salaire et la compensation annuelle des employées. On calcule la compensation annuelle en ajoutant le bonus mensuel de $100 au salaire mensuel et en multipliant le total par 12. Grâce aux parenthèses, l’addition est effectuée avant la multiplication.

SELECT last_name, salary, 12*(salary+100)
FROM   employees;

Définir une valeur NULL

Si une valeur est absente dans une colonne d’une ligne, cette valeur est dite nulle ou contient NULL.

Une valeur NULL est une valeur indisponible, non signée, inconnue ou inapplicable. Une valeur NULL n’est pas un zéro ou un espace. Zéro est un nombre et espace est un caractère.

Les colonnes de n’importe quel type de données peuvent contenir une valeur NULL. Cependant, quelques contraintes comme NOT NULL ou PRIMARY KEY interdisent l'utilisation des valeurs NULL dans la colonne.

Remarquez que dans la colonne COMMISSION_PCT de la table EMPLOYEES seuls les chefs des commerciaux (SA_MAN ou sales manager) et les commerciaux (SA_REP ou sales representative) peuvent percevoir une commission. Les autres employés n’en perçoivent pas, une valeur NULL représente cela.

SELECT last_name, job_id, salary, commission_pct
FROM   employees;

Valeurs NULL dans des expressions arithmétiques

Si une valeur d’une colonne dans une expression arithmétique est à NULL, le résultat est NULL. Par exemple, si vous tentez de faire une division par zéro, vous obtenez une erreur. Cependant si vous divisez un nombre par NULL, le résultat sera NULL ou inconnu.

Dans l’exemple ci-dessous, l’employé King n’a pas de commission. La colonne COMMISSION_PCT est NULL dans l’expression arithmétique, pour cette raison, le résultat est NULL.

SELECT last_name, 12*salary*commission_pct
FROM   employees;

Autres possibilités de SELECT

Alias de colonne

Lors de l’affichage du résultat d’une requête, iSQL*Plus utilise généralement le nom de la colonne sélectionnée comme en-tête de colonne. L’en-tête généré peut ne pas être explicite et être difficile à comprendre. Vous pouvez changer l’en-tête en utilisant un alias de colonne.

Spécifiez l’alias après la colonne dans la liste SELECT utilisant un espace comme séparateur. Par défaut, les alias apparaissent en majuscule. Si l’alias contient des espaces ou des caractères spéciaux (comme # ou $) ou si vous voulez que la casse soit respectée, placez le entre guillemets (" ").

L’exemple suivant affiche le nom et la commission pour tous les employés.

Remarquez que le mot-clé optionnel AS a été utilisé avant le nom de l’alias de colonne. Le résultat de la requête est le même avec ou sans mot clé AS. Remarquez également que les alias de la requête SQL name et comm sont en minuscule, alors que le résultat de la requête affiche les en-têtes de colonne en majuscule. Comme expliqué précédemment, les en-têtes de colonne apparaissent par défaut en majuscule.

SELECT last_name AS name, commission_pct comm
FROM   employees;

Le l’exemple ci-dessous affiche le nom et le salaire annuel pour tous les employés. L’alias Annual Salary contient un espace, pour cette raison, il doit être obligatoirement entouré de guillemets. Remarquez qu’à l’affichage l’en-tête de colonne est exactement le même que dans la requête.

SELECT last_name "Name", salary*12 "Annual Salary"
FROM   employees;

Opérateurs de concaténation

Vous pouvez lier le contenu des colonnes au contenu d’autres colonnes, aux expressions arithmétiques ou aux valeurs constantes pour créer une chaîne de caractères en utilisant l’opérateur de concaténation (||). Les colonnes autour de l’opérateur sont combinées pour être affichées en une seule colonne.

SELECT last_name||job_id AS "Employees"
FROM   employees;

Dans l’exemple, LAST_NAME et JOB_ID sont concaténés et on leur donne l’alias Employees.

Remarquez que le nom d’employé et l’identifiant de poste sont combinés ne font qu’une seule colonne.

Le mot-clé AS avant l’alias améliore la lisibilité de la clause SELECT.

Valeurs NULL avec l’opérateur de concaténation

Si vous concaténez une valeur NULL avec une chaîne de caractère, le résultat est une chaîne de caractères.

LAST_NAME || NULL donne LAST_NAME.

Chaînes de caractères littérales

Un littéral est un caractère, un nombre ou une date inclus dans la liste de la clause SELECT. Un littéral n’est pas un nom de colonne ou un alias. Il est affiché à chaque ligne retournée. Les chaînes littérales, avec un texte de format libre, peuvent être incluses dans le résultat de la requête et sont considérées comme des colonnes dans la liste de la clause SELECT.

Les dates et les chaînes littérales doivent être entourées d’apostrophes ('') ; les nombres ne doivent pas être entourés d’apostrophes.

L’exemple suivant affiche le nom d’employé et l’identifiants de poste pour tous les employés. L’en-tête de la colonne est Employee Details. Remarquez les espaces entre les apostrophes dans la clause SELECT. Les espaces améliorent la lisibilité du résultat.

SELECT last_name ||' is a '||job_id AS "Employee Details"
FROM   employees;

Dans l’exemple suivant, le nom d’employé et le salaire de chaque employé sont concaténés avec une chaîne de caractère littérale pour donner plus de sens au résultat :

SELECT last_name ||': 1 Month salary = '||salary Monthly
FROM   employees;

L’opérateur q

Beaucoup de requête SQL utilisent des chaînes de caractères littérales dans les expressions ou les conditions. Si la chaîne de caractères littérale elle-même contient des apostrophes, vous pouvez utiliser l’opérateur q et choisir vous-même le délimiteur.

Vous pouvez choisir le délimiteur que vous souhaitez, un caractère (encodé sur un ou plusieurs octets) ou n’importe laquelle des paires suivantes : [ ], { }, ( ), ou < >.

Dans l’exemple, la chaîne contient une apostrophe, qui est normalement interprétés comme un délimiteur de la chaîne de caractères. Grace à l’opérateur q les crochets sont utilisés comme délimiteurs. La chaîne entre crochets est interprétée comme une chaîne de caractère littérale.

SELECT department_name ||
       q'[, it's assigned Manager Id: ]'
       || manager_id AS "Department and Manager"
FROM   departments;

Lignes dupliquées

Sauf si vous le précisez, iSQL*Plus affiche le résultat d’une requête sans éliminer les doublons.

L’exemple suivant affiche tous les identifiants de département de la table EMPLOYEES. Remarquez qu’ils sont répétés.

SELECT department_id
FROM   employees;

Pour éliminer les lignes dupliquées dans le résultat, mettez le mot clé DISTINCT dans la clause SELECT immédiatement après le mot-clé SELECT.

Dans l’exemple ci-dessous, la table EMPLOYEES contient 20 enregistrements, mais seulement huit identifiants de département uniques sont affichés.

SELECT DISTINCT department_id
FROM   employees;

Vous pouvez spécifier plusieurs colonnes après DISTINCT. Le mot-clé DISTINCT agit sur toutes les colonnes sélectionnées et le résultat donne les combinaisons uniques des colonnes.

SELECT DISTINCT department_id, job_id
FROM   employees;

Utilisation de iSQL*Plus

SQL et iSQL*Plus

SQL est un langage permettant de communiquer avec le serveur Oracle de n’importe quel outil ou application. Le SQL d’Oracle contient beaucoup d’extensions.

iSQL*Plus est un utilitaire Oracle qui reconnaît et envoie les requêtes SQL au serveur Oracle pour exécution. Il possède ses propres commandes.

Fonctionnalités de SQL

  • Peut être utilisé par un grand nombre d’utilisateurs, les utilisateurs avec peu ou sans expérience de programmation y compris.

  • Est un langage non-procédural.

  • Est un langage fondé sur l’anglais.

Fonctionnalités d’iSQL*Plus

  • Accessible via un navigateur Web.

  • Accepte les requêtes SQL.

  • Fournit une édition en ligne des requêtes SQL.

  • Contrôle les paramètres d’environnement.

  • Formate le résultat des requêtes dans un rapport basique.

  • Permets d’accéder aux les bases de données locales et distantes.

SQL iSQL*Plus
Est un langage permettant de communiquer avec le serveur Oracle et d’accéder aux données Reconnaît les requêtes SQL et les envoie au serveur
Est basé sur le standard SQL American National Standards Institute (ANSI) Est l’interface propriétaire d’Oracle pour exécuter des requêtes SQL
Récupère les données ; manipule des données et des définitions des tables dans la base de données Ne permet pas la manipulation de valeurs dans la base de données
N’a pas de caractère de continuation A un trait (-) utilisé comme un caractère de continuation si la commande est plus longue qu’une ligne
Ne peut pas être abrégé Peut être abrégé
Utilise des fonctions pour réaliser des mises en forme Utilise des commandes pour mettre en forme les données

iSQL*Plus est un environnement qui permet :

  • d'exécuter des requêtes SQL pour récupérer, modifier, ajouter et supprimer des données de la base de données.

  • de formater, exécuter des calculs, stocker et afficher le résultat de requête sous une forme de rapport.

  • de créer des fichiers script pour stocker les requêtes SQL afin de les réutiliser.

Les commandes iSQL*Plus peuvent être divisées en plusieurs catégories :

Catégorie Description
Environnement Affecte le comportement général des requêtes SQL pour la session
Format Formate le résultat des requêtes
Manipulation de fichier Sauvegarde les requêtes dans un fichier script plat et lance des requêtes à partir d’un fichier script plat
Exécution Envoie des requêtes SQL depuis le navigateur Web au serveur Oracle
Edition Modifie les requêtes SQL dans une fenêtre d’édition
Interaction Vous permet de créer et de passer des variables aux requêtes SQL, d’afficher les valeurs et d’afficher des messages à l’écran
Divers Plusieurs commandes permettent de se connecter à la base de données, de manipuler l’environnement iSQL*Plus et d’afficher les définitions de colonnes

Se connecter à iSQL*Plus

Pour vous connecter à partir d’un navigateur Web :

  1. Démarrez le navigateur.

  2. Entrez l’adresse de l’environnement iSQL*Plus.

  3. Sur la page de connexion, entrez les bonnes valeurs dans le champ Username, Password et Connect Identifier.

L’environnement iSQL*Plus

Dans le navigateur, l’espace de travail d’iSQL*Plus a plusieurs zones clés :

  1. Champ de texte : Zone dans laquelle vous saisissez les requêtes SQL et les commandes iSQL*Plus

  2. Bouton Execute : Cliquez pour exécuter les requêtes et les commandes dans le champ de texte

  3. Bouton Load Script : Affiche un formulaire permettant d’indiquer le chemin complet ou un URL jusqu’un fichier contenant des scripts SQL, du code PL/SQL ou des commandes iSQL*Plus et les charger dans le champ de texte

  4. Bouton Save Script : Sauvegarde le contenu du champ de texte dans un fichier

  5. Bouton Cancel : Interrompt l’exécution de la commande dans le champ de texte

  6. Bouton Clear : Efface le texte dans le champ de texte

  7. Icône Logout : Cliquez pour terminer la session iSQL*Plus proprement et retourner à la page de connexion

  8. Icône Preferences : Cliquez pour changer la configuration de l’interface, du système ou pour changer votre mot de passe

  9. Icône Help : Fournit un accès à la documentation d’iSQL*Plus

Affichage de la structure des tables

Dans iSQL*Plus, vous pouvez afficher la structure d’une table en utilisant la commande DESCRIBE.

DESC[RIBE] tablename

La commande affiche les noms de colonne, les types de données et si une colonne doit contenir des données (dans le cas ou la colonne a une contrainte NOT NULL).

Dans la syntaxe, tablename est le nom de n’importe quelle table, vue ou synonyme accessible à l’utilisateur.

DESCRIBE employees

L’exemple précédant affiche les informations sur la structure de la table EMPLOYEES.

Dans le résultat, Null? indique que la colonne peut contenir des valeurs inconnues (NULL) ; NOT NULL indique que la colonne doit contenir des données. Type affiche le type de données de la colonne.

Les types de données sont décrits dans le tableau suivant :

Type de données Description
NUMBER(p,s) Une valeur nombre ayant le nombre maximal de chiffres p et de s chiffres après le point de séparation des décimales
VARCHAR2(s) Une valeur de caractères de longueur variable dont la taille est limitée par s
DATE Une valeur de date et d’heure entre 1er janvier 4712 av. J.-C. et le 31 décembre 9999 apr. J.-C.
CHAR(s) Une valeur de caractères de longueur fixe, dont la taille est limitée par s

Interaction avec des fichiers script

Placer des requêtes et des commandes dans un fichier script

Sous iSQL*Plus vous pouvez sauvegarder les commandes et les requêtes du champ de texte dans un fichier script de la façon suivante :

  1. Tapez la requête SQL dans le champ de texte sous iSQL*Plus.

  2. Cliquez sur le bouton Save Script. Cela ouvre une fenêtre de dialogue. Choisissez un nom pour le fichier. L’extension par défaut est .uix, vous pouvez choisir le type de fichier .txt ou.sql à la place.

Dans l’exemple suivant, la requête SQL SELECT est saisie dans le champ de texte et est sauvegardée dans un fichier nommé emp_data.sql. Vous pouvez choisir le type de fichier, le nom du fichier et l’endroit où le fichier sera stocké.

Utiliser des requêtes et des commandes provenant d’un fichier script dans iSQL*Plus

Vous pouvez utiliser les commandes est les requêtes précédemment sauvegardées dans un fichier script de la façon suivante :

  1. Cliquez sur le bouton Load Script. Cela ouvre une page vous permettant d’indiquer le chemin complet ou un URL jusqu’un fichier contenant des scripts SQL, du code PL/SQL ou des commandes iSQL*Plus et les charger dans le champ de texte

  2. Entrez le chemin complet ou un URL jusqu’un fichier. Vous pouvez aussi cliquer sur le bouton d’exploration pour trouver le script.

  3. Cliquez sur Load pour faire apparaître le contenu du fichier script dans le champ de texte.

Exécution des requêtes précédemment lancées

La page History de iSQL*Plus vous permet d’exécuter des requêtes précédemment lancée sous votre session. Cette page montre les requêtes SQL et commandes iSQL*Plus les plus récentes.

Pour relancer des requêtes :

  1. Sélectionnez la requête que vous voulez exécuter.

  2. Cliquez sur le bouton Load.

    [Note]
    • Vous pouvez contrôler le nombre de requêtes affichées dans la page d’historique dans Preferences.

    • Vous pouvez choisir d’effacer les requêtes sélectionnées en cliquant sur le bouton Delete.

  3. Revenez à la page de l’espace de travail (Workspace).

  4. Cliquez sur Execute pour lancer les commandes qui ont été chargées dans le champ de texte.

Préférences iSQL*Plus

  • Vous pouvez définir des préférences pour votre session iSQL*Plus en cliquant sur l’icône Preferences.

  • Les préférences sont divisées en catégories. Vous pouvez définir des préférences pour le formatage des scripts, l’exécution des scripts, l’administration de la base de données et vous pouvez changer votre mot de passe.

  • Quand vous choisissez une catégorie, un formulaire permettant de définir les préférences est affiché.

Changer la sortie par défaut

Vous pouvez envoyer le résultat généré par une requête SQL ou une commande iSQL*Plus à l’écran (par défaut), dans un fichier ou une autre fenêtre de votre navigateur.

Dans la page Preferences :

  1. Sélectionnez une option Output Location.

  2. Cliquez sur le bouton Apply.

Restriction et tri de données

Comment restreindre les données

Vue d’ensemble

Lorsque vous récupérez des données à partir de la base de données, vous pouvez avoir besoin de :

  • Restreindre les lignes des données à afficher

  • Spécifier l'ordre dans lequel les lignes sont affichées

Ce chapitre explique les requêtes SQL qui permettent d’effectuer ces opérations.

Restreindre le nombre de lignes retournées

Supposons que vous souhaitez afficher tous les employés du département 90. Seules les lignes contenants la valeur 90 dans la colonne DEPARTMENT_ID sont affichées. Cette méthode de restriction est basée sur la clause WHERE en SQL.

Vous pouvez restreindre le nombre de lignes renvoyées par une requête en utilisant la clause WHERE. La clause WHERE contient une condition qui doit être validée et suit la clause FROM. Si la condition est évaluée à vrai (true), la ligne correspondante à la condition est retournée.

SELECT *|{[DISTINCT] column|expression [alias],...}
FROM   table
[WHERE condition(s)];

Explication de la syntaxe :

  • WHERE restreint la requête aux lignes correspondantes à la condition.

  • condition est composée de noms de colonne, d’expressions, de constantes et d’un opérateur de comparaison.

La clause WHERE peut être utilisée pour comparer les valeurs de colonne, de valeurs littérales, d’expressions arithmétiques ou de fonctions. Elle est constituée de trois parties :

  • Nom de colonne.

  • Condition de comparaison.

  • Nom de colonne, constante ou liste de valeurs.

Utilisation de la clause WHERE

Dans cet exemple, la requête SELECT récupère l’identifiant de l’employé, le nom, l’identifiant du poste et le numéro de département de tous les employés travaillant pour le département 90.

Chaînes de caractères et dates

Les chaînes de caractères et les dates dans la clause WHERE doivent être entourées par les apostrophes (''). Les nombres constants, quant à eux, ne doivent pas être entourés par les apostrophes.

Toutes les recherches basées sur les caractères sont sensibles à la casse. Dans l’exemple suivant, aucune ligne n’est retournée car les noms, dans la table EMPLOYEES, sont stockés avec une première lettre en majuscules et toutes les autres lettres en minuscule :

SELECT last_name, job_id, department_id
FROM   employees
WHERE  last_name = 'WHALEN';

Les bases de données Oracle stockent les dates dans un format numérique interne, représentant le siècle, l’année, le mois, le jour, les heures, les minutes et les secondes. L’affichage par défaut des dates est DD-MON-RR.

[Note]

Les détails sur le format RR et les fonctions de manipulation de dates sont traités dans le chapitre suivant.

Conditions de comparaison

Opérateur Signification
= Egal à
> Supérieur à
>= Supérieur ou égal à
< Inférieur à
<= Inférieur ou égal à
BETWEEN … AND … Entre deux valeurs (incluses)
IN(set) Correspond à une des valeurs de la liste (set)
LIKE Correspond à un motif de caractères
IS NULL Est une valeur NULL

Les conditions de comparaisons sont utilisées dans des conditions qui comparent une expression avec une autre valeur ou une autre expression. Elles sont utilisées dans la clause WHERE au format suivant :

Syntaxe

... WHERE expr operator value

Exemples

... WHERE hire_date = '01-JAN-95'
... WHERE salary >= 6000
... WHERE last_name = 'Smith'

Un alias ne peut pas être utilisé dans une clause WHERE.

[Note]

Les symboles != et ^= peuvent être aussi utilisés dans une condition « différent de ».

Utilisation des opérateurs de comparaison

Dans cet exemple, la requête SELECT récupère le nom et salaire de la table EMPLOYEES pour tous les employés ayant un salaire inférieur ou égal à $3000. Remarquez qu’une valeur est indiquée explicitement dans la clause WHERE. La valeur explicite 3000 est comparée à la valeur de la colonne SALARY de la table EMPLOYEES.

SELECT last_name, salary
FROM   employees
WHERE  salary <= 3000;

Utilisation de la condition BETWEEN

Vous pouvez affichez les lignes basées sur une plage de valeurs en utilisant la condition BETWEEN. La plage que vous spécifiez contient une limite inférieure et une limite supérieure.

SELECT last_name, salary
FROM   employees
WHERE  salary BETWEEN 2500 AND 3500;

L’ordre SELECT retourne les lignes de la table EMPLOYEES pour tous les employés ayant un salaire compris entre $2500 et $3000.

Les valeurs que vous spécifiez dans la clause BETWEEN incluses dans la plage. Vous devez spécifier la limite inférieure en premier.

Vous pouvez aussi utiliser les conditions BETWEEN avec des caractères :

SELECT last_name
FROM   employees
WHERE  last_name BETWEEN 'King' AND 'Smith';

Utilisation de la condition IN

Vous pouvez utiliser la condition IN pour vérifier si une valeur est présente dans l’ensemble spécifié. La condition IN est aussi appelée membership condition.

L’exemple suivant affiche l’identifiant de l’employé, le nom, le salaire et l’identifiant du manager de tous les employés ayant le manager dont l’identifiant (MANAGER_ID) est 100, 101 ou 201.

SELECT employee_id, last_name, salary, manager_id
FROM   employees
WHERE  manager_id IN (100, 101, 201);

La condition IN peut être utilisée avec n’importe quel type de données.

L’exemple suivant retourne une ligne de la table EMPLOYEES pour chaque employé dont le nom est inclus dans la liste de noms spécifiée dans la clause WHERE ('Harstein', 'Vargas') :

SELECT employee_id, manager_id, department_id
FROM   employees
WHERE  last_name IN ('Hartstein', 'Vargas');

Si des caractères ou des dates sont utilisées dans la liste, vous devez les entourer d’apostrophes ('').

Utilisation de la condition LIKE

Vous risquez de ne pas toujours savoir la valeur exacte que vous recherchez. Vous pouvez sélectionner les lignes correspondant à une pseudo-expression régulière en utilisant la condition LIKE. La recherche utilisant des pseudo-expressions régulières est appelée une recherche wildcard. Deux caractères peuvent être utilisés comme caractère de recherche.

Symbole Description
% Représente n’importe quelle séquence de zéro ou plusieurs caractères
_ Représente un caractère

Exemple

SELECT first_name
FROM   employees
WHERE  first_name LIKE 'S%';

Cette requête renvoie le prénom de l’employé de la table EMPLOYEES pour chaque employé dont la première lettre est S. Notez que la recherche est sensible à la casse. Les noms commençant par un s ne seront pas retournés.

La condition LIKE peut être utilisée comme un raccourci pour les comparaisons BETWEEN.

L’exemple suivant affiche les noms et dates d’embauche de tous les employés ayant été embauchés entre janvier 1995 et décembre 1995 :

SELECT last_name, hire_date
FROM   employees
WHERE  hire_date LIKE '%95';

Combinaison des caractères Wildcard :

Les caractères '%' et '_' peuvent être utilisés en combinaison avec des caractères alphanumériques.

L’exemple ci-dessous retourne les noms de tous les employés commençant par n’importe quel caractère, dont la seconde lettre est un o suivie de n’importe quels caractères.

Exemple

SELECT last_name
FROM   employees
WHERE  last_name LIKE '_o%';

L’option ESCAPE :

Lorsque vous avez besoin d’effectuer une recherché dont les caractères ciblés sont % et _, vous devez utiliser l’option ESCAPE. Cette option permet de définir un caractère d’échappement. Si vous souhaitez rechercher les chaînes qui contiennent SA_, vous devez utiliser la requête suivante :

SELECT employee_id, last_name, job_id
FROM   employees
WHERE  job_id LIKE '%SA\_%' ESCAPE '\';

Ici, l’option ESCAPE identifie l’antislash (\) comme caractère d’échappement. Dans la recherche, l’antislash précède le tiret-bas (_), ce qui force le serveur Oracle à interpréter le tiret-bas de façon littérale.

Utilisation des conditions IS NULL

Les conditions NULL incluent les conditions IS NULL et IS NOT NULL.

La condition IS NULL vérifie les valeurs NULL. Une valeur NULL signifie que la valeur est indisponible, non-assignée, inconnue ou inapplicable. Vous ne pouvez donc pas la tester à l’aide de l’opérateur « égale à » (=) parce qu’une valeur NULL ne peut être égale ou différente d’une autre valeur.

L’exemple suivant affiche le nom et l’identifiant de manager pour les employés n’ayant pas de manager.

SELECT last_name, manager_id
FROM   employees
WHERE  manager_id IS NULL ;

Voici un autre exemple : Utilisez la requête SQL suivante pour afficher le nom, l’identifiant du poste et la commission de tous les employés qui ne perçoivent pas de commission :

SELECT last_name, job_id, commission_pct
FROM   employees
WHERE  commission_pct IS NULL;

Les conditions logiques

Définition

Une condition logique combine le résultat de deux conditions pour produire un seul résultat basé sur ces conditions ou inverse le résultat d’une seule condition. Une ligne est retournée uniquement si le résultat global de la condition est vrai.

Trois opérateurs logiques sont disponibles en SQL :

Opérateur Signification
AND Retourne TRUE si chacune des conditions est validée
OR Retourne TRUE si une des deux conditions est validée
NOT Retourne TRUE si la condition suivante n’est pas validée

Tous les exemples précédents n’avaient qu’une condition dans la clause WHERE. Vous pouvez utilisez plusieurs conditions dans la clause WHERE en utilisant les opérateurs AND et OR.

Utilisation de l’opérateur AND

Dans cet exemple, les deux conditions doivent être évaluées à TRUE pour que la requête retourne une ligne. Ainsi, seuls les employés qui ont un poste contenant la chaîne de caractères 'MAN' et qui gagnent $10000 ou plus sont affichés.

SELECT employee_id, last_name, job_id, salary
FROM   employees
WHERE  salary >=10000
AND    job_id LIKE '%MAN%';

Toutes les recherches basées sur des caractères sont sensibles à la casse. Aucune ligne ne sera retourné si la chaîne 'MAN' n’est pas en majuscules.

Les chaînes de caractères doivent être entourées d’apostrophes.

Table de vérité de l’opérateur AND

La table suivante montre les résultats de la combinaison de deux expressions avec AND :

Utilisation de l’opérateur OR

Dans cet exemple, n’importe condition doit être évaluée à TRUE pour qu’une ligne soit retournée. Ainsi, tous les employés dont l’identifiant de poste contient la chaîne de caractères 'MAN' ou qui gagnent $10000 ou plus sont affichés.

SELECT employee_id, last_name, job_id, salary
FROM   employees
WHERE  salary >= 10000
OR     job_id LIKE '%MAN%';

Table de vérité de l’opérateur OR

La table suivante montre les résultats de la combinaison de deux expressions avec OR :

Utilisation de l’opérateur NOT

Cet exemple affiche le nom et l’identifiant du poste de chaque employé dont l’identifiant du poste n’est pas IT_PROG, ST_CLERK ou SA_REP.

SELECT last_name, job_id
FROM   employees
WHERE  job_id NOT IN ('IT_PROG', 'ST_CLERK', 'SA_REP');

Table de vérité de l’opérateur NOT

La table suivante montre les résultats de l’application de l’opérateur NOT à une condition :

[Note]

L’opérateur NOT peut être également utilisé avec d’autres opérateurs SQL tels que BETWEEN, LIKE et NULL.

... WHERE job_id NOT IN ('AC_ACCOUNT', 'AD_VP')
... WHERE salary NOT BETWEEN 10000 AND 15000
... WHERE last_name NOT LIKE '%A%'
... WHERE commission_pct IS NOT NULL

Règles de priorité

Les règles de priorité déterminent l’ordre d’évaluation et de calcul des expressions. La table suivante montre les ordres de priorité par défaut. Vous pouvez outrepasser l’ordre de priorité par défaut en utilisant des parenthèses autour des expressions que vous souhaitez calculer en premier.

Exemple de priorité de l’opérateur AND

SELECT last_name, job_id, salary
FROM   employees
WHERE  job_id = 'SA_REP'
OR     job_id = 'AD_PRES'
AND    salary > 15000;

Dans cet exemple, il y a deux conditions :

  • L’identifiant du poste doit être AD_PRES et le salaire supérieur à $15000.

  • L’identifiant du poste doit être SA_REP.

De ce fait, la requête SELECT est lue ainsi :

"Sélectionner la ligne si l’employé est un commercial (SA_REP) OU si l’employé est président (AD_PRES) ET, à la fois, gagne plus de $15000."

Exemple d’utilisation des parenthèses

SELECT last_name, job_id, salary
FROM   employees
WHERE  (job_id = 'SA_REP'
OR     job_id = 'AD_PRES')
AND    salary > 15000;

Dans cet exemple, il y a deux conditions :

  • L’identifiant du poste doit être 'AD_PRES' ou 'SA_REP'.

  • Le salaire doit être supérieur à $15000.

De ce fait, la requête SELECT est lue ainsi :

"Sélectionner la ligne si l’employé est président (AD_PRES) OU commercial (AD_REP), ET, dans les deux cas, si l’employé gagne plus de $15000."

Comment trier les données

Utilisation de la clause ORDER BY

L’ordre des lignes retournées par une requête est par défaut indéfini. La clause ORDER BY peut être utilisée pour trier les lignes. Si vous utilisez cette clause, elle doit être ajoutée à la fin de la requête SQL.

La condition de tri peut être :

  • une expression

  • un alias

  • une position de colonne

Syntaxe :

SELECT    expr
FROM      table
[WHERE    condition(s)]
[ORDER BY {column, expr, numeric_position} [ASC|DESC]];

Explication de la syntaxe :

  • ORDER BY : spécifie l’ordre dans lequel les lignes sélectionnées doivent être affichées.

  • ASC : trie les lignes dans l’ordre croissant (valeur par défaut).

  • DESC : trie les lignes dans l’ordre décroissant.

Si la clause ORDER BY n’est pas utilisée, l’ordre de tri est indéfini, le serveur Oracle risque de ne pas renvoyer les lignes dans le même ordre pour la même requête exécutée une deuxième fois.

Tri par défaut des données

L’ordre de tri par défaut est l’ordre croissant :

  • Les valeurs numériques sont affichées de la plus petite à la plus grande (par exemple de 1 à 999).

  • Les dates sont affichées de la plus ancienne à la plus récente (par exemple 01-JAN-92 est affiché avant 01-JAN-95).

  • Les caractères sont affichés dans l’ordre alphabétique (par exemple de A à Z puis de a à z).

  • Les valeurs NULL sont affichées en dernier pour l’ordre croissant et en premier pour l’ordre décroissant.

  • Vous pouvez trier l’affichage en fonction d’une colonne qui n’est pas dans la liste SELECT.

Exemples d’utilisation de la clause ORDER BY

  1. Pour inverser l’ordre d’affichage des lignes, spécifiez le mot-clé DESC après le nom de la colonne dans la clause ORDER BY. Cet exemple trie les données sur les employés à partir de celui qui a été embauché le plus récemment.

    SELECT   last_name, job_id, department_id, hire_date
    FROM     employees
    ORDER BY hire_date DESC;
  2. Vous pouvez utilisez un alias de colonne dans la clause ORDER BY. Cet exemple trie les données en fonction des salaires annuels.

    SELECT   employee_id, last_name, salary*12 annsal
    FROM     employees
    ORDER BY annsal;
  3. Vous pouvez trier le résultât de requêtes grâce à plus d’une colonne. La limite pour le nombre de colonne dans la clause ORDER BY est le nombre de colonne dans la table ciblée. Dans la clause ORDER BY, spécifiez les colonnes et séparez le nom des colonnes en utilisant des virgules. Si vous souhaitez inverser l’ordre de tri pour une colonne, spécifiez DESC après le nom de cette colonne.

    SELECT   last_name, department_id, salary
    FROM     employees
    ORDER BY department_id, salary DESC;

Les variables de substitution

Définition

Dans les exemples, jusqu’ici, toutes les valeurs définies dans les requêtes étaient constantes (hard-coded). Dans une application terminée, l’utilisateur obtient un rapport sans que l’application ne lui demande d’informations supplémentaires. La plage de données est prédéfinie dans la clause WHERE d’un fichier script iSQL*Plus.

En utilisant iSQL*Plus, vous pouvez créer des rapports qui demandent à l’utilisateur de fournir ses propres valeurs pour restreindre la quantité de données retournée. Pour faire cela vous devez utilisez les variables de substitution. Vous pouvez utilisez ces variables de substitution au sein de scripts ou dans des requêtes SQL seules. Une variable peut être comparée à un container dans lequel des valeurs sont stockées temporairement. Lorsque vous lancez la requête, la valeur est alors substituée.

Dans iSQL*Plus, vous pouvez utiliser des variables de substitution définies par un « et commercial » (&) pour stocker temporairement des valeurs.

Vous pouvez utiliser des variables prédéfinies dans iSQL*Plus en utilisant la commande DEFINE. Cette commande crée une variable et lui assigne une valeur.

Exemple de plages restreintes de données

  • Fournir les données concernant le trimestre en cours ou dans un laps de temps spécifié.

  • Fournir seulement les données demandées par l’utilisateur.

  • Afficher le personnel travaillant dans un département donné.

Autres interactions

Les interactions ne se limitent pas à l’interaction avec l’utilisateur dans la clause WHERE. Le même principe peut être appliqué dans d’autres buts :

  • Obtenir les valeurs d’entrée depuis un fichier plutôt que d’une personne.

  • Passer des valeurs d'un ordre SQL à un autre.

iSQL*Plus ne vérifie pas les entrées des utilisateurs (hormis le type de donnée).

Utilisation de simple-esperluettes pour les variables de substitution

En exécutant un rapport, les utilisateurs souhaitent souvent restreindre les données de façon dynamique. iSQL*Plus fournit cette possibilité grâce aux variables utilisateur. Utilisez une « esperluette » (&) pour identifier chaque variable dans votre requête SQL. Vous n’avez pas besoin de définir de valeurs pour chaque variable.

  • &user_variable : Indique une variable dans une requête SQL; si la variable n’existe pas, iSQL*Plus demande à l’utilisateur de spécifier une valeur (iSQL*Plus supprime la valeur spécifiée une fois utilisée).

L’exemple suivant crée une variable de substitution iSQL*Plus pour un identifiant d’employé. Lors de l’exécution de cette requête, iSQL*Plus demandera à l’utilisateur un identifiant d’employé et affichera alors l’identifiant, le nom, le salaire et le numéro de département de cet employé.

SELECT employee_id, last_name, salary, department_id
FROM   employees
WHERE  employee_id = &employee_num;

Avec une esperluette (&), iSQL*Plus demandera à l’utilisateur une valeur à chaque exécution de la requête si la variable n’est pas définie (DEFINE).

Lorsque iSQL*Plus détecte la présence d’une esperluette dans une requête SQL, vous êtes invités à entrer une valeur pour la variable de substitution.

Après que vous ayez entré une valeur et cliqué sur le bouton Continue, le résultat sera affiché dans la zone de résultat de vote session iSQL*Plus.

Utilisation des variables de substitution avec les caractères et les dates

Dans une clause WHERE, les dates et les caractères doivent être entourées par des apostrophes. La même règle s’applique pour les variables de substitution.

Entourez la variable de substitution par des apostrophes au sein de la requête SQL.

Cet exemple montre une requête qui renvoie le nom de l’employé, le numéro de département et le salaire annuel pour chaque employé dont l’identifiant du poste correspond à la valeur de la variable de substitution iSQL*Plus.

SELECT last_name, department_id, salary*12 
FROM   employees
WHERE  job_id = '&job_title';

Utilisation de noms de colonne, d’expressions et de texte

Vous pouvez non seulement utiliser des variables de substitution dans la clause WHERE d’une requête SQL mais aussi pour remplacer des noms de colonne, des expressions ou du texte.

Exemple :

Cet exemple affiche l’identifiant de l’employé, le nom de l’employé, l’identifiant du poste et toute autre colonne spécifiée par l’utilisateur à l’exécution de la requête. Ces informations sont récupérées à partir de la table EMPLOYEES. Pour chaque variable de substitution dans l’ordre SELECT, vous êtes invités à entrer une valeur puis à cliquer sur le bouton Continue.

SELECT   employee_id, last_name, job_id,&column_name
FROM     employees
WHERE    &condition
ORDER BY &order_column;

Si vous n’entrez pas de valeur pour une variable de substitution, une erreur vous sera retournée.

[Note]

Une variable de substitution peut être utilisée à n’importe quel endroit dans l’ordre SELECT sauf en tant que premier mot entré dans le prompt SQL.

Utilisation de double-esperluettes pour les variables de substitution

Vous pouvez utiliser des double-esperluettes (&&) pour une variable de substitution si vous souhaitez réutiliser une valeur sans que celle-ci vous soit demandée à chaque fois. L’utilisateur ne sera invité à spécifier une valeur qu’une seule fois.

Dans cet exemple, l’utilisateur sera interrogé pour la valeur de la variable column_name qu’une seule fois. La valeur spécifiée par l’utilisateur, par exemple department_id, est utilisée pour affichage et le tri des données.

SELECT   employee_id, last_name, job_id, &&column_name
FROM     employees
ORDER BY &column_name;

iSQL*Plus stocke la valeur fournie en utilisant la commande DEFINE et l’utilise à chaque appel à la variable. Une fois la variable en place, vous pouvez utiliser la commande UNDEFINE pour la supprimer :

UNDEFINE column_name

Utilisation de la commande iSQL*Plus DEFINE

Cet exemple crée une variable de substitution iSQL*Plus pour un identifiant d’employé en utilisant le mot-clé DEFINE. Lors de l’exécution, l’identifiant de l’employé, le nom, le salaire et le numéro de département est affiché pour l’employé défini. Etant donné que la variable est créée par le biais de la commande iSQL*Plus DEFINE, l’utilisateur ne sera pas invité à spécifier un numéro d’employé. A la place, la valeur de la variable définie sera automatiquement remplacée dans l’ordre SELECT.

La variable de substitution EMPLOYEE_NUM est présente et accessible dans la session jusqu’à ce que l’utilisateur utilise la commande UNDEFINE EMPLOYEE_NUM ou qu’il quitte iSQL*Plus.

Utilisation de la commande VERIFY

Utilisez la commande iSQL*Plus VERIFY pour confirmer un changement de valeur. En utilisant SET VERIFY ON, vous forcerez iSQL*Plus à afficher le texte de la commande avant et après la substitution par la valeur de la variable.

L’exemple suivant affiche l’ancienne ainsi que la nouvelle valeur de la variable EMPLOYEE_NUM.

SET VERIFY ON

SELECT employee_id, last_name, salary, department_id
FROM   employees
WHERE  employee_id = &employee_num;
old 3: WHERE employee_id = &employee_num
new 3: WHERE employee_id = 200

Variables système iSQL*Plus

iSQL*Plus utilise de nombreuses variables système pour contrôler l’environnement de travail. Une de ces variables est VERIFY. Pour obtenir une liste complète de ces variables système, vous pouvez utiliser la commande SHOW ALL.

A propos de SUPINFO | Contacts & adresses | Enseigner à SUPINFO | Presse | Conditions d'utilisation & Copyright | Respect de la vie privée | Investir
Logo de la société Cisco, partenaire pédagogique de SUPINFO, la Grande École de l'informatique, du numérique et du management Logo de la société IBM, partenaire pédagogique de SUPINFO, la Grande École de l'informatique, du numérique et du management Logo de la société Sun-Oracle, partenaire pédagogique de SUPINFO, la Grande École de l'informatique, du numérique et du management Logo de la société Apple, partenaire pédagogique de SUPINFO, la Grande École de l'informatique, du numérique et du management Logo de la société Sybase, partenaire pédagogique de SUPINFO, la Grande École de l'informatique, du numérique et du management Logo de la société Novell, partenaire pédagogique de SUPINFO, la Grande École de l'informatique, du numérique et du management Logo de la société Intel, partenaire pédagogique de SUPINFO, la Grande École de l'informatique, du numérique et du management Logo de la société Accenture, partenaire pédagogique de SUPINFO, la Grande École de l'informatique, du numérique et du management Logo de la société SAP, partenaire pédagogique de SUPINFO, la Grande École de l'informatique, du numérique et du management Logo de la société Prometric, partenaire pédagogique de SUPINFO, la Grande École de l'informatique, du numérique et du management Logo du IT Academy Program par Microsoft, partenaire pédagogique de SUPINFO, la Grande École de l'informatique, du numérique et du management

SUPINFO International University
Ecole d'Informatique - IT School
École Supérieure d'Informatique de Paris, leader en France
La Grande Ecole de l'informatique, du numérique et du management
Fondée en 1965, reconnue par l'État. Titre Bac+5 certifié au niveau I.
SUPINFO International University is globally operated by EDUCINVEST Belgium - Avenue Louise, 534 - 1050 Brussels