You can not select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
63 lines
1.3 KiB
63 lines
1.3 KiB
-- Exercice 2
|
|
-- TEST C001
|
|
|
|
-- VE001 VE001 30-JAN-17 clio 3
|
|
-- VE002 VE002 28-JAN-17 308
|
|
-- Nombre de locations en cours pour le client C001 est de 2
|
|
|
|
DROP TABLE tligne;
|
|
CREATE TABLE tligne (ligne varchar2(200));
|
|
|
|
set echo off;
|
|
set verify off;
|
|
set feed off;
|
|
|
|
variable vclient char(4);
|
|
|
|
prompt Entrer le numero du client:
|
|
accept vnoclient
|
|
|
|
declare
|
|
dnocat Tvehicule2017.nocat%TYPE;
|
|
dnoveh Tlocation2017.noveh%TYPE;
|
|
ddatedeb Tlocation2017.datedeb%TYPE;
|
|
dmodele Tvehicule2017.modele%TYPE;
|
|
dnombreloc number;
|
|
|
|
cursor r is
|
|
select nocat, Tlocation2017.noveh, datedeb, modele
|
|
from Tlocation2017, Tvehicule2017
|
|
where noclient='&vnoclient' and Tlocation2017.noveh= Tvehicule2017.noveh
|
|
order by nocat, noveh;
|
|
|
|
begin
|
|
open r;
|
|
|
|
fetch r into dnocat, dnoveh, ddatedeb, dmodele;
|
|
while r%found
|
|
loop
|
|
insert into tligne values (dnoveh||' '||dnoveh||' '||ddatedeb||' '||dmodele);
|
|
fetch r into dnocat, dnoveh, ddatedeb, dmodele;
|
|
end loop;
|
|
close r;
|
|
|
|
select count(noclient) into dnombreloc
|
|
from Tlocation2017
|
|
where noclient='&vnoclient';
|
|
|
|
insert into tligne values ('Nombre de locations en cours pour le client '||'&noclient'||' est de '||dnombreloc);
|
|
|
|
exception
|
|
when no_data_found then
|
|
insert into tligne values ('Pas de vehicule libre');
|
|
|
|
end;
|
|
.
|
|
/
|
|
|
|
select * from tligne;
|
|
|
|
set echo on;
|
|
set verify on;
|
|
set feed on;
|