Execute user-defined routines in HCL OneDB

In HCL OneDB™, a user-defined routine is a collection of statements that performs a user-defined task. 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 values.

The following table summarizes the SQL statements for user-defined routines.

Table 1. SQL statement for user-defined routines
Task	Procedure	Function
Create and register a routine	CREATE PROCEDURE	CREATE FUNCTION
Execute a routine	EXECUTE PROCEDURE	EXECUTE FUNCTION
Drop a routine	DROP PROCEDURE	DROP FUNCTION

For more information about these statements, see the HCL OneDB Guide to SQL: Syntax.

HCL OneDB supports several languages for user-defined routines:

External routines are written in external languages such as C.
An external function can return one value while an external procedure does not return a value. For information about how to write an external routine in C, see HCL OneDB User-Defined Routines and Data Types Developer's Guide .
SPL routines are written in Stored Procedure Language (SPL).
An SPL function can return one or more values while an SPL procedure does not return any values. For information about how to write a stored routine, see the HCL OneDB Guide to SQL: Tutorial.

Tip: In earlier versions of HCL OneDB products, the term stored procedure was used for both SPL procedures and SPL functions. That is, a stored procedure can include the RETURN statement to return values. For compatibility with earlier products, HCL OneDB continues to support the execution of SPL functions with the EXECUTE PROCEDURE statement. However, for new SPL routines, it is recommended that you use EXECUTE PROCEDURE only for procedures and EXECUTE FUNCTION only for functions.

A user-defined routine can use input parameters for its arguments. However, it cannot use an input parameter for its routine name.