Problème PL/SQL Si tuple déjà dans la base

Version imprimable

Bonjour à tous, j'ai un problème que je n'arrive pas à résoudre.
J'insère des tuples dans une table que je nomme test mais le problème et que je ne veux pas insérer un tuple qui est déjà dans la base test. Et je ne peux pas utiliser de distinct dans le SELECT, car je dois vérifier chaque tuples.
La section DECLARE est bien déclarée ne vous en faites pas.

Code:

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
 
BEGIN
        Open c1;
        FETCH c1 INTO lu_nomusu, lu_nompat, lu_prenom, lu_dtenai, lu_depcol, lu_motdep;
        co_nomusu := lu_nomusu;
        co_nompat := lu_nompat;
        co_prenom := lu_prenom;
        co_dtenai := lu_dtenai;
        co_depcol := lu_depcol;
        co_motdep := lu_motdep;
    LOOP
        FETCH c1 INTO lu_nomusu, lu_nompat, lu_prenom, lu_dtenai, lu_depcol, lu_motdep;
        IF lu_nompat = co_nompat AND lu_prenom = co_prenom AND lu_dtenai = co_dtenai THEN
          IF lu_depcol IS NULL AND co_depcol IS NULL THEN
            INSERT INTO ANOMALIE VALUES
              (
              lu_nomusu, lu_nompat, lu_prenom, lu_dtenai, lu_depcol, lu_motdep
              );
            INSERT INTO ANOMALIE VALUES
              (
              co_nomusu, co_nompat, co_prenom, co_dtenai, co_depcol, co_motdep
              );
          END IF;
          IF lu_depcol IS NULL THEN
            ins_nomusu := lu_nomusu;
            ins_nompat := lu_nompat;
            ins_prenom := lu_prenom;
            ins_dtenai := lu_dtenai;
            ins_depcol := lu_depcol;
            ins_motdep := lu_motdep;
          ELSE 
            IF lu_depcol > co_depcol AND co_depcol IS NOT NULL THEN
              ins_nomusu := lu_nomusu;
              ins_nompat := lu_nompat;
              ins_prenom := lu_prenom;
              ins_dtenai := lu_dtenai;
              ins_depcol := lu_depcol;
              ins_motdep := lu_motdep;
            END IF;
          END IF;
        ELSE
          IF /* Si pas dans la table */ THEN 
            INSERT INTO UNEFOIS VALUES           
            (
            ins_nomusu, ins_nompat, ins_prenom, ins_dtenai, ins_depcol, ins_motdep
            );
          END IF;
        END IF;
        co_nomusu := lu_nomusu;
        co_nompat := lu_nompat;
        co_prenom := lu_prenom;
        co_dtenai := lu_dtenai;
        co_depcol := lu_depcol;
        co_motdep := lu_motdep;
        EXIT WHEN c1%NOTFOUND;
    END LOOP;
    CLOSE c1;
END;

Merci à tous.

17/11/2009, 11h10
orafrance

supprime tes curseurs et fais des INSERT AS SELECT WHERE NOT EXISTS ça sera bien plus rapide et tu éviteras les doublons ;)
17/11/2009, 13h21
MaRTy59

Je dois garder mes curseurs, la base est énorme :(
17/11/2009, 14h15
orafrance

c'est justement parce qu'elle est énorme que c'est très probablement plus rapide sans curseur. 1 millions d'inserts d'une ligne c'est bcp plus lent qu'un insert d'un million de lignes ;)
17/11/2009, 14h43
MaRTy59

je ne vois pas comment faire sans curseurs ?
17/11/2009, 14h45
orafrance

moi non plus sans le code :mouarf:

Code:

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
 
DECLARE
/* pour mémoriser occurrence lue */ 
    lu_codcol VARCHAR2(5);
    lu_codagt NUMBER;
    lu_nomusu VARCHAR2(30);
    lu_nompat VARCHAR2(30) := ' ';
    lu_prenom VARCHAR2(25) := ' ';
    lu_dtenai DATE := NULL;
    lu_depcol DATE := NULL;
    lu_motdep VARCHAR2(1);
/* pour comparer avec occurrence lue */
    co_codcol VARCHAR2(5);
    co_codagt NUMBER;
    co_nomusu VARCHAR2(30);
    co_nompat VARCHAR2(30) := ' ';
    co_prenom VARCHAR2(25) := ' ';
    co_dtenai DATE := NULL;
    co_depcol DATE := NULL;
    co_motdep VARCHAR2(1);
/* pour mémoriser occurrence à inserer */
    ins_codcol VARCHAR2(5);
    ins_codagt NUMBER;
    ins_nomusu VARCHAR2(30);
    ins_nompat VARCHAR2(30) := ' ';
    ins_prenom VARCHAR2(25) := ' ';
    ins_dtenai DATE := NULL;
    ins_depcol DATE := NULL;
    ins_motdep VARCHAR2(1);
/* definition du curseur */     
    CURSOR c1 IS SELECT jpb.CODCOL, jpb.CODAGT, jpb.NOMUSU, jpb.NOMPAT, jpb.PRENOM, pop.DTENAI, jpb.DEPCOL, jpb.MOTDEP 
    FROM pers.JPB_testNOV2009_AGENT jpb, centrale.POPULATION pop 
    WHERE jpb.CODPOP = pop.CODPOP 
    AND (jpb.CODCOL = '59350' OR jpb.CODCOL = '59450')
    ORDER BY jpb.NOMPAT, jpb.PRENOM, pop.DTENAI, jpb.DEPCOL;  
BEGIN
    Open c1;
        FETCH c1 INTO lu_codcol, lu_codagt, lu_nomusu, lu_nompat, lu_prenom, lu_dtenai, lu_depcol, lu_motdep;
        co_codcol := lu_codcol;
        co_codagt := lu_codagt;
        co_nomusu := lu_nomusu;
        co_nompat := lu_nompat;
        co_prenom := lu_prenom;
        co_dtenai := lu_dtenai;
        co_depcol := lu_depcol;
        co_motdep := lu_motdep;
    LOOP
        FETCH c1 INTO lu_codcol, lu_codagt, lu_nomusu, lu_nompat, lu_prenom, lu_dtenai, lu_depcol, lu_motdep;
        IF lu_nompat = co_nompat AND lu_prenom = co_prenom AND lu_dtenai = co_dtenai THEN
          IF lu_depcol IS NULL AND co_depcol IS NULL THEN
            INSERT INTO ANOMALIE VALUES
              (
              lu_codcol, lu_codagt, lu_nomusu, lu_nompat, lu_prenom, lu_dtenai, lu_depcol, lu_motdep
              );
            INSERT INTO ANOMALIE VALUES
              (
              co_codcol, co_codagt, co_nomusu, co_nompat, co_prenom, co_dtenai, co_depcol, co_motdep
              );
          END IF;
          IF lu_depcol IS NULL THEN
            ins_codcol := lu_codcol;
            ins_codagt := lu_codagt;
            ins_nomusu := lu_nomusu;
            ins_nompat := lu_nompat;
            ins_prenom := lu_prenom;
            ins_dtenai := lu_dtenai;
            ins_depcol := lu_depcol;
            ins_motdep := lu_motdep;
          ELSE 
            IF lu_depcol > co_depcol AND co_depcol IS NOT NULL THEN
              ins_codcol := lu_codcol;
              ins_codagt := lu_codagt;
              ins_nomusu := lu_nomusu;
              ins_nompat := lu_nompat;
              ins_prenom := lu_prenom;
              ins_dtenai := lu_dtenai;
              ins_depcol := lu_depcol;
              ins_motdep := lu_motdep;
            END IF;
          END IF;
        ELSE
          INSERT INTO UNEFOIS VALUES           
          (
          ins_codcol, ins_codagt, ins_nomusu, ins_nompat, ins_prenom, ins_dtenai, ins_depcol, ins_motdep
          );
        END IF;
        co_codcol := lu_codcol;
        co_codagt := lu_codagt;
        co_nomusu := lu_nomusu;
        co_nompat := lu_nompat;
        co_prenom := lu_prenom;
        co_dtenai := lu_dtenai;
        co_depcol := lu_depcol;
        co_motdep := lu_motdep;
        EXIT WHEN c1%NOTFOUND;
    END LOOP;
    CLOSE c1;
END;

17/11/2009, 15h20
orafrance

faudrait expliquer un minimum le fonctionnel aussi parce qu'on comprend pas pourquoi tu compares les lignes une à une, pourquoi des valeurs peuvent être nul et dans quelles conditions tu insères ou pas.
17/11/2009, 15h29
MaRTy59

En fait je dois détecter les anomalies dans une base, elle est constituée de plusieurs personnes, ces personnes ont des dates (depcol) qui doivent être obligatoirement renseignées, or ici, les dates ne sont pas tout le temps mises. Mais une même personne peut être aussi plusieurs fois dans la base. Si une même personne y est trois fois par exemple et que dans les trois cas, seulement deux dates (depcol) sont renseignées alors je prends celle qui n'est pas renseignée. Si dans les trois cas, les trois sont renseignées, alors je prends la date la plus récente, si dans les trois cas, deux ne sont pas renseignées je prends les deux non renseignées ... J'espère que vous me comprendrez.
17/11/2009, 15h35
Waldar

Un petit jeu de test avec une personne bien renseignée, une personne moyennement bien renseignée et une personne mal renseignée ce serait parfait !
17/11/2009, 15h53
MaRTy59

CODCOL|CODAGT|NOMPAT|PRENOM|DTENAI|DEPCOL

59350|12765|BER|JP|02/05/1950|NULL --> OK je ne prends pas
59350|6614|CHE|ER|01/09/1962|30/08/1990 --> OK je ne prends pas
59350|6614|CHE|ER|01/09/1962|19/10/2002 --> Vu qu'ils sont identiques je prends ce tuple ci car date plus récente que l'autre(19/10/2002 > 30/08/1990).
59350|28365|DEU|ROM|06/11/1987|30/06/2002 --> OK je ne prends pas
59350|28365|DEU|ROM|06/11/1987|NULL --> Je prends ce tuple car Il existe deux fois, sur les deux je prends la date non renseignée.
59350|22733|PI|NI|08/09/1980|NULL --> La c'est délicat c'est comme le premier tuple donc normalement on ne prends pas
59350|22733|PI|NI|08/09/1980|NULL --> Vu qu'il existe deux fois on prends les deux.

Je pense que c'est compréhensible ?

un début de réflexion :

Code:

1
2
3
4
5
6
7
8
9
CREATE TABLE test_tab (CODCOL integer, CODAGT integer,NOMPAT VARCHAR2(30), PRENOM VARCHAR2(30), DTENAI date, DEPCOL date);
 
insert into test_tab values (59350,12765,'BER','JP',to_date('02/05/1950','DD/MM/YYYY'),NULL); --> OK je ne prends pas
insert into test_tab values (59350,6614,'CHE','ER',to_date('01/09/1962','DD/MM/YYYY'),to_date('30/08/1990','DD/MM/YYYY')); --> OK je ne prends pas
insert into test_tab values (59350,6614,'CHE','ER',to_date('01/09/1962','DD/MM/YYYY'),to_date('19/10/2002','DD/MM/YYYY')); --> Vu qu'ils sont identiques je prends ce tuple ci car date plus récente que l'autre(19/10/2002 > 30/08/1990).
insert into test_tab values (59350,28365,'DEU','ROM',to_date('06/11/1987','DD/MM/YYYY'),to_date('30/06/2002','DD/MM/YYYY')); --> OK je ne prends pas
insert into test_tab values (59350,28365,'DEU','ROM',to_date('06/11/1987','DD/MM/YYYY'),NULL); --> Je prends ce tuple car Il existe deux fois, sur les deux je prends la date non renseignée.
insert into test_tab values (59350,22733,'PI','NI',to_date('08/09/1980','DD/MM/YYYY'),NULL); --> La c'est délicat c'est comme le premier tuple donc normalement on ne prends pas
insert into test_tab values (59350,22733,'PI','NI',to_date('08/09/1980','DD/MM/YYYY'),NULL); --> Vu qu'il existe deux fois on prends les deux.

Code:

1
2
3
4
SELECT CODCOL,CODAGT,NOMPAT,PRENOM,DTENAI,DEPCOL,
		   COUNT(0) OVER (PARTITION BY NOMPAT, PRENOM, DTENAI) nb_lig, 
		   SUM(DECODE(DEPCOL,NULL,0,1)) OVER (PARTITION BY NOMPAT, PRENOM, DTENAI) nb_lig_ok
    FROM test_tab

nb_lig = nb de lignes pour un tuple donné
nb_lig_ok = nb de lignes avec DEPCOL renseigné

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
SQL> SELECT CODCOL,CODAGT,NOMPAT,PRENOM,DTENAI,DEPCOL,
  2       COUNT(0) OVER (PARTITION BY NOMPAT, PRENOM, DTENAI) nb_lig, 
  3       SUM(DECODE(DEPCOL,NULL,0,1)) OVER (PARTITION BY NOMPAT, PRENOM, DTENAI) nb_lig_ok
  4      FROM test_tab;
 
    CODCOL     CODAGT NOMPAT                         PRENOM                         DTENAI    DEPCOL        NB_LIG  NB_LIG_OK
---------- ---------- ------------------------------ ------------------------------ --------- ------
     59350      12765 BER                            JP                             02-MAY-50                    1          0
     59350       6614 CHE                            ER                             01-SEP-62 30-AUG-90          2          2
     59350       6614 CHE                            ER                             01-SEP-62 19-OCT-02          2          2
     59350      28365 DEU                            ROM                            06-NOV-87 30-JUN-02          2          1
     59350      28365 DEU                            ROM                            06-NOV-87                    2          1
     59350      22733 PI                             NI                             08-SEP-80                    2          0
     59350      22733 PI                             NI                             08-SEP-80                    2          0

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
with matable as
(
select 59350 as codcol, 12765 as codagt, 'BER' as nompat, 'JP' as prenom, to_date('02/05/1950', 'dd/mm/yyyy') as dtenai, null                      as depcol from dual union all
select 59350          ,  6614          , 'CHE'          , 'ER'          , to_date('01/09/1962', 'dd/mm/yyyy')          , to_date('30/08/1990', 'dd/mm/yyyy') from dual union all
select 59350          ,  6614          , 'CHE'          , 'ER'          , to_date('01/09/1962', 'dd/mm/yyyy')          , to_date('19/10/2002', 'dd/mm/yyyy') from dual union all
select 59350          , 28365          , 'DEU'          , 'ROM'         , to_date('06/11/1987', 'dd/mm/yyyy')          , to_date('30/06/2002', 'dd/mm/yyyy') from dual union all
select 59350          , 28365          , 'DEU'          , 'ROM'         , to_date('06/11/1987', 'dd/mm/yyyy')          , null                                from dual union all
select 59350          , 22733          , 'PI'           , 'NI'          , to_date('08/09/1980', 'dd/mm/yyyy')          , null                                from dual union all
select 59350          , 22733          , 'PI'           , 'NI'          , to_date('08/09/1980', 'dd/mm/yyyy')          , null                                from dual
)
select codcol, codagt, nompat, prenom, dtenai, depcol
from
(
select codcol, codagt, nompat, prenom, dtenai, depcol,
       count(*) over(partition by codagt, nompat, prenom, dtenai) as nb,
       row_number() over(partition by codagt, nompat, prenom, dtenai order by depcol desc nulls first) as rn
  from matable
)
where nb > 1 and rn = 1;
 
CODCOL	CODAGT	NOMPAT	PRENOM	DTENAI		DEPCOL
59350	6614	CHE	ER	01/09/1962	19/10/2002
59350	22733	PI	NI	08/09/1980	
59350	28365	DEU	ROM	06/11/1987

Pour le cas avec les deux dates à null, il vous faut vraiment les deux ou ce n'est pas la peine ?

Ca devrait être pas mal ça :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
WITH matable AS
(
SELECT 59350 AS codcol, 12765 AS codagt, 'BER' AS nompat, 'JP' AS prenom, to_date('02/05/1950', 'dd/mm/yyyy') AS dtenai, NULL                      AS depcol FROM dual union ALL
SELECT 59350          ,  6614          , 'CHE'          , 'ER'          , to_date('01/09/1962', 'dd/mm/yyyy')          , to_date('30/08/1990', 'dd/mm/yyyy') FROM dual union ALL
SELECT 59350          ,  6614          , 'CHE'          , 'ER'          , to_date('01/09/1962', 'dd/mm/yyyy')          , to_date('19/10/2002', 'dd/mm/yyyy') FROM dual union ALL
SELECT 59350          , 28365          , 'DEU'          , 'ROM'         , to_date('06/11/1987', 'dd/mm/yyyy')          , to_date('30/06/2002', 'dd/mm/yyyy') FROM dual union ALL
SELECT 59350          , 28365          , 'DEU'          , 'ROM'         , to_date('06/11/1987', 'dd/mm/yyyy')          , NULL                                FROM dual union ALL
SELECT 59350          , 22733          , 'PI'           , 'NI'          , to_date('08/09/1980', 'dd/mm/yyyy')          , NULL                                FROM dual union ALL
SELECT 59350          , 22733          , 'PI'           , 'NI'          , to_date('08/09/1980', 'dd/mm/yyyy')          , NULL                                FROM dual
)
SELECT CODCOL,CODAGT,NOMPAT,PRENOM,DTENAI, DEPCOL, 
	   CASE WHEN ((DEPCOL IS NOT NULL AND NB_LIG = NB_LIG_OK) OR (DEPCOL IS NULL AND NB_LIG > 1 AND NB_LIG_OK = 0)) 
		THEN 'A GARDER'
		ELSE 'A SUPPRIMER'
	   END status
FROM
(
SELECT codcol, codagt, nompat, prenom, dtenai, depcol,
       COUNT(0) OVER (PARTITION BY NOMPAT, PRENOM, DTENAI) nb_lig,
       SUM(DECODE(DEPCOL,NULL,0,1)) OVER (PARTITION BY NOMPAT, PRENOM, DTENAI) nb_lig_ok
  FROM matable
);

Après il n'y a plus qu'à insérer les A GARDER et A SUPPRIMER dans les bonnes tables avec un MIN(DEPCOL) bien senti ;)

Genre :

Code:

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
INSERT INTO unefois SELECT CODCOL,CODAGT,NOMPAT,PRENOM,DTENAI, MIN(DEPCOL) FROM (
WITH matable AS
(
SELECT 59350 AS codcol, 12765 AS codagt, 'BER' AS nompat, 'JP' AS prenom, to_date('02/05/1950', 'dd/mm/yyyy') AS dtenai, NULL                      AS depcol FROM dual union ALL
SELECT 59350          ,  6614          , 'CHE'          , 'ER'          , to_date('01/09/1962', 'dd/mm/yyyy')          , to_date('30/08/1990', 'dd/mm/yyyy') FROM dual union ALL
SELECT 59350          ,  6614          , 'CHE'          , 'ER'          , to_date('01/09/1962', 'dd/mm/yyyy')          , to_date('19/10/2002', 'dd/mm/yyyy') FROM dual union ALL
SELECT 59350          , 28365          , 'DEU'          , 'ROM'         , to_date('06/11/1987', 'dd/mm/yyyy')          , to_date('30/06/2002', 'dd/mm/yyyy') FROM dual union ALL
SELECT 59350          , 28365          , 'DEU'          , 'ROM'         , to_date('06/11/1987', 'dd/mm/yyyy')          , NULL                                FROM dual union ALL
SELECT 59350          , 22733          , 'PI'           , 'NI'          , to_date('08/09/1980', 'dd/mm/yyyy')          , NULL                                FROM dual union ALL
SELECT 59350          , 22733          , 'PI'           , 'NI'          , to_date('08/09/1980', 'dd/mm/yyyy')          , NULL                                FROM dual
)
SELECT CODCOL,CODAGT,NOMPAT,PRENOM,DTENAI, DEPCOL, 
	   CASE WHEN ((DEPCOL IS NOT NULL AND NB_LIG = NB_LIG_OK) OR (DEPCOL IS NULL AND NB_LIG > 1 AND NB_LIG_OK = 0)) 
		THEN 'A GARDER'
		ELSE 'A SUPPRIMER'
	   END status
FROM
(
SELECT codcol, codagt, nompat, prenom, dtenai, depcol,
       COUNT(0) OVER (PARTITION BY NOMPAT, PRENOM, DTENAI) nb_lig,
       SUM(DECODE(DEPCOL,NULL,0,1)) OVER (PARTITION BY NOMPAT, PRENOM, DTENAI) nb_lig_ok
  FROM matable
)
) 
WHERE status = 'A GARDER'
GROUP BY CODCOL,CODAGT,NOMPAT,PRENOM,DTENAI;

Code:

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
 
INSERT INTO anomalie 
SELECT CODCOL,CODAGT,NOMPAT,PRENOM,DTENAI, DEPCOL FROM (
WITH matable AS
(
SELECT 59350 AS codcol, 12765 AS codagt, 'BER' AS nompat, 'JP' AS prenom, to_date('02/05/1950', 'dd/mm/yyyy') AS dtenai, NULL                      AS depcol FROM dual union ALL
SELECT 59350          ,  6614          , 'CHE'          , 'ER'          , to_date('01/09/1962', 'dd/mm/yyyy')          , to_date('30/08/1990', 'dd/mm/yyyy') FROM dual union ALL
SELECT 59350          ,  6614          , 'CHE'          , 'ER'          , to_date('01/09/1962', 'dd/mm/yyyy')          , to_date('19/10/2002', 'dd/mm/yyyy') FROM dual union ALL
SELECT 59350          , 28365          , 'DEU'          , 'ROM'         , to_date('06/11/1987', 'dd/mm/yyyy')          , to_date('30/06/2002', 'dd/mm/yyyy') FROM dual union ALL
SELECT 59350          , 28365          , 'DEU'          , 'ROM'         , to_date('06/11/1987', 'dd/mm/yyyy')          , NULL                                FROM dual union ALL
SELECT 59350          , 22733          , 'PI'           , 'NI'          , to_date('08/09/1980', 'dd/mm/yyyy')          , NULL                                FROM dual union ALL
SELECT 59350          , 22733          , 'PI'           , 'NI'          , to_date('08/09/1980', 'dd/mm/yyyy')          , NULL                                FROM dual
)
SELECT CODCOL,CODAGT,NOMPAT,PRENOM,DTENAI, DEPCOL, 
	   CASE WHEN ((DEPCOL IS NOT NULL AND NB_LIG = NB_LIG_OK) OR (DEPCOL IS NULL AND NB_LIG > 1 AND NB_LIG_OK = 0)) 
		THEN 'A GARDER'
		ELSE 'A SUPPRIMER'
	   END status
FROM
(
SELECT codcol, codagt, nompat, prenom, dtenai, depcol,
       COUNT(0) OVER (PARTITION BY NOMPAT, PRENOM, DTENAI) nb_lig,
       SUM(DECODE(DEPCOL,NULL,0,1)) OVER (PARTITION BY NOMPAT, PRENOM, DTENAI) nb_lig_ok
  FROM matable
)
) 
WHERE status = 'A SUPPRIMER'
GROUP BY CODCOL,CODAGT,NOMPAT,PRENOM,DTENAI;