[PL/SQL] Faisabilité d'une comparaison champ à champ

[ReiVon]

Bonjour à tous.

Je me permets de vous quémander votre aide pour un problème que me pose une partie de mon projet.

Le sujet est pourtant "simple", je dois réaliser une "méthode" dont voici les composantes :

- En entrée de ma "méthode" j'ai un nom de table et deux identifiants de cette table (IdOld, IdNew).
- En sortie de ma "méthode" j'ai une liste de triplé "Nom champ - Ancienne valeur - Nouvelle valeur".

Comme vous l'aurez peut être deviné, cette "méthode" doit trouver les champs qui ont une valeur différente entre les deux records pour me les renvoyer en sortie. (donc sortie null si tout est identique).
Je ne connais pas d'avance les tables parcourues de cette manière et donc les champs à visiter (le nom de la table à traiter est passé en paramètre).

La seule chose que je sais, c'est que chaque table possède une colonne ID qui me permet de récupérer mes deux records selon les identifiants en paramètres.

Alors voilà, en Java, je sais faire tout ça par le biais de la réflexion, mais c'est couteux en ressource et pas très joli pour ce cas là ... Je voulais donc tout simplement savoir s'il n'y aurait pas une solution pour réaliser le même traitement mais en PL/SQL ?

Je ne connais malheureusement que très peu PL/SQL, j'ai trouvé des possibilités de récupérer les noms de colonne, j'ai pu trouver des notions de Dynamic SQL, etc, mais je n'arrive pas à coller tout ces bouts pour savoir si ma "méthode" est réellement faisable en PL/SQL et cela de manière simple et efficace ??

Je vous remercie beaucoup d'avance de votre aide !

[Mohamed.Houri]

Si j'ai bien compris votre besoin alors voici un exemple

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
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
 
mhouri > CREATE TABLE a (STID number, C1 number,  C2 number, C3 number);
 
TABLE created.
 
mhouri > INSERT INTO a VALUES (1, 20, 30, 40)
  2  ;
 
1 row created.
 
mhouri > INSERT INTO a VALUES (2, 40, 50, 60);
 
1 row created.
 
mhouri > INSERT INTO a VALUES (3, 90, 80, 100);
 
1 row created.
 
mhouri > CREATE TABLE b AS SELECT *
  2  FROM a WHERE 1 = 0;
 
TABLE created.
 
mhouri > INSERT INTO b VALUES (1, 10, 30, 40)
  2  ;
 
1 row created.
 
mhouri > INSERT INTO b VALUES (2, 40, 40, 70);
 
1 row created.
 
mhouri > INSERT INTO b VALUES (3, 90, 90, 100);
 
1 row created.
 
mhouri > commit;
 
Commit complete.
 
mhouri > SELECT * FROM a;
 
      STID         C1         C2         C3                                                                             
---------- ---------- ---------- ----------                                                                             
         1         20         30         40                                                                             
         2         40         50         60                                                                             
         3         90         80        100                                                                             
 
mhouri > SELECT * FROM b;
 
      STID         C1         C2         C3                                                                             
---------- ---------- ---------- ----------                                                                             
         1         10         30         40                                                                             
         2         40         40         70                                                                             
         3         90         90        100                                                                             
 
mhouri >  SELECT stid
  2         ,c1
  3         ,c2
  4         ,c3
  5         ,min(appl_1) appl_1
  6         ,min(appl_2) appl_2
  7     FROM
  8    ( SELECT a.*,
  9           1 src1,
 10           to_number(NULL) src2
 11           ,'table a' appl_1, NULL appl_2
 12     FROM  a
 13     UNION ALL
 14     SELECT b.*,
 15           to_number(NULL) src1,
 16           2  src2
 17           ,'table b' appl_1, NULL appl_2
 18      FROM b
 19     )
 20     GROUP BY stid , c1, c2, c3
 21     HAVING count(src1) !=  count(src2)
 22     ORDER BY stid;
 
      STID         C1         C2         C3 APPL_1  A                                                                   
---------- ---------- ---------- ---------- ------- -                                                                   
         1         10         30         40 TABLE b                                                                     
         1         20         30         40 TABLE a                                                                     
         2         40         40         70 TABLE b                                                                     
         2         40         50         60 TABLE a                                                                     
         3         90         80        100 TABLE a                                                                     
         3         90         90        100 TABLE b                                                                     
 
6 rows selected.
 
mhouri > UPDATE a SET c2=90 WHERE stid = 3;
 
1 row updated.
 
mhouri > SELECT stid
  2         ,c1
  3         ,c2
  4         ,c3
  5         ,min(appl_1) appl_1
  6         ,min(appl_2) appl_2
  7     FROM
  8    ( SELECT a.*,
  9           1 src1,
 10           to_number(NULL) src2
 11           ,'table a' appl_1, NULL appl_2
 12     FROM  a
 13     UNION ALL
 14     SELECT b.*,
 15           to_number(NULL) src1,
 16           2  src2
 17           ,'table b' appl_1, NULL appl_2
 18      FROM b
 19     )
 20     GROUP BY stid , c1, c2, c3
 21     HAVING count(src1) !=  count(src2)
 22     ORDER BY stid;
 
      STID         C1         C2         C3 APPL_1  A                                                                   
---------- ---------- ---------- ---------- ------- -                                                                   
         1         10         30         40 TABLE b                                                                     
         1         20         30         40 TABLE a                                                                     
         2         40         40         70 TABLE b                                                                     
         2         40         50         60 TABLE a

[ReiVon]

Bonjour Mohamed.

Malheureusement ce n'est pas exactement mon besoin.

Mes contraintes sont très fortes : le nom de la table m'est passé en paramètre. De ce fait je ne connais pas les colonnes à comparer.
Je sais juste qu'il y a une colonne ID.

Ma sortie doit être une liste de "Nom du champ - ancienne valeur - nouvelle valeur" pour tous les champs dont leur valeur est différente.

Durant cette journée j'ai essayé de creuser et je suis finalement arrivé à quelque chose.

Voici ma solution :

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
-----------------------------------------------------------
-- CREATE PACKAGE
CREATE OR REPLACE
PACKAGE auditPackage AS
 
-- Define type of a field change
TYPE T_REC_FIELD_CHANGE IS RECORD (
  fieldName VARCHAR2(100),
  oldValue VARCHAR2(100),
  newValue VARCHAR2(100)
);
 
-- Define table of records --
TYPE TAB_T_REC_FIELD_CHANGE IS TABLE OF T_REC_FIELD_CHANGE INDEX BY binary_integer;
 
-- Define the procedure
PROCEDURE GET_CHANGED_FILE(tableName IN VARCHAR2, idOld IN CHAR, idNew IN CHAR, listFieldChange OUT TAB_T_REC_FIELD_CHANGE);
 
END auditPackage;
/
 
-----------------------------------------------------------
-- CREATE PACKAGE BODY
CREATE OR REPLACE
PACKAGE BODY auditPackage AS
 
-- CREATE PROCEDURE
PROCEDURE GET_CHANGED_FILE(tableName IN VARCHAR2, idOld IN CHAR, idNew IN CHAR, listFieldChange OUT TAB_T_REC_FIELD_CHANGE)
IS
      -- Define a cursor on column name
      v_cursor SYS_REFCURSOR;
 
      -- Define a result counter
      resultCounter NUMBER(3);
 
      -- Request result variables 
      column_name VARCHAR(50);
      column_values_new VARCHAR2(100);
      column_values_old VARCHAR2(100);
 
      -- SQL Variables
      var_sql_column_name VARCHAR(1000);
      var_sql_old VARCHAR(1000);
      var_sql_new VARCHAR(1000);
 
BEGIN
 
      resultCounter := 1;
 
      var_sql_column_name := 'Select COLUMN_NAME from USER_TAB_COLUMNS where TABLE_NAME = upper(''' || tableName || ''') AND COLUMN_NAME NOT IN ';
      var_sql_column_name := var_sql_column_name || '(''ID'', ''DELETED_BY'', ''DELETION_DATE'', ''LAST_UPDATED_BY'', ''MODIFICATION_DATE'', ''CREATED_BY'', ''CREATION_DATE'')';
 
      OPEN v_cursor FOR var_sql_column_name;
      LOOP
          FETCH v_cursor INTO column_name;
          EXIT WHEN v_cursor%NOTFOUND;
 
          var_sql_old := 'SELECT ' || column_name || ' FROM ' || tableName || ' WHERE ID = ''' || idOld || '''';
          var_sql_new := 'SELECT ' || column_name || ' FROM ' || tableName || ' WHERE ID = ''' || idNew || '''';
 
          -- Execute the sql
          EXECUTE IMMEDIATE var_sql_old INTO column_values_old;
          EXECUTE IMMEDIATE var_sql_new INTO column_values_new;
 
          IF column_values_old != column_values_new THEN
            listFieldChange(resultCounter).fieldName := column_name ;
            listFieldChange(resultCounter).oldValue := column_values_old ;
            listFieldChange(resultCounter).newValue := column_values_new ;
 
            resultCounter := resultCounter + 1;
          END IF;
 
      END LOOP;
      CLOSE v_cursor;
END;
 
END auditPackage;

Un morceau de code pour l'exécuter :

Code :

1
2
3
4
5
6
7
8
9
10
 
SET SERVEROUTPUT ON;
DECLARE
  toto AUDITPACKAGE.TAB_T_REC_FIELD_CHANGE;
BEGIN
  AUDITPACKAGE.GET_CHANGED_FILE('INVOICE', 'b6e8dee7-ffcb-4130-998a-c45b8218db6d', '267dbb77-0bda-4827-920a-52b3a09aba85', toto);
   FOR i IN 1..(toto.COUNT) Loop
      DBMS_OUTPUT.PUT_LINE(toto(i).fieldName || '  ' || toto(i).oldValue || '  ' || toto(i).newValue);
   End loop ; 
END;

Alors avec cela je suis arrivé à mon but, ça fonctionne correctement, c'est beaucoup plus rapide que par introspection Java.

Par contre je ne trouve pas cela super élégant non plus, notamment le fait de devoir faire 2 requête pour chaque champ ...

Je ne suis pas expert PL SQL, il doit donc y avoir moyen d'optimiser tout cela. Si quelqu'un voit comment réaliser ça, je le remercie d'avance !

Sinon pour ceux qui avaient le même problème que moi, voici une solution

[Rams7s]

Le PLSQL a un avantage surpuissant par rapport a plein de choses, c'est qu'il peut utiliser le %rowtype. Et en plus, on peut faire des execute immediate.
Ca devient assez simple en melangeant ces deux choses de creer des variables qui vont bien, qu'importe la table fournie en entree.

Et l'autre truc assez pratique, c'est que %rowtype va utiliser directement les colonnes de la table, et on a acces a ces colonnes, via USER_TAB_COLUMNS

Tout le reste, c'est du blabla.

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
 
DROP TABLE tmp1;
 
CREATE TABLE tmp1 (n1 number, pad1 varchar2(40));
 
INSERT INTO tmp1 VALUES (1,'x');
INSERT INTO tmp1 VALUES (2,'x');
INSERT INTO tmp1 VALUES (3,'y');
INSERT INTO tmp1 VALUES (4,'x');
INSERT INTO tmp1 VALUES (5,'z');
INSERT INTO tmp1 VALUES (6,'x');
 
commit;

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
declare
   plsql_block varchar2(32000); --ok, c'est gros
   l_table_name varchar2(20):='tmp1';
begin
   plsql_block:='';
   plsql_block:=plsql_block||'declare'||chr(10);
   plsql_block:=plsql_block||'   type l_type is record (column_name user_tab_columns.column_name%type,'||chr(10);
   plsql_block:=plsql_block||'                        old_value varchar2(100),'||chr(10);
   plsql_block:=plsql_block||'                        new_value varchar2(100));'||chr(10);
   plsql_block:=plsql_block||'   type l_type_tt is table of l_type;'||chr(10);
   plsql_block:=plsql_block||'   l_res l_type_tt:=l_type_tt();'||chr(10);
   plsql_block:=plsql_block||'   counter number(3):=1;'||chr(10);
   plsql_block:=plsql_block||''||chr(10);
      plsql_block:=plsql_block||'   var1 '||l_table_name||'%rowtype;'||chr(10);
      plsql_block:=plsql_block||'   var2 '||l_table_name||'%rowtype;'||chr(10);
      plsql_block:=plsql_block||'   --Ce devraient etre des parametres de la procedure'||chr(10);
      plsql_block:=plsql_block||'   id_old number:=:old_val;'||chr(10);
      plsql_block:=plsql_block||'   id_new number:=:new_val;'||chr(10);
      plsql_block:=plsql_block||'begin'||chr(10);
      --Comme ca, si on utilise la procedure sur la meme table plusieurs fois avec des ID differents
      -- On ne devrait pas avoir de reparser les 2 selects suivants
      plsql_block:=plsql_block||'   select * into var1 from '||l_table_name||' where n1=id_old;'||chr(10);
      plsql_block:=plsql_block||'   select * into var2 from '||l_table_name||' where n1=id_new;'||chr(10);
      plsql_block:=plsql_block||'   l_res.extend;'||chr(10);
      FOR i IN (SELECT column_name FROM user_tab_columns WHERE table_name=upper(l_table_name)) loop
         plsql_block:=plsql_block||'   if (var1.'||i.column_name||' <> var2.'||i.column_name||') then'||chr(10);
         plsql_block:=plsql_block||'      l_res(counter).column_name:='''||i.column_name||''';'||chr(10);
         plsql_block:=plsql_block||'      l_res(counter).old_value:=var1.'||i.column_name||';'||chr(10);
         plsql_block:=plsql_block||'      L_res(counter).new_value:=var2.'||i.column_name||';'||chr(10);
         plsql_block:=plsql_block||'      l_res.extend;'||chr(10);
         plsql_block:=plsql_block||'      counter:=counter+1;'||chr(10);
         plsql_block:=plsql_block||'   end if;'||chr(10)||chr(10);
      end loop;
      plsql_block:=plsql_block||'--On a forcement un extend en trop'||chr(10);
      plsql_block:=plsql_block||'l_res.trim();'||chr(10);
      plsql_block:=plsql_block||'--return l_res;'||chr(10);
      plsql_block:=plsql_block||'dbms_output.put_line(l_res.count);';
      plsql_block:=plsql_block||'end;';
      dbms_output.put_line(plsql_block);
      execute immediate plsql_block USING 1,4;
      execute immediate plsql_block USING 1,3;
end;
/

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
TMP@minilhc >@tmp
declare
   type l_type IS record (column_name user_tab_columns.column_name%type,
                        old_value varchar2(100),
                        new_value varchar2(100));
   type l_type_tt IS TABLE of l_type;
   l_res l_type_tt:=l_type_tt();
   counter number(3):=1;
 
   var1 tmp1%rowtype;
   var2 tmp1%rowtype;
   --Ce devraient etre des parametres de la procedure
   id_old
number:=:old_val;
   id_new number:=:new_val;
begin
   SELECT * INTO var1 FROM tmp1 WHERE n1=id_old;
   SELECT * INTO var2 FROM tmp1 WHERE n1=id_new;
   l_res.extend;
   IF (var1.N1 <> var2.N1) then
      l_res(counter).column_name:='N1';
      l_res(counter).old_value:=var1.N1;
      L_res(counter).new_value:=var2.N1;
      l_res.extend;
      counter:=counter+1;
   end IF;
 
   IF (var1.PAD1 <>
var2.PAD1) then
      l_res(counter).column_name:='PAD1';
      l_res(counter).old_value:=var1.PAD1;
      L_res(counter).new_value:=var2.PAD1;
      l_res.extend;
      counter:=counter+1;
   end IF;
 
--On a forcement un extend en trop
l_res.trim();
--return l_res;
dbms_output.put_line(l_res.count);end;
1
2
 
PL/SQL procedure successfully completed.
 
Elapsed: 00:00:00.06
Elapsed: 00:00:00.06
TMP@minilhc >SELECT * FROM tmp1
  2  ;
 
        N1 PAD1
---------- ----------------------------------------
         1 x
         2 x
         3 y
         4 x
         5 z
         6 x
 
6 rows selected.

Ca biche pas mal.
De temps en temps sqlplus fait des blagues, genre le retour a la ligne lignes 13/14 que je ne comprends pas.

Vous devez simplement enlever la colonne idee dans le curseur implicite.

Autrement:
Je pense que un truc qui pourrait etre malin si vous vous preparez a utiliser souvent ceci ca pourrait etre de creer des procedures avec des noms en fonction du nom de la table. Il y a tout le squelette, au lieu d'un bloc anonyme, c'est... enfin bref.

L'avantage, c'est que si vous vous balladez sur la meme table tout le temps, ben vous avez la procedure, et ensuite tout est deja passer au parser la premiere fois. Je dis ca par rapport a vos lignes 58 et 59.
Vous pourriez eviter un peu de parse en mettant une bind variable pour l'id, mais vous ne pouvez pas pour le nom de colonne.
D'ou l'utilisation du %rowtype.

[ReiVon]

... ouah !

Merci beaucoup pour votre intervention. J'avoue, je n'ai pas tout compris, mais je vais de ce pas étudier tout cela.

Merci beaucoup !