VPF::ТРигер для проверки поступления новых записей

Valpeza

Дата 13.5.2011, 12:12 (ссылка)

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

Загрузка ...

Новичок

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

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

Доброго всем времени суток) У меня есть таблица с диагностическими назначениями, записи в котрой часто повторялись, была создана на основе этой новая таблица и в нее были по средствам старой внесены уже сгруппированные записи, а в отдельном поле выводится количество повторений. Там идея конечная значительно серьезней конечно, но пока вот так. Вот теперь мне нужен тригер который при поступлении новой записи в в старую таблицу сразу ее выцеплял бы и заносил в новую присваивая к эдентичной группе либо если такой записи еще небыло, то создавал новую запись. Помогите, люди добрые, кто чем может))) А то я дальше елементарных запросов к базе никак уйти не могу((

Akina

Дата 13.5.2011, 21:17 (ссылка)

(голосов:1)

Загрузка ...

Советчик

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

Репутация: 45
Всего: 454

Valpeza, от тебя уже несколько вопросов, и все свидетельствуют о том, что ты вообще ничего не смыслишь в БД и SQL, не знаешь самых элементарных основ.
Может, поора уже прекратить маяться [censored] и поучить по предмету хоть что-нибудь?

По сабжу - реализация того, о чём ты говоришь, диалектозависима. Но в любом случае это триггер, и insert into on duplicate key update внутри.

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

О(б)суждение моих действий - в соответствующей теме, пожалуйста. Или в РМ. И высшая инстанция - Администрация форума.

Valpeza

Дата 14.5.2011, 09:10 (ссылка)

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

Загрузка ...

Новичок

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

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

Спасибо за аналитический обзор. Ну если бы я разбирался в этой области, я бы не стал сюда писать... Поэтому не обессудьте. Я воюю с oracle 11 через navigator, это касательно диалектозависимости.

Zloxa

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

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

Загрузка ...

Чо?

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

Репутация: 53
Всего: 161

Цитата(Valpeza @ 14.5.2011, 09:10

)

oracle 11

rtfm Coding triggers, merge

upd

Цитата(Valpeza @ 13.5.2011, 12:12

)

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

Мне думается, тут можно и без триггера обойтись. Создать аггрегирующую материализованную вьюху, c фастрефрешем по коммиту. >>>

Это сообщение отредактировал(а) Zloxa - 14.5.2011, 11:27

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

Достоверно известно, что 89% людей доверяют статистике взятой с потолка smile

Valpeza

Дата 14.5.2011, 21:21 (ссылка)

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

Загрузка ...

Новичок

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

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

Не могу я разобраться с этой инфой толком. Может все таки поможете)) Хотябы поступательно...
вот с этой частью я совладал

Код


create or replace trigger od_drugs_trigger
    after insert or delete or update on od_drugs
        for each row
declare

а вот что писать в declare, какие там переменные объявлять надо, я не понимаю... Мне надо чтобы он включался при вставке новой записи в таблицу od_drugs и потом эту запись вносил в таблицу od_drugs_pre, при этом если такой записи нет среди существующих, то создавал новую, а если есть, то просто увеличивал бы счетчик на +1 у такой же записи.

Zloxa

Дата 15.5.2011, 13:12 (ссылка)

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

Загрузка ...

Чо?

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

Репутация: 53
Всего: 161

Цитата(Valpeza @ 14.5.2011, 21:21

)

а вот что писать в declare, какие там переменные объявлять надо, я не понимаю...

если вам не нужны переменные, то секция declare не нужна.

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

Достоверно известно, что 89% людей доверяют статистике взятой с потолка smile

Valpeza

Дата 15.5.2011, 13:18 (ссылка)

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

Загрузка ...

Новичок

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

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

ДАк вот иенно этого я и не понимаю, нужны они мне или нет... ТАм должно быть что-то в духе если вставлена новая запись взять ее и вставить в такую-то таблицу, если там такая уже есть, то объедениеть видимо через union, если нет, то создать новую... вобщем трындец( В колове уже кишь-мишь...

Zloxa

Дата 15.5.2011, 13:34 (ссылка)

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

Загрузка ...

Чо?

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

Репутация: 53
Всего: 161

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

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

Достоверно известно, что 89% людей доверяют статистике взятой с потолка smile

Valpeza

Дата 16.5.2011, 01:09 (ссылка)

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

Загрузка ...

Новичок

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

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

На самом деле все это часть моего дипломного проекта, с дури влез в совершенно новую по сути для себя тему, хотя специализируюсь я на других вещах,скажем так. И вот остается совсем чуть-чуть до защиты, а я по уши в какулинумах...) Ну уж раз не понятно из самого простого что мог придумать объяснения, то что уж тут поделаешь..... Спасибо за попытки поставить меня на путь истинный)

Zloxa

Дата 16.5.2011, 13:40 (ссылка)

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

Загрузка ...

Чо?

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

Репутация: 53
Всего: 161

Код


create table od_drugs(field1 varchar2(10),field2 varchar2(10),field3 varchar2(10));
 
Table created
create materialized view log on od_drugs with rowid,sequence(field1,field2,field3) including new values;
 
Materialized view log created
create materialized view pre_od_drugs
  refresh fast on commit
as
select field1,field2,field3,count(*) cnt
from od_drugs
group by field1,field2,field3;
 
Materialized view created
insert into od_drugs values ('1','1',null);
 
1 row inserted
insert into od_drugs values ('1','1',null);
 
1 row inserted
insert into od_drugs values ('2',null,null);
 
1 row inserted
insert into od_drugs values ('2',null,null);
 
1 row inserted
insert into od_drugs values ('3','3','3');
 
1 row inserted
insert into od_drugs values (null,null,null);
 
1 row inserted
insert into od_drugs values (null,null,null);
 
1 row inserted
commit;
 
Commit complete
select * from pre_od_drugs;
 
FIELD1     FIELD2     FIELD3            CNT
---------- ---------- ---------- ----------
                                          2
1          1                              2
2                                         2
3          3          3                   1

Добавлено @ 13:42
Если затевать тоже с триггером, а поля группировки могут принимать неопределенные значения, жостенько весьма получается, мутороно. Матвью - проще.

Это сообщение отредактировал(а) Zloxa - 16.5.2011, 13:47

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

Достоверно известно, что 89% людей доверяют статистике взятой с потолка smile

Valpeza

Дата 16.5.2011, 18:12 (ссылка)

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

Загрузка ...

Новичок

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

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

Спасибо за помощь)

Мне навигатор выпуливает ошибку Error: ORA-12044: недействительный параметр CREATE MATERIALIZED VIEW LOG на первой же строке, С чем это связано?

Код


create materialized view log on od_drugs a with rowid,sequence(id,name,forma) including new values;

Valpeza

Дата 16.5.2011, 18:51 (ссылка)

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

Загрузка ...

Новичок

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

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

Тут ошибка "Не верное предложение на языке SQL"

Код


Materialized view log created
create materialized view od_orders_pre
  refresh fast on commit
as
select id,name,forma,count(*) cnt
from od_drugs
group by id,name,forma;

Добавлено @ 18:54
Стоп, а эту вьюху надо подвязывать к таблице из которой будут переноситься данные или к таблице в которую они будут попадать, судя по коду скорее всего второй вариант, но если так то как я вновь поступившие записи буду вырывать?

Это сообщение отредактировал(а) Valpeza - 16.5.2011, 18:56

Zloxa

Дата 16.5.2011, 19:28 (ссылка)

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

Загрузка ...

Чо?

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

Репутация: 53
Всего: 161

Цитата(Valpeza @ 16.5.2011, 18:51

)

Error: ORA-12044: недействительный параметр CREATE MATERIALIZED VIEW LOG

я вам привел лог сессии а вы и его с ошибки умудрились воспроизвести? user posted image

откуда там слово "а" перед "with" затесалось? smile

Цитата(Valpeza @ 16.5.2011, 18:51

)

Тут ошибка "Не верное предложение на языке SQL"

Это лог. Отделите команды от вывода.

Цитата(Valpeza @ 16.5.2011, 18:51

)

эту вьюху надо подвязывать к таблице

ее ни к чему не надо подвязывать. Она сама создает таблицу, в которой хранит данные. Я специально исопльзовал в своем скрипте те имена, которые вы озвучили в своих сумубурных объяснениях.

Это сообщение отредактировал(а) Zloxa - 16.5.2011, 19:39

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

Достоверно известно, что 89% людей доверяют статистике взятой с потолка smile

Valpeza

Дата 16.5.2011, 21:36 (ссылка)

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

Загрузка ...

Новичок

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

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

Да лажанулся я че-то... Прошу прощения...

ТОесть по сути мне нужно только это.?

Код


create materialized view log on od_orders_pre with rowid,sequence(a_name,a_forma) including new values;

create materialized view od_orders_pre
  refresh fast on commit
as
select a_name,a_forma,count(*) cnt
from od_orders_pre
group by a_name,a_forma;

в таком случае мне не понятно как эта вьюха будет получать инфу о том что в таблицу od_drugs внесена запись и заносить ее в od_orderc_pre....

Zloxa

Дата 16.5.2011, 22:41 (ссылка)

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

Загрузка ...

Чо?

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

Репутация: 53
Всего: 161

Цитата(Valpeza @ 16.5.2011, 21:36

)

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

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

Достоверно известно, что 89% людей доверяют статистике взятой с потолка smile

Zloxa

Дата 17.5.2011, 12:20 (ссылка)

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

Загрузка ...

Чо?

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

Репутация: 53
Всего: 161

Стала интересна разница в подходах, решил померить перфомансы

Матвьюха

Код


create table od_drugs_mv(field1 varchar2(100),field2 varchar2(100),field3 varchar2(100));
insert into od_drugs_mv select sys_guid(),sys_guid(),sys_guid() 
  from dual connect by level <=1500000;
commit;
create materialized view log on od_drugs_mv with sequence,rowid (field1,field2,field3) including new values;
create materialized view pre_otd_drugs_mw
  refresh fast on commit
as
select field1,field2,field3,count(*) cnt
from od_drugs_mv
group by field1,field2,field3;

Триггерная логика:

Код


create table od_drugs_trg as select * from od_drugs_mv;
create table pre_otd_drugs_trg as select * from pre_otd_drugs_mw;
create unique index pre_otd_drugs_trg$unc on pre_otd_drugs_trg('~'||field1,'~'||field2,'~'||field3);

create or replace trigger od_drugs_trg$aiud after insert or update or delete on od_drugs_trg for each row
declare
  v_cnt pre_otd_drugs_trg.cnt%type;
begin
  if updating or deleting then
     update pre_otd_drugs_trg set cnt = cnt-1
       where '~'||field1 = '~'||:old.field1
         and '~'||field2 = '~'||:old.field2
         and '~'||field3 = '~'||:old.field3
     returning cnt into v_cnt;
     if sql%rowcount != 1
     then
       raise_application_error(-20777,'Потеряна согласованность данных');
     end if;
     if v_cnt = 0 then
      delete from pre_otd_drugs_trg
       where '~'||field1 = '~'||:old.field1
         and '~'||field2 = '~'||:old.field2
         and '~'||field3 = '~'||:old.field3
         and cnt = 0;
     end if;
  end if;
  if updating or inserting then
     update pre_otd_drugs_trg set cnt = cnt + 1
       where '~'||field1 = '~'||:new.field1
         and '~'||field2 = '~'||:new.field2
         and '~'||field3 = '~'||:new.field3;
     if sql%rowcount = 0
     then
       insert into pre_otd_drugs_trg values (:new.field1,:new.field2,:new.field3,1);
     end if;
  end if;
end;
/

Свободная от логики табла для примеру и табла с тестовыми данными

Код


create table od_drugs_free as select * from od_drugs_mv;
create table test_data 
as 
  select sys_guid() a ,sys_guid() b, sys_guid() c
  from dual 
  connect by level <=100000;

тесткейс

Код


prompt ------------------------------------------------------
prompt добавляем в пустую таблицу
prompt ------------------------------------------------------
insert into od_drugs_free select * from test_data;
commit;
prompt ------------------------------------------------------
prompt добавляем в таблицу с матвью
prompt ------------------------------------------------------
insert into od_drugs_mv select * from test_data;
commit;
prompt ------------------------------------------------------
prompt добавляем в таблицу с триггером
prompt ------------------------------------------------------
insert into od_drugs_trg select * from test_data;
commit;

1 прогон - добавление новых данных

Код


Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 

------------------------------------------------------
добавляем в пустую таблицу
------------------------------------------------------
 
100000 rows inserted
 
Executed in 0.547 seconds
 
Commit complete
 
Executed in 0.062 seconds
------------------------------------------------------
добавляем в таблицу с матвью
------------------------------------------------------
 
100000 rows inserted
 
Executed in 9.75 seconds
 
Commit complete
 
Executed in 6.765 seconds
------------------------------------------------------
добавляем в таблицу с триггером
------------------------------------------------------
 
100000 rows inserted
 
Executed in 15.86 seconds
 
Commit complete
 
Executed in 0 seconds

имеем 9.75 + 6.765 = 16.515 на матвью против 15.86 триггером

2 прогон - добавление существующих данных

Код


Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 
 
------------------------------------------------------
добавляем в пустую таблицу
------------------------------------------------------
 
100000 rows inserted
 
Executed in 0.484 seconds
 
Commit complete
 
Executed in 0.109 seconds
------------------------------------------------------
добавляем в таблицу с матвью
------------------------------------------------------
 
100000 rows inserted
 
Executed in 11.25 seconds
 
Commit complete
 
Executed in 9.313 seconds
------------------------------------------------------
добавляем в таблицу с триггером
------------------------------------------------------
 
100000 rows inserted
 
Executed in 8.781 seconds
 
Commit complete
 
Executed in 0.016 seconds

имеем 11.25 + 9.313 = 20.563 на матвью против 8.781 триггером smile

Получается триггерная логика имеет таки некоторые преимущества по производительности.

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

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

Достоверно известно, что 89% людей доверяют статистике взятой с потолка smile

Zloxa

Дата 17.5.2011, 12:43 (ссылка)

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

Загрузка ...

Чо?

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

Репутация: 53
Всего: 161

Модификация ключа аггрегации

Код


SQL> update od_drugs_mv t
  2    set t.field3 = null
  3  where rownum <= 100000;
 
100000 rows updated
 
Executed in 23.625 seconds
SQL> commit;
 
Commit complete
 
Executed in 34.796 seconds
SQL> update od_drugs_trg t
  2    set t.field3 = null
  3  where rownum <= 100000;
 
100000 rows updated
 
Executed in 60.828 seconds

23.625+ 34.796 = 58.421 против 60.828 вообще не существенно smile

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

Достоверно известно, что 89% людей доверяют статистике взятой с потолка smile

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