Extending Informix Dynamic Server 2000 Creating a User-Defined Routine

A routine is a collection of program statements that perform a particular task. A user-defined routine (UDR) is a routine that you can define and that can be invoked within an SQL statement or another UDR. A UDR can either return values or not, as follows:

• A user-defined function returns one or more values and therefore can be used in SQL expressions.
• A user-defined procedure is a routine that optionally accepts a set of arguments but does not return any values. A procedure cannot be used in SQL expressions because it does not return a value.

The database server supports UDRs written in the following languages:

• Stored Procedure Language (SPL), a language that is internal to the database server
• External languages such as C or Java

SPL routines can execute routines written in external languages (C or Java), and external routines (routines written in C or Java) can execute SPL routines.

Figure 2-1 shows the different types of user-defined routines.

Figure 2-1 Types of User-Defined Routines

You create the UDR in a language that the database server supports and then register it in the system catalog tables so that the database server can access it. The database server stores information on user-defined routines in the following system catalog tables:

• The sysprocedures system catalog table contains information about the user-defined routine, such as its name, owner, and whether it is a user-defined function or user-defined procedure.
• The sysprocauth system catalog table contains information on which users of the database server can execute a particular user-defined routine.

An SPL routine is a user-defined routine that is written in Stored Procedure Language (SPL) and SQL that the database server parses, optimizes, and stores in the system catalog tables in executable format. Because routines written in SQL are parsed, optimized as far as possible, and then stored in the system catalog tables in executable format, consider using an SPL routine for SQL-intensive tasks. For more information on how the database server parses an SPL routine, see Executing an SPL Routine.

Tip: Not all the encapsulated SPL that you created as SPL procedures in earlier Informix products has the properties currently associated with user-defined procedures. If the SPL routine returns a value, you now refer to it as an SPL function. If the SPL routine does not return a value, you still refer to it as an SPL procedure.

You create and register an SPL routine with one of the following SQL statements.

For more information, see Registering an SPL Routine.

These SQL statements contain the SPL code that makes up the SPL routine. SPL provides the flow-control extensions to SQL. The body of an SPL routine contains SQL statements and flow-control statements for looping and branching. The CREATE FUNCTION and CREATE PROCEDURE statements store the code for the body of the routine in the sysprocbody system catalog table of the database. For information on the syntax of SPL statements, see the Informix Guide to SQL: Syntax. For an explanation of how to use SPL statements, refer to the Informix Guide to SQL: Tutorial.

An external routine is a user-defined routine that is written in an external language. The body of an external routine contains external-language statements for operations such as flow control and looping, as well as special Informix library calls to access the database server. Therefore, you must use the appropriate compilation tool to parse and compile an external routine into an executable format.

The database server supports user-defined routines written in the following external languages.

The database server stores information on external languages that it supports for user-defined routines in the following system catalog tables:

• The sysroutinelangs system catalog table contains information about the external languages.
• The syslangauth system catalog table contains information on which users of the database server can use a particular external language.

Tip: For the most current information on the languages that the database server supports for user-defined routines, query the sysroutinelangs system catalog table.

You register the external routine with one of the following SQL statements.

The CREATE FUNCTION and CREATE PROCEDURE statements do not provide the actual code that makes up the external routine. Instead, they store information about the external routine (including the name of its executable file) in the sysprocedures system catalog table. Therefore, unlike SPL routines, the code for the body of an external routine does not reside in the system catalog of the database.

For more information, see Registering an External Routine.