Как открыть несколько курсоров с в цикле FETCH главного курсора?

Question

Пытаюсь прочитать курсор в процедуре, возвращаемый из другой процедуры, и хочу записать в несколько OUT курсоров, перебирая данные главного курсора:

TYPE myCursorType IS REF CURSOR;
PROCEDURE getAllDetails (
            customerNumber      IN customer.customer_number%TYPE,
            accountDetailsCur   OUT pkgaccount.curAccountDetailsType,
            otherDetailsCur     OUT myCursorType) IS
    localAccountDetails   pkgaccount.curInvestmentAccount;
    outcfunds             curInvestmentAccount;
    accountDetails        pkgaccount.curAccountDetailsType%rowtype;
BEGIN
    pkgaccount.accountDetails(customerNumber,localAccountDetails);
    LOOP
        FETCH localAccountDetails INTO accountDetails;
        EXIT WHEN localAccountDetails%notfound;
        -- здесь нужно вернуть также accountDetailsCur, 
        -- перед этим надо пройтись по нему циклом и вернуть оставшиеся данные.
        -- На основе номера счета надо вернуть еще этот курсор:
        OPEN otherDetailsCur FOR
            SELECT col1, col2
            FROM othertable
            WHERE accountnumber = localAccountDetails.accountnumber;
    END LOOP;
END;

Так как открываю курсор в цикле, он вернет только данные последней строки курсора localAccountDetails. Не уверен, как тут использовать BUILK COLLECT, чтобы собрать все записи и выполнить цикл по ним.

Также не знаю, как вернуть accountDetailsCur как данные OUT, чтобы в цикле можно было получить из него оставшиеся данные.

_{Свободный перевод вопроса How to open multiple cursors with main cursor data от участника @Dyapa Srikanth}

Answer 1

Можно поместить один курсор в другой с помощью CURSOR выражения. Запрос с ним выглядит так:

select 1 id, cursor (
    select 99 val, cursor (
        select 'abc' str from dual) nestcur2 from dual) nestcur1 from dual;

1   {<VAL=99,NESTCUR2={<STR=abc>,}>,}

Таким образом, можно сделать что-то вроде этого:

create table item (pk primary key) as 
    select level pk from dual connect by level <= 3;
  
create table part (pk, fk) as 
    select r.lvl, item.pk from item, lateral ( 
        select level lvl from dual connect by level <= pk) r;

declare
    cursor itemcur is 
    select pk, cursor (
        select * from part p
        where p.fk = i.pk)
    from item i;
    pk int;
    partcur sys_refcursor;
    type partarr is table of part%rowtype index by pls_integer;
    parts partarr;
begin
    open itemcur;
    loop
        fetch itemcur into pk, partcur;
        exit when itemcur%notfound;
        fetch partcur bulk collect into parts;
        dbms_output.put_line ('Fetched item '||pk||' with part count '||parts.count);
    end loop;
    close itemcur;
end;
/
Fetched item 1 with part count 1
Fetched item 2 with part count 2
Fetched item 3 with part count 3

Но заметьте, что это означает, что так создаются вложенные циклы JOIN. Если только действительно не нужно контролировать, сколько записей код вызова получит из вложенного курсора, то лучше сделать это обычным JOIN.

Можно агрегировать записи подсоединяемой таблицы в JSON или во вложенную таблицу, если надо получить только одну запись из главной таблицы, например:

select i.pk, json_arrayagg (json_object (p.*))
from item i
join part p on p.fk = i.pk
group by i.pk;

        PK PARTS                                                           
---------- ----------------------------------------------------------------
         1 [{"PK":1,"FK":1}]                                               
         2 [{"PK":1,"FK":2},{"PK":2,"FK":2}]                               
         3 [{"PK":1,"FK":3},{"PK":3,"FK":3},{"PK":2,"FK":3}]

_{Свободный перевод ответа от участника @Chris Saxon}

БЛОГ НА HUSL

Как открыть несколько курсоров с в цикле FETCH главного курсора?

Ответы (1 шт):