IFT1176 Aspects Avancés en Java

Département d'informatique
et de recherche opérationnelle

Automne 2002


Accueil

Professeurs et
Démonstrateurs

Horaires et locaux

Notes de cours

Cours
après Cours

Démonstrations
et devoirs

Examens

Liens utiles

Au sujet du
livre de cours

Consultez vos notes

Foire aux questions
(FAQ)

Sessions antérieures
(A01, H02, E02)

Retour vers
session A02

Démonstration #8

                                                                              
Dernière mise à jour : Sun Nov 17 14:57:22 EST 2002


Buts:

  • sqlplus de Oracle version 9i (9.2.0.1)

L'ensemble des fichiers demo08.zip.


Une documentation en ligne de sqlplus/oracle se trouve sur titan, en tapant la commande oradoc, sinon sur les liens:

[Oracle9i Database List of Books]. Cliquez par la suite sur [SQL-Plus quick reference], pour un tour d'horizon rapide ; sinon [SQL Reference] pour une documentation complète.

Pour une version antérieure voir par exemple ce lien [Oracle7]


1. Connexion au système

Vous disposez d'un compte sur la machine du réseau qui s'appelle titan. Pour accéder à titan, on procède comme suit :

  1. Cliquez sur Branchement Unix
  2. Entrez votre nom_usager & password_unix.
  3. Tapez par la suite la commande ssh votre_nomusager@titan
  4. Il vous sera demandé votre password unix. Entrez le.
  • Une fois connecté, vous devez initialiser votre environnement ORACLE. Cela se fait avec la commande suivante :

      
      inclure oracle
      

    Vous avez maintenant accès à ORACLE en tapant la commande suivante :

      
      sqlplus
      

    Vous allez obtenir ce qui suit:

      
      
      SQL*Plus: Release 9.2.0.1.0 - Production on _ici_la_date_et_heure_
      Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

    SQLPLUS est l'utilitaire d'ORACLE qui permet de soumettre directement des commandes SQL.

    Il vous sera demandé d'insérer votre nom_usager (l'habituel) et le mot de passe oracle qui vous a été attribué (le mot de passe de la recette que vous avez utilisé la première fois pour valider votre compte au diro).

    Vous devriez normalement obtenir l'affichage des messages suivants :

      
      
      

      Enter user-name: votre_nom_usager
      Enter password: celui_de_la_recette

      Connected to:
      Oracle9i Enterprise Edition Release 9.2.0.1.0 - 64bit Production
      With the Partitioning, OLAP and Oracle Data Mining options
      JServer Release 9.2.0.1.0 - Production

    Il ne reste plus qu'à effectuer votre première requête (attention au point-virgule à la fin de la commande) :

      
      SQL> select * from user_catalog;
      

      Elle donne toutes les tables se trouvant dans le dossier de l'usager courant.


    Un bon truc pour finir : la touche CTRL-C interrompt une commande.

    2. Utilisation de SQLPLUS

    Vous allez utiliser SQLPLUS pour exécuter des commandes SQL de type DDL (création de tables) et DML (recherche, mises-à-jour, ...). On peut entrer directement les commandes en les tapant sous SQLPLUS, mais en cas de faute de frappe ou d'erreur, il est difficile de corriger le texte. Il est donc fortement recommandé de procéder de la manière suivante :

    1. Avec emacs/vi, tapez votre commande, et sauvegardez-la dans un fichier (par exemple, entrez select * from user_catalog; dans emacs/vi et enregistrez ce texte dans le fichier reqxyz.sql Il faut juste que l'extension soit .sql).
       
    2. Sous SQLPLUS : demandez l'exécution du fichier reqxyz.sql comme suit :
        
                    SQL> @reqxyz
             
    3. Si quelque chose cloche, corrigez avec emacs/vi, enregistrez le fichier, et ré-exécutez-le. Ou bien vérifier que vous avez bien le fichier dans le répertoire où vous aviez lancé sqlplus, sinon indiquez le chemin complet pour arriver au fichier.

    Créez ainsi un fichier pour chaque commande : cela vous permettra de ne rien perdre et d'avoir un minimum de frappe clavier.

    Sous SQLPLUS, vous avez quelques commandes utiles dont voici une brève liste.

      
      SQL> desc tab                   // Donne le schéma de la table 'tab'.
      SQL> select table_name
              from user_tables;       // Liste des tables que vous avez créées.
      SQL> list                       // Affiche le dernier ordre exécute
      SQL> spool fic                  // Copie l'affichage à l'écran dans 'fic.lst'
      SQL> spool off                  // Stoppe la copie dans 'fic.lst'
      SQL> exit;                      // Sortir de SQLPLUS
      

    Les commandes DROP TABLE permettent d'exécuter le fichier de création plusieurs fois de suite, en détruisant d'abord les tables éventuellement crées lors des exécutions précédentes. Il faut détruire les tables dans l'ordre inverse de création, afin de ne pas violer les contraintes de FOREIGN KEY.

    Remarques importantes :

    • TOUTES LES COMMANDES SQL DOIVENT SE TERMINER PAR UN ';'. Si vous oubliez le ';', une ligne '2' vous est proposée. Dans ce cas tapez un ';' pour finir la commande.
    • Les chaînes de caractères s'écrivent avec une simple quote : 'Vertigo' et pas ``Vertigo''.
    • Les majuscules et les minuscules sont interprétés différemment. Par exemple 'Vertigo' est considéré comme différent de 'vertigo' ou 'VERTIGO'. Pensez-y en faisant des sélections ! Un moyen d'éviter les problèmes est d'utiliser la fonctions UPPER qui met tout en majuscule. Par exemple :
      
               select * from film where UPPER(titre) = 'VERTIGO'; 
          

      Sinon, vous pouvez vous baser sur la convention suivante : toutes les chaînes de caractères commencent par une majuscule suivie de minuscules.


    3. Exercices


    exercice -1- (création de tables): Solution sqlexo1.sql

    Au départ, il n'y a aucune table dans votre propre espace ORACLE. Pour en créer une, il faut passer par cette commande:

    CREATE TABLE <table> (

      <colonne 1>, <type 1>, <contraintes 1>,
      <colonne 2>, <type 2>, <contraintes 2>,
      ...
      <colonne n>, <type n>, <contraintes n>,
      PRIMARY KEY(<colonne x1>, <colonne x1>, ...),
      FOREIGN KEY(<colonne y1>) REFERENCES <table 1> <contraintes y1>,
      FOREIGN KEY(<colonne y2>) REFERENCES <table 2> <contraintes y2>,
      ...
      FOREIGN KEY(<nom colonne yn>) REFERENCES <table> <contraintes>
    );

    Pour créer la table suivante:

    STATION (nomStation, capacité, lieu, région, tarif)

    Attention à bien définir les clés primaires et étrangères. Voici les autres contraintes portant sur ces tables.

    1. Les données capacité, lieu, nom, ville, solde et nbPlaces doivent toujours être connues.
    2. Les montants (prix, tarif et solde) ont une valeur par défaut à 0.
    3. Il ne peut pas y avoir deux stations dans le même lieu et la même région.
    4. Les régions autorisées sont : 'Ocean Indien', 'Antilles', 'Europe', 'Ameriques' et 'Extreme Orient'.
    5. La destruction d'une station doit entraîner la destruction de ses activités et de ses séjours.
    CREATE TABLE Station (nomStation VARCHAR2 (30),
                          capacite   NUMBER (10) NOT NULL,
                          lieu       VARCHAR2(30) NOT NULL,
                          region     VARCHAR2 (30),
                          tarif      NUMBER (10,2) DEFAULT 0,
                          CONSTRAINT cle_station PRIMARY KEY (nomStation),
                          CONSTRAINT cle_lieu_region UNIQUE (lieu, region),
                          CONSTRAINT nom_region
    		                CHECK (region IN ('Ocean Indien',
                                                      'Antilles', 'Europe',
                                                      'Ameriques', 'Extreme Orient'))
                        );                                                          
    
    
    
    

    En vous inspirant du précédent exemple, en tenant compte aussi des contraintes imposées sur lui, et sachant les types données:

    1. CHAR(n) Données de type caractère de longueur fixe n. n est compris entre 1 et 255.
    2. VARCHAR2(n) Chaîne de caractères de longueur variable n. n est la taille de la chaîne et est compris entre 1 et 2000.
    3. VARCHAR(n) Même chose que VARCHAR2(n).
    4. NUMBER(p,s) Nombre de précision p et d'échelle s. La précision est compris entre 1 et 38 et l'échelle entre -84 et 127.
    5. LONG Données de type caractère de longueur variable allant jusqu'à 2 giga-octets.
    6. DATE Données de type Date dont le format standard est sous forme AA-MM-JJ.
    7. RAW(n) Données de type binaire de longueur fixe n. Contrairement au type CHAR, n doit être spécifié pour ce type et son maximum est de 255.
    8. LONGRAW Données de type binaire de longueur variable allant jusqu'à 2 giga-octets.
    9. ROWID Chaîne hexadécimale représentant l'adresse unique d'une ligne de la table. Sa valeur est retournée par la pseudo-colonne du même nom.
    10. MLSLABEL Format binaire du label du système d'exploitation de longueur de 4 octets. Disponible seulement avec Oracle trusted.
    11. RAWMLSLABEL Format binaire du label du système d'exploitation. Disponible seulement avec Oracle trusted.

    Créer les tables suivantes:

    • ACTIVITE (nomStation, libellé, prix)
    • CLIENT (id, nom, prénom, ville, région, solde)
    • SEJOUR (id, station, début1, nbPlaces)

    La solution de cet exercice se trouve dans ce fichier sqlexo1.sql


    exercice -2- (charger un fichier de requêtes): Solution solexo2.txt

    Après avoir sauvegardé le fichier sqlexo1.sql dans un répertoire donné de votre compte usager, invoquez la requête sql suivante:

      
                  SQL> @sqlexo1
           

    Que va afficher la commande suivante:

      
      SQL> select * from user_catalog;
      

    exercice -3- (insertion de données) Solution: sqlexo3.sql

    Rappel : toute mise-à-jour peut être validée avec la commande commit; et annulée avec la commande rollback;.

    Insérez dans la base les données de de la figure 1 (placez les ordres INSERT pour pouvoir les exécuter à volonté). Attention, l'ordre des INSERT est important (pourquoi ?).

    Vous pouvez ensuite tester les contraintes avec quelques ordres SQL. Par exemple : détruisez la station et vérifiez que les activités ont disparu ; insérez une autre station en (Guadeloupe, Antilles) ; insérez une station dans une région 'Nullepart', etc.


    STATION
    NomStation Capacité Lieu Région Tarif
    Venusa 350 Guadeloupe Antilles 1200
    Farniente 200 Seychelles Océan Indien 1500
    Santalba 150 Martinique Antilles 2000
    Passac 400 Alpes Europe 1000

    ACTIVITES
    NomStation Libellé Prix
    Venusa Voile 150
    Venusa Plongée 120
    Farniente Plongée 130
    Passac Ski 200
    Passac Piscine 20
    Santalba Kayac 50

    CLIENT
    id nom prénom ville région solde
    10 Fogg Phileas Londres Europe 12465
    20 Pascal Blaise Paris Europe 6763
    30 Kerouac Jack New York Amérique 9812

    SEJOUR
    idClient station début nbPlaces
    20 Venusa 1998-08-03 4
    10 Passac 1998-07-01 2
    30 Santalba 1996-08-14 5
    20 Santalba 1998-08-03 4
    30 Passac 1998-08-15 3
    30 Venusa 1998-08-03 3
    20 Venusa 1998-08-03 6
    30 Farniente 1999-06-24 5
    10 Farniente 1998-09-05 3


    Figure 1 : La base 'Agence'

    À titre d'exemple pour insérer les éléments de Station

      
      INSERT INTO Station (NomStation, Capacite, Lieu, Region, tarif)
        VALUES     ('Venusa', 350, 'Guadeloupe', 'Antilles', 1200);
                  
           

    exercice -4- (un ensemble de requêtes) Solution: solexo4.txt

    En reprenant la précédente table, écrire les requêtes sql permettant de réaliser ce qui suit:

    • Nom des stations ayant strictement plus de 200 places.
    • Noms des clients dont le nom commence par P ou dont le solde est supérieur à 10 000.
    • Quelles sont les régions dont l'intitulé comprend (au moins) deux mots ?
    • Nom des stations qui proposent de la plongée.
    • Nom des clients qui sont allés à Santalba.
    • Donnez les couples de clients qui habitent dans la même région. Attention : un couple doit apparaître une seule fois.
    • Nom des régions qu'a visité Mr Pascal.
    • Nom des stations visitées par des européens.
    • Qui n'est pas allé dans la station Farniente ?
    • Quelles stations ne proposent pas de la plongée ?

  • [top]


    Dernière mise à jour : Sun Nov 17 14:57:22 EST 2002
    dift1176@iro.umontreal.ca