【Oracle】トリガーを使ってチェックディジットを検証する

はじめに

Oracleのトリガーを使って、コード値のチェックディジットを検証してNGなら例外を発生させてInsertを取り消すサンプルプログラムを作ってみました。

プロとしてのOracle PL/SQL入門【第3版】(Oracle 12c、11g、10g対応) (Oracle現場主義)

posted with amazlet at 15.05.17

アシスト教育部
SBクリエイティブ
売り上げランキング: 110,185

Amazon.co.jpで詳細を見る

テスト用のテーブル

SQL> desc table01
 名前                                      NULL?    型
 ----------------------------------------- -------- ------------

 ID                                        NOT NULL CHAR(8)
 NAME                                               VARCHAR2(50)
 FURIGANA                                           VARCHAR2(50)
 SEIBETSU                                           VARCHAR2(3)
 BIRTHDAY                                           CHAR(8)

チェックディジットを計算するユーザ関数

以下のユーザ関数は、モジュラス11 ウエイト2〜7でチェックディジットを求めます。

CREATE OR REPLACE FUNCTION MOD11WEIGHT27(P_CODE IN VARCHAR2)
RETURN NUMBER
IS
  vWeight PLS_INTEGER;
  vSum    PLS_INTEGER;
  
BEGIN
  vWeight := 2;
  vSum := 0;
 
  FOR i IN REVERSE 1..LENGTH(P_CODE)
  LOOP
    vSum := vSum + vWeight * CAST(SUBSTR(P_CODE, i, 1) AS PLS_INTEGER);
    vWeight := vWeight + 1;
    -- ウエイトが7を超えたら元に戻す
    IF (vWeight = 8) THEN
      vWeight := 2;
    END IF;
  END LOOP;
  -- あまりがゼロなら0
  IF (vSum MOD 11 = 0) THEN
    RETURN 0;
  END IF;
  -- あまりが1なら0
  IF (vSum MOD 11 = 1) THEN
    RETURN 0;
  END IF;
  -- 11 から 総和÷11のあまりを引く
  RETURN 11 - (vSum MOD 11);
END;
/

トリガー

以下のトリガーは、Insert直前にチェックディジットの検証を行い、チェックディジットが一致しない場合は例外を発生させます。

CREATE OR REPLACE TRIGGER TRIGGER01 BEFORE
  INSERT ON TABLE01 FOR EACH ROW BEGIN 
    IF (INSERTING) THEN 
      IF NOT (SUBSTR(:NEW.ID, 8, 1) = MOD11WEIGHT27(SUBSTR(:NEW.ID,1,7))) THEN
        RAISE_APPLICATION_ERROR(-20000, 'チェックディジットが不正です。');
      END IF;
    END IF;
END;
/

例外が発生すると、以下のように「ORA-20000: チェックディジットが不正です。」のエラーとなります。

SQL> INSERT INTO table01 VALUES
  2    ('12345675', '山田　太郎','やまだ　たろう','男','20150101'
  3    );
INSERT INTO table01 VALUES
            *
行1でエラーが発生しました。:
ORA-20000: チェックディジットが不正です。
ORA-06512: "HOGE.TRIGGER01", 行4
ORA-04088: トリガー'HOGE.TRIGGER01'の実行中にエラーが発生しました