Step-by-Step Guide

Step 1: Access the Data Objects Section

Navigate to the Data Object section in your Orchestrator toolbar.

Step 3: Create a new Function

In the Data Object screen, click on the "FUNCTION" button to create a new function.

Step 4: Fill out the "Add Function" form

Next a new window will appear showing the "Add Function" form, you will need write a function name, select the type of function, enter the parameters, the return value, if the function is deterministic or not, the sql characteristics and the function SQL code.

Below will be explaining in more depth each required field.

Function name

The function name is the name you provide to the function, it can be any name specify by you.

Type

There are 2 types of function you can create:

• Function:Returns a single value and can be used in SQL statements.
• Store Procedure:Executes a series of predefined operations and may or may not return values.

Parameters

Here you can setup the parameters needed for your function, for that you will have to setup the name of the variable or parameters, the type of value and the max value if needed.

Return Value

You can configure the return value in your function if you need or want to return a specific value. Simply set up the type of the value accordingly.

Deterministic

The Deterministic field refers to a function call optimization that MySQL use to internally tagged functions, a function can be Deterministic or nondeterministic

• Yes (Deterministic): The function always returns the same result for the same input parameters.
• No (Nondeterministic): The function may return different results for the same input parameters.

SQL Characteristics

Defines the nature of the SQL statements used within the function and indicates the type of SQL operations the functions performs.

• CONTAINS SQL: The function contains SQL statements but does not read or write data.
• READS SQL DATA: The function contains statements that read data.
• MODIFIES SQL DATA: The function contains statements that modify data.
• NO SQL: The function does not contain any SQL statements.

Function SQL

This is the code area where you will code your SQL function and it will be containing all your logic and operations for such function.