Oracle SQL显示一个变量除以另一个变量的结果

Question

我需要计算每周的YTDGROSS_AMOUNT的平均值

在下面的脚本中，代码的第一部分获得周数。代码的第二部分获取同一时间段的YTD总和

我如何编写SQL以便我可以检索YtdTotal/TotalWeeks的实际值

?SELECT YtdTotal/TotalWeeks FROM DUAL;不工作

-- STORES NUMBER OF WEEKS SINCE BEGINING OF YEAR INTO THE VARIABLE, TotalWeeks
DECLARE
TotalWeeks NUMBER;
BEGIN
SELECT to_number(to_char(sysdate, 'WW')) - to_number(to_char(trunc(sysdate, 'year'),'WW'))
INTO TotalWeeks
FROM DUAL
-- (retrieves number of weeks since beginning of year)
END
-- STORES THE SUM OF GROSS)AMOUNT FOR THE WEEKS CALCULATED ABOVE - INTO THE 
DECLARE
VARIABLE, YtdTotal
BEGIN
SELECT SUM(GROSS_AMOUNT) 
INTO YtdTotal
FROM PARENTS
WHERE process_date BETWEEN
(next_day(TRUNC(sysdate, 'year'),'SUN'))
AND
(next_day(TRUNC(sysdate),'SAT')-7);
END;

Answer 1

不要使用两个独立的匿名PL/SQL块-将它们合并为一个!

如果你想返回结果，那么-而不是一个匿名PL/SQL块-创建一个函数.

CREATE OR REPLACE FUNCTION f_test
return NUMBER
IS
totalweeks  NUMBER;
ytdtotal    NUMBER;
result      NUMBER;
BEGIN
-- STORES NUMBER OF WEEKS SINCE BEGINING OF YEAR INTO THE VARIABLE, TotalWeeks
SELECT   TO_NUMBER (TO_CHAR (SYSDATE, 'WW'))
- TO_NUMBER (TO_CHAR (TRUNC (SYSDATE, 'year'), 'WW'))
INTO totalweeks
FROM DUAL;
-- (retrieves number of weeks since beginning of year)
-- STORES THE SUM OF GROSS)AMOUNT FOR THE WEEKS CALCULATED ABOVE - INTO THE
SELECT SUM (gross_amount)
INTO ytdtotal
FROM parents
WHERE process_date BETWEEN (NEXT_DAY (TRUNC (SYSDATE, 'year'), 'SUN'))
AND (NEXT_DAY (TRUNC (SYSDATE), 'SAT') - 7);
-- the final result
result := ytdtogal / totalweeks;
RETURN result;
END;
/

使用

select f_test from dual;