delete avec sql dynamique

Version imprimable

Bonjour à tous,

Je voudrais faire un script qui va deleter les données de toutes les tables de ma base selon certaines conditions.

J'utilise un curseur pour récupérer les noms des tables (pour optimiser la maintenance vu que je sais pas on va ajouter ou supprimer des tables dans le futur).

Ensuite dans ma boucle je pose une variable de type varchar qui sera le contenu de ma requête, que j'exécute ensuite via un "execute immediate".

Mon problème : ça ne marche pas! (lol)
Pour entrer dans les détails : quand il n'y a aucune donnée dans les tables : ça passe nickel. (tu m'étonnes!) Le script se termine et tout va bien.
Mais quand on a des données (environ 500 000 lignes) ça mouline dans le vide et il ne delete rien.

Voici mon code :

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 PROCEDURE BOS.Suppression_des_erreurs_BOS 
    ( 
    p_DateFonc IN VARCHAR2,
    p_IDProvenance IN INTEGER
    ) IS
 
 
    CURSOR c_NomTable IS            --Curseur qui récupère le nom de toutes les tables de la base BOS        
        SELECT TABLE_NAME        
        FROM ALL_TABLES         
        WHERE OWNER='BOS';
 
    l_NomTable c_NomTable%ROWTYPE;  --Ligne de lecture du curseur  
 
    v_DateFonc varchar2(10);        --Date à supprimer en paramètre
    v_IDProvenance varchar2(10);    --ID Provenance
    v_Requete varchar2(500);        --Chaine de requête SQL dynamique
 
 
 
 
    BEGIN
 
        v_DateFonc := p_DateFonc;
        v_IDProvenance := TO_CHAR(p_IDProvenance);
 
        OPEN c_NomTable;            --Ouverture du curseur
 
        LOOP
            FETCH c_NomTable INTO l_NomTable;
            EXIT WHEN c_NomTable%NOTFOUND; --Sortir à la fin du curseur
 
            v_REQUETE := 'DELETE FROM ' || l_NomTable.TABLE_NAME || 
                         ' WHERE DAT_MAJ_PHT >=''' || v_DateFonc ||
                         ''' AND ID_PROVENANCE =''' || v_IDProvenance || '''';
            EXECUTE IMMEDIATE v_REQUETE;
        END LOOP;
 
        CLOSE c_NomTable;           --Fermeture du curseur
 
    END;
/

Merci de votre aide :oops:

03/06/2010, 18h41
Waldar

Est-ce que vous avez des triggers et des index ?

Est-ce que vos tables sont indexées sur les colonnes du delete (DAT_MAJ_PHT, ID_PROVENANCE) ?
03/06/2010, 19h03
dessinateurttuyen

Oui effectivement j'ai des index sur ces deux champs. Est ce que ça peut jouer?
03/06/2010, 20h56
jpebacher

formatage de la date
Lorsque tu convertis ton paramètre p_IDProvenance, j'ajouterais un format qui correspondrait à celui de la table.

Par exemple,
Code:

1 2 v_IDProvenance := TO_CHAR(p_IDProvenance, 'YYYY-MM-DD');
04/06/2010, 07h42
SheikYerbouti

Attention aux dates, c'est basique.

Vos variables sont en VARCHAR, il faut donc faire une conversion en date avant de comparer. De plus To_Char() sir une variable CHAR n'est pas la solution. Il faut utiliser To_Date() et comparer avec la colonne DATE de la base.
04/06/2010, 10h19
McM

1/ Mieux vaut utiliser des bind variables.

2/ Les colonnes id_provenance sont en VARCHAR2 ou en NUMBER ?
Parce qu'il ne faut pas forcer la base à faire du cast.

3/ Tu ne commites pas dans la boucle ? Ton Undo est suffisant pour absorber le delete
08/06/2010, 10h30
dessinateurttuyen

Bonjour,

Merci à vous pour toutes vos réponses.

Alors pour répondre à vos questions :
ID_PROVENANCE est un INTEGER dans toutes mes tables. Et dans la procédure je le déclare en tant qu'INTEGER.

Sinon effectivement le bind variable que je ne connaissais pas m'a permis de réduire efficacement le temps d'exécution. Un grand merci donc! :ccool:
08/06/2010, 10h47
Waldar

N'hésitez pas à mettre le nouveau code, ça servira pour les recherches !

Voici mon code :

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
 
CREATE OR REPLACE PROCEDURE BDM.suppression_des_erreurs_BDM (
   p_datefonc       IN   VARCHAR2,
      --Paramètre 1 : date à partir de laquelle on supprime toutes les données
   p_idprovenance   IN   INTEGER
                       --Paramètre 2 : ID du site dont on supprime les données
)
IS
/*
Déclaration des variables
*/
 
   --Curseur qui récupère le nom de toutes les tables de la base BDM
   CURSOR c_nomtable
   IS
      SELECT table_name
        FROM all_tables
       WHERE owner = 'BDM';
 
   l_nomtable       c_nomtable%ROWTYPE;         --Ligne de lecture du curseur
   v_datefonc       VARCHAR2 (10);            --Date à supprimer en paramètre
   v_idprovenance   VARCHAR2 (10);               --ID Provenance en paramètre
   v_requete        VARCHAR2 (5000);        --Chaine de requête SQL dynamique
/*
Corps de la procédure
*/
BEGIN
   v_datefonc := TO_DATE (p_datefonc, 'DD/MM/YYYY');
   v_idprovenance := p_idprovenance;
 
   OPEN c_nomtable;                                    --Ouverture du curseur
 
   LOOP
      FETCH c_nomtable
       INTO l_nomtable;
 
      EXIT WHEN c_nomtable%NOTFOUND;             --Sortir à la fin du curseur
      v_requete :=                         --Création de la requête dynamique
            'DELETE FROM '
         || l_nomtable.table_name
         || ' WHERE DAT_MAJ_PHT >=:v_tmp1 AND ID_PROVENANCE =:v_tmp2';
 
      EXECUTE IMMEDIATE v_requete
                     --Exécution de la requête avec un bind multiple variables
                  USING v_datefonc, v_idprovenance;
 
      --Message de confirmation à chaque suppression de données dans une table
      DBMS_OUTPUT.put_line ('Table ' || l_nomtable.table_name || ' nettoyee.');
   END LOOP;
 
   CLOSE c_nomtable;                                    --Fermeture du curseur
 
   COMMIT;
END;
/

08/06/2010, 17h49
Waldar

Merci pour les suivants.

Une dernière remarque, pourquoi ne pas directement typer p_datefonc en date ?
Vous pourriez directement utiliser vos paramètres au lieu de passer par deux variables.

Le code est pas mal, mais il reste des erreurs et quelques optis possible
Les erreurs : v_datefonc est une date ou une chaine ?
v_idprovencance : Integer ou Chaine ?

Code:

1
2
3
4
5
6
v_datefonc       VARCHAR2 (10);  
v_datefonc := TO_DATE (p_datefonc, 'DD/MM/YYYY');
 
p_idprovenance   IN   INTEGER
v_idprovenance   VARCHAR2 (10);   
v_idprovenance := p_idprovenance;

Voici un code avec la solution de waldar + mes optis (simplification de curseur)

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
CREATE OR REPLACE PROCEDURE BDM.suppression_des_erreurs_BDM (
   p_datefonc       IN   DATE,    --Paramètre 1 : date à partir de laquelle on supprime toutes les données
   p_idprovenance   IN   INTEGER      --Paramètre 2 : ID du site dont on supprime les données
)
IS
   v_requete        VARCHAR2 (5000);        --Chaine de requête SQL dynamique
BEGIN
 
   --Curseur qui récupère le nom de toutes les tables de la base BDM
   FOR l_nomtable IN (SELECT table_name
        FROM all_tables
       WHERE owner = 'BDM') 
   LOOP
 
      v_requete :=                         --Création de la requête dynamique
            'DELETE FROM '
         || l_nomtable.table_name
         || ' WHERE DAT_MAJ_PHT >=:v_tmp1 AND ID_PROVENANCE =:v_tmp2';
 
      --Exécution de la requête avec un bind multiple variables
      EXECUTE IMMEDIATE v_requete USING p_datefonc, p_idprovenance;
 
      --Message de confirmation à chaque suppression de données dans une table
      DBMS_OUTPUT.put_line ('Table ' || l_nomtable.table_name || ' nettoyee.');
   END LOOP;
 
   COMMIT;
END;
/