如何在SQL CASE表达式中返回多个值

来源：互联网发布：linux yum安装jdk1.7 编辑：程序博客网时间：2024/05/22 19:00

Oracle数据库开发中CASE...THEN子句有时需要返回多个值用于判断，如何做才能在CASE...THEN表达式中返回多个值呢，具体参考下面的演示：

SQL> select case  2           when to_char(sysdate, 'SS') > 30 then sys.odcivarchar2list('Sent')  3           else sys.odcivarchar2list('New', 'Sent')  4         end options  5    from dual;OPTIONS--------------------------------------------------------------------------------ODCIVARCHAR2LIST('Sent')SQL> /OPTIONS--------------------------------------------------------------------------------ODCIVARCHAR2LIST('New', 'Sent')

SQL> select *  2    from table(  3            select case when to_char(sysdate,'SS') > 30 then sys.odcivarchar2list('Sent')  4                        else sys.odcivarchar2list('New','Sent')      5                   end options  6            from dual  7              );COLUMN_VALUE--------------------------------------------------------------------------------NewSentSQL> /COLUMN_VALUE--------------------------------------------------------------------------------Sent

SQL> select *from empwhere report_status in (select *from table(selectcasewhen to_char(sysdate,'SS') > 30 then sys.odcivarchar2list('Sent')else sys.odcivarchar2list('New','Sent')end optionsfrom dual)) ;

阅读全文

0 0