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

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

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

2012.05.14

今回は、順序表を使って番号を発番する処理を行うプロシージャを作ります。
このプロシージャは、発番単位を渡してコールすれば、その発番単位の次の番号を返すプロシージャとします。
ですから、モードが IN のパラメータ(発番単位)とOUTのパラメータ(生成した順序番号)を持ちます。
プロシージャ名を「PROC_順序番号」として、なるべくシンプルに以下のように作成しました。

  1  CREATE OR REPLACE PROCEDURE PROC_順序番号
  2       (P_発番単位 IN  VARCHAR2, P_番号 OUT NUMBER)   -- P_番号がOUTのパラメータ
  3    IS
  4       V_発番済番号 順序.番号%TYPE;
  5    BEGIN
  6       -- 指定された発番単位を1足しこみOUTパラメータに取得
  7       UPDATE 順序
  8       SET 番号 = 番号 + 1
  9       WHERE 発番単位 = P_発番単位
 10       RETURNING 番号 INTO P_番号;
 11       --更新行数が0行なら新しい発番単位なので、初めの1番を発番し、順序表に新し発番単位を登録する
 12       IF SQL%ROWCOUNT = 0 THEN
 13          P_番号 := 1;
 14          INSERT INTO 順序(発番単位, 番号) VALUES (P_発番単位, 1);
 15       END IF;
 16* END;
SQL> /

プロシージャが作成されました。

ここでは、7-10行目で、UPDATEにより指定された発番単位の番号を「+1」加算しています。
さらに、10行目のRETURNING句で、加算後の番号列をOUTの変数 P_番号に代入しています。
ちなみにこのRETURNING句、知らない方が多いのではないかと思うのですが、どうでしょうか?

一般的には、UPDATE後の値がどういう値かわからない場合、もう一度SELECT INTOして値を取得しますが、RETURNING句を使うとUPDATEと同時に更新後の値を取得できるので大変便利です。
また実行するSQLの数もSELECTが不要となる分、パフォーマンスも向上します。

12-15行目でUPDATE行数が0行だった場合は、新しい発番単位であることを意味します。
ですので初めの1番を発番し、順序表に新しい発番単位を登録しているわけです。

番号を発番するロジックは他にも可能ですが、自分でロジックを組み立てるときに気をつけていただきたいことは、複数のトランザクションで同時実行しても同じ番号を発番することはありえないように記述にする必要があります。
今回のロジックは発番処理の最初の実行文がUPDATE文なので、排他的行ロックがかかるために、同時に同じ発番単位の発番処理が行われることはありえず、同じ番号が発番される可能性はありません。

別なロジックとしては、最初にSELECT INTO文で、該当発番単位の番号を変数に取得し、それに「+1」加算したものを新しい番号として順序表をその値に更新するロジックもありえます。
ただしその場合は、SELECT INTO文に必ずFOR UPDATE句をつけてください。
それにより、SELECT INTOの段階で該当行に対してロックを取得し、他のトランザクションをブロックできます。
もし、SELECT INTO を使っていてFOR UPDATEをつけなかった場合は、タイミングによっては、複数のトランザクションで、同じ番号を発番する可能性がありますので注意してください。

今回の例のように、最初にUPDATEで「+1」加算する処理を行っている場合は、その段階でロックがかかるので複数トランザクションが同じ番号を取得する心配はありません。

では、さっそく検証してみます。

OUTのパラメータをもつプロシージャをコールするときは、必ずその値を受け止める変数が必要です。
ここでは、V_番号という変数に、発番された値が格納されます。

SQL> DECLARE
  2     V_番号  順序. 番号%TYPE;
  3  BEGIN
  4     PROC_順序番号('注文ID',V_番号);
  5     DBMS_OUTPUT.PUT_LINE('注文ID=' || V_番号);
  6     PROC_順序番号('顧客ID',V_番号);
  7     DBMS_OUTPUT.PUT_LINE('顧客ID=' || V_番号);
  8  END;
  9  /
注文ID=3
顧客ID=101

PL/SQLプロシージャが正常に完了しました。

順序表の注文IDは、もともと2番だったので、3番が戻りました。
同様に、順序表の顧客IDは、もともと100番だったので、101番が戻りました。

このプロシージャ内でCOMMITはしていないので、ROLLBACKすればトランザクション全体が取り消され、結果として番号が欠落することはありません。
それも検証してみましょう。

SQL> ROLLBACK;

ロールバックが完了しました。

SQL> DECLARE
  2     V_番号  順序. 番号%TYPE;
  3  BEGIN
  4     PROC_順序番号('注文ID',V_番号);
  5     DBMS_OUTPUT.PUT_LINE('注文ID=' || V_番号);
  6     PROC_順序番号('顧客ID',V_番号);
  7   DBMS_OUTPUT.PUT_LINE('顧客ID=' || V_番号);
  8  END;
  9  /
注文ID=3
顧客ID=101

PL/SQLプロシージャが正常に完了しました。

ROLLBACKしたので順序表の更新も取り消され、結果として発番される値が以前と同じです。
つまり、番号が飛ぶことなく連続番号が保証されます。

検証なので、元に戻しておきます。

SQL> ROLLBACK;

ロールバックが完了しました。

このように、このプロシージャは必要な機能を満たしていますね。
では、前回(第19回)の「PROC_注文受付プロシージャ」内の処理で、注文表にINSERTする際の注文IDの値を今回の「PROC_順序番号」プロシージャから取得するように変更します。

  1  CREATE OR REPLACE PROCEDURE PROC_注文受付
  2    (P_製品名 IN 注文.製品名%TYPE, P_注文数 IN 注文.注文数%TYPE)
  3  IS
  4     V_在庫数 在庫.在庫数%TYPE;
  5     V_注文ID 注文.注文ID%TYPE; -- <<追加>>
  6  BEGIN
  7     --指定された製品の在庫数を取得
  8     SELECT 在庫数 INTO V_在庫数 FROM 在庫 WHERE 製品名 = P_製品名 FOR UPDATE;
  9     --注文数がその在庫数を越えるときはエラーとする
10     IF P_注文数 > V_在庫数 THEN
11         RAISE_APPLICATION_ERROR(-20000,'在庫不足エラー :' || P_製品名);
12     END IF;
13     --在庫不足でないときは、在庫数を注文数の文だけ減らし、
14     UPDATE 在庫
15       SET 在庫数 = 在庫数 - P_注文数
16       WHERE 製品名 = P_製品名;
17     --さらに注文表への注文登録を行う
18         --注文IDの値を取得
19     PROC_順序番号('注文ID',V_注文ID); -- <<追加>>
20     INSERT INTO 注文(注文ID, 製品名,注文数)
21       VALUES (V_注文ID, P_製品名, P_注文数); -- <<修正>>
22     COMMIT;
23  EXCEPTION
24        WHEN NO_DATA_FOUND THEN
25          RAISE_APPLICATION_ERROR(-20001,'誤った製品名です');
26* END;
SQL> /

プロシージャが作成されました。

まず、変更点としては、
●5行目で、注文ID用の変数 V_注文IDを宣言します。
●20-21行目の注文表へのUPDATEの前に
●19行目で、注文IDの次の番号をV_注文ID変数に取得して
●21行目で、注文IDの値として、INSERT文の中で使用します。

このようにすることで、PROC_注文受付プロシージャを通して注文処理を行う場合、注文表の主キー(注文ID列)の値は必ず連続番号であることが保障できるわけです。

では、さっそく注文登録して見ましょう。現在、注文表、在庫表は以下の通りです。

SQL> SELECT * FROM 注文;

    注文ID 製品
名                   注文数
----------------------------------------
         1 テレビ                        3
         2 ラジオ                        4

※この2行は前回の時の注文内容です。

SQL> SELECT * FROM 在庫
2 ;

製品名                   在庫数
------------------------------
テレビ                        7
ラジオ                        1

では、テレビの在庫は7台なので、テレビ2台、注文処理してみます。

SQL> BEGIN
2 PROC_注文受付('テレビ',2);
3 END;
4 /

SQL> SELECT * FROM 注文 ORDER BY 注文ID;

    注文ID 製品
名                   注文数
----------------------------------------
         1 テレビ                        3
         2 ラジオ                        4
         3 テレビ                        2 ←今回の注文

SQL> SELECT * FROM 在庫;

製品名                   在庫数
------------------------------
テレビ                        5 ←在庫数が2減っている
ラジオ                        1

いかがでしょうか!
「PROC_注文受付プロシージャ」から注文登録、在庫の更新がきちんとできていますね。
そしてなにより、注文IDの値が必ず連続した値で発番されるわけです♪

ところで、表の主キーには、単純な一列の主キーではなく、複数列の組み合わせで主キーという場合もあります。
そのような場合も今回の発番方法は有効です。

例えば、注文表の主キーを「注文ID列」ではなく、「顧客ID+注文ID」だとします。いわゆる複合主キーです。
このような場合、ユーザの要件はたいていの場合、「顧客ごとに1番から連続して注文IDを発番する」というものになります。
そこで今回のプロシージャで顧客毎の注文IDを発番単位として扱えば、顧客ごとに1番から連続して発番できるわけです。

例えば以下の例をご覧ください。

SQL> SET SERVEROUTPUT ON
SQL> DECLARE
  2    V_顧客番号    NUMBER := &顧客番号;
  3    V_発番単位  VARCHAR2(30);
  4    V_番号      NUMBER;
  5  BEGIN
  6    V_発番単位 := '顧客番号' || V_顧客番号 || 'の注文ID';
  7    PROC_順序番号(V_発番単位,V_番号);
  8    DBMS_OUTPUT.PUT_LINE(V_発番単位 || ':' || V_番号);
  9 END;
10 /
顧客番号に値を入力してください: 100
旧   2:   V_顧客番号    NUMBER := &顧客番号;
新   2:   V_顧客番号    NUMBER := 100;
顧客番号100の注文
ID:1                             ←顧客番号100の最初の注文ID 1番

PL/SQLプロシージャが正常に完了しました。

SQL> R
  1  DECLARE
  2    V_顧客番号    NUMBER := &顧客番号;
  3    V_発番単位  VARCHAR2(30);
  4    V_番号      NUMBER;
  5  BEGIN
  6    V_発番単位 := '顧客番号' || V_顧客番号 || 'の注文ID';
  7    PROC_順序番号(V_発番単位,V_番号);
  8    DBMS_OUTPUT.PUT_LINE(V_発番単位 || ':' || V_番号);
  9* END;
顧客番号に値を入力してください: 100
旧   2:   V_顧客番号    NUMBER := &顧客番号;
新   2:   V_顧客番号    NUMBER := 100;
顧客番号100の注文ID:2                             ←顧客番号100の次の注文ID 2番

PL/SQLプロシージャが正常に完了しました。

SQL> R
  1  DECLARE
  2    V_顧客番号    NUMBER := &顧客番号;
  3    V_発番単位  VARCHAR2(30);
  4    V_番号      NUMBER;
  5  BEGIN
  6    V_発番単位 := '顧客番号' || V_顧客番号 || 'の注文ID';
  7    PROC_順序番号(V_発番単位,V_番号);
  8    DBMS_OUTPUT.PUT_LINE(V_発番単位 || ':' || V_番号);
  9* END;
顧客番号に値を入力してください: 100
旧   2:   V_顧客番号    NUMBER := &顧客番号;
新   2:   V_顧客番号    NUMBER := 100;
顧客番号100の注文ID:3                             ←顧客番号100の次の注文ID 3番

PL/SQLプロシージャが正常に完了しました。

SQL> R
  1  DECLARE
  2    V_顧客番号    NUMBER := &顧客番号;
  3    V_発番単位  VARCHAR2(30);
  4    V_番号      NUMBER;
  5  BEGIN
  6    V_発番単位 := '顧客番号' || V_顧客番号 || 'の注文ID';
  7    PROC_順序番号(V_発番単位,V_番号);
  8    DBMS_OUTPUT.PUT_LINE(V_発番単位 || ':' || V_番号);
  9* END;
顧客番号に値を入力してください: 200
旧   2:   V_顧客番号    NUMBER := &顧客番号;
新   2:   V_顧客番号    NUMBER := 200;
顧客番号200の注文ID:1                             ←顧客番号200の最初の注文ID 1番

PL/SQLプロシージャが正常に完了しました。
PL/SQLプロシージャが正常に完了しました。

SQL> SELECT * FROM 順序;

発番単位                   番号
------------------------------
注文ID                        3
顧客ID                      100
顧客番号100の注文ID           3    ←上記の例で追加された発番単位
顧客番号200の注文ID           1    ←上記の例で追加された発番単位

SQL>

いかがでしょうか!
顧客番号ごとに注文IDを1番から連続して発番している様子がわかると思います。
また新し発番単位を使うたびに順序表にその発番単位が登録されていることもわかりますね。
発番単位の文字列を上記の例のように「顧客番号100の注文ID」という形になるようにプログラムで統一すれば、必ず顧客番号ごとに1番から連続番号で発番できるわけです。
上記の処理はそういった処理部分を無名ブロックで切り取って検証しているにすぎませんが、実際には注文受付プロシージャ内で、発番単位の文字列を生成し、順序生成プロシージャをコールするので、発番単位の文字列生成は必ず統一されます。よって、顧客ごとの連続した注文IDの生成といった処理が可能になります。

今回のポイントは連続する番号の生成方法です。一般にはその処理をプロシージャ化して汎用的に使い回しします。

それでは、今回はここまでです。次回もまたご期待ください。

先頭へ戻る