Say I have a table:
CREATE TABLE staff (
id INT,
name CHAR(9)
);
With data:
INSERT INTO staff (id, name) VALUES (1, 'Joe');
INSERT INTO staff (id, name) VALUES (2, 'Bob');
INSERT INTO staff (id, name) VALUES (3, 'Alice');
I need to create a multi row UDF, something like the built-in AVG
function, such that I can call it in the following manner:
SELECT vowel_count(name) FROM staff;
And assuming vowels are [AaEeIiOoUu]
, get the following result:
| vowel_count(name) |
|-------------------|
| 6 |
What is the syntax to take a table column as input to a UDF?
CREATE OR REPLACE FUNCTION vowel_cnt(/* what goes here? */)
RETURN NUMBER
IS
...
BEGIN
...
END;
The function must be table agnostic, just like SUM
, AVG
, etc.
I am using Oracle PL/SQL and SQL Developer.
3条答案
按热度按时间mlmc2os51#
As @WilliamRoberston said, you can define your own aggregate function with OCI Data Cartridge.
Partly because I haven't done this for a while and wanted to remind myself, here's a working implementation to count vowels.
First, create an object type with the required functions, and a numeric variable to hold the total count:
Then create the type body, with - in this case - fairly straightforward function bodies:
The count of vowels in each individual string could be done in various ways; regular expressions are clear but slow, so I've shown a
translate()
version which should be fast. I've included @MTO's suggestion to wrap that in coalesce for a null result (for the edge case where the string consists only of vowels, and also to handle null inputs (though it gets the right result without those changes; it's safer to assume it might not one day...).And finally create the function that uses that type:
With your sample data you can now do:
fiddle including the edge cases.
mftmpeh82#
Don't write a custom aggregation function.
Write a scalar function:
Then, if you want to aggregate an entire column you can use:
Which, for the sample data:
Outputs:
| TOTAL_VOWEL_COUNT |
| ------------ |
| 10 |
Then if you want to find the minimum, maximum, average, etc. number of vowels then you can easily find it using the scalar function and wrapping it with a built-in aggregation function rather than having to create many different user-defined aggregation functions for each individual use-case.
fiddle
oxf4rvwz3#
You can't get your desired result with:
becouse select works row by row meaning that the function will get parameters:
If you sum it up afterwords you will get 6. Here are 2 functions:
One to count vowels from string and
One to get you count of 6 vowels from table (I named it A_TBL) column "NAME"
...
If you call them from select statement like here:
... the result would be
| ID | NAME | VOWELS | TOTAL_VOWELS |
| ------------ | ------------ | ------------ | ------------ |
| 1 | Joe | 2 | 6 |
| 2 | Bob | 1 | 6 |
| 3 | Alice | 3 | 6 |
But, the same result you can get using analytic function Sum() Over() with the first function.
The second one is here just as an example as it doesn't make sense anyway for it is fixed to one table and one column of the table.
It would make more sense to create function that will count vowels from any table's column:
NOTE: there is a limitation using LISTAGG into VarChar2 variable - for many rows or aggregated string too long - the function should be changed to do some looping instead...
usage in this sample