IF 中的多个 Select 语句

Question

我可以使用一些帮助来编写报告的 prog./sql 构造。Sql

应该首先检查提示符 &然后决定哪些“select”语句应该运行

类似（伪代码）的内容，

Select ACCT,LOC FROM (

   IF :loc = 'MN' THEN
      Select acc as ACCT,location as LOC
   ELSE IF :loc = 'MA' THEN
      Select accid as ACCT,locid as LOC
   ELSE IF :loc = 'PA' THEN 
      Select accountid as ACCT,location as LOC
   END IF)

上面似乎不起作用。请帮忙！

Answer 1

你可以这样做：

Select acc as ACCT,location as LOC 
where :loc = 'MN' 
UNION ALL
Select accid as ACCT,locid as LOC 
where :loc = 'MA' 
UNION ALL
Select accountid as ACCT,location as LOC 
where :loc = 'PA';

Answer 2

怎么样...

select acc,
       case :loc
       When 'MN' then location
       When 'MA' then locid
       When 'PA' then location
       ...
       end
from   ...

或者...

select acc,
       case when :loc in ('MN','PA')
            then location
            When :loc in ('MA',...)
            then locid
            ...
       end
from   ...

这样做会更友好，因为它减少了唯一游标的数量。

Answer 3

如果您可以创建自定义 PL/SQL 函数，那么您可以从中返回结果集（所谓的记录表），然后只需从函数中进行选择。请参阅本文。