Oracle SQL查询中的常量 [英] Constants in Oracle SQL query
问题描述
我是Oracle的新手(虽然熟悉SQL),并且必须编写一个相当复杂的查询,其中从当前日期派生的值多次使用。
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.
然而,当我尝试在随后的SELECT中使用我的DateIndex常量时,语句(我希望返回基于DateIndex的值),解析器告诉我它正在使用SELECT INTO。
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.
我有什么(简化为最低的形式)is ...
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 not have write permissions on the database.
感谢您的帮助。
推荐答案
您的代码不是Oracle SQL,而是PL / SQL。在PL / SQL中,查询的结果必须分配给变量。所以你必须使用一个select into子句,如果你期望一个结果,或者你使用游标。
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.
在SQL中,另一方面,你可以' t声明一个常数。有时可以使用内联视图来解决此限制。
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
select something-complex-here, x.pi
from sometable, (
select 3.1415 as pi, 1234 other_constant
from dual
)
这篇关于Oracle SQL查询中的常量的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!