Procédure dans une requête

**BESTWAIL** · 28/12/2015, 14h50

Bonjour à tous.
J'essaye de faire une procédure qui me renvoie une valeur en utilisant EXECUTE STATEMENT.
Ma proc 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
SET TERM ^ ;
 
create or alter procedure CalFormula(
Formula_str varchar(1024))
returns ( xValue numeric(16,2))
as
declare variable SQL varchar(1024);
begin
SQL = 'select '||Formula_str||' from Table1 ';
EXECUTE STATEMENT SQL into :xValue;
  suspend;
end^
 
SET TERM ; ^

sachant que j'ai deux tables

Table1
--------------------------
Field_Name | Field_Value
--------------------------
Value1.......|.......50
Value2.......|.......30
Value3.......|.......10
Value4.......|.......70

Table2
----------------------------------------------
Formula_Name | Formula_Value
----------------------------------------------
Formula1 .......|.......Value1*2
Formula2 .......|.......Value2*Value1/10
Formula3 .......|.......Value3*Value2/Value1
Formula4 .......|.......Value14/Value3

L'utilisateur peut ajouter d'autres formules.

J'ai essaye le test dans une requete,quand j'utilise un parametre predifinie comme:

Code :

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

1
2
3
4
select 
Formula_Name,
xValue
from Table2, CalFormula ('Value1*2')

sa marche tres bien,

mais quand j'ai essaye d'execute la requete suivante j'obtiens un message d'erreur

Code :

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

1
2
3
4
select 
Formula_Name,
xValue
from Table2, CalFormula (Table2.Formula_Value)

**SergioMaster** · 28/12/2015, 18h17

Bonjour,

je ne comprend pas trop la logique, pour des formules complexe j'ai pris l'habitude d'utiliser une UDF http://rfunc.sourceforge.net/

J'ai essaye le test dans une requête,quand j'utilise un paramètre prédéfini CalFormula ('Value1*2')
ça marche très bien,

pas chez moi, à la rigueur CalFormula('50*2') oui

j'ai créé les tables et mis des données, puis enregistré la procédure et chez moi aucune des requêtes ne fonctionne

et cela me semble logique d'ailleurs
- Value1 est un texte pas une valeur
pour obtenir la valeur de Value1 il faudrait écrire SELECT FIELD_VALUE FROM TABLE1 WHERE FIELD_NAME='Value1'
pour la procédure on est loin du compte !

à la rigueur on pourrait peut être utiliser la fonction REPLACE de Firebird pour transformer les formules de la manière suivante

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
SET TERM ^ ;
ALTER PROCEDURE CALFORMULA (
    FORMULA_STR Varchar(1024) )
RETURNS (
    xvalue numeric(16,2)
AS
declare variable SQL varchar(1024);
declare variable    NAME Varchar(20),
declare variable    VALEUR Numeric(16,2),
begin
-- remplacement des noms dans la formule
FOR SELECT FIELD_NAME,FIELD_VALUE FROM TABLE1 INTO :Name,:Valeur
  DO   FORMULA_STR=REPLACE(FORMULA_STR,:NAME,:VALEUR);
 
 SQL='SELECT CAST('|| ||' AS NUMERIC(16,2)) FROM RDB$DATABASE';
EXECUTE STATEMENT SQL into :xValue;
 suspend;
end^
SET TERM ; ^

Testé par bout et ~~non~~ en globalité, faute de la description exacte des tables Table1 et Table2 ce n'est qu'une approximation

et ATTENTION à la CASSE des valeurs FIELD_NAME dans les formules

on peut ensuite facilement faire la requête suivante

Code :

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

1
2
3
4
select 
Formula_Name,
(SELECT XVALUE FROM CalFormula (Formula_Value)) 
from Table2

**BESTWAIL** · 28/12/2015, 21h29

Merci a votre réponse;

J'ai essaye avec des amis dans un autre forum de complexe les choses et en même temps trouver une solution efficace;
voici les étapes:

1/ les tables

Table1
--------------------------
Field_Name | Field_Value
--------------------------
Value1.......|.......50
Value2.......|.......30
Value3.......|.......10
Value4.......|.......70

Table2
----------------------------------------------
Formula_Name | Formula_Value
----------------------------------------------
Formula1 .......|.......Value1*2
Formula2 .......|.......Formula1*Value1/10 ....... // (Formula1 resultat de Value1*2)
Formula3 .......|.......Formula2*Value2/Value1 .......// (Formula2 resultat def Formula1*Value1/10)
Formula4 .......|.......Formula3*Formula2/Value3 .......// (Formula3 resultat de Formula2*Value2/Value1)

on a créer une procédure qui marche très bien

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
SET TERM ^ ;
 
create or alter procedure NEW_PROCEDURE
returns (
    VALUE1 numeric(15,2),
    SQL varchar(400))
as
declare variable FIELD_NAME1 varchar(100);
declare variable FIELD_VALUE1 varchar(100);
declare variable FORMULA_VALUE1 varchar(400);
declare variable FORMULA_NAME1 varchar(100);
declare variable FORMULA_VALUE2 varchar(400);
declare variable FORMULA_NAME2 varchar(400);
begin
for select Formula_Name, Formula_Value FROM Table2 into :Formula_Name1, :Formula_Value1 do
Begin
while ((select Out_field from check_field(:Formula_Value1)) =1) do
begin
For select Formula_Name, Formula_Value FROM Table2
Where (position(Formula_name, :Formula_Value1) > 0)
into :Formula_Name2, :Formula_Value2 do
begin
formula_value1 = replace(:Formula_Value1, :Formula_Name2, '('||cast(:Formula_Value2 as varchar(40))||')');
end
 
For select Field_Name, Field_Value from table1
Where (position(Field_name, :Formula_Value1) > 0)
into :Field_Name1, :Field_Value1 do
begin
formula_value1 = replace(:Formula_Value1, :Field_Name1, '('||cast(:Field_Value1 as varchar(40))||')');
end
end
sql =  'SELECT ' || :Formula_Value1 || ' from RDB$DATABASE' ;
execute statement sql into :value1;
end
  suspend;
end
^
 
SET TERM ; ^

la procédure CHECK_FIELD qui permet d’assurée qu'il y a pas un champ référencé a un autre champ

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
 
SET TERM ^ ;
 
create or alter procedure CHECK_FIELD (
    IN_FIELD varchar(400))
returns (
    OUT_FIELD numeric(1,0))
as
declare variable I integer;
BEGIN
  In_Field = UPPER(In_Field);
  Out_Field =0;
  i=65; -- A
  while (i <91 ) do -- to Z
  begin
    if (In_Field containing ASCII_CHAR(i)) then
    begin
     Out_field = 1;
     break;
     end
   i = i + 1;
  end
  SUSPEND;
END
^
 
SET TERM ; ^

mais il y a des observations :

1- Quand on exécute la procédure ou en l'appel dans SQL nous donne une seule lignes (dernier ligne);

Code :

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

1
2
3
4
select
    new_procedure.value1,
    new_procedure.sql
FROM new_procedure

2- et quand en ajoute un paramètre d’Entrée la procédure ne marche pas et affiche une erreur;

Code :

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

1
2
3
4
5
6
select 
    new_procedure.value1,
    new_procedure.sql,
    table2.formula_name,
    table2.formula_value
from table2, new_procedure(table2.formula_name)

erreur evec IbExpert

Code :

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

1
2
3
4
5
Error Message:
----------------------------------------
Unsuccessful execution caused by a system error that precludes successful execution of subsequent statements.
Dynamic SQL Error.
Input parameter mismatch for procedure NEW_PROCEDURE_PARAM.

Erreur avec FlameRobin

Code :

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

1
2
3
4
5
 
SQL Message: -508
The cursor identified in the UPDATE or DELETE statement is not positioned on a row.
Engine Code : 335544348
Engine Message: no current record for fetch operation

la procédure a été modifier:

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
begin
select Formula_Name, Formula_Value FROM Table2 where Formula_Name= :Input_param into :Formula_Name1, :Formula_Value1 ;
Begin
while ((select Out_field from check_field(:Formula_Value1)) =1) do
begin
For select Formula_Name, Formula_Value FROM Table2
Where (position(Formula_name, :Formula_Value1) > 0)
into :Formula_Name2, :Formula_Value2 do
begin
formula_value1 = replace(:Formula_Value1, :Formula_Name2, '('||cast(:Formula_Value2 as varchar(40))||')');
end
 
For select Field_Name, Field_Value from table1
Where (position(Field_name, :Formula_Value1) > 0)
into :Field_Name1, :Field_Value1 do
begin
formula_value1 = replace(:Formula_Value1, :Field_Name1, '('||cast(:Field_Value1 as varchar(40))||')');
end
end
sql =  'SELECT ' || :Formula_Value1 || ' from table2' ;
execute statement sql into :value1;
end
  suspend;
end

fichier de la base de donnée est jointe pour la tester

**SergioMaster** · 29/12/2015, 09h02

Bonjour,

ça pour complexifier, c'est sur vous complexifiez ! des formules dans les formules il fallait oser !
j'ai remarquer que finalement vous utiliser le REPLACE, mais je remarque que votre expression SQL reste sur une table alors que RDB$DATABASE est tout aussi efficace

pour ce qui est de cette formulation

Code :

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

1
2
3
4
5
6
select 
    new_procedure.value1,
    new_procedure.sql,
    table2.formula_name,
    table2.formula_value
from table2, new_procedure(table2.formula_name)

j'ai horreur de ce type de jointure non définie et non normalisée. Si vous aviez utiliser la normalisation vous auriez vite compris que le problème vient de l'absence d'expression de jointure
... from table2 JOIN new_procedure(table2.formula_name) ON ????et que donc il manquerait en sortie une valeur égale par exemple au nom_formule (égal au paramètre d'entrée) pour faire

Code :

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

1
2
SELECT p.Value1,p.SQL,t.Formula_Name,t.Formula_value 
FROM table2 t join new_procedure(t.formula_name) p on t.Formula_Name=p.nom_formule

pour ce qui est de gérer les formules dans des formules j'utiliserai SIMILAR TO
avec l'expression égale à SELECT LIST(FORMULA_NAME,'|') FROM TABLE2 pour tester si j'ai encore des formules dans l'expression

Quelque chose comme ça (que je n'ai pas encore pris la peine de tester)

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
 
declare variable regex  VARCHAR(1000) ; // limite le nombre de formules 
declare variable FNOM VARCHAR(50);
declare variable VNOM VARCHAR(50);
declare variable FVALEUR VARCHAR(150);
declare variable VALEUR  NUMERIC(15,2);
...
SELECT LIST(FORMULA_NAME,'|') FROM TABLE 2 INTO :regex;
WHILE (FORMULA_STR SIMILAR TO :REGEX)  DO
  BEGIN
      FOR SELECT FORMULA_NAME, FORMULA_VALUE FROM TABLE2 INTO :FNOM,:FVALEUR DO
           FORMULA_STR=REPLACE(FORMULA_STR,:FNOM,:FVALEUR); 
  END
FOR SELECT FIELD_NAME, FIELD_VALUE FROM TABLE1  INTO :VNOM,:VALEUR DO
   FORMULA_STR=REPLACE(FORMULA_STR,:VNOM,:VALEUR);

**BESTWAIL** · 29/12/2015, 10h29

Bonjour, et Merci de votre réponse.

Envoyé par SergioMaster

j'ai horreur de ce type de jointure non définie et non normalisée. Si vous aviez utiliser la normalisation vous auriez vite compris que le problème vient de l'absence d'expression de jointure
... from table2 JOIN new_procedure(table2.formula_name) ON ????et que donc il manquerait en sortie une valeur égale par exemple au nom_formule (égal au paramètre d'entrée) pour faire

Code :

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

1
2
SELECT p.Value1,p.SQL,t.Formula_Name,t.Formula_value 
FROM table2 t join new_procedure(t.formula_name) p on t.Formula_Name=p.nom_formule

J'ai fait quelque modification en ajoutant un paramètre de sortie, mais toujours il me renvoi le dernier ligne

Code :

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

1
2
3
4
5
6
7
select 
    new_procedure_param.value1,
    new_procedure_param.sql,
    table2.formula_name,
    table2.formula_value
from table2
 join new_procedure_param(table2.formula_name) on (table2.formula_name = new_procedure_param.output_param)

pour ce qui est de gérer les formules dans des formules j'utiliserai SIMILAR TO
avec l'expression égale à SELECT LIST(FORMULA_NAME,'|') FROM TABLE2 pour tester si j'ai encore des formules dans l'expression

**SergioMaster** · 29/12/2015, 10h38

Bonjour,

je n'avais pas encore testé, il est vrai que l'expression régulière est (pour l'instant) fausse, je planche dessus et viens à l'instant de trouver

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
 
-- procédure permettant de parser une formule en montrant les étapes
SET TERM ^ ;
CREATE PROCEDURE PARSE_FORMULA (
    FORMULE_STR Varchar(1024) )
RETURNS (
    FORMULE Varchar(2048) )
AS
declare variable regex varchar(1024);
declare variable fnom varchar(50);
declare variable fvaleur varchar(150);
BEGIN
  SELECT LIST(FORMULA_NAME||'_*','|') FROM TABLE2 INTO :REGEX;
  FORMULE=:REGEX;
  SUSPEND;
  WHILE (FORMULE_STR SIMILAR TO :REGEX) DO
    BEGIN
       FOR SELECT FORMULA_NAME,FORMULA_VALUE
             FROM TABLE2 INTO :FNOM,:FVALEUR DO
          FORMULE_STR=REPLACE(FORMULE_STR,:FNOM,:FVALEUR);
       FORMULE=FORMULE_STR;
       SUSPEND;
    END
 
END^
SET TERM ; ^

Procédure dans une requête

SQL Firebird

Vue hybride

Discussions similaires

Partager

Partager