Re: User-defined SQL Function

From: Alexander Penev <alex_at_cska.net>
Date: 2000/07/12
Message-ID: <396CED94.A6B0F8C3_at_cska.net>#1/1


David Zuzga wrote:

> That's really pretty good, but ultimately, I was hoping for a way to do it
> without passing the par_pr_key in and doing another lookup in the function.
>
 

> It just seemed like it would be possible if there was some way to operate on
> the current row in the function.
>

There is no current row! Only a row based trigger has a "current row". A function is independent of any table or row. You must pass a parameter to identify for which row you want run you calculation. If you want to calculate the sums of this columns or their average... you can build "sum(c12)+sum(c2).... " in you query. If you want to run your function against a single row you must tell it which row you want.
If you don't want to create an extra pr. key on the table you can use the rowid:

 create function func (bitnumber number , par_rowid varchar2) return number is  declare
 c varchar2(200);
 r number;
 i number;
 begin
 c:='select ';
 for i in 1..20 loop
 if trunc(mod(bitnumber,power(2,i))/(power(2,(i-1))))=1 then  c:=c||'c'||to_char(i)||'+';
 end if;
 end loop;
 c:=c||'0 into r from yourtable where rowid= 'par_rowid';  EXECUTE IMMEDIATE c; --(or dbms_sql if not 8i)  return r;
 end func;

You run the function like this:
select func(15,rowid) from yourtable where ..... and will calculate the sums for the rows that you select through the where clause. If that's not what you want, just give me some more details. HTH
Alex Received on Wed Jul 12 2000 - 00:00:00 CEST

Original text of this message