Create an SPL routine

For an SPL routine, the CREATE FUNCTION or CREATE PROCEDURE statement performs the following tasks:
  • Parses and optimizes all SQL statements, if possible

    The database server puts the SQL statements in an execution plan. An execution plan is a structure that enables the database server to store and execute the SQL statements efficiently.

    The database server optimizes each SQL statement within the SPL routine and includes the selected query plan in the execution plan. For more information about SPL routine optimization, refer to Optimize an SPL routine.

  • Builds a dependency list

    A dependency list contains items that the database server checks to decide if an SPL routine needs to be reoptimized at execution time. For example, the database server checks for the existence of all tables, indexes, and columns involved in the query.

  • Parses SPL statements and convert them to p-code

    The term p-code refers to pseudocode that an interpreter can execute quickly.

  • Converts the p-code, execution plan, and dependency list to ASCII format

    The database server stores these ASCII formats as character columns in the system catalog tables, sysprocbody and sysprocplan.

  • Stores information about the procedure, such as routine name parameters and modifiers, in the sysprocedures system catalog table
  • Stores permissions for the procedure in the sysprocauth system catalog table

For information about how to optimize an SPL routine, see Improve UDR performance.

For a summary of the UDR information in the system catalog tables, refer to Review information about user-defined routines.

The following figure shows the parts of a CREATE FUNCTION statement that registers a user-defined function called abs_eq().
Figure 1: Registering an SPL function

The CREATE FUNCTION abs_eq(arg1 integer, arg2 integer) statement specifies abs_eq as a routine name and arg1 and arg2 as routine parameters. The RETURNS BOOLEAN clause specifies a routine return value. The WITH (NOT VARIANT) clause provides an optional routine modifier. The routine body is between the DEFINE and END FUNCTION key words.
When you create an SPL function, you can specify optional routine modifiers that affect how the database server executes the function. Procedures in SPL do not allow routine modifiers. Use the WITH clause of the CREATE FUNCTION statement to list function modifiers. SPL functions allow the following routine modifiers:
  • INTERNAL
  • NEGATOR
  • NOT VARIANT
  • VARIANT

In the previous figure, the NOT VARIANT modifier indicates that the abs_eq() SPL function is written so that it always returns the same value when passed the same arguments.

You can use INOUT parameters in SPL routines as shown in the following syntax:
    CREATE PROCEDURE spl_proc ([IN|OUT|INOUT] arg0 DataType, ..., 
[IN|OUT|INOUT] argN  DataType);
...;...;...;
    END PROCEDURE;

For more information about the CREATE FUNCTION and CREATE PROCEDURE statements and about the syntax of SPL, refer to the HCL OneDB™ Guide to SQL: Syntax. For information about creating using SPL routines, refer to the HCL OneDB Guide to SQL: Tutorial.