Início / dba / Perguntas / 84769
Accepted
Pydi Raju
Asked: 2014-12-10 23:42:16 +0800 CST

Confirmar e reverter no procedimento armazenado do Oracle

  • 772

No meu procedimento armazenado Oracle, tenho várias instruções de inserção e atualização como abaixo:

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;

Se eu receber algum erro em qualquer instrução, quero reverter todas as instruções de inserção e atualização.

Como posso implementar commit e rollback neste procedimento armazenado?

oracle oracle-11g

2 respostas

  1. Best Answer

    Você pode fazer isso usando um ponto de salvamento.

    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;

    O commit normalmente será deixado para o chamador. Essa construção apenas garante que todas as inserções e a atualização sejam feitas ou nenhuma delas.

    No nível mais externo, o Oracle fará um rollback sozinho. Em outras palavras, se você fizer isso EXEC sptest();na linha de comando SQL+, ela será expandida automaticamente para

    DECLARE
BEGIN
   sptest();
EXCEPTION
   ROLLBACK;
   RAISE;
END;

    Mas se um procedimento de chamada tem tratamento de exceção, isso não acontece, pois a exceção pode não chegar a esse nível externo. Portanto, você pode acabar com as inserções feitas, a atualização lançando uma exceção que pode ser capturada e tratada pelo chamador, de modo que a atualização falhará, mas as inserções foram bem-sucedidas.

    Ao fazer uma reversão no procedimento armazenado para o início do procedimento, você garante que todos eles sejam bem-sucedidos ou nenhum deles.

    • 8 
  2. --Rollback on Exception else Commit;

--SET TRANSACTION READ WRITE;

 BEGIN
  SAVEPOINT ABC;  
  update table_A set column1 ='Y' where id = 69;

  update table_A set column2 ='S'  where id = 70; 

  EXCEPTION
  WHEN OTHERS THEN   
  ROLLBACK TO ABC;   
COMMIT;

END;

--createdon is of datetime type and has an exception. So rollback will happen.
    • 0

relate perguntas