mysql 数学表达式,mysql / oracle存储数学公式

is there any way apply math formula from stored string in Oracle and or MySQL?

col1 | col2 | formula

---------------------

2 | 2 | col1*col2

2 | 3 | col1+col2

SELECT * from tbl

result:

col1 | col2 | formula

---------------------

2 | 2 | 4

2 | 3 | 5

edit: for each row another formula

解决方案

I think what you're saying is you want to have the database parse the formula string. For example, for Oracle you could

Add a column to the table to contain the result

Run an update statement which would call a PL/SQL function with the values of the columns in the table and the text of the formula

update {table} set formula_result = fn_calc_result (col1, col2, formula_column);

The PL/SQL function would create a string by replacing the "col1" and "col2" and so forth with the actual values of those columns. You can do that with regular expresions, as long as the formulas are consistently written.

Then use

execute immediate 'select '||{formula}||' from dual' into v_return;

return v_return;

to calculate the result and return it.

Of course, you could also write your own parser. If you decide to go that way, don't forget to handle operation precedence, parentheses, and so forth.