［PLSQL］Stored-Procedure/Exception/Transaction test case
Initial
CREATE TABLE T_TRAN_PROC (
COLUMN_1 VARCHAR2(10) NOT NULL,
COLUMN_2 NUMBER
) ;
TRUNCATE TABLE T_TRAN_PROC ;
Case 1 : Without Rollback
begin
INSERT INTO T_TRAN_PROC VALUES ('P1',1) ;
INSERT INTO T_TRAN_PROC VALUES ('R2','A') ;
INSERT INTO T_TRAN_PROC VALUES ('P3',3) ;
EXCEPTION WHEN OTHERS THEN
INSERT INTO T_TRAN_PROC VALUES ('E1',-1) ;
end ;
SELECT * FROM T_TRAN_PROC ;
結果: 錯誤前的語法和Exception內的執行語法仍存在於Transaction中. (若後續再執行commit則一併成功)
COLUMN_1 COLUMN_2
---------- ----------
P1 1
E1 -1
2 rows selected.
Case 2 : Add Commit/Rollback
begin
INSERT INTO T_TRAN_PROC VALUES ('P1',1) ;
INSERT INTO T_TRAN_PROC VALUES ('R2','A') ;
INSERT INTO T_TRAN_PROC VALUES ('P3',3) ;
COMMIT;
EXCEPTION WHEN OTHERS THEN
ROLLBACK;
INSERT INTO T_TRAN_PROC VALUES ('E1',-1) ;
end ;
結果: 錯誤前的語法被Rollback, Exception內的執行語法則存在新的Transaction中. (若後續再執行commit則只有一筆成功)
COLUMN_1 COLUMN_2
---------- ----------
E1 -1
1 row selected.
正確的做法
begin
INSERT INTO T_TRAN_PROC VALUES ('P1',1) ;
INSERT INTO T_TRAN_PROC VALUES ('R2','A') ;
INSERT INTO T_TRAN_PROC VALUES ('P3',3) ;
COMMIT; -- 處理正常交易之成功
EXCEPTION WHEN OTHERS THEN
ROLLBACK; -- 處理正常交易之失敗
INSERT INTO T_TRAN_PROC VALUES ('E1',-1) ;
COMMIT; -- 處理異常交易之成功
end ;