Informix Guide to SQL: Syntax Segments

Use the appropriate part of the Routine Parameter List segment whenever you see a reference to a Routine Parameter List in a syntax diagram.

In Dynamic Server, although you can use the Function Parameter List with a CREATE PROCEDURE statement to write and register an SPL routine that returns one or more values (that is, an SPL function), Informix recommends that you use the Function Parameter List only with the CREATE FUNCTION statement.

In Enterprise Decision Server, you can use the Function Parameter List with the CREATE PROCEDURE statement because the database server does not support the CREATE FUNCTION statement.

A parameter is one item in a Function Parameter List or Procedure Parameter List.

To define a parameter when creating a UDR, specify its name and its data type. You can specify the data type directly or use the LIKE or REFERENCES clause to identify the data type.

The name is optional for external routines.

You can define any number of parameters for an SPL routine. However, the total length of all the parameters passed to an SPL routine must be less than 32 kilobytes.

As indicated in the diagram for Parameter, not all data types are available for you to use as a parameter.

A UDR can define a parameter of any data type defined in the database, except SERIAL, SERIAL8, TEXT, BYTE, CLOB, or BLOB.

In addition to the allowable built-in types, a parameter can be a complex type or user-defined data type.

Complex types are not yet allowed as parameter data types for UDRs written in Java.

For more information on data types, see Data Type.

Use the LIKE clause to specify that the data type of a parameter is the same as a column defined in the database and changes with the column definition. If you define a parameter with LIKE, the data type of the parameter changes as the data type of the column changes.

Restriction on Routine Overloading

If you use the LIKE clause to define any of the parameters for the UDR, you cannot overload the UDR. The database server does not consider such a UDR in the routine resolution process.

For example, suppose you create the following user-defined procedure:

CREATE PROCEDURE cost (a LIKE tab.col, b INT)
.
.
.
END PROCEDURE;

You cannot create another user-defined procedure named cost() in the same database with two arguments. However, you can create a user-defined procedure named cost() with a number of arguments other than two.

To circumvent this restriction with the LIKE clause, you might want to use user-defined data types to achieve the same purpose.

Use the REFERENCES clause to specify that a parameter contains BYTE or TEXT data. The REFERENCES keyword allows you to use a pointer to a BYTE or TEXT object as a parameter.

If you use the DEFAULT NULL option in the REFERENCES clause, and you call the UDR without a parameter, a null value is used.

Use the DEFAULT keyword followed by an expression to specify a default value for a parameter. If you provide a default value for a parameter, and the UDR is called with fewer arguments than were defined for that UDR, the default value is used. If you do not provide a default value for a parameter, and the UDR is called with fewer arguments than were defined for that UDR, the calling application receives an error.

The following example shows a CREATE FUNCTION statement that specifies a default value for a parameter. This function finds the square of the i parameter. If the function is called without specifying the argument for the i parameter, the database server uses the default value 0 for the i parameter.

CREATE FUNCTION square_w_default

	(i INT DEFAULT 0) {Specifies default value of i}

RETURNING INT; {Specifies return of INT value}

	DEFINE j INT; {Defines routine variable j}

	LET j = i * i; {Finds square of i and assigns it to j}

	RETURN j; {Returns value of j to calling module}

END FUNCTION;

In Enterprise Decision Server, to recreate this example use the CREATE PROCEDURE statement instead of the CREATE FUNCTION statement.

Warning: When you specify a date value as the default value for a parameter, make sure to specify 4 digits instead of 2 digits for the year. When you specify a 4-digit year, the DBCENTURY environment variable has no effect on how the database server interprets the date value. When you specify a 2-digit year, the DBCENTURY environment variable can affect how the database server interprets the date value, so the UDR might not use the default value that you intended. For more information on the DBCENTURY environment variable, see the "Informix Guide to SQL: Reference."

Specifying an OUT Parameter for a User-Defined Function

When you register a user-defined function, you can specify that the last parameter in the list is an OUT parameter. The OUT parameter corresponds to a value the function returns indirectly, through a pointer. The value the function returns through the pointer is an extra value, in addition to the value it returns explicitly.

Once you register a user-defined function with an OUT parameter, you can use the function with a statement-local variable (SLV) in an SQL statement. You can only mark one parameter as OUT, and it must be the last parameter.

If you specify an OUT parameter, and you use Informix-style parameters, the argument is passed to the OUT parameter by reference. The OUT parameter is not significant in determining the routine signature.

For example, the following declaration of a C user-defined function allows you to return an extra value through the y parameter:

int my_func( int x, int *y );

You would register the C user-defined function with a CREATE FUNCTION statement similar to the following example:

CREATE FUNCTION my_func( x INT, OUT y INT )
RETURNING INT
EXTERNAL NAME "/usr/lib/local_site.so"
LANGUAGE C
END FUNCTION;

For example, the following declaration of a Java method allows you to return an extra value by passing an array:

public static String allVarchar(String arg1, String[] arg2)

throws SQLException

{ 

arg2[0] = arg1;

return arg1; 

}

You would register the user-defined function with a CREATE FUNCTION statement similar to the following example:

CREATE FUNCTION all_varchar(varchar(10), OUT varchar(7)) 
RETURNING varchar(7)

WITH (class = "jvp")

EXTERNAL NAME 
'informix.testclasses.jlm.Param.allVarchar(java.lang.String, 

java.lang.String[])'

LANGUAGE JAVA;