Oracle 如何在case语句中返回多列

我有查询像:

select case when 1 in (1,2,3) then
(select 'abc' as "name 1",'xyz' as "name 2" from dual)
else 'pqr' end from dual; 

如果满足条件，如何返回两列？
简短的答案是你不能。更长的答案是你可以做这样的事情:

-用已知的分隔符将所有列连接成一列。然后把它们分开
-将值加载到XML文档并根据需要提取

select  case
          when 1 in ( 1, 2, 3 ) then 
            xmltype ( cursor (
              select 'abc' as "name 1", 'xyz' as "name 2"
              from   dual
          else xmltype ( cursor ( 
            select 'pqr' as "name 1"
            from   dual
from   dual;
CASEWHEN1IN(1,2,3)THENXMLTYPE(CURSOR(SELECT'ABC'AS"NAME1",'XYZ'AS"NAME2"FROMDUAL))ELSEXMLTYPE(CURSOR(SELECT'PQR'AS"NAME1
------------------------------------------------------------------------------------------------------------------------

等等。

但是这些都很麻烦。它通常更容易有两个案例表达式，第二个返回null在else:

select  case
          when 1 in ( 1, 2, 3 ) then 
            'abc' 
          else 'pqr' 
        end "name 1",
          when 1 in ( 1, 2, 3 ) then 
            'xyz'
          else null 
        end "name 2"
from   dual;