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.
And for Oracle:
TO_BE_DONE
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.
- 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)
And for Oracle:
TO_BE_DONE