Creating 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 on SPL routine optimization, refer to Optimizing 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 on how to optimize an SPL routine, see Improving UDR Performance.

For a summary of the UDR information in the system catalog tables, refer to Reviewing Information about User-Defined Routines.

Figure 4 shows the parts of a CREATE FUNCTION statement that registers a user-defined function called abs_eq().

Figure 4. Registering an SPL Function

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 Figure 4, 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 IBM Informix: Guide to SQL Syntax. For information about creating using SPL routines, refer to the IBM Informix: Guide to SQL Tutorial.