StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>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.</p> <pre><code>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; / </code></pre> <hr> <p>This may seem like a long-winded solution compared to the currently-accepted answer, but here's why it is the better approach: <em>it returns the correct answer</em>. </p> <p>In deparment 40 there is an employee with no name:</p> <pre><code>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> </code></pre> <p>If I implement the apparently neater DECODE() solution ...</p> <pre><code>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; / </code></pre> <p>... this is what happens:</p> <pre><code>SQL> exec :rc := get_fix_emps(null, null, 40) PL/SQL procedure successfully completed. SQL> print rc no rows selected SQL> </code></pre> <p>Because NULL does not ever equal NULL, which is what <code>ename = DECODE(NVL(i_ename,'X'), 'X', ename, i_ename)</code> evaluates to in this case.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload