使用不同类型的 AND 条件创建 ORACLE PL/SQL 存储过程 [英] Creating ORACLE PL/SQL store procedures with different kind of AND conditions

问题描述

例如我需要创建一个 Oracle 查询

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

如果三个输入都不为空，它应该像

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

如果我将 i_emp_id 传递为 null，那么查询应该像

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

如果我将 i_emp_id 传递为 null 并将 i_emp_dpt 传递为 null，那么查询应该像

select * from emp where emp_nm=i_emp_nm

解决方案

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

创建或替换函数get_dyn_emps(i_empno in emp.empno%type, emp.ename%type 中的 i_ename, i_deptno in emp.deptno%type)返回 sys_refcursor是rc sys_refcursor;stmt varchar2(32767)；开始stmt := 'select * from emp where 1=1';如果 i_empno 不为空然后stmt := stmt||'和 empno = :p_empno';别的stmt := stmt||'和 (1=1 或 :p_empno 为空)';万一;如果 i_ename 不为空然后stmt := stmt||'和 ename = :p_ename';别的stmt := stmt||'and (1=1 or :p_ename is null)';万一;如果 i_deptno 不为空然后stmt := stmt||'和 deptno = :p_deptno';别的stmt := stmt||'和 (1=1 或 :p_deptno 为空)';万一;为 stmt 打开 rc使用 i_empno, i_ename , i_deptno;返回 rc;结束 get_dyn_emps;/

<小时>

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

在第 40 部门有一个没有名字的员工:

SQL>var rc refcursorSQL>exec :rc := get_dyn_emps(null, null, 40)PL/SQL 过程成功完成.SQL>打印 rcEMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO------- ---------- --------- ---------- --------- ---------- ---------- ---------8101 03-DEC-10 40SQL>

如果我实施明显更简洁的 DECODE() 解决方案......

创建或替换函数get_fix_emps(i_empno in emp.empno%type, emp.ename%type 中的 i_ename, i_deptno in emp.deptno%type)返回 sys_refcursor是rc sys_refcursor;开始打开 rcSELECT * FROM empWHERE 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);返回 rc;结束 get_fix_emps;/

...这是发生的事情:

SQL>exec :rc := get_fix_emps(null, null, 40)PL/SQL 过程成功完成.SQL>打印 rc未选择任何行SQL>

因为 NULL 永远不等于 NULL，这就是 ename = DECODE(NVL(i_ename,'X'), 'X', ename, i_ename) 在这种情况下的计算结果.>

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

解决方案

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.

这篇关于使用不同类型的 AND 条件创建 ORACLE PL/SQL 存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！