Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
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;
/
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.