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.
160 lines
2.8 KiB
160 lines
2.8 KiB
-- Exercice 1
|
|
DROP TABLE tligne;
|
|
CREATE TABLE tligne (ligne varchar2(100));
|
|
|
|
set echo off;
|
|
set verify off;
|
|
set feed off;
|
|
|
|
variable vnumvehicule char(5)
|
|
variable toto char(5)
|
|
|
|
prompt Entrer le numero du vehicule :
|
|
|
|
accept vnumvehicule
|
|
|
|
declare
|
|
|
|
dimmatriculation char(10);
|
|
dmodele varchar2(30);
|
|
|
|
begin
|
|
|
|
select immat, modele into dimmatriculation, dmodele
|
|
from Tvehicule2017
|
|
where noveh='&vnumvehicule';
|
|
|
|
Insert into tligne values (dimmatriculation||dmodele);
|
|
|
|
exception
|
|
when no_data_found then
|
|
insert into tligne values ('&vnumvehicule'||'de vehicule inconnu');
|
|
end;
|
|
.
|
|
/
|
|
select * from tligne;
|
|
|
|
set echo on;
|
|
set verify on;
|
|
set feed on;
|
|
|
|
-- Exercice 2
|
|
|
|
DROP TABLE tligne;
|
|
CREATE TABLE tligne (ligne varchar2(100));
|
|
|
|
set echo off;
|
|
set verify off;
|
|
set feed off;
|
|
|
|
variable vnoclient char(4)
|
|
|
|
prompt Entrer le numero du client :
|
|
accept vnoclient
|
|
|
|
declare
|
|
dnovehicule char(5);
|
|
|
|
BEGIN
|
|
|
|
select noveh into dnovehicule
|
|
from Tlocation2017
|
|
where noclient='&vnoclient';
|
|
|
|
Insert into tligne values ('vehicule '||dnovehicule||' loue par '||'&vnoclient');
|
|
|
|
exception
|
|
when no_data_found then
|
|
insert into tligne values ('aucune location pour '||'&vnoclient');
|
|
|
|
when too_many_rows then
|
|
insert into tligne values ('plusieurs locations pour '||'&vnoclient');
|
|
|
|
end;
|
|
.
|
|
/
|
|
select * from tligne;
|
|
|
|
set echo on;
|
|
set verify on;
|
|
set feed on;
|
|
|
|
-- Exercice 3
|
|
|
|
DROP TABLE tligne;
|
|
CREATE TABLE tligne (ligne varchar2(100));
|
|
|
|
-- set echo off;
|
|
-- set verify off;
|
|
-- set feed off;
|
|
|
|
variable vnovehicule char(5);
|
|
variable vdateretour date;
|
|
variable vkmfin number;
|
|
|
|
prompt Entrer le numero du vehicule :
|
|
accept vnovehicule
|
|
|
|
prompt Entrer la date de retour :
|
|
accept vdateretour
|
|
|
|
prompt Entrer le kilometrage de fin :
|
|
accept vkmfin
|
|
|
|
declare
|
|
|
|
dnovehicule char(5);
|
|
ddatedebut date;
|
|
dkmdeb number;
|
|
dnoclient char(4);
|
|
|
|
dmessage varchar2(100);
|
|
|
|
begin
|
|
|
|
dmessage:='numero de vehicule inconnu';
|
|
|
|
select noveh into dnovehicule
|
|
from Tvehicule2017
|
|
where noveh='&vnovehicule';
|
|
|
|
dmessage:='Vehicule pas en cours de location';
|
|
|
|
select noclient into dnoclient
|
|
from Tlocation2017
|
|
where noveh='&vnovehicule';
|
|
|
|
select datedeb, kmdeb, noclient into ddatedebut, dkmdeb, dnoclient
|
|
from Tlocation2017
|
|
where noveh='&vnovehicule';
|
|
|
|
if ddatedebut > &vdateretour
|
|
then Insert into tligne values ('PB date de retour inf date debut');
|
|
end if;
|
|
|
|
if dkmdeb > &vkmfin
|
|
then Insert into tligne values ('PB Km de retour inf KM debut');
|
|
end if;
|
|
|
|
-- Insert into Tlocatretour2017 values (dnoclient,'&vnovehicule',ddatedebut,dkmdeb,&vkmfin,&vdateretour);
|
|
|
|
Insert into Tlocatretour2017 values (dnoclient,'&vnovehicule',ddatedebut,dkmdeb,&vkmfin,to_date('15-12-2016','DD-MM-YYYY'));
|
|
|
|
Insert into tligne values ('retour ok');
|
|
|
|
exception
|
|
when no_data_found then insert into tligne values (dmessage);
|
|
|
|
end;
|
|
|
|
.
|
|
/
|
|
|
|
select * from tligne;
|
|
|
|
-- set echo on;
|
|
-- set verify on;
|
|
-- set feed on;
|
|
|
|
|