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 ;