Stack Overflow на русском Asked on January 1, 2022
В коде ниже не могу объявить переменную с типом данных %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
Разумеется, нет желания создавать для этого статическую таблицу 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
Answered by 0xdb on January 1, 2022
Короткий ответ - так сделать не получится. Нужно определить переменную для каждого возвращаемого столбца.
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
Answered by 0xdb on January 1, 2022
Get help from others!
Recent Questions
Recent Answers
© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP