PL/SQL

PL/SQL コントロールブレイク処理 SQLとの比較も紹介

PL/SQL

こんにちは! 健史です。

SQLでは、キーごとの集計処理がとても簡単にできます!

PL/SQLで同じ処理を作成してみました。

スポンサーリンク

処理概要とSQLによる処理

処理概要です。

テーブル[商品]を項目[商品分類コード]ごとの件数を集計し、テーブル[集計]に出力する処理です。




SQLです。

INSERT INTO 集計
SELECT 商品分類コード,COUNT(*) FROM 商品
GROUP BY 商品分類コード
ORDER BY 商品分類コード

PL/SQLによる処理 ループ型

プログラム

以下の記事のロジックにて作成しました。

コンロトールブレイク キーブレイクのロジックを徹底解説!VBAで確認
コントロールブレイク・キーブレイク処理をこれから作成する、作成中だけど正確に動かない、作成したけど正確性を確認したいなど思われている方へ、チェックポイントを含め詳細を紐解いて解説します。基本中の基本であるロジックをマスターしちゃいましょう!

PL/SQLです。

-- <<カーソル定義>> --
  CURSOR CUR1 IS
    SELECT * FROM 商品
    ORDER BY 商品分類コード,商品コード;
-- <<レコード定義>> --
    商品REC CUR1%ROWTYPE;
    集計REC 集計%ROWTYPE;
-- <<ワーク定義>> --
    WK_KEY  VARCHAR2(100);
    OLD_KEY VARCHAR2(100);
    WK件数   NUMBER(5);
-- <<ファンクション定義(サブルーチン)>> --
---- 1.テーブル1読み込み処理 
FUNCTION READ1
RETURN VARCHAR2 IS 
BEGIN
  FETCH CUR1 INTO 商品REC;
  IF CUR1%FOUND THEN
      RETURN(商品REC.商品分類コード);
  ELSE
      RETURN(NULL);
  END IF;
END;
BEGIN
  OPEN CUR1;
  WK_KEY := READ1();
  WHILE CUR1%FOUND LOOP
--初期処理
      OLD_KEY := WK_KEY;
      WK件数 := 0;
      集計REC  := NULL;
      集計REC.商品分類コード := 商品REC.商品分類コード;
--集計処理
      WHILE CUR1%FOUND AND OLD_KEY = WK_KEY LOOP 
          WK件数 := WK件数 + 1;
          WK_KEY := READ1();
      END LOOP;
--ブレイク後の処理
      集計REC.件数          := WK件数;
      INSERT INTO 集計 VALUES 集計REC;
  END LOOP;
  COMMIT;
  CLOSE CUR1;

PL/SQLには[Until]文がないため、[While]文でしか記述できません。

補足説明

・複数項目を結合してもよいようにWK_KEYを使用
この記事では、キー項目をワーク上の[WK_KEY]にセットしています。

WK_KEYにセットしなくても、比較項目としてワーク上に[OLD_項目名]をそれぞれ定義し、[OLD_項目名]への退避やキーの比較は入力項目名を記載しても同じです。

ですが、現場では単一項目によるブレイクキーは少なく、ほとんどが複合項目です。

そのため、[OLD_項目名]への退避やキーの比較を単純にして、かつ、汎用性を持たせるために[WK_KEY][OLD_KEY]としています。

尚、項目の結合をPL/SQLでは、[||]を使います。

以下は、参考記事です。

PL/SQL マッチング処理 SQLとの比較も紹介
こんにちは! 健史です。 SQLでは、マッチング処理がとても簡単にできます! PL/SQLで同じ処理を作成してみました。 尚、入出力データはEXCELシートで説明していますが、オラクル上にロードしたデータをSqlDevel...

[WK_KEY][OLD_KEY]は、それぞれ[VARCHAR2(100);]としています。

この記事では100桁にしていますが、結合したキー長が100桁を超える場合は変更が必要です。


また、NUMBER型の項目をブレイクキーとしてセットする場合には、そのままセットしても問題ありませんが、習慣として「LPAD」関数で頭を'0'埋めすることをお勧めします。

PL/SQLによる処理 IF文型

プログラム

「カーソルFOR LOOP文」を使用した場合のプログラムです。

-- <<カーソル定義>> --
    CURSOR CUR1 IS
    SELECT * FROM 商品
    ORDER BY 商品分類コード,商品コード;
-- <<レコード定義>> --
    集計REC 集計%ROWTYPE;
-- <<ワーク定義>> --
    WK_KEY  VARCHAR2(100);
    OLD_KEY VARCHAR2(100);
    WK件数   NUMBER(5);
BEGIN
    OLD_KEY := '';
    FOR 商品REC IN CUR1 LOOP
        WK_KEY := 商品REC.商品分類コード;
        IF OLD_KEY IS NULL OR WK_KEY <> OLD_KEY THEN
            IF WK_KEY <> OLD_KEY THEN
                集計REC.件数          := WK件数;
                INSERT INTO 集計 VALUES 集計REC;
            END IF;
            WK件数 := 0;
            OLD_KEY := WK_KEY;
            集計REC  := NULL;
            集計REC.商品分類コード := 商品REC.商品分類コード;
        END IF;
        WK件数 := WK件数 + 1;
    END LOOP;
    IF WK件数 > 0 THEN
        集計REC.件数          := WK件数;
        INSERT INTO 集計 VALUES 集計REC;
    END IF;  
    COMMIT;

処理結果はループ型と同じです。

カーソルで使用するテーブル定義、OPEN,CLOSE,FETCH,FOUND・NOTFOUNDを使ったデータ終了判定が不要です。

補足説明

・商品コードにNULLはない前提
初期処理[OLD_KEY := '';]です。

これが意味するところは「商品コードにNULLはない前提」です。


また「OLD_KEYがNULLで、かつ、WK_KEYがNULL以外の場合」に[WK_KEY <> OLD_KEY]の条件を満たしません。

PLSQLに触れ始めたころ、「カーソルFOR LOOP文 & IF文型 によるコントロールブレイク処理」を作成していて悩まされました。

データ終了やキーブレイクの判定について

COBOLやPL/Iなど汎用機系とは対象的というか新ジャンルというかに扱われるオープン系についてです。

個人的には、ロジックを作成する上で汎用機系もオープン系も関係ないと思っていますが。


オープン系言語のPL/SQLでは、データの最終判定を

OPEN xxx_CUR;
LOOP
    FETCH xxxx_CUR INTO xxxx_REC;
    EXIT WHEN xxxx_CUR%NOTFOUND;
    1データの処理を記述
END LOOP;
CLOSE xxxx_CUR;

のように「EXIT WHEN xxxx_CUR%NOTFOUND;」にて、ループを抜けるのが一般的なのでしょうか?

「EXIT WHEN xxxx_CUR%NOTFOUND;」を否定するものではありません。

汎用機でCOBOLやPL/Iを開発してきた私にとっては違和感のある記述です。

反対に最初からオープン系に携わってこられた方には、「UntilやWhile」型は違和感のある記述なのかもしれません。


以下の記事で、お問い合わせ頂いた方(Aさん)とメールをやり取りしたときの内容です、少し編集を加えていますが。

コントロールブレイク応用編 VBAで帳票イメージを作成
こんにちは! 健史です。 この春、当サイトを訪問下さっておられる方からメールを頂きました。 「帳票系の処理を作成したいが、ヒント・アドバイスをもらえれば」との内容でした。 すぐさまプログラムを作成し、データとともに送信致し...

Aさん:データ終了の判定やコントールブレイクをUntilやWhile型で記述するのは、汎用機のCOBOLなどを経験した人だからこそのロジックだと思います。

私  :あっ~なるほど、そうなんですかね、それ気づきませんでした!



どちらにするかは慣れの問題であると思います。

ですが当記事で紹介致しましたように「UntilやWhile型の方がわかりやすいのでは?」と思っています。

コントロールブレイク処理はループ型で作成することのすすめ

コントロールブレイク処理はSQLでもできますが、複雑な処理はPL/SQLを始めとするプログラムで作成せざるを得ません。

そして「カーソルFOR LOOP文」を使用した場合は、読み込み処理を制御できないため、IF文型で作成するしかありせん。


「カーソルFOR LOOP文」を「カーソルで使用するテーブル定義、OPEN,CLOSE,FETCH,FOUND・NOTFOUNDを使ったデータ終了判定が不要」なためだけに使用するのであれば、コントロールブレイク処理ではループ型より無駄があり処理が複雑であると思います。


これまでコントロールブレイク処理を「カーソルFOR LOOP文 & IF文型」で標準化されておられる組織・担当の方、今からでもコントロールブレイク処理だけは「ループ型で構造化」することを検討し変更されてはいかがでしょうか。

IF文型よりは見やすく、新人の方にも説明しやすく分かりやすいと思います。

最後に

この記事のプログラムは個人的な技術検証の観点で作成し始めたのですが、PL/SQLで疑問に思っていたことが解消しました。

参考にして頂ければと思います。

PL/SQL
スポンサーリンク
- 面白かったらシェアお願いします! -
健史をフォローする
自分で改善

コメント