Informix Guide to SQL: Tutorial Creating and Using SPL Routines

SPL routine is a generic term that includes SPL procedures and SPL functions. An SPL procedure is a routine written in SPL and SQL that does not return a value. An SPL function is a routine written in SPL and SQL that returns a single value, a value with a complex data type, or multiple values. Generally, a routine written in SPL that returns a value is an SPL function.

You use SQL and SPL statements to write an SPL routine. SPL statements can be used only inside the CREATE PROCEDURE, CREATE PROCEDURE FROM, CREATE FUNCTION, and CREATE FUNCTION FROM statements. All these statements are available with SQL APIs such as Informix ESQL/C. The CREATE PROCEDURE and CREATE FUNCTION statements are available with DB-Access and the Relational Object Manager.

You can accomplish a wide range of objectives with SPL routines, including improving database performance, simplifying writing applications, and limiting or monitoring access to data.

Because an SPL routine is stored in an executable format, you can use it to execute frequently repeated tasks to improve performance. When you execute an SPL routine rather than straight SQL code, you can bypass repeated parsing, validity checking, and query optimization.

You can use an SPL routine in a data-manipulation SQL statement to supply values to that statement. For example, you can use a routine to perform the following actions:

• Supply values to be inserted into a table
• Supply a value that makes up part of a condition clause in a SELECT, DELETE, or UPDATE statement

These actions are two possible uses of a routine in a data-manipulation statement, but others exist. In fact, any expression in a data-manipulation SQL statement can consist of a routine call.

You can also issue SQL statements in an SPL routine to hide those SQL statements from a database user. Rather than having all users learn how to use SQL, one experienced SQL user can write an SPL routine to encapsulate an SQL activity and let others know that the routine is stored in the database so that they can execute it.

You can write an SPL routine to be run with the DBA privilege by a user who does not have the DBA privilege. This feature allows you to limit and control access to data in the database. Alternatively, an SPL routine can monitor the users who access certain tables or data. For more information about how to use SPL routines to control access to data, see the Informix Guide to Database Design and Implementation.

Enterprise Decision Server supports SPL procedures but not SPL functions.

With Enterprise Decision Server, the following SPL procedure features behave differently than they do in Dynamic Server:

• SYSPROCPLAN system catalog table

All Informix database servers modify the SYSPROCPLAN system catalog table whenever an SPL procedure is created. For Dynamic Server, the SYSPROCPLAN system catalog table is also modified during execution of an SPL procedure, if the SPL procedure generates any new query-execution plans during execution. However, Enterprise Decision Server does not modify the SYSPROCPLAN table when execution of an SPL procedure results in new query-execution plans. For example, if plans are deleted from the SYSPROCPLAN system catalog table, and the procedure is executed from any coserver, the plans are not restored in SYSPROCPLAN. However, an UPDATE STATISTICS FOR PROCEDURE statement that is executed from any coserver updates the plans in SYSPROCPLAN.

• SPL Procedure calls

An SPL procedure call can be made only to SPL procedures that are in the current database and the current database server.