corr_refcurseurs.sql

-------------------------------------------------------------------------------
-- ex_pl_refcurseurs.sql 
-- tourne le 1er avril 2020
-------------------------------------------------------------------------------
-- Base pour l'exemple : personne(nom, age, activite) dans ex_pl_bases.sql
-------------------------------------------------------------------------------

/*
Le but d'un ref cursor qui nous interesse :
- Si une table(s) est encapsulee, une procedure publique peut evaluer une requete
  dessus et en renvoyer le resultat, sans que l'appelant connaisse la table qui 
  est privee (confidentialite). 
- De plus l'appelant doit juste savoir parcourir un curseur, pas faire une
  requete (architecture en couches).
*/

-------------------------------------------------------------------------------
-- Ex : Ecrire une fonction qui prend en parametre un age, et renvoie une zone
-- curseur contenant le nom et l'age de toutes les personnes d'age strictement
-- inferieur.

create or replace function ex_ref_cursor_fun(
    a personne.age%type) 
    return sys_refcursor -- return cursor
is
    -- ce qu'on aurait ecrit naturellement : 
    -- cursor c is
    --     select nom, age from personne where age < a;
    c sys_refcursor;
begin
    -- open c; -- ce qu'on aurait ecrit naturellement
    open c for              -- remplissage de c
        select nom, age
            from personne
            where age < a;
    return c;               -- renvoi de la zone c
end;
/

-- exec dbms_output.put_line(ex_ref_cursor_fun(30)); -- NON
-- Je dois ecrire une fonction qui prend en parametre un curseur rempli et l'affiche. 
-- exo 1 ci-dessous : en PL/SQL
-- exo2 2 : en Java

-- Ecrire une procedure qui prend en parametre un age, puis recupere un curseur rempli
-- par la fonction ex_ref_cursor_fun, et affiche (le contenu de) ce curseur. 

create or replace procedure aff_ref_cursor(
    a personne.age%type)
is
    c sys_refcursor;
    x personne.nom%type;  -- rem : il faut connaitre les colonnes du select et leur type
    y personne.age%type;
begin 
    -- remplace le open c :
    c := ex_ref_cursor_fun(a);   -- zone renvoyee par fonction mise dans c
                                 -- encapsulation respectee
				 -- elle a rempli le curseur exactement commme un open
				 -- a partir d'ici, peu importe comment c a ete rempli
    -- ex_ref_cursor_proc(a, c); -- pour variante ci-dessous via variable out

    -- on supppose que le curseur c est rempli (de quelque maniere qu'il l'ait ete)
    -- et on va l'afficher :
    loop                         -- parcours normal du curseur
        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);

-------------------------------------------------------------------------------
-- ci-dessous variante avec variable out, pour s'entrainer, 
-- mais version ci-dessus avec fonction plus elegante

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

-- On appelle/teste ex_ref_cursor_proc dans la procedure aff_ref_cursor plus haut.

-------------------------------------------------------------------------------
-- Ecrire une procedure qui prend en parametre un refcursor :

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

exec aff_refcursor2(ex_ref_cursor_fun(23));
exec aff_refcursor2(ex_ref_cursor_fun(30));

-------------------------------------------------------------------------------
-- Voici ci-dessous plusieurs manieres d'appeler ex_ref_cursor_fun et
-- ex_ref_cursor_proc.

-- en cours d'ecriture au 1er avril 2020

declare
    cc sys_refcursor;
begin
    ex_ref_cursor_proc(30, cc);
    aff_ref_cursor()
end;
/

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

