Oracle® 非公式 SQL, PL/SQL & DBA's リファレンス

売上累計と売上明細に関する仮想問題の(解答)

テーブルレイアウト

売上明細                          売上累計
------------------------------    ---------------------------------
商品コード   CHAR(2)   NN / PK     商品コード     CHAR(2)   NN / PK
本日販売数   NUMBER(4) NN          総販売数       NUMBER(8) NN
販売日       DATE      NN          更新日         DATE

NN = NOT NULL / PK = PRIMARY KEY

「いったい」何がまずいのか………?

UPDATE 売上累計
SET 総販売数 =
    総販売数 + (
            SELECT SUM(本日販売数) FROM 売上明細
            WHERE 売上明細.商品コード = 売上累計.商品コード
                  AND 売上明細.販売日 >= TRUNC(SYSDATE)
                  AND 売上明細.販売日 <  TRUNC(SYSDATE+1)
    ),
    更新日 = SYSDATE
 WHERE
    更新日 < TRUNC(SYSDATE+1)

このSQLには、総販売数のNOT NULL制約に反する潜在条件が存在する。

NULLの餌食(破壊のターゲット)に選ばれるのは、「当日に売上のなかった商品」、該当商品コードの総販売数をすべてNULLに置き換えてしまう事になる。

解答例

相関問い合わせに該当レコードが無い場合 SELECTはNULLを戻し、NO_DATA_FOUND例外は発生しない。

結果 総販売数 = 総販売数 + NULL ⇒ NULLとなるはずが NOT NULL制約によりエラーとなる。

解決方法として

1. 相関問い合わせのNULLを処理する
2. 相関問い合わせになるレコードを除去する のどちらか

• その1 (相関問い合わせ全体を NVL 関数 で囲む)

UPDATE 売上累計
SET 総販売数 =
    総販売数 + NVL(
            SELECT SUM(本日販売数) FROM 売上明細
            WHERE 売上明細.商品コード = 売上累計.商品コード
                  AND 売上明細.販売日 >= TRUNC(SYSDATE)
                  AND 売上明細.販売日 <  TRUNC(SYSDATE+1)
    , 0),
    更新日 = SYSDATE
 WHERE
    更新日 < TRUNC(SYSDATE+1)

• その2 (WHERE に EXISTS を追加する)

UPDATE 売上累計
SET 総販売数 =
    総販売数 + (
            SELECT SUM(本日販売数) FROM 売上明細
            WHERE 売上明細.商品コード = 売上累計.商品コード
                  AND 売上明細.販売日 >= TRUNC(SYSDATE)
                  AND 売上明細.販売日 <  TRUNC(SYSDATE+1)
    ),
    更新日 = SYSDATE
 WHERE
    更新日 < TRUNC(SYSDATE+1) 
    AND EXISTS (
            SELECT 'MATCH' FROM 売上明細
            WHERE 売上明細.商品コード = 売上累計.商品コード
                  AND 売上明細.販売日 >= TRUNC(SYSDATE)
                  AND 売上明細.販売日 <  TRUNC(SYSDATE+1)
    )

しかし、その1 のSQLには、また別の課題が残っていた

その1 (相関問い合わせ全体を NVLで囲む) の解答には、結果上の問題はないが NULLを戻す 無駄な検索している点に問題がある。 処理件数が多い場合のレスポンスを考慮するとその2の「相関問い合わせになるレコードを除去する」方がより良い解答といえる。

---