|
|
\documentclass[a4paper,11pt]{article}
|
|
|
|
|
|
\usepackage[utf8x]{inputenc}
|
|
|
\usepackage[T1]{fontenc}
|
|
|
\usepackage[french]{babel}
|
|
|
\usepackage[a4paper,hmargin=20mm,vmargin=30mm]{geometry}%\usepackage{fullpage}
|
|
|
\usepackage{url}
|
|
|
\usepackage{comment}
|
|
|
\usepackage{fancyhdr}% fancy header
|
|
|
|
|
|
\usepackage{fancyvrb}
|
|
|
|
|
|
\fancypagestyle{monstyle}{
|
|
|
%\fancyhead{}
|
|
|
\renewcommand{\headrulewidth}{1pt}
|
|
|
%% %\renewcommand{\footrulewidth}{0.4pt}
|
|
|
|
|
|
% \fancyhead[LE]{\slshape \thepage/ \pageref{LastPage}}
|
|
|
%% \fancyhead[RO]{\slshape \thepage/ \pageref{LastPage}}
|
|
|
|
|
|
|
|
|
%\fancyhf{}
|
|
|
%\fancyhead[LE]{\slshape LE}
|
|
|
%\fancyhead[CE]{\slshape CE}
|
|
|
%\fancyhead[RE]{\slshape RE}
|
|
|
|
|
|
\fancyhead[LO]{\bfseries 2020-2021 BD PLS/SQL\rightmark}
|
|
|
%\fancyhead[CO]{\slshape APF}
|
|
|
\fancyhead[RO]{\bfseries ~\leftmark }
|
|
|
|
|
|
%% %\fancyfoot{}
|
|
|
% \fancyfoot[LE,RO]{}
|
|
|
\fancyfoot[CO,CE]{}%\slshape\thepage/\pageref{LastPage}}
|
|
|
%% %\fancyfoot[LO,RE]{\small\slshape \ddmmyyyydate version du \today}
|
|
|
|
|
|
}
|
|
|
|
|
|
% \pagestyle{fancy}
|
|
|
|
|
|
\usepackage{amsmath}
|
|
|
\usepackage{amsthm}
|
|
|
|
|
|
|
|
|
|
|
|
\theoremstyle{definition}
|
|
|
\newtheorem{exercice}{Exercice}
|
|
|
|
|
|
\pagestyle{monstyle}
|
|
|
|
|
|
\usepackage{amsmath}
|
|
|
\usepackage{amsthm}
|
|
|
|
|
|
\newcommand{\code}[1]{\texttt{#1}}
|
|
|
|
|
|
\begin{document}
|
|
|
|
|
|
|
|
|
\begin{center}
|
|
|
TP3
|
|
|
\end{center}
|
|
|
|
|
|
|
|
|
\begin{exercice}[Suppression d’un client]
|
|
|
|
|
|
Information à saisir : n° client
|
|
|
|
|
|
Pr\'evoir les messages suivants :
|
|
|
\begin{itemize}
|
|
|
\item ‘client inconnu’
|
|
|
\item ‘des locations sont en cours : suppression impossible’ suivi de la liste des locations en cours
|
|
|
\item n 'locations retournées ont été supprimées’
|
|
|
\item ‘le client a bien été supprimé’
|
|
|
\end{itemize}
|
|
|
|
|
|
\end{exercice}
|
|
|
\begin{verbatim}
|
|
|
|
|
|
-- ORA-01422 : selct renvoit trop de choses
|
|
|
|
|
|
-- Test
|
|
|
-- C004
|
|
|
-- client inconnu
|
|
|
-- C001
|
|
|
-- 2 locations sont en cours : suppression impossible
|
|
|
-- VE001
|
|
|
-- VE002
|
|
|
-- C003
|
|
|
-- BUG
|
|
|
|
|
|
start locationvehic2017;
|
|
|
|
|
|
DROP TABLE tligne;
|
|
|
CREATE TABLE tligne (ligne varchar2(300));
|
|
|
|
|
|
set echo off; set verify off; set feed off;
|
|
|
|
|
|
variable vnoclient char(4);
|
|
|
prompt Entrer le numero du client :
|
|
|
accept vnoclient
|
|
|
|
|
|
declare
|
|
|
|
|
|
dmessage varchar2(200);
|
|
|
|
|
|
dnoveh Tlocation2017.noveh%TYPE;
|
|
|
dnoclientloc Tclient2017.noclient%TYPE;
|
|
|
dnbnoclient number;
|
|
|
dnblocation number;
|
|
|
dnblocationout number;
|
|
|
|
|
|
CURSOR r IS
|
|
|
select noveh
|
|
|
from Tlocation2017
|
|
|
where noclient='&vnoclient';
|
|
|
|
|
|
CURSOR loc IS
|
|
|
select noclient
|
|
|
from Tlocatretour2017
|
|
|
where noclient='&vnoclient'
|
|
|
FOR UPDATE;
|
|
|
|
|
|
begin
|
|
|
|
|
|
dmessage:='client inconnu';
|
|
|
|
|
|
select count(noclient) into dnbnoclient
|
|
|
from Tclient2017
|
|
|
where noclient='&vnoclient';
|
|
|
|
|
|
if dnbnoclient = 0 then insert into tligne values (dmessage);
|
|
|
|
|
|
else
|
|
|
|
|
|
select count(noclient) into dnblocation from Tlocation2017 where noclient='&vnoclient';
|
|
|
|
|
|
if dnblocation > 0 then
|
|
|
dmessage:=dnblocation||' locations sont en cours : suppression impossible';
|
|
|
insert into tligne values (dmessage);
|
|
|
open r;
|
|
|
fetch r into dnoveh;
|
|
|
while r%found
|
|
|
loop
|
|
|
insert into tligne values(dnoveh);
|
|
|
COMMIT;
|
|
|
fetch r into dnoveh;
|
|
|
end loop;
|
|
|
close r;
|
|
|
else
|
|
|
|
|
|
select count(noclient) into dnblocationout from Tlocatretour2017 where noclient='&vnoclient';
|
|
|
|
|
|
dmessage:=dnblocationout||' location retourns vont etre supprimees';
|
|
|
insert into tligne values (dmessage);
|
|
|
|
|
|
if dnblocationout=1 then
|
|
|
open loc;
|
|
|
fetch loc into dnoclientloc;
|
|
|
DELETE FROM Tlocatretour2017 WHERE CURRENT OF loc;
|
|
|
COMMIT;
|
|
|
|
|
|
|
|
|
close loc;
|
|
|
end if;
|
|
|
|
|
|
DELETE FROM Tclient2017 WHERE noclient='&vnoclient';
|
|
|
|
|
|
dmessage:='le client a bien ete supprime';
|
|
|
insert into tligne values (dmessage);
|
|
|
|
|
|
end if;
|
|
|
end if;
|
|
|
|
|
|
end;
|
|
|
.
|
|
|
/
|
|
|
|
|
|
select * from tligne;
|
|
|
set echo on; set verify on; set feed on;
|
|
|
\end{verbatim}
|
|
|
\begin{exercice}
|
|
|
Afficher la liste des véhicules libres de même catégorie ou de
|
|
|
catégorie équivalente que le véhicule n° XXXXX (n° saisi).
|
|
|
|
|
|
\begin{SaveVerbatim}{exo2}
|
|
|
|
|
|
Numéro véhicule : numéro catégorie
|
|
|
|
|
|
Catégorie libellé cat. num vehic. kms
|
|
|
XXXX X...X XX-9999-XX 9999
|
|
|
XXXX X...X XX-9999-XX 9999
|
|
|
XXXX X...X XX-9999-XX 9999
|
|
|
XXXX X...X XX-9999-XX 9999
|
|
|
|
|
|
\end{SaveVerbatim}
|
|
|
|
|
|
|
|
|
\begin{center}
|
|
|
\fbox{\BUseVerbatim{exo2}}
|
|
|
\end{center}
|
|
|
|
|
|
|
|
|
{\bf Cas d’erreur :}
|
|
|
\begin{itemize}
|
|
|
\item N° véhicule inconnu
|
|
|
\item Aucun véhicule libre de catégorie équivalente
|
|
|
\end{itemize}
|
|
|
\end{exercice}
|
|
|
\begin{verbatim}
|
|
|
start locationvehic2017;
|
|
|
commit;
|
|
|
set echo off; set verify off; set feed off;
|
|
|
|
|
|
variable vnoclient char(4);
|
|
|
prompt Entrer le numero du client :
|
|
|
accept vnoclient
|
|
|
|
|
|
declare
|
|
|
dno varchar(4);
|
|
|
dkmdeb number;
|
|
|
|
|
|
CURSOR loc IS
|
|
|
select noclient
|
|
|
from Tlocatretour2017
|
|
|
where noclient='&vnoclient'
|
|
|
FOR UPDATE of kmdeb;
|
|
|
|
|
|
begin
|
|
|
open loc;
|
|
|
|
|
|
fetch loc into dno;
|
|
|
while loc%found
|
|
|
loop
|
|
|
UPDATE Tlocatretour2017 SET kmdeb=7 WHERE CURRENT OF loc ;
|
|
|
fetch loc into dno;
|
|
|
end loop;
|
|
|
COMMIT;
|
|
|
close loc;
|
|
|
|
|
|
end;
|
|
|
.
|
|
|
/
|
|
|
|
|
|
select * from TLocatretour2017;
|
|
|
set echo on; set verify on; set feed on;
|
|
|
\end{verbatim}
|
|
|
|
|
|
\begin{exercice}Afficher la liste des locations retournées du mois :
|
|
|
|
|
|
\begin{SaveVerbatim}{exo3}
|
|
|
|
|
|
Locations retournées du mois
|
|
|
Catégorie : cat libellé
|
|
|
N° véhicule : XXXXX
|
|
|
N° client Date début Nb de jours Nb de kms
|
|
|
... ... ... ...
|
|
|
... ... ... ...
|
|
|
Total véhicule : xx jours
|
|
|
99999 kms
|
|
|
N° véhicule : XXXXX
|
|
|
N° client Date début Nb de jours Nb de kms
|
|
|
... ... ... ...
|
|
|
... ... ... ...
|
|
|
Total véhicule : xx jours
|
|
|
99999 kms
|
|
|
Catégorie : cat libellé
|
|
|
N° véhicule : XXXXX
|
|
|
N° client Date début Nb de jours Nb de kms
|
|
|
... ... ... ...
|
|
|
... ... ... ...
|
|
|
Total véhicule : xx jours
|
|
|
99999 kms
|
|
|
N° véhicule : XXXXX
|
|
|
N° client Date début Nb de jours Nb de kms
|
|
|
... ... ... ...
|
|
|
... ... ... ...
|
|
|
Total véhicule : xx jours
|
|
|
99999 kms
|
|
|
|
|
|
\end{SaveVerbatim}
|
|
|
|
|
|
\begin{center}
|
|
|
\begin{small}
|
|
|
\fbox{\BUseVerbatim{exo3}}
|
|
|
\end{small}
|
|
|
\end{center}
|
|
|
|
|
|
\end{exercice}
|
|
|
|
|
|
\begin{verbatim}
|
|
|
DROP TABLE tligne;
|
|
|
CREATE TABLE tligne (ligne varchar2(300));
|
|
|
|
|
|
set echo off; set verify off; set feed off;
|
|
|
|
|
|
declare
|
|
|
dmessage varchar2(300);
|
|
|
dnoclient varchar(4);
|
|
|
dnoveh char(5);
|
|
|
dkm number;
|
|
|
ddatedeb date;
|
|
|
dmois date;
|
|
|
dnbjours number;
|
|
|
dnbkms number;
|
|
|
dtotalkms number;
|
|
|
dtotaljours number:=0;
|
|
|
dcurrentnoveh char(5);
|
|
|
|
|
|
-- between ROUND(to_date(sysdate),'MONTH') and TRUNC(to_date(sysdate,'DD-MON-YY'),'MONTH')
|
|
|
|
|
|
CURSOR r IS
|
|
|
select noveh, noclient, datedeb, (dateretour - datedeb), (kmfin-kmdeb) into dnoveh, dnoclient, ddatedeb, dnbjours, dnbkms
|
|
|
from Tlocatretour2017
|
|
|
order by noveh;
|
|
|
|
|
|
begin
|
|
|
dmessage:='Aucune voiture louee';
|
|
|
|
|
|
open r;
|
|
|
fetch r into dnoveh, dnoclient, ddatedeb, dnbjours, dnbkms;
|
|
|
dcurrentnoveh:=' ';
|
|
|
dtotalkms:=dnbkms;
|
|
|
dtotaljours:=dnbjours;
|
|
|
while r%found
|
|
|
loop
|
|
|
if (dcurrentnoveh = dnoveh) then
|
|
|
insert into tligne values (dnoclient||' '||ddatedeb||' '||dnbjours||' '||dnbkms);
|
|
|
dtotalkms:=dtotalkms+dnbkms;
|
|
|
dtotaljours:=dtotaljours+dnbjours;
|
|
|
else
|
|
|
if dcurrentnoveh<>' ' then
|
|
|
insert into tligne values ('total jour: '||dtotaljours);
|
|
|
insert into tligne values ('total km: '||dtotalkms);
|
|
|
end if;
|
|
|
insert into tligne values (dnoveh);
|
|
|
insert into tligne values (dnoclient||' '||ddatedeb||' '||dnbjours||' '||dnbkms);
|
|
|
dtotalkms:=dnbkms;
|
|
|
dtotaljours:=dnbjours;
|
|
|
end if;
|
|
|
dcurrentnoveh:=dnoveh;
|
|
|
fetch r into dnoveh, dnoclient, ddatedeb, dnbjours, dnbkms;
|
|
|
end loop;
|
|
|
insert into tligne values ('total jour: '||dtotaljours);
|
|
|
insert into tligne values ('total km: '||dtotalkms);
|
|
|
close r;
|
|
|
|
|
|
exception
|
|
|
when no_data_found then insert into tligne values (dmessage);
|
|
|
end;
|
|
|
.
|
|
|
/
|
|
|
|
|
|
select * from tligne;
|
|
|
set echo on; set verify on; set feed on;
|
|
|
\end{verbatim}
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
\end{document}
|
