Last active
July 16, 2019 07:48
-
-
Save achampav/d5b82c2cfd91af89ec371f8d66a9aa4a to your computer and use it in GitHub Desktop.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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