游标for循环会产生不可预测的结果

Question

当我执行sql时，查询给出了正确的结果集。但是当我在游标for循环中使用它时它会显示&;Not a GROUP BY表达式&;

DECLARE
v_sum_of_strength number :=0;
BEGIN
for i in (select to_char(time,'MM-YYYY') "stick" ,count(*) usage from my_view group by to_char(time,'MM-YYYY')
order by substr(to_char(time,'MM-YYYY'),6), substr(to_char(time,'MM-YYYY'),1,2))
LOOP
select sum(strength) into v_sum_of_strength from batch where substr(to_char(startdate,'MM-YYYY'),1,2)<=(substr('04-2022',1,2)) AND substr(to_char(enddate,'MM-YYYY'),1,2)>=(substr('04-2022',1,2));
--dbms_output.put_line(i.candle);
dbms_output.put_line(v_sum_of_strength);
dbms_output.put_line('------------------');
END LOOP;
END;

请查看会话打印

DECLARE
v_sum_of_strength number :=0;
BEGIN
for i in (select to_char(time,'MM-YYYY') "stick" ,count(*) usage from my_view group by to_char(time,'MM-YYYY')
order by substr(to_char(time,'MM-YYYY'),6), substr(to_char(time,'MM-YYYY'),1,2))
LOOP
select sum(strength) into v_sum_of_strength from batch where substr(to_char(startdate,'MM-YYYY'),1,2)<=(substr('04-2022',1,2)) AND substr(to_char(enddate,'MM-YYYY'),1,2)>=(substr('04-2022',1,2));
--dbms_output.put_line(i.candle);
dbms_output.put_line(v_sum_of_strength);
dbms_output.put_line('------------------');
END LOOP;
END;
Error report -
ORA-00979: not a GROUP BY expression
ORA-06512: at line 4
ORA-06512: at line 4
00979. 00000 -  "not a GROUP BY expression"
*Cause:    
*Action:

Answer 1

不要尝试使用子字符串来过滤日期。相反，您可以在GROUP BY和ORDER BY子句中使用TRUNC(time, 'MM')，它允许您简化顺序(并将正确地跨世纪边界排序):

DECLARE
v_sum_of_strength NUMBER;
BEGIN
FOR i IN (
SELECT TRUNC(time,'MM') AS "stick",
COUNT(*) AS usage
FROM   my_view
GROUP BY TRUNC(time,'MM')
ORDER BY TRUNC(time,'MM')
)
LOOP
SELECT sum(strength)
INTO   v_sum_of_strength
FROM   batch
-- This is what your code does but I'm not sure it is what you intend.
-- WHERE  EXTRACT(MONTH FROM startdate) <= 4
-- AND    EXTRACT(MONTH FROM enddate)   >= 4
-- This is, I think, what you intend.
WHERE  startdate <  DATE '2022-05-01'
AND    enddate   >= DATE '2022-04-01';
--dbms_output.put_line(i.candle);
dbms_output.put_line(v_sum_of_strength);
dbms_output.put_line('------------------');
END LOOP;
END;
/

小提琴

Answer 2

适合我:

SQL> create table my_view as select sysdate time from dual;
Table created.
SQL> create table batch as select 100 strength, sysdate startdate, sysdate enddate from dual;
Table created.
SQL> set serveroutput on
SQL> DECLARE
2    v_sum_of_strength number :=0;
3  BEGIN
4    for i in (select to_char(time,'MM-YYYY') stick ,
5                     count(*) usage
6              from my_view
7              group by to_char(time,'MM-YYYY')
8              order by substr(to_char(time,'MM-YYYY'),6),
9                       substr(to_char(time,'MM-YYYY'),1,2)
10             )
11    LOOP
12      select sum(strength)
13        into v_sum_of_strength
14        from batch
15        where substr(to_char(startdate,'MM-YYYY'),1,2) <= (substr('04-2022',1,2))
16          AND substr(to_char(enddate,'MM-YYYY'),1,2)   >= (substr('04-2022',1,2));
17      --dbms_output.put_line(i.candle);
18      dbms_output.put_line(v_sum_of_strength);
19      dbms_output.put_line('------------------');
20    END LOOP;
21  END;
22  /
100
------------------
PL/SQL procedure successfully completed.
SQL>

---

请像我一样复制/粘贴你的会话来演示错误。