|
|
-- Exercice 1
|
|
|
-- Afficher la liste des véhicules libres d’une catégorie dont on saisit le numéro
|
|
|
-- ORA-01001: verifier que le curseur est ouver
|
|
|
|
|
|
-- TEST
|
|
|
-- CAT3
|
|
|
-- VE005 ff-6000-za noire Picasso
|
|
|
|
|
|
-- CAT2
|
|
|
-- Pas de vehicule libre dans cette categorie
|
|
|
|
|
|
-- CAT7
|
|
|
-- Pas de vehicule libre dans cette categorie
|
|
|
|
|
|
DROP TABLE tligne;
|
|
|
CREATE TABLE tligne (ligne varchar2(300));
|
|
|
|
|
|
set echo off;
|
|
|
set verify off;
|
|
|
set feed off;
|
|
|
|
|
|
variable vcategorie char(4);
|
|
|
prompt Entrer le numero de la categorie :
|
|
|
accept vnocategorie
|
|
|
|
|
|
declare
|
|
|
|
|
|
dimmatriculation Tvehicule2017.immat%TYPE;
|
|
|
dnoveh Tvehicule2017.noveh%TYPE;
|
|
|
dcouleur Tvehicule2017.couleur%TYPE;
|
|
|
dmodele Tvehicule2017.modele%TYPE;
|
|
|
dcat number;
|
|
|
|
|
|
cursor r is select noveh, immat, couleur, modele from Tvehicule2017 where nocat='&vnocategorie' and noveh not in (select noveh from Tlocation2017) order by noveh;
|
|
|
|
|
|
begin
|
|
|
|
|
|
select noveh, immat, couleur, modele into dnoveh, dimmatriculation, dcouleur, dmodele from Tvehicule2017 where nocat='&vnocategorie' and noveh not in (select noveh from Tlocation2017) order by noveh;
|
|
|
|
|
|
select count(nocat) into dcat from Tcategorie2017 where nocat='&vnocategorie';
|
|
|
|
|
|
if dcat = 0 then insert into tligne values ('categorie inconnu');
|
|
|
end if;
|
|
|
|
|
|
open r;
|
|
|
fetch r into dnoveh, dimmatriculation, dcouleur, dmodele;
|
|
|
|
|
|
while r%found
|
|
|
loop
|
|
|
insert into tligne values ( dnoveh ||' '||dimmatriculation||' '||dcouleur||' '||dmodele);
|
|
|
fetch r into dnoveh, dimmatriculation, dcouleur, dmodele;
|
|
|
end loop;
|
|
|
close r;
|
|
|
|
|
|
exception
|
|
|
when no_data_found then
|
|
|
insert into tligne values ('Pas de vehicule libre dans cette categorie');
|
|
|
end;
|
|
|
.
|
|
|
/
|
|
|
select * from tligne;
|
|
|
|
|
|
set echo on;
|
|
|
set verify on;
|
|
|
set feed on;
|
