SESAM/SQL distinguishes between the following routines:
Procedures (Stored Procedure)
User Defined Functions (UDFs).
The generic term “SQL-invoked routine” from the SQL standard is not used in SESAM/SQL.
This chapter first describes common features and differences between procedures and UDFs.
It then includes a number of sections providing detailed descriptions of Procedures (Stored Procedures) and User Defined Functions (UDFs).
These are followed by information on the topics in which procedures and UDFs do not differ or differ only slightly:
Common features of routines
A routine is used to store and manage 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.
In contrast to a subroutine (in ESQL-COBOL), a routine can be used on different clients with different programming languages (e.g. via JDBC).
All database accesses can be centralized and controlled using routines. Individual SQL statements can also be activated in this way. They can then also be integrated into other routines and SQL statements according to the “modular design principle”.
Routines can also be used to facilitate writing.
The application programmer needs no knowledge of the structure of the database. The routine can be created by a database specialist, who (except for SQL) requires no programming knowledge.
Changes to the database structure do not necessarily affect the application programs. It may be sufficient to modify routines. Recompiling and relinking programs is unnecessary in such cases.
For safety's sake, only the EXECUTE privilege is required to execute the routine concerned. Global table and column privileges are no longer required.
Routines are stored directly in the database (with a complete audit trail). Separate management to manage routines outside the database is not required.
Differences between procedures and User Defined Functions
Procedures and UDFs have an identical range of functions. However, in UDFs of SESAM/SQL, SQL statements are not permitted for modifying data.
Procedures and UDFs also differ in how they are called and in their return information:
Procedures are called using the SQL statement CALL.
They have any number of output parameters but no return value.UDFs are called by means of their function call in an expression.
They have precisely one return value.
UDFs can be called in views. Procedures cannot.