[10g] Package récalcitrant avec MERGE

CUCARACHA · 21/06/2010, 17h11

Salut,

Voici l'extrait de Package qui compile impec et qui s'exécute depuis .net

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
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
 
PROCEDURE GetCreate_BHR_BREACH_PED(
    pThresholdType IN NUMBER,
    pID_BREACH_MAIN IN NUMBER,
    pTreatmentDate IN DATE,
    pNominal IN NUMBER,
    pAmount IN NUMBER,
    pLEGAL_RATIO IN NUMBER
)
IS
    vID_BREACH_PED NUMBER := null;
BEGIN
    BEGIN
        SELECT P.ID_BREACH_PED INTO
            vID_BREACH_PED
        FROM
            BHR_BREACH_PED P
        WHERE
            P.FK_BREACH_MAIN = pID_BREACH_MAIN
            AND
            P.FK_TYP_BREACH = pThresholdType
            AND
            P.FK_PED_STATUS = 1;
            exception
                when NO_DATA_FOUND then
                    vID_BREACH_PED := null;
    END;
 
    IF (vID_BREACH_PED is null) THEN
        --Creation de l'enregistrement 
        INSERT INTO BHR_BREACH_PED ( 
            FK_BREACH_MAIN,
            IS_ARCHIVED,
            FK_TYP_BREACH,
            FK_PED_STATUS,
            START_DATE,
            RM_MSG
        ) VALUES (
            pID_BREACH_MAIN,
            'N',
            pThresholdType,
            1,
            pTreatmentDate,
            ''
        ) returning BHR_BREACH_PED.ID_BREACH_PED into vID_BREACH_PED;
    END IF;
    -- Creation de l'element
    MERGE INTO BHR_BREACH_PED_ITM P1
    USING
        (
            SELECT
                Ptmp.ID_BREACH_PED_ITM,
                Ptmp.FK_BREACH_PED,
                Ptmp.DATE_RATIO,
                Ptmp.NOMINAL,
                Ptmp.AMOUNT
            FROM
                BHR_BREACH_PED_ITM Ptmp
            WHERE
                Ptmp.FK_BREACH_PED = vID_BREACH_PED
                AND
                Ptmp.DATE_RATIO = pTreatmentDate
        ) P2 ON 
        (
            P1.ID_BREACH_PED_ITM = P2.ID_BREACH_PED_ITM
        )
    WHEN MATCHED THEN
        UPDATE SET
            P1.NOMINAL = pNominal,
            P1.AMOUNT = pAmount,
            P1.LEGAL_RATIO = pLEGAL_RATIO
    WHEN NOT MATCHED THEN
        INSERT (
          FK_BREACH_PED,
          DATE_RATIO,
          NOMINAL,
          AMOUNT,
          LEGAL_RATIO
        ) VALUES (
          vID_BREACH_PED,
          pTreatmentDate,
          pNominal,
          pAmount,
          pLEGAL_RATIO
        );
END;

Je n'arrive pas à m'expliquer pourquoi mais la procédure semble s'exécuter jusqu'à la création de l'enregistrement BHR_BREACH_PED et le Merge n'ajoute rien et ne met rien à jour car je clean les tables avant de commencer mon test.

D'après vous, j'ai fait une bêtise ?

Je précise que la table BHR_BREACH_PED_ITM a une séquence qui incrémente sa clé primaire...

D'avance merci

Laurent

**Waldar** · 21/06/2010, 17h15

Oui, si la table est vide, vous voulez fusionner dans une table un dataset qui ne retourne rien, donc rien ne se passe.

Si vos valeurs sont des constantes, il vaut mieux faire la sous-requête sur DUAL.

CUCARACHA · 21/06/2010, 17h30

Humm désolé mais je ne comprends pas la réponse...

CUCARACHA · 21/06/2010, 17h43

Bon, j'ai opté pour une solution moins élégante...

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
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
 
PROCEDURE GetCreate_BHR_BREACH_PED(
    pThresholdType IN NUMBER,
    pID_BREACH_MAIN IN NUMBER,
    pTreatmentDate IN DATE,
    pNominal IN NUMBER,
    pAmount IN NUMBER,
    pLEGAL_RATIO IN NUMBER
)
IS
    vID_BREACH_PED NUMBER := null;
    vID_BREACH_PED_ITM NUMBER := null;
BEGIN
    BEGIN
        SELECT P.ID_BREACH_PED INTO
            vID_BREACH_PED
        FROM
            BHR_BREACH_PED P
        WHERE
            P.FK_BREACH_MAIN = pID_BREACH_MAIN
            AND
            P.FK_TYP_BREACH = pThresholdType
            AND
            P.FK_PED_STATUS = 1;
            exception
                when NO_DATA_FOUND then
                    vID_BREACH_PED := null;
    END;
 
    IF (vID_BREACH_PED is null) THEN
        --Creation de l'enregistrement 
        INSERT INTO BHR_BREACH_PED ( 
            FK_BREACH_MAIN,
            IS_ARCHIVED,
            FK_TYP_BREACH,
            FK_PED_STATUS,
            START_DATE,
            RM_MSG
        ) VALUES (
            pID_BREACH_MAIN,
            'N',
            pThresholdType,
            1,
            pTreatmentDate,
            ''
        ) returning BHR_BREACH_PED.ID_BREACH_PED into vID_BREACH_PED;
    END IF;
 
    /*
    -- Creation de l'element
    MERGE INTO BHR_BREACH_PED_ITM P1
    USING
        (
            SELECT
                Ptmp.ID_BREACH_PED_ITM,
                Ptmp.FK_BREACH_PED,
                Ptmp.DATE_RATIO,
                Ptmp.NOMINAL,
                Ptmp.AMOUNT
            FROM
                BHR_BREACH_PED_ITM Ptmp
            WHERE
                Ptmp.FK_BREACH_PED = vID_BREACH_PED
                AND
                Ptmp.DATE_RATIO = pTreatmentDate
        ) P2 ON 
        (
            P1.ID_BREACH_PED_ITM = P2.ID_BREACH_PED_ITM
        )
    WHEN MATCHED THEN
        UPDATE SET
            P1.NOMINAL = pNominal,
            P1.AMOUNT = pAmount,
            P1.LEGAL_RATIO = pLEGAL_RATIO
    WHEN NOT MATCHED THEN
        INSERT (
          FK_BREACH_PED,
          DATE_RATIO,
          NOMINAL,
          AMOUNT,
          LEGAL_RATIO
        ) VALUES (
          vID_BREACH_PED,
          pTreatmentDate,
          pNominal,
          pAmount,
          pLEGAL_RATIO
        );
    */
 
    BEGIN
        SELECT
            Ptmp.ID_BREACH_PED_ITM
        INTO 
            vID_BREACH_PED_ITM
        FROM
            BHR_BREACH_PED_ITM Ptmp
        WHERE
            Ptmp.FK_BREACH_PED = vID_BREACH_PED
            AND
            Ptmp.DATE_RATIO = pTreatmentDate;
        exception
            when NO_DATA_FOUND then
                vID_BREACH_PED_ITM := null;
    END;
 
    IF vID_BREACH_PED_ITM is null THEN
        INSERT INTO BHR_BREACH_PED_ITM (
          FK_BREACH_PED,
          DATE_RATIO,
          NOMINAL,
          AMOUNT,
          LEGAL_RATIO
        ) VALUES (
          vID_BREACH_PED,
          pTreatmentDate,
          pNominal,
          pAmount,
          pLEGAL_RATIO
        ) returning BHR_BREACH_PED_ITM.ID_BREACH_PED_ITM into vID_BREACH_PED_ITM;
    ELSE
        UPDATE BHR_BREACH_PED_ITM P1 SET
            P1.NOMINAL = pNominal,
            P1.AMOUNT = pAmount,
            P1.LEGAL_RATIO = pLEGAL_RATIO
        WHERE
            P1.ID_BREACH_PED_ITM = vID_BREACH_PED_ITM;        
    END IF;     
END;

Je n'utilise pas encore la valeur de retour de la création de l'enregistrement mais je la récupère quand même au cas où...

++

Laurent

**mnitu** · 22/06/2010, 09h27

Envoyé par Laurent Jordi

Bon, j'ai opté pour une solution moins élégante...
...

Non, vous avez optez pour une solution erronée parce que vous ne comprenez pas la manière de fonctionnement d’Oracle.
Avec Oracle votre traitement devient quelque chose de type

Code :

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

1
2
3
4
5
 
  insert into table ...
  exception
    when dup_val_on_index then
       update table

Mieux encore utilisez Merge avec dual comme vous suggère Waldar, ; il y a pas mal des exemples sur ce forum

Code :

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

1
2
3
4
5
6
 
  merge into table d
    using (select constant
             from dual) s             
  when not matched ...
  when matched ...

CUCARACHA · 22/06/2010, 10h12

Bonjour,

Merci pour ces précieuses informations...

Le problème n'est pas que je ne comprends pas Oracle mais plutôt que je ne le connais pas encore assez bien... Je ne suis pas inquiet, ça finira bien par rentrer d'autant que parti comme ça l'est, je devrais travailler dessus pendant encore 2 ans et demi et là, je n'ai commencé que depuis quelques semaines.

Bonne journée

Laurent

[10g] Package récalcitrant avec MERGE

PL/SQL Oracle

Vue hybride

Discussions similaires

Partager

Partager