5. Oracle case when 以及decode

最新推荐文章于 2025-09-19 23:45:05 发布

原创最新推荐文章于 2025-09-19 23:45:05 发布 · 572 阅读

0 ·

CC 4.0 BY-SA版权

oracle 专栏收录该内容

17 篇文章

订阅专栏

本文详细解析了PL/SQL中的CASE表达式的两种形式：CASEx和CASE when，并通过实例展示了如何在PL/SQL块和SQL查询中使用CASE表达式进行条件判断和值选择，包括使用DBMS_OUTPUT过程输出结果和在SELECT语句中应用CASE表达式。

两种方式:

1. case x(字段) when xx1 then .... when xx2 then.... else.......end case;

2. case when xx1=yy1 then ..... when xx2=yy2 then...... when xx3=yy3 then....else........end case;

使用例子；

case X when XX1 then ..when xx2 then ....else ....end case ;

declare 
nowDate varchar2(100):='2218';

begin

case nowDate   
when  '2017'  then  
dbms_output.put_line('年份为:'|| nowDate);

when '2018' then
dbms_output.put_line('年份为:'|| nowDate);

when '2019' then
dbms_output.put_line('年份为:'|| nowDate);
else
dbms_output.put_line('你们猜错了');
dbms_output.put_line('年份为:'|| nowDate);
end case;

end;

case when xx1 then ..... when xx2 then...... when xx3 then....else........end case;

declare 
nowDate varchar2(100):='2218';

begin

case    
when nowDate='2017'  then  
dbms_output.put_line('年份为:'|| nowDate);

when nowDate='2018' then
dbms_output.put_line('年份为:'|| nowDate);

when nowDate='2019' then
dbms_output.put_line('年份为:'|| nowDate);
else
dbms_output.put_line('你们猜错了');
dbms_output.put_line('年份为:'|| nowDate);
end case;

end;

SQL查询语句的写法:

 select T.DUMMY,
          case
            when DUMMY = 'X' then
             'dual defualt is X'
            else
             'dual defualt value is other'
          
          END DUMMY_DEFAULT
   
 from dual T

  select T.DUMMY,
          case  DUMMY
            when  'X' then
             'dual defualt is X'
            when  'XY' then
             'dual defualt is XY'
            else
             'dual defualt value is other'
          
          END DUMMY_DEFAULT
from dual T

where 条件子句中

select * from dual T where  DUMMY =
      case  DUMMY
          when  'X' then 'X'
          when  'XY' then 'XY' 
          else  ''
         END

decode（条件A，A1，条件B，B1，条件C C3）智能用户select 中，不能用户where条件后面

select decode(count(*) ,1, 100000, 2,1000 ) from dual;