Oracle Select文 引数がnullなら全件検索、引数があるならwhere句に設定したい

Question

declare
  hoge varchar(10);
begin
//  hoge:= null;
//  hoge:= '1234567890'

select
    * 
from
    hoge
where
    ??
  
end;   

hoge is nullの場合、where句に指定なし
hoge is not nullの場合、where句に
hogecode = hoge
を含めたいといった場合、どのようにすれば良いでしょうか。

お願いします。

Comments

[Orlofsky]

declare ～ end はPL/SQLです。タグ[PL/SQL]を追加してください。

Answer 1

where句では、カラム=nullは検索対象外になります。
つまり、カラム=条件値でis not nullは成立しているのでわざわざ　カラム is not nullする必要はありません。
結果的に、カラム=nullは全件検索になるとも
言えるということになります。
つまり、if文などでの制御も不要です。
なんにもしなくても
カラム=nullなら全件、カラム=nullでない値なら検索するという命令が成立しています。

たとえば
（1）where in (select カラム from テーブル名)　　
（2）where テーブル名.カラム is not null 　　　　
（1）と（2）は、同じ意味になります。
カラムにインデックスがあるなら、（1）がパフォーマンスが高いです。
（2）のようなis not null 　やis null 　は
全件検索になるためパフォーマンスは悪いです。
しかし、パフォーマンスはデータ量やアクセス件数にも影響するため、影響を考慮するのであれば
（2）のようなis not null 　やis null 　は
避けた方が良いとは言えます。
しかし、技術的に未熟でも簡単である便利さや
保守に工数を掛けたくない場合には、
（2）のようなis not null 　やis null 　は
非常に簡単で便利であり
保守による統計情報の分析などで
パフォーマンス悪化を回避することが容易に出来る可能性も外せない事項だとも言えます。
リスクと保守運用を考慮して
どちらを選択するかを検討すれば
一概にどちらが正解でどちらが誤りだと判断することは難しいのだと考えられます。
参考資料:
https://oreno-it.info/archives/1953
否定形のSQLのチューニング方法
例えば、empテーブルのbusyo_cdが10,20,30,40,50,nullのとき、null以外のデータを取得する場合です。
・否定形の場合
select * from emp where busyo_cd is not null;
・否定系をやめた場合
select * from emp where busyo_cd in (10,20,30,40,50）

Comments

[King_of_Flies]

ありがとうございました。

Answer 2

selectの結果をどうするのか不明ですので、分岐についてだけ。

性能無視していいなら

select * from hoge
where hogecode = coalesce(:hoge, hogecode)
;

性能も考慮するなら

 if :hoge is null then
   select * from hoge
   ;
 else
   select * from hoge
   where hogecode = :hoge
   ;
 end if;

Answer 3

次の質問から使っているテーブルのCREATE TABLE文やデータをINSERT文で提示してください。

CREATE TABLE hoge
    (
    hogecode   varchar2(10)
  , hogecode2  varchar2(1)
    ) ;

INSERT INTO hoge(hogecode, hogecode2) VALUES('1234567890', '0') ;
INSERT INTO hoge(hogecode, hogecode2) VALUES('1234567891', '1') ;
COMMIT ;

以下、
hoge varchar2(10) := null;
hoge varchar2(10) := '1234567890';
のどちらかを有効にして実行。

-- PL/SQL
SET SERVEROUTPUT ON
declare
    hoge varchar2(10) := null;
--  hoge varchar2(10) := '1234567890';

    CURSOR CUR_HOGE
    IS
        select
            *
        from
            hoge
        where
            hoge IS NULL
        OR  hogecode = hoge ;
begin
    FOR REC_HOGE IN CUR_HOGE LOOP
        DBMS_OUTPUT.PUT_LINE(REC_HOGE.hogecode2) ;
    END LOOP ;
end;
/

次の質問の前に
超入門「PL/SQL」 1～5
ここからはじめよう Oracle PL/SQL入門
くらいは勉強してください。

Answer 4

OR で条件指定するとどうでしょうか。

SELECT
   * 
FROM
    hoge_table
WHERE
    hoge IS NULL
 OR hogecode = hoge