| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: Really nice puzzle,but interested
A copy of this was sent to violin.hsiao_at_mail.pouchen.com.tw (Violin)
(if that email address didn't require changing)
On 20 Dec 1999 09:37:02 GMT, you wrote:
>Hello all,
>
>I have a application system with many formulas.
>For user friendly,all of the formulas are defined by application user.
>Formulas are updated frequently but variables are constant.
>like this :
>Now Formula1 = ( A + B ) / C ,
>BUT maybe next month it becomes ( A + (B * 1.1) ) / ( C + 1)
>Anyway,Formula1 always need 3 variables : A ,B and C
>But expression is often changed.
>So I create a table to store all the formula:
>Table Name : TEMP
>Column : F_Name varchar2(50) , F_Expre varchar2(200) , Modified Date
>
>F_Name F_Expre modified
>-----------------------------------------------------------------------------
>Formula1 ( A + B ) / C 1999-12-03
>Formula2 ( A + B + C ) * ( D + 0.5 ) 1999-11-15
>
>But when I coding stored procedure,
>ex. create procedure compute_formula1 (A in number,B in number,C in number)
> as ... begin ..... end;
>
>I don't know how to compute the formulas,cause they are VARCHAR.
>If you know how to solve the problem,please tell me.
>I'll very appreciate it.
>
>Best Regards.
>
>Violin.
>violin.hsiao_at_mail.pouchen.com.tw
Dynamic sql + dual will do it for you:
tkyte_at_8.0> create or replace package eval 2 as
3 function do_it 4 ( p_function in varchar2, 5 p_var1 in varchar2 default NULL, p_val1 in number default NULL, 6 p_var2 in varchar2 default NULL, p_val2 in number default NULL, 7 p_var3 in varchar2 default NULL, p_val3 in number default NULL ) 8 return number;
Package created.
tkyte_at_8.0>
tkyte_at_8.0> create or replace package body eval
2 as
3
3
3 procedure bv( c in integer, n in varchar2, v in number )
4 is
5 begin
6 if ( n is NOT NULL ) then 7 dbms_sql.bind_variable( c, n, v ); 8 end if;
11 ( p_function in varchar2, 12 p_var1 in varchar2 default NULL, p_val1 in number default NULL, 13 p_var2 in varchar2 default NULL, p_val2 in number default NULL, 14 p_var3 in varchar2 default NULL, p_val3 in number default NULL )15 return number
17 l_theCursor integer default dbms_sql.open_cursor; 18 l_rowsprocessed number default 0; 19 l_columnValue number; 20 begin 21 dbms_sql.parse(l_theCursor, 22 'select ' || p_function || ' from dual', 23 dbms_sql.native ); 24 24 bv( l_theCursor, p_var1, p_val1 ); 25 bv( l_theCursor, p_var2, p_val2 ); 26 bv( l_theCursor, p_var3, p_val3 ); 27 27 dbms_sql.define_column( l_theCursor, 1, l_columnValue ); 28 l_rowsprocessed := dbms_sql.execute(l_theCursor); 29 29 if ( dbms_sql.fetch_rows(l_theCursor) > 0 ) 30 then 31 dbms_sql.column_value( l_theCursor, 1, l_columnValue ); 32 end if; 33 dbms_sql.close_cursor( l_theCursor ); 34 34 dbms_output.put_line( 'Returning the value ' || l_columnValue || 35 ' as the result of the function ' || p_function ); 36 return l_columnValue; 37 exception 38 when others then 39 if dbms_sql.is_open(l_theCursor) then 40 dbms_sql.close_cursor(l_theCursor); 41 end if; 42 raise;
Package body created.
tkyte_at_8.0>
tkyte_at_8.0> exec dbms_output.put_line( eval.do_it( ':a+:b/:c', 'a', 1, 'b', 3,
'c', 2 ) )
Returning the value 2.5 as the result of the function :a+:b/:c
2.5
PL/SQL procedure successfully completed.
tkyte_at_8.0> exec dbms_output.put_line( eval.do_it( ':a+:b/:c', 'a', 2, 'b', 3,
'c', 2 ) )
Returning the value 3.5 as the result of the function :a+:b/:c
3.5
PL/SQL procedure successfully completed.
tkyte_at_8.0> exec dbms_output.put_line( eval.do_it( ':a+:b/:c', 'a', 3, 'b', 3,
'c', 2 ) )
Returning the value 4.5 as the result of the function :a+:b/:c
4.5
PL/SQL procedure successfully completed.
tkyte_at_8.0> exec dbms_output.put_line( eval.do_it( ':a+:b/:c', 'a', 7, 'b', 3,
'c', 2 ) )
Returning the value 8.5 as the result of the function :a+:b/:c
8.5
PL/SQL procedure successfully completed.
--
See http://osi.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'...
Current article is "Part I of V, Autonomous Transactions" updated June 21'st
Thomas Kyte tkyte_at_us.oracle.com Oracle Service Industries Reston, VA USA
Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Mon Dec 20 1999 - 06:22:39 CST
 |
 |