-------------------------------------------------------------------------------
-- corr_pl_refcurseurs.sql
-- TD6b partie PL/SQL
-- tourne le 23 mars 2022
-------------------------------------------------------------------------------

create or replace function renvoyer_sejours (
    l_idc client.idc%type)
    return sys_refcursor
is
    c sys_refcursor;
begin
    open c for
        select ids,idv,jour
            from sejour
            where idc = l_idc;
    return c;
end;
/

-- la tester :
set serveroutput on 
declare
    c sys_refcursor;
    l_ids sejour.ids%type;
    l_idv sejour.ids%type;
    l_jour sejour.ids%type;
begin
    c := renvoyer_sejours(1);
    loop
        fetch c into l_ids, l_idv, l_jour;
        exit when c%notfound;
        dbms_output.put_line(l_ids||' '||l_idv||' '||l_jour);
    end loop;
end;
/

create or replace procedure consulter_sejour_refcurseur (
    l_idc client.idc%type)
is

    ???

c sys_refcursor;
	l_ids sejour.ids%type;
	l_idv sejour.ids%type;
	l_jour sejour.ids%type;
begin
	c := renvoyer_sejours(l_idc);
	loop
		fetch c into l_ids,l_idv,l_jour;
		exit when c%notfound;
		dbms_output.put_line(l_ids||' '||l_idv||' '||l_jour);
	end loop;
end;
/

create or replace procedure aff_ref_cursor(
    a personne.age%type)
is
    c sys_refcursor;
    x personne.nom%type;
    y personne.age%type;
begin 
    c := ex_ref_cursor_fun(a);
    -- ex_ref_cursor_proc(a, c);
    loop
        fetch c into x, y;
        exit when c%notfound;
	dbms_output.put_line(x||' '||y);
    end loop;
end;
/

select * from personne;
set serveroutput on
exec aff_ref_cursor(30);

create or replace procedure ex_ref_cursor_proc (
    a personne.age%type, 
    c out sys_refcursor)
is
begin
    open c for 
        select nom, age
            from personne
            where age < a;
end;
/

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


create or replace procedure consulter_sejour_proc (l_idc client.idc%type) is
	c sys_refcursor;
	l_ids sejour.ids%type;
	l_idv sejour.ids%type;
	l_jour sejour.ids%type;
begin
	open c for
		select ids,idv,jour
			from sejour
			where idc = l_idc;
	loop
		fetch c into l_ids,l_idv,l_jour;
		exit when c%notfound;
		dbms_output.put_line(l_ids||' '||l_idv||' '||l_jour);
	end loop;
end;
/

