【Oracle】SQLの単体テスト観点

この記事でわかる・できること

SQLの単体テスト観点

この記事の対象者

OracleでSQLを書く人全般

動作環境・使用するツールや言語

Windows 10 Pro 22H2
Oracle 12cR2 Enterprise Edition

はじめに

SQLは一般的なプログラム言語と比べて何をテストすればいいのか迷いがちです。
一般的なプログラム言語はフローを確認して条件分岐をテストしたり関数やメソッドの入出力をテストするわけですが、例えば巨大な一つのSELECT文が出てきた時、何をどうテストすればいいのか？というのは確立された方法論があるわけではありません。
しかし実際にバグの原因がSQLであることは非常に多いので、テストが重要であることは言わずもがなです。
これが正解というわけでもないし全て実施しなければならないわけでもありませんが、テスト観点を考える上で一助となれば幸いです。

前提

一般的に単体テストでは、モジュール単位で、ある入力に対して期待される出力がされることをテストします。
モジュールというのはここでは１つのSQL文でもいいし複数のSQL文をひとまとめにしたファイル単位・機能単位でもいいです。
ある入力とは多くの場合はDBに保管されている既存のレコードであり、その他ユーザの入力や前段のSQLやAPの出力結果などを含みます。
出力とはSELECT文の結果でありUPDATE,DELETE,INSERTなどによりDBに格納されるレコードです。広い意味では実行時間、ロック、容量の増加、メモリへの影響なども一種の出力だと言えるでしょう。
「期待される出力」が何であるかは要するに正しい挙動が何であるか、ということです。一般的なプログラム言語と同様です。設計や要件なのでここが間違っていると前提がひっくり返ってしまいますが、テストの過程で考慮漏れを発見することもあるので必要に応じてひっくり返してください。

テスト観点

出力結果の行に対して過不足がないこと

最も基本的な観点です。
SELECT文であれば取得したいデータが過不足なく取得できていること、UPDATE,DELETE,INSERT文であれば修正したいデータが過不足なく修正できていることを確認します。
連動して他のテーブルを修正する必要があることもあります。必ずしも親子関係があるとは限りません。（どのテーブルをどのように修正するかは本来設計段階で決めるべき内容です）
取得・修正したデータが正しいこと、および取得・修正しなかったデータが正しいことを確認する必要があります。
後者は観点から漏れやすいので注意してください。

出力結果の列の値が正しいこと

上記は行に対しての確認ですが、DELETE以外では列の値が正しく取得・修正できていることを確認する必要があります。
前後比較しましょう。

WHERE・HAVING句、CASE式／文、COALESCE・DECODE関数による分岐

CASEやCOALESCEはそのままですが、WHEREやHAVINGは条件に入るか入らないかの分岐という考え方です。
特に日付関連は月の日数、うるう年、時刻データの有無、書式設定、暗黙の型変換、現在日付など複雑になりやすいので注意が必要です。

NULL値

NULLは特殊な値なのでSQLでは注意する必要があります。
テーブルをLEFT JOINした時に発生するNULLも場合によってはテストケースに入れた方がいいかもしれません。
例えばテーブルAとテーブルBをLEFT JOINしてBの列の値がX以外のものを抽出する時、列<>'X'とするとNULLの行は出ないので列<>'X' OR 列 IS NULLのようにしなければなりません。
画面上でNULLが入らないように制御しているとしても、想定外のバグで入ってくるということはよくあるので過信は禁物です。
いわゆる異常系のテストとして必要です。

業務上のデータパターン（論理削除や更新中など状態遷移を考慮する）

最も注意すべきパターンです。
パターン数が多い場合はどのケースではどう処理するのか整理しましょう。
これらのパターンは既存のデータに存在するならそれをテストケースにし、存在しなければテストデータとして登録します。
可能であれば既存のデータは全件テストすることが望ましいです。
昔の仕様で作成された、現在の仕様では発生し得ないようなデータが残っていて想定外の挙動をするというケースもあります。

パフォーマンス

実行速度については別途負荷試験を実施した方がよいですが、単体レベルでも遅くならないようにすべきです。
レコード、統計情報、インスタンスの設定、ハードウェアなど出来る限り本番環境に近い状況にした上で実行速度を測定しましょう。
キャッシュに乗るかどうかで大きく速度が変動する可能性があるので、キャッシュはクリアして測定した方がいいです。

ALTER SYSTEM FLUSH BUFFER_CACHE;
ALTER SYSTEM FLUSH SHARED_POOL;

インスタンス全体に影響するので実行時はご注意ください。
また、Oracleでは同じSQLを何度か実行すると実行計画を改善させようとする機能がありますが、場合によっては極端に遅くなることがあります。
念のため何度か続けて実行してみましょう。

過去の不具合パターン

過去に発生した不具合のうち、「これを単体テストの観点に盛り込んでいれば防げた」というものがあります。
ある程度分析してみると頻発するパターンが見えてくることもあります。
このようなパターンは観点として盛り込んでおくといいでしょう。
（実際このページで記載したパターンも経験上頻発しているパターンです）
ただしチェックリストやマニュアルにして全て盛り込むと肥大化・形骸化を招くので、必要に応じて参照できるようにナレッジ化しておくといいでしょう。横展開してチーム間でも共有できます。これは単体テストに限ったことではありません。