Statement-Local Variable Declaration

The Statement-Local Variable Declaration declares a statement-local variable (SLV) in a call to a user-defined function that defines one or more OUT or INOUT parameters.

A Statement-Local Variable Declaration has this syntax:

^{(explicit id slvd001)
slvd001} ^{(explicit id slvd002)
slvd002}

Statement-Local Variable Declaration

slv_name #

{ <Built-In Data Type>^[] | opaque_data_type | distinct_data_type | <Complex Data Type>^[] }


Element	Description	Restrictions	Syntax
distinct_data_type	Name of a distinct data type	The distinct data type must already exist in the database	Identifier
opaque_data_type	Name of an opaque data type	The opaque data type must already exist in the database	Identifier
slv_name	Name of a statement local variable you are defining	The slv_name is valid only for the life of the statement, and must be unique within the statement	Identifier

You can declare an SLV in a call to a user-defined function if both of the following conditions are true:

The UDF has one or more OUT or INOUT parameters
The SLV is declared when the UDF is invoked in the WHERE clause of a query.

The SLV declaration in the WHERE clause assigns the value of an OUT or INOUT parameter to the SLV, with the sharp (#) symbol between the identifier of the SLV and its declared data type. The UDF can be written in the SPL, C, or Java™ language. For example, if you register a function with the following CREATE FUNCTION statement, you can assign the value of its y parameter, which is an OUT parameter, to an SLV in a WHERE clause:

CREATE FUNCTION find_location(a FLOAT, b FLOAT, OUT y INTEGER)
   RETURNING VARCHAR(20)
   EXTERNAL NAME "/usr/lib/local/find.so"
LANGUAGE C;

In this example, find_location( ) accepts two FLOAT values that represent a latitude and a longitude and return the name of the nearest city with an extra value of type INTEGER that represents the population rank of the city.

You can now call find_location( ) in a WHERE clause:

SELECT zip_code_t FROM address
   WHERE address.city = find_location(32.1, 35.7, rank # INT)
   AND rank < 101;

The function expression passes two FLOAT values to find_location( ) and declares an SLV named rank of type INT. In this case, find_location( ) will return the name of the city nearest latitude 32.1 and longitude 35.7 (which might be a heavily populated area) whose population rank is between 1 and 100. The statement then returns the zip code that corresponds to that city.

The SLV can be declared only in a call to the UDF in the WHERE clause of the SELECT statement. The scope of reference of the SLV includes other parts of the same SELECT statement. The following SELECT statement, however, is invalid because the SLV declaration is in the projection list of the Projection clause, rather than in the WHERE clause:

-- invalid SELECT statement
SELECT title, contains(body, 'dog and cat', rank # INT), rank
   FROM documents;

The data type that you specify when you declare the SLV must be the same data type as the corresponding OUT or INOUT parameter in the CREATE FUNCTION statement. If you use different but compatible data types, such as INTEGER and FLOAT, the database server automatically performs the cast between the data types.

SLVs share the name space with UDR variables and the column names of the table involved in the SQL statement. Therefore, the database uses the following descending order of precedence to resolve name conflicts among the following objects:

UDR variables
Column names
SLVs

After the call to the UDF assigns the value of an OUT or INOUT parameter to the SLV, you can reference the SLV in other parts of the same query. For more information, see Statement-Local Variable Expressions.

¹ See Built-In Data Types

² See Complex Data Type