Commit and rollback in Oracle 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.