-------------------------------------------------------------------------------
-- ex_pl_sqldyn_8b.sql
-- ex_pl_sqldyn.sql (extraits)
-- derniere modif le 6 avril 2021
-- bloc 8b
-------------------------------------------------------------------------------
-- REMARQUE : TOUT TOUT TOUT EST COMME JDBC 
-------------------------------------------------------------------------------

-- ecrire proc p2 qui prend nom fonction et l'applique sur 14
create or replace procedure p2(f varchar2) is
    texte_ordre varchar2(100);
    y int; -- obligatoire (different de JDBC)
begin
    -- String texte = "{ ? = call f(14) }"; // rappel : JDBC avec point d'int. out
    /* begin n := f(14); end; -- rappel : approche JDBC */
    /* bloc ephemere PL : 
       declare 
           n int; 
       begin 
           n := f(14);
       end; 
    */
    -- texte_ordre := 'begin ? := f(14); end;'; -- rappel : approche JDBC
    -- :1 est une variable formelle comme les ? de JDBC
    -- variable formelle : ":" suivi de n'importe quoi. Ex : :1, :2, :coucou, :x, :n
    texte_ordre := 'begin :1 := '||f||'(14); end;';
    dbms_output.put_line(texte_ordre);
    execute immediate texte_ordre using out y;
    -- : en utilisant la variable y PL/SQL declaree en haut
    -- pour y mettre la valeur de retour
    dbms_output.put_line(y);
end;
/
-- ecrire proc p3 qui prend nom procedure et l'applique sur 14
create or replace procedure p3(p varchar2) is
    texte_ordre varchar2(100);
    y int;
    -- z int si deux variables out
begin
    -- texte_ordre := 'begin p(14, ?); end;'; -- rappel : approche JDBC
    -- bloc ephemere : declare n int; begin p(7,n); end;
    texte_ordre := 'begin '||p||'(14, :z); end;'; -- si p2(14, :1, :2)
                                                  -- possible : p2(14, :coucou, :x)
                                                  -- possible : p2(14, :2, :1)
    dbms_output.put_line(texte_ordre);
    execute immediate texte_ordre using out y; -- alors using out y, out z
    dbms_output.put_line(y);
end;
/

create or replace procedure oiseau(x int, y out int) is
begin
    y := x + 1 ;
end;
/

set serveroutput on

declare
    y integer;
begin
    oiseau(14, y);
    dbms_output.put_line(y);
end;
/

execute p3('oiseau');

-------------------------------------------------------------------------------
-- exo : repeter une insertion dans une table dont on ne connait pas le nom
-- comme JDBC cf ExJDBC44.java
-- question : et si on connaissait le nom ???...

create or replace procedure p631(t varchar2)
is
    texte_ordre varchar2(100);
    x int;
    y int;
begin
    texte_ordre := 'insert into '||t||' values (:1, :2)';

    x := 1;
    y := 2;
    execute immediate texte_ordre using in x, in y;
    -- x et y sont des expressions ayant une valeur: c'est cette valeur qui est utilisee
    -- f(x, 5, fact(5)) : f(valeur, valeur, valeur)
    x := 3;
    y := 4;
    execute immediate texte_ordre using x, y;
    execute immediate texte_ordre using 5, 6; -- rappel : 5 et 6 sont des valeurs
    execute immediate texte_ordre using 2+3, 2*3; -- eh oui... ce sont des valeurs...
end;
/

execute p631('t');
select * from t;

-------------------------------------------------------------------------------
-- exo : repeter une insertion dans une table dont on connait le nom : t
-- pas besoin de SQL dynamique

create or replace procedure p633
is
    x int;
    y int;
begin
    x := 1;
    y := 2;
    -- execute immediate texte_ordre using x, y;
    insert into t values (x, y);
    x := 3;
    y := 4;
    -- execute immediate texte_ordre using x, y;
    insert into t values (x, y);
    -- execute immediate texte_ordre using 5, 6;
    insert into t values (5, 6);
    -- execute immediate texte_ordre using 2+3, 2*3;
    insert into t values (2+3, 2*3);
end;
/

-------------------------------------------------------------------------------
-- ex : repeter un select plusieurs fois avec parametres differents

create or replace procedure p (t varchar2, a varchar2) is
    c sys_refcursor;
    x int;
    texte varchar2(100);
    n int;
begin
    texte := 'select '||a||' from '||t||' where b = :1'; -- variable formelle in
    dbms_output.put_line(texte);

    n := 4;
    open c for texte using n;
    loop
        fetch c into x; 
        exit when c%notfound;
        dbms_output.put_line(x);
    end loop;

    open c for texte using 17;
    loop
        fetch c into x; 
        exit when c%notfound;
        dbms_output.put_line(x);
    end loop;

end;
/

drop table t;
create table t(a int, b int);
insert into t values(1, 1); 
insert into t values(1, 4); 
insert into t values(2, 4); 
insert into t values(3, 4); 
insert into t values(5, 5); 
insert into t values(5, 17); 
select * from t;
execute p('t', 'a');
execute p('t', 'b');

-------------------------------------------------------------------------------
create or replace procedure p632(f varchar2, p varchar2)
  -- f : int -> int
is
    texte_ordre varchar2(100);
    x int;
begin
    texte_ordre := 'begin :1 := '||f||'(:2); end;';
    execute immediate texte_ordre using out x, in 14;
    dbms_output.put_line(x);
    execute immediate texte_ordre using out x, -22;
    dbms_output.put_line(x);

    texte_ordre := 'begin '||p||'(:1, :2); end;';
    execute immediate texte_ordre using 14, out x;
    dbms_output.put_line(x);
    execute immediate texte_ordre using -22, out x;
    dbms_output.put_line(x);
end;
/

execute p632('f1', 'p2');
select * from t;

-------------------------------------------------------------------------------
-- ecrire une proc qui prend en entree un login oracle et appelle la fonction
-- f de cet utilisateur :

-- ewaller_a tape : 
exec dbms_output.put_line(f(14))
grant execute
    on f
    to c##ewaller2_a;    

execute p3('oiseau');
grant execute
    on p3
    to c##ewaller2_a;    

-- ewaller2_a tape :
set serveroutput on
exec dbms_output.put_line(c##ewaller_a.f(14))
-- create or replace procedure oiseau ... -- disponible en haut de ce fichier
execute c##ewaller_a.p3('oiseau');

create or replace procedure p632(utilisateur varchar2) is
    texte_ordre varchar2(100);
    y int;
begin
    -- si p632 recoit 'ewaller_a'
    -- je veux constuire et mettre dans texte_ordre :
    -- 'begin :1 := c##ewaller_a.f(14); end;'
    texte_ordre := 'begin :1 := c##'||utilisateur||'.f(14); end;';
    dbms_output.put_line(texte_ordre);
    execute immediate texte_ordre using out y;
    dbms_output.put_line(y);
end;
/

exec p632('ewaller_a');

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