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.
42 lines
902 B
42 lines
902 B
drop table tresultat purge;
|
|
create table tresultat(ligne varchar2(200));
|
|
variable vnbRef number
|
|
prompt nombre de référence?
|
|
accept vnbRef
|
|
declare
|
|
dnbRef number:='&vnbRef';
|
|
didRep char(5);
|
|
dmessage varchar2(200);
|
|
dnbTot number;
|
|
inex exception;
|
|
cursor CUR is select I.idRep, sum(nbPlacesRes)
|
|
from inscription I, representation R
|
|
where I.idRep=R.idRep
|
|
and dateRep>sysdate
|
|
group by I.idRep
|
|
having sum(nbPlacesRes) > dnbRef
|
|
order by 2 desc;
|
|
begin
|
|
dmessage:='nombre de reference trop eleve';
|
|
insert into tresultat values('Id rep Nb total');
|
|
open CUR;
|
|
fetch CUR into didRep, dnbTot;
|
|
|
|
if CUR%notFound then raise inex;end if;
|
|
while CUR%FOUND
|
|
loop
|
|
insert into tresultat values (didRep||' '||dnbTot);
|
|
fetch CUR into didRep, dnbTot;
|
|
end loop;
|
|
close CUR;
|
|
exception
|
|
when inex then insert into tresultat values (dmessage);
|
|
end;
|
|
.
|
|
/
|
|
select * from tresultat;
|
|
|
|
|
|
|
|
|