Informix Guide to SQL: Syntax SQL Statements

Use the CREATE PROCEDURE statement to create a user-defined procedure.

Tip: If you are trying to create a procedure from text that is in a separate file, use the CREATE PROCEDURE FROM statement.

Using CREATE PROCEDURE Versus CREATE FUNCTION

In Enterprise Decision Server, in addition to using this statement to create an SPL procedure, you must use the CREATE PROCEDURE statement to write and register an SPL routine that returns one or more values (that is, an SPL function). Enterprise Decision Server does not support the CREATE FUNCTION statement.

In Dynamic Server, although you can use the CREATE PROCEDURE statement to write and register an SPL routine that returns one or more values (that is, an SPL function), Informix recommends that you use the CREATE FUNCTION statement. To register an external function, you must use the CREATE FUNCTION statement.

Use the CREATE PROCEDURE statement to write and register an SPL procedure or to register an external procedure.

For information on how terms such as user-defined procedures and user-defined functions are used in this manual, see Relationship Between Routines, Functions and Procedures.

Usage

The entire length of a CREATE PROCEDURE statement must be less than 64 kilobytes. This length is the literal length of the CREATE PROCEDURE statement, including blank space and tabs.

In ESQL/C, you can use a CREATE PROCEDURE statement only within a PREPARE statement. If you want to create a procedure for which the text is known at compile time, you must use a CREATE PROCEDURE FROM statement.

A procedure is a routine that can accept arguments but does not return any values. A function is a routine that can accept arguments and returns one or more values.

User-defined routine (UDR) is a generic term that includes both user-defined procedures and user-defined functions.

You can write a UDR in SPL (SPL routine) or in an external language (external routine) that the database server supports. Consequently, anywhere the term UDR appears in the manual, its significance applies to both SPL routines and external routines. Likewise, the term user-defined procedure applies to SPL procedures and external procedures. Similarly, the term user-defined function applies to SPL functions and external functions.

In earlier Informix products, the term stored procedure was used for both SPL procedures and SPL functions. In this manual, the term SPL routine replaces the term stored procedure. When it is necessary to distinguish between an SPL function and an SPL procedure, the manual does so.

The term external routine applies to an external procedure or an external function. When it is necessary to distinguish between an external function and an external procedure, the manual does so.

Enterprise Decision Server does not support external routines. However, the term user-defined routine (UDR) encompasses both the terms SPL routine and external routine. Therefore, wherever the term UDR appears it is applicable to SPL routines.

You must have the Resource privilege on a database to create a user-defined procedure within that database.

Before you can create an external procedure, you must also have the Usage privilege on the language in which you will write the procedure. For more information, see GRANT.

By default, the Usage privilege on SPL is granted to PUBLIC. You must also have at least the Resource privilege on a database to create an SPL procedure within that database.

The level of privilege necessary to execute a UDR depends on whether the UDR is created with the DBA keyword.

If you create a UDR with the DBA keyword, it is known as a DBA-privileged UDR. You need the DBA privilege to create or execute a DBA-privileged UDR.

If you do not use the DBA keyword, the UDR is known as an owner-privileged UDR.

If you create an owner-privileged UDR in an ANSI-compliant database, anyone can execute the UDR.

If you create an owner-privileged UDR in a database that is not ANSI compliant, the NODEFDAC environment variable prevents privileges on that UDR from being granted to PUBLIC. If this environment variable is set, the owner of a UDR must grant the Execute privilege for that UDR to other users.

In Enterprise Decision Server, you must specify a unique name for the SPL routine that you create. The name must be unique among all SPL routines in the database.

Because Dynamic Server offers routine overloading, you can define more than one user-defined routine (UDR) with the same name, but different parameter lists. You might want to overload UDRs in the following situations:

• You create a UDR with the same name as a built-in routine (such as equal()) to process a new user-defined data type.
• You create type hierarchies, in which subtypes inherit data representation and UDRs from supertypes.
• You create distinct types, which are data types that have the same internal storage representation as an existing data type, but have different names and cannot be compared to the source type without casting. Distinct types inherit UDRs from their source types.

For a brief description of the routine signature that uniquely identifies each UDR, see Routine Overloading and Naming UDRs with a Routine Signature.

You can specify a specific name for a user-defined procedure. A specific name is a name that is unique in the database. A specific name is useful when you are overloading a procedure.

The quoted string in the DOCUMENT clause provides a synopsis and description of a UDR. The string is stored in the sysprocbody system catalog table and is intended for the user of the UDR.

Anyone with access to the database can query the sysprocbody system catalog table to obtain a description of one or all the UDRs stored in the database.

For example, to find the description of the SPL procedure raise_prices, shown in SPL Procedures, enter a query such as the following example:

SELECT data FROM sysprocbody b, sysprocedures p

WHERE b.procid = p.procid 
		--join between the two catalog tables

	AND p.procname = 'raise_prices' 
		-- look for procedure named raise_prices

	AND b.datakey  = 'D';	-- want user document

The preceding query returns the following text:

USAGE: EXECUTE PROCEDURE raise_prices( xxx )
xxx = percentage from 1 - 100 

A UDR or application program can query the system catalog tables to fetch the DOCUMENT clause and display it for a user.

You can use a DOCUMENT clause at the end of the CREATE PROCEDURE statement, whether or not you use the END PROCEDURE keywords.

The WITH LISTING IN clause specifies a filename where compile time warnings are sent. After you compile a UDR, this file holds one or more warning messages.

If you do not use the WITH LISTING IN clause, the compiler does not generate a list of warnings.

This listing file is created on the computer where the database resides.

If you specify a filename but not a directory, this listing file is created in your home directory on the computer where the database resides. If you do not have a home directory on this computer, the file is created in the root directory (the directory named "/").

If you specify a filename but not a directory, this listing file is created in your current working directory if the database is on the local computer. Otherwise, the default directory is %INFORMIXDIR%\bin.

SPL procedures are UDRs written in Stored Procedure Language (SPL) that do not return a value.

To write and register an SPL procedure, use a CREATE PROCEDURE statement. Embed appropriate SQL and SPL statements between the CREATE PROCEDURE and END PROCEDURE keywords. You can also follow the procedure with the DOCUMENT and WITH FILE IN options.

SPL procedures are parsed, optimized (as far as possible), and stored in the system catalog tables in executable format. The body of an SPL procedure is stored in the sysprocbody system catalog table. Other information about the procedure is stored in other system catalog tables, including sysprocedures, sysprocplan, and sysprocauth.

If the statement block portion of the CREATE PROCEDURE statement is empty, no operation takes place when you call the procedure. You might use such a procedure in the development stage when you want to establish the existence of a procedure but have not yet coded it.

If you specify an optional clause after the parameter list, you must place a semicolon after the clause that immediately precedes the Statement Block.

The following example creates an SPL procedure:

CREATE PROCEDURE raise_prices ( per_cent INT )
	UPDATE stock SET unit_price =
		unit_price + (unit_price * (per_cent/100) );
END PROCEDURE
	DOCUMENT "USAGE: EXECUTE PROCEDURE raise_prices( xxx )",
	"xxx = percentage from 1 - 100 "
	WITH LISTING IN '/tmp/warn_file'

External procedures are procedures you write in an external language that the database server supports.

1. Write a C function that does not return a value.
2. Compile the C function and store the compiled code in a shared library (the shared-object file for C).
3. Register the C function in the database server with the CREATE PROCEDURE statement.

1. Write a Java static method, which can use the JDBC functions to interact with the database server.
2. Compile the Java source file and create a jar file (the shared-object file for Java).
3. Execute the install_jar() procedure with the EXECUTE PROCEDURE statement to install the jar file in the current database.
4. If the UDR uses user-defined types, create a mapping between SQL data types and Java classes.

Use the setUDTExtName() procedure that is explained in EXECUTE PROCEDURE.

5. Register the UDR with the CREATE PROCEDURE statement.

Rather than storing the body of an external routine directly in the database, the database server stores only the pathname of the shared-object file that contains the compiled version of the routine. When the database server executes an external routine, the database server invokes the external object code.

The database server stores information about an external function in several system catalog tables, including sysprocbody and sysprocauth. For more information on these system catalog tables, see the Informix Guide to SQL: Reference.

If an external routine returns a value, you must register it with the CREATE FUNCTION statement.

The following example registers a C user-defined procedure named check_owner() in the database. This procedure takes one argument of the type lvarchar. The external routine reference specifies the path to the C shared library where the procedure object code is stored. This library contains a C function unix_owner(), which is invoked during execution of the check_owner() procedure.

CREATE PROCEDURE check_owner ( owner lvarchar )

EXTERNAL NAME "/usr/lib/ext_lib/genlib.so(unix_owner)"

LANGUAGE C

END PROCEDURE

The following example registers a user-defined procedure named showusers():

CREATE PROCEDURE showusers()
	WITH (CLASS = "jvp")
	EXTERNAL NAME 'admin_jar:admin.showusers'
	LANGUAGE JAVA

The EXTERNAL NAME clause specifies that the Java implementation of the showusers() procedure is a method called showusers(), which resides in the admin Java class that resides in the admin_jar jar file.

The user who creates an owner-privileged UDR owns any database objects that are created by the UDR when the UDR is executed, unless another owner is specified for the created database object. In other words, the UDR owner, not the user who executes the UDR, is the owner of any database objects created by the UDR unless another owner is specified in the statement that creates the database object.

However, in the case of a DBA-privileged UDR, the user who executes the UDR, not the UDR owner, owns any database objects created by the UDR unless another owner is specified for the database object within the UDR.

For examples of these situations, see the similar section, Ownership of Created Database Objects, in the CREATE FUNCTION statement.

Related statements: ALTER FUNCTION, ALTER PROCEDURE, ALTER ROUTINE, CREATE FUNCTION, CREATE FUNCTION FROM, CREATE PROCEDURE FROM, DROP FUNCTION, DROP PROCEDURE, DROP ROUTINE, EXECUTE FUNCTION, EXECUTE PROCEDURE, GRANT, EXECUTE PROCEDURE, PREPARE, REVOKE, and UPDATE STATISTICS

For a discussion of how to create and use SPL routines, see the Informix Guide to SQL: Tutorial.

For a discussion of how to create and use external routines, see Extending Informix Dynamic Server 2000.

For information about how to create C UDRs, see the DataBlade API Programmer's Manual.

For more information on the NODEFDAC environment variable and the relative system catalog tables (sysprocedures, sysprocplan, sysprocbody and sysprocauth), see the Informix Guide to SQL: Reference.