Hi
I have a procedure which has 5 parameters , out of which 3 parameters can be passed as null.
The procedure does an update based on the 3 parameters as predicate. For instance
create or replace procedure demo(id in number,salary number,name varchar2,department varchar2, job varchar2) as
begin
update emp
set sal=salary
where empid=id
and (emp_name=name or emp_name is null)
and (dept_name=department or dept_name is null)
and (job_type=job or job_type is null);
end;
Assume that there is index on emp_name,dept_name,job_type (for this discussion) .
When i take explain plan of the above query , it goes for full table scan .
I tried using 11g feature of index creation with constant value for null values on index column and it works fine
i.e create index dept_name_idx on emp(dept_name,1)
But I'm looking for a better way to solve this problem programmatic.I used multiple if else clauses but i dont think the solution as convincing since i have to use multiple combinations in the if else clause.
Please suggest me some other way to write the above query.
Appreciate your suggestions !
Regards
sasidharan