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_TESTRETURN 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 ISSELECT 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_timestampFROM DUAL CONNECT BY ROWNUM <= 12)A LEFT JOIN DP_PJRZCB_LASTYEAR B ON TO_CHAR(A.end_timestamp , 'YYYY-MM-DD') = B.DATE_STRWHERE B.DATE_STR IS NULL ;BEGINFOR rec IN cur_months LOOP-- 用 SELECT INTO 的方式赋值SELECTTO_CHAR(rec.end_timestamp, 'YYYY-MM') AS MONTH_STR, TO_CHAR(rec.end_timestamp, 'YYYY-MM-DD') AS DATE_STR ,NVL((SUM((a.AMOUNT - (SELECTNVL(SUM(gci.amount),0)FROMGDT_CONTEXEC_INFO@FSS_DBLINK gciWHEREgci.CONTRACT_ID = a.CONTRACTIDAND gci.CONTEXEC_TYPE in (1,6)AND gci.buss_date <= rec.end_timestamp )) * (SELECTvcw.rateFROMvw_contract_with_rates@FSS_DBLINK vcwWHEREvcw.CONTRACTID = a.CONTRACTIDAND vcw.EFFECT_DATE <= rec.end_timestamp ))/100) / NVL(SUM(a.AMOUNT - (SELECTNVL(SUM(gci.amount),0)FROMGDT_CONTEXEC_INFO@FSS_DBLINK gciWHEREgci.CONTRACT_ID = a.CONTRACTIDAND 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_NUMFROMvw_gdt_contract_query_mater@FSS_DBLINK aWHEREa.state != 8AND 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_timestampAND 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_timestampFROM 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_STRWHERE 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;.
.