VPF::Определить наличие данных в таблицах

Castro

Дата 22.5.2009, 17:54 (ссылка)

(нет голосов)

Загрузка ...

Опытный

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

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

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

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

--------------------

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 это не только способ отмывания денег, но и вполне себе преличная база данных.

Castro

Дата 25.5.2009, 11:10 (ссылка)

(нет голосов)

Загрузка ...

Опытный

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

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

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

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

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

--------------------

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

--------------------

Castro

Дата 25.5.2009, 13:59 (ссылка)

(нет голосов)

Загрузка ...

Опытный

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

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

Так-так. А вот это мне уже нравится smile

Щас буду разбиратся, и вопросы задавать. Спасибо smile

--------------------

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
....

--------------------

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

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

--------------------

Castro

Дата 25.5.2009, 15:31 (ссылка)

(нет голосов)

Загрузка ...

Опытный

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

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

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

--------------------

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;

--------------------

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

--------------------

ToshaCh

Дата 25.5.2009, 16:11 (ссылка)

(нет голосов)

Загрузка ...

Опытный

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

Репутация: 12
Всего: 26

Ну дык!

Цитата

and ia_colonko is null
and ia_drugoe_colonko is null

--------------------

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

- это не ошибка?

--------------------

ToshaCh

Дата 25.5.2009, 16:52 (ссылка)

(нет голосов)

Загрузка ...

Опытный

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

Репутация: 12
Всего: 26

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

--------------------

Castro

Дата 25.5.2009, 17:06 (ссылка)

(нет голосов)

Загрузка ...

Опытный

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

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

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

Код


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 (ссылка)

(нет голосов)

Загрузка ...

Опытный

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

Репутация: 12
Всего: 26

Цитата(Castro @ 25.5.2009, 17:06

)

-- Я так понимаю, это уже не нужно будет?

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

--------------------

0 Пользователей читают эту тему (0 Гостей и 0 Скрытых Пользователей)
0 Пользователей:
« Предыдущая тема \| Oracle \| Следующая тема »