1

i 'm new member. please help me confirm my question. Why store procedure don't need commit statement in oracle ? Thank you for your answers

Mr Sơn
  • 21
  • 1
  • 1
  • 4
  • 4
    Because whatever is calling it can (and usually should) be handling transaction control. A procedure doesn't necessarily have to do any data manipulation anyway. If you have a scenario you don't understand it would be better to describe that and include the relevant code (or a simplified version) in your question. – Alex Poole Aug 06 '18 at 10:13
  • 4
    Probably a duplicate of https://stackoverflow.com/q/41998025/1509264 – MT0 Aug 06 '18 at 10:15
  • 2
    thank you. My question mean: Procedure(begin DML.......... End).Inside Code there is no commit statement. But it still save to database – Mr Sơn Aug 06 '18 at 10:21
  • 3
    @MrSơn - then whatever is calling the procedure is committing, either explicitly or because auto-commit is on. – Alex Poole Aug 06 '18 at 10:22
  • 2
    You should not have a `COMMIT` statement in a stored procedure (with a few limited exceptions such as autonomous transactions). Instead the effects of the procedure should be `COMMIT` or `ROLLBACK` within the transaction (external to the procedure) as this allows you to chain multiple stored procedures and then `COMMIT` them together or `ROLLBACK` them all if your business logic requires it. If you have a `COMMIT` in each then when one is finished you cannot `ROLLBACK` the data even if your business logic requires it when a later procedure fails. – MT0 Aug 06 '18 at 10:30

2 Answers2

2

Mr Son. There is a thing called "good practices", and regarding "commit" with stored procedures, there are several points:

  • The user, who calls your SP, controls the transaction. He should decide what he wants - commit or rollback. Not us. Your SP could be just a small part of bigger process;
  • Many frameworks, have build in transaction controls - front-end developers can control the flow in applications/etc.
Ychdziu
  • 435
  • 5
  • 10
2

A stored procedure may have a commit if there is a DML operation in the procedure and there is no restriction from Oracle's side that we cannot have commit or rollback in the procedure. How to control commit and rollback is totally depended upon business definition of a transaction and is independent of the stored procedure's functionality. We commit or rollback a whole transaction. A transaction may be made up of one or more procedure so we don't handle commit or rollback at the individual procedure level. Another case is if you are calling a stored procedure from some other external application and auto-commit is ON for your database then also data will be auto-committed or in some libraries and framework auto-commit is enabled if the transaction is initiated using that framework or library. So when the external application (written in java or shell script for any other lang) calls for the procedure and makes a connection request and opens a session post-processing when it disconnects as auto-commit is on it will commit the changes. But if you try the same thing with sql developer you will notice that once you roll back the changes it reverts the changes.

select * from temp;

enter image description here

create or replace procedure proc_temp as
begin
 insert into temp values (11,sysdate,'CPU','CPU needs');
end;
/

exec proc_temp;
Bhanu Yadav
  • 169
  • 7