本文比較了在儲存過程中事務管理上Oracle和PG的異同.

**Oracle**
測試指令碼:
```
-- drop table if exists t1;
-- create table t1(id int);

set serveroutput on

declare
begin
    delete from t1;
    insert into t1 values(1);
    begin
      insert into t1 values (2); 
      commit;
      insert into t1 values ('a');
    exception when others then      
      dbms_output.put_line('ERROR：'||SQLERRM);
      insert into t1 values(3);
      dbms_output.put_line('INSERT：3');
    end;
    insert into t1 values(4);
    dbms_output.put_line('INSERT：4');
    commit;    
end;
/

select * from t1;

```
執行結果:
```
SQL> set serveroutput on
SQL>
SQL> declare
  2  begin
  3      delete from t1;
  4      insert into t1 values(1);
  5      begin
  6        insert into t1 values (2);
  7        commit;
  8        insert into t1 values ('a');
  9      exception when others then
 10        dbms_output.put_line('ERROR：'||SQLERRM);
 11        insert into t1 values(3);
 12        dbms_output.put_line('INSERT：3');
 13      end;
 14      insert into t1 values(4);
 15      dbms_output.put_line('INSERT：4');
 16      commit;
 17  end;
 18  /
ERROR：ORA-01722: 無效數字
INSERT：3
INSERT：4

PL/SQL 過程已成功完成。

SQL>
SQL> select * from t1;

ID
----------
         1
         2
         3
         4

SQL>
```
執行完成後，t1表中有4行記錄。

**PostgreSQL**
測試指令碼：
```
-- drop table if exists t1;
-- create table t1(id int);

do $$
declare
begin
    delete from t1;
    insert into t1 values(1);
    begin
      insert into t1 values (2); 
      commit;
      insert into t1 values ('a');
    exception when others then
      raise notice 'ERROR：%',SQLERRM;
      insert into t1 values(3);
      raise notice 'INSERT：%',3;
    end;
    insert into t1 values(4);
    raise notice 'INSERT：%',4;
    commit;    
end;
$$
;

select * from t1;

```
執行結果：
```
testdb=# do $$
testdb$# declare
testdb$# begin
testdb$#     delete from t1;
testdb$#     insert into t1 values(1);
testdb$#     begin
testdb$#       insert into t1 values (2);
testdb$#       commit;
testdb$#       insert into t1 values ('a');
testdb$#     exception when others then
testdb$#       raise notice 'ERROR：%',SQLERRM;
testdb$#       insert into t1 values(3);
testdb$#       raise notice 'INSERT：%',3;
testdb$#     end;
testdb$#     insert into t1 values(4);
testdb$#     raise notice 'INSERT：%',4;
testdb$#     commit;
testdb$# end;
testdb$# $$
testdb-# ;
NOTICE:  ERROR：cannot commit while a subtransaction is active
NOTICE:  INSERT：3
NOTICE:  INSERT：4
DO
testdb=#
testdb=# select * from t1;
 id
----
  1
  3
  4
(3 rows)

```
在PG中，執行結果只有3行，原因是在內層的begin/end中的commit出現異常（cannot commit while a subtransaction is active），值2沒有插入到資料表中。
關於子事務的進一步資訊，可參見參考資料中的連結。

**參考資料**
[PostgreSQL Subtransactions Considered Harmful](https://postgres.ai/blog/20210831-postgresql-subtransactions-considered-harmful)
[Subtransactions and performance in PostgreSQL](https://www.cybertec-postgresql.com/en/subtransactions-and-performance-in-postgresql/)

Oracle vs PostgreSQL Develop（29） - COMMIT in PLSQL

相關文章