处理输入变量的不同排列的最佳方法是动态组合查询。以下示例将生成一个执行良好并且整齐地处理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)在这种情况下评估的结果。