2-12. Performing a Transaction
Problem
You need to complete a series of INSERT or UPDATE statements in order to process a complete transaction. In doing so, you need to ensure that if one of the statements fails, that all of the statements are canceled so that the transaction is not partially processed.
Solution
Use the transaction control mechanisms that are part of PL/SQL, as well as SQL itself, in order to control your transactions. When all your statements have been completed successfully, issue a COMMIT to make them final. On the other hand, if one of the statements does not complete successfully, then perform a ROLLBACK to undo all the other changes that have been made and bring the database back to the state that it was in prior to the transaction occurring.
In the following example, the code block entails the body of a script that is to be executed in order to create a new department and add some employees to it. The department change involves an INSERT and UPDATE statement to complete.
DECLARE
-- Query all programmers who make more than 4000
-- as they will be moved to the new 'Web Development' department
CURSOR new_dept_cur IS
SELECT *
FROM employees
WHERE job_id = 'IT_PROG'
AND salary > 4000
FOR UPDATE;
new_dept_rec new_dept_cur%ROWTYPE;
current_department departments.department_id%TYPE;
BEGIN
-- Create a new department
INSERT INTO departments values(
DEPARTMENTS_SEQ.nextval, -- Department ID (sequence value)
'Web Development', -- Department Title
103 -- Manager ID
1700); -- Location ID
-- Obtain the current department ID…the new department ID
SELECT DEPARTMENTS_SEQ.currval
INTO current_department
FROM DUAL;
-- Assign all employees to the new department
FOR new_dept_rec IN new_dept_cur LOOP
UPDATE employees
SET department_id = current_department
WHERE CURRENT OF new_dept_cur;
END LOOP;
COMMIT;
DBMS_OUTPUT.PUT_LINE('The transaction has been successfully completed.'),
END;
As you can see, a transaction was performed in this block of code. It is important to roll back changes if errors occur along the way so that the transaction is not partially completed.
How It Works
Transaction control is built into the Oracle Database. Any database changes that are made within a code block are visible to the current session only until a COMMIT has been made. The changes that have been made by the statements can be rolled back via the ROLLBACK command up until the point that a COMMIT is issued. Oracle uses table and row locks to ensure that data that has been updated in one session cannot be seen in another session until a COMMIT occurs.
A transaction is started when the first statement after the last COMMIT or ROLLBACK is processed or when a session is created. It ends when a COMMIT or ROLLBACK occurs. A transaction is not bound to a single code block, and any code block may contain one or more transactions. Oracle provides a SAVEPOINT command, which places a marker at the current database state so as to allow you to roll back to that point in time in a transaction. Oracle Database automatically issues a SAVEPOINT prior to processing the first statement in any transaction.
As a rule of thumb, it is always a good idea to have exception handling in place in case an exception occurs. However, if an unhandled exception occurs, then the database will roll back the statement that caused the exception, not the entire transaction. Therefore, it is up to the program to handle exceptions and issue the ROLLBACK command if the entire transaction should be undone. If a database crashes and goes down during a transaction, then when the database is restarted, all uncommitted statements are rolled back. All transactions are completed when a COMMIT or ROLLBACK is issued.