e-learning、オラクル研修、LMS(学習管理システム)のiStudy

e-learning、オラクル研修、LMS(学習管理システム)のiStudy

第19回 「ストアドプロシージャ事例2(連続性を保証した発番処理)」前編

2012.05.07

今回はプロシージャの事例というよりは、連続する番号の発番方法の定石を主に紹介し、それをストアドプロシージャ化する方法を紹介します。実際、よくあるプロシージャの事例です。

一応、前回の話から続いていますが、前回を読んでいない方もこの回だけ読むのでも大丈夫です。

前回は、ストアドプロシージャの例として、注文登録処理(PROC_注文受付プロシージャ)を解説しました。
内容としては、該当する製品の在庫数をチェックし、在庫不足でなければ注文を登録し、該当する製品の在庫数をその注文の分だけ減らすというものでした。
その一連の処理過程で、在庫表へのSELECT、条件判定のIF文、注文表へのINSERT、在庫表へのUPDATEなどがあり、一般的なトランザクションらしい処理となっています。

ところで、このプロシージャのなかで、注文表に登録する時の主キー(注文ID列)の番号を、順序オブジェクト(SEQ_注文ID)から取得していました。
主キーの番号値の取得は、オラクルの場合、順序オブジェクトを使用することが一番簡単かつ、パフォーマンスが良い方法なのですが、欠点として、連続番号が保証できません。
連続番号が保証できないことは順序オブジェクトの宿命みたいなものです。
なぜかというと、トランザクションがROLLBACKされても、順序オブジェクトの発番値はもとに戻らないからです。
たとえばあるトランザクションで、順序オブジェクトから「10」という値を取得してその値でINSERTしたのですが、結局そのトランザクションをROLLBACKしたとします。
そしてもう一度処理をやり直すと、「10」ではなく次の「11」で取得されますので、その状態でトランザクションをCOMMITすると、表に「10」の値が登録されません。つまり欠落します。

このように順序オブジェクトはROLLBACKの影響を受けないので、番号の欠落がありえるのです。
あるいは順序オブジェクトは、デフォルトで20個分の番号をメモリにキャッシュしますので、なんらかの原因(長時間の順序値の未取得や、インスタンス障害など)で、メモリの情報が失われる場合もあります。
そのような場合、番号が飛びますので、連続しないことになります。
このように、順序オブジェクトを使う以上、番号が連続しない可能性はどうしても認めた上で使用せざるを得ません。

しかし、エンドユーザのニーズにより、連続番号を保証しなければいけないケースがあります。
そういった場合、順序オブジェクトは使用できません。そのかわり、一般的な定石があります。
それは、プログラム的に番号を発番する方法です。具体的には、順序表を使って、番号を発行するという方法です。
順序表には、決まった定義はありません。アプリケーションの設計者が必要に応じて定義を決めます。
一般的には、発番単位ごとに発番済み番号を管理する表にします。
そして発番処理では、該当番号に1を足しこんだ値を新しい番号として使います。
このようにすれば、仮にトランザクションが取り消されても、順序表に対する更新もトランザクションの範囲内なので同様に取り消しされ、番号が欠落することはありません。
今回は順序表を使って番号を生成する処理をプロシージャ化してみましょう。

その前にまず順序表を作らなければなりません。
なるべく簡単に以下のように作りました。

SQL> CREATE TABLE 順序
  2  ( 発番単位   VARCHAR2(20)  PRIMARY KEY,
  3    番号       NUMBER)
  4 /

表が作成されました。

この表には、「この発番単位はこの番号まで発番済み」といった意味のデータを登録しておきます。
前回の処理で、注文表の注文IDは2番までだったので「注文ID列は2番まで発番済み」という意味のデータを順序表に登録しておきます。

SQL> INSERT INTO 順序 VALUES ('注文ID',2);

1行が作成されました。

SQL> COMMIT;

コミットが完了しました。

ここで、仮に顧客表という表があり、その表の顧客ID列は「顧客IDは、100番まで発番済みという」という意味のデータを登録しておきます。

SQL> INSERT INTO 順序 VALUES ('顧客ID',100);

1行が作成されました。

SQL> COMMIT;

この時点で順序表のデータは以下のようになっています。

SQL> SELECT * FROM 順序;

発番単位                   番号
------------------------------
注文
ID                                2
顧客ID                      100

長くなりましたので、今回はここまでにします。
次回、順序表を使って番号を発番する処理を行うプロシージャを作って、連続する番号の発番方法を解説します。
ご期待ください。

先頭へ戻る