In my Oracle stored procedure, I have multiple insert and update statements like below:
create or replace PROCEDURE SPTest
AS
BEGIN
insert into emptest(empid,empname,deptno)
(1,'ravi',10);
insert into test1(id,name,sal)
(1,'raju',4444);
update emptest set empname='hari' where empid=1;
END;
If I get any error in any statement, I want to rollback all insert and update statements.
How can I implement commit and rollback in this stored procedure?
Best Answer
You can do this by using a save point.
The commit will typically be left to the caller. This construct just guarantees that either all of the inserts and the update are done, or none of them is.
On the most outer level, Oracle will do a rollback by itself. In other words, if you do
EXEC sptest();
on the SQL+ command line, it will be expanded automatically toBut if a calling procedure has exception handling, this doesn't happen, since the exception may not get to that outer level. So you may end up with the inserts done, the update throwing an exception which may be caught and handled by the caller, so that the update will have failed but the inserts succeeded.
By doing a rollback in the stored procedure to the beginning of the procedure, you ensure that all of them succeed, or none of them do.