Demande d'aide pour script de nettoyage

**super_cros** · 27/07/2010, 18h39

Bonjour à tous (et à toutes),

Voilà mon petit problème : tous les jours (sauf arrêt des tâches automatiques), je récupère des infos d'un autre système afin de tracer d'éventuels changements d'état de mes enregistrements. Chaque fois qu'un état est modifié, je stocke la date et l'état.

Malheureusement, un bug a fait que le stockage avait lieu systématiquement (même quand l'état n'a pas changé).
Je me retrouve donc avec une base à nettoyer

Ci-dessous, un extrait de la table à nettoyer (avec en rouge les lignes à supprimer) :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
ORDER_ID	DATE			STATE
47087	07/07/2010 15:31		0
47087	13/07/2010 05:08		5
47087	14/07/2010 06:04		5
47087	15/07/2010 06:46		5
47087	21/07/2010 05:07		5
47087	22/07/2010 05:00		5
47087	23/07/2010 05:13		9
47087	24/07/2010 05:14		9
47087	25/07/2010 05:27		9
47087	26/07/2010 05:11		9
47087	27/07/2010 05:08		15
47088	07/07/2010 15:31		0
47088	13/07/2010 05:06		5
47088	14/07/2010 06:55		5
47088	15/07/2010 05:54		5
47088	21/07/2010 05:11		5
47088	22/07/2010 05:03		5
47088	23/07/2010 05:13		9
47088	24/07/2010 05:02		9
47088	25/07/2010 05:01		9
47088	26/07/2010 05:11		9
47088	27/07/2010 05:21		15
47089	07/07/2010 15:31		0
47089	13/07/2010 05:06		10
47089	14/07/2010 06:01		10
47089	15/07/2010 06:14		10
47089	21/07/2010 05:01		10
47089	22/07/2010 05:11		10
47089	23/07/2010 05:13		10
47089	24/07/2010 05:05		10
47089	25/07/2010 05:04		10
47089	26/07/2010 05:11		10
47089	27/07/2010 05:16		14

Je me suis lancé dans une procédure Oracle qui, à l'heure actuelle, ressemble à çà :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
DECLARE
 
    -- Parcourir chaque ligne de commande
    CURSOR iterOrderIds_cur
    IS
        SELECT DISTINCT order_id
          FROM state_history;
 
    -- Stocker l'id de la commande courante
    currentOrderId_rec iterOrderIds_cur%ROWTYPE;
 
    -- Parcourir chaque état pour une ligne de commande
    CURSOR iterOrderStates_cur (orderId_in NUMBER)
    IS 
        SELECT DISTINCT hist_at_state
          FROM state_history
         WHERE order_id = orderId_in;
 
    -- Stocker l'état courant
    currentState_rec iterOrderStates_cur%ROWTYPE;
 
BEGIN
    -- Parcourir l'historique des états pour chaque ligne de commande
    FOR currentOrderId_rec IN iterOrderIds_cur 
    LOOP
        dbms_output.put_line(currentOrderId_rec.order_id);
        -- Pour chaque ligne de commande, parcourir les états AT de l'historique
        FOR currentState_rec IN iterOrderStates_cur(currentOrderId_rec.order_id)
        LOOP
            dbms_output.put_line(currentState_rec.hist_at_state);
            -- A SUIVRE...
        END LOOP;
    END LOOP;
 
END;

C'est loin d'être terminé et voilà ce que je comptais faire :
- pour chaque état d'une ligne de commande, rechercher l'enregistrement avec la date la plus récente (SELECT ...)
- supprimer les autres lignes (DELETE ...)

Mais çà me parait laborieux et je me demande si je suis sur la bonne piste ou bien si une solution plus simple existe ?
Par exemple en utilisant des fonctions sur les dates ?

Je suis donc preneur de conseils et/ou des commentaires qui pourraient éclairer ma lanterne et m'aider à partir dans le bon sens

Merci d'avance

Cordialement.
S.

**Waldar** · 27/07/2010, 19h15

J'ai un peu plus simple !

Jeu de test :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
create table t
(
    order_id    number(5),
    dt          date,
    state       number(2)
);
 
insert into t (order_id, dt, state)
select 47087, to_date('07/07/2010 15:31', 'dd/mm/yyyy hh24:mi'),  0 from dual union all
select 47087, to_date('13/07/2010 05:08', 'dd/mm/yyyy hh24:mi'),  5 from dual union all
select 47087, to_date('14/07/2010 06:04', 'dd/mm/yyyy hh24:mi'),  5 from dual union all
select 47087, to_date('15/07/2010 06:46', 'dd/mm/yyyy hh24:mi'),  5 from dual union all
select 47087, to_date('21/07/2010 05:07', 'dd/mm/yyyy hh24:mi'),  5 from dual union all
select 47087, to_date('22/07/2010 05:00', 'dd/mm/yyyy hh24:mi'),  5 from dual union all
select 47087, to_date('23/07/2010 05:13', 'dd/mm/yyyy hh24:mi'),  9 from dual union all
select 47087, to_date('24/07/2010 05:14', 'dd/mm/yyyy hh24:mi'),  9 from dual union all
select 47087, to_date('25/07/2010 05:27', 'dd/mm/yyyy hh24:mi'),  9 from dual union all
select 47087, to_date('26/07/2010 05:11', 'dd/mm/yyyy hh24:mi'),  9 from dual union all
select 47087, to_date('27/07/2010 05:08', 'dd/mm/yyyy hh24:mi'), 15 from dual union all
select 47088, to_date('07/07/2010 15:31', 'dd/mm/yyyy hh24:mi'),  0 from dual union all
select 47088, to_date('13/07/2010 05:06', 'dd/mm/yyyy hh24:mi'),  5 from dual union all
select 47088, to_date('14/07/2010 06:55', 'dd/mm/yyyy hh24:mi'),  5 from dual union all
select 47088, to_date('15/07/2010 05:54', 'dd/mm/yyyy hh24:mi'),  5 from dual union all
select 47088, to_date('21/07/2010 05:11', 'dd/mm/yyyy hh24:mi'),  5 from dual union all
select 47088, to_date('22/07/2010 05:03', 'dd/mm/yyyy hh24:mi'),  5 from dual union all
select 47088, to_date('23/07/2010 05:13', 'dd/mm/yyyy hh24:mi'),  9 from dual union all
select 47088, to_date('24/07/2010 05:02', 'dd/mm/yyyy hh24:mi'),  9 from dual union all
select 47088, to_date('25/07/2010 05:01', 'dd/mm/yyyy hh24:mi'),  9 from dual union all
select 47088, to_date('26/07/2010 05:11', 'dd/mm/yyyy hh24:mi'),  9 from dual union all
select 47088, to_date('27/07/2010 05:21', 'dd/mm/yyyy hh24:mi'), 15 from dual union all
select 47089, to_date('07/07/2010 15:31', 'dd/mm/yyyy hh24:mi'),  0 from dual union all
select 47089, to_date('13/07/2010 05:06', 'dd/mm/yyyy hh24:mi'), 10 from dual union all
select 47089, to_date('14/07/2010 06:01', 'dd/mm/yyyy hh24:mi'), 10 from dual union all
select 47089, to_date('15/07/2010 06:14', 'dd/mm/yyyy hh24:mi'), 10 from dual union all
select 47089, to_date('21/07/2010 05:01', 'dd/mm/yyyy hh24:mi'), 10 from dual union all
select 47089, to_date('22/07/2010 05:11', 'dd/mm/yyyy hh24:mi'), 10 from dual union all
select 47089, to_date('23/07/2010 05:13', 'dd/mm/yyyy hh24:mi'), 10 from dual union all
select 47089, to_date('24/07/2010 05:05', 'dd/mm/yyyy hh24:mi'), 10 from dual union all
select 47089, to_date('25/07/2010 05:04', 'dd/mm/yyyy hh24:mi'), 10 from dual union all
select 47089, to_date('26/07/2010 05:11', 'dd/mm/yyyy hh24:mi'), 10 from dual union all
select 47089, to_date('27/07/2010 05:16', 'dd/mm/yyyy hh24:mi'), 14 from dual;
 
commit;

Suppression :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
delete from t
where rowid in (With SR as
                (
                select rowid rd, order_id, dt, state,
                       lag(state) over(partition by order_id order by dt asc) as lg
                  from t
                )
                select rd
                  from sr
                 where state = lg)
-- 22 rows deleted.

Données restantes :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
select * from t
order by 1, 2;
 
ORDER_ID	DT			STATE
47087		07/07/2010 15:31:00	0
47087		13/07/2010 05:08:00	5
47087		23/07/2010 05:13:00	9
47087		27/07/2010 05:08:00	15
47088		07/07/2010 15:31:00	0
47088		13/07/2010 05:06:00	5
47088		23/07/2010 05:13:00	9
47088		27/07/2010 05:21:00	15
47089		07/07/2010 15:31:00	0
47089		13/07/2010 05:06:00	10
47089		27/07/2010 05:16:00	14

**super_cros** · 28/07/2010, 09h51

Super Waldar

Merci bien, je vais tester çà.

Pour info, j'ai trouvé la solution ci-dessous entre temps.
Elle a le mérite de fonctionner même si elle est beaucoup moins esthétique

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
DECLARE
 
    -- Stocker l'identifiant de l'historique
    var_histId state_history.hist_id%type;
 
    -- Parcourir l'historique de chaque ligne de commande
    CURSOR cur_iterOrderIds
    IS
        SELECT DISTINCT order_id
          FROM state_history;
 
    -- Stocker l'identifiant de la ligne de commande courante
    rec_currentOrderId cur_iterOrderIds%ROWTYPE;
 
    -- Parcourir l'ensemble des états pour une ligne de commande donnée
    CURSOR cur_iterOrderStates (orderId_in NUMBER)
    IS 
        SELECT DISTINCT hist_state
          FROM state_history
         WHERE order_id = orderId_in;
 
    -- Stocker l'état courant
    rec_currentState cur_iterOrderStates%ROWTYPE;
 
BEGIN
    -- Parcourir l'historique de chaque ligne de commande
    FOR rec_currentOrderId IN cur_iterOrderIds
    LOOP
 
        dbms_output.put_line(rec_currentOrderId.order_id);
 
        -- Pour chaque ligne de commande, parcourir les états AT de l'historique
        FOR rec_currentState IN cur_iterOrderStates(rec_currentOrderId.order_id)
        LOOP
 
            -- Sélectionner le plus ancien pour un état donnée
            SELECT hist_id into var_histId
              FROM state_history
            WHERE order_id = rec_currentOrderId.order_id
               AND hist_state = rec_currentState.hist_state
               AND hist_state_date = (SELECT min(hist_state_date)
                                           FROM state_history
                                          WHERE order_id = rec_currentOrderId.order_id
                                            AND hist_state = rec_currentState.hist_state
               );
 
            -- Supprimer les autres
            DELETE FROM state_history
             WHERE order_id = rec_currentOrderId.order_id
               AND hist_state = rec_currentState.hist_state
               AND hist_id != var_histId;
 
        END LOOP;
 
        commit;
 
    END LOOP;
 
END;

Demande d'aide pour script de nettoyage

PL/SQL Oracle

Vue hybride

Discussions similaires

Partager

Partager