Using the CREATE PROCEDURE or CREATE FUNCTION Statement

You must first decide if the routine that you are creating returns values or not. If the routine does not return a value, use the CREATE PROCEDURE statement to create an SPL procedure. If the routine returns a value, use the CREATE FUNCTION statement to create an SPL function.

To create an SPL routine, use one CREATE PROCEDURE or CREATE FUNCTION statement to write the body of the routine and register it.

Beginning and Ending the Routine

To create an SPL routine that does not return values, start with the CREATE PROCEDURE statement and end with the END PROCEDURE keyword. Figure 357 shows how to begin and end an SPL procedure.

CREATE PROCEDURE new_price( per_cent REAL )
. . .
END PROCEDURE;

For more information about naming conventions, see the Identifier segment in the IBM Informix: Guide to SQL Syntax.

To create an SPL function that returns one or more values, start with the CREATE FUNCTION statement and end with the END FUNCTION keyword. Figure 358 shows how to begin and end an SPL function.

CREATE FUNCTION discount_price( per_cent REAL)
   RETURNING MONEY;
. . .
END FUNCTION;

The entire text of an SPL routine, including spaces and tabs, must not exceed 64 kilobytes. In SPL routines, the END PROCEDURE or END FUNCTION keywords are required.

Specifying a Routine Name

You specify a name for the SPL routine immediately following the CREATE PROCEDURE or CREATE FUNCTION statement and before the parameter list, as Figure 359 shows.

CREATE PROCEDURE add_price (arg INT ) 

CREATE PROCEDURE multiply (a INT, b FLOAT)
CREATE PROCEDURE multiply (a INT, b SMALLINT)
CREATE PROCEDURE multiply (a REAL, b REAL)

Routine resolution is the process in which the database server searches for a routine signature that it can use, given the name of the routine and a list of arguments. Every routine has a signature that uniquely identifies the routine based on the following information:

• The type of routine (procedure or function)
• The routine name
• The number of parameters
• The data types of the parameters
• The order of the parameters

The routine signature is used in a CREATE, DROP, or EXECUTE statement if you enter the full parameter list of the routine. For example, each statement in Figure 360 uses a routine signature.

CREATE FUNCTION multiply(a INT, b INT);

DROP PROCEDURE end_of_list(n SET, row_id INT);

EXECUTE FUNCTION compare_point(m point, n point);

Adding a Specific Name (IDS)

Because Dynamic Server supports routine overloading, an SPL routine might not be uniquely identified by its name alone. However, a routine can be uniquely identified by a specific name. A specific name is a unique identifier that you define in the CREATE PROCEDURE or CREATE FUNCTION statement, in addition to the routine name. A specific name is defined with the SPECIFIC keyword and is unique in the database. Two routines in the same database cannot have the same specific name, even if they have different owners.

A specific name can be up to 128 bytes long. Figure 361 shows how to define the specific name calc in a CREATE FUNCTION statement that creates the calculate() function.

CREATE FUNCTION calculate(a INT, b INT, c INT)
   RETURNING INT
   SPECIFIC calc1;
. . .
END FUNCTION;

Because the owner bsmith has given the SPL function the specific name calc1, no other user can define a routine—SPL or external—with the specific name calc1. Now you can refer to the routine as bsmith.calculate or with the SPECIFIC keyword calc1 in any statement that requires the SPECIFIC keyword.

Adding a Parameter List

When you create an SPL routine, you can define a parameter list so that the routine accepts one or more arguments when it is invoked. The parameter list is optional.

A parameter to an SPL routine must have a name and can be defined with a default value. The following table lists the categories of data types that a parameter can specify for the different Informix database servers.

Dynamic Server

Extended Parallel Server

Built-in data types

Built-in data types

Opaque data types

Distinct data types

Row types

Collection types

Smart large objects
(CLOB and BLOB)

For all Informix database servers, a parameter cannot specify any of the following data types:

• SERIAL
• SERIAL8
• TEXT
• BYTE

Figure 362 shows examples of different parameter lists.

CREATE PROCEDURE raise_price(per_cent INT)

CREATE FUNCTION  raise_price(per_cent INT DEFAULT 5)

CREATE PROCEDURE update_emp(n employee_t)
CREATE FUNCTION  update_nums( list1 LIST (ROW a varchar(10),
                                              b varchar(10),
                                              c int) NOT NULL )

When you define a parameter, you accomplish two tasks at once:

• You request that the user supply a value when the routine is executed.
• You implicitly define a variable (with the same name as the parameter name) that you can use as a local variable in the body of the routine.

If you define a parameter with a default value, the user can execute the SPL routine with or without the corresponding argument. If the user executes the SPL routine without the argument, the database server assigns the parameter the default value as an argument.

When you invoke an SPL routine, you can give an argument a NULL value. SPL routines handle NULL values by default. However, you cannot give an argument a NULL value if the argument is a collection element.

Using Simple Large Objects as Parameters

Although you cannot define a parameter with a simple large object (a large object that contains TEXT or BYTE data types), you can use the REFERENCES keyword to define a parameter that points to a simple large object, as Figure 363 shows.

CREATE PROCEDURE proc1(lo_text REFERENCES TEXT)

CREATE FUNCTION proc2(lo_byte REFERENCES BYTE DEFAULT NULL)

The REFERENCES keyword means that the SPL routine is passed a descriptor that contains a pointer to the simple large object, not the object itself.

Undefined Arguments

When you invoke an SPL routine, you can specify all, some, or none of the defined arguments. If you do not specify an argument, and if its corresponding parameter does not have a default value, the argument, which is used as a variable within the SPL routine, is given a status of undefined.

Undefined is a special status used for SPL variables that have no value. The SPL routine executes without error, as long as you do not attempt to use the variable that has the status undefined in the body of the routine.

The undefined status is not the same as a NULL value. (The NULL value means that the value is not known, or does not exist, or is not applicable.)

Adding a Return Clause

If you use CREATE FUNCTION to create an SPL routine, you must specify a return clause that returns one or more values.

To specify a return clause, use the RETURNING or RETURNS keyword with a list of data types the routine will return. The data types can be any SQL data types except SERIAL, SERIAL8, TEXT, or BYTE.

The return clause in Figure 364 specifies that the SPL routine will return an INT value and a REAL value.

NCTION find_group(id INT)
   RETURNING INT, REAL;
. . .
END FUNCTION;

After you specify a return clause, you must also specify a RETURN statement in the body of the routine that explicitly returns the values to the calling routine. For more information on writing the RETURN statement, see Returning Values from an SPL Function.

To specify that the function should return a simple large object (a TEXT or BYTE value), you must use the REFERENCES clause, as in Figure 365, because an SPL routine returns only a pointer to the object, not the object itself.

CREATE FUNCTION find_obj(id INT)
   RETURNING REFERENCES BYTE;

Adding Display Labels (IDS)

You can use CREATE FUNCTION to create a routine that specifies names for the display labels for the values returned. If you do not specify names for the display labels, the labels will display as expression.

In addition, although using CREATE FUNCTION for routines that return values is recommended, you can use CREATE PROCEDURE to create a routine that returns values and specifies display labels for the values returned.

If you choose to specify a display label for one return value, you must specify a display label for every return value. In addition, each return value must have a unique display label.

To add display labels, you must specify a return clause, use the RETURNING keyword. The return clause in Figure 366 specifies that the routine will return an INT value with a serial_num display label, a CHAR value with a name display label, and an INT value with a points display label. You could use either CREATE FUNCTION or CREATE PROCEDURE in Figure 366.

CREATE FUNCTION p(inval INT DEFAULT 0)
   RETURNING INT AS serial_num, CHAR (10) AS name, INT AS points;
   RETURN (inval + 1002), "Newton", 100;
END PROCEDURE;

The returned values and their display labels are shown in Figure 367.

serial_num    name      points

1002          Newton    100

Specifying Whether or Not the SPL Function is Variant

When you create an SPL function, the function is variant by default. A function is variant if it returns different results when it is invoked with the same arguments or if it modifies a database or variable state. For example, a function that returns the current date or time is a variant function.

Although SPL functions are variant by default, if you specify WITH NOT VARIANT when you create a function, the function cannot contain any SQL statements. You can create a functional index on a nonvariant function.

Adding a Modifier (IDS)

When you write SPL functions, you can use the WITH clause to add a modifier to the CREATE FUNCTION statement. In the WITH clause, you can specify the COMMUTATOR or NEGATOR functions. The other modifiers are for external routines.

The COMMUTATOR Modifier

The COMMUTATOR modifier allows you to specify an SPL function that is the commutator function of the SPL function you are creating. A commutator function accepts the same arguments as the SPL function you are creating, but in opposite order, and returns the same value. The commutator function might be more cost effective for the SQL optimizer to execute.

For example, the functions lessthan(a,b), which returns TRUE if a is less than b, and greaterthan(b,a), which returns TRUE if b is greater than or equal to a, are commutator functions. Figure 368 uses the WITH clause to define a commutator function.

CREATE FUNCTION lessthan( a dtype1, b dtype2 )
   RETURNING BOOLEAN
   WITH ( COMMUTATOR = greaterthan );
. . .
END FUNCTION;

The optimizer might use greaterthan(b,a) if it is less expensive to execute than lessthan(a,b). To specify a commutator function, you must own both the commutator function and the SPL function you are writing. You must also grant the user of your SPL function the Execute privilege on both functions.

For a detailed description of granting privileges, see the description of the GRANT statement in the IBM Informix: Guide to SQL Syntax.

The NEGATOR Modifier

The NEGATOR modifier is available for Boolean functions. Two Boolean functions are negator functions if they take the same arguments, in the same order, and return complementary Boolean values.

For example, the functions equal(a,b), which returns TRUE if a is equal to b, and notequal(a,b), which returns FALSE if a is equal to b, are negator functions. The optimizer might choose to execute the negator function you specify if it is less expensive than the original function.

Figure 369 shows how to use the WITH clause of a CREATE FUNCTION statement to specify a negator function.

CREATE FUNCTION equal( a dtype1, b dtype2 )
   RETURNING BOOLEAN
   WITH ( NEGATOR = notequal );
. . .
END FUNCTION;

For more information on the COMMUTATOR and NEGATOR modifiers, see the Routine Modifier segment in the IBM Informix: Guide to SQL Syntax.

Specifying a Document Clause

The DOCUMENT and WITH LISTING IN clauses follow END PROCEDURE or END FUNCTION statements.

The DOCUMENT clause lets you add comments to your SPL routine that another routine can select from the system catalog tables, if needed. The DOCUMENT clause in Figure 370 contains a usage statement that shows a user how to run the SPL routine.

CREATE FUNCTION raise_prices(per_cent INT)
. . .
END FUNCTION
   DOCUMENT "USAGE: EXECUTE FUNCTION raise_prices (xxx)",
            "xxx = percentage from 1 - 100";

Remember to place single or double quotation marks around the literal clause. If the literal clause extends past one line, place quotation marks around each line.

Specifying a Listing File

The WITH LISTING IN option allows you to direct any compile-time warnings that might occur to a file.

Figure 371 shows how to log the compile-time warnings in /tmp/warn_file when you work on UNIX.

CREATE FUNCTION raise_prices(per_cent INT)
. . .
END FUNCTION
   WITH LISTING IN '/tmp/warn_file'

Figure 372 shows how to log the compile-time warnings in \tmp\listfile when you work on Windows.

CREATE FUNCTION raise_prices(per_cent INT)
. . .
END FUNCTION
   WITH LISTING IN 'C:\tmp\listfile'

Always remember to place single or double quotation marks around the filename or pathname.

Adding Comments

You can add a comment to any line of an SPL routine, even a blank line.

To add a comment, place a double hyphen ( -- ) before the comment or enclose the comment in braces ( { } ). Double hyphen complies with the ANSI/ISO standard for SQL. Braces are an Informix extension to the ANSI/ISO standard.

To add a multiple-line comment, take either of the following actions:

• Place a double hyphen before each line of the comment
• Enclose the entire comment within braces.

All the examples in Figure 373 are valid comments.

SELECT * FROM customer -- Selects all columns and rows

SELECT * FROM customer
   -- Selects all columns and rows
   -- from the customer table

SELECT * FROM customer
   { Selects all columns and rows 
     from the customer table }