Usage

Stored procedures are simple programs, or procedures, that are executed in the server. The user can create a procedure that contains several SQL statements or a whole transaction and execute it with a single call statement. Usage of stored procedures reduces network traffic and allows more strict control to access rights and database operations.

Procedures are created with the statement

Procedures are called with the statement

The stored procedure syntax is a proprietary syntax modeled from SQL-99 specifications and dynamic SQL. Procedures contain control statements and SQL statements.

It is possible to create a stored procedure with an empty body.

All SQL DML and DDL statements can be used in procedures. Thus the procedure can, for example, create tables or commit a transaction. Each SQL statement in the procedure is atomic.

In HotStandby configurations, all SQL stored procedures are executed in the Primary unless they are specified as read-only procedures by way of the SQL standard clause SQL Data Access Indication in the procedure declaration.

To avoid unnecessary handovers of read-only procedures and functions, one of the following values can be declared:

Only MODIFIES SQL DATA (which is the default) inflicts transaction handover.

The <SQL-data-access-indication> clause comes between the (optional) RETURNS clause and the procedure body.

"CREATE PROCEDURE PHONEBOOK_SEARCH
(IN FIRST_NAME VARCHAR, LAST_NAME VARCHAR)
RETURNS (PHONE_NR NUMERIC, CITY VARCHAR)
READS SQL DATA
BEGIN
-- procedure_body
END";

Procedures are owned by the creator of the procedure. Specified access rights can be granted to other users. When the procedure is run, it has the creator’s access rights to database objects.

The “autocommit” functionality works differently for statements inside a stored procedure than for statements outside a stored procedure. For SQL statements outside a stored procedure, each individual statement is implicitly followed by a COMMIT WORK operation when autocommit is on. For a stored procedure, however, the implicit COMMIT WORK is executed after the stored procedure has returned to the caller. Note that this does not imply that a stored procedure is “atomic”. As indicated above, a stored procedure may contain its own COMMIT and ROLLBACK commands. The implicit COMMIT WORK executed after the procedure returns will commit only that portion of the stored procedure statements that were executed since:

If one stored procedure is called from inside another, the implicit COMMIT WORK is done only after the end of the OUTERMOST procedure call. There is no implicit COMMIT WORK done after nested procedure calls.

For example, in the following script, the implicit COMMIT WORK is executed only after the CALL outer_proc(); statement:

"CREATE PROCEDURE inner_proc
BEGIN
   ...
END";
CREATE PROCEDURE outer_proc
BEGIN
   ...
   EXEC SQL PREPARE cursor1 CALL inner_proc();
   EXEC SQL EXECUTE cursor1;
   ...
END";
   CALL outer_proc();