Выборка данных из вложенной таблицы в курсор

Вы можете сделать это, используя объявленные типы объектов и таблицы объявленных типов объектов.

Начнем с создания таблиц и данных:

CREATE TABLE xyz
( xyz_id VARCHAR2(3)
, p_type VARCHAR2(20)
, p_amount NUMBER
);

INSERT INTO xyz VALUES ('p_1','p_id','100000');
INSERT INTO xyz VALUES ('p_2','p_id,t_status','100');

CREATE TABLE pqr
( t_id NUMBER
, p_id VARCHAR2(2)
, amount NUMBER
, t_status VARCHAR2(5)
);

INSERT INTO pqr VALUES (1,'E1',100,'open');
INSERT INTO pqr VALUES (2,'E2',200,'open');
INSERT INTO pqr VALUES (3,'E1',200,'close');
INSERT INTO pqr VALUES (4,'E2',300,'open');
INSERT INTO pqr VALUES (5,'E1',100,'close');

Затем создайте объявленные типы объектов:

CREATE TYPE pqr_tid_sumamt_rec IS OBJECT
( t_id NUMBER
, sum_amount NUMBER
);

CREATE TYPE pqr_tid_sumamt_tab IS TABLE OF pqr_tid_sumamt_rec;

CREATE TYPE xyztab_ptstab_rec IS OBJECT
( xyz_id VARCHAR2(3)
, p_type VARCHAR2(20)
, p_amount NUMBER
, p_pqr_tid_sumamt_tab pqr_tid_sumamt_tab
);

CREATE TYPE xyztab_ptstab_tab AS TABLE OF xyztab_ptstab_rec;

Теперь мы создадим функцию, которая будет возвращать sys_refcursor с вложенной таблицей:

CREATE OR REPLACE
FUNCTION xyz_pqr_refcursor 
    RETURN sys_refcursor
IS
    TYPE querycursor IS REF CURSOR;
    v_ref querycursor;
    v_query querycursor;
    v_t_id   pqr.t_id%TYPE;
    v_total         NUMBER (38);
    CURSOR varcursor IS 
        SELECT xyz_id,p_type,p_amount    
        FROM xyz;
    v_temp          VARCHAR2 (4000);
    v_temp1         VARCHAR2 (200);
    tab_pqr_tid_sumamt pqr_tid_sumamt_tab := pqr_tid_sumamt_tab();
    tab_xyztab_ptstab  xyztab_ptstab_tab  := xyztab_ptstab_tab();
    ret_cur sys_refcursor;
BEGIN
    FOR varcursor_rec IN varcursor
    loop
        v_temp1 := varcursor_rec.p_type; --p_type contains column name of the pqr TABLE
        v_temp :=
            'SELECT T_ID,SUM(AMOUNT) OVER (PARTITION BY '     
            || v_temp1     
            || ' ORDER BY '     
            || v_temp1     
            || ') total from pqr';
--        dbms_output.put_line('v_temp: '||v_temp);
        OPEN v_ref FOR v_temp;  
        loop
            fetch v_ref INTO v_t_id,v_total;
            exit WHEN v_ref%notfound;

            IF varcursor_rec.p_amount < v_total THEN
                tab_pqr_tid_sumamt.EXTEND;
                -- cast the cursor values into the record type to be added to the table type
                tab_pqr_tid_sumamt( tab_pqr_tid_sumamt.count ) := pqr_tid_sumamt_rec(v_t_id, v_total);
            END IF;
        END loop;
        tab_xyztab_ptstab.EXTEND;
        -- cast the cursor values and nested table type to the record type and add to the table type
        tab_xyztab_ptstab(tab_xyztab_ptstab.count) := xyztab_ptstab_rec (varcursor_rec.xyz_id, varcursor_rec.p_type, varcursor_rec.p_amount, tab_pqr_tid_sumamt);
    END loop;

    --open the return refcursor
    OPEN ret_cur FOR 
        SELECT xyztab_ptstab_rec(xyz_id, p_type, p_amount, p_pqr_tid_sumamt_tab) FROM TABLE( tab_xyztab_ptstab );

    -- return the refcursor
    RETURN ret_cur;
END xyz_pqr_refcursor;

Теперь давайте проверим функцию:

DECLARE
    test_cur      sys_refcursor;
    test_rec      xyztab_ptstab_rec;
    test_pqrtab   pqr_tid_sumamt_tab;
    counter       int := 1;
BEGIN
    test_cur := xyz_pqr_refcursor();
    loop
        fetch test_cur INTO test_rec;
        exit WHEN test_cur%notfound;
        dbms_output.put_line('test_rec('||counter||') -- xyz_id: '||test_rec.xyz_id||', p_type: '||test_rec.p_type||', p_amount: '||test_rec.p_amount);
        test_pqrtab := test_rec.p_pqr_tid_sumamt_tab;
        FOR j IN 1 .. test_pqrtab.count
        loop
            dbms_output.put_line('.... test_pqrtab('||j||') - t_id: '||test_pqrtab(j).t_id||', sumamt: '||test_pqrtab(j).sum_amount);
        END loop;
        counter := counter + 1;
    END loop;
EXCEPTION 
    WHEN others THEN dbms_output.put_line('whoops: '||sqlerrm);
END;

Результаты теста:

test_rec(1) -- xyz_id: p_1, p_type: p_id, p_amount: 100000
test_rec(2) -- xyz_id: p_2, p_type: p_id,t_status, p_amount: 100
.... test_pqrtab(1) - t_id: 3, sumamt: 300
.... test_pqrtab(2) - t_id: 5, sumamt: 300
.... test_pqrtab(3) - t_id: 4, sumamt: 500
.... test_pqrtab(4) - t_id: 2, sumamt: 500

В качестве альтернативы вы можете просто вернуть объявленный тип таблицы вместо sys_refcursor.

Надеюсь, это поможет.