Sujet : etude de cas aerocnam


télécharger 104.28 Kb.
typeDocumentos
exemple-d.com > loi > Documentos
EXERCICES TP B6 . PIERRE BILGER

TP
BASES DE DONNEES B0
ANNEE 2004/2005

Sujet : ETUDE DE CAS AEROCNAM
Pour améliorer sa gestion, la compagnie aérienne AEROCNAM a décidé de s'informatiser.
Les fonctions que la compagnie souhaite automatiser sont :

* la gestion des avions,

* la gestions de personnels,

* la gestions des vols
Gestion des avions
Pour chaque avion identifié par un numéro unique d'immatriculation (NUMAV), la compagnie souhaite mémoriser :les informations suivantes :

- son type <> (TYPAV),

- sa date de mise en service (DATMS),

- son nombre d’heures de vol depuis sa dernière révision (NBHDDREV).
La sécurité des vols impose que chaque appareil soit révisé tous les 6 mois ou toutes les 1000 heures de vols sous peine d'être interdit de vol.

Ces révisions font l'objet d'un rapport (TEXTE) dans lequel sont mentionnées les anomalies constatées, les réparations effectuées et les organes changés

La date de la révision (DATREV) ainsi que le nombre d'heures de vol de l'avion au moment de la révision (NBHREV) doivent être enregistrés.

Après l’enregistrement des données de la révision, le nouveau système devra remettre automatiquement à zéro le compteur «NBHDDREV» de l’avion correspondant.

Le système à réaliser devra

* autoriser l'évolution du parc d'aéronefs :

- mise en service de nouveaux appareils,

- retrait d'avions (en vérifiant qu'il n'est pas en vol)

* interdire la modification du type et de la date de mise en service d'un avion dés lors que ces informations auront été préalablement enregistrées.
Gestion des personnels
La compagnie emploi deux catégories de personnels :
* les personnels non navigant tels que les mécaniciens, les administratifs

* les personnels navigants tels que les pilotes, steward/hôtesse
La gestion des personnels impose de disposer, pour l'ensemble des personnels de la compagnie des informations suivantes : numéro d'employé (NUMEMP), nom (NOM), prénom (PRENOM), téléphone (TEL), adresse (ADRESSE), salaire (SAL), fonction (FONCTION), date d'embauche (DATEMB).

Pour le personnel navigant composant les équipages, il est nécessaire de suivre leur nombre d'heures de vol du mois en cours (NBMHV) ainsi que la totalité de leurs heures de vol (NBTHV).

Ces données sont initialisées manuellement à l'embauche du personnel et sont mises à jour automatiquement à l'issu des vols effectués.

Gestion des vols
Catalogue des vols
Pour sa clientèle, la compagnie diffuse un catalogue des vols programmés à la semaine.
Chaque vol au catalogue se caractérise par les informations suivantes :
- un numéro de vol (NUMVOL),

- une ville de départ (VILDEP),

- une ville d'arrivée (VILARR),

- une heure de départ (HDEP),

- une durée de vol (DURVOL).
Le même numéro de vol peut être programmé sur plusieurs jours de la semaine (JVOL). En revanche le même numéro de vol ne peut pas être programmé plus qu’une fois par jour.
Pour un vol donné, le catalogue fournit la liste des escales, et par ville escale prévue (VILESC) il est nécessaire de connaître les renseignements suivants :
- heure d'arrivée à l'escale (HARRESC),

- durée à l'escale (DURESC).

Un vol peut comprendre de 0 à n escales.
Pour simplifier la description de l'itinéraire d'un vol, les étapes seront numérotées dans l'ordre croissant de desserte des villes (NOORD).
Remarques :
Pour simplifier la gestion des vols :
- il ne sera pas tenu compte des dates de validité des vols catalogues;

- il ne sera pas tenu compte des décalages horaires pouvant exister entre les villes;

- les heures de départ et d'arrivée mentionnées sont exprimées en heures locales;

- les seules villes gérées dans cette base de données sont celles qui sont concernées par les vols (ville de départ, ville d'arrivée, ville escale).

Vols réels
Le suivi de l'activité aérienne de la compagnie se fait au niveau de chaque vol réalisé.
Pour chaque vol réel la compagnie doit disposer des informations suivantes :

- le numéro du vol catalogue (NUMVOL)

- la date prévue du vol (DATVOL),

- la date réelle du vol (DATVOLR),

- l'heure réelle de départ (HDEPR),

- la durée réelle du vol (DURVOLR),

- le numéro de l'aéronef qui effectue le vol (NUMAV)

- par escale effectuée

- la ville escale réelle (VILESCR)

- l'heure d'arrivée (HARRESCR)

- la durée de l'escale (DURESCR)

- la composition de l'équipage :

- les numéros d'employés des membres d'équipages ayant assurés ce vol
Règles de gestion spécifiques aux vols
- correspondance date prévue du vol et date réelle du vol
* Deux vols portant le même numéro de vol (NUMVOL) prévus initialement à deux dates différentes (DATVOL) peuvent s’effectuer à la même date (DATVOLR).

- affectation de l'aéronef
* on ne peut affecter à un vol qu'un avion respectant les règles suivantes :

- le nombre d'heures de vol avant révision supérieur à la durée totale du vol prévu

- la date du vol prévu est inférieur à la date de révision + 6 mois
- affectation des membres d'équipages
A chaque vol est affecté une équipe de plusieurs employés navigants qui effectuent le vol complet (départ, arrivée)

Les règles de sécurité imposent qu'un équipage soit constitué au minimum d'un pilote et d'un copilote. Par contre la présence de stewards et d'hôtesses n'est pas indispensable.
- itinéraire
Les conditions météorologiques ou les incidents techniques peuvent modifier l'itinéraire (escales) prévu au catalogue.(en d'autres termes une ville escale réelle peut différer de celle prévue au catalogue).

Pour cela, les étapes de l’itinéraire effectuées sont numérotées dans l’ordre croissant de desserte des villes escales réelles (NOORDR).Par contre les villes de départ et d'arrivée seront obligatoirement les mêmes que celles prévues au catalogue des vols.

Les vols arrivant sur une ville autre que celle prévue au catalogue ne seront pas traités dans cette base de données.

PREPARATION DES TP et MISE EN ROUTE.

Pour se connecter à la machine unix de tp, il faut dans la fenêtre d’accueil du terminal X selectionner la machine newton ou tapez son adresse IP : 163.173.128.37 et cliquer sur le bouton OK. On peut également atteindre cette machine à distance par la commande telnet.

Exemple : telnet 163.173.128.37
Dès lors une fenêtre apparaît et le prompt login : vous invite à saisir votre nom d’utilisateur :en principe les 6 premiers caractères de votre nom suivi du caractère _ suivi de la première lettre de votre prénom (LE TOUT EN MINUSCULE).

L’étape suivante est la saisie du mot de passe :

passwd : taper le code de 8 caractères en haut à gauche de la carte d’étudiant.

(UNE LETTRE EN MAJUSCULE 6 CHIFFRES UNE LETTRE EN MAJUSCULE). Cette saisie n’est pas affichée à l’écran.
ATTENTION : sous unix on fait la distinction entre minuscule et majuscule.
Si votre connexion est acceptée, vous verrez apparaître le prompt du système d’exploitation UNIX qui est sur la machine newton :

Newton% Dès lors vous pouvez taper des commandes UNIX…
Si cela ne fonctionne pas :

-soit vous vous êtes trompé ! ! !

-soit votre compte n’est pas créé.

Demandez à l’assistant de TP.
COMMANDES A NE LANCER QUE LORS DU PREMIER TP POUR PREPARER VOTRE ENVIRONNEMENT UNIX.

Vous allez utiliser la commande cp qui sous UNIX permet de copier des fichiers.

Le caractère point à la fin de la commande représente le répertoire courant (votre répertoire). Il y a au moins un espace après le nom de la commande et entre chaque argument. Le répertoire de référence est /users/ensinf/abouchak (tous les fichiers dont vous aurez besoins dans ce TP se trouvent dans ce répertoire).
Newton% cp /users/ensinf/abouchak/.login .

Newton% source .login (Pour exécuter ce fichier qui définit les variables)

Newton% cp /users/ensinf/abouchak/struct1.sql .

Newton% cp /users/ensinf/abouchak/donnees1.sql .

Newton% cp /users/ensinf/abouchak/revision.dat .

Newton% cp /users/ensinf/abouchak/revision.ctl .

Attention le point à la fin de chaque commande représente le répertoire courant (votre répertoire), il ne faut pas l’oublier et laisser un espace avant !

QUELQUES COMMANDES UNIX…
ls et ls -l permet de lister le contenu d’un répertoire.

( ls –a pour voir les noms des fichiers qui commencent par un point comme par exemple .login)

pwd permet d’afficher le nom du répertoire dans lequel on se trouve.

cp permet de copier un ou plusieurs fichiers.

mv permet de renommer ou déplacer un fichier.

rm est la commande qui permet de détruire un fichier.

mkdir permet de créer un répertoire.

pg ou more permet de lire le contenu d’un fichier ASCII.

cd permet de changer de répertoire.

ps –ef permet de lister tous les programmes en cours d’exécution sur votre machine.
Avant de lancer l’outil sqlplus vous allez lancer un éditeur de texte en arrière-plan ce qui vous permettra de créer ou de modifier des fichiers :

Newton% nedit &

Cet éditeur est un éditeur graphique disposant de menu (Fichier/Ouvrir, Fichier/Sauver sous…)
L’accès au langage SQL se fait par l’intermédiaire d’un outil interactif qui sous ORACLE s’appelle sqlplus. Pour utiliser cet outils il faut bénéficier d’un compte utilisateur (user/password) pour accéder à la base de données. Vous allez utiliser le même nom d’utilisateur et le même mot de passe que pour la connexion sous UNIX.
sqlplus est un outil simple qui permet de travailler sur la base de données.
Le nom de la base de données sous laquelle on souhaite travailler est précisé dans une variable d’environnement du système d’exploitation (en effet il se peut qu’il y ait plusieurs bases sur la machine).
La variable s’appelle ORACLE_SID
Exemple de déclaration :
Sous UNIX avec le shell ksh :

ORACLE_SID=DEMO

export ORACLE_SID
Sous SOLARIS sur la machine du CNAM avec le shell csh :

setenv ORACLE_SID tpcnam
Cet variable fait partie d’un ensemble de variables qui caractérisent un utilisateur du système d’exploitation capable de travailler avec le logiciel ORACLE. Les variables dont on a besoin sont définies dans le fichier .login qui sera lancé automatiquement lors de votre connexion.

Connexion à la base tpcnam :

Newton% sqlplus

Username : < -- VOTRE NOM D’UTILISATEUR.

Password : < -- VOTRE MOT DE PASSE.

Ou plus rapide mais moins discret :


Newton% sqlplus nom_utilisateur/mot_de passe
Si la connexion réussit vous obtiendrez le prompt :

SQL>

Pour paramétrer de façon correcte la fenêtre d’affichage, après chaque connexion tapez :

SQL> set pause on (Pressez Entrée pour aller à la page suivante)

SQL> set pagesize 40 (40 lignes par page)

SQL> set linesize 100 (100 colonnes par pages)
Et dès lors vous pouvez taper des commande SQL.

Exemple : SQL>select * from user_tables ;
Un ordre SQL comporte une structure matérialisée par des mots clés (SELECT, FROM, WHERE...) et se termine par ;

Il n’y a pas de distinction entre minuscule et majuscule sauf pour ce qui apparaît entre des quôtes (alphanumérique ou date).

COMMANDES A NE LANCER QUE LORS DU PREMIER TP POUR PREPARER VOTRE ENVIRONNEMENT ORACLE.
SQL> start struct1

Cette commande permet de créer quelques tables de l’application AEROCNAM.

Vous serez déconnecté à la fin de l’exécution de la commande. Lisez le contenu de ce fichier avec nedit ou pg.

SQL> start donnees1

Cette commande permet d’alimenter les tables que l’on vient de créer. Quelques ‘INSERT’ sont réalisés.

Vous serez déconnecté à la fin de l’exécution de la commande. Lisez également le contenu de ce fichier avec nedit ou pg.
Vous environnement est maintenant prêt.
Les énoncés et questions des TP se trouvent entre les pages 10 à 24.

Pour vous aider il convient d’établir le MCD de l’application.

Regarder la structure des tables que vous allez utiliser ainsi que leur contenu :

SQL>select table_name from user_tables ; (pour vous permettre de connaître la liste des tables dont vous disposez).

SQL> describe vol;

SQL> desc vol ; (pour voir la structure d’une table).

SQL> select * from vol ; (pour voir le contenu d’une table).
Des solutions indicatives sont proposées (à partir de la page 26 du faciscule).

Ces solutions sont un minimum, je vous conseille d’essayer d’autres commandes par vous-même.

Ces solutions vous seront remises bientôt.
Bibliographie (non indispensable):
Pour les outils de développement :

Collection Le Programmeur.

Oracle 8i : Développement de bases de données.

DAVID LOCKMAN

CAMPUSPRESS
Pour l’administration de base de données ORACLE :

Oracle 9i sous Linux

Gilles BRIARD.

EYROLLES
QUELQUES FACILITES :EDITION DES ORDRES AVEC SQLPLUS.

Ce sont des directives uniquement disponible sous le SQL d’ORACLE:
SQL> save fic permet de sauver l’ordre courant dans le fichier fic.sql
SQL> get fic permet de récupérer le contenu de fic.sql pour alimenter le buffer de sqlplus.
SQL> start fic lance l’exécution du contenu du buffer.
SQL> l permet de lister le contenu du buffer.
SQL> ed permet d’appeler l’éditeur du système d’exploitation (vi sous UNIX).

Il vous sera plus facile d’utiliser nedit.
SQL> ! est le caractère d’échappement qui permet de lancer une commande du système d’exploitation sans sortir de sqlplus.
SQL> exit permet de sortir de sqlplus.
SQL> def variable=emp permet de définir une variable ;

SQL> select * from &variable ;

SQL> undef permet d’annuler la définition de variable : undef variable.
SQL> show all permet de lister les variables de sqlplus.
SQL> set permet de positionner des variables :

SQL> set linesize 80 ;

SQL> set pagesize 24 ;

SQL> set pause on ;

SQL> set pause “ PAGE SUIVANTE ” permet d’arrêter le défilement entre chaque page.
SQL> spool fichier permet de copier la sortie écran dans un fichier fichier.lst. L’opération est terminée par spool off pour permettre le vidage du buffer de sortie.

TRAVAUX PRATIQUES :LES QUESTIONS…



A partir de la base de données qui vous est fournie :

Fichiers struct1.sql et donnees1.sql à copier et à lancer dans SQLPLUS (voir paragraphe précédent).
I - En utilisant le langage de description de données


    1. - Compléter la base de données avec les tables suivantes ainsi que leurs indexes :

Ne pas mettre de contraintes pour l’instant.


  1. REVISION :

Nom de colonne Type Null ?
NUMAV NUMBER(3) NOT NULL

DATEDEBREV DATE NOT NULL

RAPPORT VARCHAR2(60) NOT NULL

NBHREV NUMBER(4) NOT NULL

DUREV NUMBER(4) NOT NULL

  1. AVION :


NUMAV NUMBER(3) NOT NULL

TYPAV VARCHAR2(10) NOT NULL

DATMS DATE NOT NULL

NBHVA NUMBER(5)
c) EQUIPAGE (la copier à partir de la table EQUIPAGE_OLD qui vous est fournie)
nota : sans les contraintes d’intégrité
1.2 - Créer et tester les vues suivantes :
* VUE_VOL_DU_JOUR matérialisant les vols prévus du jour

* VUE_AEROPORT regroupant par ville les numéros des vols arrivants, partants ou transitants

* VUE_PERSONNEL semblable à la table EMPLOYE mais interdisant la consultation et la mise

à jour du salaire des employés
1.3 - Modifier les tables en :
a) ajoutant à la table EMPLOYE une colonne MGR NUMBER(3) créant ainsi une structure d’arbre permettant de décrire la hiérarchie des employés
b) remplaçant, dans la table REVISION, la colonne DATEDEBREV par la colonne DATREV.
c) retirant la contrainte NOT NULL à la colonne TEL de la table EMPLOYE

d) supprimant la coonne PRENOM de la table EMPLOYE
e) changeant le type NUMBER de la colonne TEL de la table EMPLOYE en VARCHAR2
f) supprimant la vue VUE_PERSONNEL
II En utilisant le langage de manipulation de données et les utilitaires d’ORACLE réaliser les traitements suivants :
2.1 Insérer des données en tables


  1. avec la commande INSERT de SQLPLUS insérer dans la table AVION les données suivantes :


NUMAV TYPAV DATMS NBHVA

2 A300 14/06/1993 179

3 DC10 11/07/1994 249

4 B737 10/04/1992 46


  1. avec l’utilitaire sqlldr username/password (comme avec la commande sqlplus).

(commande UNIX) charger, à partir des fichiers revision.dat et revision.ctl situé dans le répertoire /users/ensinf/abouchak , la table REVISION .

(visualiser les données rejetées lors du chargement et faire les corrections appropriées)
2.2 Modification des données de certaines tables :
a) ajouter 250 heures de vol à l’avion numéro 4
b) renseigner la colonne MGR de la table EMPLOYE par le contenu de la colonne SUP de la table

EMPLOYE_STRUC
c) effacer le numéro de téléphone de l’employé ayant pour nom MARTIN
d) modifier le sdonnées de la table EMPLOYE afin que le supérieur hiérarchique des employés

MARTIN, TURNER, JAMES soit l’employe ALLEN (et non plus l’employé BLAKE)
2.3 Suppression des données de certaines tables :
a) supprimer dans la table AVION l’avion numéro 3 et répercuter cette suppression dans les tables

dépendantes de cette table AVION
III Implémentations de contraintes d’intégrité
3.1 Création de clés
a) créer une clé primaire sur les tables VOL et AVION
b) créer une clé étrangère sur la colonne NUMAV des tables VOL et REVISION.
3.2 Implémentation d’un contrôle d’intégrité
Ajouter une clause contôlant que le salaire d’un amployé ne soit pas inférieur à 5000 francs.
3.3 Création de déclencheur (trigger)
a) créer un trigger propageant la suppression d’un avion de la table AVION dans les autres tables

dépendantes de cette table
b) créer un trigger interdisant l’insertion d’un équipage sant pilote ou copilote
IV Consultation de données en tables
Exprimer les requêtes en langage SQL permettant de répondre aux question suivantes :
4.1 interrogations relatives aux avions
a) quel est l’avion (numav) qui a été mis en service le plus récemment ?
b) quel est l’avion le plus ancien de la compagnie aérienne ?
c) quel est l’avion qui totalise le plus d’heures de vol ?
d) combien la compagnie possède-t-elle d’avions de type B747 ?
e) combien d’avion ont été mis en service en 1992 ?
f) quels sont les avions qui n’ont pas été révisés en 1993 ?
g) quels sont les avions qui ont eu des problèmes de trains d’attérrissage décelés lors des révisions?
h) quelle est la durée moyenne d’immobilisation, pour cause de révision, par type d’appareil ?
i) classer les avions de la compagnie par type d’appareil et au sein de chaque type par ordre

croissant de nombre de révisions
j)quel est l’avion qui posséde la durée minimale entre deux révision ?
4.2 interrogations relatives aux employés
a) quels sont les employés (numemp, nom) qui ont été embauchés en 1981 ?
b) quels sont les employés habitant en banlieue parisienne ?
c) quels sont les copilotes de la compagnie aérienne qui totalise plus de x heures de vol ?

(x paramètre saisi à l’exécution de la requête)
d) combien y a t il d’hôtesses et de steward qui ont été embauchés en 1981 et qui ont un salaire

supérieur à 12000 euros ?
e) quelles sont les hotêsses dont le salaire est supérieur à celui du steward le plus mal rémunéré ?

f) éditer la hiérarchie des employés selon la présentation suivante :

ORGANIGRAMME MGR

-------------------------------------------------------------------------------------

KING 839

CLARK 782 839

MILLER 934 782

JONES 566 839

SCOTT 788 566

ADAMS 876 788

FORD 902 566

SMITH 369 902

BLAKE 698 839

TURNER 844 698

ALLEN 499 698

WARD 521 698

MARTIN 654 698

JAMES 900 698
g) quel est le nombre d’employés embauchés par année ?
h) quel est l’employé le plus ancien de la compagnie ?
i) quels sont les pilotes qui ne sont jamais allés à VANCOUVER (ville arrivée ou ville escale) ?
j) quels sont les employés qui gagne plus que la moyenne de tous les employés ?
4.3 interrogations relatives aux vols
a) quel est le prochain vol prévu à destination de TOKYO ?
b) quels sont les vols sans escale pour se rendre à CHICAGO en partant de PARIS ?
c) quels sont les avions actuellement (ce jour à la présente heure) en vol ?

nota : normalement pour l’instant il n’y en a pas ! Insèrer des

données dans les tables de façon à avoir des occurences répondant à la présente question
d) quels sont les avions qui n’ont pas respectés les horaires de départ prévus ?
e) quel est le vol dont la durée d’immobilisation aux escales est la plus longue ?
f) pour chaque vol réalisé quel est le nombre total de membre d’équipage ?
g) quel est le nombre de vols réels pour TOKYO pour la période du 07.02.96 à

ce jour ?

h) quel est le type d’avion qui a effectué le vol AF888 le 09 février 1996 ?
i) quels sont les vols qui me permettent de me rendre à CHICAGO (ville arrivée ou ville escale) en

partant de BORDEAUX (ville départ ou ville escale)et en transitant à MONTREAL (utilisation de

vols différents avec correspondance aux escales) ?

PARTIE ADMINISTRATION.
Création d’un utilisateur.
SQL>create user tintin

2>identified by tintin

3>default tablespace etudiants

4>quota 2M on etudiants ;
Essayer de vous connecter avec votre nouvel utilisateur. Qu’est ce qu’il manque ?
SQL> grant connect to tintin ;
Essayer à nouveau. Essayer de consulter les tables de la démonstration AEROCNAM. Que faut-il faire pour y avoir accès ?
Consultez quelques tables systèmes. En déduire la structure de la base ?

Reperez dans les quelques tables suivantes les informations intéressantes.
dba_tables dba_tablespaces

dba_constraints dba_data_files

dba_indexes dba_rollback_segs

dba_roles V$logfile

dba_sys_privs V$controlfile

dba_role_privs dba_users

dba_tab_privs dict

dba_col_privs V$parameter

dba_extents

dba_free_space
OPTIMISATION.
Placez des statistiques sur toutes vos tables :
Sous sqlplus :

SQL>analyze table avion compute statistics ;

Etc…
Demander un plan d’execution pour savoir si vous utilisez l’index :

  1. Création pour votre utilisateur d’une table plan_table qui servira à recevoir des informations sur les plans d’exécution (cette opération est réalisée par un script qui s’appelle utlxplan.sql):

SQL>start $ORACLE_HOME/rdbms/admin/utlxplan.sql


  1. On souhaite pister la requête qui recherche un avion pour savoir si elle utilise l’index ou pas :

SQL>explain plan set statement_id=’PE1’ for select * from avion where numav=2 ;


  1. Il nous reste à regarder le résultat :

SQL>select * from plan_table ;

Comme dans la plupart des tables systèmes, seules une partie des informations est intéressantes. Repérer si on procède soit :

par un balayage complet de la table : TABLE ACCESS FULL

ou si on utilise l’index : TABLE ACCES BY INDEX ROWID.


  1. Conclusion :

Le résultat vous surprend t’il ?

Supprimer les statistiques sur votre table :

SQL>analyze table avion delete statistics ;

Refaites les opérations b et c. La façon de travailler d’ORACLE a-t’elle changer ?

Avez-vous compris ?…..
LES SAUVEGARDES.

Pour sauver la base de données, plusieurs possibilités s’offrent à nous :

1)sauvegarde système : on sauve tous les fichiers de la base avec un outil ou une commande de sauvegarde du système d’exploitation. 

2)sauvegarde des tables : on sauve les tables de la base avec la commande export.
EXEMPLE D’EXPORT EN INTERACTIF.
/oracle8/app/oracle/product/8.0.3/rdbms/log :exp
Export: Release 8.0.3.0.0 - Production on Thu Oct 7 10:41:7 1999
(c) Copyright 1997 Oracle Corporation. All rights reserved.

Username: system

Password:
Connected to: Oracle8 Enterprise Edition Release 8.0.3.0.50 - Production

With the Partitioning and Objects options

PL/SQL Release 8.0.3.0.0 - Production

Enter array fetch buffer size: 4096 >
Export file: expdat.dmp >
(1)E(ntire database), (2)U(sers), or (3)T(ables): (2)U > T
Export table data (yes/no): yes >
Compress extents (yes/no): yes >
Export done in US7ASCII character set and US7ASCII NCHAR character set
About to export specified tables via Conventional Path ...

Table(T) or Partition(T:P) to be exported: (RETURN to quit) > emp
. . exporting table EMP 14 rows exported

Table(T) or Partition(T:P) to be exported: (RETURN to quit) > dept
. . exporting table DEPT 4 rows exported

Table(T) or Partition(T:P) to be exported: (RETURN to quit) >
Export terminated successfully without warnings.

/oracle8/app/oracle/product/8.0.3/rdbms/log :

Essayer de sauvegarder quelques tables de votre application en utilisant la commande export.

Newton% exp nom_utilisateur/mot_de passe

Regarder le contenu de votre fichier de sauvegarde avec la commande more.

Renommez les tables que vous venez de sauver et essayer de les restaurer avec la commande import.


EXEMPLE D’IMPORT EN INTERACTIF.


IMPORT
/oracle8/app/oracle/product/8.0.3/rdbms/log :imp system/manager
Import: Release 8.0.3.0.0 - Production on Thu Oct 7 10:42:24 1999
(c) Copyright 1997 Oracle Corporation. All rights reserved.

Connected to: Oracle8 Enterprise Edition Release 8.0.3.0.50 - Production

With the Partitioning and Objects options

PL/SQL Release 8.0.3.0.0 - Production
Import file: expdat.dmp >
Enter insert buffer size (minimum is 4096) 30720>
Export file created by EXPORT:V08.00.03 via conventional path

List contents of import file only (yes/no): no >
Ignore create error due to object existence (yes/no): no > y
Import grants (yes/no): yes >
Import table data (yes/no): yes >
Import entire export file (yes/no): no >

Username: system
Enter table(T) or partition(T:P) names. Null list means all tables for user

Enter table(T) or partition(T:P) name or . if done: emp
Enter table(T) or partition(T:P) name or . if done: .
. importing SYSTEM's objects into SYSTEM

. . importing table "EMP" 14 rows imported

Import terminated successfully without warnings.

/oracle8/app/oracle/product/8.0.3/rdbms/log :

Vérifiez avec sqlplus si vos tables ont bien été restaurées.
PARTIE PL/SQL.

Présentation du jeu d’essai ORACLE permettant de réaliser les exemples de PL/SQL: tables dept et emp.
Les exemples suivants sont tirés des scripts sql de la démo demobld de sqlplus. Cette démo est lancée par la commande :
SQL>start $ORACLE_HOME/sqlplus/demo/demobld
Cette démo crée quelques tables dont les tables emp et dept qui sont les tables contenant la liste des employés et des départements d’une entreprise américaine.
Voici le contenu de ces tables, qui serviront de base pour les exemples et les exercices.
SQL> select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

---------- ---------- --------- ---------- --------- ---------- ---------- ----------

7369 SMITH CLERK 7902 17-DEC-80 800 20

7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30

7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30

7566 JONES MANAGER 7839 02-APR-81 2975 20

7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30

7698 BLAKE MANAGER 7839 01-MAY-81 2850 30

7782 CLARK MANAGER 7839 09-JUN-81 2450 10

7788 SCOTT ANALYST 7566 09-DEC-82 3000 20

7839 KING PRESIDENT 17-NOV-81 5000 10

7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30

7876 ADAMS CLERK 7788 12-JAN-83 1100 20

7900 JAMES CLERK 7698 03-DEC-81 950 30

7902 FORD ANALYST 7566 03-DEC-81 3000 20

7934 MILLER CLERK 7782 23-JAN-82 1300 10
14 rows selected.
SQL> select * from dept;
DEPTNO DNAME LOC

---------- -------------- -------------

10 ACCOUNTING NEW YORK

20 RESEARCH DALLAS

30 SALES CHICAGO

40 OPERATIONS BOSTON
La structure des tables est fournie par la commande describe ou desc.

SQL> desc emp ;

SQL>desc dept ;


Exemples de script PL/SQL.
Les extensions de SQL qui s’appellent PL/SQL permettent de réaliser des traitements procéduraux et de travailler avec des enregistrements tampons que l’on appelle curseur ou cursor. Ce sont les curseurs qui permettent des traitements de type boucle (loop, for…). Le PL/SQL propose également toutes une série de fonctions : fonctions d’entrées-sorties, fonction de formatage de données (to_date pour mettre en forme les dates, to_char pour une présentation alphanumérique…).
Voici quelques exemples :
Tout d’abord une boucle (loop..end loop) qui affiche 10 message à l’écran de type voici le numéro de passage.

Notez :

le signe d’affectation :=

la fonction d’affichage put_line qui fait partie du package (ensemble de procédures et fonctions regroupées) dbms_output ;

le signe de concaténation || qui permet de construire une ligne composée de plusieurs parties ;

; matérialise la fin d’une instruction PL/SQL et on indique la fin du traitement par /
declare

i number:=0;

begin

loop

i:=i+1;

dbms_output.put_line('voici le'|| i || ' ieme passage...');

exit when i=10;

end loop;

end;

/
SQL> start p10

voici le1 ieme passage...

voici le2 ieme passage...

voici le3 ieme passage...

voici le4 ieme passage...

voici le5 ieme passage...

voici le6 ieme passage...

voici le7 ieme passage...

voici le8 ieme passage...

voici le9 ieme passage...

voici le10 ieme passage...

PL/SQL procedure successfully completed.
Saisie interactive.
SQL>PROMPT ‘SAISISSEZ VOTRE TEXTE :’

SQL>ACCEPT v_texte

Saisie d’un libelle…

SQL>declare

2>var number ;

3>begin

4>var := &v_texte ;

5>end ;

6>/
TP : Que font les procédures et fonctions suivantes ?
DECLARE

v_salaire NUMBER(7) --variable locale

BEGIN

select sal into v_salaire from emp

where ename=’SCOTT’ for update of sal ;

IF v_salaire <= 1000 THEN

UPDATE emp set sal=sal*1.1 where ename=’SCOTT’ ;

ELSIF v_salaire BETWEEN (1000 and 5000) THEN

UPDATE emp set sal=sal*1.05 where ename=’SCOTT’ ;

ELSE UPDATE emp set comm=comm+100 where ename=’SCOTT’ ;

END IF ;

COMMIT ;

END ;
DECLARE

V_cinq CONSTANT :=5 ;

V_resultat NUMBER ;

i NUMBER :=1 ;

BEGIN

WHILE i <=9

LOOP

V_resultat := V_cinq * i ;

INSERT INTO table5 VALUES (V_cinq || ‘*’ || i || ‘=’ || V_resultat) ;

I :=i+1 ;

END LOOP ;

END ;

/
CREATE OR REPLACE FUNCTION f_euro (v_francs IN NUMBER)

RETURN REAL IS

V_euro REAL ;

BEGIN

v_euro :=v_francs / 6.55957 ;

return v_euro ;

END ;

/
DECLARE

v_francs REAL :=’1000’ ;

v_euro REAL ;

BEGIN

dbms_output.put_line(‘Valeur en francs :’|| to_char(v_francs)) ;

v_euro := f_euro (v_francs) ;

dbms_output.put_line(‘Valeur en euros :’|| to_char(v_euro)) ;

END ;

/
Dans cet exemple, on déclare un curseur (mémoire intermédiaire de travail). Le curseur est rempli par un ordre SQL (select empno,ename,dname from emp,dept where dept.deptno=emp.deptno and dname='SALES' order by ename;) et on traite successivement toutes les lignes du curseur grâce à la variable ligne (qui comporte les champs empno, ename et dname) :on affiche juste le nom de l’employé et le nombre total d’employés à la fin du département SALES (pour cette opération simple un simple ordre SQL aurait suffit). Le script est mis dans le fichier p10.sql ce qui permet des modifications et de réutiliser les lignes de code SQL.

DECLARE

numemp number(10):=0;

cursor C1 is

select empno,ename,dname from emp,dept

where dept.deptno=emp.deptno

and dname='SALES'

order by ename;

begin

FOR ligne in C1

loop

dbms_output.put_line(ligne.ename);

numemp:=numemp+1;

end loop;

dbms_output.put_line(numemp);

end;

/
SQL> start p10

PL/SQL procedure successfully completed.
Attention : on ne voit rien si on active pas l’affichage à l’écran pas la commande set serveroutput on. Cette commande se tape en début de session sqlplus et reste valable pendant toute la durée de la session.
SQL> set serveroutput on

SQL> start p10

ALLEN

BLAKE

JAMES

MARTIN

TURNER

WARD

6

PL/SQL procedure successfully completed.

Cet exemple ressemble au précédent mais une autre table est impactée (insertion de lignes dans cette table). Mais dans cette exemple la variable ligne a la même structure qu’une ligne de la table emp (c%rowtype), le curseur doit être ouvert quand on en a besoin par la commande open (pour des raisons d’économie de place mémoire). On transfère chaque ligne du curseur une par une dans la variable ligne avec la commande fetch into.

La condition d’arrêt est c%notfound c’est à dire que la boucle s’arrête quand il n’y a plus de lignes à traiter.

Il faut définir une autre table que l’on appellera table_temporaire :

SQL> create table table_temporaire

2 as select empno,ename,sal from emp where 1=2;

Table created.

declare

cursor c is select * from emp

where sal<2000;

ligne c%rowtype;

begin

open c;

loop

fetch c into ligne;

exit when c%notfound;

if ligne.sal<2000 then

insert into table_temporaire values

(ligne.empno,ligne.ename,ligne.sal);

end if;

end loop;

close c;

commit;

end;

/
Le résultat se voit dans la table table_temporaire :

SQL> select * from table_temporaire;

Appuyer sur ENTREE pour continuer...

EMPNO ENAME SAL

------ ---------- --------

7369 SMITH 800

7499 ALLEN 1600

7521 WARD 1250

7654 MARTIN 1250

7844 TURNER 1500

7876 ADAMS 1100

7900 JAMES 950

7934 MILLER 1300

8 rows selected.


Les triggers.
Les triggers sont des procédures prédéfinies qui seront déclenchées (lancées) de façon automatique en plus lors de la réalisation sur la base de l’opération SQL mentionnée dans le trigger ou déclencheur. Dans notre exemple, on souhaite à chaque INSERT ou UPDATE sur la table emp, vérifier que le salaire est compris dans une fourchette de salaire (les fourchettes de salaire sont définies pour chaque métier dans la table sal_guide). Il n’y a pas de contrôle pour le président !

SQL> select * from sal_guide;

Appuyer sur ENTREE pour continuer...

MINSAL MAXSAL JOB

-------- -------- ---------

500 9500 SALESMAN

1000 4000 CLERK

1000 4000 ANALYST

2000 7000 MANAGER
Remarquez :

Create or replace trigger pour remplacer l’ancienne version du trigger s’il existe déjà,

:NEW qui est un opérateur qui permet de préciser la nouvelle valeur d’un champ (il existe aussi :OLD) ; ainsi on peut faire une distinction entre l’ancienne et la nouvelle valeur d’une rubrique qui va être changée.

FOR EACH ROW : toutes les lignes de la tables sont contrôlées.

La fonction RAISE_APPLICATION_ERROR qui permet d’envoyer un message en bas de l’écran de l’utilisateur.
create or replace trigger verif_salaire

before INSERT or UPDATE of sal,job on emp

for EACH ROW

when (NEW.job <> 'PRESIDENT')

DECLARE

vminsal number;

vmaxsal number;

BEGIN

select minsal,maxsal INTO vminsal,vmaxsal from sal_guide

where job= :NEW.job;

if :NEW.sal < vminsal or :NEW.sal > vmaxsal

then RAISE_APPLICATION_ERROR (-20601, 'Salaire' || :NEW.sal ||

'hors tranche pour la profession' || :NEW.job ||

'de l employe' || :NEW.ename);

end if;

end;

/

SQL> start p9

Trigger created.




TP BASES DE DONNEES. PIERRE BILGER

similaire:

Sujet : etude de cas aerocnam iconFormulaire de conformite marchandises dangereuses ncc – nco – spo
«quantité raisonnable» et l’étude sera faite au cas par cas selon la md transportée et son utilisation, l’appareil utilisé et les...

Sujet : etude de cas aerocnam iconUne étude de cas emblématique

Sujet : etude de cas aerocnam iconDocument de plaidoyer – programme f. A. M
«L’association marocaine de lutte contre la violence» a fait une étude sur la philosophie du code pénal marocain, assez proche du...

Sujet : etude de cas aerocnam iconLe principe de précaution : allons-nous vers une société de défiance ?
«Ce soir ou jamais» sur France 3, datée du 11 mars 2009 (début de l’émission : 5 minutes)… pour faire émerger le sujet d’étude et...

Sujet : etude de cas aerocnam iconSaisine de la direction générale de l’aviation civile
«à partir d’»; «vers» un pays tiers (précisez votre cas) et pour une distance de kilomètres (précisez votre cas)

Sujet : etude de cas aerocnam iconDe la commune de port d’envaux
«excepté dans les cas ou la réglementation l’exige». La fourniture d’un zinc est obligatoire dans les cas de maladie contagieuse,...

Sujet : etude de cas aerocnam iconRapport d’Étude 30/04/2008

Sujet : etude de cas aerocnam iconChèque régularisé, demande de levée d'interdiction
«l’approvisionnement du compte et la représentation du chèque» ou cas n°2 saisir «la récupération du chèque et le règlement par un...

Sujet : etude de cas aerocnam iconEtude et propositions : la polygamie en France

Sujet : etude de cas aerocnam iconL'étude de marché : son utilité






Tous droits réservés. Copyright © 2017
contacts
exemple-d.com