Как объявить переменную с типом данных для слабо типизированного SYS_REFCURSOR?

Question

В коде ниже не могу объявить переменную с типом данных %ROWTYPE базовой таблицы для fetch-into, т.к. запрос для SYS_REFCURSOR объединяет две таблицы, а также содержит несколько функций, вызываемых с колонками двух базовых таблиц. То есть, не могу объявить как L_RECORD T%ROWTYPE.
CREATE or REPLACE PROCEDURE CAPITALEXTRACT(p_rs OUT SYS_REFCURSOR) AS
BEGIN
    OPEN p_rs for 
        select t.*,tminusone.*, f(t.cash), g(t.cash) 
        FROM T t, TMINUSONE tminusone
        where t.ticket=tminusone.ticket;
END CAPITALEXTRACT;
/
DECLARE
    P_RS SYS_REFCURSOR;
    L_RECORD P_RS%ROWTYPE;
BEGIN
    CAPITALEXTRACT(P_RS => P_RS);
    OPEN P_RS;
    LOOP
        BEGIN
            FETCH P_RS INTO L_RECORD;
            EXIT WHEN P_RS%NOTFOUND;
            ...
        EXCEPTION WHEN OTHERS THEN
        ...
        END;
    END LOOP;
    CLOSE P_RS;
END;

Разумеется, нет желания создавать для этого статическую таблицу R со столбцами, возвращаемыми в SYS_REFCURSOR, а затем объявлять переменную как L_RECORD R%ROWTYPE.
Так как же объявить переменную с %ROWTYPE для слабо типизированного SYS_REFCURSOR?

Свободный перевод вопроса how to declare %ROWTYPE of a variable that is a weakly typed SYS_REFCURSOR? от участника @Vishal Saxena

0xdb · Answer

Разумеется, нет желания создавать для этого статическую таблицу R со столбцами, возвращаемыми в SYS_REFCURSOR

Таблицу создавать действительно не стоит, но создать предсставление, будет вполнее уместное решение. Оно позволит создать тип данных для переменной с заранее заданной структурой. Кроме того, позволит создать тип курсора со строгой типизацией.
create or replace view view1 as
    select e.*, department_name, round (salary/30) perday  
    from hr.employees e
    join hr.departments d on d.department_id = e.department_id
/

Пример для задачи как в вопросе:
declare
    type refcursor1 is ref cursor return view1%rowtype;
    subtype record1 is view1%rowtype;
    rc refcursor1; 
    rec record1;    
    procedure openCursor (rc out refcursor1) is
    begin
        open rc for select * from view1
        where 'some conditions here' != 'x';
    end;
begin 
    openCursor (rc);
    fetch rc into rec;
    dbms_output.put_line ('row '||rc%rowcount||': '||rec.last_name||','||rec.first_name||
        ' from '||rec.department_name||' has salary per day '||rec.perday);
    close rc;
end;
/

row 1: Whalen,Jennifer from Administration has salary per day 147

0xdb · Answer

Короткий ответ - так сделать не получится. Нужно определить переменную для каждого возвращаемого столбца.
DECLARE
    P_RS SYS_REFCURSOR;
    L_T_COL1 T.COL1%TYPE;
    L_T_COL1 T.COL2%TYPE;
    ...

А затем выбирать в лист этих переменных:
FETCH P_RS INTO L_T_COL1, L_T_COL2, ... ;

Это довольно кропотливо, но вполне выполнимо до тех пор, пока достоверно известно, что курсор вернёт. Использование T.* в процедуре делает эту процедуру "хрупкой", так как добавление столбца в таблицу сломает код, который построен на том, что известно, какие столбцы, и в каком порядке они находятся.
Также код может сломатся, если его запускать в разных окружениях. Если структура таблиц не изменяется в строгой последовательности, то порядок следования столбцов в разных окружениях может отличаться.

Начиная с 11g можно использовать пакет DBMS_SQL для преобразования sys_refcursor в DBMS_SQL курсор, который можно опросить его для определения столбцов курсора. В качестве примера, вывод значения каждого столбца в новой строке, с именем столбца:
declare
    rc sys_refcursor;
    cols number;
    ds dbms_sql.desc_tab;
    cid integer;
    var varchar2 (4000);
    procedure openCursor (rc out sys_refcursor) is
    begin
        open rc for select 1 id, 'text' txt, sysdate now from dual;
    end;
begin
    openCursor (rc => rc);
    cid := dbms_sql.to_cursor_number (rc);
    dbms_sql.describe_columns (c=>cid, col_cnt=>cols, desc_t=>ds);
    for i in 1..cols loop
        dbms_sql.define_column (cid, i, var, 4000);
    end loop;
    while dbms_sql.fetch_rows (cid) > 0 loop
        for i in 1..cols loop
            dbms_sql.column_value (cid, i, var);
            dbms_output.put_line ('row '||
                dbms_sql.last_row_count||': '||ds(i).col_name||'='||var);
        end loop;
    end loop;
    dbms_sql.close_cursor (cid);
end;
/
row 1: ID=1
row 1: TXT=text
row 1: NOW=2020-07-24 17:12:49

Для краткости каждый тип данных конвертировал в символьный, так как все равно хочу ее только вывести. В документации можно найти более практические примеры использования.
Но всё же, если ожидается, что порядок и тип возвращаемых столбцов известен, так как полагаю, что избавится от T.* не так сложно, то гораздо лучше сократить возвращаемые столбцы до минимума и просто объявить их все по отдельности.

Свободный перевод ответа от участника @Alex Poole

Как объявить переменную с типом данных для слабо типизированного SYS_REFCURSOR?

2 Answers

Add your own answers!

Ask a Question