Re: pl/sql Question

From: Karsten Farrell <kfarrell_at_belgariad.com>
Date: Wed, 23 Apr 2003 20:30:24 GMT
Message-ID: <MPG.191097af86086678989750_at_news.la.sbcglobal.net>

harshkohli007_at_yahoo.com said...
> I have to compute a value within pl/sql using a formula stored in
> another user table so that user's can change the formula without
> changing the code. The formula looks like :
> [SH1 value +SH2 value)/TOT1 * 100 * W1] + [SH3 value+SH4 value)/TOT2
> * 100 * W2] + [SH5 value+SH6 value)/TOT3 * 100 * W3] + ......
>
> where SH* Value comes from a table which has the following 2 columns :
> SH Code SH Value
>
> example :
> SH1 94
> SH2 198
> SH3 218
> SH4 782
> .....
>
> The TOT* and W* comes from 2 different tables.
>
> What is the best way to store this formula in a table and how can I
> execute with pl/sql?
>
Don't know about the "best" way ... but here's one way to do it in pl/sql (didn't actually type it to check for errors):

create or replace function get_value() return number is

  cursor c_sh  is select * from sh_table;
  cursor c_tot is select * from tot_table;
  cursor c_w   is select * from w_table;

  --

  r_sh   c_sh%rowtype;
  r_tot  c_tot%rowtype;
  r_w    c_w%rowtype;

  --
  final_value number(38,4); -- or whatever precision required   --
begin
  open c_sh;
  fetch c_sh into r_sh;
  close c_sh;
  --
  open c_tot;
  fetch c_tot into r_tot;
  close c_tot;
  --
  open c_w;
  fetch c_w into r_w;
  close c_w;
  --
  final_value :=
    (

      (to_number(r_sh.sh1) + to_number(r_sh.sh2))
      / to_number(r_tot.tot1) * 100
      * to_number(r_w.w1)

    ) ...
  --
  return final_value;
exception
  ...[any error handling required]...
end get_value;

-- 
/Karsten
DBA > retired > DBA

Received on Wed Apr 23 2003 - 22:30:24 CEST