Oracle 儲存過程中的送出和回滾

在我的 Oracle 儲存過程中，我有多個插入和更新語句，如下所示：

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;

如果我在任何語句中遇到任何錯誤，我想回滾所有插入和更新語句。

如何在這個儲存過程中實現送出和回滾？

您可以使用保存點來執行此操作。

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;

送出通常留給呼叫者。這個結構只是保證所有的插入和更新都完成了，或者沒有一個。

在最外層，Oracle 將自己進行回滾。換句話說，如果你EXEC sptest();在 SQL+ 命令行上做，它會自動擴展為

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

但是如果呼叫過程有異常處理，則不會發生這種情況，因為異常可能不會到達那個外層。因此，您最終可能會完成插入，更新會引發異常，呼叫者可能會擷取並處理該異常，因此更新將失敗但插入成功。

通過在儲存過程中回滾到過程的開頭，您可以確保所有這些都成功，或者它們都不成功。

引用自：https://dba.stackexchange.com/questions/84769