CODEWITHSUNDEEP
oracle-databaseuser-defined-functions
user2024024
user2024024

Reputation: 209

How to write user defined average function in oracle

I am trying to create a user defined avg function, e.g. to calculate an average, sum or count - are there any examples I can follow?

Upvotes: 1

Views: 1512

Answers (2)

Jon Heller
Jon Heller

Reputation: 36807

As @David Aldridge mentioned, the "official" way to build a user-defined aggregate function is to use Oracle Data Cartridge. But in my experience, it is simpler, faster, and less buggy to use the CAST(COLLECT method. The only downside is your SQL statement requires some extra syntax.

For example, to create a custom average function that ignores the number 1:

--Created nested table of numbers
create or replace type number_nt is table of number;

--Create a custom average function.
--For example, average everything except the number "1".
create or replace function my_avg(numbers number_nt) return number is
    v_sum number := 0;
    v_count number := 0;
begin
    --Sum and count all the values, excluding nulls and "1".
    for i in 1 .. numbers.count loop
        if numbers(i) is not null and numbers(i) <> 1 then
            v_sum := v_sum + numbers(i);
            v_count := v_count + 1;
        end if;
    end loop;

    if v_count = 0 then
        return null;
    else
        return v_sum/v_count;
    end if;
end;
/

Here's how to call the function:

--Regular average is 2, our custom average that excludes 1 should be 2.5.
select
    avg(test_value) avg
    ,my_avg(cast(collect(test_value) as number_nt)) my_avg
from
(
    select 1 test_value from dual union all
    select 2 test_value from dual union all
    select 3 test_value from dual
);

AVG  MY_AVG
--   ------
2    2.5

Upvotes: 1

David Aldridge
David Aldridge

Reputation: 52346

What you're looking for is the User Defined Aggregates Function Interface, documented in the Data Cartridge Developers' Guide.

Some examples are documented here.

Upvotes: 1

Related Questions