Example 1: Assume an external function program in C is needed that implements the following logic:
rslt = 2 * input - 4
The function should return a null value if and only if one of the input arguments is null. The simplest way to avoid a function call and get a null result when an input value is null is to specify RETURNS NULL ON NULL INPUT on the CREATE FUNCTION statement. The following statement defines the function, using the specific name MINENULL1.
CREATE FUNCTION NTEST1 (SMALLINT) RETURNS SMALLINT EXTERNAL NAME NTESTMOD SPECIFIC MINENULL1 LANGUAGE C DETERMINISTIC NO SQL FENCED PARAMETER STYLE SQL RETURNS NULL ON NULL INPUT NO EXTERNAL ACTION
The program code:
void nudft1 (int *input, /* ptr to input argument */ int *output, /* ptr to output argument */ short *input_ind, /* ptr to input indicator */ short *output_ind, /* ptr to output indicator */ char sqlstate[6], /* sqlstate */ char fname[140], /* fully qualified function name */ char finst[129], /* function specific name */ char msgtext[71]) /* msg text buffer */ { if (*input_ind == -1) *output_ind = -1; else { *output = 2*(*input)-4; *output_ind = 0; } return; }
Example 2: Assume that a user wants to define an external function named CENTER. The function program will be written in C. The following statement defines the function, and lets the database manager generate a specific name for the function. The name of the program containing the function body is the same as the name of the function, so the EXTERNAL clause does not include 'NAME external-program-name'.
CREATE FUNCTION CENTER (INTEGER, FLOAT) RETURNS FLOAT LANGUAGE C DETERMINISTIC NO SQL PARAMETER STYLE SQL NO EXTERNAL ACTION
Example 3: Assume that user McBride (who has administrative authority) wants to define an external function named CENTER in the SMITH schema. McBride plans to give the function specific name FOCUS98. The function program uses a scratchpad to perform some one-time only initialization and save the results. The function program returns a value with a DOUBLE data type. The following statement written by user McBride defines the function and ensures that when the function is invoked, it returns a value with a data type of DECIMAL(8,4).
CREATE FUNCTION SMITH.CENTER (DOUBLE, DOUBLE, DOUBLE) RETURNS DECIMAL(8,4) CAST FROM DOUBLE EXTERNAL NAME CMOD SPECIFIC FOCUS98 LANGUAGE C DETERMINISTIC NO SQL FENCED PARAMETER STYLE SQL NO EXTERNAL ACTION SCRATCHPAD NO FINAL CALL
Example 4: The following example defines a Java(TM) user-defined function that returns the position of the first vowel in a string. The user-defined function is written in Java, is to be run fenced, and is the FINDVWL method of class JAVAUDFS.
CREATE FUNCTION FINDV (VARCHAR(32000)) RETURNS INTEGER FENCED LANGUAGE JAVA PARAMETER STYLE JAVA EXTERNAL NAME 'JAVAUDFS.FINDVWL' NO EXTERNAL ACTION CALLED ON NULL INPUT DETERMINISTIC NO SQL
(C) Copyright IBM Corporation 1992, 2006. All Rights Reserved.