I've written some C-language UDF's but management is not keen on using C. I recently discovered that SQL UDF's are available in 13.10, but I am unclear as to what is allowed to be contained in an SQL UDF. The most info I have seen is in the doc SQL Functions, Operators, Expressions, and Predicates (Rel 13.10). But, the examples in this doc contain only the simplest of logic (ie. RETURN a + b). Is there a doc with more detailed info and examples?
What else can be done in an SQL UDF? Can any elements of the stored procedure language be used in an SQL UDF? Can I loop through a character string, making decisions as I go? My goal is to be able to implement the Oracle REPLACE() function (replace a string with another string), and implement some specific uses of the Oracle REGEXP_LIKE() function (for example, return TRUE if the first character of a string is letters E or V, followed by 4 numeric characters, a dot, and two more numeric characters).
my understanding is that an SQL UDF can contain everything which can be done with a standard SQL which result in a single value output.
Think of SQL UDF simple code wrapper which will be replaced when the SQL is executed.
So you can call different functions, do complex case statements etc. No loops.
Check also http://developer.teradata.com/blog/madmac/2010/03/a-few-basic-scalar-string-udfs for replace and there might be also the reg expression available somewhere.