如何在更换静态查询时在PLSQL中创建过程/功能

Question

我写了以下静态查询

SELECT (
        SUM(CASE 
                WHEN DATE BETWEEN add_months('22-MAR-17', 0) - 13
                        AND add_months('22-MAR-17', 0) - 7 THEN VOLUME ELSE 0 END ) ) LAST_WEEK_Volume
                    ,(
                        SUM(CASE 
                                WHEN DATE BETWEEN add_months('22-MAR-17', 0) - 6
                                        AND add_months('22-MAR-17', 0)
                                    THEN VOLUME
                                ELSE 0
                                END)
                        ) THIS_WEEK_Volume FROM abc
        )
        )

问题statemet - 我想为last_week_volume和this_week_volume创建功能，然后传递列。您可以添加动态查询。因此，通过它可以计算上周的《旧数据》和本周的旧数据

Answer 1

尝试像这样

--creating function
CREATE OR REPLACE FUNCTION Calculate_Volume(Date_Val DATE,Start NUMBER, End Number)
   RETURN NUMBER
   IS Sum_Value NUMBER(10,2);
BEGIN
   Sum_Value := 0;
   SELECT (SUM (CASE WHERE DATE BETWEEN add_months(Date_Val ,0)- Start AND add_months(Date_Val ,0)- End then VOLUME ELSE 0 END)) INTO Sum_Value 
   FROM ABC
   RETURN Sum_Value;
END Calculate_Volume;
--Calling the function:
SELECT Calculate_Volume('22-MAR-17', 13, 7) AS LAST_WEEK_Volume, Calculate_Volume('22-MAR-17', 6, 0) AS THIS_WEEK_Volume,
FROM ABC;