ALLBASE/SQL Reference Manual (36216-90216)

Chapter 12 547
SQL Statements S - Z
SET TRANSACTION
Example
Declare multiple cursors
DECLARE C1 CURSOR FOR SELECT BranchNo FROM Branches
WHERE TellerNo > :TellerNo
DECLARE C2 CURSOR FOR SELECT BranchNo FROM Tellers
WHERE BranchNo = :HostBranchNo FOR UPDATE OF Credit
DECLARE C3 CURSOR FOR SELECT * FROM PurchDB.Parts
Set the isolation level to RC.
SET TRANSACTION ISOLATION LEVEL RC, PRIORITY 100, LABEL 'xact1'
.
.
.
Implicit BEGIN WORK with transaction isolation level RC.
OPEN C1
FETCH C1 INTO :HostBranchNo1
.
.
.
Change isolation level to CS.
SET TRANSACTION ISOLATION LEVEL CS
OPEN C2
FETCH C2 INTO :HostBranchNo2
UPDATE Tellers SET Credit = Credit * 0.005
WHERE CURRENT OF C2
CLOSE C2 Close cursor C2.
CLOSE C1 Close cursor C1.
Change the transaction isolation level back to RC.
SET TRANSACTION ISOLATION LEVEL RC
OPEN C3
FETCH C3 INTO :PartsBuffer
.
.
.
End the transaction. Transaction attributes return to those set at the session level or to
the session default.
COMMIT WORK