Oracle SQL - How to build where clause with additional search parameters
There are four fields on the page:
EMPLOYEE ID
DEPT
LOCATION
UNIT:
The user can enter any of the field values, all of them are optional, if he enters EMPLOYEE ID
, then the query should return the rows associated with that EMPLOYEE ID
. If it only enters LOCATION
, then the request should return all employees of that location. How to write a where clause condition with additional parameters.
source to share
Oracle will most likely build a well-optimized query if you use NVL
in your predicates:
select *
from employee
where employee_id = nvl(:employee_id, employee_id)
and dept = nvl(:dept, dept)
and location = nvl(:location, location)
and unit = nvl(:unit, unit)
This is logically equivalent to LeoLozes' answer. While his answer is more readable, the cryptic version is better in this case. Oracle uses this "trick" and can automatically convert it to a dynamic query using the FILTER operation. The execution plan will have both FULL TABLE SCAN and INDEX RANGE SCAN, and will choose the appropriate one at run time, depending on the value of the binding variable. See my answer here for a sample code demonstrating how this works.
source to share
I would recommend against this thinking for real world applications. Submitting a dynamic style query to the database has proven to be a problem in terms of security, optimization, and functional correctness. Since there will be some application code between the UI and the database, its better to build the query as needed and then submit it for execution.
source to share
Well, there is always a (very poorly optimized) option to do it like this:
SELECT *
FROM EMPLOYEE
WHERE (EMPLOYEE_ID = :p_EMPLOYEE_ID OR :p_EMPLOYEE_ID IS Null)
AND (DEPT = :p_DEPT OR :p_DEPT IS Null)
AND (LOCATION = :p_LOCATION OR :p_LOCATION IS Null)
AND (UNIT = :p_UNIT OR :p_UNIT IS Null)
I only use it on tables with a small number of rows. However, it is recommended that you have at least one required parameter that will use the indexed fields (since here you will have a FULL ACCESS TABLE).
source to share