平均融資利率求法及ORACLE語法解析

1.首先創造一個名為 PJRZCB_YD_RESULT_SET 的 OBJECT 類型

??注意其中不可以使用保留字段作為列名 , 如DATE 等, 否則可以創建 , 但無法被后續的 TABLE類型引用

CREATE OR REPLACE TYPE ZMDM.PJRZCB_YD_RESULT_SET AS OBJECT (
  month VARCHAR2(10000),
  DATE_STR  VARCHAR2(1000),
  value VARCHAR2(1000)
);

此處創建的是行級別的類型

2.創建一個名為 多行(表類型)的定義

CREATE OR REPLACE TYPE ZMDM.PJRZCB_YD_RESULT_SET_TABLE AS TABLE OF ZMDM.PJRZCB_YD_RESULT_SET;

3.創建函數 PJRZCB_YD_FUNC_TEST
??其中如果有使用到DML語句 , 則需要使用IS PRAGMA AUTONOMOUS_TRANSACTION;

CREATE OR REPLACE FUNCTION ZMDM.PJRZCB_YD_FUNC_TEST
  RETURN ZMDM.PJRZCB_YD_RESULT_SET_TABLE PIPELINED
IS PRAGMA AUTONOMOUS_TRANSACTION;
  IN_MONTH_STR  VARCHAR2(7);
  IN_DATE_STR VARCHAR2(10);
  IN_MONTH_NUM    NUMBER;
  MONTH_STR  VARCHAR2(7);
  DATE_STR VARCHAR2(10);
  MONTH_NUM    NUMBER;
  v_row ZMDM.PJRZCB_YD_RESULT_SET;
  CURSOR cur_months IS
 SELECT A.end_timestamp FROM (
  SELECT 
      TO_TIMESTAMP(
        CASE 
          WHEN ADD_MONTHS(TRUNC(SYSDATE, 'MM'), 1 - ROWNUM) = TRUNC(SYSDATE, 'MM') 
          THEN TO_CHAR(SYSDATE - 1, 'YYYY-MM-DD') || ' 00:00:00'
          ELSE TO_CHAR(LAST_DAY(ADD_MONTHS(TRUNC(SYSDATE, 'MM'), 1 - ROWNUM)), 'YYYY-MM-DD') || ' 00:00:00'
        END,
        'YYYY-MM-DD HH24:MI:SS'
      ) AS end_timestamp
    FROM DUAL 
    CONNECT BY ROWNUM <= 12)A 
    LEFT JOIN DP_PJRZCB_LASTYEAR B 
    ON TO_CHAR(A.end_timestamp , 'YYYY-MM-DD') = B.DATE_STR
    WHERE B.DATE_STR IS NULL ;

BEGIN
  FOR rec IN cur_months LOOP
    -- 用 SELECT INTO 的方式賦值
 
  SELECT
        TO_CHAR(rec.end_timestamp, 'YYYY-MM') AS MONTH_STR, 
        TO_CHAR(rec.end_timestamp, 'YYYY-MM-DD') AS DATE_STR  ,
        NVL((SUM((a.AMOUNT - (SELECT
            NVL(SUM(gci.amount),
            0)
        FROM
            GDT_CONTEXEC_INFO@FSS_DBLINK gci
        WHERE
            gci.CONTRACT_ID = a.CONTRACTID
            AND gci.CONTEXEC_TYPE in (1,6)
            AND gci.buss_date <= rec.end_timestamp  )) *    (SELECT
            vcw.rate
        FROM
            vw_contract_with_rates@FSS_DBLINK vcw
        WHERE
            vcw.CONTRACTID = a.CONTRACTID
            AND vcw.EFFECT_DATE <= rec.end_timestamp   ))/100)    / NVL(SUM(a.AMOUNT - (SELECT
            NVL(SUM(gci.amount),
            0)
        FROM
            GDT_CONTEXEC_INFO@FSS_DBLINK gci
        WHERE
            gci.CONTRACT_ID = a.CONTRACTID
            AND gci.CONTEXEC_TYPE in (1,6)
            AND gci.buss_date <= rec.end_timestamp  )),
        0)*100,
        0) AS MONTH_NUM  INTO   IN_MONTH_STR ,IN_DATE_STR  , IN_MONTH_NUM
    FROM
        vw_gdt_contract_query_mater@FSS_DBLINK a
    WHERE
        a.state != 8
        AND a.CLTNO IN(2000,2001,5792,5797,2002,5895,2003,2031,2025,2023,2022,2068,1513,6905,6912,5251,2015,2004,2556,2018,2008,
      1000,6904,6903,6909,6906,6910,8805,8701,1016,2542,2543,2541,2641,1505,1511,1512,1019,2037,2009,9803,1002,0024,2016,6501,1021,
      2713,6502,1012,2546,2583,2577,2586,2584,2585,2574,2580,2886,3551,2507,2591,2606,2604,2605,2603,2611,2602,2707,2608,2610,2612,
      2616,2617,2621,2620,2609,2622,1014,0802,1018,2209,1022,2725,2516,0128,0133)
        AND a.BUSSVARIETY NOT IN ('CLMS02'    ,
            'CLMS10', 'CLMS17'
        )
        AND a.assbankname NOT LIKE '%中煤財務%'
        AND a.startDate <= rec.end_timestamp
        AND a.endDate >= rec.end_timestamp  ;
       
        
       INSERT INTO DP_PJRZCB_LASTYEAR (MONTH_STR, DATE_STR, MONTH_NUM) VALUES (IN_MONTH_STR ,IN_DATE_STR  , IN_MONTH_NUM); 
       
    COMMIT; 
      
   END LOOP;
         

   FOR rec_return IN (
	SELECT MONTH_STR,DATE_STR,MONTH_NUM 
	FROM (
  	SELECT 
      TO_TIMESTAMP(
        CASE 
          WHEN ADD_MONTHS(TRUNC(SYSDATE, 'MM'), 1 - ROWNUM) = TRUNC(SYSDATE, 'MM') 
          THEN TO_CHAR(SYSDATE - 1, 'YYYY-MM-DD') || ' 00:00:00'
          ELSE TO_CHAR(LAST_DAY(ADD_MONTHS(TRUNC(SYSDATE, 'MM'), 1 - ROWNUM)), 'YYYY-MM-DD') || ' 00:00:00'
        END,
        'YYYY-MM-DD HH24:MI:SS'
      ) AS end_timestamp
    FROM DUAL 
    CONNECT BY ROWNUM <= 12) A 
    LEFT JOIN  (SELECT MONTH_STR, DATE_STR, MONTH_NUM FROM DP_PJRZCB_LASTYEAR) B 
    ON  TO_CHAR(end_timestamp , 'YYYY-MM-DD') = B.DATE_STR
    WHERE B.DATE_STR IS NOT NULL 
    ) LOOP
        -- 每行數據直接通過PIPE ROW返回
      PIPE ROW( ZMDM.PJRZCB_YD_RESULT_SET( rec_return.MONTH_STR, rec_return.DATE_STR,  rec_return.MONTH_NUM ) ); 
     
    END LOOP;
  RETURN;
END;

.

.