-- Ecrire la procédure qui permet de créer un nouveau producteur. Ce nouveau producteur devra avoir
-- un couple (nom, prenom) différent de tous les couples existants. Cette procédure devra afficher un
-- message pour préciser si l'opération s'est bien déroulée.

CREATE OR REPLACE PROCEDURE creer_producteur(pnom varchar2, pprenom
varchar2, pregion varchar2) is
cpt number;
BEGIN
if pnom is null then
dbms_output.put_line('Veuillez donner le nom du producteur');
elsif pprenom is null then
dbms_output.put_line('Veuillez donner le prénom du producteur');
elsif pregion is null then
dbms_output.put_line('Veuillez donner la région du producteur');
else
SELECT count(*) INTO cpt FROM producteur
 WHERE nom = pnom and prenom = pprenom;
IF cpt > 0 THEN
dbms_output.put_line('Le couple (' || pnom || ',' || pprenom || ') existe déjà');
ELSE
INSERT INTO Producteur(nom,prenom,region)
VALUES(pnom,pprenom,pregion);
dbms_output.put_line('Insertion réussie');
END IF;
END IF;
END creer_producteur;
/

-- Reprendre la question précédente en traitant les erreurs ....

CREATE OR REPLACE PROCEDURE creer_producteur2(pnom varchar2, pprenom
varchar2, pregion varchar2) is
cpt number;
err_sans_nom exception;
err_sans_prn exception;
err_doublon exception;
BEGIN
if pnom is null then
 raise err_sans_nom;
elsif pprenom is null then
 raise err_sans_prn;
elsif pregion is null then
dbms_output.put_line('Veuillez donner la région du producteur');
else
SELECT count(*) INTO cpt FROM producteur
 WHERE nom = pnom and prenom = pprenom;
IF cpt > 0 THEN
dbms_output.put_line('Le couple (' || pnom || ',' || pprenom || ') existe déjà');
ELSE
INSERT INTO Producteur(nom,prenom,region)
VALUES(pnom,pprenom,pregion);
dbms_output.put_line('Insertion réussie');
END IF;
END IF;
EXCEPTION
when err_sans_nom then
 dbms_output.put_line('Veuillez donner le nom du producteur');
when err_sans_prn then
 dbms_output.put_line('Veuillez donner le prénom du producteur');
END creer_producteur2;
/

-- Ecrire une procédure nommée reporting_recoltes qui reçoit le nom et le prénom d'un producteur et qui affiche :
--  * Sa région de production et la quantité totale produite
--   * Un tableau des récoltes classées par type de vin

CREATE OR REPLACE PROCEDURE reporting_recoltes(pnom varchar2, pprenom varchar2) is
vregion producteur.region%type;
cursor cur_recoltes is
  select * from recolte
  WHERE numprod in (select numprod from producteur
  WHERE nom = pnom and prenom = pprenom)
  order by numvin;
vnumvin vin.numvin%type;
cpt number;
BEGIN
  if pnom is null then
    dbms_output.put_line('Veuillez donner le nom du producteur');
  elsif pprenom is null then
    dbms_output.put_line('Veuillez donner le prénom du producteur');
  else
    SELECT region INTO vregion FROM producteur
    WHERE nom = pnom and prenom = pprenom;
      dbms_output.put_line('La région du producteur (' || pnom || ',' || pprenom || ') est ' ||vregion);
      vnumvin := 'bad';
      for enr_recoltes in cur_recoltes
        loop
          if vnumvin <> enr_recoltes.numvin then
            dbms_output.put_line('Recoltes de ' || enr_recoltes.numvin);
            cpt := 1;
          end if;
          vnumvin := enr_recoltes.numvin;
          dbms_output.put_line('Recolte ' || cpt || ' : ' || enr_recoltes.quantite || 'bouteille(s)');
          cpt := cpt + 1;
        end loop;
  END IF;
END reporting_recoltes;
/
-- Ecrire une fonction qui reçoit l'identifiant d'un vin et qui renvoie une chaîne de caractères contenant
-- les caractéristiques de ce vin.

CREATE or replace function Carac_Vin(pnumvin varchar2) return varchar2 is
  cpt number;
  res varchar2(50);
  begin
    select count(*) into cpt from vin WHERE numvin = pnumvin;
	if cpt = 0 then
     res := 'vin inconnu';
    else
     select 'Cru : ' || cru || ' -- degré : ' || degre || ' -- annee : ' || annee into res 
	 from vin WHERE numvin = pnumvin;
    end if;
	return res;
end Carac_Vin;
/

-- pour tester on crée une procédure qui appelle cette fonction ou bien  :

select Carac_Vin('v1') from dual;

-- Réécrire la procédure reporting_recoltes qui appelle la fonction précédente pour une meilleure
-- présentation du reporting des récoltes.

CREATE OR REPLACE PROCEDURE reporting_recoltes2(pnom varchar2, pprenom
varchar2) is
vregion producteur.region%type;
cursor cur_recoltes is
select * from recolte
WHERE numprod in (select numprod from producteur
 WHERE nom = pnom and prenom = pprenom)
order by numvin;
vnumvin vin.numvin%type;
cpt number;
BEGIN
if pnom is null then
 dbms_output.put_line('Veuillez donner le nom du producteur');
elsif pprenom is null then
 dbms_output.put_line('Veuillez donner le prénom du producteur');
else
 SELECT region INTO vregion FROM producteur
 WHERE nom = pnom and prenom = pprenom;
 dbms_output.put_line('La région du producteur (' || pnom || ',' || pprenom || ') est ' ||
vregion);
 vnumvin := 'bad';
 
 for enr_recoltes in cur_recoltes
 loop
  if vnumvin <> enr_recoltes.numvin then
   dbms_output.put_line('Recoltes du ' || carac_vin(enr_recoltes.numvin));
   cpt := 1;
  end if;
  vnumvin := enr_recoltes.numvin;
  dbms_output.put_line('Recolte ' || cpt || ' : ' || enr_recoltes.quantite || '
bouteille(s)');
  cpt := cpt + 1;
 end loop;  
END IF;
END reporting_recoltes2;
/

--Dans le cadre d'une utilisation plus globale d'une gestion de l'agriculture, on vous demande de créer
--un package nommé pck_prod_vin qui regroupe ces procédures et fonctions.

CREATE OR REPLACE PACKAGE TP2 AS
PROCEDURE creer_producteur(pnom varchar2, pprenom varchar2, pregion varchar2);
PROCEDURE creer_producteur2(pnom varchar2, pprenom varchar2, pregion varchar2);
PROCEDURE reporting_recoltes(pnom varchar2, pprenom varchar2);
PROCEDURE reporting_recoltes2(pnom varchar2, pprenom varchar2);
FUNCTION Carac_Vin(pnumvin varchar2) RETURN varchar2;
END TP2;
/

CREATE OR REPLACE PACKAGE BODY TP2 AS
PROCEDURE creer_producteur(pnom varchar2, pprenom
varchar2, pregion varchar2) is
cpt number;
BEGIN
if pnom is null then
dbms_output.put_line('Veuillez donner le nom du producteur');
elsif pprenom is null then
dbms_output.put_line('Veuillez donner le prénom du producteur');
elsif pregion is null then
dbms_output.put_line('Veuillez donner la région du producteur');
else
SELECT count(*) INTO cpt FROM producteur
 WHERE nom = pnom and prenom = pprenom;
IF cpt > 0 THEN
dbms_output.put_line('Le couple (' || pnom || ',' || pprenom || ') existe déjà');
ELSE
INSERT INTO Producteur(nom,prenom,region)
VALUES(pnom,pprenom,pregion);
dbms_output.put_line('Insertion réussie');
END IF;
END IF;
END creer_producteur;

PROCEDURE creer_producteur2(pnom varchar2, pprenom
varchar2, pregion varchar2) is
cpt number;
err_sans_nom exception;
err_sans_prn exception;
err_doublon exception;
BEGIN
if pnom is null then
 raise err_sans_nom;
elsif pprenom is null then
 raise err_sans_prn;
elsif pregion is null then
dbms_output.put_line('Veuillez donner la région du producteur');
else
SELECT count(*) INTO cpt FROM producteur
 WHERE nom = pnom and prenom = pprenom;
IF cpt > 0 THEN
dbms_output.put_line('Le couple (' || pnom || ',' || pprenom || ') existe déjà');
ELSE
INSERT INTO Producteur(nom,prenom,region)
VALUES(pnom,pprenom,pregion);
dbms_output.put_line('Insertion réussie');
END IF;
END IF;
EXCEPTION
when err_sans_nom then
 dbms_output.put_line('Veuillez donner le nom du producteur');
when err_sans_prn then
 dbms_output.put_line('Veuillez donner le prénom du producteur');
END creer_producteur2;

PROCEDURE reporting_recoltes(pnom varchar2, pprenom varchar2) is
vregion producteur.region%type;
cursor cur_recoltes is
  select * from recolte
  WHERE numprod in (select numprod from producteur
  WHERE nom = pnom and prenom = pprenom)
  order by numvin;
vnumvin vin.numvin%type;
cpt number;
BEGIN
  if pnom is null then
    dbms_output.put_line('Veuillez donner le nom du producteur');
  elsif pprenom is null then
    dbms_output.put_line('Veuillez donner le prénom du producteur');
  else
    SELECT region INTO vregion FROM producteur
    WHERE nom = pnom and prenom = pprenom;
      dbms_output.put_line('La région du producteur (' || pnom || ',' || pprenom || ') est ' ||vregion);
      vnumvin := 'bad';
      for enr_recoltes in cur_recoltes
        loop
          if vnumvin <> enr_recoltes.numvin then
            dbms_output.put_line('Recoltes de ' || enr_recoltes.numvin);
            cpt := 1;
          end if;
          vnumvin := enr_recoltes.numvin;
          dbms_output.put_line('Recolte ' || cpt || ' : ' || enr_recoltes.quantite || 'bouteille(s)');
          cpt := cpt + 1;
        end loop;
  END IF;
END reporting_recoltes;

PROCEDURE reporting_recoltes2(pnom varchar2, pprenom
varchar2) is
vregion producteur.region%type;
cursor cur_recoltes is
select * from recolte
WHERE numprod in (select numprod from producteur
 WHERE nom = pnom and prenom = pprenom)
order by numvin;
vnumvin vin.numvin%type;
cpt number;
BEGIN
if pnom is null then
 dbms_output.put_line('Veuillez donner le nom du producteur');
elsif pprenom is null then
 dbms_output.put_line('Veuillez donner le prénom du producteur');
else
 SELECT region INTO vregion FROM producteur
 WHERE nom = pnom and prenom = pprenom;
 dbms_output.put_line('La région du producteur (' || pnom || ',' || pprenom || ') est ' ||
vregion);
 vnumvin := 'bad';
 
 for enr_recoltes in cur_recoltes
 loop
  if vnumvin <> enr_recoltes.numvin then
   dbms_output.put_line('Recoltes du ' || carac_vin(enr_recoltes.numvin));
   cpt := 1;
  end if;
  vnumvin := enr_recoltes.numvin;
  dbms_output.put_line('Recolte ' || cpt || ' : ' || enr_recoltes.quantite || '
bouteille(s)');
  cpt := cpt + 1;
 end loop;  
END IF;
END reporting_recoltes2;

function Carac_Vin(pnumvin varchar2) return varchar2 is
  cpt number;
  res varchar2(50);
  begin
    select count(*) into cpt from vin WHERE numvin = pnumvin;
	if cpt = 0 then
     res := 'vin inconnu';
    else
     select 'Cru : ' || cru || ' -- degré : ' || degre || ' -- annee : ' || annee into res 
	 from vin WHERE numvin = pnumvin;
    end if;
	return res;
end Carac_Vin;




END TP2;
/