Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Oracle Новый топик    Ответить
 sys_refcursor в составлении запроса  [new]
Retvit
Member

Откуда:
Сообщений: 5
Можно ли использовать переменную sys_refcursor в составлении запроса

т.е. вместо
begin
  open v_cursor for
    select dummy, cursor(select 1 id from dual) cr from dual;
end;
/


у нас есть уже открытый курсор, который и нужно поместить в конструкцию запроса
declare
  v_c   sys_refcursor;
begin
  open v_c for select 1 id from dual;

  open v_cursor for
    select dummy, v_c  cr from dual;
end;
/
9 окт 18, 22:25    [21699824]     Ответить | Цитировать Сообщить модератору
 Re: sys_refcursor в составлении запроса  [new]
booby
Member

Откуда:
Сообщений: 1609
Retvit,

курсор может быть параметром функции, используемой в sql-запросе.
9 окт 18, 22:59    [21699847]     Ответить | Цитировать Сообщить модератору
 Re: sys_refcursor в составлении запроса  [new]
SkilledJunior
Member

Откуда:
Сообщений: 295
Retvit,

Результирующий набор данных открытого курсора уже предопределен, он не зависит от второго курсора, смысл такой конструкции непонятен.

Возможность создать вложенный курсор есть CURSOR Expressions
9 окт 18, 23:13    [21699858]     Ответить | Цитировать Сообщить модератору
 Re: sys_refcursor в составлении запроса  [new]
xtender
Member

Откуда: Мск
Сообщений: 4960
Retvit,

да, и это же легко проверить в вашем же коде парой строчек:

declare
  v_c   sys_refcursor;
  v_cursor sys_refcursor;
  
  res_dummy  dual.dummy%type;
  res_cursor sys_refcursor;
  res_id     int;
begin
  open v_c for select 1 id from dual;

  open v_cursor for
    select dummy, v_c  cr from dual;
  
  fetch v_cursor into res_dummy,res_cursor;
  fetch res_cursor into res_id;

  dbms_output.put_line('dummy = '||res_dummy);
  dbms_output.put_line('id    = '||res_id);
end;
/
10 окт 18, 01:40    [21699948]     Ответить | Цитировать Сообщить модератору
 Re: sys_refcursor в составлении запроса  [new]
Stax
Member

Откуда: Ukraine,Lviv
Сообщений: 1419
xtender,

я так понял Retvit хочет
...
begin
  open v_cursor for
    select dummy, cursor(v_cursor) cr from dual;
end;
/



.....
stax
10 окт 18, 08:45    [21700050]     Ответить | Цитировать Сообщить модератору
 Re: sys_refcursor в составлении запроса  [new]
-2-
Member

Откуда:
Сообщений: 14107
Stax
я так понял Retvit хочет
Даже, если и так. И что??
Retvit
поместить в конструкцию запроса
В чем смысл курсора из дуала, возвращающего курсор?
10 окт 18, 09:07    [21700068]     Ответить | Цитировать Сообщить модератору
 Re: sys_refcursor в составлении запроса  [new]
Retvit
Member

Откуда:
Сообщений: 5
Я не смог вчера сразу понять ошибку, думал это именно наличие sys_refcursor в курсоре все портит.

Мне нужно получить данные (в примере таблицы) + на каждую строку курсор (в примере v$version), который будет потом конвертироваться в XML.

Если возвращаемая строка одна, то ок, если несколько - ошибка, из-за того, что курсор v_c уже прочитан.

declare
  v_cursor      sys_refcursor;
  v_c           sys_refcursor;
  xmldoc        xmltype;
  ctx           number;
  v_table_name  varchar2(30);
  v_sub_curr    sys_refcursor;
begin
  open v_c for select * from v$version;
 
  open v_cursor for select table_name, v_c cr from dba_tables where rownum <= 2; --> Error
  --open v_cursor for select table_name, cursor( select * from v$version ) cr from dba_tables where rownum <= 2; --> Работает
 
  loop
    fetch v_cursor into v_table_name, v_sub_curr;
    exit when v_cursor%notfound;
   
    dbms_output.put_line( v_table_name );
   
    ctx := dbms_xmlgen.newcontext(v_sub_curr);
    xmldoc := dbms_xmlgen.getxmltype(ctx);
    dbms_output.put_line( xmldoc.getstringval() );
  end loop;
end;


Так что хотелось бы понять, можно как-то с переменной sys_refcursor получить поведение с cursor(), переоткрыть ее что ли.
10 окт 18, 19:55    [21700962]     Ответить | Цитировать Сообщить модератору
 Re: sys_refcursor в составлении запроса  [new]
-2-
Member

Откуда:
Сообщений: 14107
Retvit
Мне нужно получить данные (в примере таблицы) + на каждую строку курсор (в примере v$version)
Какой смысл в каждой строке возвращать один и тот же курсор?

Retvit
который будет потом конвертироваться в XML.
Зачем сейчас извращаться с вложенным курсором, если он потом не нужен как курсор?
10 окт 18, 20:34    [21700986]     Ответить | Цитировать Сообщить модератору
 Re: sys_refcursor в составлении запроса  [new]
SY
Member

Откуда: Middlebury, CT USA
Сообщений: 9230
Retvit,

Похоже ты курсоры перепутал:

declare
    v_cursor     sys_refcursor;
    xmldoc       xmltype;
    ctx          number;
    v_banner     varchar2(80);
    v_table_name varchar2(80);
    v_sub_curr   sys_refcursor;
begin
    open v_cursor for select banner,cursor(select table_name from dba_tables where rownum <= 10) from v$version;
    fetch v_cursor into v_banner,v_sub_curr;
    ctx := dbms_xmlgen.newcontext(v_sub_curr);
    xmldoc := dbms_xmlgen.getxmltype(ctx);
    dbms_output.put_line(xmldoc.getstringval());
end;
/
<ROWSET>
 <ROW>
  <TABLE_NAME>IND$</TABLE_NAME>
 </ROW>
 <ROW>

<TABLE_NAME>CDEF$</TABLE_NAME>
 </ROW>
 <ROW>
  <TABLE_NAME>CLU$</TABLE_NAME>

</ROW>
 <ROW>
  <TABLE_NAME>UNDO$</TABLE_NAME>
 </ROW>
 <ROW>

<TABLE_NAME>CCOL$</TABLE_NAME>
 </ROW>
 <ROW>

<TABLE_NAME>PROXY_ROLE_DATA$</TABLE_NAME>
 </ROW>
 <ROW>

<TABLE_NAME>FET$</TABLE_NAME>
 </ROW>
 <ROW>
  <TABLE_NAME>CON$</TABLE_NAME>

</ROW>
 <ROW>
  <TABLE_NAME>ICOL$</TABLE_NAME>
 </ROW>
 <ROW>

<TABLE_NAME>COL$</TABLE_NAME>
 </ROW>
</ROWSET>


PL/SQL procedure successfully completed.

SQL> 


SY.
10 окт 18, 20:49    [21700996]     Ответить | Цитировать Сообщить модератору
 Re: sys_refcursor в составлении запроса  [new]
SY
Member

Откуда: Middlebury, CT USA
Сообщений: 9230
Вернее:

declare
    v_cursor     sys_refcursor;
    xmldoc       xmltype;
    ctx          number;
begin
    open v_cursor
      for
        with t as (
                   select  listagg(banner,' ') within group(order by rownum) banner
                     from  v$version
                  )
        select  banner,
                cursor(select table_name from  dba_tables where rownum <= 3) table_name
          from  t;
    ctx := dbms_xmlgen.newcontext(v_cursor);
    xmldoc := dbms_xmlgen.getxmltype(ctx);
    dbms_output.put_line(xmldoc.getstringval());
end;
/
<ROWSET>
 <ROW>
  <BANNER>Oracle Database 12c Enterprise Edition Release
12.2.0.1.0 - 64bit Production PL/SQL Release 12.2.0.1.0 - Production
CORE    12.2.0.1.0      Production TNS for 64-bit Windows: Version 12.2.0.1.0 -
Production NLSRTL Version 12.2.0.1.0 - Production</BANNER>
  <TABLE_NAME>

<TABLE_NAME_ROW>
    <TABLE_NAME>IND$</TABLE_NAME>
   </TABLE_NAME_ROW>

<TABLE_NAME_ROW>
    <TABLE_NAME>CDEF$</TABLE_NAME>
   </TABLE_NAME_ROW>

<TABLE_NAME_ROW>
    <TABLE_NAME>CLU$</TABLE_NAME>
   </TABLE_NAME_ROW>

</TABLE_NAME>
 </ROW>
</ROWSET>


PL/SQL procedure successfully completed.

SQL> 


SY.
10 окт 18, 21:16    [21701017]     Ответить | Цитировать Сообщить модератору
 Re: sys_refcursor в составлении запроса  [new]
Retvit
Member

Откуда:
Сообщений: 5
-2-
Retvit
Мне нужно получить данные (в примере таблицы) + на каждую строку курсор (в примере v$version)
Какой смысл в каждой строке возвращать один и тот же курсор?

Пример придумать нетрудно =)
Составляется список таблиц для обзвона, в поле идут 3 вопроса к ним из заготовленного списка.
А одинаковые, потому что система сначала готовит списки вопросов и потом формирует к ним батчи с таблицами.
-2-
Retvit
который будет потом конвертироваться в XML.
Зачем сейчас извращаться с вложенным курсором, если он потом не нужен как курсор?

На вход будет приходить sys_refcursor
10 окт 18, 21:35    [21701027]     Ответить | Цитировать Сообщить модератору
 Re: sys_refcursor в составлении запроса  [new]
Retvit
Member

Откуда:
Сообщений: 5
SY,
Ты не понял суть вопроса =(
10 окт 18, 21:36    [21701028]     Ответить | Цитировать Сообщить модератору
 Re: sys_refcursor в составлении запроса  [new]
SkilledJunior
Member

Откуда:
Сообщений: 295
Retvit
Так что хотелось бы понять, можно как-то с переменной sys_refcursor получить поведение с cursor(), переоткрыть ее что ли.

Можно open -> xml -> close внутри цикла, но не нужно, первый курсор будет возвращать все время один и тот же результат, зачем выполнять один и тот же запрос много раз, если ты сразу можешь сформировать xml-объект и далее использовать его.

declare
  v_cursor      sys_refcursor;
  v_c           sys_refcursor;
  xmldoc        xmltype;
  ctx           number;
  v_table_name  varchar2(30);
begin
  open v_c for select * from v$version;
  ctx := dbms_xmlgen.newcontext(v_c);
  xmldoc := dbms_xmlgen.getxmltype(ctx);
  close v_c;

  open v_cursor for select table_name from dba_tables where rownum <= 2;
  loop
    fetch v_cursor into v_table_name;
    exit when v_cursor%notfound;

    dbms_output.put_line( v_table_name );
    dbms_output.put_line( xmldoc.getstringval() );
  end loop;
  close v_cursor;
end;
10 окт 18, 22:20    [21701058]     Ответить | Цитировать Сообщить модератору
 Re: sys_refcursor в составлении запроса  [new]
SY
Member

Откуда: Middlebury, CT USA
Сообщений: 9230
Retvit
Пример придумать нетрудно =)
Составляется список таблиц для обзвона, в поле идут 3 вопроса к ним из заготовленного списка.


Ну и зачем тут PL/SQL?

SQL> select  *
  2    from  questions
  3  /

QUESTION
------------------------
question 1
question 2
question 3

with t1 as (
            select  xmlagg(xmlelement("question",question)) questions
              from questions
           ),
     t2 as (
            select  xmlagg(xmlelement("responder",ename)) responders
              from  emp
           )
select  xmlserialize(
                     document
                     xmlelement(
                                "survey",
                                 xmlelement("questions",questions),
                                 xmlelement("responders",responders)
                               )
                     indent size = 2
                    ) survey
  from  t1,
        t2
/

SURVEY
--------------------------------------
<survey>
  <questions>
    <question>question 1</question>
    <question>question 2</question>
    <question>question 3</question>
  </questions>
  <responders>
    <responder>SMITH</responder>
    <responder>ALLEN</responder>
    <responder>WARD</responder>
    <responder>JONES</responder>
    <responder>MARTIN</responder>
    <responder>BLAKE</responder>
    <responder>CLARK</responder>
    <responder>SCOTT</responder>
    <responder>KING</responder>
    <responder>TURNER</responder>
    <responder>ADAMS</responder>
    <responder>JAMES</responder>
    <responder>FORD</responder>
    <responder>MILLER</responder>
  </responders>
</survey>


SQL> 


SY.
10 окт 18, 23:35    [21701093]     Ответить | Цитировать Сообщить модератору
 Re: sys_refcursor в составлении запроса  [new]
MaximaXXL
Member

Откуда: Киев
Сообщений: 635
Retvit,

Да можно и так, лиж бы ручки не болели
with questions as (select 'question 1' QUESTION from dual union all
                   select 'question 2' QUESTION from dual union all
                   select 'question 3' QUESTION from dual) 
,responder as (select 'resp1' Name from dual union all
               select 'resp2' Name from dual union all
               select 'resp13' Name from dual)
,t2 as (
            select  xmlagg(xmlelement("responder",Name)) responders
              from  responder
           )
           
select  xmlserialize(
                     document
                     xmlelement(
                                "survey",
                                 xmlagg(xmlelement("responders", xmlattributes(QUESTION as "QUESTION"), responders))
                               )
                     indent size = 2
                    ) survey
  from  questions,
        t2

<survey>
  <responders QUESTION="question 1">
    <responder>resp1</responder>
    <responder>resp2</responder>
    <responder>resp13</responder>
  </responders>
  <responders QUESTION="question 2">
    <responder>resp1</responder>
    <responder>resp2</responder>
    <responder>resp13</responder>
  </responders>
  <responders QUESTION="question 3">
    <responder>resp1</responder>
    <responder>resp2</responder>
    <responder>resp13</responder>
  </responders>
</survey>
11 окт 18, 13:55    [21701587]     Ответить | Цитировать Сообщить модератору
Все форумы / Oracle Ответить