如果在 Oracle sql developer 中使用 Select 语句
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/34059028/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
If Else with Select statement in Oracle sql developer
提问by Srinivas Parthasarathy
I would like to know if it's possible to write a sql query that returns a set of columns based on a condition.
我想知道是否可以编写一个基于条件返回一组列的 sql 查询。
Like for example:
例如：
If (id=='A')
{
Select id,name
From Table A
}
Else If(Condition=B)
{
Select Column1, Column3
From Table A
}
If yes please help me write it
如果是，请帮我写
回答by japzdivino
Syntax (IF-THEN-ELSE)
语法 (IF-THEN-ELSE)
The syntax is for IF-THEN-ELSE in Oracle/PLSQL is:
Oracle/PLSQL 中 IF-THEN-ELSE 的语法是：
IF condition THEN
{...statements to execute when condition is TRUE...}
ELSE
{...statements to execute when condition is FALSE...}
END IF;
回答by Gergely Bacso
You can do switch-like statements with CASEexpressions in plain SQL. See the following example:
您可以使用CASE普通 SQL 中的表达式执行类似 switch 的语句。请参阅以下示例：
SELECT some_other_field,
id,
CASE ID
WHEN A THEN columnA
WHEN B THEN columnB
ELSE 'Unknown'
END genericvalue
FROM customers;
There are some limitations of course. For example the type of the return values in the THEN clause need to match, so you may need to convert for example all to char, or to int, etc.
当然也有一些限制。例如，THEN 子句中返回值的类型需要匹配，因此您可能需要将例如 all 转换为 char 或 int 等。
回答by Alex Kirko
SQL itself isn't turing-complete and it doesn't have syntax for loops and conditions: you can perform a query with it, no matter how complex it is, but you can't decide which query to execute depending on a condition or perform a query a number of times, which is what you are trying to do here.
SQL 本身不是图灵完备的，并且它没有循环和条件的语法：您可以使用它执行查询，无论它有多复杂，但您无法根据条件决定执行哪个查询或多次执行查询，这就是您在此处尝试执行的操作。
In order to provide such functionality each database developer typically provides an additional language that includes variable declaration, loops, conditionals, etc. For Oracle this language is PL/SQL.
为了提供这样的功能，每个数据库开发人员通常会提供一种额外的语言，包括变量声明、循环、条件等。对于 Oracle，这种语言是 PL/SQL。
What you need to do in SQL Developer to solve your issue and see how PL/SQL works is create an empty script, then write something like this:
您需要在 SQL Developer 中解决您的问题并查看 PL/SQL 的工作原理是创建一个空脚本，然后编写如下内容：
--Enabling output to the console
SET SERVEROUTPUT ON;
DECLARE
--Variable declaration block; can initialize variables here too
test_var varchar2(10);
test_result varchar2(10);
BEGIN
--Initializing variables, the first one we will check in the IF statement, the second one is just for transparency
test_var := 'test';
test_result := '';
--IF block: check some condition, perform a select based on the value, save result into a variable
IF test_var = 'test' THEN
SELECT '1' INTO test_result FROM dual;
ELSE
SELECT '2' INTO test_result FROM dual;
END IF;
--Output the result to console
DBMS_OUTPUT.PUT_LINE(test_result);
END;
Then run it with 'Run script'/F5. You will get '1' as output as you would expect. Change test_var to something else and run it again, you will get '2'.
然后使用“运行脚本”/F5 运行它。正如您所料，您将获得“1”作为输出。将 test_var 更改为其他内容并再次运行，您将获得“2”。
If you have questions of this kind it might be useful to read about what exactly SQL and PL/SQL are. PL/SQL is quite efficient and versatile and can be used for anything from automating SQL scripts to implementing complex optimisation algorithms.
如果您有此类问题，阅读 SQL 和 PL/SQL 究竟是什么可能会很有用。PL/SQL 非常高效且通用，可用于从自动化 SQL 脚本到实现复杂优化算法的任何事情。
Of course, PL/SQL has similar constructs for FOR and WHILE loops, CASE checks, etc.
当然，PL/SQL 对 FOR 和 WHILE 循环、CASE 检查等具有类似的构造。
回答by gowthi
I guess this is what you are looking at.
我想这就是你正在看的。
It is not possible to do such a selection in SQL even by using CASE and DECODE.
即使使用 CASE 和 DECODE，也不可能在 SQL 中进行这样的选择。
But the best we can do is we can create a function which returns a ref_cursor and use the function in the SQL stetement to fit your requirement.
但我们能做的最好的事情是我们可以创建一个返回 ref_cursor 的函数，并使用 SQL 语句中的函数来满足您的要求。
Below is an example for it:
下面是一个例子：
CREATE OR REPLACE
FUNCTION test1(
id1 VARCHAR)
RETURN sys_refcursor
AS
v_ref sys_refcursor;
BEGIN
IF(id1='A') THEN
OPEN v_ref FOR SELECT id,name FROM TABLE A;
ElsIf(id1='B') THEN
OPEN v_ref FOR SELECT Column1, Column3 FROM TABLE A;
END IF;
RETURN v_ref;
END;
select test1(A) from dual;
Above will display only the columns id and name.
以上将仅显示列 id 和 name。
