Optimisation traitement SQL

**decisio** · 20/06/2012, 10h56

Bonjour,
J'ai créé plusieurs requêtes sql du type
Insert tableA ( select .... from tableB).
Je souhaite voir le nombre de ligne insérées de chaque table et si possible insérer l'information dans une table de log.
Je souhaite avoir cette information pour chaque requête.
Je travail sous oracle et pl sql develloper

Voici un exemple

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
Begin 
 
INSERT INTO  tableA (DATE_TRT,ENT, BAT)
 
(SELECT 
trunc(sysdate - 1) AS DATE_TRT,
ENT,
 BAT
from tableB
 
UNION
 
SELECT 
trunc(sysdate - 1) AS DATE_TRT,
ENT,
 BAT
from tableC );
 
 
 
INSERT INTO tableD
( JOUR,
ENT,
BAT)
 
(Select 
JOUR,
ENT,
BAT
FROM tableE
 
UNION
 
Select 
JOUR,
ENT,
BAT
FROM tableF );
end;
/

Merci de votre aide

**Scriuiw** · 20/06/2012, 11h12

via un package/procédure PL/SQL il serait aisé de récupérer le nombre de lignes insérées (SQL%ROWCOUNT) pour chaque requête exécutée et insérer le tout dans une table de log.

Il serait aussi facile de gérer les éventuelles erreurs.

**decisio** · 20/06/2012, 11h37

Envoyé par Scriuiw

via un package/procédure PL/SQL il serait aisé de récupérer le nombre de lignes insérées (SQL%ROWCOUNT) pour chaque requête exécutée et insérer le tout dans une table de log.

Il serait aussi facile de gérer les éventuelles erreurs.

Merci
en utilisant cette fonction, le résultat est OK
Par contre, avez vous une syntaxe pour la gestion des erreurs
D'avance merci

**Scriuiw** · 20/06/2012, 11h46

Les erreurs sont récupérées via des EXCEPTION en PL/SQL telles que :

Code :

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

1
2
3
EXCEPTION
  WHEN DUP_VAL_ON_INDEX THEN ... (Pour les clés dupliquées)
  WHEN OTHERS THEN ... (Pour tout autres types d'erreurs rencontrés)

A voir les erreurs que vous êtes susceptibles d'avoir et de les "catcher" proprement

**decisio** · 20/06/2012, 12h11

Envoyé par Scriuiw

Les erreurs sont récupérées via des EXCEPTION en PL/SQL telles que :

Code :

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

1
2
3
EXCEPTION
  WHEN DUP_VAL_ON_INDEX THEN ... (Pour les clés dupliquées)
  WHEN OTHERS THEN ... (Pour tout autres types d'erreurs rencontrés)

A voir les erreurs que vous êtes susceptibles d'avoir et de les "catcher" proprement

oK MERCI
pour le premier point, j'ai plusieurs requêtes insert à exécuter dans le même PL.
Je ne vois pas comment passer d'une requête insert à l'autre requête tout en utilisant la même variable.

**Scriuiw** · 20/06/2012, 13h49

Soit tu enregistres dans ta table LOG le nombre de linges insérées après chaque insertion ou alors tu déclares autant de variables que nécessaire pour stocker les différents résultats et enregistrer dans la LOG en fin de traitement.

Peux-tu poster ton code pour que l'on voit mieux où tu en es ?

**decisio** · 20/06/2012, 17h50

encore merci de ton aide
voici le code
le besoin est de stocker dans une table log le nom de la table alimenter TableA dans l'exemple , le nombre de lignes ainsi que la date du traitement ( sysdate).
Sachant que dans mon PL, il y a plusieurs table à alimenter ( plusieurs requêtes sql d'insert qui s’enchaînent)
ENCORE MERCI

Code sql :

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
   Create or Replace
PROCEDURE ALIM_BASE
IS 
LN$Cpt PLS_INTEGER ;
 
BEGIN 
 
 SELECT COUNT(*) INTO LN$Cpt FROM TABLEA ; 
  DBMS_OUTPUT.PUT_LINE( To_char( LN$Cpt ) || ' enreg_MASS') ; 
 
INSERT INTO TABLEA
( JOUR,
ENT,
BAT)
(
Select 
JOUR,
ENT,
BAT
FROM X
 
UNION
 
Select 
JOUR,
ENT,
BAT
FROM Y ) ;
DBMS_OUTPUT.PUT_LINE( To_char( SQL%ROWCOUNT ) || ' enreg_INSERT_MASS');
end;

**Scriuiw** · 21/06/2012, 09h20

Dans ce cas je te conseillerais de mettre ta procédure actuelle dans un package auquel tu ajoutes une procédure qui fera les insert dans ta table de LOG et de l'appeler après chaque INSERT en transmettant le SQL%ROWCOUNT

Par exemple :

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
CREATE OR REPLACE mon_pkg IS
  PROCEDURE log (
    pe_table IN VARCHAR2,
    pe_dml IN VARCHAR2,
    pe_nblignes IN NUMBER,
    pe_date IN DATE DEFAUT TRUNC(sysdate));
 
  PROCEDURE alim_base;
 
END Mon_pkg;
/
 
CREATE OR REPLACE PACKAGE BODY mon_pkg IS
 
PROCEDURE log (
    pe_table IN VARCHAR2,
    pe_dml IN VARCHAR2,
    pe_nblignes IN NUMBER,
    pe_date IN DATE DEFAUT TRUNC(sysdate));
BEGIN
  INSERT INTO table_log [..]
END log;
 
PROCEDURE ALIM_BASE IS 
LN$Cpt PLS_INTEGER ;
 
BEGIN 
 
 SELECT COUNT(*) INTO LN$Cpt FROM TABLEA ; 
  DBMS_OUTPUT.PUT_LINE( To_char( LN$Cpt ) || ' enreg_MASS') ; 
 
  INSERT INTO TABLEA
  ( JOUR,ENT,BAT)
  (SELECT JOUR,ENT,BAT
  FROM X
  UNION
  SELECT JOUR,ENT,BAT
  FROM Y ) ;
 
  LOG('TABLEA', 'INSERT', SQL%ROWCOUNT);
 
  [..]
 
END PROCEDURE ALIM_BASE;
END Mon_pkg;
/

**mnitu** · 21/06/2012, 09h59

Code :

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

1
2
3
4
 
...
SELECT COUNT(*) INTO LN$Cpt FROM TABLEA ; 
...

Ne sert à rien sauf à alourdir le traitement.

**McM** · 21/06/2012, 10h03

Pas besoin de t'embêter à créer un package à moins de vouloir réutiliser la procédure de log depuis un autre endroit, une simple procédure suffit.

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
PROCEDURE ALIM_BASE IS 
 
	LN$Cpt PLS_INTEGER ;
 
  PROCEDURE LOG (pe_table IN VARCHAR2, pe_dml IN VARCHAR2,
      pe_nblignes IN NUMBER, pe_date IN DATE DEFAUT SYSDATE) IS
  BEGIN
    INSERT INTO table_log [..]
  END LOG;
 
BEGIN 
 
  SELECT COUNT(*) INTO LN$Cpt FROM TABLEA ; 
  DBMS_OUTPUT.PUT_LINE( TO_CHAR( LN$Cpt ) || ' enreg_MASS') ; 
 	LOG('TABLEA', 'Count(*)', LN$Cpt);
 
  INSERT INTO TABLEA  ( JOUR,ENT,BAT)
  SELECT JOUR,ENT,BAT  FROM X
  UNION
  SELECT JOUR,ENT,BAT  FROM Y;
 
  LOG('TABLEA', 'INSERT', SQL%ROWCOUNT);
 
END ALIM_BASE;

**mnitu** · 21/06/2012, 10h09

Il n’y a rien d’embêtant dans la création d’un package et en plus la procédure pourrait être utilisée ailleurs. Quoi que dans ce cas elle doive appartenir à un autre package.

**Scriuiw** · 21/06/2012, 14h04

L'organisation interne des packages et procédures est à l'initiative du demandeur je ne proposais là qu'une idée.

Tout dépend si la procédure de log peut être ré-utilisée ailleurs ou pas et si la création des données est du "one-shot" ou périodique.

**decisio** · 22/06/2012, 11h31

Envoyé par McM

Pas besoin de t'embêter à créer un package à moins de vouloir réutiliser la procédure de log depuis un autre endroit, une simple procédure suffit.

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
PROCEDURE ALIM_BASE IS 
 
	LN$Cpt PLS_INTEGER ;
 
  PROCEDURE LOG (pe_table IN VARCHAR2, pe_dml IN VARCHAR2,
      pe_nblignes IN NUMBER, pe_date IN DATE DEFAUT SYSDATE) IS
  BEGIN
    INSERT INTO table_log [..]
  END LOG;
 
BEGIN 
 
  SELECT COUNT(*) INTO LN$Cpt FROM TABLEA ; 
  DBMS_OUTPUT.PUT_LINE( TO_CHAR( LN$Cpt ) || ' enreg_MASS') ; 
 	LOG('TABLEA', 'Count(*)', LN$Cpt);
 
  INSERT INTO TABLEA  ( JOUR,ENT,BAT)
  SELECT JOUR,ENT,BAT  FROM X
  UNION
  SELECT JOUR,ENT,BAT  FROM Y;
 
  LOG('TABLEA', 'INSERT', SQL%ROWCOUNT);
 
END ALIM_BASE;

merci de vos retour tous..
Je vous remercie de m'aider car je débute vraiment sur pl
je n'arrive pas à compliter le PL après la modification
Je crois que c'est lié à ma table log qui le format suivant :
DATE_TRAITEMENT date
NOM_TABLE varchar 2
NB_LIGNES_INSEREES NUMBER

Code sql :

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
 
create or replace
PROCEDURE ALIM_TEST2 IS 
 
	LN$Cpt PLS_INTEGER ;
 
  PROCEDURE LOG (pe_table IN VARCHAR2, pe_dml IN VARCHAR2,
      pe_nblignes IN NUMBER, pe_date IN DATE DEFAUT SYSDATE) IS
  BEGIN
   INSERT INTO RDO_LOG ( 
               NOM_TABLE,
              NB_LIGNES_INSEREES,
              DATE_TRAITEMENT)
 
END LOG;
 
BEGIN 
 
  SELECT COUNT(*) INTO LN$Cpt FROM TABLEA   ; 
  DBMS_OUTPUT.PUT_LINE( TO_CHAR( LN$Cpt ) || ' enreg_MASS') ; 
 	LOG('TABLEA  ', 'Count(*)', LN$Cpt);
 
  INSERT INTO TABLEA  ( JOUR,ENT,BAT)
  SELECT JOUR,ENT,BAT  FROM X
  UNION
  SELECT JOUR,ENT,BAT  FROM Y;
 
 
  LOG('TABLEA', 'INSERT', SQL%ROWCOUNT);
 
END ALIM_BASE;

**mnitu** · 22/06/2012, 11h37

Regardez l'ordre des colonnes et surtout leur type entre la définition de la table et l'insert.

Envoyé par decisio

...
Je crois que c'est lié à ma table log qui le format suivant :
DATE_TRAITEMENT date
NOM_TABLE varchar 2
NB_LIGNES_INSEREES NUMBER

Envoyé par decisio

...
INSERT INTO RDO_LOG (
NOM_TABLE,
NB_LIGNES_INSEREES,
DATE_TRAITEMENT)

....

**decisio** · 22/06/2012, 11h43

Envoyé par mnitu

Regardez l'ordre des colonnes et surtout leur type entre la définition de la table et l'insert.

L'ordre est le type est OK
c'est cette procédure que je ne comprends pas !

Code sql :

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

1
2
3
4
5
6
7
8
9
10
 
 PROCEDURE LOG (pe_table IN VARCHAR2, pe_dml IN VARCHAR2,
      pe_nblignes IN NUMBER, pe_date IN DATE DEFAUT SYSDATE) IS
  BEGIN
   INSERT INTO RDO_LOG ( 
               NOM_TABLE,
              NB_LIGNES_INSEREES,
              DATE_TRAITEMENT)
 
END LOG;

Les pe_table, pe_dml .. correspondent à des colonnes ?

**McM** · 22/06/2012, 11h53

Insert into Ok.. mais les valeurs ??

Code :

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

1
2
INSERT INTO RDO_LOG ( NOM_TABLE, NB_LIGNES_INSEREES, DATE_TRAITEMENT)
VALUES (pe_table, etc..);

peXX correspondent à des paramètres de la procédure

**decisio** · 22/06/2012, 13h08

Merci c'est Ok
Une erreur dans le script : il manquait le L dans DEFAULT ^^

Le script fonctionne, il inséré deux lignes dans une table de Log ( VOLUMETRIE AVANT ET VOLUMETRIE APRES INSERT)

par contre, mon script fait plusieurs insert dans plusieurs tables enchainement d'insert. comment faire pour rappeler la proc log dans le deuxième insert ? j'ai ecrit le code ci dessous ( insert2) mais cela ne fonctionne pas

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
[/CODE= SQL]

Create or replace
PROCEDURE ALIM IS 
 
	LN$Cpt PLS_INTEGER ;
 
 PROCEDURE LOG (pe_table IN VARCHAR2, pe_dml IN VARCHAR2,
      pe_nblignes IN NUMBER, pe_date IN DATE DEFAULT SYSDATE) IS
      
  BEGIN
   INSERT INTO RDO_LOG ( 
               NOM_TABLE,
               NB_LIGNES_INSEREES,
               DATE_TRAITEMENT)
  VALUES (pe_table,  pe_nblignes, pe_date );

END LOG;

BEGIN 
 
  SELECT COUNT(*) INTO LN$Cpt FROM TABLEA; 
  DBMS_OUTPUT.PUT_LINE( TO_CHAR( LN$Cpt ) || ' enreg_MASS') ; 
 	LOG('TABLEA', 'Count(*)', LN$Cpt);
 
   INSERT INTO TABLEA  ( JOUR,ENT,BAT)
  SELECT JOUR,ENT,BAT  FROM X
  UNION
  SELECT JOUR,ENT,BAT  FROM Y;

  LOG('TABLEA', 'INSERT', SQL%ROWCOUNT);
  
-- insert 2
      
   INSERT INTO RDO_LOG ( 
               NOM_TABLE,
               NB_LIGNES_INSEREES,
               DATE_TRAITEMENT)
  VALUES (pe_table,  pe_nblignes, pe_date );

    
SELECT COUNT(*) INTO LN$Cpt FROM TABLEB; 
  DBMS_OUTPUT.PUT_LINE( TO_CHAR( LN$Cpt ) || ' enreg_MASS') ; 
 	LOG('TABLEB', 'Count(*)', LN$Cpt);
	
	INSERT INTO TABLEB  ( JOUR,ENT,BAT)
  SELECT JOUR,ENT,BAT  FROM W
  UNION
  SELECT JOUR,ENT,BAT  FROM V;

  LOG('TABLEB', 'INSERT', SQL%ROWCOUNT);

END ALIM;

merci

**Scriuiw** · 22/06/2012, 14h24

La syntaxe semble bonne.
Y a t-il une erreur ?

**decisio** · 22/06/2012, 14h35

Oui, j'ai l'impression qu'il n'arrive pas à enchainer.

L'erreur est au niveau de

Code sql :

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

1
2
3
4
5
6
7
8
-- insert 2
      
   INSERT INTO RDO_LOG ( 
               NOM_TABLE,
               NB_LIGNES_INSEREES,
               DATE_TRAITEMENT)
  VALUES (pe_table,  pe_nblignes, pe_date );

sql statement ignored / column not allowed

**decisio** · 22/06/2012, 15h40

Envoyé par decisio

Oui, j'ai l'impression qu'il n'arrive pas à enchainer.

L'erreur est au niveau de

Code sql :

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

1
2
3
4
5
6
7
8
-- insert 2
      
   INSERT INTO RDO_LOG ( 
               NOM_TABLE,
               NB_LIGNES_INSEREES,
               DATE_TRAITEMENT)
  VALUES (pe_table,  pe_nblignes, pe_date );

sql statement ignorad / column not allowed

merci de votre aide, je suis bloqué sur ce point

Optimisation traitement SQL

PL/SQL Oracle

Discussions similaires

Partager

Partager