Q:

How to write and call an Oracle function in SQL

I have two Oracle tables with a similar structure:

I'd like to write a function in Oracle which sum all values for each ID and returns a pair (ID,Text) where Text='ALERT' if the sum is greater than 100, 'OK' otherwise:

Then, I'd like to execute a query for each table, for example something like that:
SELECT MY_FUN() FROM TABLE_1
SELECT MY_FUN() FROM TABLE_2

Is this a right approach? How could I write this function?
Thanks

A:

Generally it is considered bad practice to call a function in SQL which executes SQL. It creates all kinds of problems.
Here is one solution:
create or replace function my_fun
  ( p_sum in number) return varchar2 is
begin
    if p_sum > 100 then return 'ALERT';
    else return 'OK';
    end if;
end;
/

Run it like this:
select id, my_fun(sum(val)) as state
from your_table
group by id;

