Oracle存储过程中的commit 和 savepoint

Oracle存储过程中的commit 和 savepoint

Oracle 11g

CREATE OR REPLACE PROCEDURE skeleton 
IS
BEGIN
  begin
       insert into a values(10);
       begin
         insert into a values(11);
       end;
  end;
  begin
    --savepoint ps;
    insert into a values(20);
    commit;
  end;
 
  begin
    insert into a values(30);
  end;
  insert into a values(40);
  --commit;
  rollback;
  --rollback to ps;
END;

在oracle 中，begin end 只起标记作用，commit会把前面没提交的全部提交，不管begin ,end所在的层次，而且提交后，后面的rollback 也无法回滚，

savepoint  和 rollback 可以在不同的begin end 中，而且一但commit之后，savepoint将失效。

exec skeleton();

在postgresql 9.0中

CREATE OR REPLACE function skeleton() RETURNS VOID AS
$$
BEGIN
  insert into a values(0);
  begin
    --savepoint ps;
    insert into a values(1);
    --commit;
  end;
 
  begin
    insert into a values(2);
  end;
  insert into a values(3);
  --commit;
  --rollback to ps;
  --ROLLBACK;
END;
EXCEPTION WHEN unique_violation THEN

$$LANGUAGE plpgsql;

不支持存储过程，只支持function,

在function之中，不支持rollback ,commit, savepoint

Question 1:  does pg/psql functions allow "SAVEPOINT/ROLLBACK" functionality? (if so how?)

Yes.  However, you cannot use that syntax directly.  You rather use it by establishing EXCEPTION clauses in BEGIN/END blocks.  Upon entering any BEGIN/END block which has an EXCEPTION clause, an implicit SAVEPOINT

is executed.  If any exception (read: error) is found while executing the block, the savepoint will be automatically rolled back and control passed to the EXCEPTION block.

It is important not to confuse the use of BEGIN/END for grouping statements in PL/pgSQL with the database commands for transaction control. PL/pgSQL's BEGIN/END are only for grouping; they do not start or end a transaction. Functions and trigger procedures are always executed within a transaction established by an outer query — they cannot start or commit that transaction, since there would be no context for them to execute in. However, a block containing an EXCEPTION clause effectively forms a subtransaction that can be rolled back without affecting the outer transaction.

mysql

DELIMITER $$

DROP PROCEDURE IF EXISTS `a`.`skeleton` $$
CREATE PROCEDURE `a`.`skeleton` ()
BEGIN
 begin
       insert into a values(10);
       begin
         insert into a values(11);
       end;
      -- rollback;
  end;

  begin
    insert into a values(20);
    -- commit;
  end;

  START TRANSACTION;
  -- savepoint ps1;
  begin
    insert into a values(30);
  end;
  -- rollback to savepoint ps1;

  insert into a values(40);
  -- commit;
  rollback;

END $$
DELIMITER ;