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

I need to create a Oracle query for example

select * from emp where emp_id=i_emp_id and emp_nm=i_emp_nm and emp_dpt=i_emp_dpt

if all the three inputs are not null it should function like

select * from emp where emp_id=i_emp_id and emp_nm=i_emp_nm and emp_dpt=i_emp_dpt

if i pass i_emp_id as null then the query should function like

select * from emp where emp_nm=i_emp_nm and emp_dpt=i_emp_dpt

if i pass i_emp_id as null and i_emp_dpt as null then the query should function like

select * from emp where emp_nm=i_emp_nm

Answers


The best way to handle different permutations of input variables is to assemble the query dynamically. The following example will produce a query which performs well and handles NULL values neatly so as to return the 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
is
    rc sys_refcursor;
    stmt varchar2(32767);
begin
    stmt := 'select * from emp where 1=1';
    if i_empno is not null
    then
        stmt := stmt||' and empno = :p_empno';
    else
        stmt := stmt||' and (1=1 or :p_empno is null)';
    end if;
    if i_ename is not null
    then
        stmt := stmt||' and ename = :p_ename';
    else
        stmt := stmt||' and (1=1 or :p_ename is null)';
    end if;        
    if i_deptno is not null
    then
        stmt := stmt||' and deptno = :p_deptno';
    else
        stmt := stmt||' and (1=1 or :p_deptno is null)';
    end if;

    open rc for stmt 
        using i_empno, i_ename , i_deptno;
    return rc;
end get_dyn_emps;
/

This may seem like a long-winded solution compared to the currently-accepted answer, but here's why it is the better approach: it returns the correct answer.

In deparment 40 there is an employee with no name:

SQL> var rc refcursor
SQL> exec :rc := get_dyn_emps(null, null, 40)

PL/SQL procedure successfully completed.

SQL> print rc

  EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM    DEPTNO
------- ---------- --------- ---------- --------- ---------- ---------- ---------
   8101                                 03-DEC-10                              40

SQL>

If I implement the 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
is
    rc sys_refcursor;
begin
    open rc for 
        SELECT * FROM emp 
         WHERE empno = DECODE(NVL(i_empno,0), 0, empno, i_empno)
           AND ename = DECODE(NVL(i_ename,'X'), 'X', ename, i_ename) 
           AND deptno = DECODE(NVL(i_deptno,0), 0, deptno, i_deptno);
    return rc;
end get_fix_emps;
/

... this is what happens:

SQL> exec :rc := get_fix_emps(null, null, 40)

PL/SQL procedure successfully completed.

SQL> print rc

no rows selected

SQL>

Because NULL does not ever equal NULL, which is what ename = DECODE(NVL(i_ename,'X'), 'X', ename, i_ename) evaluates to in this case.


Need Your Help

How to print String buffer lines from last to first order

java string stringbuilder stringbuffer

I have requirement to print String buffer lines as last to first order.

Working with ocaml Lwt sockets

sockets ocaml

I have been on learning Ocaml for a week, some things got clear, the others rather not.