Actualiser une table en fonction d'une autre

Version imprimable

Voir 40 message(s) de cette discussion en une page

Bonjour je suis face au problème suivant et je sollicite votre aide.
J'ai sur deux serveurs oracle (orcl1 et orcl2) la même table compagnie avec la structure suivante.

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
 
compagnie_orcl1
 
username | id | compagnie| seq
--------------------------------------
USER1     |us1id1| CMR| 123
USER1     |us1id1| RWD| 456
USER1     |us1id1| SING| 789
USER1     |us1id1| CNG | 1011
------------------------------------
USER2     |us2id2| SEN | 1213
USER2     |us2id2| CND | 1415
USER2     |us2id2| CGT | 1617
USER2     |us2id2| CNG | 1819
USER2     |us2id2| KPG | 2021
------------------------------------
USER3     |us3id3| RWD| 2223
USER3     |us3id3| CND | 2425
USER3     |us3id3| CID  | 2627
USER3     |us3id3| CNG | 2829
USER3     |us3id3| SING| 3031

sa copie sur le serveur2 (orcl2)

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
 
compagnie_orcl2
username | id | compagnie |seq
-------------------------------------
USER1     |us1id1| RWD|0010
USER1     |us1id1| SING|0011
USER1     |us1id1| CNG |0012
------------------------------------
USER2     |us2id2| SEN|0013
USER2     |us2id2| CNG|0014
USER2     |us2id2| KPG|0015
-----------------------------------
USER3     |us3id3| RWD|0016
USER3     |us3id3| CND|0017
USER3     |us3id3| SING|0018

J'aimerai pouvoir ajouter dans la table compagnie_orcl2 toutes les lignes de la table compagnie_orcl1 commencant par "C" et qui n' y sont pas encore.
merci pour votre aide

Code:

1
2
3
4
5
6
7
8
9
insert into compagnie_orcl2 (username, id, compagnie)
select c1.username, c1.id, c1.compagnie
  from compagnie_orcl1 c1
 where c1.compagnie like 'C%'
   and not exists (select 1
                     from compagnie_orcl2 c2
                    where c1.username  = c2.username 
                      and c1.id        = c2.id
                      and c1.compagnie = c2.compagnie)

Si tu as un DBLINK, il est parfois plus rapide de le gérer en MINUS

Code:

1
2
3
4
5
6
7
8
INSERT INTO COMPAGNIE_ORCL2@db2 (username, ID, compagnie)
SELECT username, ID, compagnie
FROM COMPAGNIE_ORCL1
WHERE (username, ID, compagnie) IN 
  (SELECT username, ID, compagnie FROM COMPAGNIE_ORCL1  WHERE compagnie LIKE 'C%'
  MINUS
  SELECT username, ID, compagnie FROM COMPAGNIE_ORCL12@db2 WHERE compagnie LIKE 'C%'
  )

skuatamad et McM merci pour vos réponses.

skuatamad par ce qu'avec ta solution j'ai eu 278 lignes en retour je me suis décidée à faire l'insertion avec un curseur comme suit:

**** Correction j'ai actualisée la table en ajoutant la colonne seq que j'avais oubliée.****
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 create or replace function comp (p_error OUT VARCHAR2) RETURN BOOLEAN is cursor compa_cur is select * from compagnie_orcl1@orcl1 c1 where c1.compagnie like 'C%' and not exists (select 1 from compagnie_orcl2 c2 where c1.username = c2.username and c1.id = c2.id and c1.compagnie = c2.compagnie); compa_tmp compa_cur%ROWTYPE; begin open compa_cur ; loop fetch compa_cur into compa_tmp; exit when compa_cur%NOTFOUND; insert into compagnie_orcl2 (USERNAME, compagnie, id, Seq) values (compa_cur.username, compa_cur.compagnie, compa_cur.id, Seq_cmp.nextval); commit; end loop; close compa_cur; return true; end comp;
seulement l'insertion ne fonctionne pas telle que je veux. c' est à dire que pour un enregistrement une seule des lignes commencant par 'C' sera insérée et pas les autres.
j'ai l'impression que la récursion ne fonctionne pas.

27/08/2019, 11h40
Scriuiw

Pourquoi utiliser un curseur alors qu'une instruction ensembliste fait l'affaire et sera bien plus efficace ?
Par ailleurs l'abus de COMMIT est à éviter, dans votre code vous en effectuez un à chaque boucle ce qui est très contre-productif.

Sinon la fonction compile ?
Je trouve étonnant qu'une fonction ait une valeur de retournée (RETURN BOOLEAN) et un paramètre sortant (p_error OUT VARCHAR2)
D'ailleurs, mise à part une erreur, votre fonction retournera toujours vrai qu'il y ait eu ou non des lignes insérées, l'intérêt s'en trouve limité.

Citation:
Envoyé par McM
Si tu as un DBLINK, il est parfois plus rapide de le gérer en MINUS
Code:

1 2 3 4 5 6 7 8 INSERT INTO COMPAGNIE_ORCL2@db2 (username, ID, compagnie) SELECT username, ID, compagnie FROM COMPAGNIE_ORCL1 WHERE (username, ID, compagnie) IN (SELECT username, ID, compagnie FROM COMPAGNIE_ORCL1 WHERE compagnie LIKE 'C%' MINUS SELECT username, ID, compagnie FROM COMPAGNIE_ORCL12@db2 WHERE compagnie LIKE 'C%' )
merci MCM
malheureusement je n'obtiens aucun résultat avec cette requête.

Visiblement tu te positionnes sur la base 2, mon select est fait pour être utilisé sur la base 1, car c'est là où sont les données.
Mais tu peux changer

Ce sous select doit te ramener tous les éléments distincts, mais tu as rajouté la colonne seq. Quelle est la PK ?

Code:

1
2
3
4
(SELECT username, ID, compagnie FROM COMPAGNIE_ORCL1@orcl1  WHERE compagnie LIKE 'C%'
  MINUS
  SELECT username, ID, compagnie FROM COMPAGNIE_ORCL2 WHERE compagnie LIKE 'C%'
  )

Si la PK est username, id, compagnie

Code:

1
2
3
4
5
6
7
8
INSERT INTO COMPAGNIE_ORCL2 (username, ID, compagnie, seq)
SELECT username, ID, compagnie, seq
FROM COMPAGNIE_ORCL1@orcl1
WHERE (username, ID, compagnie) IN 
  (SELECT username, ID, compagnie FROM COMPAGNIE_ORCL1@orcl1  WHERE compagnie LIKE 'C%'
  MINUS
  SELECT username, ID, compagnie FROM COMPAGNIE_ORCL2 WHERE compagnie LIKE 'C%'
  )

si la pk est username,id,compagnie,seq

Code:

1
2
3
4
5
6
7
8
INSERT INTO COMPAGNIE_ORCL2 (username, ID, compagnie, seq)
SELECT username, ID, compagnie, seq
FROM COMPAGNIE_ORCL1@orcl1
WHERE (username, ID, compagnie,seq) IN 
  (SELECT username, ID, compagnie,seq FROM COMPAGNIE_ORCL1@orcl1  WHERE compagnie LIKE 'C%'
  MINUS
  SELECT username, ID, compagnie,seq FROM COMPAGNIE_ORCL2 WHERE compagnie LIKE 'C%'
  )

27/08/2019, 11h54
teddi

Scriuiw merci également pour ta réponse.
je ne suis malheureusement pas sûre d'avoir compris grand chose. pourrais-tu être stp plus explicite?

Citation:

Envoyé par Scriuiw

Pourquoi utiliser un curseur alors qu'une instruction ensembliste fait l'affaire et sera bien plus efficace ?
aurais-tu un exemple stp?

Par ailleurs l'abus de COMMIT est à éviter, dans votre code vous en effectuez un à chaque boucle ce qui est très contre-productif.
le COMMIT est en fin de boucle. où devrais-je le mettre sinon?

Sinon la fonction compile ?
oui la fonction compile.

D'ailleurs, mise à part une erreur, votre fonction retournera toujours vrai qu'il y ait eu ou non des lignes insérées, l'intérêt s'en trouve limité.
n'étant pas forcément un as de la programmation j'essaie justement par ce post de comprendre mon erreur.
27/08/2019, 11h59
teddi

McM la PK est seq.

effectivement je me positionne sur la base2. merci je ressaie ton select.

Il fonctionne à merveille merci. Je parle du select. l'insertion est pour moi encore floue.
27/08/2019, 12h18
McM

Pourquoi floue ?

Un insert select, c'est facile, tu exécutes que le selec, tu vois toutes les lignes.. et bien si tu fais un insert select, toutes les lignes du select seront insérées.*

* bien sûr sous réserve qu'il n'y ait pas une erreur sur une ligne (contrainte, etc..)
27/08/2019, 12h36
teddi

merci MCM.
floue par ce que la fonction que j'ai postée ne fonctionne pas.

mais si tu es d'avis que seul l'insert tel que skuatamad et toi avez proposé suffit et qu'une fonction est dans ce cas est obsolète. dans ce cas tout est clair.
Mais ton insert ne prend pas en compte la PK SEQ c' est pour ca que je pensais qu'un curseur était indispensable. surtout pour gérer la récursion.

Citation:

Pourquoi utiliser un curseur alors qu'une instruction ensembliste fait l'affaire et sera bien plus efficace ?
aurais-tu un exemple stp?

ton curseur comparé à insert/Select tels que proposés

Citation:

Par ailleurs l'abus de COMMIT est à éviter, dans votre code vous en effectuez un à chaque boucle ce qui est très contre-productif.
le COMMIT est en fin de boucle. où devrais-je le mettre sinon?

pas de commit dans une boucle (sauf cas très rares), il faut le mettre à la fin du traitement
exemple
Code:

1 2 3 4 loop insert into compagnie_orcl2 (USERNAME, ..); end loop; commit;
Citation:

D'ailleurs, mise à part une erreur, votre fonction retournera toujours vrai qu'il y ait eu ou non des lignes insérées, l'intérêt s'en trouve limité.
n'étant pas forcément un as de la programmation j'essaie justement par ce post de comprendre mon erreur.

A titre uniquement informatif (car comme dit plus haut, un insert/select sera plus performant qu'une boucle), voici comment serait le curseur.
1/ Privilégier le FOR LOOP que le OPEN CURSEUR qui doit être fermé, affecté, etc...
2/ Toujours penser à simplifier : Less code, less bug.

exemple de fonction qui retourne le nombre d'enregistrements créés
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 CREATE OR REPLACE FUNCTION comp RETURN NUMBER IS v NUMBER := 0; BEGIN FOR rc IN (SELECT username, ID, compagnie, seq FROM COMPAGNIE_ORCL1@orcl1 c1 WHERE c1.compagnie LIKE 'C%' AND NOT EXISTS (SELECT 1 FROM COMPAGNIE_ORCL2 c2 WHERE c1.username = c2.username AND c1.ID = c2.ID AND c1.compagnie = c2.compagnie AND c1.seq = c2.seq)) -- rajout du seq, il faut tous les cas LOOP v := v + 1; INSERT INTO compagnie_orcl2 (USERNAME, COMPAGNIE, ID, SEQ) VALUES (rc.username, rc.compagnie, rc.ID, rc.seq); -- pourquoi avoir mis nextval ? END LOOP; RETURN v; END comp;
Pourquoi avoir mis un nextval dans seq ? Si tu doit mettre la séquence de la base locale, alors la définition du problème n'est pas bonne, et il manquerait la règle de gestion pour cette colonne.

27/08/2019, 14h18
teddi

merci infiniment pour ces précision McM.

le traitement de la seq n' est pas encore très claire mais peut etre qu'il te faut d'abord ma reponse.

j' ai besoin du nexval par ce que orcl2 etant une coopie de orcl1, la sequence est definie séparément et independament sur les 2 bases.
ce qui fait que je ne peux pas tout simplement récupérer les valeurs de seq sur orcl1 dans orcl2.
27/08/2019, 15h47
Scriuiw

[QUOTE]Pourquoi utiliser un curseur alors qu'une instruction ensembliste fait l'affaire et sera bien plus efficace ?
aurais-tu un exemple stp?
Voir l'exemple de McM ou Skuatamad

Citation:

Par ailleurs l'abus de COMMIT est à éviter, dans votre code vous en effectuez un à chaque boucle ce qui est très contre-productif.
le COMMIT est en fin de boucle. où devrais-je le mettre sinon?

Non le COMMIT est dans la boucle et ce n'est vraiment pas préconisé, il devrait plutôt après le "END LOOP;"

Citation:

Sinon la fonction compile ?
oui la fonction compile.

Citation:

D'ailleurs, mise à part une erreur, votre fonction retournera toujours vrai qu'il y ait eu ou non des lignes insérées, l'intérêt s'en trouve limité.
n'étant pas forcément un as de la programmation j'essaie justement par ce post de comprendre mon erreur.

Quel est l'intérêt pour vous d'avoir une fonction ?
Y a t-il un besoin spécifique ?
27/08/2019, 17h24
teddi

Merci pour ta réponse.
Les exemples envoyés par McM et Skuamat sont parfaits et je les ai déjà considérés.

Pour répondre à ta question: j'ai pensé utiliser une fonction par ce que j'avais la séquence à gérer.
Sachant que ce sont 2 séquences créés séparément l'une de l'autre. Il ne m'est donc pas possible de récupérer les valeurs de la séquence sur orcl1 et de les ajouter dans orcl2.

Je ne sais pas si tu as les droits suffisants pour créer un TRIGGER sur ta table ( celle dans laquelle tu écris).
Si oui, voici une piste.

Tu fais ce INSERT/SELECT :
Code:

1 2 3 4 5 6 7 8 9 INSERT INTO COMPAGNIE_ORCL2 (username, ID, compagnie, seq) SELECT username, ID, compagnie, NULL As seq FROM COMPAGNIE_ORCL1@orcl1 WHERE (username, ID, compagnie) IN (SELECT username, ID, compagnie FROM COMPAGNIE_ORCL1@orcl1 WHERE compagnie LIKE 'C%' MINUS SELECT username, ID, compagnie FROM COMPAGNIE_ORCL2 WHERE compagnie LIKE 'C%' )
La seule chose qui m'intéresse, c'est ce que j'ai mis en rouge : Tu mets NULL dans la colonne SEQ ... Pour le reste, j'ai repris telle quelle la requête de MCM, je n'ai pas vérifié si elle te convenait.

MAIS Avant de lancer ce Insert/Select , tu crées un Trigger sur ta table, pour dire :

Citation:

Si on essaie d'insérer une ligne avec NULL dans la colonne SEQ, alors remplacer NULL par Seq_cmp.nextval

Le trigger en question traitera ton insert, mais il s'appliquera aussi à chaque fois que tu tenteras d'insérer une ligne avec NULL dans la colonne SEQ.

tbc92 merci beaucoup pour cette nouvelle piste. je n'avais pas pensé au trigger du tout.

voici comment j' ai l'intention de l'écrire. Ce serait ok?
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 CREATE OR REPLACE TRIGGER secure_comp BEFORE INSERT OR UPDATE OF seq ON COMPAGNIE_ORCL2 FOR EACH ROW BEGIN IF INSERTING THEN :new.seq := Seq_cmp.nextval; ELSE IF :old.seq IS NULL THEN :new.seq := Seq_cmp.nextval; END IF; END IF; END;

28/08/2019, 09h15
Scriuiw

Attention s'il y a des intégrations en masse, le trigger va écrouler les performances.
Et faire une insertion à NULL dans une PK, je trouve l'idée scabreuse...
28/08/2019, 09h22
teddi

merci Scriuiw.

Citation:

Envoyé par Scriuiw

Attention s'il y a des intégrations en masse, le trigger va écrouler les performances.
Et faire une insertion à NULL dans une PK, je trouve l'idée scabreuse...

Qu'est ce que tu me proposes donc pour gérer ma séquence. c' est en fait le seul point qui me manque.
28/08/2019, 09h59
McM
Citation:

Envoyé par Scriuiw

Attention s'il y a des intégrations en masse, le trigger va écrouler les performances.
Et faire une insertion à NULL dans une PK, je trouve l'idée scabreuse...

Un trigger comme ça qui écroule les perfs ? Je ne pense pas.

Une insertion à NULL, oui, ou sans la définir (bon, généralement c'est une seule colonne ID), mais ça m'arrive.

Ensuite le trigger est mal formé je pense
Code:

1 2 3 IF INSERTING THEN :new.seq := Seq_cmp.nextval; ELSIF :old.seq IS NULL THEN :new.seq := Seq_cmp.nextval; END IF;
Premier test : Si tu insères une ligne avec une valeur dans seq, tu vas la remplacer par seq_cmp.nextval
Second test : Si tu updates une ligne qui a seq à NULL.. normalement c'est pas possible si c'est la PK, et ensuite tu remplaces la valeur que tu mets dans l'update par seq_cmp.nextval.

Pas très logique

Voir 40 message(s) de cette discussion en une page