oracle 多个and条件,用不同类型的AND条件创建ORACLE PL / SQL存储过程

处理输入变量的不同排列的最佳方法是动态组合查询。以下示例将生成一个执行良好并且整齐地处理NULL值的查询，以便返回正确的结果。

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;

/

与目前接受的答案相比，这似乎是一个冗长的解决方案，但这就是为什么它是更好的方法：它返回正确的答案。

在公证40中，有一名没有姓名的雇员：

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>如果我实施明显更整洁的DECODE()解决方案......

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;

/......这就是：

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

PL/SQL procedure successfully completed.

SQL> print rc

no rows selected

SQL>因为NULL不等于NULL，这就是ename = DECODE(NVL(i_ename,'X'), 'X', ename, i_ename)在这种情况下评估的结果。