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

Откуда:
Сообщений: 64
Извучаю вот курсоры, посему накопилось вопросов кучу. Хотелось бы услышать советов умных людей

create or replace
PROCEDURE CHECK_DATA_IN
IS
   stmt    VARCHAR2 (250);
   stmt1   VARCHAR2 (250);
   foo     elon.castro%ROWTYPE;
   c       sys_refcursor;
BEGIN

   OPEN c FOR    'SELECT trunc (y.TIME,''hh24'') time_info FROM CELL_HO@grandb x,  cell_adm@grandb y WHERE x.ID = y.ID and y.ne = ''KIEB6'
              || ''' AND y.TIME >= TO_DATE (''05.07.2009'', ''DD.MM.YYYY HH24:MI:SS'') and y.TIME < TO_DATE (''12.07.2009'', ''DD.MM.YYYY HH24:MI:SS'') group by trunc (y.TIME,''hh24'') order by trunc (y.TIME,''hh24'')';

   FETCH c INTO foo;

   INSERT INTO elon.castro2 VALUES foo;

   dbms_output.put_line('Fetched');
   IF c%NOTFOUND
   THEN
      dbms_output.put_line('NOTFOUND');

      dbms_output.put_line('Insert result: ' || stmt);
   ELSE dbms_output.put_line ('Shit');
   END IF;
   CLOSE c;
END;

Запрос правильный, вывожу в дебаг его и всё правильно возвращает. Сам запрос возвращает десяток строк.
INSERT INTO elon.castro2 VALUES foo;
-вставляет в таблицу только первую строку, которую должен вернуть курсор. (как я уже писал запрос возвращает больше чем одну строку и это не правильно)
IF c%NOTFOUND
- не срабатывает никогда, хотя
INSERT INTO elon.castro2 VALUES foo;
вставил одну строку.

Это упрощённая схема. Идея в том, что взять запросом проверить, если ли в таблице данные за вчера(поле TIME присутствует), если данных нету, то нужно записать это в обдельную таблицу. Пока привожу простую схему.

Сейчас вопрос такой: почему
INSERT INTO elon.castro2 VALUES foo;
вставляет только первую строку и как посмотерть содержимое курсора с ?
17 июл 09, 15:28    [7429850]     Ответить | Цитировать Сообщить модератору
 Re: Как узнать содержимое курсора ?  [new]
Lecter
Member

Откуда: Киев
Сообщений: 2032
Castro men
обдельную таблицу

По Фрейду.

По теме: 1. цыкл который пройдет по курсору?!
2. Почему не написать сразу запрос который проверит данные на предмет необходимости вставки?
17 июл 09, 15:33    [7429884]     Ответить | Цитировать Сообщить модератору
 Re: Как узнать содержимое курсора ?  [new]
Lecter
Member

Откуда: Киев
Сообщений: 2032
create or replace
PROCEDURE CHECK_DATA_IN
IS
   stmt    VARCHAR2 (250);
   stmt1   VARCHAR2 (250);
   foo     elon.castro%ROWTYPE;
   c       sys_refcursor;
BEGIN

   OPEN c FOR    'SELECT trunc (y.TIME,''hh24'') time_info FROM CELL_HO@grandb x,  cell_adm@grandb y WHERE x.ID = y.ID and y.ne = ''KIEB6'
              || ''' AND y.TIME >= TO_DATE (''05.07.2009'', ''DD.MM.YYYY HH24:MI:SS'') and y.TIME < TO_DATE (''12.07.2009'', ''DD.MM.YYYY HH24:MI:SS'') group by trunc (y.TIME,''hh24'') order by trunc (y.TIME,''hh24'')';

   FETCH c INTO foo;

   WHILE( c%FOUND )
    LOOP
        INSERT INTO elon.castro2 VALUES foo;

       FETCH c INTO foo;

    END LOOP;
   CLOSE c;
END;

Never again...
17 июл 09, 15:35    [7429901]     Ответить | Цитировать Сообщить модератору
 Re: Как узнать содержимое курсора ?  [new]
dmidek
Member

Откуда: Киев - Дортмунд
Сообщений: 116070
Castro men

Сейчас вопрос такой: почему
INSERT INTO elon.castro2 VALUES foo;
вставляет только первую строку и как посмотерть содержимое курсора с ?


Брррр, а сколько же он болезный может вставить.
Вы сделали один FETCH + один INSERT VALUES...

А вообще то Лектер прав, только надо выбрасывать вообще все
и цикл и курсор и динамику и проверку , а оставлять
красивенький и одинокий

INSERT
SELECT

:-)
17 июл 09, 15:39    [7429925]     Ответить | Цитировать Сообщить модератору
 Re: Как узнать содержимое курсора ?  [new]
suPPLer
Member

Откуда: Харків, Україна
Сообщений: 7794
Блог
0. Каша. В знаниях пробелы, в желаниях нет конкретики. Ну да ладно.
1. Чтобы считать все данные из курсора, пользуясь одной переменной-записью, нужны циклы.
2. sys_refcursor для указанного Вами запроса не нужен.
3. Для этой задачи хватит insert into ... <Ваш_запрос>. Дальше проверяем кол-во обработанных записей с помощью SQL%ROWCOUNT и выводим чего там Вам нужно. Без всяких циклов и sys_refcursor...
-------------------------------------------------------
When I say "RTFM" or "STFF" or "STFW",
the third letter means "Following" or "Fine"...
17 июл 09, 15:41    [7429936]     Ответить | Цитировать Сообщить модератору
 Re: Как узнать содержимое курсора ?  [new]
Lecter
Member

Откуда: Киев
Сообщений: 2032
dmidek

INSERT
SELECT

:-)


Это в идеале. Но бизнес логика часто-густо не дает возможности это сделать. А если база криво спроектирована то тут начнется...
17 июл 09, 15:42    [7429948]     Ответить | Цитировать Сообщить модератору
 Re: Как узнать содержимое курсора ?  [new]
dmidek
Member

Откуда: Киев - Дортмунд
Сообщений: 116070
Lecter
dmidek

INSERT
SELECT

:-)


Это в идеале. Но бизнес логика часто-густо не дает возможности это сделать. А если база криво спроектирована то тут начнется...


Не увидел никакой опасности для бизнес- логики (часто это напоминает некое
ритуальное заклинание - как же без slow-by-slow processing :-)) , ну да автору виднее...

Зато увидел возможное обоснование динамики : таблицы - источники - remote...
17 июл 09, 15:46    [7429968]     Ответить | Цитировать Сообщить модератору
 Re: Как узнать содержимое курсора ?  [new]
suPPLer
Member

Откуда: Харків, Україна
Сообщений: 7794
Блог
dmidek,

возможное обоснование динамики - в первой строке:
Castro men
Изучаю вот курсоры...
17 июл 09, 15:48    [7429982]     Ответить | Цитировать Сообщить модератору
 Re: Как узнать содержимое курсора ?  [new]
Lecter
Member

Откуда: Киев
Сообщений: 2032
dmidek

Не увидел никакой опасности для бизнес- логики (часто это напоминает некое
ритуальное заклинание - как же без slow-by-slow processing :-)) , ну да автору виднее...

Где Вы у меня вычитали слово опасность я могу лишь гадать.
Вы хотите поспорить что не все можно сделать одним оператором? Тогда я за чипсами и пивом
Такой подход оправдан( я про курсоры и циклы ) когда запись "размазывается" по многим таблицам, что часто следствие ненормированной схемы. И в "процессе" размазывание обрастает идентификаторами записей из других таблиц.
17 июл 09, 15:55    [7430028]     Ответить | Цитировать Сообщить модератору
 Re: Как узнать содержимое курсора ?  [new]
dmidek
Member

Откуда: Киев - Дортмунд
Сообщений: 116070
suPPLer
dmidek,

возможное обоснование динамики - в первой строке:
Castro men
Изучаю вот курсоры...


Их можно изучать и без динамики. Убрали несколько десятков кавычек и порядок
17 июл 09, 15:56    [7430043]     Ответить | Цитировать Сообщить модератору
 Re: Как узнать содержимое курсора ?  [new]
dmidek
Member

Откуда: Киев - Дортмунд
Сообщений: 116070
Lecter
dmidek

Не увидел никакой опасности для бизнес- логики (часто это напоминает некое
ритуальное заклинание - как же без slow-by-slow processing :-)) , ну да автору виднее...

Где Вы у меня вычитали слово опасность я могу лишь гадать.
Вы хотите поспорить что не все можно сделать одним оператором? Тогда я за чипсами и пивом
Такой подход оправдан( я про курсоры и циклы ) когда запись "размазывается" по многим таблицам, что часто следствие ненормированной схемы. И в "процессе" размазывание обрастает идентификаторами записей из других таблиц.


Спасибо, Лектер, мне пока пива нельзя, я на работе

Вы, я понял, несколько оторвались от конкретной задачи и
перешли к сферическим лошадям ? Я давал вообще то
совет по конкретной ситуации, какое там размазывание,
что размазывается - trunc ...hh24 ?

Это явное (само)обучение плохому, но так многие учат : залазят на курсоры,
а потом и не слазят с них . А совет "Все что может быть сделано в SQL,
должно быть сделано в нем" (ОК, с большим количеством исключений, о которых
надо говорить конкретно) абсолютно никто не отменял...
17 июл 09, 16:01    [7430069]     Ответить | Цитировать Сообщить модератору
 Re: Как узнать содержимое курсора ?  [new]
Castro men
Member

Откуда:
Сообщений: 64
Я знаю что рукожопо написано, но на нешёл нормального описалова, к тому я с динамическим никогда не работал и пытаюсь раздуплатиться.

Смысл в том, что есть таблицы, в которые каждый день сваливаются данные.
Сейчас там 2 таблицы, то бишь одна строка, будет больше. Собсвенно сейчас оно хранится в переменной j.
В переменной i хранится список названий объектов реального мира, например марка автомобиля (например VAZ, ZAZ, GAZ).
С общих чертах такая стурктура (очень опрощённо)
time_info | some_values | car_name
10.07.2009 | cool | VAZ

Сейчас их около 10 и они есть во всех проверяемых таблицах.
Для каждого такого объекта каждый день в таблицах, имя которых хранится в переменной j, создаётся запись содержащая дату и какие-то данные.
Моя задача состоит в том, чтоб проверить есть ли в таблицах (тоесть здесь в переменной j) какие-то записи за вчерашний день для каждого из марок атомобилей. Если в какой-то из проверяемых таблиц нету данных по какому-то автомобилю, то нужно записать в таблицу check_av_res,
что "В таблице cool_table нету данных за 13,07,2009 для марки Ваз", проще говоря 3 поля в таблице с датой, именем таблицы и маркой авто.

Как такое сделать правильно? Опыта мало, примеров тоже немного, бьюсь с переменным успехом, но хочу научится и понять что не так сделал,
потому что перепробовал много вариантов.

Вот более полный вариант

PROCEDURE CHECK_TEST_DATA_IN
IS
   stmt   VARCHAR2 (250);
   stmt1   VARCHAR2 (250);
   foo    elon.castro%ROWTYPE;
   c      sys_refcursor;
BEGIN
   FOR j IN (SELECT DISTINCT t.table_name tname
                        FROM elon.check_e_data_avail t)
   LOOP
   --DBG('j: '||j.tname);
   dbms_output.put_line('j: '||j.tname);
      FOR i IN (SELECT DISTINCT a.NAME cname
                           FROM dvlaznev.bsc a,
                                dvlaznev.site s,
                                dvlaznev.vendor v
                          WHERE a.bsc_id = s.bsc_id
                            AND s.vendor_id = v.vendor_id
                            AND v.vendor_id = 2
                            AND a.time_update >
                                          TO_DATE ('01.06.2009', 'DD.MM.YYYY')
                       GROUP BY a.NAME, TRUNC (a.time_update, 'hh24'))
      LOOP
      --DBG('BSC: '||i.cname);
      dbms_output.put_line('BSC: '||i.cname);
      DBG('SELECT trunc (y.TIME,''hh24'') time_info FROM '||j.tname||' WHERE x.ID = y.ID and y.ne = '''|| i.cname || 
      ''' AND y.TIME > TO_DATE (sysdate, ''DD.MM.YYYY HH24:MI:SS'') group by trunc (y.TIME,''hh24'') order by trunc (y.TIME,''hh24'')');
     
         OPEN c FOR    'SELECT trunc (y.TIME,''hh24'') time_info FROM '||j.tname||' WHERE x.ID = y.ID and y.ne = '''|| i.cname || 
      ''' AND y.TIME > TO_DATE (sysdate, ''DD.MM.YYYY HH24:MI:SS'') group by trunc (y.TIME,''hh24'') order by trunc (y.TIME,''hh24'')';

         FETCH c INTO foo;
         --insert into elon.castro2 values (foo.time_info); 
         --INSERT INTO elon.castro3 values (j.tname,i.cname, foo);
         dbms_output.put_line('Fetched');
        -- DBG('Fetched'); 
         IF c%NOTFOUND
         THEN
       --  DBG('NOTFOUND');
          dbms_output.put_line('NOTFOUND');
         stmt := 'INSERT INTO elon.check_av_res values (sysdate,''has no data'')';
          dbms_output.put_line('Insert result: '||stmt);
         --DBG('Insert result: '||stmt);
         EXECUTE IMMEDIATE stmt;
         END IF;

         CLOSE c;
      END LOOP;
   END LOOP;
END;
17 июл 09, 16:22    [7430200]     Ответить | Цитировать Сообщить модератору
 Re: Как узнать содержимое курсора ?  [new]
dmidek
Member

Откуда: Киев - Дортмунд
Сообщений: 116070
Castro men
Я знаю что рукожопо написано, но на нешёл нормального описалова, к тому я с динамическим никогда не работал и пытаюсь раздуплатиться.

Смысл в том, что есть таблицы, в которые каждый день сваливаются данные.
Сейчас там 2 таблицы, то бишь одна строка, будет больше. Собсвенно сейчас оно хранится в переменной j.
В переменной i хранится список названий объектов реального мира, например марка автомобиля (например VAZ, ZAZ, GAZ).
С общих чертах такая стурктура (очень опрощённо)
time_info | some_values | car_name
10.07.2009 | cool | VAZ

Сейчас их около 10 и они есть во всех проверяемых таблицах.
Для каждого такого объекта каждый день в таблицах, имя которых хранится в переменной j, создаётся запись содержащая дату и какие-то данные.
Моя задача состоит в том, чтоб проверить есть ли в таблицах (тоесть здесь в переменной j) какие-то записи за вчерашний день для каждого из марок атомобилей. Если в какой-то из проверяемых таблиц нету данных по какому-то автомобилю, то нужно записать в таблицу check_av_res,
что "В таблице cool_table нету данных за 13,07,2009 для марки Ваз", проще говоря 3 поля в таблице с датой, именем таблицы и маркой авто.

Как такое сделать правильно? Опыта мало, примеров тоже немного, бьюсь с переменным успехом, но хочу научится и понять что не так сделал,
потому что перепробовал много вариантов.

Вот более полный вариант


очень грубая , естественно неоткомпилированная калька..

declare 
v_count NUMBEr;
BEGIN
FOR j IN (SELECT DISTINCT t.table_name tname
          FROM elon.check_e_data_avail t)
LOOP
 FOR i IN (SELECT DISTINCT a.NAME cname
          FROM dvlaznev.bsc a,
               dvlaznev.site s,
               dvlaznev.vendor v
          WHERE a.bsc_id = s.bsc_id
          AND s.vendor_id = v.vendor_id
          AND v.vendor_id = 2
          AND a.time_update >
                     TO_DATE ('01.06.2009', 'DD.MM.YYYY'))
 LOOP                     
       BEGIN
       EXECUTE IMMEDIATE 'SELECT 1 time_info FROM '||j.tname||' WHERE x.ID = y.ID and y.ne = :cname'|| 
       ' AND y.TIME > TO_DATE (sysdate, ''DD.MM.YYYY HH24:MI:SS'') AND rownum = 1' USING i.cname INTO v_count;
       EXCEPTION 
       WHEN NO_DATA_FOUND THEN
            INSERT INTO elon.check_av_res values (sysdate,'has no data'); -- cname, table_name ????    
       END;
   END LOOP;       
END LOOP;
END;

Комментарии
1. Повыкидывал у Вас group by - не вижу пользы от них
2. Вам нужно узнать только существование / несуществование - выкидываем
group by из основного запроса и вставляем rownum = 1
3. Нужный Вам инсерт вставляет по исключению NO_DATA_FOUND
4. Ваш инсерт совершенно неконкретный - надо бы вставить в него название
таблицы и машины

Все надо дотачивать , но идея такая..
17 июл 09, 17:12    [7430566]     Ответить | Цитировать Сообщить модератору
 Re: Как узнать содержимое курсора ?  [new]
suPPLer
Member

Откуда: Харків, Україна
Сообщений: 7794
Блог
Castro men
Моя задача состоит в том, чтоб проверить есть ли в таблицах (тоесть здесь в переменной j) какие-то записи за вчерашний день для каждого из марок атомобилей. Если в какой-то из проверяемых таблиц нету данных по какому-то автомобилю, то нужно записать в таблицу check_av_res,
что "В таблице cool_table нету данных за 13,07,2009 для марки Ваз", проще говоря 3 поля в таблице с датой, именем таблицы и маркой авто.


Оттолкнёмся от этого.

Connected to Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 
Connected as ***
 
SQL> create table car_types(type_name varchar2(20) primary key);
 
Table created
 
SQL> insert into car_types (type_name) values('ВАЗ');
 
1 row inserted
 
SQL> insert into car_types (type_name) values('ГАЗ');
 
1 row inserted
 
SQL> insert into car_types (type_name) values('ТАЗ');
 
1 row inserted
 
SQL> create table car_params1(ddate date, car_type varchar2(20), param_val varchar2(20));
 
Table created
 
SQL> create table car_params2(ddate date, car_type varchar2(20), param_val varchar2(20));
 
Table created
 
SQL> insert into car_params1 (ddate, car_type, param_val) values(trunc(sysdate-1),'ВАЗ', '123');
 
1 row inserted
 
SQL> insert into car_params1 (ddate, car_type, param_val) values(trunc(sysdate-1),'ГАЗ', '567');
 
1 row inserted
 
SQL> insert into car_params1 (ddate, car_type, param_val) values(trunc(sysdate),'ТАЗ', '567');
 
1 row inserted
 
SQL> insert into car_params2 (ddate, car_type, param_val) values(trunc(sysdate-1),'ГАЗ', 'йцу');
 
1 row inserted
 
SQL> insert into car_params2 (ddate, car_type, param_val) values(trunc(sysdate-1),'ТАЗ', 'кен');
 
1 row inserted
 
SQL> create table car_param_tables (table_name varchar2(32));
 
Table created
 
SQL> insert into car_param_tables(table_name) values('car_params1');
 
1 row inserted
 
SQL> insert into car_param_tables(table_name) values('car_params2');
 
1 row inserted
 
SQL> commit;
 
Commit complete
 
SQL> create table check_car_params (ddate date, table_name varchar2(32), car_type varchar2(20));
 
Table created
 
SQL> declare
  2    procedure pr_check_car_params(p_date date)
  3    is
  4    begin
  5      for r_table in (select table_name from car_param_tables)
  6      loop
  7        execute immediate 'insert into check_car_params(ddate, table_name, car_type)
  8                           select :p_date, :p_table_name, type_name
  9                             from car_types ct
 10                            where not exists (select null from ' || r_table.table_name || ' cp
 11                                               where car_type = ct.type_name and ddate = :p_date)' using p_date, r_table.table_name, p_date;
 12      end loop;
 13    end;
 14  begin
 15    pr_check_car_params(trunc(sysdate-1));
 16    commit;
 17    pr_check_car_params(trunc(sysdate));
 18    commit;
 19  end;
 20  /
 
PL/SQL procedure successfully completed
 
SQL> select * from check_car_params;
 
DDATE       TABLE_NAME                       CAR_TYPE
----------- -------------------------------- --------------------
16.07.2009  car_params1                      ТАЗ
16.07.2009  car_params2                      ВАЗ
17.07.2009  car_params1                      ВАЗ
17.07.2009  car_params1                      ГАЗ
17.07.2009  car_params2                      ВАЗ
17.07.2009  car_params2                      ГАЗ
17.07.2009  car_params2                      ТАЗ
 
7 rows selected
 
SQL> 
17 июл 09, 17:15    [7430598]     Ответить | Цитировать Сообщить модератору
 Re: Как узнать содержимое курсора ?  [new]
Castro men
Member

Откуда:
Сообщений: 64
Wow, сразу 2 прохожих вариантов. Чесно признаюсь, юзал и using для подстановки переменных ни о в определённый момент почему-то выбросил их.
Спасибо огромное. Буду сейчас пробовать.
17 июл 09, 17:32    [7430733]     Ответить | Цитировать Сообщить модератору
 Re: Как узнать содержимое курсора ?  [new]
dmidek
Member

Откуда: Киев - Дортмунд
Сообщений: 116070
Castro men
Wow, сразу 2 прохожих вариантов. Чесно признаюсь, юзал и using для подстановки переменных ни о в определённый момент почему-то выбросил их.
Спасибо огромное. Буду сейчас пробовать.


ИМХО вариант Supplera более идейный. Просто в Вашей реальности в него надо
еще SELECT c cname пихать. Я как это увидел, понял, что "не осилю" :-)
и выбрал компромисс. Но повторюсь теоретически вполне можно и вариант Supplera
заточить до нужного Вам вида...
17 июл 09, 17:36    [7430753]     Ответить | Цитировать Сообщить модератору
 Re: Как узнать содержимое курсора ?  [new]
Goldminer
Member

Откуда:
Сообщений: 553
Вот это порадовало:
   
   ............... 
   FETCH c INTO foo;
   INSERT INTO elon.castro2 VALUES foo;
   dbms_output.put_line('Fetched');
   IF c%NOTFOUND
   ...............
т.е. сначала используем, потом проверяем, что вообще что-то получили...
17 июл 09, 18:05    [7430947]     Ответить | Цитировать Сообщить модератору
 Re: Как узнать содержимое курсора ?  [new]
Castro men
Member

Откуда:
Сообщений: 64
Нет, я пытался проверить что в курсоре лежало, чтобы оценить, посоветовали его залить в таблицу. Или как-то ещё можно просмотеть содержимое курсора?
17 июл 09, 18:23    [7431055]     Ответить | Цитировать Сообщить модератору
 Re: Как узнать содержимое курсора ?  [new]
Castro men
Member

Откуда:
Сообщений: 64
Балин, вариант Supplera отлично работает!!
Только я не понял, почему сначала инсерт, потом уж селект. Это такая фишка самого execute immediate ?
17 июл 09, 18:37    [7431144]     Ответить | Цитировать Сообщить модератору
 Re: Как узнать содержимое курсора ?  [new]
dmidek
Member

Откуда: Киев - Дортмунд
Сообщений: 116070
Castro men
Балин, вариант Supplera отлично работает!!
Только я не понял, почему сначала инсерт, потом уж селект. Это такая фишка самого execute immediate ?


А теперь бегите
17 июл 09, 18:40    [7431161]     Ответить | Цитировать Сообщить модератору
 Re: Как узнать содержимое курсора ?  [new]
Lecter
Member

Откуда: Киев
Сообщений: 2032
dmidek

А теперь бегите


Я б даже сказал копайте :)
17 июл 09, 18:55    [7431225]     Ответить | Цитировать Сообщить модератору
 Re: Как узнать содержимое курсора ?  [new]
Castro men
Member

Откуда:
Сообщений: 64
Взял лопату и ушёл копать:)
17 июл 09, 19:04    [7431262]     Ответить | Цитировать Сообщить модератору
 Re: Как узнать содержимое курсора ?  [new]
suPPLer
Member

Откуда: Харків, Україна
Сообщений: 7794
Блог
Castro men
Балин, вариант Supplera отлично работает!!
Только я не понял, почему сначала инсерт, потом уж селект. Это такая фишка самого execute immediate ?


Это "фишка" самого инсёрта и правильность подхода. Зачем вставлять по одной строке для каждой пары таблица-марка_машины, если можно проверить таблицу для всех марок машин сразу? Чем меньше циклов, тем быстрее будет... :) Ну и так, на всякий: RTFM INSERT.
17 июл 09, 19:15    [7431303]     Ответить | Цитировать Сообщить модератору
Все форумы / Oracle Ответить