Все технические форумы на одном сайте Удобный поиск информации с популярных форумов в одном месте
Вопрос: работа с sys_refcursor

Приветик.
Есть функция, возвращаюящая курсор, грубо говоря вот:
create or replace function new_func return sys_refcursor is
  r_cursor sys_refcursor;  
  begin
  open r_cursor for
       select null from dual;
  return r_cursor;
  end;


Вызывается как обычная функция:
select new_func from dual


Вопрос: можно ли каким-то образом использовать ее в тексте другой функции и оперировать с курсором, который она возвращает?

Возможно, посыпятся ответы про то, что я не понимаю, что такое курсоры и все такое прочее, я знаю, я дилетант, просто мне так нужно.

И если не сложно, расскажите, чем принципиально отличаются sys_refcursor и обычный курсор.
Ответ:
Кейт Марш
"чем принципиально отличаются sys_refcursor и обычный курсор"


курсор - это указатель на набор данных.
sys_refcursor - системный тип нестрогого курсора
Вопрос: Параметр типа SYS_REFCURSOR в процедуре

У меня есть процедура
Oracle 11 SQL
1
2
3
4
5
6
7
8
9
CREATE OR REPLACE PROCEDURE test( p_deptno IN NUMBER
                                , p_cursor OUT SYS_REFCURSOR)
IS
BEGIN
  OPEN p_cursor FOR 
  SELECT *
  FROM   emp
  WHERE  deptno = p_deptno;
END test;
Подскажите, как ее вызвать? Особенно интересует как привязать второй параметр.
Пытаюсь так сделать, но ругается на тип или количество аргументов...
Oracle 11 SQL
1
2
3
4
5
6
DECLARE
cur sys_refcursor;
BEGIN
    test(448138, cur);
END;
/
Ответ: Спасибо, примерно это я и хотел услышать.
Вот так у меня всё работает. Что я и хотел...

Oracle 11 SQL
1
2
3
4
5
6
7
8
9
DECLARE
    cur sys_refcursor;
    zz VARCHAR2(30);
BEGIN
    test(448138, cur);
    FETCH cur INTO zz;
    dbms_output.put_line(zz);
    CLOSE cur;
END;
Вопрос: sys_refcursor

Добрый день Гуру Оракл!
я изучаю оракл никак допереть не могу до курсора или sys_refcursor
есть процедура написана кем то, никак немогу запустить запрос
что подставить по умолчанию вместо :cur (null, '' не принимает)

begin
-- Call the procedure
kp_dreport002_list(cur => :cur,
bdate_ => :bdate_,
edate_ => :edate_);
end;
Ответ:
declare
  c sys_refcursor;
begin
  KP_READ_TS_UL_VIEW(c, 1, '');
end;


Заодно подумайте перед прогоном, почему так не сработает.
Вопрос: Заполнить коллекцию

Уважаемые форумчане, подскажите, пожалуйста, как правильно заполнить коллекцию.
SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
DECLARE
s2 sys_refcursor;
TYPE rrec IS TABLE OF varchar2(20);
rec rrec:=rrec();
--type trec is table of varchar2(500);
--t_rec trec := trec();
TYPE trec IS varray (1) OF varchar2(500);
t_rec trec := trec();
col varchar2(10);
fl NUMBER:=0;
i pls_integer;
BEGIN  
   BEGIN
    s2 := get_nodes ('0255000046',to_date ('10042016', 'ddmmrrrr'),to_date ('11042016', 'ddmmrrrr'));
        loop 
            fetch s2 bulk collect INTO rec;                                           
            exit WHEN s2%notfound;    
        END loop;                             
   END;
   --
  dbms_output.put_line('<script src="#APP_IMAGES#springyui.js"></script>');
  dbms_output.put_line('<script src="#APP_IMAGES#springy.js"></script>');
  dbms_output.put_line('<script>');
  dbms_output.put_line('var graph = new Springy.Graph();');
  dbms_output.put_line('graph.addNodes( ');
  --
  FOR i IN rec.FIRST..rec.LAST
      loop
               FOR x IN (WITH plat
                         AS (SELECT ROW_NUMBER () OVER (partition BY inn_plat ORDER BY SUM (sum_ekv) DESC)
                                         rn,
                                      inn_plat,
                                      inn_pol,
                                      SUM (sum_ekv) sm
                                 FROM atb_segmen_pl_kb
                                WHERE      date_d BETWEEN to_date ('10042016', 'ddmmrrrr') AND to_date ('11042016', 'ddmmrrrr')
                                      AND inn_plat = rec(i)
                                      AND inn_plat <> inn_pol
                             GROUP BY inn_plat, inn_pol),
                         pol
                         AS (  SELECT ROW_NUMBER () OVER (partition BY inn_pol ORDER BY SUM (sum_ekv) DESC) rn,
                                      inn_plat,
                                      inn_pol,
                                      SUM (sum_ekv) sm
                                 FROM atb_segmen_pl_kb
                                WHERE      date_d BETWEEN to_date ('10042016', 'ddmmrrrr') AND to_date ('11042016', 'ddmmrrrr')
                                      AND inn_pol = rec(i)
                                      AND inn_plat <> inn_pol
                             GROUP BY inn_plat, inn_pol)
                          SELECT inn_pol inn FROM plat WHERE rn < 5
                          UNION
                          SELECT inn_pol inn FROM pol WHERE rn < 5
                          UNION
                          SELECT inn_plat inn FROM plat WHERE rn < 5
                          UNION
                          SELECT inn_plat inn FROM pol WHERE rn < 5)           
                          loop
                              IF x.inn = '0255000046' THEN col:='#33CC00'; ELSE col:='#000'; END IF;
                              dbms_output.put_line('{name:'''||x.inn||''',color:'''||col||'''}');
                              IF fl <> 0 THEN dbms_output.put_line(','); ELSE fl := 1; END IF;
                            END loop;
      END loop;
  --
  dbms_output.put_line(');graph.addEdges(');
  --
  fl:=0;
  
  FOR s IN rec.FIRST..rec.LAST --26
      loop        
          FOR x IN (
              SELECT  '['''||inn_plat||''', '''||inn_pol||''', {color: ''#000'', label: '''||sm||'''}]' str     
                --distinct min(rownum) over() m, inn_plat, /*max(inn_pol) over() tbm,*/ inn_pol,round (sm, 0) sm, '#000' col
          FROM (SELECT *
                  FROM (SELECT to_char (inn_plat) inn_plat,
                                 to_char (inn_pol) inn_pol,
                                 SUM (sum_ekv) sm
                            FROM atb_segmen_pl_kb s
                           WHERE     date_d BETWEEN to_date ('10042016', 'ddmmrrrr') AND to_date ('11042016', 'ddmmrrrr')
                                 AND inn_plat = rec(s)
                                 AND s.inn_plat <> s.inn_pol
                        GROUP BY inn_plat, inn_pol
                        ORDER BY sm DESC)
                 WHERE rownum < 5
                UNION ALL
                SELECT *
                  FROM (SELECT inn_plat, inn_pol, SUM (sum_ekv) sm
                            FROM atb_segmen_pl_kb s
                           WHERE     date_d BETWEEN to_date ('10042016', 'ddmmrrrr') AND to_date ('11042016', 'ddmmrrrr')
                                 AND inn_pol = rec(s)
                                 AND s.inn_plat <> s.inn_pol
                        GROUP BY inn_plat, inn_pol
                        ORDER BY sm DESC)
                 WHERE rownum < 5)
                 ) 
                 loop                     
                     --if fl <> 0 then dbms_output.put_line(','); else fl := 1; end if;
                     --dbms_output.put_line('['''||x.inn_plat||''', '''||x.inn_pol||''', {color: ''#000'', label: '''||x.sm||'''}]');
                     --select x.s bulk collect into t_rec from dual;                     
                     t_rec(i) := x.str;                    
                     t_rec.extend;
                     i:=t_rec.NEXT(i);
  --Вот в этом цикле, в коллекции REC 26 элементов, инн, по ним хочу пробежаться в другом цикле и собрать строку, строку сложить в коллекцию t_rec. Как правильно реализовать?--
                 END loop;
       END loop;
    
    dbms_output.put_line(t_rec.COUNT);
    --
    dbms_output.put_line('); ');
 
    dbms_output.put_line('
    jQuery(function(){
 
    var springy = jQuery(''#springydemo'').springy({
    graph: graph
    });
    });
 
    </script>
 
    <canvas id="springydemo" width="1100" height="800" />'); 
END;
Добавлено через 3 часа 2 минуты
Проблема решена. Можно закрыть. Счётчик нужно было брать не по I , а по курсору х
Ответ: Добрый день. Прошу знатоков подсказать с решением проблемки
фкнкция get_nodes возвращает набор инн, в sys_refcursor;
По всем этим инн'ам необходимо еще раз прогнать эту функцию, что б для каждого из них еще вернуть список инн (получаем список нодов с определенной глубиной).
В коллекции rec 10 элементов.
По ним прохожусь еще раз функцией get_nodes и пишу их в t_rec.
Но в результате в t_rec получаю тот же набор данных что и в rec.
Может ошибка в написании кода...
SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
DECLARE
   s2   sys_refcursor;
   s3   sys_refcursor;
   --
   c_inn varchar2(20) := '7404052938';
   ds DATE := to_date ('01032016', 'ddmmrrrr');
   de DATE := to_date ('31032016', 'ddmmrrrr');
   --
   TYPE rrec IS TABLE OF varchar2(20);
   rec rrec := rrec();
   --
   TYPE trec IS TABLE OF varchar2(25);
   t_rec trec := trec();
   x pls_integer := 1;
BEGIN   
   BEGIN
    s2 := get_nodes_tb(c_inn, ds, de);
    loop
      fetch s2 bulk collect
        INTO rec;
      exit WHEN s2%notfound;
    END loop;
   END;
   BEGIN    
    FOR i IN rec.FIRST..rec.LAST        
        loop
            s3 := get_nodes_tb(rec(i), ds, de);     
            loop                
                fetch s3 bulk collect INTO t_rec;                
                exit WHEN s3%notfound;                               
            END loop;
      END loop;
    END;        
    --
    FOR s IN rec.FIRST..rec.LAST 
        loop
            dbms_output.put_line(rec(s));
        END loop;
--
--    for t in t_rec.first..t_rec.last 
--        loop
--            dbms_output.put_line(t_rec(t));
--        end loop;
END;
/*7404056121
7404065172
7404052938
7414003633
7415061109
7423004062
7447171400
7706074737
7708683999
7404052938*/
--Набор данных в rec и t_rec одинаковый на выходе
Вопрос: MongoDB. выборка полей из нескольких коллекций

У меня есть две коллекции связанные по первичному и внешнему ключу.
Первая коллекция:
OPERCODE:
{
  "OPERCODE_ID" <----Первичный ключ
  "OPERNAME" 
  "OPERDESCRIP" 
  "RECSTATE"<---Внешний ключ
} 

Вторая коллекция:
RECSTATES:
{
  "RECSTATE" <----Первичный ключ
  "NAME"
}

Вопрос:как мне в MongoDB реализовать следующий запрос (в MS SQL SERVER)?:
select o.*,r.*
from OPERCODE as o inner join RECSTATES as r 
on o.RECSTATE=r.RECSTATE

и можно ли его вообще реализовать?
Ответ:
ASukhov1986
skyANA,
а если у меня две коллекции связанные первичный-внешний ключи по 20 столбцов каждая, то мне нужно создать одну коллекцию с 40 столбцами так?
"первичный-внешний ключи", "столбцы"...

В MongoDB не так всё делается :)

Ну и в первом топике видно, что RECSTATE имеет только два атрибута id и name.

P.S.: почитайте
Вопрос: Не обновляется коллекция TableDefs

Приветствую!
Set dao_TmpDB = DAO.OpenDatabase(Application.DefaultFilePath & "\" & "check.mdb")'пустая БД

'если в этот момент посмотреть, что есть в коллекции dao_TmpDB.TableDefs, то видно 9 системных таблиц

'*************************************** _
SQL_str = "CREATE TABLE " & SMEX_IDS_TABLE_NAME _
    & "(" _
            & "TKey COUNTER CONSTRAINT Key_Pr_Constr PRIMARY KEY, " _
            & "ID_SU Long NOT NULL" _
    & ");"
    Debug.Print SQL_str
dao_TmpDB.Execute SQL_str
SQL_str = "CREATE INDEX MyIdIndex ON " & SMEX_IDS_TABLE_NAME & " (ID_SU Asc);"
dao_TmpDB.Execute SQL_str
'***************************************
'если в этот момент посмотреть, что есть в коллекции dao_TmpDB.TableDefs, то видно ВСЕ ТЕ ЖЕ 9 системных таблиц

В этой же процедуре заполняю вновь созданную таблицу:
Set Filler = dao_TmpDB.OpenRecordset(SMEX_IDS_TABLE_NAME, dbOpenTable, dbDenyWrite)
Set asdf = Filler.Parent
With Filler
    For Each k In RangeOfIDs
        .AddNew
        !ID_SU = k.Value
        .Update
    Next
End With
Filler.Close

Ошибок не возникает, но если в этот момент посмотреть dao_TmpDB.TableDefs, то видно ВСЕ ТЕ ЖЕ 9 системных таблиц.
Теперь если закрыть БД и открыть ее заново (все через ДАО), то вуаля, новая таблица появилась в наборе TableDefs.

Есть ли способ обновить TableDefs не отключаясь от базы?
Ответ:
Lockpickup
'если в этот момент посмотреть, что есть в коллекции dao_TmpDB.TableDefs, то видно 9 системных таблиц
Попробуйте выполнить dao_TmpDB.TableDefs.Refresh
Вопрос: Работа с коллекциями.

Имею некоторое непонимание работы с коллекциями.
Необходимо заполнить две таблицы на основе xml документа. Была-бы одна таблица, можно было-бы выполнить вставку из select ... from table(XMLSequence(xmltype(xml_doc).extract('root/t')))
Но поскольку заполнять нужно две таблички вложенными данными, необходимо выполнять вставку в цикле.
Привожу нерабочий пример:
declare
  type t_col_1 IS TABLE OF xmltype;
  xml_doc xmltype;
  col_1 t_col_1;
begin   
  xml_doc:= xmltype('<root><t>1</t><t>2</t><t>3</t><t>4</t><t>5</t><t>6</t><t>7</t><t>8</t><t>9</t><t>10/t><t>11</t><t>12</t><t>13</t><t>14</t><t>15</t><t>16</t></root>');
  col_1:= table(XMLSequence(xmltype(xml_doc).extract('root/t')));
  forall i in IN INDICES OF col_1
    DBMS_OUTPUT.PUT_LINE col_1(i).extract('t').getStringVal();
end; 


на строчке col_1:= table(XMLSequence(xmltype(xml_doc).extract('root/t'))); естественно возникает ошибка
PLS-00103: Encountered the symbol "TABLE" when expecting one of the following ...
как правильно поместить коллекцию в col_1 ?
Ответ:
Игорь Ч
как правильно поместить коллекцию в col_1 ?


SELECT xml_filed bulk collect into col_1 limit N from table ;
Вопрос: Коллекции в функциях

Годная ли практика использовать функции, которые принимают, либо возвращают заполненные коллекции?
и как в этому случае происходит освобождение памяти? (dbms_session.free_unused_user_memory)
type Shod
IS
  record
  (...);
type ShodTable
IS
  TABLE OF Shod;
...
type ds
IS
  record
  (
    ts ShodTable, 
tr xxxTable,
tz yyyTable
...);
function f1(...) return ds;
procedure p1(DataSet in ds);
Ответ:
PsyDiamond
благодарю за ответы


Кстати, если вы уж озаботились очисткой коллекций, то прочитайте про свойство DELETE у коллекций.
Вопрос: непоследовательного заполнения коллекций

В книге PL/SQL для профессионалов есть пример непоследовательного заполнения коллекции с использованием функции, в сигнатуре которой есть один параметр.
DROP TABLE hairstyles;

CREATE TABLE hairstyles (
   code INTEGER,
   description VARCHAR2(100)
   );

INSERT INTO hairstyles VALUES (1000, 'CREWCUT');
INSERT INTO hairstyles VALUES (1001, 'BOB');
INSERT INTO hairstyles VALUES (1002, 'SHAG');
INSERT INTO hairstyles VALUES (1003, 'BOUFFANT');
INSERT INTO hairstyles VALUES (1004, 'PAGEBOY');

CREATE OR REPLACE PACKAGE justonce
IS
   FUNCTION description (code_in IN hairstyles.code%TYPE)
      RETURN hairstyles.description%TYPE;
END justonce;
/

CREATE OR REPLACE PACKAGE BODY justonce
IS
   TYPE desc_t IS TABLE OF hairstyles.description%TYPE
      INDEX BY BINARY_INTEGER;
   descriptions   desc_t;

   FUNCTION description (code_in IN hairstyles.code%TYPE)
      RETURN hairstyles.description%TYPE
   IS
      return_value   hairstyles.description%TYPE;

      FUNCTION desc_from_database RETURN hairstyles.description%TYPE
      IS
         CURSOR desc_cur IS
            SELECT description FROM hairstyles WHERE code = code_in;
         desc_rec   desc_cur%ROWTYPE;
      BEGIN
         OPEN desc_cur;
         FETCH desc_cur INTO desc_rec;
         RETURN desc_rec.description;
      END;
   BEGIN
      RETURN descriptions (code_in);
   EXCEPTION
      WHEN NO_DATA_FOUND 
	  THEN
         descriptions (code_in) := desc_from_database;
         RETURN descriptions (code_in);
   END;
END justonce;
/

BEGIN
   DBMS_OUTPUT.PUT_LINE (justonce.description (1000));
   DBMS_OUTPUT.PUT_LINE (justonce.description (1002));
   DBMS_OUTPUT.PUT_LINE (justonce.description (1004));
END;
/

Вопрос как сделать тоже самое, если в сигнатуре функции 2 и более параметров? Непонятно какую коллекцию нужно будет использовать. Т.е. должно быть, что то типа:
BEGIN
      RETURN descriptions (code_in, param1_in, param2_in);
   EXCEPTION
      WHEN NO_DATA_FOUND 
	  THEN
         descriptions (code_in, param1_in, param2_in) := desc_from_database;
         RETURN descriptions (code_in, param1_in, param2_in);
   END;

Напишите возможно ли это и пример кода пожалуйста.
Ответ: Спасибо.
Не получилось реализовать задуманное, создав коллекцию записей непонятно как вернуть данные из нее.
Создавал ее так:
TYPE my_rec IS RECORD (
  r_code hairstyles.code%TYPE,
  r_grp hairstyles.grp%TYPE
);
TYPE my_array IS TABLE OF my_rec INDEX BY BINARY_INTEGER;
my_table my_array ;

Конструкция вида
RETURN my_table(my_rec(code_in, grp_in))

Не работает.
Вопрос: Возврат SYS_REFCURSOR в Talend

Есть скрипты такого вида:

Declare vKIOTable       varchar2(50);
    sSql            varchar2(4000);
    crs             sys_refcursor;
Begin
...
 
    sSql:='' ||
        'SELECT ' ||
                    'cl.ID                                                            AS CLIENTID  ' ||                                  
                    ',nvl(cl.c_inn, ' || vKIOTable || '.c_kio)                        AS INN ' ||     
					..........
          'FROM     Z#CLIENT cl ' || 
					...........
         'WHERE     1 = 1' ;
 
    open crs for sSql ;

    :crsout:=crs;
END;


Через TOAD при запуске :crsout указывается как переменная типа cursor c direction = out.

Если использовать скрипт в Talend в tOracleRow, убрав :crsout и добавив dbms_output, то вывод dbms_output.putline не считывается, SET serveroutput ON воспринимает как ошибку. Создать процедуру, функцию нельзя, только селект.
Каким образом передать sys_refcursor в Talend?
Ответ:
magnolija939
убрав :crsout и добавив dbms_output,


Месье знает толк в извращениях.