-----------------------------------------------------------------------------------------------------
-- Correction du TD2 sur les fonctions et procédures
-----------------------------------------------------------------------------------------------------
-- ATTENTION !!! : ce fichier n'est pas à exécuter directement, mais au fur et à mesure des questions
-----------------------------------------------------------------------------------------------------


-- 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
	-- Gestion des erreurs de parametres
	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	-- Insertion si le producteur n'existe pas
		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;
/

-- Test de la procedure
EXEC creer_producteur('', '', ''); -- Manque le nom
EXEC creer_producteur('Saint Emilion', '', ''); -- Manque le prenom
EXEC creer_producteur('Saint Emilion', 'Charles', ''); -- Manque la region
EXEC creer_producteur('Saint Emilion', 'Charles', 'Bordeaux'); -- Valide
EXEC creer_producteur('Saint Emilion', 'Charles', 'Bordeaux'); -- Erreur de duplication

-- 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;	-- Definition des exceptions
	err_sans_prn exception;
	err_sans_rgn exception;
	err_doublon exception;
BEGIN
	-- Gestion des erreurs de parametres par exception
	IF pnom is null THEN
		raise err_sans_nom;
	end if;
	IF pprenom is null THEN
		raise err_sans_prn;
	end if;
	IF pregion is null THEN
		raise err_sans_rgn;
	end if;
	SELECT count(*) INTO cpt FROM producteur
	WHERE nom = pnom and prenom = pprenom;
	IF cpt > 0 THEN
		raise err_doublon;
	ELSE
		INSERT INTO Producteur(nom,prenom,region) VALUES (pnom,pprenom,pregion);
		dbms_output.put_line('Insertion reussie');
	END IF;
EXCEPTION	-- Traitement des exceptions
	when err_sans_nom THEN
		raise_application_error(-20001,'erreur parametre nom');
	when err_sans_prn THEN
		raise_application_error(-20002,'erreur parametre prenom');
	when err_sans_rgn THEN
		raise_application_error(-20003,'erreur parametre region');
	when err_doublon THEN
		raise_application_error(-20004,'erreur (nom,prenom) deja existant');
END creer_producteur2;
/

-- Test de la procedure
EXEC creer_producteur2('', '', ''); -- Manque le nom
EXEC creer_producteur2('Montbazillac', '', ''); -- Manque le prenom
EXEC creer_producteur2('Montbazillac', 'Louis', ''); -- Manque la region
EXEC creer_producteur2('Montbazillac', 'Louis', 'Bergerac'); -- Valide
EXEC creer_producteur2('Montbazillac', 'Louis', 'Bergerac'); -- Erreur de duplication

-- Modification de la table recolte impossible car la colonne ajoutée ne peut pas être not null
-- et doit faire partie de la clé primaire
-- Donc soit on ajoute la colonne no_recolte sans que ce soit not null (mauvaise solution) soit
-- on crée une autre table recolte 2 dans laquelle on copie le contenu de recolte et on inséère le champ
-- no_recolte avec pour valeur 1 (il n'y a qu'une récolte avant).
-- C'est cette dernière solution que nous avons choisie
-- Attention

CREATE TABLE Recolte2 (
	numvin varchar(5),
	numprod varchar(5),
	no_recolte number(2),
	quantite number(6,2),
	constraint pk_recolte2 primary key(numvin,numprod, no_recolte)
);

CREATE OR REPLACE PROCEDURE copier_recoltes
IS
	CURSOR cur_recolte IS
		SELECT * FROM recolte;
BEGIN
	FOR enr_recolte IN cur_recolte
    LOOP	-- insertion des enregistrements dans recolte2 avec no_recolte = 1
		INSERT INTO recolte2 VALUES
			(enr_recolte.numvin, enr_recolte.numprod, 1, enr_recolte.quantite);
    END LOOP;
END copier_recoltes;
/

-- Execution
EXEC copier_recolte;

-- 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 recolte2 	-- curseur de recuperation de la recolte
		WHERE numprod IN (SELECT numprod FROM producteur
						  WHERE nom = pnom AND prenom = pprenom)
		ORDER BY numvin;
	vnumvin vin.numvin%type;
	cpt number;
	err_sans_nom exception;	-- Definition des exceptions
	err_sans_prn exception;
BEGIN
	-- Gestion d'erreurs des paramètres
	IF pnom is null THEN
		raise err_sans_nom;
	END IF;
	IF pprenom is null THEN
		raise err_sans_prn;
	END IF;
	SELECT region INTO vregion FROM producteur -- recupératon de la region
	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;
EXCEPTION	-- Traitement des exceptions
	WHEN err_sans_nom THEN
		raise_application_error(-20001,'erreur parametre nom');
	WHEN err_sans_prn THEN
		raise_application_error(-20002,'erreur parametre prenom');
	WHEN no_data_found THEN
		raise_application_error(-20003,'erreur producteur inexistant');
END reporting_recoltes;
/

-- Test
EXEC reporting_recoltes('', ''); -- Manque nom
EXEC reporting_recoltes('Rotchild', ''); -- Manque prenom
EXEC reporting_recoltes('Rotchild', 'Alfred'); -- producteur inexistant
EXEC reporting_recoltes('Rotchild', 'James'); -- Valide

-- 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	-- curseur de recuperation de la recolte
		SELECT * FROM recolte2
		WHERE numprod IN (SELECT numprod FROM producteur
						  WHERE nom = pnom AND prenom = pprenom)
		ORDER BY numvin;
	vnumvin vin.numvin%type;
	cpt number;
	err_sans_nom exception;	-- Definition des exceptions
	err_sans_prn exception;
BEGIN
	IF pnom is null THEN
		raise err_sans_nom;
	END IF;
	IF pprenom is null THEN
		raise err_sans_prn;
	END IF;
	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;
EXCEPTION	-- Traitement des exceptions
	WHEN err_sans_nom THEN
		raise_application_error(-20001,'erreur parametre nom');
	WHEN err_sans_prn THEN
		raise_application_error(-20002,'erreur parametre prenom');
	WHEN no_data_found THEN
		raise_application_error(-20003,'erreur producteur inexistant');
END reporting_recoltes2;
/

-- Test
EXEC reporting_recoltes('', ''); -- Manque nom
EXEC reporting_recoltes('Rotchild', ''); -- Manque prenom
EXEC reporting_recoltes('Rotchild', 'Alfred'); -- producteur inexistant
EXEC reporting_recoltes('Rotchild', 'James'); -- Valide

--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

	CREATE OR REPLACE PROCEDURE creer_producteur(pnom varchar2, pprenom varchar2, pregion varchar2)
	IS
		cpt number;
	BEGIN
		-- Gestion des erreurs de parametres
		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	-- Insertion si le producteur n'existe pas
			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;

	CREATE OR REPLACE PROCEDURE creer_producteur2(pnom varchar2, pprenom varchar2, pregion varchar2)
	IS
		cpt number;
		err_sans_nom exception;	-- Definition des exceptions
		err_sans_prn exception;
		err_sans_rgn exception;
		err_doublon exception;
	BEGIN
		-- Gestion des erreurs de parametres par exception
		IF pnom is null THEN
			raise err_sans_nom;
		end if;
		IF pprenom is null THEN
			raise err_sans_prn;
		end if;
		IF pregion is null THEN
			raise err_sans_rgn;
		end if;
		SELECT count(*) INTO cpt FROM producteur
		WHERE nom = pnom and prenom = pprenom;
		IF cpt > 0 THEN
			raise err_doublon;
		ELSE
			INSERT INTO Producteur(nom,prenom,region) VALUES (pnom,pprenom,pregion);
			dbms_output.put_line('Insertion reussie');
		END IF;
	EXCEPTION	-- Traitement des exceptions
		when err_sans_nom THEN
			raise_application_error(-20001,'erreur parametre nom');
		when err_sans_prn THEN
			raise_application_error(-20002,'erreur parametre prenom');
		when err_sans_rgn THEN
			raise_application_error(-20003,'erreur parametre region');
		when err_doublon THEN
			raise_application_error(-20004,'erreur (nom,prenom) deja existant');
	END creer_producteur2;

	CREATE OR REPLACE PROCEDURE reporting_recoltes(pnom varchar2, pprenom varchar2)
	IS
		vregion producteur.region%type;
		CURSOR cur_recoltes IS
			SELECT * FROM recolte2 	-- curseur de recuperation de la recolte
			WHERE numprod IN (SELECT numprod FROM producteur
							  WHERE nom = pnom AND prenom = pprenom)
			ORDER BY numvin;
		vnumvin vin.numvin%type;
		cpt number;
		err_sans_nom exception;	-- Definition des exceptions
		err_sans_prn exception;
	BEGIN
		-- Gestion d'erreurs des paramètres
		IF pnom is null THEN
			raise err_sans_nom;
		END IF;
		IF pprenom is null THEN
			raise err_sans_prn;
		END IF;
		SELECT region INTO vregion FROM producteur -- recupératon de la region
		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;
	EXCEPTION	-- Traitement des exceptions
		WHEN err_sans_nom THEN
			raise_application_error(-20001,'erreur parametre nom');
		WHEN err_sans_prn THEN
			raise_application_error(-20002,'erreur parametre prenom');
		WHEN no_data_found THEN
			raise_application_error(-20003,'erreur producteur inexistant');
	END reporting_recoltes;

	CREATE OR REPLACE PROCEDURE reporting_recoltes2(pnom varchar2, pprenom varchar2)
	IS
		vregion producteur.region%type;
		CURSOR cur_recoltes IS	-- curseur de recuperation de la recolte
			SELECT * FROM recolte2
			WHERE numprod IN (SELECT numprod FROM producteur
							  WHERE nom = pnom AND prenom = pprenom)
			ORDER BY numvin;
		vnumvin vin.numvin%type;
		cpt number;
		err_sans_nom exception;	-- Definition des exceptions
		err_sans_prn exception;
	BEGIN
		IF pnom is null THEN
			raise err_sans_nom;
		END IF;
		IF pprenom is null THEN
			raise err_sans_prn;
		END IF;
		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;
	EXCEPTION	-- Traitement des exceptions
		WHEN err_sans_nom THEN
			raise_application_error(-20001,'erreur parametre nom');
		WHEN err_sans_prn THEN
			raise_application_error(-20002,'erreur parametre prenom');
		WHEN no_data_found THEN
			raise_application_error(-20003,'erreur producteur inexistant');
	END reporting_recoltes2;

	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;

END TP2;
/
