Declare routine parameters

You define routine parameters in a parameter list when you declare the routine. In the parameter list, each parameter provides the name and data type of a value that the routine expects to handle. Routine parameters are optional; you can write a UDR that has no input parameters.

When you invoke the routine, the argument value must have a data type that is compatible with the parameter data type. If the data types are not the same, the database server tries to resolve the differences. For more information, see The routine-resolution process.

The way that you declare a UDR depends on the language in which you write that routine.

The parameters in an SPL routine must be declared with SQL data types, either built-in or user defined. For more information, see Execute an SPL routine.

For routines written in C or Java™, you use the syntax of that language to declare the routine. The routine parameters indicate the argument data types that the routine expects to handle.

You declare the routine parameters with data types that the external language supports. However, when you register the routine with CREATE FUNCTION or CREATE PROCEDURE, you use SQL data types for the parameters. (For more information, see Register parameters and a return value.) Therefore, you must ensure that these external data types are compatible with the SQL data types that the routine registration specifies.

For C UDRs, the DataBlade® API provides special data types for use with SQL data types. For most of these special data types, you must use the pass by reference mechanism. However, for a few data types, you can use the pass-by-value mechanism. For more information, see the topics on DataBlade API data types in the HCL OneDB™ DataBlade API Programmer's Guide and the HCL OneDB DataBlade API Function Reference.

Every Java UDR maps to an external Java static method whose class resides in a JAR file that has been installed in a database. The SQL-to-Java data type mapping is done according to the JDBC specification. For more information, refer to the HCL® J/Foundation Developer's Guide and your Java documentation.