If you are coming to Oracle from another database, you are in for some surprises. One of them is the key word BEGIN. They don’t mean the same in Oracle. Read on.
A TRANSACTION block
In SQL Standards they list Transaction control statements such as START TRANSACTION,
END TRANSACTION, and SAVE TRANSACTION. Different database vendors implement this as they see fit. For e.g., Informix always had a “BEGIN [WORK]” statement to mark the beginning of a transaction block. The block will end when an explicit
COMMIT or ROLLBACK is issued or SQL ends abnormally (in which case it’s rolled back).
So in non-ORACLE database (Informix for example), you may have:
BEGIN [WORK]
–(1)
INSERT….
UPDATE…
DELETE…
COMMIT [WORK];
– << commit marks the end of the current transaction>>
In Oracle, there is no BEGIN [WORK] equivalent. It’s just “understood” when the first executable statement makes some changes to the database. This includes DML and DDL statements.
So, the above statement block in Oracle will be written as:
– << implicit Transaction block begins>>
–(1)
INSERT….
UPDATE…
DELETE…
COMMIT [WORK];
– << commit marks the end of the current transaction>>
So, there you have it. In Oracle, an implicit transaction block is started when the first DML (here INSERT) statement is executed and it is not started with an explicit BEGIN or
START
keyword. On the other hand,
COMMIT or
ROLLBACK in Oracle does mark the end of a transaction. (There are situations where a transaction may be ended implicitly as well. DDL statements for e.g., participate in implicit transactions).
BEGIN…END in Oracle
But then, you have surely seen a BEGIN (and END) in Oracle? Yes, you are correct about that keyword being there, only it’s a statement in PL/SQL not plain SQL. PL/SQL is a separate programming language in Oracle, just like C or Java, only PL/SQL is specialized to handle SQL more efficiently.
This BEGIN statement in PL/SQL can be easily confused with BEGIN [WORK] in other databases, if you are new to Oracle. A
BEGIN..END block in PL/SQL is merely a syntactical collection of statements (SQL, non-SQL). They don’t relate to transactions. However, since a bunch of SQL statements inside the
BEGIN..END block mark a single PL/SQL statement, a COMMIT (or
ROLLBACK) after a PL/SQL block could commit (or rollback) entire list of SQL statements within the PL/SQL block, thus implying
BEGIN had to do with a new transaction. Not so! Suppose you had couple of PL/SQL blocks (BEGIN…END) before a
COMMIT is issued, statements
in both those blocks are committed. If you stopped at the end of first PL/SQL block, (marked by the first "/"), it won't be committed yet.
– <PLSQL BLock 1>
BEGIN
<SQL statements>
END;
/
– <PLSQL BLock 1>
BEGIN
<SQL Statements>
END;
/
COMMIT;
Here COMMIT applies to all the statements above (here PLSQL blocks 1 and 2), showing us that PL/SQL block does not correspond to a transaction block.
