ITコーディネータのシュウです。

EXCELブログのモカちゃんさんより頂いた写真です。長野県の安曇野にある、わさび園というところで、とても水がきれいなところだそうです。この風景を見ていると何年か前のNHKの朝ドラ「おひさま」によく出てきていた場面を思い出しますね。なつかし～い！
日本には美しいところが本当に多いですね。この美しい自然環境を如何に保存して後世に残していくか、ということも本当に重要な問題だと思います。

＜本日の題材＞
カーソル(Oracle)

題材の順番としては、少し遅くなってしまった感がありますが、今回は、カーソルについての使用方法についてです。
プログラムにおいては、SELECT文によって抽出したデータを変数に代入して処理をするということがいろいろな場面で必要とされるケースがあります。カーソルとは、検索したデータを変数に代入する方法の１つで、検索結果が複数行の場合も処理できるという特徴があります。

【カーソル処理の流れ】
DECLARE
  カーソルの定義 (CURSOR <カーソル名> IS <SELECT文>)
BEGIN
  カーソルのオープン (OPEN <カーソル名>)
LOOP
  データの取出し (FETCH <カーソル名> INTO <変数名>)
END LOOP;
データのクローズ (CLOSE <カーソル名>)
END;

それでは、どういう場合にカーソルを使用するのか？ということですが、抽出した行を１件ずつ順番に処理したい場合などに使用します。1行目の処理の結果が、次行以降の処理に影響を与えるような場合には、カーソル処理でしかうまく対応できないということがあります。

例えば、よく例で上げられるのが在庫引き当て処理。
受注データが複数件あった場合に、以下のような処理を受注した順番に行うようなケース。

１）受注(明細)の各レコードについて、対象の商品について在庫の問い 合わせを実施
２）在庫数が受注数より多ければ、在庫の引当を実施。（在庫テーブルのUPDATE）
３）在庫の引当を実施したものについては、受注のステータスを「出荷」に変更して、出荷テーブルにデータを登録。在庫数が不足していれば、受注のステータスを在庫引当エラーにして出荷をとめる。

この場合、受注(明細)データ1レコード毎に在庫数を更新して行くことになり、途中で在庫数が不足するということも考えられるため、1行ずつ順に処理をして行く必要があります。

SQLの例としては、1行ごとの受注データについて、在庫テーブルの在庫数を抽出して、在庫数が受注数以上の場合には、結果がわかるように引当OKのメッセージを出しながら、在庫数から受注数を差し引く処理を行うもの（簡略化したもの)を以下に記します。

その前に、当日の受注データと、在庫テーブルの情報を示します。
・受注データの内訳
SELECT
  J01.受注番号, J01.顧客番号, J01.受注日, J02.商品CD, J02.商品数
  FROM 受注テーブル J01
  JOIN 受注明細テーブル J02
      ON J01.受注番号 = J02.受注番号
WHERE TO_CHAR(J01.受注日, 'YYYYMMDD') = TO_CHAR(SYSDATE, 'YYYYMMDD')
ORDER BY J01.受注番号, J02.受注明細番号;
（当日が 2014/11/07 になってます）

・在庫テーブルの情報
SELECT 商品CD, 在庫数
FROM 在庫テーブル
ORDER BY 商品CD;

上記で示したカーソルを使った例：

DECLARE
CURSOR jyutyu_cur IS
SELECT
  J01.受注番号, J01.顧客番号, J01.受注日, J02.商品CD, J02.商品数
  FROM 受注テーブル J01
  JOIN 受注明細テーブル J02
      ON J01.受注番号 = J02.受注番号
WHERE TO_CHAR(J01.受注日, 'YYYYMMDD') = TO_CHAR(SYSDATE, 'YYYYMMDD')
ORDER BY J01.受注番号, J02.受注明細番号;

jyutyu_rec jyutyu_cur%ROWTYPE;
V_在庫数 在庫テーブル.在庫数%TYPE;

BEGIN
  OPEN jyutyu_cur;
  LOOP
      FETCH jyutyu_cur INTO jyutyu_rec;
      EXIT WHEN jyutyu_cur%NOTFOUND;

      SELECT 在庫数 INTO V_在庫数 FROM 在庫テーブル
       WHERE 商品CD = jyutyu_rec.商品CD;

      IF V_在庫数 >= jyutyu_rec.商品数 THEN
         DBMS_OUTPUT.PUT_LINE('受注番号='|| jyutyu_rec.受注番号||',商品CD='|| jyutyu_rec.商品CD||',商品数='|| jyutyu_rec.商品数||', 引当OK');

      UPDATE 在庫テーブル SET
           在庫数 = 在庫数 - jyutyu_rec.商品数
       WHERE 商品CD = jyutyu_rec.商品CD;

      ELSIF V_在庫数 < jyutyu_rec.商品数 THEN
        DBMS_OUTPUT.PUT_LINE('受注番号='|| jyutyu_rec.受注番号||',商品CD='|| jyutyu_rec.商品CD||',商品数='|| jyutyu_rec.商品数||', 引当不可');
      END IF;
  END LOOP;
  CLOSE jyutyu_cur;
END;
/

結果は、以下

処理後の在庫テーブルの各商品の在庫数は以下になります。

SELECT 商品CD, 在庫数
FROM 在庫テーブル
ORDER BY 商品CD;

在庫数が受注数以上の場合は、在庫の引当OKとなるが、在庫数が少なくなって、受注の商品数のほうが在庫数より大きくなった場合（受注番号の J000010009、J100010010では商品CD=S00005、S00002が在庫不足）は引当不可となっています。

※「%ROWTYPE」属性は、Oracleですでに定義されている表などの行構造を参照するもので、列名やデータ型はそのまま元の表のものを使用します。そして、「カーソル名%ROWYPE」とすれば、カーソルで指定している列を持つ変数というかたちになり、一つ一つ変数を設定する必要がなくなり、SQLの記載がシンプルになります。SELECT文で抽出する項目が多いほど、記述はらくになりますね。

今日は以上まで

にほんブログ村