Oracle 使用case语句更改where子句

你好，汤姆

是否可以根据某些变量更改where条件 (使用case语句)？

例如

var T varchar2(1)
exec :T := 'E';
var E number;
exec :E := 7788;
var N varchar2(20)
exec :N := 'MILLER';

select empno, ename
from emp
where
-- how to use case statement to vary the condition based on :T
-- if :T = 'E' then the condition should be
where empno = :E
-- and if :T = 'N' then the condition should be 
where ename like :N

嗯，你可以通过在你的病情的两边都有一个案例表达来做到这一点。

例如:

var T varchar2(1)
exec :T := 'E';
var E number;
exec :E := 100;
var N varchar2(20)
exec :N := 'Neena';

select employee_id, first_name 
from   hr.employees
where  case
  when :T = 'E' then to_char ( :E )
  when :T = 'N' then :N
end = case
  when :T = 'E' then to_char ( employee_id )
  when :T = 'N' then first_name
end;

EMPLOYEE_ID    FIRST_NAME   
           100 Steven        

exec :T := 'N';

select employee_id, first_name 
from   hr.employees
where  case
  when :T = 'E' then to_char ( :E )
  when :T = 'N' then :N
end = case
  when :T = 'E' then to_char ( employee_id )
  when :T = 'N' then first_name
end;

EMPLOYEE_ID    FIRST_NAME   
           101 Neena   

但这是一个TERRIBLE想法!

像这样使用表达式会破坏优化器使用索引的能力。所以这些查询会很慢。

最好先检查T的值，然后运行适当的 (静态) SQL语句:

begin
  if :T = 'E' then

    select ...
    into   ...
    from   hr.employees
    where  employee_id = :E; 

  elsif :T = 'N' then

    select ...
    into   ...
    from   hr.employees
    where  first_name = :N;

  end if; 
end;
/