Statement-local variables (SLVs)

An SLV transmits OUT parameters from a user-defined function to other parts of an SQL statement. An SLV is local to the SQL statement; that is, it is valid only for the life of the SQL statement. It provides a temporary name by which to access an OUT parameter value. Any or all user-defined function arguments can be an SLV.

In the SQL statement that calls the user-defined function, you declare the SLV with the syntax: SLV_name # SLV_type, where SLV_name is the name of the SLV variable and SLV_type is its data type, as in:

SELECT SLV_name1, SLV_nameN FROM table WHERE 
   udr (param1, SLV_name1 # SLV_type1, ...
        SLV_nameN # SLV_typeN, paramN);

For example, the following SELECT statement declares SLVs x and z that are typed as INTEGER in its WHERE clause and then accesses both SLVs in the projection list:

SELECT x, z WHERE my_func(x # INT, y, z # INT) < 100 
    AND (x = 3) AND (z = 5)

For more information about the syntax and use of an SLV, see the description of function expressions within the Expression section in the HCL OneDB™ Guide to SQL: Syntax.