-------------------------------------------------------------------------------
-- corr_pl_ref_curseur.sql
-- par Jesus Camacho-Rodriguez (presentation adaptee par EW)
-------------------------------------------------------------------------------

-- ATTENTION : necessite que bonnes tables soient deja presentes dans base
-- a verifier que tourne avec tables EW

create or replace procedure village_client_cursor (
	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 consultation_village_client2 (
    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
    village_client_cursor(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;
/

exec consultation_village_client2(1);

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