INFORMIX
Extending INFORMIX-Universal Server: User-Defined Routines
Chapter 1: Overview of User-Defined Routines
Home Contents Index Master Index New Book

Tasks That You Can Perform with UDRs

 You can write user-defined routines to accomplish the following tasks:

Routines also can accomplish tasks that address new technologies, including the following:

Encapsulate Multiple SQL Statements

 You create an SPL routine to simplify writing programs or to improve performance of SQL-intensive tasks.

Simplify Writing Programs

An SPL routine can batch frequently performed tasks that require several SQL statements. SPL offers program control statements that extend what SQL can accomplish alone. You can test database variables in an SPL routine and perform the appropriate actions for the values the routine finds.

By encapsulating several statements in a single routine that the database server can call by name, you reduce program complexity. Different programs that use the same code can execute an SPL routine or external routine, so you need not include the same code in each program. The code is stored in only one place, eliminating duplicate code.

SPL routines are especially helpful in a client/server environment. If a change is made to application code, it must be distributed to every client computer. An SPL routine resides on the server computer, so the change is made in only one location.

SPL
Instead of centralizing database code in client applications, you create SPL routines to move this code to the database server. This separation allows applications to concentrate on user-interface interaction, which is especially important if multiple types of user interfaces are required.

Improve Performance of Multiple SQL Statements

Because an SPL routine contains native database language that the database server parses and optimizes as far as possible when you create the routine, rather than at runtime, SPL routines can improve performance for some tasks. SPL routines can also reduce the amount of data transferred between a client application and the database server.

For more information on performance considerations for SPL routines, refer to Chapter 5, "Performance Considerations."

Tip: Not all the encapsulated SPL that you created as SPL procedures in earlier Informix products has the properties currently associated with 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.

Extend Functions for Built-In Data Types

Universal Server provides the following types of routines for built-in data types:

SQL-Invoked Functions

 An SQL-invoked function is a routine that end users can specify within an SQL statement to operate on a data type. SQL-invoked functions on built-in data types can be one of the following:

    Universal Server provides built-in functions that provide some basic mathematical operations. Universal Server provides versions of the built-in functions that handle the built-in data types. You can write a new version of a built-in function to allow the function to operate on your new data type.

Casting Functions

Universal Server provides system-defined casts that perform automatic conversions between certain built-in data types. For more information on these system-defined casts, refer the Informix Guide to SQL: Reference.

You cannot create user-defined casts to allow conversions between two built-in data types for which a system-defined cast does not currently exist. For more information on when you would want to write new casting functions, refer to"Casting Functions".

Operator Class Functions

An operator class is the set of operators that Universal Server associates with a secondary access method for query optimization and building the index. A secondary access method (sometimes referred to as an index access method) is a set of server functions that build, access, and manipulate an index structure such as a B-tree, an R-tree, or an index structure that a DataBlade module provides.

Two types of functions make up the operator class for the secondary access method:

    Strategy functions are functions that end users can invoke within an SQL statement to operate on a data type. End users can invoke a strategy function by its operator symbol (such as > or =) or by its name (such as contains or within).

    Support functions are functions that the secondary access method uses internally to build and search the index.

You can write new operator class functions if you want to do the following:

    The query optimizer uses an operator class to determine if an index can be considered in the cost analysis of query plans. The query optimizer can consider use of the index for the given query when the following conditions are true:

For more information on operator classes, refer to Chapter 3 of Extending INFORMIX-Universal Server: Data Types.

Support New Data Types

When you create a new data type, you must provide the following:

SQL-Invoked Functions

An SQL-invoked function is a routine that end users can specify within an SQL statement to operate on a data type. SQL-invoked functions can be one of the following:

Support Functions

If you define a new opaque data type, you also provide support routines that enable the server to operate on the type. Universal Server requires some routines, and others are optional. The following list shows the standard routines that you define to support opaque data types:

For more information on the support routines for opaque data types, refer to Extending INFORMIX-Universal Server: Data Types.

Operator Class Functions

"Operator Class Functions" explains what an operator class is and describes the functions associated with an operator class.

When you create a new opaque data type, you write new operator class functions to do the following:

    Because of routine overloading, these functions can have the same name as the functions in the default operator class. For more information on routine overloading, refer to "What is Routine Overloading?" on page 2-5.

    These functions are called the strategy functions for the operator class.

For more information on the routines for operator classes, refer to Extending INFORMIX-Universal Server: Data Types.

Casting Functions

You can create user-defined casts to perform conversions between most data types, including opaque types, distinct types, row types, and built-in types. For example, you can define casts for any of the following user-defined data types:

In addition, you might want to define a new casting function to do the following:

For more information on how to create and register casts on extended data types, refer to the Extending INFORMIX-Universal Server: Data Types manual.

Operator Binding

Operator binding is the implicit invocation of an operator function when an operator symbol is used in an SQL statement. Universal Server implicitly maps a built-in operator function name to a built-in operator.

For example, suppose you create a data type that represents Scottish names, and you want to order the data type in a different way than the U.S. English collating sequence. You might want the names McDonald and MacDonald to appear together on a phone list. The default operators (for example, =) for character strings do not achieve this ordering.

To order Mc and Mac in the same way, you must create external functions that contain code that treats Mc and Mac the same. You can use the same function names as in the default operator class, btree_ops, so that the SQL user does not need to specify one function name to handle regular names and a different function name for Scottish names.

Function overloading is the ability to use the same name for multiple functions to handle different data types. Universal Server uses the external function because the CREATE TABLE statement specifies the Scottish names data type for the column. For more information on function overloading, refer to "What is Routine Overloading?" on page 2-5.

Use as a Triggered Action

An SQL trigger is a database mechanism that executes an action automatically when a certain event occurs. The event that can trigger an action can be an INSERT, DELETE, or UPDATE statement on a specific table. The table on which the triggered event operates is called the triggering table.

An SQL trigger is available to any user who has permission to use it. When the trigger event occurs, the database server executes the trigger action.The actions can be any combination of one or more INSERT, DELETE, UPDATE, EXECUTE PROCEDURE, or EXECUTE FUNCTION statements.

Because a trigger resides in the database and anyone who has the required privilege can use it, a trigger lets you write a set of SQL statements that multiple applications can use. It lets you avoid redundant code when multiple programs need to perform the same database operation. By invoking triggers from the database, a DBA can ensure that data is treated consistently across application tools and programs.

You can use triggers to perform the following actions as well as others that are not found in this list:

    For example, you can track updates to the orders table by updating corroborating information in an audit table.

    For example, you can determine when an order exceeds a customer's credit limit and display a message to that effect.

    For example, when an update occurs to the quantity column of the items table, you can calculate the corresponding adjustment to the total_price column.

For more information on triggers, refer to the Informix Guide to SQL: Tutorial.

Restrict Access to a Table

SPL routines offer the ability to restrict access to a table. For example, if an administrator grants insert permissions to a user, that user can insert a row using INFORMIX-Connect, DB-Access, or an application program. This situation could be a problem if an administrator wants to enforce any business rules (see the next section).

Rather than granting insert privileges, an administrator can force users to execute a routine to perform the insert.

Enforce Business Rules

Using the extra level of security that SPL routines provide, you can enforce business rules. For example, you might have a business rule that a row must first be archived before it is deleted. You can write an SPL routine that accomplishes both tasks and prohibits users from directly accessing the table.



Extending INFORMIX-Universal Server: User-Defined Routines, version 9.1
Copyright © 1998, Informix Software, Inc. All rights reserved.