4.2.2　採番業務で使用する表

採番業務では次の2種類の方法で採番できます。

WITHOUT ROLLBACKオプション指定の表での採番
自動採番機能

ここでは，WITHOUT ROLLBACKオプション指定の表での採番について説明します。自動採番機能については，「自動採番機能」を参照してください。

〈この項の構成〉

(1)　どのようなときに使用するか
(2)　表の設計
(3)　業務への適用条件
(4)　採番を管理する表の例
(5)　採番業務のアプリケーションプログラムの例
(6)　複数種類の採番を管理するときの考慮点
(7)　ストアドプロシジャを使用した採番の例

(1)　どのようなときに使用するか

実際の業務では，伝票番号や書類の番号の管理など，様々な採番業務があります。採番業務では，あるユーザが伝票番号を取得しようとしたとき，それと同時に他ユーザも伝票番号を取得しようとするケースが考えられます。

また，あるユーザが伝票番号を取得しようとしたときには，ほかのユーザが以前に取得した伝票番号と重複しないように，番号をカウントアップしておく必要があります。

このようなケースでは，あるユーザが伝票番号の取得処理中に，他ユーザが待ち状態になる可能性があります。このようなことを考慮し，HiRDBでは，排他待ちの影響を少なくして，採番業務をするための機能を提供しています。

ページの先頭へ

(2)　表の設計

採番業務を効率良く実施するため，排他待ちの影響が少なくなるように表を設計する必要があります。採番を管理する表への排他の影響を少なくするために，トランザクションのコミットを待たないで表への更新（追加，削除を含む）処理が完了した時点で，その行への排他を解除し，それ以降はロールバックされなくなるという機能を提供しています。この機能を実現するためには，表の設計者が，表定義時にCREATE TABLEのWITHOUT ROLLBACKオプションを指定する必要があります。

ページの先頭へ

(3)　業務への適用条件

WITHOUT ROLLBACKオプションを指定して行う採番は，欠番を許容できない業務には適していません。番号が連続でなくてもよい場合に適用するようにしてください。欠番は次に示す場合に発生します。

表定義時にWITHOUT ROLLBACKオプションを指定した表の行を更新したトランザクションがROLLBACK文やSQL実行中のエラーによりロールバックした場合，取得した番号を使用した業務の表に対してはロールバックされます。この場合，整合性が保たれますが，取得した番号はロールバックされません。このため，ロールバックしたトランザクションで取得した値はHiRDBシステム内で使われずに次の番号が取得され，欠番となります。

また，表定義時にWITHOUT ROLLBACKオプションを指定した表の行を更新したトランザクションが完了する前にHiRDBシステムが異常終了した場合，HiRDBシステムの再開始後に採番した値が戻るときがあります。

採番した値をHiRDBシステム内に閉じて使用する場合，採番した値が戻っても，その値を使用したトランザクションもロールバックするため，HiRDBシステム内で一意性が保てます。HiRDBシステム外で使用する場合は採番した値が戻ると一意性が保てなくなります。HiRDBシステム外で使用する場合は，更新するSQL文にWRITE IMMEDIATEオペランドを指定してください。WRITE IMMEDIATEオペランドを指定すると，HiRDBシステムが異常終了しても値が戻りません。ただし，WRITE IMMEDIATEオペランドを指定すると1行の更新ごとにシステムログの書き出しが行われ，その書き出し時間がSQLの実行時間に加算されます。

ページの先頭へ

(4)　採番を管理する表の例

採番を管理する表の例を次の図に示します。

図4‒7　採番を管理する表の例

注: 表の定義例（WITHOUT ROLLBACKオプションの指定）については，マニュアル「HiRDB システム導入・設計ガイド」を参照してください。

ページの先頭へ

(5)　採番業務のアプリケーションプログラムの例

採番業務のアプリケーションプログラムの例を次に示します。なお，採番管理表と業務表を操作するアプリケーションプログラムは同一トランザクションを想定します。

（例）

伝票番号と書類番号を管理する採番管理表があるものとします。採番管理表から，最新の伝票番号を取得し，それを業務で使用するSQLの例を次に示します。

  INSERT INTO 採番管理表 VALUE('伝票番号',1)  ....1
 
                :
 
  DECLARE CUR1 CURSOR FOR  .......................2
    SELECT 採番 FROM 採番管理表
      WHERE 種類='伝票番号' FOR UPDATE OF 採番
  OPEN CUR1  .....................................3
  FETCH CUR1 INTO :x_採番  .......................4
  UPDATE 採番管理表 SET 採番=:x_採番+1  ..........5
      WHERE CURRENT OF CUR1
  CLOSE CUR1  ....................................6
 
                :
 
  取得した番号を利用した業務表へのアクセス処理  ..7
 
                :

［説明］

採番管理表に，伝票番号の初期値として1を挿入します。
採番管理表から最新の伝票番号を検索する，カーソルCUR1を宣言します。
カーソルCUR1をオープンします。
伝票番号をx_採番に取り出します。
次に検索するユーザのために，採番をカウントアップします（最新の採番にするため）。この処理が終了した時点で，コミットを待たないで行への排他を解除します。
カーソルCUR1をクローズします。
x_採番に取り出した伝票番号を基に，ユーザ任意の業務を実施します。

なお，採番ごとに3〜7を繰り返します。

ページの先頭へ

(6)　複数種類の採番を管理するときの考慮点

(a)　排他について

WITHOUT ROLLBACKオプションを指定した表に複数の行を格納する場合，その表にインデクスを定義していないときは，すべての行が検索対象となるため，すべての行に対して一時的に排他が掛かります。このような場合，例えば，伝票番号の採番処理と書類番号の採番処理との間で，排他待ちになることがあります。これを回避するためには，クライアント環境定義のPDLOCKSKIPにYESを指定して，無排他条件判定をする必要があります。無排他条件判定をした場合には，検索処理時には排他を掛けないで，探索条件を満たした行に対してだけ排他を掛けます。

(b)　ロールバックについて

複数種類の採番を扱う場合，1回のSQLで複数行を更新するような処理はしないでください。排他の解除，及びロールバックがされなくなるタイミングが各行単位に，それぞれの行の更新処理が完了した時点となります。そのため，複数行を更新するUAPが異常終了すると，一部の行の更新がロールバックされない場合があります。

ページの先頭へ

(7)　ストアドプロシジャを使用した採番の例

採番業務では，ある決まったパターンで処理することが多いため，その処理をストアドプロシジャとして登録しておくと便利です。

表の定義例，ストアドプロシジャの例を例1〜例3に示します。

（例1）

WITHOUT ROLLBACK指定の表と，ストアドプロシジャを使用して，順序番号を割り当てます。

初期値1，増分値1でINTEGERの最大値までの値を採番します。

INTEGERの最大値を超えた場合は，オーバフローのエラーが返されます。ただし，既定値設定機能（PDDFLNVAL）使用時はオーバフローのエラーにはならないで，ナル値となるため，非ナル値制約違反のエラーとなります。また，初期値を持つ行が挿入されていない場合，表中に行がない状態になるため，UPDATE文実行時にカーソルが行に位置づけられていないというエラーになります。複数行が挿入されている場合には，2行目以降は無視されます。

CREATE FIX TABLE
    owner_id.sequence_tbl(sequence_no INTEGER NOT NULL)
    WITHOUT ROLLBACK;  .......................................1
CREATE PROCEDURE owner_id.nextval(OUT next_no INTEGER)
  BEGIN
    DECLARE update_no INTEGER;  ..............................2
    DECLARE cr1 CURSOR FOR
      SELECT sequence_no FROM owner_id.sequence_tbl
        FOR UPDATE;
    OPEN cr1;
    FETCH cr1 INTO update_no;  ...............................3
    SET next_no=update_no;  ..................................4
    UPDATE owner_id.sequence_tbl SET sequence_no=update_no+1
      WHERE CURRENT OF cr1;  .................................5
    CLOSE cr1;  ..............................................3
  END  .......................................................2
COMMIT WORK;  ................................................6
INSERT INTO owner_id.sequence_tbl(sequence_no) VALUES(1);  ...7
COMMIT WORK;  ................................................8
 
<順序番号の割り当て>  ........................................9
CALL owner_id.nextval(OUT:xnext_no);
                :
割り当てた順序番号xnext_noを使用した処理
                :
CALL owner_id.nextval(OUT:xnext_no);
                :

［説明］

INTEGERの値を採番するための表owner_id.sequence_tblを定義します。
順序番号を割り当てて，パラメタnext_noで出力する手続きowner_id.nextvalを定義します。
表owner_id.sequence_tblの列sequence_noを検索します。
検索した値をパラメタnext_noに設定します。
表owner_id.sequence_tblの列sequence_noに増分値1を加えた値に更新します。
表と手続きの定義を有効にするため，トランザクションをコミットします。
INSERT文で，初期値1を持つ行をあらかじめ挿入しておきます。
挿入した行を有効にするため，トランザクションをコミットします。
手続きowner_id.nextvalをCALL文で呼び出し，順序番号を割り当て，パラメタnext_noで値を取得します。CALL文を実行するごとに次の順序番号が割り当てられます。

（例2）

WITHOUT ROLLBACK指定の表と，ストアドプロシジャを使用して，2種類以上の順序番号を割り当てます。

順序番号を識別するキーごとに，初期値1，増分値1でINTEGERの最大値までの値を採番します。

INTEGERの最大値を超えた場合は，オーバフローのエラーが返されます。ただし，既定値設定機能（PDDFLNVAL）使用時はオーバフローのエラーにはならないで，ナル値となるため，非ナル値制約違反のエラーとなります。また，順序番号を識別するために指定したキー値に対して，初期値を持つ行が挿入されていない場合，表中に行がない状態になるため，UPDATE文実行時にカーソルが行に位置づけられていないというエラーになります。順序番号を識別するために指定したキー値に対して，複数行が挿入された場合には2行目以降は無視されます。

注1: WITHOUT ROLLBACK指定の表には，インデクスを定義できません。排他の競合を防ぐためにクライアント環境定義のPDLOCKSKIP=YESを指定する必要があります。
注2: WITHOUT ROLLBACK指定の表には，インデクスを定義できないので，順序番号の種類が非常に多い場合は表及び手続きを分けてください。

CREATE FIX TABLE
    owner_id.sequence_tbl(sequence_key CHAR(30) NOT NULL,
                          sequence_no INTEGER  NOT NULL)
    WITHOUT ROLLBACK;  ..........................................1
CREATE PROCEDURE owner_id.nextval(IN input_key CHAR(30),
                                  OUT next_no INTEGER)
  BEGIN
    DECLARE update_no INTEGER;  .................................2
    DECLARE cr1 CURSOR FOR
      SELECT sequence_no FROM owner_id.sequence_tbl
        WHERE sequence_key=input_key FOR UPDATE OF sequence_no;
    OPEN cr1;
    FETCH cr1 INTO update_no;  ..................................3
    SET next_no=update_no;  .....................................4
    UPDATE owner_id.sequence_tbl SET sequence_no=update_no+1
      WHERE CURRENT OF cr1;  ....................................5
    CLOSE cr1;  .................................................3
  END  ..........................................................2
COMMIT WORK;  ...................................................6
INSERT INTO owner_id.sequence_tbl(sequence_key,sequence_no)
    VALUES('key_value_1',1);  ...................................7
COMMIT WORK;  ...................................................8
INSERT INTO owner_id.sequence_tbl(sequence_key,sequence_no)
    VALUES('key_value_2',1);  ...................................7
COMMIT WORK;  ...................................................8
       :
（順序番号の種類数分，初期値の行を挿入します）
 
<'key_value_1'の順序番号の割り当て>  ............................9
xinput_key <-- 'key_value_1'
CALL owner_id.nextval(IN:xinput_key,OUT:xnext_no);
                  :
'key_value_1'に対して割り当てた順序番号xnext_noを使用した処理
                  :
xinput_key <-- 'key_value_1'
CALL owner_id.nextval(IN:xinput_key,OUT:xnext_no);
                  :
 
<'key_value_2'の順序番号の割り当て>  ............................9
xinput_key <-- 'key_value_2'
CALL owner_id.nextval(IN :xinput_key,OUT:xnext_no);
                  :
'key_value_2'に対して割り当てた順序番号xnext_noを使用した処理
                  :
xinput_key <-- 'key_value_2'
CALL owner_id.nextval(IN:xinput_key,OUT:xnext_no);
                  :

［説明］

順序番号を識別するキーごとに，INTEGERの値を採番するための表owner_id.sequence_tblを定義します。
順序番号を識別するキーをパラメタinput_keyで入力し，そのキーに対して順序番号を割り当てて，パラメタnext_noで出力する手続きowner_id.nextvalを定義します。
表owner_id.sequence_tblの列sequence_keyに対して順序番号を識別するキーを指定して，列sequence_noを検索します。
検索した値をパラメタnext_noに設定します。
表owner_id.sequence_tblの列sequence_noを増分値1を加えた値に更新します。
表と手続きの定義を有効にするため，トランザクションをコミットします。
順序番号を識別するキーごとに，INSERT文で初期値1を持つ行をあらかじめ挿入しておきます。
挿入した行を有効にするため，トランザクションをコミットします。
手続きowner_id.nextvalをCALL文で呼び出し，順序番号を割り当て，パラメタnext_noで値を取得します。CALL文を実行するごとに次の順序番号が割り当てられます。

（例3）

WITHOUT ROLLBACK指定の表と，ストアドプロシジャを使用して，最小値と最大値の間の値を循環させて順序番号を割り当てます。

初期値を持つ行が挿入されていない場合，表中に行がない状態になるため，UPDATE文実行時にカーソルが行に位置づけられていないというエラーになります。また，複数行が挿入されている場合には，2行目以降は無視されます。

CREATE FIX TABLE
     owner_id.sequence_tbl(sequence_no INTEGER NOT NULL)
     WITHOUT ROLLBACK;  .........................................1
CREATE PROCEDURE owner_id.nextval(OUT next_no INTEGER)
  BEGIN
    DECLARE update_no INTEGER;  .................................2
    DECLARE cr1 CURSOR FOR
      SELECT sequence_no FROM owner_id.sequence_tbl FOR UPDATE;
    OPEN cr1;
    FETCH cr1 INTO update_no;  ..................................3
    SET next_no=update_no;  .....................................4
    IF update_no=2147483647 THEN
      SET update_no=-2147483648;
    ELSE
      SET update_no=update_no+1;
    END IF;  ....................................................5
    UPDATE owner_id.sequence_tbl SET sequence_no=update_no
      WHERE CURRENT OF cr1;  ....................................6
    CLOSE cr1;  .................................................3
  END  ..........................................................2
COMMIT WORK;  ...................................................7
INSERT INTO owner_id.sequence_tbl(sequence_no)VALUES(1);  .......8
COMMIT WORK;  ...................................................9
 
<順序番号の割り当て>  ...........................................10
CALL owner_id.nextval(OUT:xnext_no);
                :
割り当てた順序番号xnext_noを使用した処理
                :
CALL owner_id.nextval(OUT:xnext_no);
                :

［説明］

INTEGERの値を採番するための表owner_id.sequence_tblを定義します。
表owner_id.sequence_tblの列sequence_noを，増分値1，最小値−2,147,483,648，最大値2,147,483,647で，最大値の次の値が最小値となるように値を循環させて順序番号を割り当てる手続きowner_id.nextvalを定義します。
表owner_id.sequence_tblの列sequence_keyを検索します。
検索した値をパラメタnext_noに設定します。
検索した値が最大値2,147,483,647ならば，最小値−2,147,483,648を順序番号の次の値とし，そうでなければ増分値1を加えた値を順序番号の次の値とします。
表owner_id.sequence_tblの列sequence_noを順序番号の次の値に更新します。
表と手続きの定義を有効にするため，トランザクションをコミットします。
INSERT文で，初期値1を持つ行をあらかじめ挿入しておきます。
挿入した行を有効にするため，トランザクションをコミットします。
手続きowner_id.nextvalをCALL文で呼び出し，順序番号を割り当て，パラメタnext_noで値を取得します。CALL文を実行するごとに次の順序番号が割り当てられます。

ページの先頭へ

4.2.2 採番業務で使用する表

(1) どのようなときに使用するか

(2) 表の設計

(3) 業務への適用条件

(4) 採番を管理する表の例

(5) 採番業務のアプリケーションプログラムの例

(6) 複数種類の採番を管理するときの考慮点

(a) 排他について

(b) ロールバックについて

(7) ストアドプロシジャを使用した採番の例

4.2.2　採番業務で使用する表

(1)　どのようなときに使用するか

(2)　表の設計

(3)　業務への適用条件

(4)　採番を管理する表の例

(5)　採番業務のアプリケーションプログラムの例

(6)　複数種類の採番を管理するときの考慮点

(a)　排他について

(b)　ロールバックについて

(7)　ストアドプロシジャを使用した採番の例