How can I create a custom PL/SQL-Function that is similar to built-in Oracle functions like REGR_INTERCEPT or COVAR_POP, meaning that it uses two (or even more columns) of a table as parameters and returns a single value representing a kind of regression or correlation between this columns for example. How do I tell my function, that the parameters are not single values but represent a column with several values?
As a simple example I might have a table
create table test
(id number,
x number,
y number);
insert into test values (1,10,9);
insert into test values (2,11,11);
insert into test values (3,13,14);
insert into test values (4,15,14);
insert into test values (5,16,17);
insert into test values (6,18,16);
insert into test values (7,16,11);
and the simple example function my_test(a,b) should return the number of rows where x is greater then y:
select my_test(x,y) t from test;
t
------
2
This, of course could be done with:
select sum(sign(x-y)) from test;
but the question is, how to put this in a function that can work with more complex formulas as well.
(Edit on July 15th: Format code as code)