Commit and rollback in Oracle stored procedure

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?

You can do this by using a save point.
CREATE OR REPLACE PROCEDURE SPTest
AS
BEGIN
-- We create a savepoint here.
SAVEPOINT sp_sptest;

INSERT INTO emptest(empid, empname, deptno)
VALUES(1, 'ravi', 10);

INSERT INTO test1(id, name, sal)
VALUES(1, 'raju', 4444);

UPDATE emptest
SET empname = 'hari'
WHERE empid = 1;

-- If any exception occurs
EXCEPTION
WHEN OTHERS THEN
-- We roll back to the savepoint.
ROLLBACK TO sp_sptest;

-- And of course we raise again,
-- since we don't want to hide the error.
-- Not raising here is an error!
RAISE;
END;


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 to
DECLARE
BEGIN
sptest();
EXCEPTION
ROLLBACK;
RAISE;
END;


But 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.

StackExchange Database Administrators Q#84769, answer score: 9