Informix Guide to SQL: Tutorial Creating and Using SPL Routines

An SPL routine consists of a beginning statement, a statement block, and an ending statement. Within the statement block, you can use SQL or SPL statements.

The maximum size of an SPL routine is 64 kilobytes. The maximum size includes any SPL global variables in the database and the routine itself.

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.

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

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

The name that you assign to the SPL procedure can be up to 18 characters long. For more information about naming conventions, see the Identifier segment in the 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 10-2 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.

Important: For compatibility with earlier Informix products, you can use CREATE PROCEDURE with a RETURNING clause to create a user-defined routine that returns a value. However, Informix recommends that you use CREATE PROCEDURE for SPL routines that do not return values (SPL procedures) and CREATE FUNCTION for SPL routines that return one or more values (SPL functions).

Specifying a Routine Name

You specify a name for the UDR immediately following the CREATE PROCEDURE or CREATE FUNCTION statement and before the parameter list, as Figure 10-3 shows.

CREATE PROCEDURE add_price (arg INT )...

Dynamic Server allows you to create more than one SPL routine with the same name but with different parameters. This feature is known as routine overloading. For example, you might create each of the following SPL routines in your database:

CREATE PROCEDURE multiply (a INT, b basetype1)...
CREATE PROCEDURE multiply (a INT, b basetype2)... 
CREATE PROCEDURE multiply (a REAL, b basetype3)... 

If you call a UDR with the name multiply(), the database server evaluates the name of the UDR and its arguments to determine which UDR to execute.

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

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

The UDR signature is used in a CREATE, DROP, or EXECUTE statement if you enter the full parameter list of the UDR. For example, each statement in Figure 10-4 uses a UDR 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);

Because Dynamic Server supports routine overloading, an SPL routine might not be uniquely identified by its name alone. However, a UDR 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 UDR name. A specific name is defined with the SPECIFIC keyword and is unique in the database. Two UDRs in the same database cannot have the same specific name, even if they have different owners.

A specific name can be up to 18 characters long. Figure 10-5 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 UDR-SPL or external-with the specific name calc1. Now you can refer to the UDR as bsmith.calculate or with the SPECIFIC keyword calc1 in any statement that requires the SPECIFIC keyword.

When you create an SPL UDR, you can define a parameter list so that the UDR 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.

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

• SERIAL
• SERIAL8
• TEXT
• BYTE

Figure 10-6 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.

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 10-7 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.

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. Null means the value is not available or not applicable.

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

Tip: If you use the CREATE PROCEDURE statement to create an SPL routine, you have the option of specifying a return clause. However, Informix recommends that you always use the CREATE FUNCTION statement to create a routine that returns values.

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

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

CREATE FUNCTION 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 UDR that explicitly returns the values to the calling UDR. 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 10-9, because an SPL routine returns only a pointer to the object, not the object itself.

CREATE FUNCTION find_obj(id INT)
	RETURNING REFERENCES BYTE;

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.

By default, SPL functions are variant. 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.

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.

Important: You can use the COMMUTATOR or NEGATOR modifiers with SPL functions only. You cannot use any modifiers with SPL procedures. 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 10-10 uses the WITH clause to define a commutator function.

CREATE FUNCTION lessthan( a basetype1, b basetype2 )

	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 Informix Guide to SQL: Syntax.

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 10-11 shows how to use the WITH clause of a CREATE FUNCTION statement to specify a negator function.

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



Tip: By default, any SPL routine can handle null values that are passed to it in the argument list. In other words, the HANDLESNULLS modifier is set to YES for SPL routines, and you cannot change its value.

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

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 10-12 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.

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

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

CREATE FUNCTION raise_prices(per_cent INT)

.
.
.
END FUNCTION
	WITH LISTING IN '/tmp/warn_file'

Figure 10-14 shows how to log the compile-time warnings in \tmp\listfile when you work in Windows NT.

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.

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

To add a comment, place a double dash (--) before the comment or enclose the comment in braces ({ }). The double dash complies with the ANSI standard. The braces are an Informix extension to the ANSI standard.

To add a multiple-line comment, you can either

• place a double dash before each line of the comment.
• enclose the entire comment within braces.

All the examples in Figure 10-15 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 }


Warning: Braces ({    }) can be used to delimit both comments and the list of elements in a collection. To ensure that the parser correctly recognizes the end of a comment or list of elements in a collection, Informix recommends that you use the double dash for comments in an SPL routine that handles collection types.

The following CREATE FUNCTION statement creates a routine that reads a customer address:

CREATE FUNCTION read_address		 (lastname CHAR(15)) -- one 
argument

	RETURNING CHAR(15), CHAR(15), CHAR(20), CHAR(15),CHAR(2)

		CHAR(5); -- 6 items



	DEFINE p_lname,p_fname, p_city CHAR(15); --define each

		routine variable

	DEFINE p_add CHAR(20);

	DEFINE p_state CHAR(2);

	DEFINE p_zip CHAR(5);



	SELECT fname, address1, city, state, zipcode

		INTO p_fname, p_add, p_city, p_state, p_zip

		FROM customer

		WHERE lname = lastname;



	RETURN p_fname, lastname, p_add, p_city, p_state, p_zip;

		--6 items

END FUNCTION;



DOCUMENT 'This routine takes the last name of a customer

	as', --brief description

	'its only argument. It returns the full name and address

	of the customer.'



WITH LISTING IN 'pathname' -- modify this pathname according
-- to the conventions that your operating system requires 



-- compile-time warnings go here

; -- end of the routine read_address 

To use an SQL API to create an SPL routine, put the text of the CREATE PROCEDURE or CREATE FUNCTION statement in a file. Use the CREATE PROCEDURE FROM or CREATE FUNCTION FROM statement and refer to that file to compile the routine. For example, to create a routine to read a customer name, you can use a statement such as the one in the previous example and store it in a file. If the file is named read_add_source, the following statement compiles the read_address routine:

CREATE PROCEDURE FROM 'read_add_source';

The following example shows how the previous SQL statement looks in an ESQL/C program:

/* This program creates whatever routine is in *

 * the file 'read_add_source'.

 */

#include <stdio.h>

EXEC SQL include sqlca;

EXEC SQL include sqlda;

EXEC SQL include datetime;

/* Program to create a routine from the pwd */



main()

{

EXEC SQL database play;

EXEC SQL create procedure from 'read_add_source';

}

After you create an SPL routine, you cannot change the body of the routine. Instead, you need to drop the routine and re-create it. Before you drop the routine, however, make sure that you have a copy of its text somewhere outside the database.

In general, use DROP PROCEDURE with an SPL procedure name and DROP FUNCTION with an SPL function name, as Figure 10-16 shows.

DROP PROCEDURE raise_prices;
DROP FUNCTION read_address;


Tip: You can also use DROP PROCEDURE with a function name to drop an SPL function. However, Informix recommends that you use DROP PROCEDURE only with procedure names and DROP FUNCTION only with function names.

However, if the database has other routines of the same name (overloaded routines), you cannot drop the SPL routine by its routine name alone. To drop a routine that has been overloaded, you must specify either its signature or its specific name. Figure 10-17 shows two ways that you might drop a routine that is overloaded.

DROP FUNCTION calculate( a INT, b INT, c INT);
	-- this is a signature


DROP SPECIFIC FUNCTION calc1;
	-- this is a specific name

If you do not know the type of a routine (function or procedure), you can use the DROP ROUTINE statement to drop it. DROP ROUTINE works with either functions or procedures. DROP ROUTINE also has a SPECIFIC keyword, as Figure 10-18 shows.

DROP ROUTINE calculate;
DROP SPECIFIC ROUTINE calc1;

Before you drop an SPL routine stored on a remote database server, be aware of the following restriction. You can drop an SPL routine with a fully qualified routine name in the form database@dbservername:owner.routinename only if the routine name alone, without its arguments, is enough to identify the routine. Because user-defined data types on one database might not exist on another database, you cannot use qualified names with arguments that are user-defined data types.