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
|
CREATE OR REPLACE PROCEDURE P_DEL_DOUBLONS_FDET_DET_HISTO(p_dir_log IN VARCHAR2, p_fic_log IN VARCHAR2)
IS
FILE_LOG UTL_FILE.FILE_TYPE; -- journal de traces
NB_DET_BEFORE INTEGER ;
NB_DET_AFTER INTEGER ;
NB_DET_PURGE INTEGER ;
chaine VARCHAR2(200);
PROCEDURE tracer_info_operation(p_chaine IN VARCHAR2)
IS
BEGIN
UTL_FILE.PUTF(FILE_LOG,'\n' || p_chaine);
END;
FUNCTION f_tab_count(p_nom_tab IN VARCHAR2) RETURN INTEGER
IS
retval INTEGER;
BEGIN
EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM ' || p_nom_tab INTO retval;
RETURN retval;
END;
BEGIN
FILE_LOG := UTL_FILE.FOPEN(p_dir_log,p_fic_log,'W');
NB_DET_BEFORE := f_tab_count('fdet_det_histo');
chaine:=to_char(SYSDATE,'DD/MM/YYYY HH24:MI:SS ') ||' Nombre d''enregistrements dans fdet_det_histo avant purge : ' || NB_DET_BEFORE;
tracer_info_operation(chaine);
DELETE FROM fdet_det_histo T1
WHERE EXISTS ( SELECT 1
FROM fdet_det_histo T2
WHERE T1.num_ecro_ini = T2.num_ecro_ini
AND T1.cod_typ_evt_pen = T2.cod_typ_evt_pen
AND T1.cod_cat_pen = T2.cod_cat_pen
AND NVL(T1.dcod_nbr_aff,'zwx') = NVL(T2.dcod_nbr_aff,'zwx')
AND NVL(T1.cod_proc_pen,'zwx') = NVL(T2.cod_proc_pen,'zwx')
AND NVL(T1.cod_tran_age,'zwx') = NVL(T2.cod_tran_age,'zwx')
AND NVL(T1.cod_cat_adm,'zwx') = NVL(T2.cod_cat_adm,'zwx')
AND NVL(T1.num_ecro_cou,-99) = NVL(T2.num_ecro_cou,-99)
AND NVL(T1.cod_etab_penit,'zwx') = NVL(T2.cod_etab_penit,'zwx')
AND NVL(T1.dat_lib_det,to_date('01/01/1900','DD/MM/YYYY')) = NVL(T2.dat_lib_det,to_date('01/01/1900','DD/MM/YYYY'))
AND NVL(T1.nbr_enf_det,-99) = NVL(T2.nbr_enf_det,-99)
AND NVL(T1.nbr_enf_deten,-99) = NVL(T2.nbr_enf_deten,-99)
AND NVL(T1.dat_nais_det,to_date('01/01/1900','DD/MM/YYYY')) = NVL(T2.dat_nais_det,to_date('01/01/1900','DD/MM/YYYY'))
AND NVL(T1.ind_fran_det,'W') = NVL(T2.ind_fran_det,'W')
AND NVL(T1.ind_incar,'W') = NVL(T2.ind_incar,'W')
AND NVL(T1.sexe,'W') = NVL(T2.sexe,'W')
AND NVL(T1.dat_ecro_cou,to_date('01/01/1900','DD/MM/YYYY')) = NVL(T2.dat_ecro_cou,to_date('01/01/1900','DD/MM/YYYY'))
AND NVL(T1.dat_ecro_ini,to_date('01/01/1900','DD/MM/YYYY')) = NVL(T2.dat_ecro_ini,to_date('01/01/1900','DD/MM/YYYY'))
AND NVL(T1.dt_fin_pen_det,to_date('01/01/1900','DD/MM/YYYY')) = NVL(T2.dt_fin_pen_det,to_date('01/01/1900','DD/MM/YYYY'))
AND NVL(T1.dt_comd_det,to_date('01/01/1900','DD/MM/YYYY')) = NVL(T2.dt_comd_det,to_date('01/01/1900','DD/MM/YYYY'))
AND NVL(T1.qtm_ii_val,'W') = NVL(T2.qtm_ii_val,'W')
AND NVL(T1.qtm_hh_val,-99) = NVL(T2.qtm_hh_val,-99)
AND NVL(T1.qtm_mm_val,-99) = NVL(T2.qtm_mm_val,-99)
AND NVL(T1.qtm_jj_val,-99) = NVL(T2.qtm_jj_val,-99)
AND NVL(T1.qtm_mm_pen,-99) = NVL(T2.qtm_mm_pen,-99)
AND NVL(T1.qtm_aa_pen,'zwx') = NVL(T2.qtm_aa_pen,'zwx')
AND NVL(T1.qtm_ss_pen,-99) = NVL(T2.qtm_ss_pen,-99)
AND NVL(T1.qtm_hh_pen,-99) = NVL(T2.qtm_hh_pen,-99)
AND NVL(T1.qtm_jj_pen,-99) = NVL(T2.qtm_jj_pen,-99)
AND NVL(T1.sum_qtm_aa_pen,-99) = NVL(T2.sum_qtm_aa_pen,-99)
AND NVL(T1.sum_qtm_mm_pen,-99) = NVL(T2.sum_qtm_mm_pen,-99)
AND NVL(T1.sum_qtm_ss_pen,-99) = NVL(T2.sum_qtm_ss_pen,-99)
AND NVL(T1.sum_qtm_hh_pen,-99) = NVL(T2.sum_qtm_hh_pen,-99)
AND NVL(T1.sum_qtm_jj_pen,-99) = NVL(T2.sum_qtm_jj_pen,-99)
AND NVL(T1.sum_aa_rem_pen,-99) = NVL(T2.sum_aa_rem_pen,-99)
AND NVL(T1.sum_mm_rem_pen,-99) = NVL(T2.sum_mm_rem_pen,-99)
AND NVL(T1.sum_ss_rem_pen,-99) = NVL(T2.sum_ss_rem_pen,-99)
AND NVL(T1.sum_hh_rem_pen,-99) = NVL(T2.sum_hh_rem_pen,-99)
AND NVL(T1.sum_jj_rem_pen,-99) = NVL(T2.sum_jj_rem_pen,-99)
AND NVL(T1.age_det,-99) = NVL(T2.age_det,-99)
AND NVL(T1.ind_gide,'W') = NVL(T2.ind_gide,'W')
AND NVL(T1.libl_typ_evt_pen,'zxw') = NVL(T2.libl_typ_evt_pen,'zwx')
AND NVL(T1.libc_cat_pen,'zwx') = NVL(T2.libc_cat_pen,'zwx')
AND NVL(T1.dlibl_nbr_aff,'zwx') = NVL(T2.dlibl_nbr_aff,'zwx')
AND NVL(T1.libc_proc_pen,'zwx') = NVL(T2.libc_proc_pen,'zwx')
AND NVL(T1.libc_tran_age,'zwx') = NVL(T2.libc_tran_age,'zwx')
AND NVL(T1.libl_cat_adm,'zwx') = NVL(T2.libl_cat_adm,'zwx')
AND NVL(T1.grpevt_pen_det,-9) = NVL(T2.grpevt_pen_det,-9)
AND NVL(T1.qtm_tot_pen,-99) = NVL(T2.qtm_tot_pen,-99)
AND NVL(T1.qtm_tot_val,-99) = NVL(T2.qtm_tot_val,-99)
AND NVL(T1.cod_tra_peine,'zwx') = NVL(T2.cod_tra_peine,'')
AND NVL(T1.libl_tra_peine,'zwx') = NVL(T2.libl_tra_peine,'zwx')
AND NVL(T1.cod_natio,'zwx') = NVL(T2.cod_natio,'zwx')
AND NVL(T1.libl_natio,'zwx') = NVL(T2.libl_natio,'zwx')
AND NVL(T1.cod_typ_mvt,'zwx') = NVL(T2.cod_typ_mvt,'zwx')
AND NVL(T1.libc_typ_mvt,'zwx') = NVL(T2.libc_typ_mvt,'zwx')
AND NVL(T1.cod_mot_lib,'zwx') = NVL(T2.cod_mot_lib,'zwx')
AND NVL(T1.cod_typ_mes,'zwx') = NVL(T2.cod_typ_mes,'zwx')
AND NVL(T1.cod_niv_ins,'zwx') = NVL(T2.cod_niv_ins,'zwx')
AND NVL(T1.libl_niv_ins,'zwx') = NVL(T2.libl_niv_ins,'zwx')
AND NVL(T1.libl_typ_mes,'zwx') = NVL(T2.libl_typ_mes,'zwx')
AND NVL(T1.dat_mvt_det,to_date('01/01/1900','DD/MM/YYYY')) = NVL(T2.dat_mvt_det,to_date('01/01/1900','DD/MM/YYYY'))
AND T2.dat_der_modif = ( SELECT MAX(T3.dat_der_modif)
FROM fdet_det_histo T3
WHERE T3.num_ecro_ini = T2.num_ecro_ini
AND T3.dat_der_modif < T1.dat_der_modif
)
);
COMMIT;
NB_DET_AFTER := SQL%ROWCOUNT;
NB_DET_PURGE := NB_DET_BEFORE - NB_DET_AFTER;
chaine:=to_char(SYSDATE,'DD/MM/YYYY HH24:MI:SS ') ||' Nombre d''enregistrements dans fdet_det_histo apres purge : ' || NB_DET_PURGE;
tracer_info_operation(chaine);
UTL_FILE.FCLOSE(FILE_LOG);
EXCEPTION
WHEN OTHERS
THEN ROLLBACK;
chaine:='Probleme lors de la purge de fdet_det_histo ' || SQLERRM(SQLCODE);
tracer_info_operation(chaine);
UTL_FILE.FCLOSE(FILE_LOG);
END P_DEL_DOUBLONS_FDET_DET_HISTO;
/ |
[pb]Optimisation d'une procedure de suppression de doublons
Répondre avec citation
Partager