问oracle EBS中的绑定参数

EN

SQL> select value
  2  from (select 1 rn, 'all' value from dual
  3        union all
  4        select 2 rn, to_char(deptno) from dept
  5       )
  6  order by rn, value;

VALUE
----------------------------------------
all
10
20
30
40

SQL>

select e.deptno, e.ename, e.job
from emp e
where e.deptno = case when :par_deptno = 'all' then to_char(e.deptno)
                      else :par_deptno
                 end
order by e.deptno, e.ename;

SQL> select e.deptno, e.ename, e.job
  2  from emp e
  3  where e.deptno = case when '&&par_deptno' = 'all' then to_char(e.deptno)
  4                        else '&&par_deptno'
  5                   end
  6  order by e.deptno, e.ename;
Enter value for par_deptno: 10

    DEPTNO ENAME      JOB
---------- ---------- ---------
        10 CLARK      MANAGER
        10 KING       PRESIDENT
        10 MILLER     CLERK

SQL> undefine par_deptno
SQL> /
Enter value for par_deptno: all

    DEPTNO ENAME      JOB
---------- ---------- ---------
        10 CLARK      MANAGER
        10 KING       PRESIDENT
        10 MILLER     CLERK
        20 ADAMS      CLERK
        20 FORD       ANALYST
        20 JONES      MANAGER
        20 SCOTT      ANALYST
        20 SMITH      CLERK
        30 ALLEN      SALESMAN
        30 BLAKE      MANAGER
        30 JAMES      CLERK
        30 MARTIN     SALESMAN
        30 TURNER     SALESMAN
        30 WARD       SALESMAN

14 rows selected.

SQL>