if then elsif与select case when

case when

View Code

1  select (case userid
2     when 'CUS0000003017804' then
3       '张三'
4     when 'CUS0000003017805' then
5       '李四'
6     else
7       '王五'
8   end) userid from users a where loginname = 'xxx';

View Code

 1 select case
 2          when userid ='CUS0000003017804' then
 3           '张三'
 4          when userid ='CUS0000003017805' then
 5           '李四'
 6          else
 7           '王五'
 8        end userid
 9   from users a
10  where loginname = 'xxx';

 

//字段中有符合delete等关键字的字段查询出来，不包含的不显示
select (case
         when (instr(sql_text, 'delete') > 0 or
              instr(sql_text, 'update') > 0 or
              instr(sql_text, 'insert') > 0) and
              instr(sql_text, 't_recordsql') > 0 then
          sql_text
         else
          ''
       end),length(sql_text)
  from v$sql a
 where a.LAST_ACTIVE_TIME >=
       to_date('2013/06/04 16:04:05', 'yyyy/mm/dd hh24:mi:ss')
   and a.LAST_ACTIVE_TIME <=
       to_date('2013/06/04 18:04:05', 'yyyy/mm/dd hh24:mi:ss')
 order by length(sql_text) asc

View Code

 

 

if then elsif

View Code

 1 declare
 2   v_sal users.userid%type;
 3   i     integer;
 4 begin
 5   select userid into v_sal from users where loginname = 'zhuqiaoping';
 6   if v_sal = 'zhuqiaoping' then
 7     dbms_output.put_line('张三');
 8   elsif v_sal = 'zhu' then
 9     dbms_output.put_line('李四');
10   else
11     dbms_output.put_line('王五');
12   end if;
13 
14 end;

我到现在发现的区别是，case when不能作为单独的语句块