Using the CREATE PROCEDURE or CREATE FUNCTION Statement

Tip: To create an SPL routine, you use one CREATE PROCEDURE or CREATE FUNCTION statement to write the body of the routine and register it. In contrast, external routines require that you write and register the routine as separate tasks.

Beginning and Ending the Routine

Figure 14-1

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

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 14-2 shows how to begin and end an SPL function.

Figure 14-2

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

In SPL routines, the END PROCEDURE or END FUNCTION keywords are required. Furthermore, the keyword PROCEDURE or FUNCTION must match in the beginning and ending statements.

Important: For compatibility with earlier Informix products, you can use CREATE PROCEDURE with a RETURNING clause to create a 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

Figure 14-3

CREATE PROCEDURE add_price (arg INT )...

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

• 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

Figure 14-4

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);

A specific name can be up to 18 characters long. Figure 14-5 shows how to define the specific name calc in a CREATE FUNCTION statement that creates the calculate() function.

Figure 14-5

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

Adding a Parameter List

A parameter to an SPL routine must have a name and can be defined with a default value. A parameter can specify any of the following categories of data types:

• Built-in data type
• Opaque data type
• Distinct data type
• Row type
• Collection type

• SERIAL
• SERIAL8
• TEXT
• BYTE
• CLOB
• BLOB

Figure 14-6

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 )

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

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

Figure 14-7

CREATE PROCEDURE proc1( lo_text REFERENCES TEXT )


CREATE PROCEDURE proc2( lo_byte REFERENCES BYTE DEFAULT NULL)

Undefined Arguments

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.

Adding a Return Clause

Tip: If you use CREATE PROCEDURE to create an SPL routine, you have the option of specifying a return clause. However, Informix recommends that you always use CREATE FUNCTION to create routines that return 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, BYTE, CLOB, or BLOB.

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

Figure 14-8

CREATE FUNCTION find_group( id INT )
	RETURNING INT, REAL;
.
.
.
END 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 14-9, because an SPL routine returns only a pointer to the object, not the object itself.

Figure 14-9

CREATE FUNCTION find_obj( id INT )
	RETURNING REFERENCES BYTE;

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 14-10 contains a usage statement that shows a user how to run the SPL procedure.

Figure 14-10

CREATE PROCEDURE raise_prices( per_cent INT)

.
.
.
END PROCEDURE
	DOCUMENT "USAGE: EXECUTE PROCEDURE raise_prices (xxx)",
			"xxx = percentage from 1 - 100";

Specifying a Listing File

Figure 14-11

CREATE PROCEDURE raise_prices( per_cent INT)

.
.
.
END PROCEDURE
	WITH LISTING IN '/tmp/warn_file';

Adding Comments

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

Figure 14-12

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 ( { } ) are 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 when an SPL routine operates on collection types.

In general, use DROP PROCEDURE with a procedure name and DROP FUNCTION with a function name, as Figure 14-13 shows.

Figure 14-13

DROP PROCEDURE raise_prices;
DROP FUNCTION calculate;

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.

Figure 14-14

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


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

Figure 14-15

DROP ROUTINE calculate;
DROP SPECIFIC ROUTINE calc1;