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

秋も深まってきて、紅葉の季節になってきました。なかなか時間が取れないですが、ひとときでも、美しい自然の中を散策したいですね。

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

Oracleの場合のカーソルについて、前々回、前回と題材としてアップしてきましたが、今回はSQL Serverの場合を上げてみようと思います。カーソル処理の基本的な流れは同じですが、SQL Serverの場合は以下のようになります。

【カーソル処理の流れ】
カーソルの定義
(DECLARE <カーソル名> CURSOR FOR <SELECT文>)
カーソルのオープン (OPEN <カーソル名>)
カーソルから1行データの取出し
(FETCH NEXT FROM <カーソル名> INTO <変数名>)
@@FETCH_STATUS が 0 (success) の間ループ
(WHILE @@FECTH STATUS = 0
BEGIN
    1行ごとに行いたい処理
    次の行の取得
(FETCH NEXT FROM <カーソル名> INTO <変数名>)
END)
カーソルのクローズ (CLOSE <カーソル名>)
カーソル参照を削除 (DEALLOCATE <カーソル名>)

ちょっとくどいかも知れませんが、前回のOracleで行ったカーソル処理を SQL Serverで試してみます。

DECLARE
jyutyu_cur CURSOR FOR
SELECT J01.受注番号, J01.顧客番号, J01.受注日, J02.商品CD, J02.商品数
FROM dbo.受注テーブル J01
JOIN dbo.受注明細テーブル J02 ON J01.受注番号 = J02.受注番号
WHERE CONVERT(VARCHAR, J01.受注日, 111) = CONVERT(VARCHAR, GETDATE(), 111)
ORDER BY J01.受注番号, J02.受注明細番号;

DECLARE
@受注番号 VARCHAR(10)
, @顧客番号 VARCHAR(8)
, @受注日 DATE
, @商品CD VARCHAR(6)
, @商品数 DECIMAL(4)
, @在庫数 DECIMAL(8);

OPEN jyutyu_cur;
FETCH NEXT FROM jyutyu_cur INTO @受注番号, @顧客番号, @受注日, @商品CD, @商品数;

WHILE (@@fetch_status = 0)
BEGIN

SELECT @在庫数 =在庫数 FROM dbo.在庫テーブル
WHERE 商品CD = @商品CD;

IF @在庫数 >= @商品数
BEGIN
Print '受注番号='+ @受注番号+',商品CD='+ @商品CD+',商品数='+ CAST(@商品数 AS VARCHAR)+', 引当OK';

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

ELSE
Print '受注番号='+ @受注番号+',商品CD='+ @商品CD+',商品数='+ CAST(@商品数 AS VARCHAR)+', 引当不可';

FETCH NEXT FROM jyutyu_cur INTO @受注番号, @顧客番号, @受注日, @商品CD, @商品数;
END;
CLOSE jyutyu_cur;
DEALLOCATE jyutyu_cur;

結果は、以下

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

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

SQLの記述の仕方が、OracleとSQL Serverでは多少異なることがわかると思いますが、行いたい処理は同様になすことができて、結果もOracleのときと同じになります。

今日は以上まで

にほんブログ村

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

前回に引き続き、EXCELブログのモカちゃんさんより頂いた、長野県安曇野のわさび園の写真です。とてものどかで心が休まる景色ですね。

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

今回は、前回のカーソルに関連して、カーソルFORループの使用方法を題材とします。
１行１行をフェッチし、最後の行まで同じ処理を繰り返すということをカーソル処理で行いますが、それをよりシンプルに記述することができるのが、カーソルFORループです。カーソル処理で必要な以下の処理が自動化され、処理全体が単純化されます。
・OPEN、FETCH、CLOSE文の指定
・取り出した行データを格納するための変数の定義

【カーソルFORループの記述】
DECLARE
カーソルの定義 (CURSOR <カーソル名> IS <SELECT文>)
BEGIN
FOR <ループ索引名> IN <カーソル名> LOOP
処理
END LOOP;
END;

前回の例をカーソルFORループで置き換えます。

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.受注明細番号;

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

BEGIN
FOR jyutyu_rec IN jyutyu_cur LOOP

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;
END;
/

結果は、以下

結果は前回と同じです。記述がいくぶんシンプルになりました。

今日は以上まで

にほんブログ村

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文で抽出する項目が多いほど、記述はらくになりますね。

今日は以上まで

にほんブログ村