Is there a way to check is a list of values is NULL in PL/SQL?
I have something along the lines of:
SELECT * FROM myTable t WHERE t.dataId IN (:myList) OR :myList IS NULL
At run-time, the :myList symbol is substituted for a list of strings e.g.
SELECT * FROM myTable t WHERE t.dataId IN ('a', 'b', 'c') OR ('a', 'b', 'c') IS NULL
I've realised that ('a', 'b', 'c') IS NULL is invalid PL/SQL, so I wondered if there is another way to check a list of values evaluate to NULL.
The behaviour I'm attempting to emulate would evaluate ('a', 'b', 'c') to NOT NULL. I'm trying to avoid creating another variable (e.g. :myListFlag) which would return '' if the list was empty.
Maybe COALESCE will help you:
It evaluates a list of values left to right and returns the first value that is NOT NULL. If all values are NULL, it evaluates to NULL, which is a single value that you can check for IS NULL.
SELECT * FROM myTable t WHERE t.dataId IN (:myList) OR COALESCE(:myList) IS NULL
この記事はインターネットから収集されたものであり、転載の際にはソースを示してください。
侵害の場合は、連絡してください[email protected]
コメントを追加