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.

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.