Projet de SGBD II
Location hôtelière au sein
d’une agence de voyage
Préparé par :
ABBES Hanen
YANGUI Rania
Classe : T-MIM g3 TP 1
Enseignant de cours :
Mr BOUZGUENDA Lotfi
Enseignant de TD/TP :
Mr MILADY Nadhmi
Année universitaire : 2007 -
2008
────────
SOMMAIRE
────────
INTRODUCTION GENERALE .
……………………………page3
MISE EN ŒUVRE DU
PROJET……………………..….page3
CREATION DES
TABLES ………………………………..p
age3
Script de création de la table
hotel………………….page3
Script de création de la table
clients………………..page4
Script de création de la table categories………….
… page4
Script de création de la table chambres………….…
page4
Script de création de la table reservations……….…
page5
2
Script de création de la table demandes………….…
page5
INSERTION DES
DONNEES……………………………..pa
ge6
Données de la table hotels……………………...…
page6
Données de la table clients …………………….…
page6
Données de la table categories………… ……….…
page6
Données de la table chambres ………………….…
page6
REPONSES AUX QUESTIONS
PROPOSEES…………page7
Réponse à la question n° 1…………………….…
page7
Réponse à la question n° 2…………….…….…
page8
Réponse à la question n° 3 et à la question n° 4……
page9
Réponse à la question n° 5………………….…
page10
Réponse à la question n° 6………………….…
page11
CONCLUSION : AVANTAGES DE PL-
SQL …….…page13
INTRODUCTION GENERALE :
3
Le langage SQL peut être considéré comme l’une des principales raisons du
succès des bases de données relationnelles dans le monde des entreprises et des
sociétés.
Cependant, ce paradigme déclaratif de manipulation des données a des limites au
niveau du pouvoir d’expression : il est dès lors impossible de pouvoir exprimer
certaines manipulations complexes.
Pour cela il faut étendre SQL avec des constructions typiques des langages de
programmation (boucles, conditions, variables, etc.). Etant donné qu’un extension
procédurale de SQL n’a été standardisée que récemment avec SQL:1999, les
différents constructeurs de bases de données ont proposé depuis des longues
années leur langage propre.
PL/SQL est donc le langage proposé par Oracle.
MISE EN ŒUVRE DU PROJET :
On se propose de réaliser une application permettant la gestion des réservations
d’une chaîne d’hôtels au sein d’une agence de voyage.
CREATION DES TABLES :
Pour ce faire on a besoin de la creation de six tables pour faciliter cette affaire.
On donne ci-après le script de creations des tables necessaires :
Script de création de la table hotels :
create table hotels(
numh integer,
nomh varchar(30),
etoiles number,
adresse varchar(40),
code_postal char(5),
ville varchar(30),
constraint pk_hotel primary key(numh));
4
Script de création de la table clients :
create table clients(
numcli integer,
nomcli varchar(30),
adrcli varchar(50),
codepostcli char(5),
villecli varchar(30),
telcli char(15),
constraint pk_cli primary key(numcli));
Script de création de la table categories:
create table categories(
numcat integer,
nbplace integer,
nblits integer,
sdb char(1),
prix float,
constraint pk_cat primary key(numcat));
Script de création de la table chambres:
create table chambres(
numch integer,
numh integer,
exposition varchar(20),
priseTV char(1),
telephone char(15),
categorie number,
constraint pk_ch primary key(numh, numch),
constraint fk_chcat foreign key(categorie)
references categories(numcat) on delete set null ) ;
5
Script de création de la table reservations:
create table reservations(
numres integer,
datereserv char(8),
debut char(8),
fin char(8),
nbpers integer,
client integer,
hotel integer,
chambre integer,
constraint pk_res primary key(numres),
constraint fk_rescli foreign key(client) references clients(numcli) on delete set
null,
constraint fk_res foreign key(hotel,chambre) references
chambres(numh,numch) on delete set null) ;
Script de création de la table demandes:
create table demandes(
numdem integer,
datedemande char(8),
debut char(8),
fin char(8),
nbpers integer,
region varchar(20),
refus char(1),
client integer,
categorie integer,
hotel number,
constraint pk_dem primary key(numdem),
constraint fk_demcli foreign key(client) references clients(numcli) on delete set
null,
constraint fk_demcat foreign key(categorie) references categories(numcat) on
delete set null,
constraint fk_demhot foreign key(hotel) references hotels (numh) on delete set
null) ;
INSERTION DES DONNEES:
6
Afin de concrétiser le travail on insère quelques informations dans les tables
pour tester la fonctionnalité de la base de données:
Données de la table hotels :
insert into hotels values(1,'MOURADIE',5,'SOUSSE','5000','SOUSSE');
insert into hotels values(2,'LaLA Baya',5,'HAMMEMET','2000','HAMMEMET');
insert into hotels values(3,'SAVANA',5,'HAMMEMET','3000','HAMMEMET');
insert into hotels values(4,'HILTON',5,'TUNIS','1000','TUNIS');
Données de la table clients :
insert into clients values(1,'MOHAMED','Sfax','3000','sfax','74620390');
insert into clients values(2,'ALI','Tunis','1000','Tunis','71248793');
insert into clients values(3,'Maroua','Sousse','5000','Sousse','73248793');
insert into clients values(4,'AMAL','Gabes','6000','Gabes','76248793');
Données de la table categories :
insert into categories values(1,3,2,'1',40);
insert into categories values(2,4,3,'1',60);
insert into categories values(3,2,1,'1',30);
Données de la table chambres :
insert into chambres values(1,1,'mer','1','73666333','1');
insert into chambres values(2,1,'terain','1','73666222','1');
insert into chambres values(3,1,'terain','1','73666111','2');
insert into chambres values(1,2,'piscine','1','72666111','1');
insert into chambres values(2,2,'mer','1','72666222','2');
insert into chambres values(3,2,'mer','1','72666333','3');
insert into chambres values(1,3,'mer','1','72777111','1');
insert into chambres values(2,3,'terrain','1','72777222','1');
insert into chambres values(3,3,'terrain','1','72777333','2');
insert into chambres values(4,3,'terrain','1','72777333','3');
insert into chambres values(5,3,'mer','1','72777444','3');
insert into chambres values(1,4,'mer','1','71222444','1');
insert into chambres values(2,4,'piscine','1','71222555','1');
insert into chambres values(3,4,'piscine','1','71222666','2');
insert into chambres values(4,4,'mer','1','71222777','2');
insert into chambres values(5,4,'terrain','1','71222888','3');
insert into chambres values(6,4,'mer','1','71222999','3');
7
REPONSES AUX QUESTIONS
PROPOSEES:
Avant tout on active le serveur d’affichage par l’instruction :
set serveroutput on;
Réponse à la question n° 1:
Code de la fonction JULIENNE :
create or replace function julienne(d char) return Number is
v_jour Number;
begin
v_jour:=to_number(to_char(to_date(d),'J'));
return v_jour;
end julienne;
Test de la fonction JULIENNE :
declare
v_resultat Number;
begin
v_resultat:=julienne('01/02/08');
dbms_output.put_line('la date julienne est: '|| v_resultat);
end;
8
Réponse à la question n° 2:
Code de la fonction EST_LIBRE:
create or replace function est_libre(nh number, nch number, d number,
f number) return boolean is
v_debutj number;
v_finj number;
cursor c is select * from reservations
where chambre=nch and hotel=nh;
v_libre boolean :=true;
begin
for rec in c loop
v_debutj:=julienne(rec.debut);
v_finj:=julienne(rec.fin);
if((v_finj >= d and v_finj<=f) or (v_debutj <= f and v_debutj>=d)) then
return false;
end if;
end loop;
return v_libre;
end est_libre;
Test de la fonction EST_LIBRE :
declare
v_resultat boolean;
v_dj Number;
v_fj Number;
begin
v_dj:=julienne('05/02/08');
v_fj:=julienne('08/05/08');
v_resultat:=est_libre(1,2,v_dj,v_fj);
if(v_resultat=true) then
dbms_output.put_line('Cette chambre est disponible');
else
dbms_output.put_line('Cette chambre n est pas disponible');
end if;
end;
9
Réponse à la question n° 3 et à la question n° 4 :
declare
v_nh integer:= &numhot;
v_catch number:= &catchamb;
cursor c is select * from chambres where numh=v_nh and categorie=v_catch;
rec c %ROWTYPE;
begin
open c;
loop
fetch c into rec;
exit when c %notfound;
dbms_output.put_line(rec.numch);
end loop;
close c;
end;
10
Réponse à la question n° 5:
Code de la fonction CHERCHE_LIBRE :
create or replace function cherche_libre(nh number, catch number, d char, f
char) return integer is
dj number;
fj number;
v_libre boolean;
e exception;
v_resultat integer;
cursor c is select * from chambres where numh=nh and categorie=catch;
rec c %ROWTYPE;
begin
dj:=julienne(d);
fj:=julienne(f);
open c;
loop
fetch c into rec;
if c% notfound then
raise e;
end if;
v_libre:=est_libre(nh, rec.numch, dj, fj);
if v_libre then
v_resultat:= rec.numch;
return v_resultat;
end if;
end loop;
close c;
Exception
when e then
v_resultat:=0;
return v_resultat;
end cherche_libre;
Test de la fonction CHERCHE_LIBRE :
declare
v_resultat integer;
begin
v_resultat:=cherche_libre(1, 1, '06/05/08', '08/05/08');
dbms_output.put_line('la premiere chambre libre est: ' || v_resultat);
end;
11
Réponse à la question n° 6:
On tient également à créer deux séquences pour faciliter le traitement : l’une
sur la table reservations et l’autre sur la table demandes : leurs scripte de
creation sont les suivants:
create sequence seq_reserv start with 1 increment by 1;
create sequence seq_demande start with 1 increment by 1;
Code de la procédure RESERVER :
create or replace PROCEDURE reserver (ncli number, nom_hotel varchar, la_ville
varchar, catch number, nbp number, deb_res char, fin_res char, OK out boolean)
is
HOTEL_COMPLET Exception;
HOTEL_INCONNU Exception;
CURSOR c_hotel is select numh from hotels where nomh=nom_hotel and
ville=la_ville;
v_numh hotels.numh%type;
v_chambre chambres.numch%type;
begin
ok:=false;
open c_hotel;
fetch c_hotel into v_numh;
if c_hotel%NOTFOUND then
raise HOTEL_INCONNU;
end if;
close c_hotel;
v_chambre:=cherche_libre(v_numh,catch,deb_res,fin_res);
if v_chambre=0 then
raise HOTEL_COMPLET;
end if;
insert into reservations
values(seq_reserv.nextval,to_char(sysdate,'DD/MM/YY'),deb_res,fin_res,nbp,n
cli,v_numh,v_chambre);
insert into demandes values
(seq_demande.nextval,to_char(sysdate,'DD/MM/YY'),deb_res,fin_res,nbp,la_vil
le,0,ncli,catch,v_numh);
ok:=true;
close c_hotel;
Exception
when HOTEL_INCONNU then
12
close c_hotel;
DBMS_OUTPUT.PUT_LINE('HOTEL INCONNU');
when HOTEL_COMPLET then
insert into demandes values
(seq_demande.nextval,to_char(sysdate,'DD/MM/YY'),deb_res,fin_res,nbp,la_vil
le,1,ncli,catch,v_numh);
DBMS_OUTPUT.PUT_LINE('HOTEL COMPLET');
end reserver;
Test de la procédure RESERVER :
declare
ok boolean;
begin
reserver(2,'MOURADIE','SOUSSE',1,3,'05/02/08','07/02/08',ok);
end;
13
CONCLUSION : AVANTAGES DE PL-
SQL :
PL/SQL est le langage procédural d'Oracle. Il est une extension du SQL qui est
un langage ensembliste.
PL/SQL permet de gérer des traitements qui utilisent les instructions SQL dans
un langage procédural.
Les instructions de manipulation des données, de description des données, de
contrôle des transactions,
les fonctions SQL peuvent être utilisées avec la même syntaxe.
La gestion des variables et des structures de contrôle (tests, boucles) augmente
la capacité
de traitement des données
La gestion des curseurs et du traitement des erreurs accroît les possibilités de
traitement
Les instructions sont regroupées dans une unité appelée bloc qui ne génère qu'un
accès à la base
Les blocs ou procédures PL/SQL sont compilés et exécutés par le moteur
PL/SQL.
Ce moteur est intégré au moteur de la base de données et dans un certain
nombre d'outils (Forms, Report).
En résumé, PL/SQL permet de construire des applications.
14
