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屋！