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

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

第18回 「ストアドプロシージャ事例1(注文登録)」

2012.04.23

今回はストアドプロシージャの具体例を解説します。
ストアドプロシージャは一連の処理を記述した名前つきのPL/SQLブロックです。
用途はまさに千差万別ですが、今回は具体例でわかりやすい例を用意しました。
以下をご覧ください。

例えば、ある会社の業務では、製品の在庫を管理しています。
したがって「在庫」という表があるとします。
ここでは、あくまでも例なので、極力単純化し以下のような表を作りました。

SQL> CREATE TABLE 在庫(
  2   製品名   VARCHAR2(20) PRIMARY KEY,
  3   在庫数   NUMBER);

表が作成されました。

ついでに、データも登録しておきます。テレビの在庫数は10、ラジオの在庫数は5とします。

SQL> INSERT INTO 在庫 VALUES ('テレビ',10);

1行が作成されました。

SQL> INSERT INTO 在庫 VALUES ('ラジオ',5);

1行が作成されました。

SQL> COMMIT;

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

SQL> SELECT   FROM 在庫;

製品名                   在庫数
------------------------------
テレビ                       10
ラジオ                        5

この会社は在庫してある製品を注文を受けて販売します。注文のデータは注文表で管理します。
注文表は極力簡単に以下のように作りました。

SQL> CREATE TABLE 注文
  2  (  注文ID   NUMBER PRIMARY KEY,
  3     製品名   VARCHAR2(20),
  4     注文数   NUMBER);

表が作成されました。

この会社は注文を受けると注文表にデータを登録し、同時に在庫表の該当製品の在庫を注文の分だけ減らします。
ただし、在庫数よりも注文数の方が多い場合は、在庫不足のエラーとします。
これら一連の注文受付処理をストアドプロシージャに一任したいと思います。
プロシージャの名前は、「PROC_注文受付」にします。
このプロシージャに渡してあげなければいけない情報は、表の定義から「製品名」と「注文数」です。
「注文ID」は主キーであるため、順序オブジェクトから値を取得したいと思います。
よって順序オブジェクトを作ります。名前は「SEQ_注文ID」という名前で作りました。

SQL> CREATE SEQUENCE SEQ_注文ID;

順序が作成されました。

ではさっそく、プロシージャ「PROC_注文受付」を作ります。
なるべく簡単に以下のようにコーディングしました。

  1  CREATE OR REPLACE PROCEDURE PROC_注文受付
  2        (P_製品名 IN 注文.製品名%TYPE, P_注文数 IN 注文.注文数%TYPE)
  3  IS
  4        V_在庫数  在庫.在庫数%TYPE;
  5  BEGIN
  6        SELECT 在庫数 INTO V_在庫数 FROM 在庫 WHERE 製品名 = P_製品名 FOR UPDATE;
  7        IF P_注文数 > V_在庫数 THEN
  8            RAISE_APPLICATION_ERROR(-20000,'在庫不足エラー :' || P_製品名);
  9        END IF;
10        UPDATE 在庫
11        SET 在庫数 = 在庫数 - P_注文数
12        WHERE 製品名 = P_製品名;
13        INSERT INTO 注文(注文ID, 製品名,注文数)
14          VALUES (SEQ_注文ID.NEXTVAL, P_製品名, P_注文数);
15        COMMIT;
16  EXCEPTION
17        WHEN NO_DATA_FOUND THEN
18             RAISE_APPLICATION_ERROR(-20001,'誤った製品名です');
19  END;
SQL> /

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

上記の例で、6行目で該当製品の在庫数を変数に取得しています。
このSELECT文にFOR UPDATE句をつけているのは、複数のトランザクションによる同じ製品の同時の注文処理を防止するためです。
SELECTにFOR UPDATEをつけることにより、問い合わされた行に対してロックがかかります。
したがって、同時処理を防止できるのでトランザクションの整合性を保障できます。

7~9行目で、取得した在庫数と注文数を比較し、注文数の方が多ければ在庫不足エラーにします。
したがって、その下の10行目以降は、在庫不足でないときの処理です。

10~12行目で、在庫表の該当製品の在庫数を注文数の分だけ減らしています。

13~14行目で、注文表に注文内容を登録しています。
製品名、注文数はこのプロシージャのパラメータから、そして注文番号は、順序「SEQ_注文ID」から値を取得しています。

15行目の「COMMIT」は、このプロシージャ内の更新の確定ではなく、あくまでもトランザクションの確定です。
したがってこのプロシージャの呼び出し元の段階でトランザクション(データ更新など)が発生している場合は、それも含めて確定です。
ですから、COMMITをこのプロシージャの中で行わずに、呼び出し元で行う考え方もあります。

実行部(6~15行目)の中で、システムエラーを起こす可能性があるのは、6行目のSELECT INTO文が0件の場合です。
つまり、パラメータで指定した製品名が誤った製品名の場合です。
それに備えて、17~18行目でNO_DATA_FOUND例外ハンドラを用意して、SELECT INTO文で0件の時に「誤った製品名です」というエラーにしています。

ではさっそく、コールしてみます。

テレビを3台注文しましょう。テレビの在庫は10台なので、在庫不足にはなりません。

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

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

これにより、テレビの注文が登録されています。

SQL> SELECT   FROM 注文;

    注文ID 製品名                   注文数
-----------------------------------------
         1     テレビ                      3

また、テレビの在庫数は、7になっています。

SQL> SELECT   FROM 在庫;

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

では、ラジオの在庫数は5台ですが、ラジオを8台注文してみます。当然在庫不足です。

SQL> BEGIN
  2     PROC_注文受付('ラジオ',8);
  3  END;
  4  /
BEGIN
 
行1でエラーが発生しました。:
ORA-20000: 在庫不足エラー :ラジオ
ORA-06512: "SCOTT.PROC_注文受付", 行8
ORA-06512: 行2

上記の結果どおり、在庫不足エラーになります。

では、4台の注文ならOKです。

  1  BEGIN
  2     PROC_注文受付('ラジオ',4);
  3  END;
SQL> /

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

これにより注文表にラジオ4台の注文があります。

SQL> SELECT   FROM 注文;

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

そして、ラジオの在庫は、注文数の分だけ減り1台となりました。

SQL> SELECT   FROM 在庫;

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

いかがですが。プロシージャの用途はまさに千差万別です。
今回はプロシージャらしい処理を具体例として紹介しました。
似たような処理がきっと、みなさんの身近にもあるのではないでしょうか。

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

先頭へ戻る