-- corr_pl_curseurs.sql
-- corrige partiel
-------------------------------------------------------------------------------
-- Jesus Camacho, 27 septembre 2012

-- 11. Curseurs
-- 11.1. Familiarisation.
-- 11.2.
CREATE OR REPLACE PROCEDURE consultationvillageclient (
	idc_p IN sejour.idc%type)
IS
	CURSOR c IS
		SELECT ville,activite,prix
		FROM village,sejour
		WHERE village.idv=sejour.idv AND sejour.idc=idc_p;
	BEGIN
		FOR x IN c LOOP
			dbms_output.put_line(x.ville||', '||x.activite||', '||x.prix);
		END LOOP;
	END;
/

EXECUTE consultationvillageclient(1);

-- 11.3. Obtenir le numero des lignes resultant de la requete et faire un boucle
CREATE OR REPLACE PROCEDURE consultationvillageclient (
	idc_p IN sejour.idc%type)
IS
	c INT;
	ville_r village.ville%type;
	activite_r village.activite%type;
	prix_r village.prix%type;
	BEGIN
		SELECT COUNT(*) INTO c
			FROM village,sejour
			WHERE village.idv=sejour.idv AND sejour.idc=idc_p;
		FOR x IN 1..c LOOP
			SELECT ville,activite,prix INTO ville_r,activite_r,prix_r
				FROM village,sejour
				WHERE village.idv=sejour.idv AND sejour.idc=idc_p AND rownum=x;
			dbms_output.put_line(ville_r||', '||activite_r||', '||prix_r);
		END LOOP;
	END;
/
-- On fait une requete pour chaque ligne!

-------------------------------------------------------------------------------
-- 3 octobre 2012
-- 13. Curseurs (2/2)
-- 13.1. 
CREATE OR REPLACE PROCEDURE villageclientcursor (
	idc_p IN sejour.idc%type,
	c OUT SYS_REFCURSOR)
IS
	BEGIN
		OPEN c FOR
			SELECT DISTINCT destination,activite,prix
			FROM village,sejour
			WHERE village.idv=sejour.idv AND sejour.idc=idc_p;
	END;
/

CREATE OR REPLACE PROCEDURE consultationvillageclient2 (
	idc_p IN sejour.idc%type)
IS
	c SYS_REFCURSOR;
	destination_p village.destination%type;
	activite_p village.activite%type;
	prix_p village.prix%type;
	BEGIN
		villageclientcursor(idc_p, c);
		LOOP
	    	FETCH c INTO destination_p, activite_p, prix_p;
	    	EXIT WHEN c%NOTFOUND;
			dbms_output.put_line(destination_p||', '||activite_p||', '||prix_p);
	   	END LOOP;
	END;
/

EXECUTE consultationvillageclient2(1);

-------------------------------------------------------------------------------
-- 11. Curseurs (1/2)
-- 11.1. Familiarisation.
-- 11.2.
CREATE OR REPLACE PROCEDURE consultationvillageclient (
	idc_p IN sejour.idc%type)
IS
	CURSOR c IS
		SELECT DISTINCT ville,activite,prix
		FROM village,sejour
		WHERE village.idv=sejour.idv AND sejour.idc=idc_p;
	BEGIN
		FOR x IN c LOOP
			dbms_output.put_line(x.ville||', '||x.activite||', '||x.prix);
		END LOOP;
	END;
/

EXECUTE consultationvillageclient(1);

-- 11.3. Obtenir le numero des lignes resultant de la requete et faire un boucle
-- Deux curseurs!
CREATE OR REPLACE PROCEDURE consultationvillagewrong1 (
	idc_p IN sejour.idc%type)
IS
	idv_p sejour.idv%type;
	CURSOR c1 IS
		SELECT DISTINCT idv
		FROM sejour
		WHERE sejour.idc=idc_p;
	c2 SYS_REFCURSOR;
	destination_p village.destination%type;
	activite_p village.activite%type;
	prix_p village.prix%type;
	BEGIN
		FOR x IN c1 LOOP
			OPEN c2 FOR
				SELECT destination,activite,prix
				FROM village
				WHERE village.idv=x.idv;
			LOOP
			   	FETCH c2 INTO destination_p, activite_p, prix_p;
			   	EXIT WHEN c2%NOTFOUND;
				dbms_output.put_line(destination_p||', '||activite_p||', '||prix_p);
			END LOOP;
		END LOOP;
	END;
/

-- Une requete pour chaque ligne!
CREATE OR REPLACE PROCEDURE consultationvillagewrong2 (
	idc_p IN sejour.idc%type)
IS
	c INT;
	ville_r village.ville%type;
	activite_r village.activite%type;
	prix_r village.prix%type;
	BEGIN
		SELECT COUNT(*) INTO c
			FROM village,sejour
			WHERE village.idv=sejour.idv AND sejour.idc=idc_p;
		FOR x IN 1..c LOOP
			SELECT ville,activite,prix INTO ville_r,activite_r,prix_r
				FROM village,sejour
				WHERE village.idv=sejour.idv AND sejour.idc=idc_p AND rownum=x;
			dbms_output.put_line(ville_r||', '||activite_r||', '||prix_r);
		END LOOP;
	END;
/


-- 13. Curseurs (2/2)
-- 13.1. 
CREATE OR REPLACE PROCEDURE villageclientcursor (
	idc_p IN sejour.idc%type,
	c OUT SYS_REFCURSOR)
IS
	BEGIN
		OPEN c FOR
			SELECT DISTINCT destination,activite,prix
			FROM village,sejour
			WHERE village.idv=sejour.idv AND sejour.idc=idc_p;
	END;
/

CREATE OR REPLACE PROCEDURE consultationvillageclient2 (
	idc_p IN sejour.idc%type)
IS
	c SYS_REFCURSOR;
	destination_p village.destination%type;
	activite_p village.activite%type;
	prix_p village.prix%type;
	BEGIN
		villageclientcursor(idc_p, c);
		LOOP
	    	FETCH c INTO destination_p, activite_p, prix_p;
	    	EXIT WHEN c%NOTFOUND;
			dbms_output.put_line(destination_p||', '||activite_p||', '||prix_p);
	   	END LOOP;
	END;
/

EXECUTE consultationvillageclient2(1);

-------------------------------------------------------------------------------
