The driver supports user-defined procedures and functions that are defined by the application or in the Model file. Functions and procedures are logically grouped statements that can be used to access data in the REST service or call additional functions and procedures. Since functions and procedures are saved, you can use them to store commonly used code and call them whenever needed, which can improve memory usage, initial coding effort, and coding maintenance.

Note that when the application defines a function or procedure, the driver stores the definition in internal memory for only the life of the session. Therefore, stored procedures and functions must be defined in a session before they are called. If you wish for stored procedures or functions to persist between sessions, use the Model file to store your definitions.

This section describes the syntax used to define functions and procedures supported by the driver. The syntax for defining the function in the application and the Model file is identical, with the exception of the #routines entry tags being exclusive to the Model file. For an overview of the syntax, see:

Function syntax

The following demonstrates the basic syntax used when defining a function. Note that not all the clauses in this example are required. See the following sections for more information on these statements and supported syntax not defined in this example.
Note: The #routines entry tags are used only in the Model file, not when defining a function with the application. 
"#routines":[
CREATE FUNCTION <function_name> (<parameters>) RETURNS <data_type>(<p>,<s>)[,...]
    [NOT] DETERMINISTIC 
    [RETURNS NULL ON NULL INPUT | CALLED ON NULL INPUT]
    <language>
    SPECIFIC <reference_name>
    //For SQL, use the RETURN keyword for single-line statements. For compound 
    //statements, use the Body keyword. Neither keyword is used for Java functions. 
    [RETURN | BEGIN ATOMIC <routine_body>]
    END;"
]
function_name
Specifies the name of the function to be called by the application.
data_type
Specifies the data type, including the precision and scale, for the results of the function. See "Data types syntax" for a complete list of supported data types. See Data types syntax for more information and supported syntax.
parameters
Defines the parameter variables used in the function. See Parameters for more information and supported syntax.
language
Specifies the language used by the routine. For example, if the function only reads data from the underlying data source, you would specify the READS SQL DATA keywords. See Routine language for more information and supported syntax.
reference_name
Optionally, specifies the unique reference name to be assigned for polymorphic functions. To access polymorphic functions through the DDL, each function is assigned a generated implementation name for reference in schema manipulation commands. To specify a name for these functions, instead of a generated one, use the SPECIFIC keyword. See SPECIFIC statements for more information and supported syntax.
routine_body
Specifies the statements used to perform the operation of the function. This can be a single statement defined by the RETURN statement or multiple statements wrapped by the BEGIN ATOMIC and END keywords. See Compound statements for more information and supported syntax.

Procedure syntax

The following demonstrates the basic syntax used when defining a procedure. Note that not all the clauses in this example are required. See the following sections for more information on these statements and supported syntax not defined in this example.
Note: The #routines entry tags are used only in the Model file, not when defining a procedure with the application.
"#routines":[
CREATE PROCEDURE <procedure_name> (<parameters>) RETURNS <data_type>(<p>,<s>)[,...]
    [NOT] DETERMINISTIC 
    <language>
    SPECIFIC <name>
    DYNAMIC RESULT SETS <sets_returned>
    //For SQL, use the RETURN statement for single-line statements. For compound 
    //statements, use BEGIN ATOMIC. Neither keyword is used for Java procedures. 
    [RETURN | BEGIN ATOMIC <routine_body>]
    END;"
]
procedure_name
Specifies the name of the procedure to be called by the application.
data_type
Specifies the data type, including the precision and scale, for the results of the function. See Data types syntax for more information and supported syntax.
parameters
Defines the parameter variables used in the procedure. See Parameters for more information and supported syntax.
language
Specifies the language used by the routine. For example, if the function only reads data from the underlying data source, you would specify the READS SQL DATA keywords. See Routine language for more information and supported syntax.
reference_name
Optionally, specifies the unique reference name to be assigned for polymorphic functions. To access polymorphic functions through the DDL, each function is assigned a generated implementation name for reference in schema manipulation commands. To specify a name for these functions, instead of a generated one, use the SPECIFIC keyword. See SPECIFIC statements for more information and supported syntax.
routine_body
Specifies the statements used to perform the operation of the procedure. This can be a single statement defined by the RETURN statement or multiple statements wrapped by the BEGIN ATOMIC and END statements. In addition to DQL, DML, and DDL statements, the driver supports a number of procedural SQL statements. See Compound statements for more information and supported syntax.