Menu
In an Oracle stored procedure, how do I write a transaction? Do I need to do it explicitly or will Oracle automatically lock rows?
222
Yes, a stored procedure can be run inside a transaction.
Return Values: Stored Procedures can return values but Triggers cannot return value. Transaction: Transaction statements such as begin transaction, commit transaction, and rollback inside a Stored Procedure. But, these statements cannot be used inside Trigger. Calling: Stored Procedure can be called inside a Trigger.
A transaction is a logical, atomic unit of work that contains one or more SQL statements. A transaction groups SQL statements so that they are either all committed, which means they are applied to the database, or all rolled back, which means they are undone from the database.
Implicit commit is issued by Oracle database engine automatically after most of the DDL (alter, drop, create etc) execution. In other words, the commit does not need your interference. If you enable auto commit enabled on your SQL*Plus or if you enabled auto commit on exit also can be considered as implicit commits.
You might want to browse the concept guide, in particular the chapter about transactions:
A transaction is a logical unit of work that comprises one or more SQL statements run by a single user. [...] A transaction begins with the user's first executable SQL statement. A transaction ends when it is explicitly committed or rolled back by that user.
You don't have to explicitely start a transaction, it is done automatically. You will have to specify the end of the transaction with a commit (or a rollback).
The locking mechanism is a fundamental part of the DB, read about it in the chapter Data Concurrency and Consistency.
A stored procedure is a set of statements, they are executed in the same transaction as the calling session (*). Usually, transaction control (commit and rollback) belongs to the calling application. The calling app has a wider vision of the process (which may involve several stored procedures) and is therefore in a better position to determine if the data is in a consistent state. While you can commit in a stored procedure, it is not the norm.
(*) except if the procedure is declared as an autonomous transaction, in which case the procedure is executed as an independent session (thanks be here now, now I see your point).
190
@AdamStevenson Concerning DDL, there's a cite from the Concept's Guide:
If the current transaction contains any DML statements, Oracle first commits the transaction, and then runs and commits the DDL statement as a new, single statement transaction.
So if you have started a transaction before the DDL statement (e.g. wrote an INSERT, UPDATE, DELETE, MERGE statements), the transaction started will be implicitly commited - you should always keep that in mind when processing DML statements.
I agree with Vincent Malgrat, you might find some very useful information about transaction processing at the Concept's Guide.
39
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
