oracle 对返回数据和where进行判断 case when

 

 

1.对返回值进行判断。

select 
        oper.opid,
        oper.user_name,
        oper.user_host,
        case 
        when oper.oper_type = 1  then 'System Manager' 
        when oper.oper_type = 2  then 'USER Manager'
        end case,
        case
        when oper.oper_object_type = 1 then 'User'
        when oper.oper_object_type = 2 then 'Role'
        when oper.oper_object_type = 3 then 'Broker'
        when oper.oper_object_type = 4 then 'QM Manager'
        when oper.oper_object_type = 5 then 'User Group'
        when oper.oper_object_type = 6 then 'Msg Flow'
        when oper.oper_object_type = 7 then 'Queue'
        end case
     
 from esb_log_user_oper oper;

 

 

 

2.对条件值进行判断

select 1 from mps.mps_sent_goods where STORE_NO =(case when 1<2 then '3011' else '3012' end);

 如果条件成立：sql为：select 1 from mps.mps_sent_goods where STORE_NO =3011 。

如果条件不成立：sql为：select 1 from mps.mps_sent_goods where STORE_NO =3012 。

 

 

 

 

 

3.条件进行判断，

declare

  str varchar(10):='null';
begin
commit;
  insert into T_FIRSTPAGE_RESULTS(pt_index) select count(*) from mps.mps_sent_goods where 1=1 and  (( str!='null' and STORE_NO =str  ) or (str='null' and 1 =1 ))  ;
end ; 

 

如果str值为null，拼接的sql为：select count(*) from mps.mps_sent_goods。

如果str值非null，拼接的sql为：select count(*) from mps.mps_sent_goods where  STORE_NO =str。