Re: PLSQL function

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: 28 Nov 2001 06:11:03 -0800
Message-ID: <9u2r9n025en@drn.newsguy.com>

In article <9u29ad$h0g$1_at_unbe.sarenet.es>, "Jon" says...
>
>Hi,
>
>I like to know if it is posible to create a new function so that I could
>pass to it some numeric variables and on the other hand a string variable
>that contains a formula doing some calculations with the numeric variables I
>have also passed to the function. The contect of the string variable could
>look like this "+ cost - vat + net_change". So the question is if it could
>be posible, using PLSQL, to evalute the content of the variable with the
>formula and give me back the result of apliying the formula to the numeric
>values.
>
>Thanks for you help
>
>

Yes, you would use dbms_sql to do this. Do OVER DO this, it obviously is not as fast as static operations.

ops$tkyte_at_ORA815.US.ORACLE.COM> 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 varchar2 default null,
6        p_var2      in varchar2 default null, p_val2 in varchar2 default null,
7        p_var3      in varchar2 default null, p_val3 in varchar2 default null,
8        p_var4      in varchar2 default null, p_val4 in varchar2 default null)
  9      return number;

 10
 11 end;
 12 /

Package created.

ops$tkyte_at_ORA815.US.ORACLE.COM>
ops$tkyte_at_ORA815.US.ORACLE.COM> create or replace package body eval   2 as
  3

  4  g_theCursor        integer default dbms_sql.open_cursor;
  5  g_theStmt       long default 'x';

  6
  7 procedure bind( p_name in varchar2, p_val in varchar2 )   8 is
  9 begin

 10      if ( p_name is not null )
 11      then
 12          dbms_sql.bind_variable( g_theCursor, p_name, p_val );
 13      end if;

 14 end;
 15
 16
 17 function do_it

 18      ( p_function  in varchar2,
19        p_var1      in varchar2 default null, p_val1 in varchar2 default null,
20        p_var2      in varchar2 default null, p_val2 in varchar2 default null,
21        p_var3      in varchar2 default null, p_val3 in varchar2 default null,
22        p_var4      in varchar2 default null, p_val4 in varchar2 default null)

 23 return number
 24 is

 25      l_rowsprocessed number  default 0;
 26      l_retval       number;
 27  begin
 28      if ( g_theStmt <> p_function )
 29      then
 30          dbms_sql.parse(g_theCursor,
 31                        'begin :ret_val := ' || p_function || '; end;',
 32                         dbms_sql.native );
 33                  g_theStmt := p_function;
 34      end if;
 35  
 36      dbms_sql.bind_variable( g_theCursor, ':ret_val', 0 );
 37      bind( p_var1, p_val1 );
 38      bind( p_var2, p_val2 );
 39      bind( p_var3, p_val3 );
 40      bind( p_var4, p_val4 );
 41  
 42      l_rowsprocessed := dbms_sql.execute(g_theCursor);
 43      dbms_sql.variable_value( g_theCursor, ':ret_val', l_retval );
 44  
 45      return l_retval;

 46 end do_it;
 47
 48
 49 end eval;
 50 /

Package body created.

ops$tkyte_at_ORA815.US.ORACLE.COM>
ops$tkyte_at_ORA815.US.ORACLE.COM> begin
  2 dbms_output.put_line

  3    ( eval.do_it( '+ :cost - :vat + :net_change',
  4                  'cost', 55,
  5                  'vat',  10,
  6                  'net_change', 1000 )

  7 );
  8 dbms_output.put_line

  9    ( eval.do_it( '+ :cost - :vat + :net_change',
 10                  'cost', 12,
 11                  'vat',  13,
 12                  'net_change', 2000 )

 13 );
 14
 15
 16 dbms_output.put_line
 17 ( eval.do_it( '2.06 * 1.77371') );  18 end;
 19 /
1045
1999
3.6538426

PL/SQL procedure successfully completed.

ops$tkyte_at_ORA815.US.ORACLE.COM>

--
Thomas Kyte (tkyte@us.oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp 

Received on Wed Nov 28 2001 - 08:11:03 CST