DB procedures are identified in the module header by the keyword DBPROC.
DB procedures serve to formulate complex operations on application objects (abstract data types). In this way, frequently recurring sequences of SQL statements used by a great number of users are collected and executed by the database server.
Thus communication between client and server can be reduced.
To be able to execute a DB procedure, the user must have the execute privilege for it. This execute privilege is independent of the privileges which the user may have for the tables and columns used in the DB procedure. It can therefore happen that a user is allowed to execute SQL statements via a DB procedure which outside this DB procedure are not available to him.
DB procedures are explicitly called from the application programming language. They may contain parameters. LONG columns are not permitted as parameters. Within a DB procedure, all SQL statements (DDL and DML) are available without restriction (DDL statements, however, only make sense for the owner of a DB procedure). The call of further DB procedures is also supported.
As in the case of any SQL statement, it must be ensured for a DB procedure call that this call has the desired effects, if successful, and that it does not affect the database at all, if an error occurs. To achieve this behavior for DB procedures as well, ADABAS provides nested transactions (subtransactions).
Every DB procedure should be formulated as a subtransaction which can be explicitly reset if the DB procedure ends with a serious error. In addition, any number of subtransactions can be explicitly opened and closed within the DB procedure. The additional call option COMMIT causes the DB procedure to terminate not only the subtransaction, but also the current transaction in the case of success.
In DB procedures, it is also possible to call further DB procedures or functions and to start application programs (C, COBOL). Operating system commands can also be called from DB procedures. Screen output is not possible in DB procedures.
The call of a DB procedure from an application program works like an SQL statement. In particular, a DB procedure call produces a return code as if an SQL statement had been executed. In addition to the return codes used by SQL, the developer of DB procedures can use own return codes within a specific range of numbers.
DB procedures cannot only create single rows but also tables as the result. It is recommended to use the feature that variables can be used as result table names in SELECT statements (see the ADABAS Reference manual).
As long as DB procedures have not been made known to the ADABAS server and activated as such, they can be tested and executed with the debugger, like normal SQL-PL routines.