oracle - 如何在游标循环内使用批量收集追加表类型对象中的记录

Question

我正在尝试使用游标循环内的批量收集将记录附加到表类型对象中。但我得到了对象中添加的最后一条记录。我认为它被覆盖在前一条记录上。如何在循环时附加所有记录而不是每次都覆盖？

我的代码:

create or replace FUNCTION GET_DEM_CONTAINER_LIST RETURN DEM_CNT_TBL_TYPE AS
    DEM_CNT_LIST            DEM_CNT_TBL_TYPE :=DEM_CNT_TBL_TYPE();
    P_FREE_DAYS             NUMBER;
    P_DEM_REQ_FLAG          CHAR(1);
    P_STORERKEY             VARCHAR2(15);
    P_TOID                  VARCHAR2(30);
    P_SKU                   VARCHAR2(20);
    P_RECVD_DATE            DATE;
    P_DEM_DATE              DATE;
    P_LOT                   VARCHAR2(10);
    P_DEM_DAYS              NUMBER;
    P_DIFF_DAYS             NUMBER;
    
    CURSOR C1 IS        SELECT CCM_FREE_STORE_DAYS,CCM_DEM_BILL_REQUIRED,W.STORERKEY,TOID,SKU,RECVD_DATE,DEM_DATE,LOT
    FROM                CUSTOMER_CONTRACT_MASTER,WEB_BAL_CONTAINER_LIST W
    WHERE               W.STORERKEY=CCM_STORERKEY
    AND                 QTY_BAL>0
    AND                 SKU LIKE 'CNT%'
    ORDER BY            RECVD_DATE;
BEGIN
    OPEN C1;
    LOOP
        FETCH C1 INTO P_FREE_DAYS,P_DEM_REQ_FLAG,P_STORERKEY,P_TOID,P_SKU,P_RECVD_DATE,P_DEM_DATE,P_LOT;
        EXIT WHEN C1%NOTFOUND;
        P_DIFF_DAYS :=(TRUNC(SYSDATE)-TRUNC(P_DEM_DATE))+1;
        IF P_DIFF_DAYS>P_FREE_DAYS THEN
            DEM_CNT_LIST.EXTEND();
            P_DEM_DAYS  :=P_DIFF_DAYS-P_FREE_DAYS;
            --DBMS_OUTPUT.PUT_LINE(P_TOID||','||P_LOT||','||P_DEM_DATE||','||P_FREE_DAYS||','||P_DEM_DAYS);
            SELECT DEM_CNT_OBJ_TYPE(P_TOID,P_LOT,P_FREE_DAYS,P_DEM_DAYS)
            BULK COLLECT INTO    DEM_CNT_LIST
            FROM    (SELECT P_TOID,P_LOT,P_FREE_DAYS,P_DEM_DAYS FROM DUAL);  
        END IF;
    END LOOP;
    CLOSE C1; 
    RETURN DEM_CNT_LIST;
END;

Answer 1

此查询完全覆盖之前存储的 DEM_CNT_LIST 值:

SELECT DEM_CNT_OBJ_TYPE(P_TOID,P_LOT,P_FREE_DAYS,P_DEM_DAYS)
BULK COLLECT INTO    DEM_CNT_LIST
FROM    (SELECT P_TOID,P_LOT,P_FREE_DAYS,P_DEM_DAYS FROM DUAL);  

替换为:

DEM_CNT_LIST(DEM_CNT_LIST.LAST) := DEM_CNT_OBJ_TYPE(P_TOID,P_LOT,P_FREE_DAYS,P_DEM_DAYS);