1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
|
PROCEDURE X
(
p_dt_min IN DATE,
p_dt_max IN DATE
) IS
-- Curseur de recherche des operations dans la table de reporting
CURSOR c1 IS
SELECT DISTINCT a
FROM tab1
ORDER BY a;
-- Curseur de recherche des UO traitantes dans la table de reporting
CURSOR c2(p_a tab3.a%TYPE) IS
SELECT DISTINCT b
FROM tab1
WHERE a = p_a;
-- Curseur de recherche des UO traitee dans la table de reporting
CURSOR c3(p_a tab3.a%TYPE, p_b tab3.b%TYPE) IS
SELECT DISTINCT c
FROM tab1
WHERE a = p_a
AND b = p_b;
rec1 c1%ROWTYPE;
rec2 c2%ROWTYPE;
rec3 c3%ROWTYPE;
BEGIN
-- Vidage de la table temporaire de calcul des moyennes mobiles tab2
truncate_table('tab2');
-- Suppression des index de la table tab2
drop_table_index('index_tab2');
-- Boucle sur le colonne a
FOR rec1 IN c1
LOOP
-- Boucle sur colonne b
FOR rec2 IN c2(rec1.a)
LOOP
-- Boucle sur colonne c
FOR rec3 IN c3(rec1.a, rec2.b)
LOOP
INSERT INTO tab2
(date_a
,b
,a
,c
,d_mym
,e_mym
,f_mym
,g_mym
,h_mym
,i_mym
,j_mym
,k_mym
,l_mym
,m_mym)
SELECT date_a,
b,
a,
c,
AVG(SUM(d)) over(ORDER BY date_a rows cst_nbmm preceding) AS em,
AVG(SUM(e)) over(ORDER BY date_a rows cst_nbmm preceding) AS sm,
AVG(SUM(d - e)) over(ORDER BY date_a rows cst_nbmm preceding) AS dm,
AVG(SUM(g)) over(ORDER BY date_a rows cst_nbmm preceding) AS debm,
AVG(SUM(h)) over(ORDER BY date_a rows cst_nbmm preceding) AS finm,
AVG(SUM(d * nvl(n, 0))) over(ORDER BY date_a rows cst_nbmm preceding) AS uem,
AVG(SUM(e * nvl(o, 0))) over(ORDER BY date_a rows cst_nbmm preceding) AS usm,
AVG(SUM((d - e) * nvl(p, 0))) over(ORDER BY date_a rows cst_nbmm preceding) AS udm,
AVG(SUM(g * nvl(p, 0))) over(ORDER BY date_a rows cst_nbmm preceding) AS udebm,
AVG(SUM(h * nvl(p, 0))) over(ORDER BY date_a rows cst_nbmm preceding) AS ufinm
FROM tab3 t
WHERE a = rec1.a
AND b = rec2.b
AND nvl(c, ' ') = nvl(rec3.c, ' ')
AND date_a >= p_dt_min
AND date_a <= p_dt_max
GROUP BY date_a,
b,
a,
c;
END LOOP;
COMMIT;
END LOOP;
END LOOP;
-- Creation de l'index index_tab2 = a, b, c, date_a sur la table tab2
create_table_index('index_tab2',
'tab2',
'a,b,c,date_a');
EXCEPTION
WHEN OTHERS THEN
lblmsg := 'ERREUR ORACLE : ' || to_char(SQLCODE) || ' : ' || SQLERRM;
--ecriture message d erreur
RAISE;
END X; |
Optimisation requête et trace
Répondre avec citation
Envoyé par fontome_ 123
mais plutot ton code le deviser pour que tu puisse voir où ca se plante.
Partager