我如何在SQL的CASE语句中获取用户输入?

Question

SELECT 
&input,
(CASE input
WHEN '1' THEN 'Yes'
WHEN '2' THEN 'No'
WHEN '3' THEN 'Maybe'
ELSE 'Wrong'
END) Answer
FROM DUAL;

这是我试图在SQL开发人员执行的查询，但我得到一个错误:

Error at Command Line: 2 Column: 7
Error report -
SQL Error: ORA-00904: "INPUT": invalid identifier
00904。00000 - "%s:无效标识符">

有谁能帮我吗?

Answer 1

正如Alex所评论的那样，如果您想要使用该替换变量两次，则最好在其名称前面加上双&号。它也可以用一个，但是Oracle会提示你两次，你必须小心不要输入不同的值。另一方面，&&将防止这种情况发生，但如果您想再次运行相同的代码，则需要将变量undefine提供不同的值。

SQL> select &input,
2         case &input when 1 then 'Yes'
3                     when 2 then 'No'
4                     when 3 then 'Maybe'
5         end as answer
6  from dual;
Enter value for input: 1       --> this is for line #1
Enter value for input: 3       --> line #2; note that I provided different values
1 ANSWE
---------- -----
1 Maybe
SQL> /
Enter value for input: 2   --> reran, this time providing same values
Enter value for input: 2
2 AN
---------- --
2 No
SQL>

---

带双&号:

SQL> select &&input,
2         case &&input when 1 then 'Yes'
3                      when 2 then 'No'
4                      when 3 then 'Maybe'
5         end as answer
6  from dual;
Enter value for input: 1    --> see? I have to provide only one value
1 ANS
---------- ---
1 Yes
SQL> /                      --> but, if I rerun the same code, it uses previous value ...
1 ANS
---------- ---
1 Yes
SQL> undefine input         --> ... so you have to undefine it first
SQL> /
Enter value for input: 3
3 ANSWE
---------- -----
3 Maybe
SQL>

---

或者，您可以使用CTE，提供一次值，稍后引用它:

SQL> with temp as
2    (select &input as val from dual)
3  select val,
4         case val when 1 then 'Yes'
5                  when 2 then 'No'
6                  when 3 then 'Maybe'
7         end as answer
8  from temp;
Enter value for input: 2
VAL AN
---------- --
2 No
SQL> /
Enter value for input: 1
VAL ANS
---------- ---
1 Yes
SQL>