The transaction control commands that can be used in stored procedures are COMMIT and ROLLBACK
statements. The use of these commands within a stored procedure can affect tasks that are
being conducted outside of the stored procedure.
Using it in nonautocommit mode, it can affect another statement in a session.
That is, in case of issuing following statement, assume that statements below is executed in an nonautocommit mode.
In a proc1 procedure, commit statement is included in that procedure.
Even if a commit statement is executed inside of the procedure, a statement outside of the procedure will be complete
as a transaction on account of a previous commit statement.
Restrictions
It is impossible to execute the COMMIT or ROLLBACK commands while a cursor is open. That is, a
transaction that uses cursors must contain an entire cursor control block, comprising OPEN, FETCH,
and CLOSE statements.
Stored functions called from within SELECT statements cannot contain DML statements. In addition, they cannot include TCL statements.
LOB type variables cannot be declared in the declare section of a stored procedure. Additionally, the
%TYPE and %ROWTYPE attributes cannot be used to declare variables when the underlying column
in the actual database object is a LOB type column.
Because LOB type variables cannot be declared within stored procedures, data in LOB type columns
cannot be fetched using cursors. Therefore, LOB type columns cannot be referenced using cursor
control statements.
Example
ORACLE
ALTIBASE HDB
Comments
DECLARE
transfer NUMBER(8,2) := 250;
BEGIN
UPDATE accounts SET balance = balance - transfer WHERE account_id = 7715;
UPDATE accounts SET balance = balance + transfer WHERE account_id = 7720;
COMMIT COMMENT 'Transfer From 7715 to 7720' WRITE IMMEDIATE NOWAIT;
END;
/
CREATE OR REPLACE PROCEDURE PROC1
AS
transfer NUMBER(8,2) := 250;
BEGIN
UPDATE accounts SET balance = balance - transfer WHERE account_id = 7715;
UPDATE accounts SET balance = balance + transfer WHERE account_id = 7720;
COMMIT;
END;
/
Commit Statement
CREATE OR REPLACE PROCEDURE PROC1
AS
emp_id NUMBER(6);
emp_lastname VARCHAR2(25);
emp_salary NUMBER(8,2);
emp_jobid VARCHAR2(10);
BEGIN
SELECT eno, e_lastname, salary, emp_job INTO emp_id, emp_lastname,
emp_salary, emp_jobid FROM employees WHERE eno = 20;
INSERT INTO emp_name VALUES (emp_id, emp_lastname);
INSERT INTO emp_sal VALUES (emp_id, emp_salary);
INSERT INTO emp_job VALUES (emp_id, emp_jobid);
EXCEPTION
WHEN DUP_VAL_ON_INDEX THEN
ROLLBACK;
DBMS_OUTPUT.PUT_LINE('Inserts have been rolled back');
END;
/
CREATE OR REPLACE PROCEDURE PROC1
AS
emp_id NUMBER(6);
emp_lastname VARCHAR2(25);
emp_salary NUMBER(8,2);
emp_jobid VARCHAR2(10);
BEGIN
SELECT eno, e_lastname, salary, emp_job INTO emp_id, emp_lastname,
emp_salary, emp_jobid FROM employees WHERE eno = 20;
INSERT INTO emp_name VALUES (emp_id, emp_lastname);
INSERT INTO emp_sal VALUES (emp_id, emp_salary);
INSERT INTO emp_job VALUES (emp_id, emp_jobid);
EXCEPTION
WHEN DUP_VAL_ON_INDEX THEN
ROLLBACK;
SYSTEM_.PRINTLN('Inserts have been rolled back');
END;
/
Rollback Statement
CREATE OR REPLACE PROCEDURE PROC1
AS
emp_id employees.eno%TYPE;
emp_lastname employees.e_lastname%TYPE;
emp_salary employees.salary%TYPE;
BEGIN
SELECT eno, e_lastname, salary INTO emp_id, emp_lastname,
emp_salary FROM employees WHERE eno = 120;
SAVEPOINT my_savepoint;
UPDATE emp_name SET salary = salary * 1.1 WHERE eno = emp_id;
DELETE FROM emp_name WHERE eno = 10;
SAVEPOINT my_savepoint; -- move my_savepoint to current poin
INSERT INTO emp_name VALUES (emp_id, emp_lastname, emp_salary);
EXCEPTION
WHEN DUP_VAL_ON_INDEX THEN
ROLLBACK TO my_savepoint;
DBMS_OUTPUT.PUT_LINE('Transaction rolled back.');
END;
/
CREATE OR REPLACE PROCEDURE PROC1
AS
emp_id employees.eno%TYPE;
emp_lastname employees.e_lastname%TYPE;
emp_salary employees.salary%TYPE;
BEGIN
SELECT eno, e_lastname, salary INTO emp_id, emp_lastname,
emp_salary FROM employees WHERE eno = 120; SAVEPOINT my_savepoint;
UPDATE emp_name SET salary = salary * 1.1 WHERE eno = emp_id;
DELETE FROM emp_name WHERE eno = 10;
SAVEPOINT my_savepoint; -- move my_savepoint to current point
INSERT INTO emp_name VALUES (emp_id, emp_lastname, emp_salary);
EXCEPTION
WHEN DUP_VAL_ON_INDEX THEN ROLLBACK TO my_savepoint;
SYSTEM_.PRINTLN('Transaction rolled back.');
END;
/
Savepoint statement cannot be issued
within the ALTIBASE Stored Procedure.
CREATE OR REPLACE PROCEDURE PROC1
AS
daily_order_total NUMBER(12,2);
weekly_order_total NUMBER(12,2);
monthly_order_total NUMBER(12,2);
BEGIN
COMMIT; -- ends previous transaction
SET TRANSACTION READ ONLY NAME 'Calculate Order Totals';
SELECT SUM (order_total) INTO daily_order_total FROM orders
WHERE order_date = SYSDATE;
SELECT SUM (order_total) INTO weekly_order_total FROM orders
WHERE order_date = SYSDATE - 7;
SELECT SUM (order_total) INTO monthly_order_total FROM orders
WHERE order_date = SYSDATE - 30;
COMMIT; -- ends read-only transaction
END;
/
CREATE OR REPLACE PROCEDURE PROC1
AS
daily_order_total NUMBER(12,2);
weekly_order_total NUMBER(12,2);
monthly_order_total NUMBER(12,2);
BEGIN
COMMIT; -- ends previous transaction SET TRANSACTION READ ONLY NAME 'Calculate Order Totals';
SELECT SUM (order_total) INTO daily_order_total FROM orders
WHERE order_date = SYSDATE;
SELECT SUM (order_total) INTO weekly_order_total FROM orders
WHERE order_date = SYSDATE - 7;
SELECT SUM (order_total) INTO monthly_order_total FROM orders
WHERE order_date = SYSDATE - 30;
COMMIT; -- ends read-only transaction
END;
/
Set Transaction statement cannot be issued
within the ALTIBASE Stored Procedure.
CREATE OR REPLACE PROCEDURE PROC1
AS
-- if "FOR UPDATE OF salary" is included on following line, an error is raised
CURSOR c1 IS SELECT * FROM employees;
emp_rec employees%ROWTYPE;
BEGIN
OPEN c1;
LOOP
FETCH c1 INTO emp_rec; -- FETCH fails on the second iteration with FOR UPDATE
EXIT WHEN c1%NOTFOUND;
IF emp_rec.eno = 10 THEN
UPDATE employees SET salary = salary * 1.05 WHERE eno = 10;
END IF;
COMMIT; -- releases locks
END LOOP;
END;
/
CREATE OR REPLACE PROCEDURE PROC1
AS
-- if "FOR UPDATE OF salary" is included on following line, an error is raised
CURSOR c1 IS SELECT * FROM employees;
emp_rec employees%ROWTYPE;
BEGIN
OPEN c1;
LOOP
FETCH c1 INTO emp_rec; -- FETCH fails on the second iteration with FOR UPDATE
EXIT WHEN c1%NOTFOUND;
IF emp_rec.eno = 10 THEN
UPDATE employees SET salary = salary * 1.05 WHERE eno = 10;
END IF;
COMMIT; -- releases locks
END LOOP;
END;
/
Fetch Across Commit
iSQL> exec proc1;
[ERR-31168 : Invalid CURSOR
0009 : FETCH C1 INTO EMP_REC;
-- FETCH fails on the second iteration with
FOR UPDATE ^ ^ ]
It is supported in the ALTIBASE Stored Procedure.
But, it will be applied in the Altibase HDB V7.
