Oracle SQL 查询中的常量
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1293008/
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
Constants in Oracle SQL query
提问by FourOaks
I am new to Oracle (though familiar with SQL) and have to write a fairly complex query where a value derived from the current date is used many times. Rather than calculate the value each time, it would seem obvious to declare a constant for the purpose.
我是 Oracle 的新手（虽然熟悉 SQL）并且必须编写一个相当复杂的查询，其中多次使用从当前日期派生的值。与其每次都计算该值，不如为此目的声明一个常量似乎很明显。
However, when I then try to use my DateIndex constant in the subsequent SELECT statement (which I wish to return values based on "DateIndex"), the parser tells me that it is exepcting SELECT INTO.
但是，当我尝试在随后的 SELECT 语句中使用我的 DateIndex 常量（我希望返回基于“DateIndex”的值）时，解析器告诉我它正在执行 SELECT INTO。
What I have (simplified to the lowest form) is...
我所拥有的（简化为最低形式）是......
DECLARE DateIndex CONSTANT NUMBER(10,0) := 24;
BEGIN
SELECT DateIndex
FROM DUAL;
END;
Is it only possible to use constants when selecting into a table rather than returning results? Seems very odd.
是否只能在选择表格时使用常量而不是返回结果？看起来很奇怪。
Note that I do nothave write permissions on the database.
请注意，我对数据库没有写权限。
Many thanks for any assistance.
非常感谢您的帮助。
回答by f3lix
I prefer the following use of WITHand the DUAL table:
我更喜欢以下使用WITH和 DUAL 表：
WITH
const AS ( SELECT
3.14 AS pi,
1 AS one
FROM DUAL
)
SELECT * FROM sometable t, const
WHERE t.value = const.pi;
This lets you define constants before the use in a statement and the actual statement is not cluttered with subselects.
这使您可以在语句中使用之前定义常量，并且实际语句不会被子选择弄乱。
回答by Jens Schauder
Your code is not Oracle SQL but PL/SQL. In PL/SQL the result of a query has to be assigned to a variable. So you either have have to use a "select into clause" if you expect exactly one result, or you use a cursor.
您的代码不是 Oracle SQL，而是 PL/SQL。在 PL/SQL 中，查询的结果必须分配给一个变量。因此，如果您只期望一个结果，则必须使用“select into 子句”，或者使用游标。
In SQL on the other hand you can't declare a constant. You can sometimes work around this limitation by using an inline view like so
另一方面，在 SQL 中你不能声明一个常量。您有时可以通过使用像这样的内联视图来解决此限制
select something-complex-here, x.pi
from sometable, (
select 3.1415 as pi, 1234 other_constant
from dual
)
回答by jva
DECLARE
DateIndex CONSTANT NUMBER(10,0) := 24;
TargetVariable NUMBER;
BEGIN
SELECT DateIndex
INTO TargetVariable
FROM DUAL;
END;
回答by Chris Cameron-Mills
The error is not to do with your constant, the error is because you are using a SELECT statement without an INTO. A SELECT in an anonymous block is not the same as if you were to run a SELECT statement from SQL*Plus for example. It doesn't make sense to select something and do nothing with it, which is why it is prompting you for an into:
错误与您的常量无关，错误是因为您使用的是没有 INTO 的 SELECT 语句。例如，匿名块中的 SELECT 与从 SQL*Plus 运行 SELECT 语句不同。选择某些东西而不对其进行任何操作是没有意义的，这就是为什么它会提示您进入：
SELECT colA, colB
INTO variable_a, variable_b
WHERE something = DateIndex;
This of course assumes your query will only return one row. I have a feeling what you are really after is writing a function that contains your logic and returns a nested tabletype that you could select from.
这当然假设您的查询只会返回一行。我有一种感觉，您真正想要的是编写一个包含您的逻辑并返回您可以从中选择的嵌套表类型的函数。
EDIT: nevermind, I see that are not able to create type
编辑：没关系，我看到无法创建类型
回答by tuinstoel
When you want to return a result set you need a ref cursor.
当你想返回一个结果集时，你需要一个ref cursor。
create or replace procedure getlogs(p_sys_refcursor out sys_refcursor)
is
begin
open p_sys_refcursor for
select *
from log
where monthindex = 12 * to_char(sysdate,'yyyy') + to_char(sysdate,'mm');
end;
/
The value of sysdate will be determined only once at the start of the query, so there is no need for declaring some kind of constant inside sql or pl/sql.
sysdate 的值只会在查询开始时确定一次，因此不需要在 sql 或 pl/sql 中声明某种常量。
Edit1
编辑1
When you don't want to call a stored proc, do:
当您不想调用存储过程时，请执行以下操作：
select *
from log
where monthindex = 12 * to_char(sysdate,'yyyy') + to_char(sysdate,'mm');
