Products Support Documentation Download
Compound Statements

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
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. The following simple example illustrates this behavioral property.
create procedure begin_trans(
newId char,
newName char) modifies sql data
begin transaction
insert into acctmgr values newId, newName, current_date(), 0.025;
end;
;
commit;

The above stored procedure text is stored in file begin_trans.sql which is compiled and executed as shown in the following rdm-sql script.

rdm-sql: use bookshop;
rdm-sql: .r begin_trans.sql
rdm-sql: select * from acctmgr order by hire_date;
MGRID | NAME | HIRE_DATE | COMMISSION
-------+-------------------------+-----------+-----------
BARNEY | Noble, Barney | 1972-05-08| 0.035
FRANK | Doel, Frank | 1987-02-13| 0.030
KLARA | Novac, Klara | 1990-01-02| 0.025
AMY | Zonn, Amy | 1994-07-06| 0.025
ALFRED | Kralik, Alfred | 1997-07-02| 0.025
JOE | Fox, Joe | 1998-12-18| 0.025
KATE | Kelly, Kathleen | 1998-12-18| 0.025
*** 7 row(s) returned
rdm-sql:
rdm-sql: call begin_trans("SLY", "Stallone, Sylvester");
*** 1 row(s) affected
rdm-sql: select * from acctmgr order by hire_date;
MGRID | NAME | HIRE_DATE | COMMISSION
-------+-------------------------+-----------+-----------
BARNEY | Noble, Barney | 1972-05-08| 0.035
FRANK | Doel, Frank | 1987-02-13| 0.030
KLARA | Novac, Klara | 1990-01-02| 0.025
AMY | Zonn, Amy | 1994-07-06| 0.025
ALFRED | Kralik, Alfred | 1997-07-02| 0.025
JOE | Fox, Joe | 1998-12-18| 0.025
KATE | Kelly, Kathleen | 1998-12-18| 0.025
SLY | Stallone, Sylvester | 2018-04-30| 0.025
*** 8 row(s) returned
rdm-sql:
rdm-sql: rollback;
rdm-sql: select * from acctmgr order by hire_date;
MGRID | NAME | HIRE_DATE | COMMISSION
-------+-------------------------+-----------+-----------
BARNEY | Noble, Barney | 1972-05-08| 0.035
FRANK | Doel, Frank | 1987-02-13| 0.030
KLARA | Novac, Klara | 1990-01-02| 0.025
AMY | Zonn, Amy | 1994-07-06| 0.025
ALFRED | Kralik, Alfred | 1997-07-02| 0.025
JOE | Fox, Joe | 1998-12-18| 0.025
KATE | Kelly, Kathleen | 1998-12-18| 0.025
SLY | Stallone, Sylvester | 2018-04-30| 0.025
*** 8 row(s) returned

The above sequence shows that the execution of the begin_trans stored procedure was executed as a complete transaction. Note that the acctmgr row with mgrid "SLY" remained in the table after the rollback was issued.

However, in the following sequence, a start transaction is executed before the call to begin_trans to add "CLINT" to the acctmgr table so that the changes made by the procedure are part of the outer transaction so that the rollback removes CLINT from the table.

rdm-sql: start transaction;
rdm-sql: call begin_trans("CLINT","Eastwood, Clint");
*** 1 row(s) affected
rdm-sql:
rdm-sql: select * from acctmgr order by hire_date;
MGRID | NAME | HIRE_DATE | COMMISSION
-------+-------------------------+-----------+-----------
BARNEY | Noble, Barney | 1972-05-08| 0.035
FRANK | Doel, Frank | 1987-02-13| 0.030
KLARA | Novac, Klara | 1990-01-02| 0.025
AMY | Zonn, Amy | 1994-07-06| 0.025
ALFRED | Kralik, Alfred | 1997-07-02| 0.025
KATE | Kelly, Kathleen | 1998-12-18| 0.025
JOE | Fox, Joe | 1998-12-18| 0.025
SLY | Stallone, Sylvester | 2018-04-30| 0.025
CLINT | Eastwood, Clint | 2018-04-30| 0.025
*** 9 row(s) returned
rdm-sql:
rdm-sql: rollback;
rdm-sql: select * from acctmgr order by hire_date;
MGRID | NAME | HIRE_DATE | COMMISSION
-------+-------------------------+-----------+-----------
BARNEY | Noble, Barney | 1972-05-08| 0.035
FRANK | Doel, Frank | 1987-02-13| 0.030
KLARA | Novac, Klara | 1990-01-02| 0.025
AMY | Zonn, Amy | 1994-07-06| 0.025
ALFRED | Kralik, Alfred | 1997-07-02| 0.025
JOE | Fox, Joe | 1998-12-18| 0.025
KATE | Kelly, Kathleen | 1998-12-18| 0.025
SLY | Stallone, Sylvester | 2018-04-30| 0.025
*** 8 row(s) returned