Here is a code excerpt that shows the method signature for a method that implements a table UDF:
public class UDFExample {
...
public static void getStoreData( int storedata, /* input arg */
int[] storeno, /* output arg */
int[] itemno, /* output arg */
int[] quantsold) /* output arg */
{
...
}
...
}
If the JAR file for the UDFExample class is called UDFExample.jar, the following statement registers UDFExample.jar and the UDFExample class with the database, and creates an SQL identifier called JarUDF for the JAR file:
CALL SQLJ.INSTALL_JAR('CJ!udfsrc/UDFExample.jar','JarUDF',0);
The corresponding CREATE FUNCTION statement to define the UDF looks like this :
CREATE FUNCTION getStoreData (FileToRead INTEGER) RETURNS TABLE (StoreNo INTEGER ,ItemNo INTEGER ,QuantSold INTEGER) LANGUAGE JAVA NO SQL EXTERNAL NAME 'JarUDF:UDFExample.getStoreData' PARAMETER STYLE JAVA;
Here is an example of an INSERT … SELECT statement that invokes the table function in the FROM clause:
INSERT INTO Sales_Table SELECT * FROM TABLE (getStoreData(9005)) AS tf;