--faudra tester la fct
CREATE OR REPLACE FUNCTION RECHERCHER
                  (ANNETUD IN Groupes.annetud%TYPE DEFAULT 2 , ANSCO IN Groupes.ansco%TYPE DEFAULT 2004 )
RETURN Groupes.refgroupe%TYPE
AS
  maxEtud FORMATIONS.MAXANNETUD%TYPE;
	RefGrpe Groupes.refgroupe%TYPE;
	ExcAnnetudNull EXCEPTION;
	ExcAnnetud EXCEPTION;
	ExcAnsco EXCEPTION;

BEGIN
  IF Rechercher.ANNETUD < 1
		then raise ExcAnnetudNull;

	IF Rechercher.ANNETUD > maxEtud
		then raise ExcAnnetud;

  IF Rechercher.ANSCO > (select EXTRACT (YEAR FROM current_date) from dual;)--pt-on remplacer la rqt par une variable?
		THEN RAISE ExcAnsco;

	SELECT refgroupe INTO Rechercher.refgroupe--RefGrpe
	FROM GROUPES
	WHERE refformdet = 'TECH-INDU'
	AND refimplan = 'INPS'
	AND ANNETUD = 2
	AND ANSCO = 2004
	AND refgroupe is not NULL
	group by refgroupe,refimplan,ansco,refformdet,annetud
	HAVING COUNT(*) = (select min(count(*))
					   from GROUPES
					   where refformdet = 'TECH-INDU'
					   AND refimplan = 'INPS'
					   AND ANNETUD = 2
				       AND ANSCO = 2004
			           AND refgroupe is not NULL
					   group by refgroupe,refimplan,ansco,refformdet,annetud);
  RETURN RefGrpe;
EXCEPTION--<<<<----
	WHEN ExcAnnetudNull
		THEN RAISE_APPLICATION_ERROR(-20055,'L annee d etude ' || Rechercher.ANNETUD || ' est NULL ou negative');
	WHEN ExcAnnetud
		THEN RAISE_APPLICATION_ERROR(-20100,'L annee d etude ' || Rechercher.ANNETUD || 'est superieure au max ' || maxEtud);
	WHEN ExcAnsco
		THEN RAISE_APPLICATION_ERROR(-20066,'L annee scolaire ' || Rechercher.ANSCO || ' est superieure a l annee en cours' || select EXTRACT (YEAR FROM current_date) from dual;);
	WHEN NO_DATA_FOUND
		THEN RAISE_APPLICATION_ERROR(100,'Pas de groupe ' || RefGrpe || ' trouve');
	WHEN TOO_MANY_ROWS
		THEN RAISE_APPLICATION_ERROR(-1422,'Il y a plusieurs groupes ayant le meme nb d etudiants min');

END RECHERCHER;
/