Convertir chaîne de caractères en code interprété

**theworst** · 20/02/2009, 14h40

Bonjour, J'ai un problème dont l'énoncé est un peu long, mais je tente quand même. Merci de votre aide.

J'ai plusieurs tables, par exemple :
table app_toto, colonnes : intdate char(8), inttime char(5), formula varchar(99), result NUMBER, resdate DATE, zyxw NUMBER, vutsr NUMBER, ...
table app_titi, colonnes intdate char(8), inttime char(5), formula varchar(99), result NUMBER, resdate DATE, abcd NUMBER, efgh NUMBER, ijkl NUMBER, ...
table app_...
Le nombre de tables app_% dans un même schéma n'est pas connu à l'avance.
Pour chacune de ces tables, les 5 premières colonnes (intdate, inttime, formula, result, resdate) sont toujours là.
Les autres ne sont pas connues à l'avance, ni leur nom, ni leur nombre.

Pour chaque ligne, le champ formula de la table app_toto contient une formule du style "zyxw + vutsr" ou "zyxw * vutsr". Cette formule peut changer suivant les lignes,
mais il s'agit toujours d'une formule qui est fonction des colonnes.
De même, pour chaque ligne de la table app_titi, le champ formula contient une formule du style "if (abcd + efgh) * ijkl >= 95% then 100%", dans un langage à trouver.
Chaque table a un état avant traitement puis un état après traitement, le but étant d'écrire ce qu'il faut pour réaliser ce traitement.

J'ai donc besoin d'aide pour écrire une ou plusieurs procédure(s) et/ou fonction(s) qui permette(nt) à un curseur se baladant
d'une ligne à une autre (donc d'une date à une autre) pour chaque table, d'évaluer la formule en fonction des colonnes et
d'inscrire le résultat dans le champ result et d'inscrire dans le champ resdate la date à laquelle le calcul est fait (sysdate).
Il s'agit donc de convertir une chaîne de caractères en code interprété ! (en évitant de développer un langage, donc en considérant que
la chaîne de caractères est directement interprétable)

Exemple :
Table APP_TOTO avant traitement :
INTDATE | INTTIME | FORMULA | RESULT | RESDATE | zyxw | vutsr | ...
09/02/19 | 1400 | "zyxw + vutsr" | NULL | NULL | 1 | 1 |
09/02/19 | 1500 | "zyxw + vutsr" | NULL | NULL | 1 | 0 |
09/02/19 | 1600 | "zyxw * vutsr" | NULL | NULL | 1 | 0 |
09/02/19 | 1700 | "zyxw * vutsr" | NULL | NULL | 1 | 1 |
...

table APP_TOTO après traitement
INTDATE | INTTIME | FORMULA | RESULT | RESDATE | zyxw | vutsr | ...
09/02/19 | 1400 | "zyxw + vutsr" | 2 | 20.02.2009 14:05:10 | 1 | 1 |
09/02/19 | 1500 | "zyxw + vutsr" | 1 | 20.02.2009 14:05:10 | 1 | 0 |
09/02/19 | 1600 | "zyxw * vutsr" | 0 | 20.02.2009 14:05:10 | 1 | 0 |
09/02/19 | 1700 | "zyxw * vutsr" | 1 | 20.02.2009 14:05:10 | 1 | 1 |
...

Les formules peuvent être de différents types :
"zyxw + vutsr"
"if (abcd + efgh) * ijkl >= 95% then 100%"
"max (zyxw, vutsr)"
et elles sont toujours fonction des colonnes.

L'exercice, plus complexe en réalité, voudrait que le champ formula contienne un identifiant
qui "pointe" sur une formule dans une autre table.
Si quelqu'un a compris cette pseudo spécification, arriverait-il à me proposer quelque chose ?
Ou quelque chose qui n'a rien à voir mais qui permet le traitement de façon générique.
Si impossible, en perl, ça me va aussi...

Merci beaucoup !

**SheikYerbouti** · 20/02/2009, 15h07

Code :

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

if (abcd + efgh) * ijkl >= 95% then 100%

Aucun language ne saurait évaluer que 10 + 20 > 80% ?
80% de quoi ???

**guizaniseifislam** · 20/02/2009, 15h37

Bonjour,
je peux t'aider d'abord pour les formules de type "zyxw + vutsr", "zyxw * vutsr"... ==> tu fais une procédure ou fonction qui renvoi les noms des colonnes à utiliser dans la formule.

**GoLDoZ** · 20/02/2009, 15h57

Si les formules se limitent à des foncions mathématiques, cela est possible. Tu récupères la formule dans une chaîne, avec un EXECUTE IMMEDIATE ensuite.

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
DECLARE
  ma_formule VARCHAR2(50);
  mon_id NUMBER;
  ma_requete VARCHAR2(200);
  mon_resultat NUMBER;
BEGIN
  SELECT formula, id INTO ma_formule, mon_id
    FROM APP_TOTO
   WHERE (condition);
 
  ma_requete := 'SELECT '||ma_formule||' FROM APP_TOTO';
  EXECUTE IMMEDIATE ma_requete INTO mon_resultat;
 
  UPDATE APP_TOTO
     SET result = mon_resultat
   WHERE id = mon_id;
  COMMIT;
END;

Si la formule contient autre chose qu'une opération, je ne vois comment faire (et pas sûr que ce soit faisable).

**SheikYerbouti** · 20/02/2009, 16h00

La formule peut inclure des tests:

Code :

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

1
2
3
4
5
6
7
DECLARE
  v VARCHAR2(200) := 'select case when (10 + 10) * 4 >= 80 then 100 else 0 end  from dual';
  r NUMBER ;
BEGIN
  EXECUTE IMMEDIATE v INTO r ;
  DBMS_OUTPUT.PUT_LINE('result:' || r);
END;

**theworst** · 20/02/2009, 16h08

Bonjour,
Merci pour ces promptes réponses. Il y a de l'idée car ça ressemble à ce que mon intuition me dictait. Je vais essayer ça courant semaine prochaine, je vous tiendrai au courant.
Je garde donc en tête qu'une proposition marche bien si on s'impose comme langage une formule mathématique. Mais la seconde complète bien dans des cas où ce n'est pas mathématique, ce qui sera très peu le cas, donc c'est jouable pour moi.
le 95% ou le 100% était un façon de retourner 0,95 ou 1... Je l'ai mis à titre d'exemple.

Vraiment merci.

**GoLDoZ** · 20/02/2009, 16h20

Bien vu Sheik, je n'y avais pas pensé.

Dans ce cas, theworst, il faut bien stocker "case when" à la place de "if".

**theworst** · 23/02/2009, 15h36

Bonjour,
Alors, ..., j'ai essayé. Mais je suis tellement nul en PL/SQL que ça ne passe pas. Ni avec la proc citée en exemple, ni quand j'essaie au travers d'un curseur. Voilà le code :

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
 
   DECLARE
      v_date varchar(8);
      v_time varchar(5);
      v_formula VARCHAR2(199);
      v_id VARCHAR(15);
      v_result VARCHAR(15);
      v_requete VARCHAR(199);
   BEGIN
      SELECT A.INT_START_DATE, A.INT_START_TIME, F.FORMULA INTO v_date, v_time, v_formula
         FROM APP_IAM A, FORMULA F
         WHERE A.FORMULA = F.ID ;
 
      v_requete := 'SELECT '||v_formula||' FROM APP_IAM' ;
      execute immediate v_requete INTO v_result;
 
      UPDATE APP_IAM 
      SET RESULT = v_result 
      WHERE INT_START_DATE = v_date AND INT_START_TIME = v_time ;
      COMMIT;
   END;

et voilà le message d'erreur :
15:30:15.656 DBMS BBTW -- Error:
ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at ligne 9

**SheikYerbouti** · 23/02/2009, 15h46

Cela vient de votre ordre SELECT ... INTO qui ramène plus d'une ligne. Vérifiez la clause WHERE.

**theworst** · 23/02/2009, 15h56

Le code

Code :

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

1
2
3
4
 
SELECT A.INT_START_DATE, A.INT_START_TIME, F.FORMULA 
         FROM APP_IAM A, FORMULA F
         WHERE A.FORMULA = F.ID

me retourne forcément plusieurs lignes.
Le but de l'opération est d'appliquer cette formule (différente pour chaque ligne) ... à chaque ligne.

**SheikYerbouti** · 23/02/2009, 16h04

Dans ce cas il ne faut pas utiliser un SELECT ... INTO mais un curseur.

**theworst** · 23/02/2009, 16h07

OK, ça me va.
Mais le souci, c'est que la fameuse variable "ma_requete" ne peut plus s'appliquer, car il faudrait préciser, devant chaque nom de colonne, l'dentifiant de l'enregistrement correspondant au curseur...
Sauf s'il y a une autre méthode...

**SheikYerbouti** · 23/02/2009, 16h16

quelle variable "ma_requete" ?

**theworst** · 23/02/2009, 16h16

Voilà mon code avec curseur :

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
 
CREATE OR REPLACE PROCEDURE BEST1.SETRESULT
AS
BEGIN
   DECLARE
      v_date DATE;
      v_time DATE;
      v_formula VARCHAR2(199);
      v_requete VARCHAR(200);
      v_result NUMBER;
      v_id VARCHAR(15);
      my_rowid UROWID;
      CURSOR c1 IS
         SELECT INT_START_DATE, INT_START_TIME, FORMULA, RESULT, ROWID 
         FROM APP_IAM 
         ORDER BY INT_START_DATE, INT_START_TIME
         FOR UPDATE;
      t_rec c1%ROWTYPE;
   BEGIN
      OPEN c1;
      LOOP
         FETCH c1 INTO t_rec;
         EXIT WHEN c1%NOTFOUND;
         SELECT f.FORMULA INTO v_formula
         FROM FORMULA f
         WHERE f.Id = t_rec.FORMULA ;
 
         v_requete := 'SELECT '||v_formula||' FROM APP_IAM' ;
         execute immediate v_requete INTO v_result;
 
         UPDATE APP_IAM 
         SET RESULT = v_result 
         WHERE INT_START_DATE = v_date AND INT_START_TIME = v_time ;
 
      END LOOP;
      CLOSE c1;
      COMMIT;
   END;
END;

Et l'erreur est ~ la même :
Error: ORA-01422: exact fetch returns more than requested number of rows ORA-06512: at line 1, Batch 1 Line 1 Col 1

**GoLDoZ** · 23/02/2009, 16h25

Utilisez un second curseur pour formula.

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
BEGIN
      OPEN c1;
 
      LOOP
         FETCH c1
          INTO t_rec;
 
         EXIT WHEN c1%NOTFOUND;
 
         FOR cur_formula IN (SELECT f.formula
                               FROM formula f
                              WHERE f.ID = t_rec.formula)
         LOOP
            v_requete := 'SELECT ' || v_formula || ' FROM APP_IAM';
 
            EXECUTE IMMEDIATE v_requete
                         INTO v_result;
 
            UPDATE app_iam
               SET RESULT = v_result
             WHERE int_start_date = v_date AND int_start_time = v_time;
         END LOOP;
      END LOOP;
 
      CLOSE c1;
 
      COMMIT;
   END;

**theworst** · 23/02/2009, 16h31

Merci.
Je vais essayer de continuer sur cette base.
Je vous tiens au courant ... semaine prochaine !

**theworst** · 03/03/2009, 14h56

Bonjour à tous,

Alors, tout d'abord merci pour les coups de main...
Mais ma première tentative est ... ratée. L'erreur est :

Code :

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

1
2
3
 
Error:  ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at line 1, Batch 1 Line 1 Col 1

J'avoue que j'y vois de moins en moins clair car tellement incompétent dans ce domaine...

- Le nouveau code est :

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
 
CREATE OR REPLACE PROCEDURE TEST2
AS
BEGIN
   DECLARE
      v_date DATE;
      v_time DATE;
      v_requete VARCHAR2(199);
      v_formula VARCHAR2(199);
      v_result NUMBER;
      v_id VARCHAR(15);
      my_rowid UROWID;
      CURSOR c1 
      IS
      SELECT INT_START_DATE,
         INT_START_TIME,
         FORMULA,
         RESULT,
         ROWID 
      FROM BEST1.APP_IAM 
      ORDER BY INT_START_DATE, INT_START_TIME
      FOR UPDATE;
      t_rec c1%ROWTYPE;
   BEGIN
      OPEN c1;
 
      LOOP
         FETCH c1
          INTO t_rec;
 
         EXIT WHEN c1%NOTFOUND;
          SELECT f.ID, f.FORMULA INTO v_id, v_formula
         FROM BEST1.FORMULA f 
         WHERE t_rec.INT_START_DATE >= f.INT_START_DATE 
         AND t_rec.INT_START_DATE <= f.INT_END_DATE 
         AND t_rec.INT_START_TIME >= f.INT_START_TIME 
         AND t_rec.INT_START_TIME <= f.INT_END_TIME;
         UPDATE APP_IAM SET FORMULA = v_id WHERE ROWID = t_rec.rowid;
 
         FOR cur_formula IN (SELECT f.formula
                               FROM formula f
                              WHERE f.ID = t_rec.formula)
         LOOP
            v_requete := 'SELECT ' || v_formula || ' FROM APP_IAM';
 
            EXECUTE IMMEDIATE v_requete INTO v_result;
 
            UPDATE app_iam
               SET RESULT = v_result
             WHERE int_start_date = v_date AND int_start_time = v_time;
         END LOOP;
      END LOOP;
 
      CLOSE c1;
      COMMIT;
   END;      
END ;
/

- voilà un exemple de contenu de la table APP_IAM

Code :

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

1
2
3
4
5
6
7
 
INT_START_DATE	INT_START_TIME	INT_DURATION	APPLI	AM_SRVDG_SOLVE31P	AM_SRV_SOLVE31P	APACHE_HTTPD_SOLVE37P	APACHE_HTTPD_SOLVE38P	LDAP_AM_SOLVE32P	LDAP_CFG_SOLVE32P	LDAP_ENT_SOLVE32P	LDAP_TRSFR_SOLVE32P	ORA_BIAP_SOLVE01P	RESULT	FORMULA	INT_START
09/02/17	530	1	IAM            	100	100	100	100	100	100	100	100	100	[NULL]	IAM_003        	[NULL]
09/02/17	531	1	IAM            	100	100	100	100	100	100	100	100	100	[NULL]	IAM_003        	[NULL]
09/02/17	532	1	IAM            	100	100	100	100	100	100	100	100	100	[NULL]	IAM_003        	[NULL]
09/02/17	533	1	IAM            	100	100	100	100	100	100	100	100	100	[NULL]	IAM_003        	[NULL]
09/02/17	534	1	IAM            	100	100	100	100	100	100	100	100	100	[NULL]	IAM_003        	[NULL]

- et un exemple de contenu de la table formula

Code :

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

1
2
3
4
5
 
INT_START_DATE	INT_START_TIME	INT_END_DATE	INT_END_TIME	APPLI	ID	FORMULA
09/02/17	0	09/02/17	200	IAM            	IAM_001        	AM_SRVDG_solve31p
09/02/17	201	09/02/17	359	IAM            	IAM_002        	AM_SRVDG_solve31p + AM_SRV_solve31p
09/02/17	400	09/02/17	2359	IAM            	IAM_003        	AM_SRVDG_solve31p * AM_SRV_solve31p * ORA_BIAP_solve01p

Voilà !
A votre bon coeur... Et n'hésitez pas à m'envoyer bouler si vous pensez que je vais trop loin dans la demande d'aide.

**GoLDoZ** · 03/03/2009, 15h30

A coup sûr, ceci ramène plus d'une ligne et ne rentre pas dans votre nombre.

Code :

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

1
2
3
v_requete := 'SELECT ' || v_formula || ' FROM APP_IAM';
 
EXECUTE IMMEDIATE v_requete INTO v_result;

Je ne vois pas où vous utilisez votre curseur cur_formula...

**theworst** · 03/03/2009, 15h44

C'est que je n'ai pas compris le sens de votre précédent message (utilisation d'un 2ème curseur).
A vrai dire, j'ai à peu près compris l'utilisation d'un seul curseur, ..., mais travailler sur un autre curseur à " l'intérieur " du premier dépasse mon niveau de compréhension.

Mais le code

Code :

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

1
2
3
 
v_requete := 'SELECT ' || v_formula || ' FROM APP_IAM';
 EXECUTE IMMEDIATE v_requete INTO v_result;

ramène forcément plusieurs lignes... si on l'utilise en SQL "normal".
Mais si on est sur la ligne pointée par le curseur, on n'en a forcément plus qu'une. C'est bien le principe du curseur, non ?

Je vais recommencer.
Merci.

**GoLDoZ** · 03/03/2009, 16h28

Ce n'est pas tout à fait cela. Le curseur sert à ramener des enregistrements un par un, dans le but de les utiliser.
Ici vous n'utilisez pas votre curseur, et votre requête est indépendante du curseur.

Edit: j'ai dû mal à voir la conception au départ, mais il y a forcément une erreur.
A la place de

Code :

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

v_requete := 'SELECT ' || v_formula || ' FROM APP_IAM';

ce doit être

Code :

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

v_requete := 'SELECT ' || cur_formula.formula || ' FROM APP_IAM';

Edit2: après réflexion, je ne vois pas l'utilité d'un second curseur...

Convertir chaîne de caractères en code interprété

PL/SQL Oracle

Discussions similaires

Partager

Partager