Oracle存储过程中的commit 和 savepoint

Oracle 11gCREATE 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, savepointQuestion 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 SAVEPOINTis 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.mysqlDELIMITER $$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 ;Oracle SQL 空值排序（Nulls）Linux下Oracle 11G r2 （64位）安装说明相关资讯 Oracle教程

Oracle中纯数字的varchar2类型和（07/29/2015 07:20:43）
Oracle教程：Oracle中查看DBLink密（07/29/2015 07:16:55）
[Oracle] SQL*Loader 详细使用教程（08/11/2013 21:30:36）

Oracle教程：Oracle中kill死锁进程（07/29/2015 07:18:28）
Oracle教程：ORA-25153 临时表空间（07/29/2015 07:13:37）
Oracle教程之管理安全和资源（04/08/2013 11:39:32）

本文评论查看全部评论（0）