Модераторы: LSD

Поиск:

Ответ в темуСоздание новой темы Создание опроса
> Определить наличие данных в таблицах 
:(
    Опции темы
Castro
Дата 22.5.2009, 17:54 (ссылка) | (нет голосов) Загрузка ... Загрузка ... Быстрая цитата Цитата


Опытный
**


Профиль
Группа: Участник
Сообщений: 353
Регистрация: 31.8.2007
Где: Украина

Репутация: нет
Всего: 5
Привет всем.
Появилась надобность ежедневно проверять наличие данный в таблицах базы. Но не во всех, а в определённых и этот список будет хранится в отдельной таблице.

Получить список таблиц которые будеи проверять легко, но я не могу понять как написать процедуру которая бы подставляла этот список в запрос проверки данных. Только начал учить PL/SQL. Это цикл какой-то дожен быть? 


--------------------
user posted image
PM MAIL ICQ GTalk   Вверх
ToshaCh
Дата 22.5.2009, 18:55 (ссылка) | (нет голосов) Загрузка ... Загрузка ... Быстрая цитата Цитата


Опытный
**


Профиль
Группа: Участник
Сообщений: 555
Регистрация: 10.11.2005
Где: Москва, РФ

Репутация: 12
Всего: 26
Если просто нужно узнать количество строк то можно воспользоваться вьюхой all_tables (поле num_rows). Но! для этого статистика по таблице должна быть актуальной.

Если же нужно узнавать наличие каких-то определённых данных, то можно воспользоваться динамическим sql:
http://mab.h1.ru/OTHER/dplsql.html


Это сообщение отредактировал(а) ToshaCh - 22.5.2009, 19:05


--------------------
Slackware 12.2 | Linux 2.6.27 | Fluxbox 1.1.1 | Wmii 3 | Opera 9.63 
--
Oracle это не только способ отмывания денег, но и вполне себе преличная база данных.
PM MAIL Jabber   Вверх
Castro
Дата 25.5.2009, 11:10 (ссылка) | (нет голосов) Загрузка ... Загрузка ... Быстрая цитата Цитата


Опытный
**


Профиль
Группа: Участник
Сообщений: 353
Регистрация: 31.8.2007
Где: Украина

Репутация: нет
Всего: 5
Чёрт, не работает ваша ссылка но похоже, что динамический sql - это оно. Но всё равно хотелось бы конкретных советов от человека, который занимался динамическим sql-ом. Я нашёл всего пару примеров но они какие-то неявные, а очень хочется и нужно разобраться во всём этом.
Хотелось бы начать с простого.

Например, есть 2 таблицы a и b, к обоих есть поле time_info хранящее дату. Запросом узнаю, есть ли какоя-то запись в таблице за сегодня.
Если нет -  пишу в таблицу result , например, дату и название таблицы с отсутствующей датой. 

Как сделать такую схему с помощью динамического запроса?


--------------------
user posted image
PM MAIL ICQ GTalk   Вверх
ToshaCh
Дата 25.5.2009, 13:14 (ссылка) | (нет голосов) Загрузка ... Загрузка ... Быстрая цитата Цитата


Опытный
**


Профиль
Группа: Участник
Сообщений: 555
Регистрация: 10.11.2005
Где: Москва, РФ

Репутация: 12
Всего: 26
Странно я щас по сслыке перешёл, походу она перманентно оживает.

Твой пример можно разобрать где-то вот так. (не судите строго написал за 5 минут, мимоходом левой задней ногой) smile
Код

 create table a (time_info date);
 create table b (time_info date);

--Таблица с именами исследуемых таблиц
 create table c (table_name varchar(100));

--Заполняем таблицы хламом
 insert into a select created from all_objects;
 insert into b select created-1 from all_objects;

--Заполняем имена
 insert into c (table_name) values ('a');
 insert into c (table_name) values ('b');

set serveroutput on;

declare
    stmt                varchar(1000);
    stmt_first_part     varchar(1000)   := 'select count(*) from ';
    stmt_last_part      varchar(1000)   := ' where time_info>=trunc(sysdate)';
    cur                 integer;            
    ret                 integer;
    cnt                 integer;                
begin 
    for r in (select table_name from c)
    loop
        --Компонуем наш селект в строковую переменную
        stmt:=stmt_first_part||r.table_name||stmt_last_part;

        --Создаём под него курсор
        cur := dbms_sql.open_cursor;

        --Парсим наш селект для курсора. Третий параметр это так сказать спецификация диалекта sql с которого надо парсить.
        dbms_sql.parse(cur, stmt, DBMS_SQL.NATIVE);

        --Связываем номер вовращаемого столбца с необходимой нам переменной
        dbms_sql.define_column(cur,1,cnt);

        --Выполняем запрос (читай курсор)
        ret := dbms_sql.execute(cur);

        if  dbms_sql.fetch_rows(cur) > 0   then
    --Если в результате что-то получили, то вытаскиваем этот результат в переменную cnt
         dbms_sql.column_value(cur,1,cnt);

             if cnt>0 then 
           --Если количество больше 0 (а путем не сложных соображений понятно что это правда для таблицы 'a')
           --то радостно сообщаем об этом миру!!!
                       dbms_output.put_line('Table '||r.table_name||' has data for today');
             else 
           --Если нет, то грустно говорим правду. :)
                       dbms_output.put_line('Table '||r.table_name||' has no data for today');
             end if;
        end if;

        --Закрываем курсор;
        dbms_sql.close_cursor(cur);
    end loop;
end;
/


В результате:
Цитата

Table a has data for today
Table b has no data for today


Добавлено через 2 минуты и 44 секунды
З.Ы. Ну уж со вставкой в таблицу result думаю и сам разберёшься. smile


--------------------
Slackware 12.2 | Linux 2.6.27 | Fluxbox 1.1.1 | Wmii 3 | Opera 9.63 
--
Oracle это не только способ отмывания денег, но и вполне себе преличная база данных.
PM MAIL Jabber   Вверх
Castro
Дата 25.5.2009, 13:59 (ссылка) | (нет голосов) Загрузка ... Загрузка ... Быстрая цитата Цитата


Опытный
**


Профиль
Группа: Участник
Сообщений: 353
Регистрация: 31.8.2007
Где: Украина

Репутация: нет
Всего: 5
Так-так. А вот это мне уже нравитсяsmile  Щас буду разбиратся, и вопросы задавать. Спасибо smile 


--------------------
user posted image
PM MAIL ICQ GTalk   Вверх
Castro
Дата 25.5.2009, 15:11 (ссылка) | (нет голосов) Загрузка ... Загрузка ... Быстрая цитата Цитата


Опытный
**


Профиль
Группа: Участник
Сообщений: 353
Регистрация: 31.8.2007
Где: Украина

Репутация: нет
Всего: 5
Так. 
Теперь чтобы дать гибкости, нужно передавать как параметр проверять наличии значений ещё по одному полю в некоторых таблицах (По идее будет так - в таблице С лежат список таблиц(как сделали Вы), а таблице D - список полей каждой из этих таблиц, таблицы связаны по id (c.id = d.id)).
Как научить процедуру принимать внешние параметры, или же можно сделать как-то изнутри всё это, как это сделали Вы?

Попробовал вот такОбернул всё это дело в процедуру

Код

CREATE OR REPLACE PROCEDURE checkData(ne in varchar2)

is

declare
    stmt                varchar(1000);
    stmt_first_part     varchar(1000)   := 'select count(*) from ';
    stmt_last_part      varchar(1000)   := ' where time_info>=trunc(sysdate)';
    stmt_param_2     varchar(1000)  : = ' and' ||ne|| 'is NOT NULL '

    cur                 integer;            
    ret                 integer;
    cnt                 integer;                
begin 
    for r in (select table_name from c)
        stmt:=stmt_first_part||r.table_name||stmt_last_part;
           IF stmt_param_2 IS NOT NULL 
              stmt := stmt||stmt_param_2
           END IF
....



--------------------
user posted image
PM MAIL ICQ GTalk   Вверх
ToshaCh
Дата 25.5.2009, 15:24 (ссылка) | (нет голосов) Загрузка ... Загрузка ... Быстрая цитата Цитата


Опытный
**


Профиль
Группа: Участник
Сообщений: 555
Регистрация: 10.11.2005
Где: Москва, РФ

Репутация: 12
Всего: 26
Цитата(Castro @  25.5.2009,  15:11 Найти цитируемый пост)
declare

declare здесь не надо

Цитата(Castro @  25.5.2009,  15:11 Найти цитируемый пост)
' and' ||ne|| 'is NOT NULL '

Пробелов не хватает - синтаксическая ошибка будет.

А вот в чём вопрос я не понял.  smile 
Сейчас у тебя получилось, что для всех таблиц будет один и тот же вариант дополнительного поля, зависящий от того как ты вызовешь проседуру.



--------------------
Slackware 12.2 | Linux 2.6.27 | Fluxbox 1.1.1 | Wmii 3 | Opera 9.63 
--
Oracle это не только способ отмывания денег, но и вполне себе преличная база данных.
PM MAIL Jabber   Вверх
Castro
Дата 25.5.2009, 15:31 (ссылка) | (нет голосов) Загрузка ... Загрузка ... Быстрая цитата Цитата


Опытный
**


Профиль
Группа: Участник
Сообщений: 353
Регистрация: 31.8.2007
Где: Украина

Репутация: нет
Всего: 5
Идея в том что есть, как Вы создали уже, таблица С, где будет хранится список исследуемых таблиц, и будет ещё связанная с ней таблица D, в которой будут хранится имена полей какждой из исследуемых таблиц. Количество полей во всех таблицах разное, для упрощения скажем что их будет 10 (точно сам пока не знаю). Эти таблицы связаны по id. 
Мне нужно чтобы процедура брала с таблицы С имя исследуемой таблицы, и в WHERE проверяла связаныне на пустоту поля из таблицы D.


--------------------
user posted image
PM MAIL ICQ GTalk   Вверх
ToshaCh
Дата 25.5.2009, 15:41 (ссылка) | (нет голосов) Загрузка ... Загрузка ... Быстрая цитата Цитата


Опытный
**


Профиль
Группа: Участник
Сообщений: 555
Регистрация: 10.11.2005
Где: Москва, РФ

Репутация: 12
Всего: 26
Ну тогда как-нить так:
Код

for r in (select id,table_name from c)
    loop
    stmt_param:=null;
    for r1 in (select ' and '||column_name||' is null' as param from d where d.id=r.id)
    loop
        stmt_param:=stmt_param||r1.param;
    end loop;
    stmt := stmt||stmt_param;



--------------------
Slackware 12.2 | Linux 2.6.27 | Fluxbox 1.1.1 | Wmii 3 | Opera 9.63 
--
Oracle это не только способ отмывания денег, но и вполне себе преличная база данных.
PM MAIL Jabber   Вверх
Castro
Дата 25.5.2009, 16:07 (ссылка) | (нет голосов) Загрузка ... Загрузка ... Быстрая цитата Цитата


Опытный
**


Профиль
Группа: Участник
Сообщений: 353
Регистрация: 31.8.2007
Где: Украина

Репутация: нет
Всего: 5
Похоже на правду. smile 
 Правда, не понял строчку 4
Код

select ' and '||column_name||' is null'

Что должно по идее вернуть 
Код

select ' and '||column_name||' is null' as param from d where d.id=r.id

?


--------------------
user posted image
PM MAIL ICQ GTalk   Вверх
ToshaCh
Дата 25.5.2009, 16:11 (ссылка) | (нет голосов) Загрузка ... Загрузка ... Быстрая цитата Цитата


Опытный
**


Профиль
Группа: Участник
Сообщений: 555
Регистрация: 10.11.2005
Где: Москва, РФ

Репутация: 12
Всего: 26
Ну дык! 
Цитата

and ia_colonko is null
and ia_drugoe_colonko is null



--------------------
Slackware 12.2 | Linux 2.6.27 | Fluxbox 1.1.1 | Wmii 3 | Opera 9.63 
--
Oracle это не только способ отмывания денег, но и вполне себе преличная база данных.
PM MAIL Jabber   Вверх
Castro
Дата 25.5.2009, 16:47 (ссылка) | (нет голосов) Загрузка ... Загрузка ... Быстрая цитата Цитата


Опытный
**


Профиль
Группа: Участник
Сообщений: 353
Регистрация: 31.8.2007
Где: Украина

Репутация: нет
Всего: 5
Нет, меня смутило то, что из 
Код

select ' and '||column_name||' is null' as param from d where d.id=r.id

я не понял откуда возьмётся переменная column_name ?

А строка 4, когда подставиться имя колонки, например 
Код

select ' and ia_colonko is null' as param from d where d.id=r.id
 - это не ошибка?


--------------------
user posted image
PM MAIL ICQ GTalk   Вверх
ToshaCh
Дата 25.5.2009, 16:52 (ссылка) | (нет голосов) Загрузка ... Загрузка ... Быстрая цитата Цитата


Опытный
**


Профиль
Группа: Участник
Сообщений: 555
Регистрация: 10.11.2005
Где: Москва, РФ

Репутация: 12
Всего: 26
Это не переменная это поле таблицы d


--------------------
Slackware 12.2 | Linux 2.6.27 | Fluxbox 1.1.1 | Wmii 3 | Opera 9.63 
--
Oracle это не только способ отмывания денег, но и вполне себе преличная база данных.
PM MAIL Jabber   Вверх
Castro
Дата 25.5.2009, 17:06 (ссылка) | (нет голосов) Загрузка ... Загрузка ... Быстрая цитата Цитата


Опытный
**


Профиль
Группа: Участник
Сообщений: 353
Регистрация: 31.8.2007
Где: Украина

Репутация: нет
Всего: 5
Гляньте, пожалуйста, ещё раз на конечный вариант процедуры и проверте всё ли на месте и ничего я не напутал ли. Идею я понял, но мог что-то напутать с реализацией  smile 

Код

CREATE OR REPLACE PROCEDURE checkData()
    stmt                varchar(1000);
    -- Я так понимаю, это уже не нужно будет?
    --stmt_first_part     varchar(1000)   := 'select count(*) from ';
    --stmt_last_part      varchar(1000)   := ' where time_info>=trunc(sysdate)';
    cur                 integer;            
    ret                 integer;
    cnt                 integer;                
begin 
    for r in (select id,table_name from c)
    loop
    stmt_param:=null;
    for r1 in (select ' and '||column_name||' is null' as param from d where d.id=r.id)
    loop
        stmt_param:=stmt_param||r1.param;
    end loop;
     --Компонуем наш селект в строковую переменную
    stmt := stmt||stmt_param;

    --Создаём под него курсор
        cur := dbms_sql.open_cursor;
        --Парсим наш селект для курсора. Третий параметр это так сказать спецификация диалекта sql с которого надо парсить.
        dbms_sql.parse(cur, stmt, DBMS_SQL.NATIVE);
        --Связываем номер вовращаемого столбца с необходимой нам переменной
        dbms_sql.define_column(cur,1,cnt);
        --Выполняем запрос (читай курсор)
        ret := dbms_sql.execute(cur);
        if  dbms_sql.fetch_rows(cur) > 0   then
    --Если в результате что-то получили, то вытаскиваем этот результат в переменную cnt
         dbms_sql.column_value(cur,1,cnt);
             if cnt>0 then 
           --Если количество больше 0 (а путем не сложных соображений понятно что это правда для таблицы 'a')
           --то радостно сообщаем об этом миру!!!
                       dbms_output.put_line('Table '||r.table_name||' has data for today');
             else 
           --Если нет, то грустно говорим правду. :)
                       dbms_output.put_line('Table '||r.table_name||' has no data for today');
             end if;
        end if;
        --Закрываем курсор;
        dbms_sql.close_cursor(cur);
    end loop;
end;
/



--------------------
user posted image
PM MAIL ICQ GTalk   Вверх
ToshaCh
Дата 25.5.2009, 17:13 (ссылка) | (нет голосов) Загрузка ... Загрузка ... Быстрая цитата Цитата


Опытный
**


Профиль
Группа: Участник
Сообщений: 555
Регистрация: 10.11.2005
Где: Москва, РФ

Репутация: 12
Всего: 26
Цитата(Castro @  25.5.2009,  17:06 Найти цитируемый пост)
-- Я так понимаю, это уже не нужно будет?

С чего это? Представь что там получается в переменной stmt сейчас?


--------------------
Slackware 12.2 | Linux 2.6.27 | Fluxbox 1.1.1 | Wmii 3 | Opera 9.63 
--
Oracle это не только способ отмывания денег, но и вполне себе преличная база данных.
PM MAIL Jabber   Вверх
Castro
Дата 25.5.2009, 17:23 (ссылка) | (нет голосов) Загрузка ... Загрузка ... Быстрая цитата Цитата


Опытный
**


Профиль
Группа: Участник
Сообщений: 353
Регистрация: 31.8.2007
Где: Украина

Репутация: нет
Всего: 5
Ммм.... Это да. Но условие where  ведь дальше выстраиваться будет, значит так?

Код

stmt                varchar(1000);
stmt_first_part     varchar(1000)   := 'select count(*) from ';
stmt_last_part      varchar(1000)   := ' where ';
    cur                 integer;            
    ret                 integer;
    cnt                 integer;                
begin 
    for r in (select id,table_name from c)
    loop
    stmt:=stmt_first_part||r.table_name||stmt_last_part;
    stmt_param:=null;
       for r1 in (select ' and '||column_name||' is null' as param from d where d.id=r.id)
       loop
          stmt_param:=stmt_param||r1.param;
      end loop;
....



--------------------
user posted image
PM MAIL ICQ GTalk   Вверх
ToshaCh
Дата 25.5.2009, 17:35 (ссылка) | (нет голосов) Загрузка ... Загрузка ... Быстрая цитата Цитата


Опытный
**


Профиль
Группа: Участник
Сообщений: 555
Регистрация: 10.11.2005
Где: Москва, РФ

Репутация: 12
Всего: 26
Мдя а вот тут проблема. Я предполагал, что будет одно статичекое условие и n динамических: щас вообще ерунда получается: 
where and .....

Это надо исправлять.




--------------------
Slackware 12.2 | Linux 2.6.27 | Fluxbox 1.1.1 | Wmii 3 | Opera 9.63 
--
Oracle это не только способ отмывания денег, но и вполне себе преличная база данных.
PM MAIL Jabber   Вверх
ToshaCh
Дата 25.5.2009, 17:51 (ссылка) | (голосов:1) Загрузка ... Загрузка ... Быстрая цитата Цитата


Опытный
**


Профиль
Группа: Участник
Сообщений: 555
Регистрация: 10.11.2005
Где: Москва, РФ

Репутация: 12
Всего: 26
Наверное так.
Код

for r in (select id,table_name from c)
loop
       stmt_param:=null;
       first_element:=true;--защёлка для первого элемента
       for r1 in (select column_name||' is null' as param from d where d.id=r.id)
       loop
            if first_element then
                  first_element:=false;
                  stmt_param:=' where '||r1.param;
            else
                  stmt_param:=stmt_param||' and '||r1.param;
            end if;
       end loop;
       stmt:=stmt_first_part||r.table_name||stmt_param;



--------------------
Slackware 12.2 | Linux 2.6.27 | Fluxbox 1.1.1 | Wmii 3 | Opera 9.63 
--
Oracle это не только способ отмывания денег, но и вполне себе преличная база данных.
PM MAIL Jabber   Вверх
Castro
Дата 25.5.2009, 17:59 (ссылка) | (нет голосов) Загрузка ... Загрузка ... Быстрая цитата Цитата


Опытный
**


Профиль
Группа: Участник
Сообщений: 353
Регистрация: 31.8.2007
Где: Украина

Репутация: нет
Всего: 5
А, вот теперь я понял почему такая конструкция была 
Код

select ' and '||column_name||'
 , потому что было ж статичное поле time_info.

+1 вам в карму! smile 
Спасибо огромное за помощь, буду докручивать завтра, уже сегодня голова не варит.




--------------------
user posted image
PM MAIL ICQ GTalk   Вверх
Zloxa
Дата 26.5.2009, 09:58 (ссылка) | (нет голосов) Загрузка ... Загрузка ... Быстрая цитата Цитата


Чо?
****


Профиль
Группа: Завсегдатай
Сообщений: 3473
Регистрация: 12.9.2008

Репутация: 37
Всего: 161
ToshaCh, 

1) dbms_sql тут лишний, без него, ИМХО проще.
2) для того чтобы проверить наличие, считать count() это оверхед, достаточно лишь одного фетча

Код

set echo on;
set serveroutput on;
create table test1 (time_info date);
 
Table created
create table test2 (time_info date);
 
Table created
insert into test2 values(trunc(sysdate));
 
1 row inserted
create table src_table_info as
  select 'test1' table_name,'time_info' field_name from dual
  union all select 'test2','time_info' from dual;
 
Table created
declare
 foo dual%rowtype;
 c sys_refcursor;
begin
  for i in (select *  from src_table_info)
  loop
    open c for 'select /*+first_rows(1)*/ ''X''' -- first_rows, подсказывает оптимизатору,
                                              -- что нужно выбрать план позволяющий нам
                                              -- максимально быстро выбрать первую строку
                                              -- Однако чудес не бывает, при отсутствии
                                              -- индекса по критерию отбора, это ни на что не повлияет
               ||' from '||i.table_name
               ||' where '||i.field_name||'=:val'
               ||' and rownum = 1'-- Эта строка, при наличии хинта first_rows - лишняя
                                  -- если хинта не стоит, цель ее присутствия таже что у хинта
           using trunc(sysdate);
    fetch c into foo;
    if c%notfound then
      dbms_output.put_line(i.table_name|| ' has no data');
    else
      dbms_output.put_line(i.table_name|| ' has some data');
    end if;
    close c;
  end loop;
end;
/
 
test1 has no data
test2 has some data
 
PL/SQL procedure successfully completed
 


Это сообщение отредактировал(а) Zloxa - 19.6.2009, 10:46


--------------------
Достоверно известно, что 89% людей доверяют статистике взятой с потолка smile
PM   Вверх
ToshaCh
Дата 26.5.2009, 13:12 (ссылка) | (нет голосов) Загрузка ... Загрузка ... Быстрая цитата Цитата


Опытный
**


Профиль
Группа: Участник
Сообщений: 555
Регистрация: 10.11.2005
Где: Москва, РФ

Репутация: 12
Всего: 26
О! Никогда не видел, чтобы курсор та компоновали (в смысле как строку). Спасибо.


--------------------
Slackware 12.2 | Linux 2.6.27 | Fluxbox 1.1.1 | Wmii 3 | Opera 9.63 
--
Oracle это не только способ отмывания денег, но и вполне себе преличная база данных.
PM MAIL Jabber   Вверх
Zloxa
Дата 27.5.2009, 11:07 (ссылка) | (нет голосов) Загрузка ... Загрузка ... Быстрая цитата Цитата


Чо?
****


Профиль
Группа: Завсегдатай
Сообщений: 3473
Регистрация: 12.9.2008

Репутация: 37
Всего: 161
Цитата(ToshaCh @  26.5.2009,  13:12 Найти цитируемый пост)
О! Никогда не видел, чтобы курсор та компоновали

Весьма странно, обычно для народа dbms_sql - открытие smile)

Раз так - Рекомендую


--------------------
Достоверно известно, что 89% людей доверяют статистике взятой с потолка smile
PM   Вверх
Castro
Дата 27.5.2009, 11:29 (ссылка) | (нет голосов) Загрузка ... Загрузка ... Быстрая цитата Цитата


Опытный
**


Профиль
Группа: Участник
Сообщений: 353
Регистрация: 31.8.2007
Где: Украина

Репутация: нет
Всего: 5
А вот теперь нужен совет, потому что правила игры как оказалось уже изменились.
Оказалось, что таблицы разные и в основном они связаны друг с другом по айдишникам, что вполне понятно, ну конечно что же поле даты присутсвует только в одной из таблиц. По сути элементарная связаная выборка получается. Запросы будут меняться вероятно?
В чём вопрос: как это всё хранить теперь? Создать одну таблицу с полями table_name и query_str,  где хранить сами условия, и подставлять в цикле в шаблон
Код

Select * from '||table_name|| ' where '||query_str|| ' and time_info > trunc(sysdate)-1 and  rownum = 1
 ?

Или, возможно, есть смысл хранить толко названия полей, по которым связаны таблицы?

 
Или есть какое мудрое решение и в тоже время гибкое?

Это сообщение отредактировал(а) Castro - 27.5.2009, 11:41


--------------------
user posted image
PM MAIL ICQ GTalk   Вверх
Zloxa
Дата 27.5.2009, 12:47 (ссылка) | (нет голосов) Загрузка ... Загрузка ... Быстрая цитата Цитата


Чо?
****


Профиль
Группа: Завсегдатай
Сообщений: 3473
Регистрация: 12.9.2008

Репутация: 37
Всего: 161
Цитата(Castro @  27.5.2009,  11:29 Найти цитируемый пост)
Создать одну таблицу с полями table_name и query_str

Думаю так правильнее
Цитата(Castro @  27.5.2009,  11:29 Найти цитируемый пост)
Или есть какое мудрое решение и в тоже время гибкое?


Любая гибкость достигается, как правило, за счет потери прочности.
Я за железобетонные решения.
По сему, я не рекомендовал бы хранить запросы в данных. В любом случае, при добавлении таблицы нужно писать запрос. Запрос должен писать программист, не пользователь. Меньше шансов ошибиться у программиста будет, если он будет редактировать текст ХП, которая, будет еще на этапе компиляции проверяться.
От того бросайте вы это баловство с универсальными гибкими решениями, пракитика показывает, что ни к чему хорошему они не приводят. Автомобиль амфибия плохо ездиит и плохо плавает, от того их производят так мало и лишь по частным заказам.

Это сообщение отредактировал(а) Zloxa - 27.5.2009, 12:48


--------------------
Достоверно известно, что 89% людей доверяют статистике взятой с потолка smile
PM   Вверх
Castro
Дата 18.6.2009, 18:48 (ссылка) | (нет голосов) Загрузка ... Загрузка ... Быстрая цитата Цитата


Опытный
**


Профиль
Группа: Участник
Сообщений: 353
Регистрация: 31.8.2007
Где: Украина

Репутация: нет
Всего: 5
Появилось вот время занятся сново с эти заданием и уже почти но всё ещё не всё

Код

stmt   VARCHAR2 (250);
stmt1  VARCHAR2 (250);
   foo    DUAL%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);
      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('i: '||i.cname);

-- Собираю в кучу курсор, чтобы записать его в дебаг-таблицу
    DBG('cursor query: SELECT trunc (y.TIME,''hh24'') time_info FROM '||j.tname||' WHERE x.ID = y.ID ' ||
      'AND y.TIME > TO_DATE (sysdate, ''DD.MM.YYYY HH24:MI:SS'')-1 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.name
                 ||'' AND y.TIME > TO_DATE (''14.06.2009'', ''DD.MM.YYYY HH24:MI:SS'') and y.TIME > TO_DATE (''15.06.2009'', ''DD.MM.YYYY HH24:MI:SS'')-1'
                    || 'group by trunc (y.TIME,''hh24'') '
                    || 'order by trunc (y.TIME,''hh24'') ';

         FETCH c
          INTO foo;

   stmt1 := 'insert into elon.castro values (c)';
        EXECUTE IMMEDIATE stmt1;

         IF c%NOTFOUND
         THEN
            stmt :=
               'INSERT INTO elon.check_av_res values (to_date('' || foo.time_info || '',''DD.MM.YYYY HH:MI:SS''), '''||i.cname||' has no data'')';
         END IF;


Вроде всё ок. Но помоему не приходит ничсего в курсор, потому что постоянно идёт вставка  "has no data" и не может получить foo.time_info (мож там ксатати чёто с кавычками напутал).

DBG - функция дебага, просто пишет в табличку переменную нужную.

DBG('j: '||j.tname) вернуло правильно, DBG('cursor query: SELEC...) - тоже правильный запрос, который если скопировать из таблицы и выполнить, то выполнится правильно.
Но 
Код

stmt1 := 'insert into elon.castro values (c)';

возвращает пустоту в таблице и соотвественно всегда срабатывает 
Код

IF c%NOTFOUND


Oracle не умеет работать со вложенными циклами? Или в чём может быить причина? Как ещё можно отдебажить курсор. Работаю в SQL Navigator и все приблуды c SQL * plus к сожалению не могу испытать (или не знаю просто как это сделать :(( )


--------------------
user posted image
PM MAIL ICQ GTalk   Вверх
Zloxa
Дата 18.6.2009, 18:58 (ссылка) | (нет голосов) Загрузка ... Загрузка ... Быстрая цитата Цитата


Чо?
****


Профиль
Группа: Завсегдатай
Сообщений: 3473
Регистрация: 12.9.2008

Репутация: 37
Всего: 161
Это не код это бред.
Код

   stmt1 := 'insert into elon.castro values (c)';
        EXECUTE IMMEDIATE stmt1;

Должно поднимать эксепшн.

Код

        stmt :=
               'INSERT INTO elon.check_av_res values (to_date('' || foo.time_info || '',''DD.MM.YYYY HH:MI:SS''), '''||i.cname||' has no data'')';

если попытаться выполнить это, тоже должен быть эксепшн.


--------------------
Достоверно известно, что 89% людей доверяют статистике взятой с потолка smile
PM   Вверх
Castro
Дата 19.6.2009, 10:04 (ссылка) | (нет голосов) Загрузка ... Загрузка ... Быстрая цитата Цитата


Опытный
**


Профиль
Группа: Участник
Сообщений: 353
Регистрация: 31.8.2007
Где: Украина

Репутация: нет
Всего: 5
Код

stmt1 := 'insert into elon.castro values (c)';
        EXECUTE IMMEDIATE stmt1;

это бред - я это понял сразу, но эксепшна не вываливается. Как парвильно встать в таблицу содержмое курсора?
Код


 stmt :=
               'INSERT INTO elon.check_av_res values (to_date('' || foo.time_info || '',''DD.MM.YYYY HH:MI:SS''), '''||i.cname||' has no data'')'; 

 а что здесь не так?

Это сообщение отредактировал(а) Castro - 19.6.2009, 10:09


--------------------
user posted image
PM MAIL ICQ GTalk   Вверх
Zloxa
Дата 19.6.2009, 10:41 (ссылка) | (нет голосов) Загрузка ... Загрузка ... Быстрая цитата Цитата


Чо?
****


Профиль
Группа: Завсегдатай
Сообщений: 3473
Регистрация: 12.9.2008

Репутация: 37
Всего: 161
Цитата(Castro @  19.6.2009,  10:04 Найти цитируемый пост)
Как парвильно встать в таблицу содержмое курсора?

Код

SQL> create table target as select dummy d1,dummy d2 from dual;
 
Table created
SQL> declare
  2    c sys_refcursor;
  3    target_row target%rowtype;
  4  begin
  5    open c for 'select dummy,dummy from dual';
  6    fetch c into target_row;
  7    insert into target values target_row;
  8  end;
  9  /
 
PL/SQL procedure successfully completed
SQL> select * from target;
 
D1 D2
-- --
X  X
X  X



Цитата(Castro @  19.6.2009,  10:04 Найти цитируемый пост)
 а что здесь не так?

считай кавычки, ну или хотябы смотри на то как подсвечен синтаксис.



--------------------
Достоверно известно, что 89% людей доверяют статистике взятой с потолка smile
PM   Вверх
Castro
Дата 19.6.2009, 11:08 (ссылка) | (нет голосов) Загрузка ... Загрузка ... Быстрая цитата Цитата


Опытный
**


Профиль
Группа: Участник
Сообщений: 353
Регистрация: 31.8.2007
Где: Украина

Репутация: нет
Всего: 5
Спасибо. Использовал Вашу процедуру, но она не успела отработатьsmile 

Так, упростил всё что только можно.

Код

stmt   VARCHAR2 (250);
   foo    DUAL%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);
      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);
      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'')-1 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'')-1 group by trunc (y.TIME,''hh24'') order by trunc (y.TIME,''hh24'')';

         FETCH c INTO foo;

В цикле делается один проход (в дебаге видны данные) и всё валится на строке 
Код

FETCH c INTO foo;

с ошибкой Error executing statement: ORA-06502: PL/SQL: numeric or value error
Погуглил и нашёл что http://www.mydatabasesupport.com/ora-06502...alue-error.html
Это попытка записать большой объём данных в переменную foo, больший чем оно может вместить? В смысле в DUAL не может влезть? Илия ошибаюсь? Как это побороть?


--------------------
user posted image
PM MAIL ICQ GTalk   Вверх
Zloxa
Дата 19.6.2009, 11:20 (ссылка) | (нет голосов) Загрузка ... Загрузка ... Быстрая цитата Цитата


Чо?
****


Профиль
Группа: Завсегдатай
Сообщений: 3473
Регистрация: 12.9.2008

Репутация: 37
Всего: 161
Цитата(Castro @  19.6.2009,  11:08 Найти цитируемый пост)
ORA-06502

структура переменной foo не соответствует структуре, возвращенной курсором.
Вы в переменную типа чар(1) пытаеесь впихнуть дату


--------------------
Достоверно известно, что 89% людей доверяют статистике взятой с потолка smile
PM   Вверх
Castro
Дата 19.6.2009, 11:31 (ссылка) | (нет голосов) Загрузка ... Загрузка ... Быстрая цитата Цитата


Опытный
**


Профиль
Группа: Участник
Сообщений: 353
Регистрация: 31.8.2007
Где: Украина

Репутация: нет
Всего: 5
Спасибо большое. 
Глаз замылился уже, пока пишу пост, в голове решаю задачу, потом отправляю пост и за 10 секунд решаю проблемму smile)
Хотел ещё по поводу кавычек спросить - иногда применяются 2 кавычки, а иногда 3. 
2 кавычки - это чтобы экарнировать одну кавычку при вложенности, а когда применяют 3?

Это сообщение отредактировал(а) Castro - 19.6.2009, 11:32


--------------------
user posted image
PM MAIL ICQ GTalk   Вверх
Zloxa
Дата 19.6.2009, 11:57 (ссылка) | (нет голосов) Загрузка ... Загрузка ... Быстрая цитата Цитата


Чо?
****


Профиль
Группа: Завсегдатай
Сообщений: 3473
Регистрация: 12.9.2008

Репутация: 37
Всего: 161
Цитата(Castro @  19.6.2009,  11:31 Найти цитируемый пост)
а когда применяют 3

3=2+1
т.е. кавычка внутри строки+закрывающая кавычка

Добавлено через 2 минуты и 4 секунды
Цитата(Castro @  19.6.2009,  11:31 Найти цитируемый пост)
отправляю пост и за 10 секунд решаю проблемму

Это говорит лишь о том, что Вы сначала спрашиваете а потом думаете, что не делает Вам чести.


--------------------
Достоверно известно, что 89% людей доверяют статистике взятой с потолка smile
PM   Вверх
Castro
Дата 19.6.2009, 12:47 (ссылка) | (нет голосов) Загрузка ... Загрузка ... Быстрая цитата Цитата


Опытный
**


Профиль
Группа: Участник
Сообщений: 353
Регистрация: 31.8.2007
Где: Украина

Репутация: нет
Всего: 5
Согласен, но:
1) Это для меня новое поле деятельности и не у кого спросить так чтобы быстро получить вразумительный ответ.
2) Правилоно сформулированый вопрос - это уже половина ответа. Формулирую вопрос я обдумаваю проблемму. Значит нужно научится заставлять себя писать суть вопроса но не отправлять его на форум. smile

В любом случае спасибо. Процедура заработала и гребёт данные.
+1 вам в карму!


--------------------
user posted image
PM MAIL ICQ GTalk   Вверх
Страницы: (3) [Все] 1 2 3 
 Ответ в темуСоздание новой темы Создание опроса
Правила форума "Oracle"
Zloxa
LSD

Данный раздел предназначен для обсуждения проблем с Oracle Database, другие продукты Oracle здесь не обсуждаются. Просьба при создании темы, придерживаться следующих правил:

  • при создании темы давайте ей осмысленное название, описывающее суть проблемы
  • указывайте используемую версию базы, способ соединения и язык программирования
  • при ошибках обязательно приводите код ошибки и сообщение сервера
  • приводите код в котором возникла ошибка, по возможности дайте тестовый пример демонстрирующий ошибку
  • при вставке кода используйте соответсвующие теги: [code=sql] [/code] для подсветки SQL и PL/SQL кода, [code=java] [/code] - для Java, и т.д.
  • документация по Oracle: 9i, 10g, 11g
  • книги по Oracle можно поискать здесь
  • действия модераторов можно обсудить здесь

Если Вам понравилась атмосфера форума, заходите к нам чаще! С уважением, Zloxa, LSD.
 
0 Пользователей читают эту тему (0 Гостей и 0 Скрытых Пользователей)
0 Пользователей:
« Предыдущая тема | Oracle | Следующая тема »

Подписаться на тему | Подписка на этот форум | Скачать/Распечатать тему


 




[ Время генерации скрипта: 0.0837 ]   [ Использовано запросов: 22 ]   [ GZIP включён ]


Реклама на сайте     Информационное спонсорство

 
 По вопросам размещения рекламы пишите на vladimir(sobaka)vingrad.ru
Отказ от ответственности     Powered by Invision Power Board(R) 1.3 © 2003  IPS, Inc.