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.
54 lines
1.4 KiB
54 lines
1.4 KiB
drop table tresultat purge;
|
|
create table tresultat(ligne varchar2(200));
|
|
|
|
variable vidterrain char(2);
|
|
prompt id terrain?
|
|
accept vidterrain
|
|
|
|
declare
|
|
didterrain char(2):='&vidterrain';
|
|
didReserv char(3);
|
|
ddateReserv date;
|
|
dmessage varchar2(200);
|
|
inexistant exception;
|
|
probleme exception;
|
|
dcpt number;
|
|
dcptReserv number;
|
|
begin
|
|
|
|
dmessage:='terrain inconnu';
|
|
select count(*) into dcpt
|
|
from terrain
|
|
where idterrain=didterrain;
|
|
if dcpt=0
|
|
then raise inexistant;
|
|
else select count(*) into dcptReserv
|
|
from terrain T, reservation R
|
|
where T.idterrain=R.idterrain
|
|
and R.dateReserv >= sysdate;
|
|
dmessage:='Impossible, il existe des réservations faites par ce terrain pour aujourd''hui ou dans le futur';
|
|
if dcptReserv>0
|
|
then raise probleme;
|
|
else select count(*) into dcptReserv
|
|
from terrain T, reservation R
|
|
where T.idterrain=R.idterrain
|
|
and R.dateReserv < sysdate;
|
|
if dcptReserv>0
|
|
then delete from reservation
|
|
where idterrain=didterrain;
|
|
insert into tresultat values(to_char(dcptReserv)||' action(s) de réservations faites dans le passé par ce terrain ont été supprimées');
|
|
end if;
|
|
delete from terrain where idterrain=didterrain;
|
|
insert into tresultat values('terrain supprimé');
|
|
commit;
|
|
end if;
|
|
end if;
|
|
exception
|
|
when inexistant then insert into tresultat values (dmessage);
|
|
when probleme then insert into tresultat values (dmessage);
|
|
end;
|
|
.
|
|
/
|
|
select * from tresultat;
|
|
|