DWDW

Database Programming with
PL/SQL
3-4
Using Transaction Control Statements
Copyright © 2015, Oracle and/or its affiliates. All rights reserved.

Objectives
This lesson covers the following objectives:

• Define a transaction and provide an example
• Construct and execute a transaction control statement in
PL/SQL
• Since Oracle Application Express automatically commits
changes, the following information will be presented as if you
were issuing the commands in an installed/local environment
with the ability to use COMMIT and ROLLBACK.
PLSQL 3-4 Copyright © 2015, Oracle and/or its affiliates. All rights reserved. 3
Purpose
• In this lesson, you learn how to include transaction control

statements such as COMMIT, ROLLBACK, and SAVEPOINT in
PL/SQL.
• Just think, if you write a paper for your teacher in pencil, you
have captured your thoughts but not yet turned the paper in.
If you want to change something on your paper, you can use
an eraser to make the change.
Purpose
• But once you turn the paper in to your teacher, you have
completed the task. Transaction control statements in PL/SQL
allow you to do the same thing using the keywords COMMIT,
ROLLBACK, and SAVEPOINT.
Database Transaction
• A transaction is an inseparable list of database operations

that must be executed either in its entirety or not at all.
Transactions maintain data integrity and guarantee that the
database is always in a consistent state.
Example of a Transaction
• To illustrate the concept of a transaction, consider a banking

database. When a bank customer transfers money from a
savings account to a checking account, the transaction can
consist of three separate operations:
Decrease savings Increase checking Record the

account account transaction in the
balance. balance. transaction journal.
Transaction
• What would happen if there were insufficient funds in the
savings account? Would the funds still be added to the
checking account? Would an entry be logged in the
transaction journal? What do you think should happen?
Decrease savings UPDATE savings_accounts

account SET balance = balance - 500
balance. WHERE account = 3209;
Increase checking UPDATE checking_accounts

account SET balance = balance + 500
balance. WHERE account = 3208;
Record the transaction INSERT INTO journal VALUES

in the transaction (journal_seq.NEXTVAL, '1B'
journal. 3209, 3208, 500);
• If all three SQL statements can be performed to maintain the
accounts in proper balance, the effects of the transaction can
be committed, or applied to the database tables.
If OK, then
Decrease savings
account.
Increase checking If OK, then

account.
Record the
transaction in the If OK, then
transaction journal. COMMIT!
• However, if a problem, such as insufficient funds, invalid
account number, or a hardware failure prevents one or two of
the statements in the transaction from completing, the entire
transaction must be rolled back (reversed out) so that the
balance of all accounts is correct.
If OK, then
Decrease savings
account.
Increase checking If not OK then

ROLLBACK!
account.
Record the
transaction in the
transaction journal.
Transaction Control Statements
• You use transaction control statements to make the changes
to the database permanent or to discard them. The three
main transaction control statements are:
– COMMIT
– ROLLBACK
– SAVEPOINT
• The transaction control commands are valid in PL/SQL and

therefore can be used directly in the executable or exception
section of a PL/SQL block.
COMMIT
• COMMIT is used to make the database changes permanent. If

a transaction ends with a COMMIT statement, all the changes
made to the database during that transaction are made
permanent.
BEGIN
INSERT INTO pairtable VALUES (1, 2);
COMMIT;
END;
• Note: The keyword END signals the end of a PL/SQL block, not
the end of a transaction.
ROLLBACK
• ROLLBACK is for discarding any changes that were made to

the database after the last COMMIT. If the transaction fails, or
ends with a ROLLBACK, then none of the statements takes
effect.
• In the example, only the second INSERT statement adds a row
of data.
BEGIN
ROLLBACK;
COMMIT;
END;
SAVEPOINT
• SAVEPOINT is used to mark an intermediate point in

transaction processing. Only ROLLBACK can be used to a
SAVEPOINT.
BEGIN
SAVEPOINT my_sp_1;
SAVEPOINT my_sp_2;
ROLLBACK to my_sp_1;
COMMIT;
END;
Terminology
Key terms used in this lesson included:

• COMMIT
• END
• ROLLBACK
• SAVEPOINT
• Transaction
Summary
In this lesson, you should have learned how to:

• Define a transaction and provide an example
• Construct and execute a transaction control statement in
PL/SQL

DWDW

Uploaded by

Copyright:

Available Formats

DWDW

Uploaded by

Document Information

Original Title

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

DWDW

Uploaded by

Copyright:

Available Formats

Database Programming with

Copyright © 2015, Oracle and/or its affiliates. All rights reserved.

This lesson covers the following objectives:

• In this lesson, you learn how to include transaction control

• A transaction is an inseparable list of database operations

• To illustrate the concept of a transaction, consider a banking

Decrease savings Increase checking Record the

Decrease savings UPDATE savings_accounts

Increase checking UPDATE checking_accounts

Record the transaction INSERT INTO journal VALUES

Increase checking If OK, then

Increase checking If not OK then

• The transaction control commands are valid in PL/SQL and

• COMMIT is used to make the database changes permanent. If

• ROLLBACK is for discarding any changes that were made to

• SAVEPOINT is used to mark an intermediate point in

Key terms used in this lesson included:

In this lesson, you should have learned how to:

You might also like