【PL/SQL】自律型トランザクションのすゝめ
いきなりですが、「自律型トランザクション」とは！？ 処理中にテーブルを使用した採番や、テーブルにエラー内容を出力と言った事をやりたいな～と思う事があります。 あるはずです！ 特にメイン処理でエラーが発生して「ROLLBACK」すると、テーブルに出力したエラー内容まで消えてしまいます。 採番の例で説明していきます。
採番の例
採番 FUNCTION を作成
CREATE OR REPLACE FUNCTION SAIBAN IS
PRAGMA AUTONOMOUS_TRANSACTION; -- ←自律型トランザクションの指定(プラグマ)
L_AAA SEQTBL.AAA%TYPE;
BEGIN
UPDATE SEQTBL SET
AAA = AAA + 1
WHERE (BBB = 'B')
RETURNING AAA INTO L_AAA;
COMMIT; -- ←自律型トランザクションではトランザクションを終了させないとエラーとなります
RETURN(L_AAA);
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
RAISE;
END SAIBAN;メイン処理の作成
DECLARE
L_SEQNO SEQTBL.AAA%TYPE;
L_TEST NUMBER(10);
BEGIN
INSERT INTO TSTBL1(BBB) VALUES(1); --(1)
L_SEQNO := SAIBAN; --(2) 採番テーブルから番号取得
UPDATE TSTBL2 SET --(3)
DDD = L_SEQNO
WHERE (CCC = 'AAA');
L_TEST := 1 / 0; --(4) 0除算でエラー発生
COMMIT;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK; --(5)
RAISE; --(6)
END;少し長く見えますが順に説明して行きます。
メイン処理の流れは以下となります。
- (1)「TSTBL1」テーブルに値をINSERT
- (2)「SAIBAN」FUNCTIONを呼び出し、採番値取得
- (3)「TSTBL2」テーブルの値をUPDATE
- (4)「0除算」でシステムエラー発生
- (5) ROLLBACK
- (6) RAISE で呼び元にエラーを返して処理終了
(5)で「ROLLBACK」されますが、
通常の場合、「SAIBAN」FUNCTIONの中で「COMMIT」を発行している為、
(1)の「INSERT」もCOMMITされてしまいます。
でも、上記のロジックの場合、そうはなりません。
「SAIBAN」の中で、「PRAGMA AUTONOMOUS_TRANSACTION」が指定されているので、このFUNCTIONのトランザクションは自律（独立）しています。メイン処理のトランザクションとは別となります。
結果として、
「TSTBL1」、「TSTBL2」はROLLBACKされ、「SEQTBL」は更新されます。