存储过程在FM中的使用

create or replace procedure p_test(v_cstm_no in CHAR, v_accs OUT  sys_refcursor  )

IS

BEGIN

  OPEN v_accs FOR

    SELECT 1 ITEM_NO, --4.日均存款余额,按月。  项目年日均的季度日军，取over_year_bal/over_year_days就可

           T1.DTL_MON,

           T1.MONTH_BAL,

           T1.DAYS_IN_MONTH,

           SUM(T1.MONTH_BAL) OVER(PARTITION BY T1.YEAR_KEY, T1.QUARTER_KEY ORDER BY T1.DTL_MON) OVER_MONTH_BAL, --逐季度日均余额之和

           SUM(T1.DAYS_IN_MONTH) OVER(PARTITION BY T1.YEAR_KEY, T1.QUARTER_KEY ORDER BY T1.DTL_MON) OVER_MONTH_DAYS, --季度天数

           SUM(T1.MONTH_BAL) OVER(PARTITION BY T1.YEAR_KEY ORDER BY T1.DTL_MON) OVER_YEAR_BAL, --年日均余额之和

           SUM(T1.DAYS_IN_MONTH) OVER(PARTITION BY T1.YEAR_KEY ORDER BY T1.DTL_MON) OVER_YEAR_DAYS, --年天数

           SYSDATE

      FROM (SELECT SUM(A.CR_CRT_BAL) MONTH_BAL,

                   A.DTL_MON,

                   SUBSTR(A.DTL_MON, 1, 4) YEAR_KEY,

                   B.DAYS_IN_MONTH,

                   B.QUARTER_KEY

              FROM T_GNL_DTL_ERP A,

                   (SELECT DISTINCT MONTH_KEY, DAYS_IN_MONTH, QUARTER_KEY

                      FROM D_TIME) B --取得月份以及该月的天数

             WHERE A.DTL_MON = B.MONTH_KEY

               AND A.ITM_NO IN ('2011', '2002')

             GROUP BY A.DTL_MON, B.DAYS_IN_MONTH, B.QUARTER_KEY) T1;

end p_test;