SESAM/SQL distinguishes between the following routines:
Procedures (Stored Procedures)
User Defined Functions (UDFs).
The generic term “SQL-invoked routine” from the SQL standard is not used in SESAM/SQL.
A routine is used to store sequences of SQL statements in the database which can be executed later with a single call. A routine is comparable to a subroutine which runs entirely in the DBH, in other words without exchanging data with the application program.
The text of a routine in SESAM/SQL is written entirely in the SQL programming language.
In addition to the usual DML statements, a routine can also contain local definition, control, and diagnostic statements. Definition statements define local data and the cursor and specify special error handling procedures. Control statements control execution of the routine,e.g. by means of loops or conditions. Diagnostic statements provide information on possibly faulty execution of the routine.
The EXECUTE privilege is required to execute a routine.
Information on routines is provided in the information schemas.
Procedures can be defined with input and output parameters. Input parameters are supplied with arguments in the procedure call. Output values are stored by the procedure at predefined locations.
A procedure is generated with CREATE PROCEDURE, executed with the SQL statement CALL, and deleted with DROP PROCEDURE.
UDFs can be defined with input parameters. Input parameters are supplied with arguments in an expression when the function is called. UDFs have precisely one return value (SQL statement RETURN).
A UDF is generated with CREATE FUNCTION, executed by a function call, and deleted with DROP FUNCTION.