oracle sql if condition then select statement1 else select statement2

2021-12-30 00:00:00 sql oracle11g oracle

我有参数 :prmtr，我想要的是使用基于参数输入的选择语句.

I have parameter :prmtr and what I wanted is to use a select statement based on the parameter input.

我试过了:

if :prmtr= 'A' then
    select * from tblA;
else
    select * from tblB;
end if;

但这行不通.

还有其他方法可以做到这一点吗?

Is there some other way to do this?

推荐答案

您可以使用 CURSOR 变量和 PRINT 命令尝试类似的操作.当作为脚本运行时，这适用于 SQL* plus 和 SQL Developer 或 TOAD.

You may try something like this with a CURSOR variable and PRINT command. This works in SQL* plus and in SQL developer or TOAD when run as script.

VARIABLE prmtr VARCHAR2
EXEC :PRMTR := 'A'  -- SET values of parameter

VARIABLE x refcursor -- a cursor variable

DECLARE
BEGIN
    IF :PRMTR = 'A' THEN
      OPEN :x FOR
        SELECT *
        FROM   employees;
    ELSE
      OPEN :x FOR
        SELECT *
        FROM   departments;
    END IF;
END;
/

PRINT x  -- gives you the result of the query.

相关文章