Версия для печати темы Нажмите сюда для просмотра этой темы в оригинальном формате

Форум программистов > Oracle > Определить наличие данных в таблицах

Автор: Castro 22.5.2009, 17:54

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

Автор: ToshaCh 22.5.2009, 18:55

Если просто нужно узнать количество строк то можно воспользоваться вьюхой all_tables (поле num_rows). Но! для этого статистика по таблице должна быть актуальной. Если же нужно узнавать наличие каких-то определённых данных, то можно воспользоваться динамическим sql: http://mab.h1.ru/OTHER/dplsql.html

Автор: Castro 25.5.2009, 11:10

Чёрт, не работает ваша ссылка но похоже, что динамический sql - это оно. Но всё равно хотелось бы конкретных советов от человека, который занимался динамическим sql-ом. Я нашёл всего пару примеров но они какие-то неявные, а очень хочется и нужно разобраться во всём этом. Хотелось бы начать с простого. Например, есть 2 таблицы a и b, к обоих есть поле time_info хранящее дату. Запросом узнаю, есть ли какоя-то запись в таблице за сегодня. Если нет -  пишу в таблицу result , например, дату и название таблицы с отсутствующей датой.  Как сделать такую схему с помощью динамического запроса?

Автор: ToshaCh 25.5.2009, 13:14

Странно я щас по сслыке перешёл, походу она перманентно оживает. Твой пример можно разобрать где-то вот так. (не судите строго написал за 5 минут, мимоходом левой задней ногой)  Код  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 думаю и сам разберёшься.

Автор: Castro 25.5.2009, 13:59

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

Автор: Castro 25.5.2009, 15:11

Так.  Теперь чтобы дать гибкости, нужно передавать как параметр проверять наличии значений ещё по одному полю в некоторых таблицах (По идее будет так - в таблице С лежат список таблиц(как сделали Вы), а таблице 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 ....

Автор: ToshaCh 25.5.2009, 15:24

Цитата(Castro @  25.5.2009,  15:11 ) declare declare здесь не надо Цитата(Castro @  25.5.2009,  15:11 ) ' and' ||ne|| 'is NOT NULL ' Пробелов не хватает - синтаксическая ошибка будет. А вот в чём вопрос я не понял.    Сейчас у тебя получилось, что для всех таблиц будет один и тот же вариант дополнительного поля, зависящий от того как ты вызовешь проседуру.

Автор: Castro 25.5.2009, 15:31

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

Автор: ToshaCh 25.5.2009, 15:41

Ну тогда как-нить так: Код 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;

Автор: Castro 25.5.2009, 16:07

Похоже на правду.    Правда, не понял строчку 4 Код select ' and '||column_name||' is null' Что должно по идее вернуть  Код select ' and '||column_name||' is null' as param from d where d.id=r.id ?

Автор: ToshaCh 25.5.2009, 16:11

Ну дык!  Цитата and ia_colonko is null and ia_drugoe_colonko is null

Автор: Castro 25.5.2009, 16:47

Нет, меня смутило то, что из  Код 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  - это не ошибка?

Автор: ToshaCh 25.5.2009, 16:52

Это не переменная это поле таблицы d

Автор: Castro 25.5.2009, 17:06

Гляньте, пожалуйста, ещё раз на конечный вариант процедуры и проверте всё ли на месте и ничего я не напутал ли. Идею я понял, но мог что-то напутать с реализацией    Код 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; /

Автор: ToshaCh 25.5.2009, 17:13

Цитата(Castro @  25.5.2009,  17:06 ) -- Я так понимаю, это уже не нужно будет? С чего это? Представь что там получается в переменной stmt сейчас?

Автор: Castro 25.5.2009, 17:23

Ммм.... Это да. Но условие 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; ....

Автор: ToshaCh 25.5.2009, 17:35

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

Автор: ToshaCh 25.5.2009, 17:51

Наверное так. Код 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;

Автор: Castro 25.5.2009, 17:59

А, вот теперь я понял почему такая конструкция была  Код select ' and '||column_name||'  , потому что было ж статичное поле time_info. +1 вам в карму!   Спасибо огромное за помощь, буду докручивать завтра, уже сегодня голова не варит.

Автор: Zloxa 26.5.2009, 09:58

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

Автор: ToshaCh 26.5.2009, 13:12

О! Никогда не видел, чтобы курсор та компоновали (в смысле как строку). Спасибо.

Автор: Zloxa 27.5.2009, 11:07

Цитата(ToshaCh @  26.5.2009,  13:12 ) О! Никогда не видел, чтобы курсор та компоновали Весьма странно, обычно для народа dbms_sql - открытие ) Раз так - http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14251/adfns_dynamic_sql.htm#ADFNS008

Автор: Castro 27.5.2009, 11:29

А вот теперь нужен совет, потому что правила игры как оказалось уже изменились. Оказалось, что таблицы разные и в основном они связаны друг с другом по айдишникам, что вполне понятно, ну конечно что же поле даты присутсвует только в одной из таблиц. По сути элементарная связаная выборка получается. Запросы будут меняться вероятно? В чём вопрос: как это всё хранить теперь? Создать одну таблицу с полями table_name и query_str,  где хранить сами условия, и подставлять в цикле в шаблон Код Select * from '||table_name|| ' where '||query_str|| ' and time_info > trunc(sysdate)-1 and  rownum = 1  ? Или, возможно, есть смысл хранить толко названия полей, по которым связаны таблицы?   Или есть какое мудрое решение и в тоже время гибкое?

Автор: Zloxa 27.5.2009, 12:47

Цитата(Castro @  27.5.2009,  11:29 ) Создать одну таблицу с полями table_name и query_str Думаю так правильнее Цитата(Castro @  27.5.2009,  11:29 ) Или есть какое мудрое решение и в тоже время гибкое? Любая гибкость достигается, как правило, за счет потери прочности. Я за железобетонные решения. По сему, я не рекомендовал бы хранить запросы в данных. В любом случае, при добавлении таблицы нужно писать запрос. Запрос должен писать программист, не пользователь. Меньше шансов ошибиться у программиста будет, если он будет редактировать текст ХП, которая, будет еще на этапе компиляции проверяться. От того бросайте вы это баловство с универсальными гибкими решениями, пракитика показывает, что ни к чему хорошему они не приводят. Автомобиль амфибия плохо ездиит и плохо плавает, от того их производят так мало и лишь по частным заказам.

Автор: Castro 18.6.2009, 18:48

Появилось вот время занятся сново с эти заданием и уже почти но всё ещё не всё Код 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 к сожалению не могу испытать (или не знаю просто как это сделать :(( )

Автор: Zloxa 18.6.2009, 18:58

Это не код это бред. Код    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:04

Код 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'')';   а что здесь не так?

Автор: Zloxa 19.6.2009, 10:41

Цитата(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 )  а что здесь не так? считай кавычки, ну или хотябы смотри на то как подсвечен синтаксис.

Автор: Castro 19.6.2009, 11:08

Спасибо. Использовал Вашу процедуру, но она не успела отработать  Так, упростил всё что только можно. Код 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-pl-sql-numeric-or-value-error.html Это попытка записать большой объём данных в переменную foo, больший чем оно может вместить? В смысле в DUAL не может влезть? Илия ошибаюсь? Как это побороть?

Автор: Zloxa 19.6.2009, 11:20

Цитата(Castro @  19.6.2009,  11:08 ) ORA-06502 структура переменной foo не соответствует структуре, возвращенной курсором. Вы в переменную типа чар(1) пытаеесь впихнуть дату

Автор: Castro 19.6.2009, 11:31

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

Автор: Zloxa 19.6.2009, 11:57

Цитата(Castro @  19.6.2009,  11:31 ) а когда применяют 3 3=2+1 т.е. кавычка внутри строки+закрывающая кавычка Добавлено через 2 минуты и 4 секунды Цитата(Castro @  19.6.2009,  11:31 ) отправляю пост и за 10 секунд решаю проблемму Это говорит лишь о том, что Вы сначала спрашиваете а потом думаете, что не делает Вам чести.

Автор: Castro 19.6.2009, 12:47

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

Powered by Invision Power Board (http://www.invisionboard.com)
© Invision Power Services (http://www.invisionpower.com)