PL/SQL 'case ...when then ... else end' 用法

  select nvl(max(to_number(TRIM( case
                                  when substr(replace(replace(mtln.lot_number,'+',''),'*',''), 1, 1) in
                                      ('0', '1', '2', '3', '4', '5', '6', '7', '8', '9') then
                                  substr(replace(replace(mtln.lot_number,'+',''),'*',''), 1)
                                 when substr(replace(replace(mtln.lot_number,'+',''),'*',''), 2, 1) in
                                      ('0', '1', '2', '3', '4', '5', '6', '7', '8', '9') then
                                  substr(replace(replace(mtln.lot_number,'+',''),'*',''), 2)
                                 else
                                  substr(replace(replace(mtln.lot_number,'+',''),'*',''), 3)
                               end))),500)   from dual;

 

 

相对decode 强大些

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/24658529/viewspace-713619/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/24658529/viewspace-713619/