oracle SQL Developer - 在查询的 where 子句中使用开始和结束日期变量
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4083579/
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
SQL Developer - Using start and end date variables in where clause of my query
提问by JanW
I have a query that I run everyday that requires a StartDateand EndDatevalue. The StartDateand EndDateused to be a manual input but I am trying to get away from that and calculate the StartDateand EndDateto be used in the query. I've developed code to capture the StartDateand EndDatein variables:
我有一个每天运行的查询，它需要一个StartDate和EndDate值。在StartDate和EndDate曾经是手动输入，但我想从脱身，计算StartDate和EndDate查询使用。我开发了代码来捕获StartDate和EndDate变量：
DECLARE
c_DateMask VARCHAR2(20) := 'DD-Mon-YYYY';
c_TimeMask VARCHAR2(20) := 'HH24:MI';
v_Month char(4) := 'Prev';
v_StartDate date;
v_EndDate date;
v_Environment char(7) := 'Prod';
BEGIN
if v_MONTH = 'Prev'
THEN
select TO_DATE ('01-' || TO_CHAR (ADD_MONTHS (SYSDATE, -1),'mon-yyyy')) into v_StartDate from dual;
select Last_day(TO_DATE('01-' || TO_CHAR (ADD_MONTHS (SYSDATE, -1),'mon-yyyy'))) into v_EndDate from dual;
ELSE
select TO_DATE ('01-' || TO_CHAR (ADD_MONTHS (SYSDATE, 0),'mon-yyyy')) into v_StartDate from dual;
CASE
WHEN v_Environment = 'Prod'
THEN
-- Production Environment --
select
to_char(sysdate, 'dd-Mon-yyyy ') ||
case
when to_char(sysdate, 'mi') between 00 and 20
then to_char(sysdate, 'hh24')-1||':58'||':00'
when to_char(sysdate, 'mi') between 21 and 40
then to_char(sysdate, ' hh24')||':18'||':00'
when to_char(sysdate, 'mi') between 41 and 60
then to_char(sysdate, ' hh24')||':38'||':00'
END
into v_EndDate from dual;
WHEN v_Environment = 'OldTest'
THEN
-- Test Environment --
select
to_char(sysdate, 'dd-Mon-yyyy ') ||
case
when to_char(sysdate, 'mi') between 10 and 30
then to_char(sysdate, 'hh24')||':08'||':00'
when to_char(sysdate, 'mi') between 31 and 50
then to_char(sysdate, ' hh24')||':28'||':00'
when to_char(sysdate, 'mi') between 51 and 60
then to_char(sysdate, ' hh24')||':48'||':00'
END
into v_EndDate from dual;
end case;
end if;
I then want to use the variables in my select statement below:
然后我想在下面的 select 语句中使用变量：
-----------------
/* KPI Figures */
-----------------
SELECT
SysDate as RunTime
, v_StartDate
, v_EndDate
, TTM_OFF_CONTRIBUTOR
, SUM(NVL(TTM_PER_OFF_FEE,0)) Fees
FROM [Table]
Where
TTM_PROCESSED_DATE >= v_StartDate
AND TTM_PROCESSED_DATE <= v_EndDate
group by SysDate, v_StartDate, v_EndDate, TTM_OFF_CONTRIBUTOR
END;
It all works up until when I try to use the variable values in the KPI Figures query. What am I missing?
直到我尝试在 KPI Figures 查询中使用变量值之前，这一切都有效。我错过了什么？
Update:
更新：
Regarding Phil's answer: I tried but it didn't work and I get the following error:
关于菲尔的回答：我试过了，但没有用，我收到以下错误：
PLS-00428: an INTO clause is expected in this SELECT statement.
PLS-00428: an INTO clause is expected in this SELECT statement.
I am sure I saw another response yesterday which is now gone relating to being able to assign multiple values to variables or something.
我确信我昨天看到了另一个响应，现在它不再涉及能够为变量或其他东西分配多个值。
Is that what I need and how would I do that?
那是我需要的吗，我该怎么做？
回答by Tony Andrews
The variables v_StartDate and v_EndDate are only in scope within the PL/SQL block where they are declared. It looks like you are then trying to use them outside the block in a separate query. To do that you will need to create SQL Developer bind variables outside the PL/SQL block like this:
变量 v_StartDate 和 v_EndDate 仅在声明它们的 PL/SQL 块内的范围内。看起来您正在尝试在单独的查询中的块外使用它们。为此，您需要在 PL/SQL 块之外创建 SQL Developer 绑定变量，如下所示：
var v_start_date varchar2(11)
var v_end_date varchar2(11)
Then reference these as bind variables in both the PL/SQL block and the SQL query:
然后在 PL/SQL 块和 SQL 查询中将它们作为绑定变量引用：
declare
...
begin
....
:v_start_date := '01-' || TO_CHAR (ADD_MONTHS (SYSDATE, -1), 'mon-yyyy');
:v_end_date := TO_CHAR(Last_day(TO_DATE('01-'
|| TO_CHAR (ADD_MONTHS (SYSDATE, -1),'mon-yyyy'))));
-- (NB No need to select from dual)
...
end;
SQL:
查询语句：
...
Where
TTM_PROCESSED_DATE >= TO_DATE(:v_StartDate)
AND TTM_PROCESSED_DATE <= TO_DATE(:v_EndDate)
Note that these variables cannot be declared with a type of DATE, so they need to be converted back to dates in the query (using the correct format mask).
请注意，这些变量不能用 DATE 类型声明，因此需要将它们转换回查询中的日期（使用正确的格式掩码）。
回答by Martin Schapendonk
I suggest you wrap the startdate/enddate in two functions (stored procedures)
我建议您将开始日期/结束日期包装在两个函数中（存储过程）
create or replace function get_startdate(p_month in varchar2, p_env in varchar2)
return date
is
l_return date;
begin
... logic goes here ...
return l_return;
end;
Similar for enddate.
结束日期类似。
Then use those functions in your query:
然后在您的查询中使用这些函数：
SELECT
SysDate as RunTime
, get_startdate('Prev', 'Prod')
, get_enddate('Prev', 'Prod')
, TTM_OFF_CONTRIBUTOR
, SUM(NVL(TTM_PER_OFF_FEE,0)) Fees
FROM [Table]
Where
TTM_PROCESSED_DATE >= get_startdate('Prev', 'Prod')
AND TTM_PROCESSED_DATE <= get_enddate('Prev', 'Prod')
group by SysDate, get_startdate('Prev', 'Prod'), get_enddate('Prev', 'Prod'), TTM_OFF_CONTRIBUTOR
Sidenote: please consider a more concise version to determine first and last day of a month. You also can just assign a value to a variable without using SELECT INTO, e.g.
旁注：请考虑一个更简洁的版本来确定一个月的第一天和最后一天。您也可以只为变量赋值而不使用 SELECT INTO，例如
startdate := trunc(sysdate, 'MM'); -- first day of current month
enddate := last_day(trunc(sysdate, 'MM')); -- last day of current month
To calculate with hours, you could add/subtract a number of minutes, that's more readable than all the to_date/to_char conversions you do, e.g.
要以小时计算，您可以添加/减去分钟数，这比您所做的所有 to_date/to_char 转换更具可读性，例如
enddate := trunc(sysdate, 'HH24');
case
when to_char(sysdate, 'mi') between 00 and 20 then enddate := enddate - 2/(24*60);
when to_char(sysdate, 'mi') between 21 and 40 then enddate := enddate + 18/(24*60);
when to_char(sysdate, 'mi') between 41 and 60 then enddate := enddate + 38/(24*60);
end;
Good luck, Martin
祝你好运，马丁
回答by Phil
I think the issue is with your query and aliases. Try
我认为问题在于您的查询和别名。尝试
SELECT
SysDate as RunTime
, v_StartDate AS StartDate
, v_EndDate AS EndDate
, TTM_OFF_CONTRIBUTOR
, SUM(NVL(TTM_PER_OFF_FEE,0)) Fees
FROM [Table]
Where
TTM_PROCESSED_DATE >= v_StartDate
AND TTM_PROCESSED_DATE <= v_EndDate
group by RunTime, StartDate, EndDate, TTM_OFF_CONTRIBUTOR
