Creating ORACLE PL/SQL store procedures with different kind of AND conditions -

i need create oracle query example

select * emp emp_id=i_emp_id , emp_nm=i_emp_nm , emp_dpt=i_emp_dpt 

if 3 inputs not null should function like

select * emp emp_id=i_emp_id , emp_nm=i_emp_nm , emp_dpt=i_emp_dpt 

if pass i_emp_id null query should function like

select * emp emp_nm=i_emp_nm , emp_dpt=i_emp_dpt 

if pass i_emp_id null , i_emp_dpt null query should function

select * emp emp_nm=i_emp_nm 

the best way handle different permutations of input variables assemble query dynamically. following example produce query performs , handles null values neatly return correct result.

create or replace function get_dyn_emps    (i_empno in emp.empno%type     ,  i_ename in emp.ename%type     , i_deptno in emp.deptno%type)     return sys_refcursor     rc sys_refcursor;     stmt varchar2(32767); begin     stmt := 'select * emp 1=1';     if i_empno not null             stmt := stmt||' , empno = :p_empno';     else         stmt := stmt||' , (1=1 or :p_empno null)';     end if;     if i_ename not null             stmt := stmt||' , ename = :p_ename';     else         stmt := stmt||' , (1=1 or :p_ename null)';     end if;             if i_deptno not null             stmt := stmt||' , deptno = :p_deptno';     else         stmt := stmt||' , (1=1 or :p_deptno null)';     end if;      open rc stmt          using i_empno, i_ename , i_deptno;     return rc; end get_dyn_emps; / 

---

this may seem long-winded solution compared currently-accepted answer, here's why better approach: it returns correct answer.

in deparment 40 there employee no name:

sql> var rc refcursor sql> exec :rc := get_dyn_emps(null, null, 40)  pl/sql procedure completed.  sql> print rc    empno ename      job              mgr hiredate         sal       comm    deptno ------- ---------- --------- ---------- --------- ---------- ---------- ---------    8101                                 03-dec-10                              40  sql> 

if implement apparently neater decode() solution ...

create or replace function get_fix_emps    (i_empno in emp.empno%type     ,  i_ename in emp.ename%type     , i_deptno in emp.deptno%type)     return sys_refcursor     rc sys_refcursor; begin     open rc          select * emp           empno = decode(nvl(i_empno,0), 0, empno, i_empno)            , ename = decode(nvl(i_ename,'x'), 'x', ename, i_ename)             , deptno = decode(nvl(i_deptno,0), 0, deptno, i_deptno);     return rc; end get_fix_emps; / 

... happens:

sql> exec :rc := get_fix_emps(null, null, 40)  pl/sql procedure completed.  sql> print rc  no rows selected  sql> 

because null not ever equal null, ename = decode(nvl(i_ename,'x'), 'x', ename, i_ename) evaluates in case.