SQL Function for DB2 and ORA

SQL Function in DB2 9

(UDF=User Defined Function)

DB2 9 and earlier, your could only has RETURN statement in your UDF logic. (This is a too limited design)

• RETURN can’t contain a SELECT statement

• RETURN can’t include a column name

So the most cases you define a function is to receive a value (as parameter) as input, and transforming that value and returning result of that transformation

For example: define TOCHAR function

CREATE FUNCTION TOCHAR(X DECIMAL(7, 2))
 RETURNS VARCHAR(20)
 LANGUAGE SQL
 RETURN VARCHAR(X, '.');

SQL Function in DB2 10

Compared with limited design in DB2 9,  SQL function in DB2 10 UDF can do much more, it looks more likely a normal FUNCTION design.
General function logic (compared with only RETURN  in DB2 9) are supported, variable declaration are supported.

BEGIN
   DECLARE VAR1 INTEGER;
   DECLARE VAR2, VAR3 CHAR(10);
 
   SET VAR1 = …;
   IF P1 = …;
   RETURN VAR2;

END@

SQL Function in Oracle

Sample, convert a BINARY_DOUBLE to string:

CREATE OR REPLACE FUNCTION <F_NAME>(P_FLOATVAL BINARY_DOUBLE)
  RETURN VARCHAR2
IS
  V_RETURN VARCHAR2(100);
BEGIN
  V_RETURN := TO_CHAR(P_FLOATVAL, 'FM09.09999999999999EEEE');
  RETURN V_RETURN;
END;
/

Overloading SQL Function

Both DB2 and Oracle can support function overloading.

But DB2 there are some limitation:
1. It does not consider any length, precision, or scale attributes of the arguments.
    Following functions overloading will FAIL, because are not overloading, but conflicting
    - FOO( DECIMAL )         vs. FOO( DECIMAL(5) )              # precision is ignored
    - FOO( DECIMAL(5) )    vs. FOO( DECIMAL(5, 2) )           # scale
    - FOO( CHAR )              vs. FOO(  CHAR(5) )                    #
    - FOO( VARCHAR(1) )   vs. FOO(  VARCHAR(5) )             #

    But CHAR(5) and VARCHAR(5) are not conflicting, but overloading, because they treated as different data types.

2. Synonymous and inherited data types are considered identical.
    - FOO(REAL)         vs. FOO(FLOAT)
    - FOO(REAL)         vs. FOO(FLOAT)
    - FOO(CHAR)        vs. FOO(GRAPHIC)
    - FOO(VARCHAR) vs. FOO(VARGRAPHIC)
    - FOO(CLOB)         vs. FOO(DBCLOB)
    - FOO(DECIMAL)   vs. FOO(NUMERIC)

3. If the function has more than 30 parameters, only the first 30 parameters to determine whether the function unique.

And for Oracle:
TO_BE_DONE