ORACLE-CASE 需要 INTO？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1837280/
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
ORACLE- CASE requires an INTO?
提问by Hymanson
When I try the following:
当我尝试以下操作时：
declare var_type VARCHAR2(10);
begin
var_type := 'B';
select case var_type
when 'B' then 'Beans'
when 'L' then 'Legumes'
end
from tableOfBeans ;
end;
I get an error saying
我收到一条错误消息
ORA-06550: line 4, column 1:
PLS-00428: an INTO clause is expected in this SELECT statement
Error detected at position #:62
ORA-06550：第 4 行，第 1 列：
PLS-00428：此 SELECT 语句中需要 INTO 子句
在位置 #:62 处检测到错误
But when I don't use var_typebut instead use 'B' as the condition it works nicely. Can anyone tell me why this happens and how do I fix it so I can use var_typeproperly?
但是当我不使用var_type而是使用“B”作为条件时，它运行良好。谁能告诉我为什么会发生这种情况以及如何修复它以便我可以var_type正确使用？
回答by Bernard Dy
You have wrapped your SQL in a begin/end block, which makes it a PL-SQL statement. In PL-SQL, Oracle is expecting a place to put the result set from the SQL. That's the source of your ORA-06550 error. You'll want a PL-SQL variable declared to hold the result of the query via the INTO.
您已将 SQL 包装在开始/结束块中，这使其成为 PL-SQL 语句。在 PL-SQL 中，Oracle 希望有一个地方可以放置来自 SQL 的结果集。这就是您的 ORA-06550 错误的根源。您需要声明一个 PL-SQL 变量以通过 INTO 保存查询结果。
But the SELECT statement is still a little weird. The way you have it, there's no point in having a real table "tableOfBeans" in the FROM clause.
但是SELECT语句还是有点奇怪。按照你的方式，在 FROM 子句中有一个真正的表“tableOfBeans”是没有意义的。
Did you want something like this?
你想要这样的东西吗？
declare var_type VARCHAR2(10);
begin
select case tableOfBeans.beanType
when 'B' then 'Beans'
when 'L' then 'Legumes'
end
into var_type
from tableOfBeans;
end;
Actually, that would generate an error too if you had more than one row in tableOfBeans. Maybe what you wanted was to retrieve items from tableOfBeans where tableOfBeans.beanType = var_type. In that case you'd still need a way to store the result set. Sorry if I'm not understanding what you're trying to do. A little more detail will help.
实际上，如果 tableOfBeans 中有不止一行，那也会产生错误。也许您想要的是从 tableOfBeans 中检索项目，其中 tableOfBeans.beanType = var_type。在那种情况下，您仍然需要一种方法来存储结果集。对不起，如果我不明白你想要做什么。多一点细节会有所帮助。
回答by OMG Ponies
The CASEstatement doesn't require an INTO.
ORA-6550 is a generic errorfor when your SQL doesn't compile. The error is reporting that it thinks the issue is there's no INTOclause because it thinks you're trying to perform a SELECT INTO statement, like this:
该CASE语句不需要INTO.
ORA-6550 是SQL 无法编译时的一般错误。错误报告它认为问题在于没有INTO子句，因为它认为您正在尝试执行 SELECT INTO 语句，如下所示：
SELECT t.column
INTO var_type
FROM tableOfBeans
I don't have an Oracle install to test against, but I have experienced Oracle 9i/10g being weird with CASE statements, sometimes requiring you to use END CASErather than just END. END CASEis mentioned in the Oracle documentation here.
我没有要测试的 Oracle 安装，但我经历过 Oracle 9i/10g 的 CASE 语句很奇怪，有时需要您使用END CASE而不仅仅是END. END CASE在Oracle文档中这里提到。
Usually you'd use DUAL for this sort of thing, testing if the variable is set:
通常你会使用 DUAL 做这种事情，测试变量是否设置：
var_type := 'B';
SELECT CASE var_type
WHEN 'B' then 'Beans'
WHEN 'L' then 'Legumes'
END CASE
FROM DUAL;
回答by Christian13467
As answered before the errors says that you have to put the result of the select intoa variable.
正如在错误之前回答的那样，您必须将选择的结果放入变量中。
declare
var_type VARCHAR2(10);
var_into VARCHAR2(10);
begin
var_type := 'B';
select case var_type
when 'B' then 'Beans'
when 'L' then 'Legumes'
end
into var_into
from tableOfBeans;
end;
If your table tableOfBeans contains more than one row you will run into another error ORA-01422: exact fetch returns more than one row. Use pseudo table dual instead or following pl/sql code.
如果您的表 tableOfBeans 包含多行，您将遇到另一个错误 ORA-01422：精确获取返回多于一行。使用伪表双代替或遵循 pl/sql 代码。
declare
var_type VARCHAR2(10);
var_into VARCHAR2(10);
begin
var_type := 'B';
var_into := case var_type
when 'B' then 'Beans'
when 'L' then 'Legumes'
end;
end;
