optim_rqe_fonction.sql

create or replace function f_requete_metier(v_dte_min date,v_dte_max date,v_filtre1 number,v_filtre2 number,v_filtre3 number)
return t_tbl_rqe_metier
as
    pragma autonomous_transaction;
    v_select t_tbl_requete_metier := t_tbl_requete_metier();
    cpt number := 1;
    cursor c1 is
        select
          d1,ca,cb,cc,c1,c2,c3,c4,c5,c6,c7,c8
        from
          temp_requete_metier;
begin
    -- Effacement de la table. Optionnel mais montre que la table doit être vide
    delete from temp_agregat_metier;
    -- Requête WITH de la requête initiale
    insert into temp_agregat_metier(d1,c1,c2,c3,c4,c5,c6,c7,c8)
    select
        a.d1,a.c1,a.c2,b.c3,b.c4,b.c5,c.c6,c.c7,sum(c.c8) c8
    from
        t1 a
        join t2 b on a.t2_id = b.id
        join t3 c on b.t3_id = c.id
    where
        a.d1 between to_date(v_dte_min,'YYYY-MM-DD') and to_date(v_dte_max,'YYYY-MM-DD')
    group by
        a.d1,a.c1,a.c2,b.c3,b.c4,b.c5,c.c6,c.c7
    
    -- Première partie de l'UNION de la requête initiale
    delete from temp_requete_metier;
    insert into temp_requete_metier(d1,ca,cb,cc,c1,c2,c3,c4,c5,c6,c7,c8)
        select
            a.d1,d.ca,d.cb,d.cc,a.c1,a.c2,a.c3,a.c4,a.c5,a.c6,a.c7,a.c8
        from
            t4 d
            join agregat_metier a on d.t1_id = a.id
        where
            d.cd in ('R1','R2','AB3')
            and d.ce = v_filtre1;
    
    -- Deuxième partie de l'UNION de la requête initiale
    merge into temp_requete_metier
    using
    (
        select
            a.d1,d.ca,d.cb,d.cc,a.c1,a.c2,a.c3,a.c4,a.c5,a.c6,a.c7,a.c8
        from
            t4 d
            join agregat_metier a on d.t1_id = a.id
        where
            d.cd in ('R2','ZZ4','ZZ8')
            and d.ce = v_filtre2
    ) s
    on
    (
            t.d1 = s.d1
        and t.ca = s.ca
        and t.cb = s.cb
        and t.cc = s.cc
        and t.c1 = s.c1
        and t.c2 = s.c2
        and t.c3 = s.c3
        and t.c4 = s.c4
        and t.c5 = s.c5
        and t.c6 = s.c6
        and t.c7 = s.c7
        and t.c8 = s.c8
    )
    when not matched then
        insert into temp_requete_metier(d1,ca,cb,cc,c1,c2,c3,c4,c5,c6,c7,c8)
        values(
            s.d1,s.ca,s.cb,s.cc,s.c1,s.c2,s.c3,s.c4,s.c5,s.c6,s.c7,s.c8
        );
    -- Troisième partie de l'UNION de la requête initiale
    merge into temp_requete_metier
    using
    (
        select
            a.d1,d.ca,d.cb,d.cc,a.c1,a.c2,a.c3,a.c4,a.c5,a.c6,a.c7,a.c8
        from
            t4 d
            join agregat_metier a on d.t1_id = a.id
        where
            d.cd in ('R1','ZZ8','AB3')
            and d.ce = v_filtre3;
    ) s
    on
    (
            t.d1 = s.d1
        and t.ca = s.ca
        and t.cb = s.cb
        and t.cc = s.cc
        and t.c1 = s.c1
        and t.c2 = s.c2
        and t.c3 = s.c3
        and t.c4 = s.c4
        and t.c5 = s.c5
        and t.c6 = s.c6
        and t.c7 = s.c7
        and t.c8 = s.c8
    )
    when not matched then
        insert into temp_requete_metier(d1,ca,cb,cc,c1,c2,c3,c4,c5,c6,c7,c8)
        values(
            s.d1,s.ca,s.cb,s.cc,s.c1,s.c2,s.c3,s.c4,s.c5,s.c6,s.c7,s.c8
        );
        
    -- Commit de la transaction autonome
    commit;
    
    -- Le peuplement du résultat de la requête étant terminé,
    -- préparation du résultat pour retour à la requête appelante
    for i in c1
    loop
        v_select.extend;
        -- Le peuplement de la pseudo-table se fait en utilisant le type objet, non le type table
        v_select(cpt) := t_requete_metier(i.d1,i.ca,i.cb,i.cc,i.c1,i.c2,i.c3,i.c4,i.c5,i.c6,i.c7,i.c8);
        cpt := cpt + 1;
    end loop;
    
    -- Retour de la pseudo-table à la requête appelante
    return v_select;
end;
/