Oracle存储过程中的commit 和 savepoint

$$LANGUAGE plpgsql;不支持存储过程，只支持function,在function之中，不支持rollback ,commit, savepointQuestion 1: does pg/

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 本文来源gao@!dai!ma.com搞$$代^@码网*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 ;

，

喜欢 (0)赏

[搞代码]

分享 (0)