Name of a database procedure (dbproc_name)

The name of a database procedure (dbproc_name)designates a database procedure.

Syntax

<dbproc_name> ::= [<schema_name>.]<identifier>

You cannot specify TEMP as the schema in a database procedure.

Neither the schema name nor the ID can be longer than 32 characters.

Explanation

In a well-structured database application, SQL statements are typically not distributed over the entire application but concentrated in a single access layer instead. This access layer has a procedural interface to the rest of the application at which operations for application objects are made available in form of abstract data types.

In client/server configurations, the client and server interact when an SQL statement is executed in the access layer. The number of these interactions can be reduced considerably by transferring the SQL access layer from the client to the server.

To this end, the database system provides a language (special SQL syntax that has been extended to include variables, control structures, and troubleshooting measures) that allows an SQL access layer to be formulated on the server side. This special SQL syntax can be used to define database procedures.

This has three main advantages:

·        The number of interactions between client and server is reduced by factors. Client/server communication is only required for each operation on the application object, and not for each SQL statement. This enhances the performance of client-server configurations considerably.

·        The SQL access layer contains the procedurally formulated integrity and business rules. By concentrating these rules on the server side and eliminating them from the database applications, modifications can be made centrally and thus become valid immediately in all database applications. In this way, the integrity and decision rules also become a part of the catalog in the database system.

·        An SQL access layer in the form of database procedures transferred to the server side is an essential customizing tool, as it allows customer-specific database functionality to be included.

To execute a database procedure, users must have the call privilege. This call privilege is independent of the user privileges for the tables and columns used in the database procedure. As a result, users can use a database procedure to execute SQL statements that they otherwise would not have access to.

Database procedures are called explicitly from the programming language of the application. DB procedures can contain parameters, apart from LONG columns. The extent to which LONG columns can be used within database procedures depends on the length of the LONG columns and the amount of storage space available.

As with any SQL statement, precautions must be taken to ensure that calling a database procedure has the desired effect, and that errors do not have any lasting effects on the database system. MaxDB provides nested transactions for this purpose. Each database procedure call can run in a subtransaction that can be reset without interfering with transaction control in the database application.

SQL statements for creating, calling, and dropping a database procedure

CREATE DBPROC[EDURE] Statement

CALL Statement

DROP DBPROC[EDURE] Statement

See also:

Trigger Name (trigger_name)

Name of a Database Function (dbfunction_name)

Ensuring Data Integrity