こんにちは！ 健史です。
ＳＱＬでは、キーごとの集計処理がとても簡単にできます！
ＰＬ／ＳＱＬで同じ処理を作成してみました。
処理概要とＳＱＬによる処理
処理概要です。
テーブル[商品]を項目[商品分類コード]ごとの件数を集計し、テーブル[集計]に出力する処理です。
ＳＱＬです。
INSERT INTO 集計
SELECT 商品分類コード,COUNT(*) FROM 商品
GROUP BY 商品分類コード
ORDER BY 商品分類コード
ＰＬ／ＳＱＬによる処理 ループ型
プログラム
以下の記事のロジックにて作成しました。
ＰＬ／ＳＱＬです。
-- ＜＜カーソル定義＞＞ --
CURSOR CUR1 IS
SELECT * FROM 商品
ORDER BY 商品分類コード,商品コード;
-- ＜＜レコード定義＞＞ --
商品REC CUR1%ROWTYPE;
集計REC 集計%ROWTYPE;
-- ＜＜ワーク定義＞＞ --
WK_KEY VARCHAR2(100);
OLD_KEY VARCHAR2(100);
WK件数 NUMBER(5);
-- ＜＜ファンクション定義(サブルーチン)＞＞ --
---- 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]としています。
尚、項目の結合をＰＬ／ＳＱＬでは、[||]を使います。
以下は、参考記事です。
[WK_KEY][OLD_KEY]は、それぞれ[VARCHAR2(100);]としています。
この記事では100桁にしていますが、結合したキー長が100桁を超える場合は変更が必要です。
また、NUMBER型の項目をブレイクキーとしてセットする場合には、そのままセットしても問題ありませんが、習慣として「LPAD」関数で頭を'0'埋めすることをお勧めします。
ＰＬ／ＳＱＬによる処理 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;
１データの処理を記述
END LOOP;
CLOSE xxxx_CUR;
のように「EXIT WHEN xxxx_CUR%NOTFOUND;」にて、ループを抜けるのが一般的なのでしょうか？
「EXIT WHEN xxxx_CUR%NOTFOUND;」を否定するものではありません。
汎用機でCOBOLやPL/Iを開発してきた私にとっては違和感のある記述です。
反対に最初からオープン系に携わってこられた方には、「UntilやWhile」型は違和感のある記述なのかもしれません。
以下の記事で、お問い合わせ頂いた方(Ａさん)とメールをやり取りしたときの内容です、少し編集を加えていますが。
Ａさん：データ終了の判定やコントールブレイクをUntilやWhile型で記述するのは、汎用機のCOBOLなどを経験した人だからこそのロジックだと思います。
私 ：あっ～なるほど、そうなんですかね、それ気づきませんでした！
どちらにするかは慣れの問題であると思います。
ですが当記事で紹介致しましたように「UntilやWhile型の方がわかりやすいのでは？」と思っています。
コントロールブレイク処理はループ型で作成することのすすめ
コントロールブレイク処理はＳＱＬでもできますが、複雑な処理はＰＬ／ＳＱＬを始めとするプログラムで作成せざるを得ません。
「カーソルFOR LOOP文」を使用した場合は、読み込み処理を制御できないため、IF文型で作成するしかありせん。
「カーソルFOR LOOP文」を「カーソルで使用するテーブル定義、OPEN,CLOSE,FETCH,FOUND・NOTFOUNDを使ったデータ終了判定が不要」なためだけに使用するのであれば、コントロールブレイク処理ではループ型より無駄があり処理が複雑であると思います。
これまでコントロールブレイク処理を「カーソルFOR LOOP文 ＆ IF文型」で標準化されておられる組織・担当の方、今からでもコントロールブレイク処理だけは「ループ型で構造化」することを検討し変更されてはいかがでしょうか。
IF文型よりは見やすく、新人の方にも説明しやすく分かりやすいと思います。
ブレイク時のテストにおいても、[テスト仕様書の作成]や[テストエビデンスの採取]を「ループ内」及び「データ終了後」の２つ、同じことを行う必要があります。
ブレイク時の処理が多ければ多いほど、作業時間が掛かり勿体ないです。
ただ、コントロールブレイクをバルク処理で作成すると、構造化は制御が複雑になり「非構造化のほうが見やすい」と思います。
しかし、これも「ロジックの品質を確保でき、定型化できれば良い」と考えています。
最後に
この記事のプログラムは個人的な技術検証の観点で作成し始めたのですが、ＰＬ／ＳＱＬで疑問に思っていたことが解消しました。
参考にして頂ければと思います。
コメント