begin

SQL/PL

Begin a compound statement block

Syntax

compound_stmt:
          [ label_name:] BEGIN [[[NOT]] ATOMIC | TRANSACTION]
               declarations
               statement_list
          END [ label_name]

Description

A compound statement is a set of statements enclosed within a BEGIN and END statement pair. Except for the BEGIN, each statement must be terminated with a semi-colon (;). Execution of a BEGIN statement initiates a new programming block which can contain its own set of declarations followed by one or more statements. Except for the most trivial procedures and functions all will be defined within an outer compound statement block.

Any needed declarations must be made before any other statements. Variables are declared first, followed by any cursor declarations, and lastly exception handler declarations. Specifics are provided below in the Declarations section.

In standard SQL, a programming block can be specified to be ATOMIC which means that either all of the database modifications made within the block succeed or none do. An ATOMIC block cannot contain a COMMIT or ROLLBACK statement (nor can it contain a START TRANSACTION) as that would violate its operational purpose. If the outermost block (i.e., BEGIN statement) specifies ATOMIC then any error that occurs will automatically cause all prior changes made by the stored procedure to be rolled back. If no errors occur, then the changes can be committed by the program or procedure that executed the CALL to the procedure.

If you want your stored procedure to commit its own changes (i.e., a transactional stored procedure) then under standard SQL it needs to issue its own START TRANSACTION and COMMIT statements. This technique, however, is not particularly useful when one wants to be assured that the stored procedure is guaranteed to be deadlock free.

In order to guarantee that a stored procedure is deadlock free, it needs to ensure that all needed locks are acquired at the beginning of the procedure. RDM SQL has extended the START TRANSACTION statement so that the tables that need to be locked can be explicitly specified. However, a better solution that does not require the user to have explicit control over the needed locks, is to use an alternative to BEGIN ATOMIC called BEGIN TRANSACTION. This will start a transaction and automatically acquire all of the locks needed by the SQL statements contained in the stored procedure. The associated (i.e., final) END statement will automatically commit those changes.

Note that if the stored procedure is called when a transaction is already active then the system-initiated START TRANSACTION will map to an RDM Core API nested transaction start and the changes "committed" by the END statement will only be committed to the database should the caller issue a COMMIT. This allows the stored procedure to function either as an independent transaction or as part of the caller's transaction. Note that, as with ATOMIC, the transaction associated with the BEGIN TRANSACTION cannot be committed or rolled back inside the stored procedure.

Example

create procedure begin_trans(
     newId char, 
     newName char) modifies sql data
begin transaction
     insert into acctmgr values newId, newName, current_date(), 0.025;
end;