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
|
/******************* PROCEDURES ******************/
SET TERM ^ ;
CREATE PROCEDURE SERGIOMASTERPROC (
FORMULE Varchar(50) )
RETURNS (
VALEUR Numeric(15,2) )
AS
BEGIN SUSPEND; END^
SET TERM ; ^
/******************** TABLES **********************/
CREATE TABLE VALEURS
(
FIELD_NAME Varchar(50) NOT NULL,
FIELD_VALUE Numeric(15,2),
CONSTRAINT PK_TABLE1_1 PRIMARY KEY (FIELD_NAME)
);
CREATE TABLE FORMULES
(
FORMULA_NAME Varchar(50) NOT NULL,
FORMULA_VALUE Varchar(150),
CONSTRAINT PK_TABLE2_1 PRIMARY KEY (FORMULA_NAME)
);
COMMIT;
INSERT INTO VALEURS (FIELD_NAME, FIELD_VALUE) VALUES ('Value1', '50.00');
INSERT INTO VALEURS (FIELD_NAME, FIELD_VALUE) VALUES ('Value2', '30.00');
INSERT INTO VALEURS (FIELD_NAME, FIELD_VALUE) VALUES ('Value3', '10.00');
INSERT INTO VALEURS (FIELD_NAME, FIELD_VALUE) VALUES ('Value4', '70.00');
COMMIT;
INSERT INTO FORMULES (FORMULA_NAME, FORMULA_VALUE) VALUES ('Formula1', 'Value1*2');
INSERT INTO FORMULES (FORMULA_NAME, FORMULA_VALUE) VALUES ('Formula2', 'Formula1*Value1/10');
INSERT INTO FORMULES (FORMULA_NAME, FORMULA_VALUE) VALUES ('Formula3', 'Formula2*Value2/Value1');
INSERT INTO FORMULES (FORMULA_NAME, FORMULA_VALUE) VALUES ('Formula4', 'Formula3*Formula2/Value3');
COMMIT;
SET TERM ^ ;
ALTER PROCEDURE SERGIOMASTERPROC (
FORMULE Varchar(50) )
RETURNS (
VALEUR Numeric(15,2) )
AS
declare variable REGEX VARCHAR(1000) ;
declare variable FORMULE_STR VARCHAR(2048);
declare variable FNOM VARCHAR(50);
declare variable VNOM VARCHAR(50);
declare variable FVALEUR VARCHAR(150);
declare variable VVALEUR NUMERIC(15,2);
declare variable SQLSTMT VARCHAR(2048);
BEGIN
FORMULE_STR=:FORMULE;
SELECT LIST(FORMULA_NAME||'_*','|') FROM FORMULES INTO :REGEX; -- Table des formules
WHILE (FORMULE_STR SIMILAR TO :REGEX) DO
BEGIN
FOR SELECT FORMULA_NAME,FORMULA_VALUE
FROM FORMULES INTO :FNOM,:FVALEUR DO
FORMULE_STR=REPLACE(FORMULE_STR,:FNOM,:FVALEUR);
END
FOR SELECT FIELD_NAME,FIELD_VALUE FROM VALEURS INTO :VNOM,:VVALEUR DO -- Table des Valeurs
FORMULE_STR=REPLACE(FORMULE_STR,:VNOM,:VVALEUR);
SQLSTMT='SELECT CAST('||FORMULE_STR||' AS NUMERIC(15,2)) FROM RDB$DATABASE';
EXECUTE STATEMENT SQLSTMT INTO :valeur;
SUSPEND;
END^
SET TERM ; ^ |
Partager