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
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
| -- Version : 1.0
SET HEADING OFF
SET PAGES 0
SET SERVEROUT ON SIZE UNLIMITED
SET SERVEROUTPUT ON SIZE UNLIMITED
-- Sauvegarde de la table
DECLARE
conf_if_debug BOOLEAN :=true;
pseudoBoolean NUMBER;
--Fonction LOG INFO
PROCEDURE loginfo (
v_txt IN VARCHAR2
) IS
BEGIN
dbms_output.put_line('INFO : ' || v_txt);
END;
BEGIN
-- Sauvegarde de la table compétences dans une table temporaire
SELECT count(*) INTO pseudoBoolean FROM user_tables WHERE TABLE_NAME = 'SAVE_DD01405_COMPETENCE';
IF ( pseudoboolean = 0 )THEN
loginfo('SAUVEGARDE DE LA TABLE COMPETENCE');
execute immediate 'CREATE TABLE SAVE_DD01405_COMPETENCE AS SELECT * FROM DD01405_COMPETENCE';
END IF;
COMMIT;
-- table temporaire
SELECT count(*) INTO pseudoBoolean FROM user_tables WHERE TABLE_NAME = 'DD01405_COMPETENCE_TMP';
IF ( pseudoboolean = 0 ) THEN
loginfo('CREATION TABLE TEMPORAIRE');
execute immediate 'CREATE TABLE DD01405_COMPETENCE_TMP AS SELECT * FROM DD01405_COMPETENCE WHERE 1=0';
ELSE
loginfo('VIDAGE TABLE TEMPORAIRE');
execute immediate 'truncate table DD01405_COMPETENCE_TMP';
END IF;
-- setup table final ou debug
IF conf_if_debug THEN
loginfo('ACTION DEBUG');
SELECT count(*) INTO pseudoBoolean FROM user_tables WHERE TABLE_NAME = 'DD01405_COMPETENCE_DEBUG';
IF ( pseudoboolean = 0 ) THEN
execute immediate 'CREATE TABLE DD01405_COMPETENCE_DEBUG AS SELECT * FROM DD01405_COMPETENCE WHERE 1=0';
END IF;
ELSE
loginfo('ACTION PROD');
--action pour la table final
-- TODO supprimer les contraintes et index de la table principale dans la version final
-- TODO vidé la table original dans la version final
-- TODO 'truncate table DD01405_COMPETENCE'
END IF;
COMMIT;
END;
/
-- SELECTION COMPETENCE A METTRE A JOUR
DECLARE
pseudoBoolean NUMBER;
BEGIN
SELECT count(*) INTO pseudoBoolean FROM user_tables WHERE TABLE_NAME = 'COMPETENCE_A_MODIFIE';
IF ( pseudoboolean = 0 ) THEN
execute immediate 'CREATE TABLE COMPETENCE_A_MODIFIE AS SELECT MIN(CPT.ID_COMPETENCE) AS "ID_COMPETENCE" FROM SAVE_DD01405_COMPETENCE CPT INNER JOIN MIG_COMPETENCES_4_0 MIG ON mig.old_code_ogr = CPT.CODE
WHERE DECODE(CPT.TYPE,''L'',NULL,TO_CHAR(CPT.ID_RCI)||NVL(CPT.TYPE,''-'')||NVL(MIG.NEW_CODE_OGR,''-'')) NOT IN (
SELECT DECODE(CPT2.TYPE,''L'',NULL,TO_CHAR(CPT2.ID_RCI)||NVL(CPT2.TYPE,''-'')||NVL(CPT2.CODE,''-''))
FROM SAVE_DD01405_COMPETENCE CPT2 INNER JOIN MIG_COMPETENCES_4_0 MIG2 ON MIG2.new_code_ogr = CPT2.CODE)
AND MIG.ACTION = ''R''
GROUP BY DECODE(CPT.TYPE,''L'',NULL,TO_CHAR(CPT.ID_RCI)||NVL(CPT.TYPE,''-'')||NVL(MIG.NEW_CODE_OGR,''-''))';
ELSE
execute immediate 'TRUNCATE TABLE COMPETENCE_A_MODIFIE';
execute immediate 'INSERT INTO COMPETENCE_A_MODIFIE SELECT MIN(CPT.ID_COMPETENCE) AS "ID_COMPETENCE" FROM SAVE_DD01405_COMPETENCE CPT INNER JOIN MIG_COMPETENCES_4_0 MIG ON mig.old_code_ogr = CPT.CODE
WHERE DECODE(CPT.TYPE,''L'',NULL,TO_CHAR(CPT.ID_RCI)||NVL(CPT.TYPE,''-'')||NVL(MIG.NEW_CODE_OGR,''-'')) NOT IN (
SELECT DECODE(CPT2.TYPE,''L'',NULL,TO_CHAR(CPT2.ID_RCI)||NVL(CPT2.TYPE,''-'')||NVL(CPT2.CODE,''-''))
FROM SAVE_DD01405_COMPETENCE CPT2 INNER JOIN MIG_COMPETENCES_4_0 MIG2 ON MIG2.new_code_ogr = CPT2.CODE)
AND MIG.ACTION = ''R''
GROUP BY DECODE(CPT.TYPE,''L'',NULL,TO_CHAR(CPT.ID_RCI)||NVL(CPT.TYPE,''-'')||NVL(MIG.NEW_CODE_OGR,''-''))';
END IF;
COMMIT;
END;
/
DECLARE
--------------------------------------- CONF GLOBALE -------------------------------------------------------------
conf_realiser_les_commits BOOLEAN := true; --/!\ pour la PROD, à mettre à TRUE (si FALSE, un ROLLBACK est effectue apres les INSERT BULK)
conf_if_debug BOOLEAN :=true;
--------------------------------------- TEMPS D'EXECUTION --------------------------------------------------------
--temps de traitement du script complet
v_start_total NUMBER;
v_stop_total NUMBER;
--------------------------------------- FONCTIONS TECHNIQUES -----------------------------------------------------
--Fonction LOG INFO
PROCEDURE loginfo (
v_txt IN VARCHAR2
) IS
BEGIN
dbms_output.put_line('INFO : ' || v_txt);
END;
--Fonction de recuperation du temps systeme
FUNCTION getsystime RETURN NUMBER AS
BEGIN
RETURN dbms_utility.get_time();
END;
--Fonction de calcul et formattage du temps d'execution
FUNCTION calcultempsexecution (
p_start IN NUMBER,
p_stop IN NUMBER
) RETURN VARCHAR2 AS
BEGIN
RETURN ( ( p_stop - p_start ) * 10 )|| ' ms';
END;
--------------------------------------- FONCTIONS RG -----------------------------------------------------
BEGIN
loginfo('---------------------------');
loginfo('------- DEBUT BATCH -------');
loginfo('---------------------------');
--
v_start_total := getsystime();--start temps exec total
INSERT INTO DD01405_COMPETENCE_TMP (
id_rci,
id_competence,
type,
code,
date_ajout,
code_statut,
code_niveau,
libelle_competence,
json_liaison,
origine_donnee,
visibilite_donnee,
date_modification,
code_acteur,
code_origine,
date_validite,
vu_candidat,
ref_externe
)
SELECT
cpt.id_rci,
cpt.id_competence,
cpt.type,
mig.new_code_ogr,
cpt.date_ajout,
cpt.code_statut,
cpt.code_niveau,
cpt.libelle_competence,
cpt.json_liaison,
cpt.origine_donnee,
cpt.visibilite_donnee,
cpt.date_modification,
cpt.code_acteur,
cpt.code_origine,
cpt.date_validite,
cpt.vu_candidat,
cpt.ref_externe
FROM
SAVE_DD01405_COMPETENCE cpt
INNER JOIN mig_competences_4_0 mig ON cpt.code = mig.old_code_ogr
INNER JOIN COMPETENCE_A_MODIFIE cptm ON cptm.ID_COMPETENCE = cpt.ID_COMPETENCE
WHERE
mig.action = 'R';
loginfo(SQL%rowcount || ' Compétences mise à jour');
IF ( conf_realiser_les_commits ) THEN
COMMIT;
ELSE
ROLLBACK;
END IF;
EXECUTE IMMEDIATE 'INSERT INTO DD01405_COMPETENCE_DEBUG SELECT * FROM DD01405_COMPETENCE_TMP';
v_stop_total := getsystime();--start temps exec total
loginfo('> Temps d''execution total = ' || calcultempsexecution(v_start_total, v_stop_total));
loginfo('---------------------------');
loginfo('-------- FIN BATCH --------');
loginfo('---------------------------');
END;
/
EXIT SUCCESS; |
