ReSQL.ru
     
powered by simpleCommunicator - 2.0.61     © 2026 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / Oracle [игнор отключен] [закрыт для гостей] / Задачка
145 сообщений из 145, показаны все 6 страниц
  все  
Задачка
    #33318694
Lom-22
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Lom-22
Гость
Помогите пожалуйста решить такую задачку:
Создать триггер базы данных, не позволяющий вводить в таблицу ААА
более трех однофамильцев, работающих в одном отделе. В случае попытки
вставить четвертого однофамильца в отдел должно выводиться на экран
соответсвующее сообщение.
В таблице уже существуют и заполгненны поля:
nom_sotr - номер сотрудника
nom_otd - номер отдела
name1 - фамилия
name2 - имя
name3 - отчество
city - город
zarpl - зарплата
заранее спасибо за ответ....
...
Рейтинг: 0 / 0
12.10.2005, 06:50
| Ответить | Цитировать | Написать  
Задачка
    #33318697
Sxak
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Sxak
Участник
Lom-22Помогите пожалуйста решить такую задачку:
Создать триггер базы данных, не позволяющий вводить в таблицу ААА
более трех однофамильцев, работающих в одном отделе. В случае попытки
вставить четвертого однофамильца в отдел должно выводиться на экран
соответсвующее сообщение.
В таблице уже существуют и заполгненны поля:
nom_sotr - номер сотрудника
nom_otd - номер отдела
name1 - фамилия
name2 - имя
name3 - отчество
city - город
zarpl - зарплата
заранее спасибо за ответ....Кажется такое можно сталать триггером after на весь statement ... но не уверен, не писал такого.. попробуй
...
Рейтинг: 0 / 0
12.10.2005, 06:57
| Ответить | Цитировать | Написать  
Задачка
    #33318702
Sxak
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Sxak
Участник
Lom-22Помогите пожалуйста решить такую задачку:
Такое точно у меня сработало... переделай под свою задачу:
create table ttttt (a number)
/
-- сбой на 3м значении (1,2 - можно а 3 уже нельзя)
create or replace
trigger trg_ttttt_3a_err
after insert or update on ttttt
begin
for r in (select a,count(*) from ttttt group by a having count(*)>2) loop
raise_application_error(-20001,'3j povtor '||to_char(r.a));
end loop;
end;
/
Теперь в таблицку tttt можно затолкать только 2 одинаковых значения но не 3.. Проверил работает
...
Рейтинг: 0 / 0
12.10.2005, 07:03
| Ответить | Цитировать | Написать  
Задачка
    #33318711
Sxak
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Sxak
Участник
Sxak Lom-22Помогите пожалуйста решить такую задачку:
Такое точно у меня сработало... переделай под свою задачу:
create table ttttt (a number)
Наврал - так нельзя:-(
Пусть 3 пользователя вводят одинаковое одновременно и пока не коммитят.. Триггер тогда не ругается (не видит сессии другию пользователей) Потом происходит коммит и триггер начинает ругаться даже на безобидное изменение...
...
Рейтинг: 0 / 0
12.10.2005, 07:18
| Ответить | Цитировать | Написать  
Задачка
    #33318713
Sxak
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Sxak
Участник
SxakНаврал - так нельзя:-(
Пусть 3 пользователя вводят одинаковое одновременно и пока не коммитят.. Триггер тогда не ругается (не видит сессии другию пользователей) Потом происходит коммит и триггер начинает ругаться даже на безобидное изменение...
PS может быть так будет можно если ты создашь индекс по ключевым полям (в твоем случае - отдел и фамилия) тогда может быть при вставке произойдет блокировка нужных блоков етого индекса и вставка одновременно одниъх значений будет невозможна, но тут у меня в голове некоторая каша - произойдет ли реально нужная блокировка не знаю. Но даже если произойдет перед созданием триггера надо будет проверить есть ли уже неправильные записи...
...
Рейтинг: 0 / 0
12.10.2005, 07:23
| Ответить | Цитировать | Написать  
Задачка
    #33318729
Sxak
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Sxak
Участник
Lom-22Помогите пожалуйста решить такую задачку:
Ну и последнее раз уж все молчат про блокировку а делать все и проверять тут времени нет и если блокировка обломается то теоритически возможна еще след схема:
1.триггер before на все - сбрасывает пакетные переменные
2. триггер after for each row - добавляет в PL/SQL таблицу которая есть пакетная переменая (та которая сбрасыватеся триггеров бефоре) ключи
3. триггер after на все - провереят только по ключам в пак переменной - тогда по кр мере лишней ругани не будет если уже есть лишние записи
...
Рейтинг: 0 / 0
12.10.2005, 07:46
| Ответить | Цитировать | Написать  
Задачка
    #33318751
Падонак
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Падонак
Участник
Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
select count(*) into i from 
 ( select t.name1, count(*) from t
   group by t.name1, t.nom_otd
   having count(*) >  3 
  )

if i >  0  then
 -- есть больше 3-х однофамильцов
...
Рейтинг: 0 / 0
12.10.2005, 08:11
| Ответить | Цитировать | Написать  
Задачка
    #33318758
Падонак
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Падонак
Участник
можно и так
Код: plaintext
1.
2.
3.
select count(*) into i from 
(select g.name1, count(*) over (partition by g.name1, g.otdel) as cnt from g)
where cnt >  3
...
Рейтинг: 0 / 0
12.10.2005, 08:22
| Ответить | Цитировать | Написать  
Задачка
    #33318761
Sxak
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Sxak
Участник
Падонакможно и так
Не понял твоего поста? Куда ты ему предлагаешь запихнуть такой блок?
...
Рейтинг: 0 / 0
12.10.2005, 08:24
| Ответить | Цитировать | Написать  
Задачка
    #33318764
Падонак
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Падонак
Участник
В триггере each row можно поступить проще

Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
create or replace trigger CheckNames before insert or update on t for each row
declare
  i integer;
begin
  select count(*) into i from t where t.name1 = :new.name1 and t.nom_otd = :new.nom_otd;
  if i >=  3  then
    raise_application_error(- 20101 , 'В отделе итак много однофамильцов (' || :new.name1 || ')');
  end if;
end;
...
Рейтинг: 0 / 0
12.10.2005, 08:26
| Ответить | Цитировать | Написать  
Задачка
    #33318766
Падонак
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Падонак
Участник
Sxak Падонакможно и так
Не понял твоего поста? Куда ты ему предлагаешь запихнуть такой блок?
да хоть в табличный триггер
...
Рейтинг: 0 / 0
12.10.2005, 08:26
| Ответить | Цитировать | Написать  
Задачка
    #33318768
Вячеслав Любомудров
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Вячеслав Любомудров
Участник
Ты все-таки сначала попробуй
Да и вставляй не по одной строке, а хотя бы по 2 ;-)
...
Рейтинг: 0 / 0
12.10.2005, 08:27
| Ответить | Цитировать | Написать  
Задачка
    #33318773
Падонак
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Падонак
Участник
Вячеслав ЛюбомудровТы все-таки сначала попробуй
Да и вставляй не по одной строке, а хотя бы по 2 ;-)
это кому?
...
Рейтинг: 0 / 0
12.10.2005, 08:30
| Ответить | Цитировать | Написать  
Задачка
    #33318777
Вячеслав Любомудров
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Вячеслав Любомудров
Участник
тебе
...
Рейтинг: 0 / 0
12.10.2005, 08:31
| Ответить | Цитировать | Написать  
Задачка
    #33318779
Sxak
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Sxak
Участник
ПадонакВ триггере each row можно поступить проще

Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
create or replace trigger CheckNames before insert or update on t for each row
declare
  i integer;
begin
  select count(*) into i from t where t.name1 = :new.name1 and t.nom_otd = :new.nom_otd;
  if i >=  3  then
    raise_application_error(- 20101 , 'В отделе итак много однофамильцов (' || :new.name1 || ')');
  end if;
end;
Сразу видно не писал ты триггеров...
...
Рейтинг: 0 / 0
12.10.2005, 08:32
| Ответить | Цитировать | Написать  
Задачка
    #33318787
Sxak
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Sxak
Участник
Падонак[quot Sxak]дурень,
ты со мной письками мерица решыл чтоли?
На себя посмотри. Интересно здесь модераторы есть?
...
Рейтинг: 0 / 0
12.10.2005, 08:37
| Ответить | Цитировать | Написать  
Задачка
    #33318789
Падонак
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Падонак
Участник
Вячеслав ЛюбомудровТы все-таки сначала попробуй
Да и вставляй не по одной строке, а хотя бы по 2 ;-)

Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
create table G
(
  X     INTEGER,
  NAME1 VARCHAR2( 15 ),
  OTDEL INTEGER
)

create or replace trigger CheckNames after insert on G
declare 
  i integer;
begin

  select count(*) into i from 
    (select g.name1, count(*) over (partition by g.name1, g.otdel) as cnt from g)
  where cnt >  3 ;

  if i >  0  then
    raise_application_error(- 20101 , 'В отделе итак много однофамильцов');
  end if;
end;

insert into g
(
select  1 , 'Сидоров',  1  from dual
union
select  2 , 'Сидоров',  1  from dual
union
select  3 , 'Сидоров',  1  from dual
union
select  4 , 'Сидоров',  1  from dual
)

=
ORA- 20101  'В отделе итак много однофамильцов'
...
Рейтинг: 0 / 0
12.10.2005, 08:38
| Ответить | Цитировать | Написать  
Задачка
    #33318793
Вячеслав Любомудров
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Вячеслав Любомудров
Участник
На ходу подметки рвешь Падонак В триггере each row можно поступить проще

Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
create or replace trigger CheckNames before insert or update on t for each row
declare
  i integer;
begin
  select count(*) into i from t where t.name1 = :new.name1 and t.nom_otd = :new.nom_otd;
  if i >= 3  then
    raise_application_error(- 20101 , 'В отделе итак много однофамильцов (' || :new.name1 || ')');
  end if;
end;
Падонак
Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
create or replace trigger CheckNames after insert on G
declare 
  i integer;
begin

  select count(*) into i from 
    (select g.name1, count(*) over (partition by g.name1, g.otdel) as cnt from g)
  where cnt >  3 ;

  if i >  0  then
    raise_application_error(- 20101 , 'В отделе итак много однофамильцов');
  end if;
end;
...
Рейтинг: 0 / 0
12.10.2005, 08:40
| Ответить | Цитировать | Написать  
Задачка
    #33318795
Sxak
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Sxak
Участник
ПадонакНу-ну А теперь прежде чем дурнями кидаться посмотри то же самое да с тем триггером на каждую строчку который ты писал. Кстати такой же (тот который работетат а не тот что фор ич роу) который ты тут только что написал я тут до тебя запостил....
Не писал ты триггров ето видно
...
Рейтинг: 0 / 0
12.10.2005, 08:40
| Ответить | Цитировать | Написать  
Задачка
    #33318797
Падонак
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Падонак
Участник
Вячеслав ЛюбомудровНа ходу подметки рвешь
Так ты предметно пиши, о чем говоришь,
я предложил два варианта
один (даже два первых) потабличный,
другой (третий) построчный

так ты о третьем писал или как?
...
Рейтинг: 0 / 0
12.10.2005, 08:42
| Ответить | Цитировать | Написать  
Задачка
    #33318799
Фотография Elic
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Elic
Участник
Sxak Падонак[quot Sxak]дурень,
ты со мной письками мерица решыл чтоли?
На себя посмотри. Интересно здесь модераторы есть?Вчера модератор вежливо сделал этой паскуде замечание. Так она и модератора облаяла. Чувствует свою безнаказанность, собака.

В этоим форуме модераторов почти не видно - А ЖАЛЬ
Такое паскудство надо пресекать на корню - отключением.
...
Рейтинг: 0 / 0
12.10.2005, 08:45
| Ответить | Цитировать | Написать  
Задачка
    #33318801
Sxak
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Sxak
Участник
Падонак Вячеслав ЛюбомудровНа ходу подметки рвешь
Так ты предметно пиши, о чем говоришь,
я предложил два варианта
один (даже два первых) потабличный,
другой (третий) построчный

так ты о третьем писал или как?Он предметно и написал. И я написал. Попиши триггеры познакомишься с ошибкаим типа "... изменяется триггер ф-ция может не заметить етого...." забыл уже точный текст ошибки
...
Рейтинг: 0 / 0
12.10.2005, 08:45
| Ответить | Цитировать | Написать  
Задачка
    #33318803
Вячеслав Любомудров
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Вячеслав Любомудров
Участник
А заодно побалуйся с табличным триггером в разных сессиях
...
Рейтинг: 0 / 0
12.10.2005, 08:46
| Ответить | Цитировать | Написать  
Задачка
    #33318806
Падонак
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Падонак
Участник
Вячеслав ЛюбомудровТы все-таки сначала попробуй
Да и вставляй не по одной строке, а хотя бы по 2 ;-)

Да, с построчным я погорячился. Моя ошибка.
...
Рейтинг: 0 / 0
12.10.2005, 08:49
| Ответить | Цитировать | Написать  
Задачка
    #33318807
Вячеслав Любомудров
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Вячеслав Любомудров
Участник
Ну чтож, с такими репликами этот топик скорее всего закроют и автору придется начинать новый

А жаль, задачка интересная
...
Рейтинг: 0 / 0
12.10.2005, 08:51
| Ответить | Цитировать | Написать  
Задачка
    #33318812
Sxak
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Sxak
Участник
Вячеслав ЛюбомудровНу чтож, с такими репликами этот топик скорее всего закроют и автору придется начинать новый

А жаль, задачка интересная
Кстати а по моему вопросу не скажешь ты или Элик? в смысле поможет ли индекс блокировать вставку на другие сессии?
...
Рейтинг: 0 / 0
12.10.2005, 08:55
| Ответить | Цитировать | Написать  
Задачка
    #33318817
Падонак
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Падонак
Участник
при вставке одинаковых уникальных значенй в двух сессиях одна будет заблокирована
...
Рейтинг: 0 / 0
12.10.2005, 08:59
| Ответить | Цитировать | Написать  
Задачка
    #33318822
Sxak
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Sxak
Участник
Падонакпри вставке одинаковых уникальных значенй в двух сессиях одна будет заблокированаПри чем здесь уникальных? Тут же неуникальные надо...
подучись еще маленько прежде чем советы давать
...
Рейтинг: 0 / 0
12.10.2005, 09:00
| Ответить | Цитировать | Написать  
Задачка
    #33318841
Падонак
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Падонак
Участник
Sxak Падонакпри вставке одинаковых уникальных значенй в двух сессиях одна будет заблокированаПри чем здесь уникальных? Тут же неуникальные надо...
подучись еще маленько прежде чем советы давать

действительно, ошыбсо
...
Рейтинг: 0 / 0
12.10.2005, 09:10
| Ответить | Цитировать | Написать  
Задачка
    #33318873
Фотография Elic
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Elic
Участник
Падонакдействительно, ошыбсоЧто характерно, это наблюдается в 99 случаях из 100. Взрослый уважающий себя человек уже давно бы вспомнил то, чему учили в школе: что нужно сперва думать, а потом открывать рот.
...
Рейтинг: 0 / 0
12.10.2005, 09:21
| Ответить | Цитировать | Написать  
Задачка
    #33318880
Владимир Бегун
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Владимир Бегун
Участник
Lom-22Помогите пожалуйста решить такую задачку:
Создать триггер базы данных, не позволяющий вводить в таблицу ААА
...
заранее спасибо за ответ....
Базовая идея тут . Это не триггер, но для некоторых случаев позволяет решить задачку. Кодирование, обход мутаций и обеспечение целостности данных при конкурентном изменении данных в таблице -- это ряд вещей, над которыми приходится задумываться решая эту и подобные ей задачи используя DIY-методы.
...
Рейтинг: 0 / 0
12.10.2005, 09:24
| Ответить | Цитировать | Написать  
Задачка
    #33318898
Падонак
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Падонак
Участник
Интересно,
а если сделать через сохранение поля (фамилия в данном случае) в массиве в пакете, и проверять в триггере на таблицу, записи имеющиеся в таблице + в массиве?
такое возможно?
...
Рейтинг: 0 / 0
12.10.2005, 09:32
| Ответить | Цитировать | Написать  
Задачка
    #33318909
Sxak
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Sxak
Участник
ПадонакИнтересно,
а если сделать через сохранение поля (фамилия в данном случае) в массиве в пакете, и проверять в триггере на таблицу, записи имеющиеся в таблице + в массиве?
такое возможно?Возможно и об етом я тоже писал. Воттолько единственное что ето даст - исчезнет ругань на безобидные изменения а проблема разных сессий останется и об ето м я тут тоже писал
...
Рейтинг: 0 / 0
12.10.2005, 09:35
| Ответить | Цитировать | Написать  
Задачка
    #33318923
Падонак
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Падонак
Участник
проблема разных сессий, кажыцо должна при этом уйти?
сейчас проблема, что не зафиксированные данные в одной сессии не видны в другой.
Сохраняя поле в пакет оно будет видно всем и будет видно, сколько значений пытаются записать.
Или я ошибаюс?
...
Рейтинг: 0 / 0
12.10.2005, 09:38
| Ответить | Цитировать | Написать  
Задачка
    #33318929
Sxak
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Sxak
Участник
Падонак
Сохраняя поле в пакет оно будет видно всем и будет видно, сколько значений пытаются записать.
Или я ошибаюс?Ошибаешься
...
Рейтинг: 0 / 0
12.10.2005, 09:40
| Ответить | Цитировать | Написать  
Задачка
    #33318932
Вячеслав Любомудров
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Вячеслав Любомудров
Участник
Падонакпроблема разных сессий, кажыцо должна при этом уйти?
сейчас проблема, что не зафиксированные данные в одной сессии не видны в другой.
Сохраняя поле в пакет оно будет видно всем и будет видно, сколько значений пытаются записать.
Или я ошибаюс?Пакетные переменные видны только текущей сессии

В принципе, можно намутить с глобальным контекстом

Еще как вариант - выставлять пользовательскую блокировку для изменяемой фамилии (dbms_lock), но это жуткая сериализация
...
Рейтинг: 0 / 0
12.10.2005, 09:41
| Ответить | Цитировать | Написать  
Задачка
    #33318940
Падонак
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Падонак
Участник
Вячеслав Любомудров Падонакпроблема разных сессий, кажыцо должна при этом уйти?
сейчас проблема, что не зафиксированные данные в одной сессии не видны в другой.
Сохраняя поле в пакет оно будет видно всем и будет видно, сколько значений пытаются записать.
Или я ошибаюс?Пакетные переменные видны только текущей сессии


не знал
...
Рейтинг: 0 / 0
12.10.2005, 09:44
| Ответить | Цитировать | Написать  
Задачка
    #33318946
Падонак
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Падонак
Участник
Вячеслав ЛюбомудровПакетные переменные видны только текущей сессии

В принципе, можно намутить с глобальным контекстом

Еще как вариант - выставлять пользовательскую блокировку для изменяемой фамилии (dbms_lock), но это жуткая сериализация
хорошо,
а если в в триггере, во временную(якобы) таблицу в автономной транзакции вставлять то, что пытаются вставить в таблицу?
...
Рейтинг: 0 / 0
12.10.2005, 09:45
| Ответить | Цитировать | Написать  
Задачка
    #33318950
Sxak
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Sxak
Участник
Падонак
хорошо,
а если в в триггере, во временную(якобы) таблицу в автономной транзакции вставлять то, что пытаются вставить в таблицу?
rollback
...
Рейтинг: 0 / 0
12.10.2005, 09:47
| Ответить | Цитировать | Написать  
Задачка
    #33318964
Sxak
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Sxak
Участник
Вячеслав Любомудров
В принципе, можно намутить с глобальным контекстом
Не очень понятно как....
dbms_lock понятно можно, а с глоб контекстом? rollback надо оттянуть переменные назад и ето не единственная проблема. Ругаться на чем? на триггере афтер на предложение? тогда ругань пойдет и на безобидные сессии...
на изменение переменных вроде блокировок не бывает так что непонятно как етосделать
...
Рейтинг: 0 / 0
12.10.2005, 09:49
| Ответить | Цитировать | Написать  
Задачка
    #33318967
Stax.
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Stax.
Участник
Lom-22Помогите пожалуйста решить такую задачку:
Создать триггер базы данных, не позволяющий вводить в таблицу ААА
более трех однофамильцев, работающих в одном отделе. В случае попытки
вставить четвертого однофамильца в отдел должно выводиться на экран
соответсвующее сообщение.

Тригер будет не базы а табличный
задача не такая простая как кажется,
решения
1.1 блокировка
1.2 триггере проверяем количество
1.2.1 мона вставлять только по одной строке (тогда проще код)
1.2.2 многострочная вставка код чуть сложнее
1.3 снимаем блокировку по завершению транзакции(вставки)

PS
мона организовывать по разному
......
Stax
...
Рейтинг: 0 / 0
12.10.2005, 09:50
| Ответить | Цитировать | Написать  
Задачка
    #33318974
Фотография Elic
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Elic
Участник
Вячеслав ЛюбомудровЕще как вариант - выставлять пользовательскую блокировку для изменяемой фамилии (dbms_lock), но это жуткая сериализацияМожно делать select for update для проверяемых однофомильцев. Сессия, желающая проверить ту же фамилию, будет ждать.
...
Рейтинг: 0 / 0
12.10.2005, 09:52
| Ответить | Цитировать | Написать  
Задачка
    #33318976
Vint
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Vint
Участник
Может быть автору подойдет немного другой сценарий выполнения, ведь не обязательно для решения данной задачи использовать только триггер, можно воспользоваться очень полезной весчью for update:
1. Блокируем все записи с данной фамилией програмно
select family from t where upper('family') = upper('блаблабла') for update nowait;
2. Проверяем сколько у нас выбралось фамилий
3. Вставляем запись либо выдаем предупреждение
Конечно может быть у автора нет возмождности модифицировать программу, но тогда скорее всего встанет проблема описанная выше, не буду проверять некогда но предложу еще один вариант, немного некрасивый но может кто модифицирует и найдет более красивое решение
1. создаем дополнительный столбец с чеком, диапазон значений от 1 до 3 и вешаем уникальный констрейнс на фамилию и этот столбец
2 при вставке в триггере вычисляем значения от 1 до 3 в зависимости от фамилии, если значения будут выходить за рамки выдаем ошибку, в 2 разных сессиях тоже не будет вставки так как одна из низ выдаст unique constrains violatide
PS
to Elic
не думал что вы так не любите комаров :) кожа за столь долгое время должна была уже стать потолще:)
to Падонак
Ты что не можеш понять что тебя либо закроют либо просто удалят этот ник и будут чистить сообщения, правила форума почитай и будь повежливее, а то как то ндоело бред читать
...
Рейтинг: 0 / 0
12.10.2005, 09:52
| Ответить | Цитировать | Написать  
Задачка
    #33318978
Sxak
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Sxak
Участник
Elic Вячеслав ЛюбомудровЕще как вариант - выставлять пользовательскую блокировку для изменяемой фамилии (dbms_lock), но это жуткая сериализацияМожно делать select for update для проверяемых однофомильцев. Сессия, желающая проверить ту же фамилию, будет ждать.Не совсем. Если только начали вставлять (нет еще однофамильцев) то ждать она ничего не будет и пока не закоммитили можно наделать кучу незакоммиченных сессий с такой же фамилией. Или я ошибаюс?:-)
...
Рейтинг: 0 / 0
12.10.2005, 09:54
| Ответить | Цитировать | Написать  
Задачка
    #33318981
Вячеслав Любомудров
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Вячеслав Любомудров
Участник
Elic Вячеслав ЛюбомудровЕще как вариант - выставлять пользовательскую блокировку для изменяемой фамилии (dbms_lock), но это жуткая сериализацияМожно делать select for update для проверяемых однофомильцев. Сессия, желающая проверить ту же фамилию, будет ждать.Имелось ввиду и добавляемой и удаляемой - алгоритм Stax расписал
...
Рейтинг: 0 / 0
12.10.2005, 09:56
| Ответить | Цитировать | Написать  
Задачка
    #33318991
Падонак
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Падонак
Участник
Elic Вячеслав ЛюбомудровЕще как вариант - выставлять пользовательскую блокировку для изменяемой фамилии (dbms_lock), но это жуткая сериализацияМожно делать select for update для проверяемых однофомильцев. Сессия, желающая проверить ту же фамилию, будет ждать.
как ты собираешься это делать, если такой фамилии нет и будет сделана многострочная вставка?
...
Рейтинг: 0 / 0
12.10.2005, 09:59
| Ответить | Цитировать | Написать  
Задачка
    #33319002
Вячеслав Любомудров
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Вячеслав Любомудров
Участник
Вячеслав Любомудров Elic Вячеслав ЛюбомудровЕще как вариант - выставлять пользовательскую блокировку для изменяемой фамилии (dbms_lock), но это жуткая сериализацияМожно делать select for update для проверяемых однофомильцев. Сессия, желающая проверить ту же фамилию, будет ждать.Имелось ввиду и добавляемой и удаляемой - алгоритм Stax расписалНу и на обновление фамилии, соответственно, две блокировки - так что сериализация вааще крутая будет
...
Рейтинг: 0 / 0
12.10.2005, 10:02
| Ответить | Цитировать | Написать  
Задачка
    #33319009
Вячеслав Любомудров
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Вячеслав Любомудров
Участник
Плюс, при ошибке нужно снять только вновь выставленные блокировки для этого оператора - короче, тихий ужас
...
Рейтинг: 0 / 0
12.10.2005, 10:04
| Ответить | Цитировать | Написать  
Задачка
    #33319011
Stax.
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Stax.
Участник
Vint
...
2 при вставке в триггере вычисляем значения от 1 до 3 в зависимости от фамилии, если значения будут выходить за рамки выдаем ошибку, в 2 разных сессиях тоже не будет вставки так как одна из низ выдаст unique constrains violatide
PS

Как вычислите номер?

.....
Stax
...
Рейтинг: 0 / 0
12.10.2005, 10:04
| Ответить | Цитировать | Написать  
Задачка
    #33319020
Vint
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Vint
Участник
Stax.Как вычислите номер?

Код: plaintext
select count( 1 )+ 1  from t where family = 'Иванов'
...
Рейтинг: 0 / 0
12.10.2005, 10:06
| Ответить | Цитировать | Написать  
Задачка
    #33319028
Stax.
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Stax.
Участник
создать уникальный fbi индекс,
имхо он должен строится построчно

но я неуверен что это правильно,
не сильно понимаю как отреагирует на обман оракля
.....
Stax
...
Рейтинг: 0 / 0
12.10.2005, 10:07
| Ответить | Цитировать | Написать  
Задачка
    #33319033
Stax.
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Stax.
Участник
Vint Stax.Как вычислите номер?

Код: plaintext
select count( 1 )+ 1  from t where family = 'Иванов'


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

......
Stax
...
Рейтинг: 0 / 0
12.10.2005, 10:09
| Ответить | Цитировать | Написать  
Задачка
    #33319039
Vint
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Vint
Участник
Stax. Vint Stax.Как вычислите номер?

Код: plaintext
select count( 1 )+ 1  from t where family = 'Иванов'


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

......
Stax
Видимо вы просмотрели дополнение о уникальном констрэйнсе на фамилию и доп столбец:)
...
Рейтинг: 0 / 0
12.10.2005, 10:11
| Ответить | Цитировать | Написать  
Задачка
    #33319040
Вячеслав Любомудров
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Вячеслав Любомудров
Участник
Stax.создать уникальный fbi индекс,
имхо он должен строится построчно

но я неуверен что это правильно,
не сильно понимаю как отреагирует на обман оракля
.....
StaxЧто-то идея не совсем ясна
Можно подробней?
...
Рейтинг: 0 / 0
12.10.2005, 10:11
| Ответить | Цитировать | Написать  
Задачка
    #33319045
Sxak
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Sxak
Участник
Кстати а чем эта идея то не нравится?
Владимир БегунБазовая идея тут . Это не триггер, но для некоторых случаев позволяет решить задачку. Кодирование, обход мутаций и обеспечение целостности данных при конкурентном изменении данных в таблице -- это ряд вещей, над которыми приходится задумываться решая эту и подобные ей задачи используя DIY-методы.
Я в принципе перебежчик с 7го оракла не знаю были ли там mater.view по р мере не работал с ними но как я понимаю иде красивая.. По сути получается отлож ограничение до коммита если я правильно понял
...
Рейтинг: 0 / 0
12.10.2005, 10:11
| Ответить | Цитировать | Написать  
Задачка
    #33319048
Вячеслав Любомудров
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Вячеслав Любомудров
Участник
Vint
Видимо вы просмотрели дополнение о уникальном констрэйнсе на фамилию и доп столбец:)delete from t where ... and "доп столбец"=2
...
Рейтинг: 0 / 0
12.10.2005, 10:12
| Ответить | Цитировать | Написать  
Задачка
    #33319066
Stax.
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Stax.
Участник
Я могу сделать индекс
Но я сразу говорю, что у себя я токое б не внедрял
подозреваю что будет слетать
Так ради трепа

или не надо даже браться?
....
stax
...
Рейтинг: 0 / 0
12.10.2005, 10:15
| Ответить | Цитировать | Написать  
Задачка
    #33319078
Stax.
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Stax.
Участник
VintВидимо вы просмотрели дополнение о уникальном констрэйнсе на фамилию и доп столбец:)
нет не просмотрел,
доп поле надо заполнить в тригере ,
вот и спрашиваю как

ЗЫ
со мной можно и надо на ты
.......
Stax
...
Рейтинг: 0 / 0
12.10.2005, 10:18
| Ответить | Цитировать | Написать  
Задачка
    #33319081
Vint
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Vint
Участник
Вячеслав Любомудров Vint
Видимо вы просмотрели дополнение о уникальном констрэйнсе на фамилию и доп столбец:)delete from t where ... and "доп столбец"=2
да если удалить 2 то 1 и 3 остються, конечно можно вычислять дырку но как то получаеться сложно и громоздко решение, намного проще с блокировками сделать ИМХО
...
Рейтинг: 0 / 0
12.10.2005, 10:21
| Ответить | Цитировать | Написать  
Задачка
    #33319082
Stax.
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Stax.
Участник
SxakКстати а чем эта идея то не нравится?
....


почему не нравится, мож и нравится,
но нет у меня знаний насчет снапшотов

імхо
да и слетать будет сомміт а не інсерт
что там флейміть не знаю я єтого
......
Stax
...
Рейтинг: 0 / 0
12.10.2005, 10:21
| Ответить | Цитировать | Написать  
Задачка
    #33319088
Vint
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Vint
Участник
Stax.
ЗЫ
со мной можно и надо на ты
.......
Stax
ЗЫ
Не надо, и не можно ))), главное в человеке воспитание, вот приличной встрече как нить и определимся, а пока будем с вами обсчаться так )))
...
Рейтинг: 0 / 0
12.10.2005, 10:22
| Ответить | Цитировать | Написать  
Задачка
    #33319095
Фотография Elic
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Elic
Участник
Вячеслав Любомудров Вячеслав Любомудров Elic Вячеслав ЛюбомудровЕще как вариант - выставлять пользовательскую блокировку для изменяемой фамилии (dbms_lock), но это жуткая сериализацияМожно делать select for update для проверяемых однофомильцев. Сессия, желающая проверить ту же фамилию, будет ждать.Имелось ввиду и добавляемой и удаляемой - алгоритм Stax расписалНу и на обновление фамилии, соответственно, две блокировки - так что сериализация вааще крутая будетСериализация в пределах одной фамилии и к тому же в операторном режиме - imho, совсем не проблема.
Другое дело, что это просто не работает в определённых случаях: SxakЕсли только начали вставлять (нет еще однофамильцев) то ждать она ничего не будет и пока не закоммитили можно наделать кучу незакоммиченных сессий с такой же фамилией.

Кстати, при помощи dbms_lock вообще нельзя осуществить пофамильную блокировку.
...
Рейтинг: 0 / 0
12.10.2005, 10:24
| Ответить | Цитировать | Написать  
Задачка
    #33319098
Вячеслав Любомудров
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Вячеслав Любомудров
Участник
Vint Stax.
ЗЫ
со мной можно и надо на ты
.......
Stax
ЗЫ
Не надо, и не можно ))), главное в человеке воспитание, вот приличной встрече как нить и определимся, а пока будем с вами обсчаться так )))Тады уж старайся общаться грамотно

Меня, например, больше коробит обращение на "вы" (с маленькой буквы), чем на "ты" (без гадостей, естественно ;-))
...
Рейтинг: 0 / 0
12.10.2005, 10:24
| Ответить | Цитировать | Написать  
Задачка
    #33319099
Stax.
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Stax.
Участник
Вячеслав Любомудров
Что-то идея не совсем ясна
Можно подробней?
мне просто лень создавать,
если уже известно что это гиблая идея,
то зачем буду мучить (позорится)
у меня и так ляпов хватает

ведь есть простое имхо правильное решение

ps
придется попробовать
.....
Stax
...
Рейтинг: 0 / 0
12.10.2005, 10:25
| Ответить | Цитировать | Написать  
Задачка
    #33319102
Вячеслав Любомудров
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Вячеслав Любомудров
Участник
ElicКстати, при помощи dbms_lock вообще нельзя осуществить пофамильную блокировку.Почему?
...
Рейтинг: 0 / 0
12.10.2005, 10:25
| Ответить | Цитировать | Написать  
Задачка
    #33319111
Sxak
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Sxak
Участник
Elic SxakЕсли только начали вставлять (нет еще однофамильцев) то ждать она ничего не будет и пока не закоммитили можно наделать кучу незакоммиченных сессий с такой же фамилией.

Кстати, при помощи dbms_lock вообще нельзя осуществить пофамильную блокировку.Стоп. Там по номеру лочится так? А номер то можно задать хеш-фцией от фамилии или я ошибаюсь?:-)
...
Рейтинг: 0 / 0
12.10.2005, 10:27
| Ответить | Цитировать | Написать  
Задачка
    #33319123
Падонак
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Падонак
Участник
ElicКстати, при помощи dbms_lock вообще нельзя осуществить пофамильную блокировку.
опять незачот
...
Рейтинг: 0 / 0
12.10.2005, 10:30
| Ответить | Цитировать | Написать  
Задачка
    #33319131
Stax.
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Stax.
Участник
Elic
Кстати, при помощи dbms_lock вообще нельзя осуществить пофамильную блокировку.
Пачему?
мона блокировать и по хешу,
ну что хеш совпадет, тут вероятнось маленькая,
не в космос же летим

В моей жизни бывает проще
1 не вставляют пачками в отделе кадров,
так что мона забится на одиночный инсерт

2 фамилий этих не так уж и много, это не милионы

......
Stax
...
Рейтинг: 0 / 0
12.10.2005, 10:34
| Ответить | Цитировать | Написать  
Задачка
    #33319135
Vint
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Vint
Участник
Если бы данная проблема просто решалась думаю разработчики oracla предоставили возможность писать селекты в check`ах, это было бы иногда очень полезно. но всегда связанно с проблеммами многопользовательского режима, а в данном случае по моему единого решения нет, и автору придеться выбирать из предложенных и мериться с конкретными недостатками конкретного решения

ЗЫ
Вячеслав Любомудров
Тады уж старайся общаться грамотно
Меня, например, больше коробит обращение на "вы" (с маленькой буквы), чем на "ты" (без гадостей, естественно ;-))
Подскажите пожалуйста, а где написано что обращение на Вы в русском языке пишеться с большой буквы(конечно я может быть млохо учился в школе не помню правил ), мне с детсва вдолбили в голову что обрашение к старшим и незнакомым людям всегда на ВЫ, поэтому я и стараюсь обсчаться с участниками форума на ВЫ, очень редко когда достают некоторые отдельно взятые личности перехожу на ты(просто нервничаю и это передаеться в интонациях)
...
Рейтинг: 0 / 0
12.10.2005, 10:38
| Ответить | Цитировать | Написать  
Задачка
    #33319138
Фотография Elic
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Elic
Участник
Sxak ElicКстати, при помощи dbms_lock вообще нельзя осуществить пофамильную блокировку.Стоп. Там по номеру лочится так? А номер то можно задать хеш-фцией от фамилии или я ошибаюсь?:-)Единственный способ получить уникальный номер блокировки - это dbms_lock.allocate_unique, который в триггере использовать нельзя. Все остальные способы не гарантируют отсутствие конфликтов.
...
Рейтинг: 0 / 0
12.10.2005, 10:40
| Ответить | Цитировать | Написать  
Задачка
    #33319146
Sxak
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Sxak
Участник
Elic Sxak ElicКстати, при помощи dbms_lock вообще нельзя осуществить пофамильную блокировку.Стоп. Там по номеру лочится так? А номер то можно задать хеш-фцией от фамилии или я ошибаюсь?:-)Единственный способ получить уникальный номер блокировки - это dbms_lock.allocate_unique, который в триггере использовать нельзя. Все остальные способы не гарантируют отсутствие конфликтов.А зачем обязателнь оуникальный? Ну будет происходить ожидание не на той фамилии иногда но редко. Разве ето критично?
...
Рейтинг: 0 / 0
12.10.2005, 10:42
| Ответить | Цитировать | Написать  
Задачка
    #33319158
Вячеслав Любомудров
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Вячеслав Любомудров
Участник
VintПодскажите пожалуйста, а где написано что обращение на Вы в русском языке пишеться с большой буквы(конечно я может быть млохо учился в школе не помню правил ), мне с детсва вдолбили в голову что обрашение к старшим и незнакомым людям всегда на ВЫ, поэтому я и стараюсь обсчаться с участниками форума на ВЫ, очень редко когда достают некоторые отдельно взятые личности перехожу на ты(просто нервничаю и это передаеться в интонациях) http://www.gramota.ru/dic/search.php?word=%E2%FB&lop=x&gorb=x&efr=x&zar=x&ag=x&ab=x&lv=x&pe=x&az=x устроит?
...
Рейтинг: 0 / 0
12.10.2005, 10:48
| Ответить | Цитировать | Написать  
Задачка
    #33319163
Вячеслав Любомудров
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Вячеслав Любомудров
Участник
ElicЕдинственный способ получить уникальный номер блокировки - это dbms_lock.allocate_unique, который в триггере использовать нельзя. Все остальные способы не гарантируют отсутствие конфликтов.Автономная транзакция?
...
Рейтинг: 0 / 0
12.10.2005, 10:49
| Ответить | Цитировать | Написать  
Задачка
    #33319185
Вячеслав Любомудров
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Вячеслав Любомудров
Участник
Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
tst> create or replace function get_unique(name varchar2) return varchar2 as
   2       pragma autonomous_transaction;
   3       handle varchar2( 128 );
   4   begin dbms_lock.allocate_unique(name, handle);
   5       commit;
   6       return handle;
   7   end;
   8   /

Function created.

tst> create table t1(a varchar2( 128 ));

Table created.

tst> create trigger trg_t1
   2   before insert on t1
   3   for each row
   4   begin :new.a := get_unique(:new.a);
   5   end;
   6   /

Trigger created.

tst> insert into t1
   2   select 'One' a from dual
   3   union all select 'Two' from dual;

 2  rows created.

tst> select * from t1;

A
--------------------------------------------------------------------------------
 10737418241073741824147 
 10737418251073741825148
...
Рейтинг: 0 / 0
12.10.2005, 10:56
| Ответить | Цитировать | Написать  
Задачка
    #33319207
Vint
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Vint
Участник
Вячеслав Любомудров http://www.gramota.ru/dic/search.php?word=%E2%FB&lop=x&gorb=x&efr=x&zar=x&ag=x&ab=x&lv=x&pe=x&az=x устроит?
Спасибо постараюсь исправиться хотя это и трудно.
...
Рейтинг: 0 / 0
12.10.2005, 11:01
| Ответить | Цитировать | Написать  
Задачка
    #33319224
Фотография Elic
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Elic
Участник
Sxak ElicЕдинственный способ получить уникальный номер блокировки - это dbms_lock.allocate_unique, который в триггере использовать нельзя. Все остальные способы не гарантируют отсутствие конфликтов.А зачем обязателнь оуникальный? Ну будет происходить ожидание не на той фамилии иногда но редко. Разве ето критично?Дело не в уникальном хэше. Номера блокировок - это очень ценный и ограниченный ресурс, который нельзя использовать как попало.
Представь, что некая задача тоже использует пользовательские блокировки в таком же широком диапазоне номеров. И для такой задачи может оказаться крайне критичным напороться на чужую (а не свою) блокировку. Т.е. подсистемы могут пересечься по диапазону используемых номеров блокировок с более катастрофическими последствиями, чем "подумаешь, немножко подождёт".

Вячеслав ЛюбомудровАвтономная транзакция?А вот это уже похоже на правду. Выходит, я был не прав.
...
Рейтинг: 0 / 0
12.10.2005, 11:05
| Ответить | Цитировать | Написать  
Задачка
    #33319270
va_kochnev
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
va_kochnev
Участник
Для решения задачи можно создать дополнительную таблицу со структурой
-отдел
-фамилия
-число_однофамильцев
с первичным ключом на (отдел, фамилия)

На исходную таблицу повесить триггер
for each row
before insert or update or delete
begin
select ... from доп_таблица where :new.отдел=отдел and ... for update; -- for update для многопользовательской работы
if inserting and число_однофамильцев=3 then
raise_application_error(...)
elsif inserting and число_однофамильцев=0 then
insert into доп_таблица ... -- если до commit кто-то еще дернется с insert-ом, то получит отлуп из-за уникальности, что не есть хорошо, т.к. в сумме получается только 2 однофамильца.
elsif inserting and число_однофамильцев between 1 and 2 then
update доп_таблица set число_однофамильцев+1 where ...;
elsif deleting and число_однофамильцев>1 then
update доп_таблица set число_однофамильцев-1 where ...;
elsif -- дальше писать лень, но идея, думаю, понятна

end if;

end;
...
Рейтинг: 0 / 0
12.10.2005, 11:18
| Ответить | Цитировать | Написать  
Задачка
    #33319286
Sxak
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Sxak
Участник
ElicДело не в уникальном хэше. Номера блокировок - это очень ценный и ограниченный ресурс, который нельзя использовать как попало.
Представь, что некая задача тоже использует пользовательские блокировки в таком же широком диапазоне номеров. И для такой задачи может оказаться крайне критичным напороться на чужую (а не свою) блокировку. Т.е. подсистемы могут пересечься по диапазону используемых номеров блокировок с более катастрофическими последствиями, чем "подумаешь, немножко подождёт".
Ага похоже на правду а вот интренесно перекрестные блокировки dbms_lockом оракл отлавливает? Если нет то есть риск получить 2 зависшие сессии
...
Рейтинг: 0 / 0
12.10.2005, 11:22
| Ответить | Цитировать | Написать  
Задачка
    #33319297
Sxak
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Sxak
Участник
va_kochnevДля решения задачи можно создать дополнительную таблицу со структурой
-отдел
-фамилия
-число_однофамильцев
с первичным ключом на (отдел, фамилия)

На исходную таблицу повесить триггер
for each row
....
select ... from доп_таблица where :new.отдел=отдел and ... for update; -- for update для многопользовательской работы
.....
insert into доп_таблица ... -- если до commit кто-то еще дернется с insert-ом, Сдается мне что и ето рискует вызвать ...триггер фция может не заметить етого
...
Рейтинг: 0 / 0
12.10.2005, 11:25
| Ответить | Цитировать | Написать  
Задачка
    #33319391
Фотография Elic
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Elic
Участник
Sxakа вот интренесно перекрестные блокировки dbms_lockом оракл отлавливает? Если нет то есть риск получить 2 зависшие сессииЭти блокировки ничем не "особенней". dbmslock.sql
Код: plaintext
1.
2.
3.
  function  request(...
  --    If a deadlock is detected, then an arbitrary session is
  --    chosen to receive deadlock status.
...
Рейтинг: 0 / 0
12.10.2005, 11:50
| Ответить | Цитировать | Написать  
Задачка
    #33319487
Stax.
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Stax.
Участник
Elic
Единственный способ получить уникальный номер блокировки - это dbms_lock.allocate_unique, который в триггере использовать нельзя. Все остальные способы не гарантируют отсутствие конфликтов.
а нам и не надо уникальный, надо по фалилии
.....
Stax
...
Рейтинг: 0 / 0
12.10.2005, 12:07
| Ответить | Цитировать | Написать  
Задачка
    #33319494
Вячеслав Любомудров
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Вячеслав Любомудров
Участник
Ну вот что у меня получилось (кому не лень потестите, а то я ухожу) - сразу говорю - весьма нагрузит систему
Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
70.
71.
72.
73.
74.
75.
76.
77.
78.
79.
80.
81.
82.
83.
84.
85.
86.
87.
88.
89.
90.
91.
92.
93.
94.
95.
96.
97.
98.
99.
100.
101.
102.
103.
104.
105.
106.
107.
108.
109.
110.
tst> create or replace function get_unique(name varchar2) return varchar2 as
   2       pragma autonomous_transaction;
   3       handle varchar2( 128 );
   4   begin dbms_lock.allocate_unique(name, handle);
   5       commit;
   6       return handle;
   7   end;
   8   /

Function created.

tst> create or replace package p1 as
   2   type lock_rec is record (name varchar2( 128 ), handle varchar2( 128 ));
   3   type lock_rec_tab is table of lock_rec index by binary_integer;
   4   procedure set_lock(name varchar2);
   5   procedure clear_locks;
   6   procedure submit_locks;
   7   end;
   8   /

Package created.

tst> create or replace package body p1 as
   2   locks lock_rec_tab;
   3   procedure set_lock(name varchar2) is
   4       i number :=  1 ;
   5       ret number;
   6       rec lock_rec;
   7   begin
   8       if locks.first is not null then
   9           for i in locks.first..locks.last loop
  10               if locks(i).name=name then
  11                   return;
  12               end if;
  13           end loop;
  14       end if;
  15       rec.name := name;
  16       rec.handle := get_unique(name);
  17       ret := dbms_lock.request(rec.handle, release_on_commit=>true);
  18       if ret= 4  then
  19           return; /* already locked by me */
  20       elsif ret !=  0  then
  21           raise_application_error(- 20001 , 'Can''t lock for '||name);
  22       end if;
  23       locks(i) := rec;
  24   end;
  25   /* ----- */
  26   procedure clear_locks is
  27       i number :=  1 ;
  28       ret number;
  29   begin
  30       if locks.first is not null then
  31           for i in locks.first..locks.last loop
  32               ret := dbms_lock.release(locks(i).handle);
  33               if ret !=  0  then
  34                   raise_application_error(- 20001 , 'Can''t release lock for '||locks(i).name);
  35               end if;
  36               locks.delete(i);
  37           end loop;
  38       end if;
  39   end;
  40   /* ----- */
  41   procedure submit_locks is
  42   begin
  43       locks.delete;
  44   end;
  45   end;
  46   /

Package body created.

tst> drop table t1;

Table dropped.

tst> create table t1(name varchar2( 30 ));

Table created.

tst> create or replace trigger t1_bef
   2   before update or insert or delete on t1
   3   for each row
   4   begin
   5       if inserting then
   6           p1.set_lock(:new.name);
   7       elsif deleting then
   8           p1.set_lock(:old.name);
   9       else
  10           p1.set_lock(:old.name);
  11           p1.set_lock(:new.name);
  12       end if;
  13   end;
  14   /

Trigger created.

tst> create or replace trigger t1_aft
   2   after update or insert or delete on t1
   3   begin
   4       for r in (select name, count(*) cnt from t1
   5                 group by name
   6                 HAVING count(*) >  2  /* Our limit */ ) loop
   7           p1.clear_locks;
   8           raise_application_error(- 20003 , 'Too many count for (as example) '||r.name);
   9       end loop;
  10       p1.submit_locks;
  11   end;
  12   /

Trigger created.
И в одной сессии
Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
tst> insert into t1 values('One');

 1  row created.

tst> /

 1  row created.

tst> /
insert into t1 values('One')
*
ERROR at line  1 :
ORA- 20003 : Too many count for (as example) One
ORA- 06512 : at "U1.T1_AFT", line  6 
ORA- 04088 : error during execution of trigger 'U1.T1_AFT'
В другой
Код: plaintext
ts2> insert into t1 values('One');
Ждемс до коммита в первой ...
и получаем
Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
insert into t1 values('One')
*
ERROR at line  1 :
ORA- 20003 : Too many count for (as example) One
ORA- 06512 : at "U1.T1_AFT", line  6 
ORA- 04088 : error during execution of trigger 'U1.T1_AFT'


ts2>
Все блокировки вроде почистились
...
Рейтинг: 0 / 0
12.10.2005, 12:08
| Ответить | Цитировать | Написать  
Задачка
    #33319708
mcureenab
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
mcureenab
Участник
va_kochnevДля решения задачи можно создать дополнительную таблицу со структурой
-отдел
-фамилия
-число_однофамильцев
с первичным ключом на (отдел, фамилия)

end;

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

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

Если после update счётчика SQL%rowcount = 0, нужно в автономной транзакции добавить запись с новой фамилией и счётчиком = 0, а затем снова повторить update.


Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
if deleting then
    inc := - 1 ;
    update fam_counter set cnt=cnt+inc where ... returning cnt into cnt;
    if  0  = sql%rowcount then
        raise application_error; -- Нет записи, хотя должна быть!
    end if;
    if not  0  < cnt then
         delete fam_counter where ... ;
    end if;
end if;

if inserting then
    inc :=  1 ;
    loop
        update fam_counter set cnt=cnt+inc where ... returning cnt into cnt;
        exit when  0  < sql%rowcount;
        {insert  (...., cnt) values (...,  0 ); в автономной транзакции с обработкой dupval_on_index}
    end loop;
    if  3  < cnt then
         raise dupval_on_index; -- Дублирование в размере > 3!
    end if;
end if;

-- Изменение фамилии или перевод в другой отдел обрабатывается последовательным удалением старой фамилии и заведением новой.

Иногда в БД будут оставаться записи с cnt=0, их время от времени придётся удалять.
...
Рейтинг: 0 / 0
12.10.2005, 12:48
| Ответить | Цитировать | Написать  
Задачка
    #33320039
Stax.
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Stax.
Участник
Вячеслав ЛюбомудровНу вот что у меня получилось (кому не лень потестите, а то я ухожу) - сразу говорю - весьма нагрузит систему
....

Если грубо нужен обход мутации
лично я пишу несколько иначе
в операторном before очищаю pl/sql таблицу
імхо
бокировку надо выставить до проверки (до фор)
иначе успеют вставить

мона несколько улучшить сам селект
select name, count(*) cnt from t1
where name in (таблица)

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

....
Stax
...
Рейтинг: 0 / 0
12.10.2005, 14:01
| Ответить | Цитировать | Написать  
Задачка
    #33321379
Вячеслав Любомудров
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Вячеслав Любомудров
Участник
Stax.Если грубо нужен обход мутацииТам нет мутации (по крайней мере, я не смог нарваться) Stax.
лично я пишу несколько иначе
в операторном before очищаю pl/sql таблицуЭто дополнительный триггер - все равно нужет операторный AFTER для проверки результата и строчный BEFORE для блокировок.
Хотя, конечно, дело вкуса
Stax.імхо
бокировку надо выставить до проверки (до фор)
иначе успеют вставитьПроверка по фор чисто для ускорения (там ошибка, кстати), чтоб не дергать лишний раз DBMS_LOCK.ALLOCATE_UNIQUE и DBMS_LOCK.REQUEST. Вставить успеют, только если успеют выставить блокировку, тогда уже мы будем ждать
Stax.
мона несколько улучшить сам селект
select name, count(*) cnt from t1
where name in (таблица)

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

....
StaxЛогично

Теперь об ошибках
Неправильно ведется таблица блокировок (всегда перезаписывается первая и единственная):
Код: plaintext
1.
2.
3.
4.
5.
6.
...
  20       elsif ret !=  0  then
  21           raise_application_error(- 20001 , 'Can''t lock for '||name);
  22       end if;
  23       locks(i+ 1 ) := rec;
  24   end;
...


Функция P1.CLEAR_LOCKS нафинг не нужна, т.к. при сбое оператора освободятся все блокировки выставленные в этом операторе (по крайней мере для RELEASE_ON_COMMIT=>TRUE, этакий мини-роллбэк), но нужно почистить таблицу блокировок (P1.SUBMIT_LOCKS), иначе при commit/rollback блокировки освободятся, а мы будем считать что они установлены


Как следствие, нет необходимости хранить handle блокировки, соответствующий имени


По большому счету таблицу блокировок можно вообще не вести (соответственно, лишние вызовы DBMS_LOCK.ALLOCATE_UNIQUE и DBMS_LOCK.REQUEST)


Триггер, естественно, нужно лепить не на любое обновление, а только при обновлении используемых полей (UPDATE OF name)


Наверняка, еще куча ошибок/неоптимальностей
...
Рейтинг: 0 / 0
13.10.2005, 02:06
| Ответить | Цитировать | Написать  
Задачка
    #33321454
xif
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
xif
Участник
вот из этого можно что нибудь придумать
http://www.dbazine.com/oracle/or-articles/tropashko8
...
Рейтинг: 0 / 0
13.10.2005, 07:12
| Ответить | Цитировать | Написать  
Задачка
    #33321459
Владимир Бегун
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Владимир Бегун
Участник
Да... мучения похоже будут продолжаться долго...

Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
70.
71.
72.
73.
74.
75.
76.
77.
78.
79.
80.
81.
82.
83.
84.
85.
86.
87.
Присоединен к:
Oracle Database 10g Enterprise Edition Release  10 . 1 . 0 . 4 . 0  - Production
With the Partitioning, Oracle Label Security, OLAP and Data Mining Scoring Engine options

SQL> DROP TABLE tbl;

Таблица удалена.

SQL> CREATE TABLE tbl (v VARCHAR2( 10 ) NOT NULL);

Таблица создана.

SQL> CREATE MATERIALIZED VIEW LOG ON tbl WITH ROWID, SEQUENCE (v) INCLUDING NEW VALUES;

Журнал материализованного представления создан.

SQL> DROP MATERIALIZED VIEW tbl_mv;
DROP MATERIALIZED VIEW tbl_mv
*
ошибка в строке  1 :
ORA- 12003 : materialized view "APPS"."TBL_MV" does not exist


SQL> CREATE MATERIALIZED VIEW tbl_mv
   2   REFRESH FAST ON COMMIT
   3   AS
   4      SELECT v, COUNT(v) cnt, COUNT(*)
   5        FROM tbl
   6       GROUP BY v
   7   /

Материализованное представление создано.

SQL> ALTER TABLE tbl_mv ADD CONSTRAINT c$tbl_mv CHECK (cnt <=  3 )
   2   /

Таблица изменена.

SQL> DELETE tbl;

 0  строк удалено.

SQL> INSERT INTO tbl VALUES('t');

 1  строка создана.

SQL> INSERT INTO tbl VALUES('t');

 1  строка создана.

SQL> INSERT INTO tbl VALUES('t');

 1  строка создана.

SQL> INSERT INTO tbl VALUES('t1');

 1  строка создана.

SQL> COMMIT;

Фиксация обновлений завершена.

SQL> INSERT INTO tbl VALUES('t');

 1  строка создана.

SQL> COMMIT;
COMMIT
*
ошибка в строке  1 :
ORA- 12008 : error in materialized view refresh path
ORA- 02290 : check constraint (APPS.C$TBL_MV) violated


SQL> UPDATE tbl SET v = 't' WHERE v = 't1';

 1  строка обновлена.

SQL> COMMIT;
COMMIT
*
ошибка в строке  1 :
ORA- 12008 : error in materialized view refresh path
ORA- 02290 : check constraint (APPS.C$TBL_MV) violated


SQL>
...
Рейтинг: 0 / 0
13.10.2005, 07:16
| Ответить | Цитировать | Написать  
Задачка
    #33321463
Вячеслав Любомудров
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Вячеслав Любомудров
Участник
Да нет, причем тут мучения
Просто с MV сушественный недостаток - это "отложенность" проверки до коммита (полчаса вводили, а потом узнали, что так низзя)
Хотя может кому и подойдет

С блокировками мне больше нравится, т.к. попутно решается и задача найти того, кто держит записи не давая их обновить
...
Рейтинг: 0 / 0
13.10.2005, 07:21
| Ответить | Цитировать | Написать  
Задачка
    #33321468
Владимир Бегун
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Владимир Бегун
Участник
Вячеслав ЛюбомудровДа нет, причем тут мучения
Просто с MV сушественный недостаток - это "отложенность" проверки до коммита (полчаса вводили, а потом узнали, что так низзя)
Хотя может кому и подойдет

С блокировками мне больше нравится, т.к. попутно решается и задача найти того, кто держит записи не давая их обновить
"Пилите, Шура, пилите..." (с)
...
Рейтинг: 0 / 0
13.10.2005, 07:36
| Ответить | Цитировать | Написать  
Задачка
    #33321700
Stax.
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Stax.
Участник
Вячеслав Любомудров Stax.Если грубо нужен обход мутацииТам нет мутации (по крайней мере, я не смог нарваться) Stax.
лично я пишу несколько иначе
в операторном before очищаю pl/sql таблицуЭто дополнительный триггер - все равно нужет операторный AFTER для проверки результата и строчный BEFORE для блокировок.
Хотя, конечно, дело вкуса

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

Очищаю в бифоре в основном из-за рестарта оператора,
если не очищать, то таблицу заполним два раза
Вячеслав Любомудров
Stax.імхо
бокировку надо выставить до проверки (до фор)
иначе успеют вставитьПроверка по фор чисто для ускорения (там ошибка, кстати), чтоб не дергать лишний раз DBMS_LOCK.ALLOCATE_UNIQUE и DBMS_LOCK.REQUEST. Вставить успеют, только если успеют выставить блокировку, тогда уже мы будем ждать


Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
tst> create or replace trigger t1_aft
   2   after update or insert or delete on t1
   3   begin
   4       for r in (select name, count(*) cnt from t1
   5                 group by name
   6                 HAVING count(*) >  2  /* Our limit */ ) loop
   7           p1.clear_locks;
   8           raise_application_error(- 20003 , 'Too many count for (as example) '||r.name);
   9       end loop;
 мы вставили две фамилии и не блокировали
 во второй тоже вставили две
 теперь не важно кто первый заблокирует
 проверки уже выполнены если будут комитеть
 получим четыре фамилии
  10       p1.submit_locks;
  11   end;
  12   /

Вячеслав Любомудров
Функция P1.CLEAR_LOCKS нафинг не нужна, т.к. при сбое оператора освободятся все блокировки выставленные в этом операторе (по крайней мере для RELEASE_ON_COMMIT=>TRUE, этакий мини-роллбэк), но нужно почистить таблицу блокировок (P1.SUBMIT_LOCKS), иначе при commit/rollback блокировки освободятся, а мы будем считать что они установлены


тут я не понял, ну освободятся и добре,
что плохого что блокировки снимаеются в случае
ошибки

Вячеслав Любомудров
По большому счету таблицу блокировок можно вообще не вести (соответственно, лишние вызовы DBMS_LOCK.ALLOCATE_UNIQUE и DBMS_LOCK.REQUEST)


я бы вел токо таблицу имен, мона даже с distinct

.....
Stax
...
Рейтинг: 0 / 0
13.10.2005, 10:07
| Ответить | Цитировать | Написать  
Задачка
    #33321745
Вячеслав Любомудров
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Вячеслав Любомудров
Участник
Stax.
Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
tst> create or replace trigger t1_aft
   2   after update or insert or delete on t1
   3   begin
   4       for r in (select name, count(*) cnt from t1
   5                 group by name
   6                 HAVING count(*) >  2  /* Our limit */ ) loop
   7           p1.clear_locks;
   8           raise_application_error(- 20003 , 'Too many count for (as example) '||r.name);
   9       end loop;
 мы вставили две фамилии и не блокировали
 во второй тоже вставили две
 теперь не важно кто первый заблокирует
 проверки уже выполнены если будут комитеть
 получим четыре фамилии
  10       p1.submit_locks;
  11   end;
  12   /
Мы их заблокировали при вставке в триггере BEFORE ... FOR EACH ROWS (точнее повесили блокировки на фамилии). И вторая сессия эти фамилии уже не вставит до нашего окончания транзакции.

В принципе, поведение, как у уникального ключа - встретилась дублирующая запись (неподтвержденная) - ждем результата блокирующей транзакции

В общем, вот такое (приглаженное) получилось - я думаю добавить еще и отдел будет нетрудно
Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
70.
71.
72.
73.
74.
75.
76.
77.
78.
79.
80.
81.
82.
83.
84.
85.
86.
87.
88.
89.
90.
91.
92.
93.
94.
define TAB_NAME=T1
define FLD_NAME=NAME
define MAX_COUNT= 2 
define LK_TIMEOUT= 2 

set verify off
column user_and_table new_value LK_PREFIX noprint
select '('||user||'.&&TAB_NAME.) ' user_and_table from dual
/
create table &&TAB_NAME(&&FLD_NAME varchar2( 30 ))
/
create or replace package &&TAB_NAME._lock_mgr as
	type lock_rec_tab is table of &&TAB_NAME..&&FLD_NAME%type index by binary_integer;
	locks lock_rec_tab;
	procedure set_lock(str varchar2);
end;
/
create or replace package body &&TAB_NAME._lock_mgr as
/* ----- */
function get_unq(str varchar2) return varchar2 is
	pragma autonomous_transaction;
	handle varchar2( 128 );
begin
	dbms_lock.allocate_unique('&&LK_PREFIX'||str, handle);
	commit;
	return handle;
end;
/* ----- */
procedure set_lock(str varchar2) is
	i number :=  0 ;
	ret number;
begin
	if locks.first is not null then
		for j in locks.first..locks.last loop
			if locks(j)=str then
				return;
			end if;
		end loop;
		i := locks.last;
	end if;
	ret := dbms_lock.request(get_unq(str), timeout=>&&LK_TIMEOUT,
			release_on_commit=>true);
	if ret= 0  or ret= 4  /* already locked by me */ then
		locks(i+ 1 ) := str;
	elsif ret= 1  /* locked by another session */ then
		for r in (select username, s.sid from v$session s, v$lock l, sys.dbms_lock_allocated la
				where l.type='UL' and l.lmode> 0  and l.id1=la.lockid
				and s.sid=l.sid and la.name='&&LK_PREFIX'||str) loop
			raise_application_error(- 20002 ,	'"'||str||'" locked by '||
					r.username||' (SID='||r.sid||')');
		end loop;
	else
		raise_application_error(- 20001 ,	'Can''t lock for "'||str||'"');
	end if;
end;
end;
/
create or replace trigger &&TAB_NAME._lock_before
before insert or delete or update of &&FLD_NAME on &&TAB_NAME
for each row
begin
	if inserting or updating then
		&&TAB_NAME._lock_mgr.set_lock(:new.&&FLD_NAME);
	end if;
	if updating or deleting then
		&&TAB_NAME._lock_mgr.set_lock(:old.&&FLD_NAME);
	end if;
end;
/
create or replace trigger &&TAB_NAME._lock_after
after insert or delete or update of &&FLD_NAME on &&TAB_NAME
declare
	nm &&TAB_NAME..&&FLD_NAME%type;
	cnt number;
begin
	if &&TAB_NAME._lock_mgr.locks.first is not null then
		for i in &&TAB_NAME._lock_mgr.locks.first..&&TAB_NAME._lock_mgr.locks.last loop
			nm := &&TAB_NAME._lock_mgr.locks(i);
			select count(*) into cnt from &&TAB_NAME where &&FLD_NAME=nm;
			if cnt > &&MAX_COUNT then
				&&TAB_NAME._lock_mgr.locks.delete;
				raise_application_error(- 20003 , 'Too many count for "'||nm||'"');
			end if;
		end loop;
	end if;
	&&TAB_NAME._lock_mgr.locks.delete;
end;
/
set verify on
undef LK_PREFIX
undef LK_TIMEOUT
undef MAX_COUNT
undef FLD_NAME
undef TAB_NAME
...
Рейтинг: 0 / 0
13.10.2005, 10:18
| Ответить | Цитировать | Написать  
Задачка
    #33321900
Stax.
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Stax.
Участник
Вячеслав Любомудров
Мы их заблокировали при вставке в триггере BEFORE ... FOR EACH ROWS (точнее повесили блокировки на фамилии). И вторая сессия эти фамилии уже не вставит до нашего окончания транзакции.

Понял, вот что такое невнимательность,
просто я блокировал непосредственно перед проверкой
(перед select count(*) )

А еще забыл сразу написать,
блокировать желательно отсортировав по какому то критерию,
иначе легко получить деадлок
PS
зря Вы не очищаете таблицу в операторном before,
но это так мысли в слух
......
Stax
...
Рейтинг: 0 / 0
13.10.2005, 11:01
| Ответить | Цитировать | Написать  
Задачка
    #33321947
__LEV__
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
__LEV__
Гость
Суть идеи: при редактировании оракл блокирует запись, поэтому я попытался свести операции над фамилией+отделом(tab1) к изменению одной записи в другой таблице(tab2). В tab2 фамилия, отдел - первичный ключ, таким образом любые операции над одной фамилией+отдел встанут "в очередь".
Проверил работоспобность при insert,delete и update в одной и разных транзакциях, вроде работает.
Сразу предупрежу, не работает с множественными изменениями (ограничения на merge). То есть если за один DML удалить, обновить, создать несколько записей в tab1. Ошибки можно измежать, надо просто усовершенствовать триггер tr_tab1_name3_row. Если есть интерес буду рад поделиться дальше...

create table tab1(
id number primary key,
name varchar2(20),
otdel number
)
/
create table tab2(
name varchar2(20) not null,
otdel number not null,
cnt number default 1 not null
)
/
CREATE GLOBAL TEMPORARY TABLE tab3
(
name VARCHAR2(20) NOT NULL,
otdel NUMBER NOT NULL,
action char(1) NOT NULL
)
ON COMMIT DELETE ROWS
/
alter table tab2 add constraint pk_tab2 primary key (name,otdel);
CREATE OR REPLACE TRIGGER tr_tab2_name3
AFTER
UPDATE OF CNT
ON TAB2
FOR EACH ROW
begin
if :new.cnt>3 then
raise_application_error(-20000,'Нарушение целостности ...');
end if;
end;
/
create index ind_name3 on tab1 (name asc,otdel asc)
/
create or replace trigger tr_tab1_name3_row
after
insert or update or delete of name,otdel
on tab1
for each row
begin
if INSERTING then
insert into tab3(name,otdel,action)
values (:new.name,:new.otdel,'I');
elsif UPDATING and (:new.name<>:old.name or :new.otdel<>:old.otdel) then
insert into tab3(name,otdel,action)
values (:new.name,:new.otdel,'I');
insert into tab3(name,otdel,action)
values (:old.name,:old.otdel,'D');
else
insert into tab3(name,otdel,action)
values (:old.name,:old.otdel,'D');
end if;
end;
/
create or replace trigger tr_tab1_name3_st
after
insert or update or delete of name,otdel
on tab1
begin
for r in (select * from tab3) loop
dbms_output.put_line(R.name||' '||R.otdel||' '||R.action);
end loop;
merge into tab2 T2
using (select * from tab3) T3
on (T2.name=T3.name and T2.otdel=T3.otdel)
when matched then update set T2.cnt=T2.cnt + decode(T3.action,'D',-1,1)
when not matched then insert(name,otdel) values(decode(T3.action,'D',null,T3.name),T3.otdel);

delete from tab3;
end;
/
...
Рейтинг: 0 / 0
13.10.2005, 11:13
| Ответить | Цитировать | Написать  
Задачка
    #33321974
__LEV__
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
__LEV__
Гость
... и еще, в данном примере нет никаких автономных транзакций, ипользования dbms_lock и т.п.. Все операции поиска идут по первичным ключам, должно летать на любых объемах, во всяком случае не тормозить (нет select count(*) from tab group by having - перебор всех записей таблицы - из моего опыта - это подвесит систему при промышленных объемах данных)

Спасибо.
...
Рейтинг: 0 / 0
13.10.2005, 11:20
| Ответить | Цитировать | Написать  
Задачка
    #33322158
Stax.
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Stax.
Участник
__LEV__... и еще, в данном примере нет никаких автономных транзакций, ипользования dbms_lock и т.п.. Все операции поиска идут по первичным ключам, должно летать на любых объемах, во всяком случае не тормозить (нет select count(*) from tab group by having - перебор всех записей таблицы - из моего опыта - это подвесит систему при промышленных объемах данных)
Спасибо.

1 Если есть индекс(а он у вас есть) то летать будет
и вариант с count(*) where name=p_name

2 у вас две дополнительные таблицы
а ето нагрузка на систему ввода вывода

3 если заводить другую таблицу то имхо мона обойтись
одним триггером for ech row на основной,
во второй поставить CHECK (cnt <= 3) (как у Владимира)
(непонял зачем так сложно)

.....
Stax
...
Рейтинг: 0 / 0
13.10.2005, 12:06
| Ответить | Цитировать | Написать  
Задачка
    #33322177
Вячеслав Любомудров
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Вячеслав Любомудров
Участник
Да и где-то в логике напутано
Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
tst> insert into tab1 values( 1 , 'One',  1 );
One  1  I

 1  row created.

tst> insert into tab1 values( 2 , 'One',  1 );
One  1  I

 1  row created.

tst> insert into tab1 values( 3 , 'One',  1 );
One  1  I

 1  row created.

tst> update tab1 set name='Two' where id= 1 ;
Two  1  I
One  1  D

 1  row updated.
во второй сессии
Код: plaintext
ts2> insert into tab1 values( 4 , 'One',  1 );
висим - ждемс до коммита в первой.
Вроде все правильно, но после коммита в первой сессии получаем во второй
Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
insert into tab1 values( 4 , 'One',  1 )
            *
ERROR at line  1 :
ORA- 00001 : unique constraint (U1.PK_TAB2) violated
ORA- 06512 : at "U1.TR_TAB1_NAME3_ST", line  5 
ORA- 04088 : error during execution of trigger 'U1.TR_TAB1_NAME3_ST'


ts2> select * from tab1;

        ID NAME                      OTDEL
---------- -------------------- ----------
          1  Two                            1 
          2  One                            1 
          3  One                            1
PS. А идею заценить трудновато - нет никакого желания копать неформатированный код
...
Рейтинг: 0 / 0
13.10.2005, 12:11
| Ответить | Цитировать | Написать  
Задачка
    #33322335
__LEV__
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
__LEV__
Гость
Да согласен, наверно можно упростить.
По поводу дополнительных таблиц - блокировки (dbms_lock)тоже ведь хранятся в системных таблицах - теже таблицы,та же нагрузка на ввод/вывод.
К тому же количество блокировок ограничено (порядок 10^9), боюсь "на всех не хватит".
Могу ошибаться,но мне кажется, что без дополнительной таблицы (своей, системной и т.п.) не обойтись. Сами посудите: анализировать текущую таблицу в любом типе триггера(пакете и т.п.) - бесполезно - не видно не подтвержденных транзакций других сессий. Хочешь упорядочить операции - юзай оракл (ключи, юник индексы и т.п.). Мне кажется что ответив на этот вопрос(с доп таблицами), мы определимся в каком направлении копать.
...
Рейтинг: 0 / 0
13.10.2005, 12:52
| Ответить | Цитировать | Написать  
Задачка
    #33322563
Stax.
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Stax.
Участник
Сначала надо решить Lom-22 ,
характер задачи,
насколько критичны блокировки,
как часто происходят операции над таблицей,
мож у них например запрещено удаление инфы(отдел кадров)
мож это редкие операции,
тогда вообще мона блокировать напр dual
надавно обсуждали

если код позволяет,
пользовать простой пример Владимира

Вариантов много,
но главное нужно блокировть "инсерт"
.......
Stax
...
Рейтинг: 0 / 0
13.10.2005, 13:38
| Ответить | Цитировать | Написать  
Задачка
    #33324322
TRust
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
TRust
Участник
Lom-22Помогите пожалуйста решить такую задачку:
Создать триггер базы данных, не позволяющий вводить в таблицу ААА
более трех однофамильцев, работающих в одном отделе.
В общем случае решить эту задачу только средствами сервера Oracle нельзя, так как задача является несколько завуалированной классической задачей эмуляции констрейнтов ч/л, кроме самих констрейнтов. А эта задача не имеет решения.
...
Рейтинг: 0 / 0
14.10.2005, 10:46
| Ответить | Цитировать | Написать  
Задачка
    #33324530
Stax.
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Stax.
Участник
TRust Lom-22Помогите пожалуйста решить такую задачку:
Создать триггер базы данных, не позволяющий вводить в таблицу ААА
более трех однофамильцев, работающих в одном отделе.
В общем случае решить эту задачу только средствами сервера Oracle нельзя, так как задача является несколько завуалированной классической задачей эмуляции констрейнтов ч/л, кроме самих констрейнтов. А эта задача не имеет решения.

Как это не имеет?
на триггерах Вячеслав Любомудров показал
с доп таблицей __LEV__
через снапшоты Владимир Бегун

Выбирайте

......
Stax
...
Рейтинг: 0 / 0
14.10.2005, 11:37
| Ответить | Цитировать | Написать  
Задачка
    #33324624
gardenman
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
gardenman
Участник
Интересно, никто не пробовал решить подобную задачу на блокировочнике?
На грязном чтении?
...
Рейтинг: 0 / 0
14.10.2005, 12:00
| Ответить | Цитировать | Написать  
Задачка
    #33327649
Stax.
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Stax.
Участник
gardenmanИнтересно, никто не пробовал решить подобную задачу на блокировочнике?
На грязном чтении?
Меня убедили, что в оракле нет грязного чтения
Можете привести пример когда есть?

.......
Stax
...
Рейтинг: 0 / 0
17.10.2005, 11:57
| Ответить | Цитировать | Написать  
Задачка
    #33327739
Фотография Gluk (Kazan)
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Gluk (Kazan)
Участник
2 Stax

Он о другом, посмотри в профиль. Пытается показать что в данной задаче MS SQL круче, поскольку обеспечивает грязное чтение
...
Рейтинг: 0 / 0
17.10.2005, 12:29
| Ответить | Цитировать | Написать  
Задачка
    #33327741
Фотография Gluk (Kazan)
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Gluk (Kazan)
Участник
Sorry DB2 конечно
...
Рейтинг: 0 / 0
17.10.2005, 12:30
| Ответить | Цитировать | Написать  
Задачка
    #33328216
gardenman
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
gardenman
Участник
Данную задачу блокировочники действительно решают получше.
...
Рейтинг: 0 / 0
17.10.2005, 14:37
| Ответить | Цитировать | Написать  
Задачка
    #33328292
Фотография Gluk (Kazan)
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Gluk (Kazan)
Участник
Грязное чтение это не решение
...
Рейтинг: 0 / 0
17.10.2005, 15:01
| Ответить | Цитировать | Написать  
Задачка
    #33328321
Падонак
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Падонак
Участник
gardenmanДанную задачу блокировочники действительно решают получше.
пашутил?
...
Рейтинг: 0 / 0
17.10.2005, 15:08
| Ответить | Цитировать | Написать  
Задачка
    #33328352
mcureenab
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
mcureenab
Участник
gardenmanДанную задачу блокировочники действительно решают получше.

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

Поясню на сценарии. Пусть сессии С1 и С2 добавляют в уникальный индекс одинаковый ключ К. Почучаем вот что:

1. С1 insert К. Ok.
2. С2 insert К. Wait
3. С1 commit
4. С2 ORA-00001: unique constraint violated

На шаге 2 С2 "видит", что в индексе присутствует ключ К, который добавила С1, но ещё не завершила транзакцию.
...
Рейтинг: 0 / 0
17.10.2005, 15:17
| Ответить | Цитировать | Написать  
Задачка
    #33328427
Stax.
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Stax.
Участник
mcureenab gardenmanДанную задачу блокировочники действительно решают получше.

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

Поясню на сценарии. Пусть сессии С1 и С2 добавляют в уникальный индекс одинаковый ключ К. Почучаем вот что:

1. С1 insert К. Ok.
2. С2 insert К. Wait
3. С1 commit
4. С2 ORA-00001: unique constraint violated

На шаге 2 С2 "видит", что в индексе присутствует ключ К, который добавила С1, но ещё не завершила транзакцию.

Нужен пример индекса,
какой создаете?

......
Stax
...
Рейтинг: 0 / 0
17.10.2005, 15:38
| Ответить | Цитировать | Написать  
Задачка
    #33328522
mcureenab
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
mcureenab
Участник
Stax. mcureenab gardenmanДанную задачу блокировочники действительно решают получше.

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

Поясню на сценарии. Пусть сессии С1 и С2 добавляют в уникальный индекс одинаковый ключ К. Почучаем вот что:

1. С1 insert К. Ok.
2. С2 insert К. Wait
3. С1 commit
4. С2 ORA-00001: unique constraint violated

На шаге 2 С2 "видит", что в индексе присутствует ключ К, который добавила С1, но ещё не завершила транзакцию.

Нужен пример индекса,
какой создаете?

......
Stax

create unique index ...

Ещё добавлю, что статистика Current Gets это ничто иное, как чтения текущих состояний блоков, т.е. состояний которые могут включать изменения созданые незавершёнными транзакциями. Статистика Consistent Gets это чтения блоков по состоянию на определённый момет времени, которые включают изменения только из завершенных к этому моменту времени транзакций.
...
Рейтинг: 0 / 0
17.10.2005, 16:05
| Ответить | Цитировать | Написать  
Задачка
    #33328555
Stax.
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Stax.
Участник
mcureenabcreate unique index ...

Звиняйте, не точно задал вопрос,
интересует как укажите по чем индекс,
если грубо то по каких полях?

.......
Stax
...
Рейтинг: 0 / 0
17.10.2005, 16:15
| Ответить | Цитировать | Написать  
Задачка
    #33328561
mcureenab
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
mcureenab
Участник
Поскольку в задече речь идёт о триггере, то может быть не в тему.

добавляем колонку:

num integer; -- Номер записи с одинаковым dept и name.

создаём ограничение целостности

check(num between 1 and 3)

-- замечу, что тип колонки integer, тоже содержит неявное ограничение, т.е. мы не можем сохранить, например 1.3.

создаём ограничение целостности

unique (dept, name, num)

теперь в таблицу с колонками (dept, name, num) можно вставить не более трёх записей с одинаковым значением (dept, name).

Как вычислить в триггере значение num, это уже другой вопрос. Один из вариантов, использовать дополнительную таблицу со счётчиком уже обсуждался тут.
...
Рейтинг: 0 / 0
17.10.2005, 16:17
| Ответить | Цитировать | Написать  
Задачка
    #33328673
Stax.
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Stax.
Участник
mcureenabКак вычислить в триггере значение num, это уже другой вопрос
Это не другой, а главный вопрос,
имхо, пронумеровать не так просто

......
Stax
...
Рейтинг: 0 / 0
17.10.2005, 16:54
| Ответить | Цитировать | Написать  
Задачка
    #33328810
mcureenab
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
mcureenab
Участник
Stax. mcureenabКак вычислить в триггере значение num, это уже другой вопрос
Это не другой, а главный вопрос,
имхо, пронумеровать не так просто

......
Stax

Во истину, мы не ищем лёгких путей! Неужели от 1 до 3х посчитать так сложно?

Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
for i in  1 .. 3  loop
    begin
        insert into t (..., num) values (..., i);
        retun i;
    exception
    when dup_val_on_index then null;
    end;
end loop;
return  0 ;

Это конечно не триггерный вариант и далеко не самый оптимальный, но он тоже работает.

Придумать бы, чтобы одновременно три сессии могли скоординированно получить уникальные значения num и не мешая друг другу произвести вставку...
...
Рейтинг: 0 / 0
17.10.2005, 17:33
| Ответить | Цитировать | Написать  
Задачка
    #33329093
Stax.
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Stax.
Участник
mcureenab Stax. mcureenabКак вычислить в триггере значение num, это уже другой вопрос
Это не другой, а главный вопрос,
имхо, пронумеровать не так просто

......
Stax

Во истину, мы не ищем лёгких путей! Неужели от 1 до 3х посчитать так сложно?

Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
for i in  1 .. 3  loop
    begin
        insert into t (..., num) values (..., i);
        retun i;
    exception
    when dup_val_on_index then null;
    end;
end loop;
return  0 ;

Это конечно не триггерный вариант и далеко не самый оптимальный, но он тоже работает.

Придумать бы, чтобы одновременно три сессии могли скоординированно получить уникальные значения num и не мешая друг другу произвести вставку...
мне нравится последний абзац,
мне лично придумать сложно
......
Stax
...
Рейтинг: 0 / 0
17.10.2005, 20:04
| Ответить | Цитировать | Написать  
Задачка
    #33329162
Фотография andrey_anonymous
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
andrey_anonymous
Участник
А вот кому решение без триггера! Чистый FBI!
Elic, мой приз еще здесь?
К сожалению, "скоординировать" три сессии и мне не удалось, хотя... думаю, если хорошо извратить сущность dbms_lock, то все у нас получится :)

Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
70.
71.
72.
73.
74.
75.
76.
77.
78.
79.
80.
81.
82.
83.
84.
85.
86.
87.
88.
89.
90.
91.
92.
93.
94.
95.
96.
97.
98.
99.
100.
101.
102.
103.
104.
105.
106.
107.
108.
109.
110.
111.
112.
113.
114.
115.
116.
117.
118.
119.
120.
121.
122.
123.
124.
125.
126.
127.
128.
129.
130.
131.
132.
133.
134.
135.
136.
137.
138.
139.
140.
141.
142.
143.
144.
145.
146.
147.
148.
149.
150.
151.
152.
153.
154.
155.
156.
157.
158.
159.
160.
161.
162.
163.
164.
165.
166.
167.
168.
169.
170.
171.
172.
173.
174.
175.
176.
177.
Connected to Oracle9i Enterprise Edition Release  9 . 2 . 0 . 7 . 0  
set serveroutput off
set echo on

create global temporary table ane_test_tmp( nom_otd number not null
                                          , name1 varchar2( 300 ) not null
                                          , inc number
                                          )
                              on commit delete rows;
Table created

create table ane_test( nom_otd number not null -- номер отдела
                     , name1 varchar2( 300 ) not null -- фамилия
);
Table created

create or replace package ane_test_p as
 function f( p_nom_otd number
           , p_name1 varchar2
           ) return varchar2
           deterministic;
end ane_test_p;
/
Package created
sho err
No errors for PACKAGE ANE_TEST_P

create or replace package body ane_test_p as
 upd_stage pls_integer :=  0 ;
 function get_persistent ( p_nom_otd number, p_name1 varchar2
                         ) return number deterministic
                         is pragma autonomous_transaction;
   cnt number;
 begin
   select count(*)
   into cnt
   from ane_test
   where nom_otd=p_nom_otd
     and name1=p_name1;
   return cnt;
 end;

 function calc_key (p_nom_otd number, p_name1 varchar2, offset number) return varchar2 is
 begin
   return p_nom_otd||'/#/'||p_name1||'/#/'||offset;
 end;

 function f( p_nom_otd number
           , p_name1 varchar2
           ) return varchar2
           deterministic is
   pers_cnt number;
   temp_cnt number;
   rzt varchar2( 2000 );
 begin
   select nvl(sum(inc), 0 )
   into temp_cnt
   from ane_test_tmp
   where nom_otd=p_nom_otd and name1=p_name1;

   pers_cnt := get_persistent(p_nom_otd, p_name1);

   if inserting then
     dbms_output.put_line('FBI: INSERTING: p_nom_sotr='||p_nom_otd||', p_name1='||p_name1||', tmp_cnt='||temp_cnt||', pers_cnt='||pers_cnt);
     if pers_cnt + temp_cnt >=  3  then
       rzt := calc_key(p_nom_otd, p_name1, 1 ); -- DUP_VAL_ON_INDEX
       dbms_output.put_line('FBI: INSERTING: too many, key='||rzt);
     else
       rzt := calc_key(p_nom_otd, p_name1,pers_cnt + temp_cnt +  1 );
       dbms_output.put_line('FBI: INSERTING: OK, key='||rzt);
     end if;
     insert into ane_test_tmp values(p_nom_otd, p_name1,  1 );
   end if;

   if deleting then
     dbms_output.put_line('FBI: DELETING: p_nom_sotr='||p_nom_otd||', p_name1='||p_name1||', tmp_cnt='||temp_cnt||', pers_cnt='||pers_cnt);
     delete ane_test_tmp where nom_otd = p_nom_otd and name1=p_name1 and rownum= 1  and inc= 1 ;
     if SQL%ROWCOUNT =  0  then
       insert into ane_test_tmp values(p_nom_otd, p_name1, - 1 );
     end if;
     rzt := calc_key(p_nom_otd, p_name1,pers_cnt + temp_cnt);
   end if;

   if UPDATING then
     dbms_output.put_line('FBI: UPDATING: p_nom_sotr='||p_nom_otd||', p_name1='||p_name1||', tmp_cnt='||temp_cnt||', pers_cnt='||pers_cnt);
     if upd_stage =  0  then
       dbms_output.put_line('FBI: UPDATE DELETING: p_nom_sotr='||p_nom_otd||', p_name1='||p_name1||', tmp_cnt='||temp_cnt||', pers_cnt='||pers_cnt);
       delete ane_test_tmp where nom_otd = p_nom_otd and name1=p_name1 and rownum= 1  and inc= 1 ;
       if SQL%ROWCOUNT =  0  then
         insert into ane_test_tmp values(p_nom_otd, p_name1, - 1 );
       end if;
       rzt := calc_key(p_nom_otd, p_name1,pers_cnt + temp_cnt);
       upd_stage :=  1 ;
     else
       dbms_output.put_line('FBI: UPDATE INSERTING: p_nom_sotr='||p_nom_otd||', p_name1='||p_name1||', tmp_cnt='||temp_cnt||', pers_cnt='||pers_cnt);
       insert into ane_test_tmp values(p_nom_otd, p_name1,  1 );
       if pers_cnt + temp_cnt >=  3  then
         rzt := calc_key(p_nom_otd, p_name1, 1 ); -- DUP_VAL_ON_INDEX
       else
         rzt := calc_key(p_nom_otd, p_name1,pers_cnt + temp_cnt +  1 );
       end if;
       upd_stage :=  0 ;
     end if;
   end if;
   return rzt;
 end;
end ane_test_p;
/
Package body created

sho err
No errors for PACKAGE BODY ANE_TEST_P

--ВОТ ОНО!!!

create unique index ane_test_f_unq on ane_test (substr(ane_test_p.f(nom_otd,name1), 1 , 2000 ));

Index created

insert into ane_test( nom_otd, name1) values( 1 ,'Q!Q');
 1  row inserted
insert into ane_test( nom_otd, name1) values( 1 ,'Q!Q');
 1  row inserted
insert into ane_test( nom_otd, name1) values( 1 ,'Q!Q');
 1  row inserted

--сейчас будет reject
insert into ane_test( nom_otd, name1) values( 1 ,'Q!Q');
*insert into ane_test( nom_otd, name1) values( 1 ,'Q!Q')
ORA- 00001 : unique constraint (ANE_TEST_F_UNQ) violated

delete ane_test where nom_otd= 1  and rownum< 2 ;
 1  row deleted

insert into ane_test( nom_otd, name1) values( 1 ,'Q!Q');
 1  row inserted

insert into ane_test( nom_otd, name1) values( 1 ,'^Q!Q');
 1  row inserted

update ane_test set nom_otd=nom_otd+ 1 ;
 4  rows updated

commit;
Commit complete

insert into ane_test( nom_otd, name1) values( 3 ,'Q!Q');
 1  row inserted

--сейчас будет reject

update ane_test set nom_otd=nom_otd+ 1  where nom_otd <  3 ;
*update ane_test set nom_otd=nom_otd+ 1  where nom_otd <  3 
ORA- 00001 : unique constraint (ANE_TEST_F_UNQ) violated

commit;
Commit complete

select * from ane_test;

   NOM_OTD NAME1
---------- -----------------------------------------------
          2  Q!Q
          2  Q!Q
          2  Q!Q
          2  ^Q!Q
          3  Q!Q

drop table ane_test;
Table dropped

drop package ane_test_p;
Package dropped

drop table ane_test_tmp;
Table dropped
...
Рейтинг: 0 / 0
17.10.2005, 21:11
| Ответить | Цитировать | Написать  
Задачка
    #33329203
Фотография SY
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
SY
Участник
andrey_anonymousА вот кому решение без триггера! Чистый FBI!
Elic, мой приз еще здесь?

I do not think you can claim it. Your FBI is not deterministic:

Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
.
.
.
SQL> insert into ane_test( nom_otd, name1) values( 1 ,'Q!Q');

 1  row created.

SQL> insert into ane_test( nom_otd, name1) values( 1 ,'Q!Q');

 1  row created.

SQL> insert into ane_test( nom_otd, name1) values( 1 ,'Q!Q');

 1  row created.

SQL> insert into ane_test( nom_otd, name1) values( 1 ,'Q!Q');
insert into ane_test( nom_otd, name1) values( 1 ,'Q!Q')
*
ERROR at line  1 :
ORA- 00001 : unique constraint (SCOTT.ANE_TEST_F_UNQ) violated


SQL> alter index ane_test_f_unq rebuild;

Index altered.

SQL> insert into ane_test( nom_otd, name1) values( 1 ,'Q!Q');

 1  row created.

SQL> select count(*) from ane_test where nom_otd =  1  and name1 = 'Q!Q';

  COUNT(*)
----------
          4 

SQL>

SY.
...
Рейтинг: 0 / 0
17.10.2005, 22:03
| Ответить | Цитировать | Написать  
Задачка
    #33329217
Владимир Бегун
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Владимир Бегун
Участник
:-)
...
Рейтинг: 0 / 0
17.10.2005, 22:19
| Ответить | Цитировать | Написать  
Задачка
    #33329239
Andrew Max
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Andrew Max
Участник
Почему-то не заметил раньше этой ветки. ;(

Мои несколько копеек:
Задача интересная. Хотя само бизнес-правило "не иметь более n однофамильцев в одном отделе" несколько странное. Неужели, где-то это в реальности практикуется?
И что делают, если появляется n+1-ый однофамилец? Его в другой отдел переводят? А как быть, если в каждом отделе уже работает n Ивановых и приходит еще один Иванов? Новый отдел создается??? ;)

Тем не менее, если не задумываться над странностями бизнес-правила, -- наилучший вариант, имхо, предложил Владимир Бегун.
...
Рейтинг: 0 / 0
17.10.2005, 22:40
| Ответить | Цитировать | Написать  
Задачка
    #33329297
Фотография SY
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
SY
Участник
Well, somehow nobody offered INSTEAD OF trigger solution:

Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
CREATE TABLE AAA(
                 DEPARTMENT NUMBER,
                 LAST_NAME  VARCHAR2( 30 )
                )
/
CREATE OR REPLACE
  TRIGGER BIU_AAA
    BEFORE INSERT
        OR UPDATE
    ON AAA
    BEGIN
        IF WHO_CALLED_ME <> 'SCOTT.BIUR_AAA_VIEW'
          THEN
            RAISE_APPLICATION_ERROR(- 20987 ,'Table SCOTT.AAA can not be updated/inserted directly.');
        END IF;
END;
/
CREATE OR REPLACE
  VIEW AAA_VIEW
  AS 
    SELECT * FROM AAA
/
CREATE OR REPLACE
  TRIGGER BIUR_AAA_VIEW
    INSTEAD OF INSERT
            OR UPDATE
    ON AAA_VIEW
    FOR EACH ROW
    DECLARE
        v_cnt NUMBER;
    BEGIN
        SELECT  COUNT(*)
          INTO  v_cnt
          FROM  AAA
          WHERE LAST_NAME  = :NEW.LAST_NAME
            AND DEPARTMENT = :NEW.DEPARTMENT;
        IF v_cnt >=  3 
          THEN
            RAISE_APPLICATION_ERROR(- 20986 ,'Get your name changed.');
          ELSE
            INSERT INTO AAA VALUES(:NEW.DEPARTMENT,:NEW.LAST_NAME);
        END IF;
END;
/

where WHO_CALLED_ME is a function that reads dbms_utility.format_call_stack and retrurns calling routine name (I believe you can find source code у Кайта).

SY.
...
Рейтинг: 0 / 0
18.10.2005, 00:05
| Ответить | Цитировать | Написать  
Задачка
    #33329303
Фотография andrey_anonymous
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
andrey_anonymous
Участник
SY andrey_anonymousА вот кому решение без триггера! Чистый FBI!
Elic, мой приз еще здесь?
I do not think you can claim it. Your FBI is not deterministic:
SY.Ну не так уж все и плохо.
Просто забыл про rebuild
Добавьте в ane_test_p.f следующий кусок и все заработает:
Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
...
   if NOT inserting and NOT updating and NOT deleting then
     dbms_output.put_line('FBI: REBUILDING INSERTING: p_nom_otd='||p_nom_otd||', p_name1='||p_name1||', tmp_cnt='||temp_cnt||', pers_cnt='||pers_cnt);
     if /*pers_cnt +*/ temp_cnt >=  3  then
       rzt := calc_key(p_nom_otd, p_name1, 1 ); -- DUP_VAL_ON_INDEX
       dbms_output.put_line('FBI: REBUILDING INSERTING: too many, key='||rzt);
     else
       rzt := calc_key(p_nom_otd, p_name1,/*pers_cnt +*/ temp_cnt +  1 ); 
       dbms_output.put_line('FBI: REBUILDING INSERTING: OK, key='||rzt);
     end if;
     insert into ane_test_tmp values(p_nom_otd, p_name1,  1 );
   end if;   
...
Хотя я, разумеется, осознаю, что это не deterministic. Потому и спрашиваю Elic'a про приз
...
Рейтинг: 0 / 0
18.10.2005, 00:16
| Ответить | Цитировать | Написать  
Задачка
    #33329307
Фотография SY
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
SY
Участник
Sorry, I hit "post it" too early. There is a caveat. Trigger based solution, applies to future changes only (changes since trigger create time), while index applies to current data + future data. Another word, if we will create table AAA, populate it with, lets say, 5 Joe Shmoe's in department 8, and only then create view and triggers, it will not be detected. It could be considered as a disadvantage or as an advantage since it allows to implement "from now on no more более трех однофамильцев, работающих в одном отделе" rule.

SY.
...
Рейтинг: 0 / 0
18.10.2005, 00:28
| Ответить | Цитировать | Написать  
Задачка
    #33329310
Фотография SY
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
SY
Участник
andrey_anonymous Ну не так уж все и плохо

I think it is worse than you think. Just add dbms_output to deleting part:

Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
SQL> truncate table ane_test;

Table truncated.

SQL> insert into ane_test( nom_otd, name1) values( 1 ,'Q!Q');
FBI: INSERTING: p_nom_sotr= 1 , p_name1=Q!Q, tmp_cnt= 0 , pers_cnt= 0 
FBI: INSERTING: OK, key= 1 /#/Q!Q/#/ 1 

 1  row created.

SQL> select rowid from ane_test;

ROWID
------------------
AAAKULAABAAANdCAAA

SQL> insert into ane_test( nom_otd, name1) values( 1 ,'Q!Q');
FBI: INSERTING: p_nom_sotr= 1 , p_name1=Q!Q, tmp_cnt= 1 , pers_cnt= 0 
FBI: INSERTING: OK, key= 1 /#/Q!Q/#/ 2 

 1  row created.

SQL> select rowid from ane_test;

ROWID
------------------
AAAKULAABAAANdCAAA
AAAKULAABAAANdCAAB

SQL> insert into ane_test( nom_otd, name1) values( 1 ,'Q!Q');
FBI: INSERTING: p_nom_sotr= 1 , p_name1=Q!Q, tmp_cnt= 2 , pers_cnt= 0 
FBI: INSERTING: OK, key= 1 /#/Q!Q/#/ 3 

 1  row created.

SQL> select rowid from ane_test;

ROWID
------------------
AAAKULAABAAANdCAAA
AAAKULAABAAANdCAAB
AAAKULAABAAANdCAAC

SQL>

As you can see FBI values for each of three rows will be:

Код: plaintext
1.
2.
 1 /#/Q!Q/#/ 1         AAAKULAABAAANdCAAA
 1 /#/Q!Q/#/ 2         AAAKULAABAAANdCAAB
 1 /#/Q!Q/#/ 3         AAAKULAABAAANdCAAC

Now lets delete row with 1/#/Q!Q/#/2:

Код: plaintext
1.
2.
3.
4.
5.
6.
SQL> delete ane_test where rowid = 'AAAKULAABAAANdCAAB';
FBI: DELETING: p_nom_sotr= 1 , p_name1=Q!Q, tmp_cnt= 3 , pers_cnt= 0 
FBI: DELETING: OK, key= 1 /#/Q!Q/#/ 3 

 1  row deleted.

SQL>

As you can see your function wll delete wrong FBI entry 1/#/Q!Q/#/3. So index becomes logically corrupt. You can probably get away with it, since you can not use this FBI in WHERE clause anyway, but still corrupt index is a corrupt index.

SY.
...
Рейтинг: 0 / 0
18.10.2005, 00:52
| Ответить | Цитировать | Написать  
Задачка
    #33329317
Фотография SY
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
SY
Участник
Actually, I overlooked a major issue with your solution. It simply will not work. Assume session A inserts a row (for simplicity lets say table is empty). That row will have index value of 1/#/Q!Q/#/1. Session A does not commit/rollback yet. Now session B tries to insert a row. Obviously, table is locked and session B waits. Now the fun part - read consistency. Since session B transaction started before Session A committed, session B will not see any changes done by session A. So when session A will commit and release the lock, session B will also try to insert index value 1/#/Q!Q/#/1 and will get unique constraint violation.

SY.
...
Рейтинг: 0 / 0
18.10.2005, 01:12
| Ответить | Цитировать | Написать  
Задачка
    #33329324
Владимир Бегун
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Владимир Бегун
Участник
:-)
Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
70.
71.
72.
73.
74.
75.
76.
77.
78.
79.
80.
81.
82.
83.
SQL> CREATE TABLE AAA(
   2                    DEPARTMENT NUMBER,
   3                    LAST_NAME  VARCHAR2( 30 )
   4                   )
   5   /

Table created.

SQL> CREATE OR REPLACE
   2     VIEW AAA_VIEW
   3     AS 
   4       SELECT * FROM AAA
   5   /

View created.

SQL> CREATE OR REPLACE
   2     TRIGGER BIUR_AAA_VIEW
   3       INSTEAD OF INSERT
   4               OR UPDATE
   5       ON AAA_VIEW
   6       FOR EACH ROW
   7       DECLARE
   8           v_cnt NUMBER;
   9       BEGIN
  10           SELECT  COUNT(*)
  11             INTO  v_cnt
  12             FROM  AAA
  13             WHERE LAST_NAME  = :NEW.LAST_NAME
  14               AND DEPARTMENT = :NEW.DEPARTMENT;
  15           IF v_cnt >=  3 
  16             THEN
  17               RAISE_APPLICATION_ERROR(- 20986 ,'Get your name changed.');
  18             ELSE
  19               INSERT INTO AAA VALUES(:NEW.DEPARTMENT,:NEW.LAST_NAME);
  20           END IF;
  21   END;
  22   /

Trigger created.

SQL> INSERT INTO aaa_view VALUES( 1 , 'A');

 1  row created.

SQL> INSERT INTO aaa_view VALUES( 1 , 'A');

 1  row created.

SQL> INSERT INTO aaa_view VALUES( 1 , 'A');

 1  row created.

SQL> DECLARE
   2     PRAGMA AUTONOMOUS_TRANSACTION;
   3   BEGIN
   4     INSERT INTO aaa_view VALUES( 1 , 'A');
   5     COMMIT;
   6   END;
   7   /

PL/SQL procedure successfully completed.

SQL> COMMIT;

Commit complete.

SQL> SELECT * FROM aaa;

DEPARTMENT LAST_NAME
---------- ------------------------------
          1  A
          1  A
          1  A
          1  A

SQL> INSERT INTO aaa_view VALUES( 1 , 'A');
INSERT INTO aaa_view VALUES( 1 , 'A')
*
ERROR at line  1 :
ORA- 20986 : Get your name changed.
ORA- 06512 : at "BIUR_AAA_VIEW", line  11 
ORA- 04088 : error during execution of trigger 'BIUR_AAA_VIEW'

Let me repeat: "Кодирование, обход мутаций и обеспечение целостности данных при конкурентном изменении данных в таблице -- это ряд вещей, над которыми приходится задумываться решая эту и подобные ей задачи используя DIY-методы."
...
Рейтинг: 0 / 0
18.10.2005, 01:34
| Ответить | Цитировать | Написать  
Задачка
    #33329327
Фотография SY
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
SY
Участник
И поделом мне. Классический случай который я только-что описал для andrey_anonymous.

SY.
...
Рейтинг: 0 / 0
18.10.2005, 01:53
| Ответить | Цитировать | Написать  
Задачка
    #33329328
Владимир Бегун
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Владимир Бегун
Участник
SYИ поделом мне. Классический случай который я только-что описал для andrey_anonymous.

SY.
Бывает.
...
Рейтинг: 0 / 0
18.10.2005, 01:56
| Ответить | Цитировать | Написать  
Задачка
    #33329664
Stax.
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Stax.
Участник
SY
.....
CREATE OR REPLACE
TRIGGER BIUR_AAA_VIEW
INSTEAD OF INSERT
OR UPDATE
ON AAA_VIEW
FOR EACH ROW
DECLARE
v_cnt NUMBER;
BEGIN
SELECT COUNT(*)
INTO v_cnt
FROM AAA
WHERE LAST_NAME = :NEW.LAST_NAME
AND DEPARTMENT = :NEW.DEPARTMENT;
--
-- а в это время в другой сессии тож вставили две записи :)
--
IF v_cnt >= 3
....
[/src]
SY.
...
Рейтинг: 0 / 0
18.10.2005, 10:36
| Ответить | Цитировать | Написать  
Задачка
    #33329684
mcureenab
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
mcureenab
Участник
SYActually, I overlooked a major issue with your solution. It simply will not work. Assume session A inserts a row (for simplicity lets say table is empty). That row will have index value of 1/#/Q!Q/#/1. Session A does not commit/rollback yet. Now session B tries to insert a row. Obviously, table is locked and session B waits. Now the fun part - read consistency. Since session B transaction started before Session A committed, session B will not see any changes done by session A. So when session A will commit and release the lock, session B will also try to insert index value 1/#/Q!Q/#/1 and will get unique constraint violation.

SY.

"and will get unique constraint violation". Тем не менее ограничение целостности выполняется, хотя в случае конкурентного доступа может возникать ложная тревога. Получив данную ошибку нужно ещё два раза повторить попытку.
...
Рейтинг: 0 / 0
18.10.2005, 10:39
| Ответить | Цитировать | Написать  
Задачка
    #33329819
mcureenab
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
mcureenab
Участник
__LEV__Да согласен, наверно можно упростить.
По поводу дополнительных таблиц - блокировки (dbms_lock)тоже ведь хранятся в системных таблицах - теже таблицы,та же нагрузка на ввод/вывод.
К тому же количество блокировок ограничено (порядок 10^9), боюсь "на всех не хватит".
Могу ошибаться,но мне кажется, что без дополнительной таблицы (своей, системной и т.п.) не обойтись. Сами посудите: анализировать текущую таблицу в любом типе триггера(пакете и т.п.) - бесполезно - не видно не подтвержденных транзакций других сессий. Хочешь упорядочить операции - юзай оракл (ключи, юник индексы и т.п.). Мне кажется что ответив на этот вопрос(с доп таблицами), мы определимся в каком направлении копать.

Я всегда думал, что пользовательские блокировки размещаются в оперативной памяти, более того, количество одновременно существующих блокировок далеко не 10^9, оно не превышает ENQUEUE_RESOURCES.
При чём надо обратить внимание, что оркл вычисляет ENQUEUE_RESOURCES по умолчанию исходя из того, что пользовательских блокировок не будет. При использовании пользовательских блокировок значение этого параметра нужно увеличивать вручную.

У меня с пользовательскими блокировками был случай. Когда параметры DML_LOCKS и ENQUEUE_RESOURCES оказались несогласованными при выделении большого числа пользовательских блокировок падал фоновый процесс сервера, ему не хватало очередизируемых ресурсов.
...
Рейтинг: 0 / 0
18.10.2005, 11:11
| Ответить | Цитировать | Написать  
Задачка
    #33330381
mcureenab
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
mcureenab
Участник
Ещё вот такой вариант. Основан на использовании дополнительной таблицы с номерами однофамильцев.
Триггер автоматически назначает свободный номер, который не занят незавершенными транзакциями.
Если все номера заняты, ждёт освобождения одного из них и повторяет попытку.
Если свободных номеров нет совсем, поднимается dup_val_on_index.
Два триггера нужны для того, чтобы правильно отрабатывать ожидание и удаление 0 строк.
Обновление 0 строк работает не совсем корректно, в том плане что ругается на нарушение уникальности,
тогда как обновлять уже нечего.

Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
70.
71.
72.
73.
74.
75.
76.
77.
78.
79.
80.
81.
82.
83.
84.
85.
86.
87.
88.
89.
90.
91.
92.
93.
94.
95.
96.
97.
98.
99.
100.
101.
102.
103.
104.
105.
106.
107.
108.
109.
110.
111.
112.
113.
114.
115.
116.
117.
118.
119.
120.
121.
122.
123.
124.
125.
126.
127.
128.
129.
130.
create table tbl_le3 (
  name varchar2( 30 ),
  ind integer constraint tbl_le3_ind_ck check(ind between  1  and  3 ),
  constraint tbl_le3_pk primary key (ind, name),
  data varchar2( 30 )
);

create table tbl_le3i(
  name varchar2( 30 ),
  ind integer constraint tbl_le3i_ind_ck check(ind between  0  and  3 ),
  constraint tbl_le3i_pk primary key (name, ind)
)
organization index;

create trigger tbl_le3_betg
before insert or update on tbl_le3
for each row
declare
function take_ind(
  new_name varchar2,
  new_ind number
)return number
is
  MAX_NUM constant number :=  3 ;
  res number;
  found boolean;
  busy boolean;
  resource_busy exception;
  pragma exception_init(resource_busy, - 00054 );
  cursor cr1(i number) is
    select ind
    from tbl_le3i
    where name = new_name and i = ind;
  cursor cr2 is
    select ind
    from tbl_le3i
    where name = new_name and  0  < ind;
  cursor cr3(i number) is
    select ind
    from tbl_le3i
    where name = new_name and i = ind for update nowait;
  cursor cr4(i number) is
    select ind
    from tbl_le3i
    where name = new_name and i = ind for update
;
procedure allocate
-- Размещаем в пуле допустимые номера однофамильцев и номер 0,
-- который означает, что пул выделен.
is
  pragma autonomous_transaction;
  name dbms_sql.varchar2_table;
  ind dbms_sql.number_table;
begin
    for i in  0 ..MAX_NUM loop
        name(i) := new_name;
        ind(i) := i;
    end loop;
    forall i in  0 ..MAX_NUM insert into tbl_le3i (name, ind) values (name(i), ind(i));
    commit;
exception
when dup_val_on_index then
    rollback;
end allocate
;
begin
-- Если пул номеров для имени не выделен, выделяем новый пул
    open cr1( 0 ); fetch cr1 into res; found := cr1%found; close cr1;
    if not found then
        allocate;
    end if;
-- Номер назначен явно, просто блокируем запись или сообщаем, что номер занят.
    if new_ind is not null then
        open cr4(new_ind); fetch cr4 into res; found := cr4%found; close cr4;
        if found then
            return new_ind;
        end if;
        raise dup_val_on_index;
    end if;
-- Ищем и блокируем свободный номер в пуле.
    loop
        found := false; -- Свободных номеров нет
        busy := false; -- Заблокированных номеров нет
        for r in cr2 loop
            begin
                open cr3(r.ind); fetch cr3 into res; found := cr3%found; close cr3;
            exception
            when resource_busy then
                busy := true;  -- Нашли заблокированных номер. Запомним...
                res := r.ind;
            end;
            if found then -- Нашли свободный номер.
                return res;
            end if;
        end loop;
        if busy then -- Подождём освобождения номера.
            open cr4(res); fetch cr4 into res; found := cr4%found; close cr4;
            if found then -- Дождались.
                return res;
            end if; -- Номер занят окончательно. Повторяем попытку
        else -- Все номера заняты
            raise dup_val_on_index;
        end if;
    end loop;
end take_ind
;
begin
    :new.ind := take_ind(:new.name, :new.ind);
-- Удаляем номер из пула
    delete tbl_le3i where name = :new.name and ind = :new.ind;
end;
/
show errors trigger tbl_le3_betg
drop trigger tbl_le3_betg
.

create trigger tbl_le3_aetg
after delete or update on tbl_le3
for each row
begin
-- Возвращаем номер в пул
    insert into tbl_le3i (name, ind) values (:old.name, :old.ind);
end;
/
show errors trigger tbl_le3_aetg
drop trigger tbl_le3_aetg
.

drop table tbl_le3;
drop table tbl_le3i;
...
Рейтинг: 0 / 0
18.10.2005, 13:48
| Ответить | Цитировать | Написать  
Задачка
    #33333370
Фотография andrey_anonymous
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
andrey_anonymous
Участник
SYActually, I overlooked a major issue with your solution. It simply will not work. Assume session A inserts a row (for simplicity lets say table is empty). That row will have index value of 1/#/Q!Q/#/1. Session A does not commit/rollback yet. Now session B tries to insert a row. Obviously, table is locked and session B waits. Now the fun part - read consistency. Since session B transaction started before Session A committed, session B will not see any changes done by session A. So when session A will commit and release the lock, session B will also try to insert index value 1/#/Q!Q/#/1 and will get unique constraint violation.
SY.Описанное Вами поведение имеет место быть, но... Как ни странно, это поведение присуще почти всем приведенным реализациям в той или иной форме, а именно: мы должны каким-либо образом "скоординировать" усилия _всех транзакций, производящих изменения, с точки зрения обеспечения уникальности "троичного" :) ключа. В предложенной реализации FBI я не стал этим заниматься, о чем сразу честно написал.
При этом следует сразу отметить, что если таковая синхронизация технически возможна (в чем я почти не сомневаюсь), то ничто не мешает добавить ее в решение на FBI. НО, повторюсь, эта проблема в той или иной форме присуща _всем приведенным _работающим решениям.
Теперь про "разрушенный" индекс.
Обсуждаемое решение предназначено для обеспечения целостности. Оно изначально не предназначалось для обеспечения _поиска. Поэтому данная критика, хотя и справедлива, не имеет практического значения.
Для DML-операций FBI реализует что-то вроде "стека" индексных ключей, чем и обеспечивает целостность. Я пока не нашел сценария, в котором "порушенные" ROWID помешают выполнению основной задачи.
Попробую резюмировать:
- оно работает
- оно НЕ deterministic, и это надо четко понимать (dbms_output я включил специально дабы можно было понаблюдать за этим забавным процессом)
- оно решает вполне конкретную задачу без претензий на "универсальность"
- оно представляется надежнее "триггерных" решений, поскольку:
как справедливо было отмечено, проверит целостность существующих данных уже при создании индекса.

может либо работать целиком, либо не работать вообще (триггера, например, могут быть отключены по одному и включены обратно с предсказуемыми последствиями в виде сложно проверяемой нарушенной целостности данных)
ВАЖНОЕ ЗАМЕЧАНИЕ : "добавочный" кусок кода - обязательная часть решения.
...
Рейтинг: 0 / 0
19.10.2005, 15:35
| Ответить | Цитировать | Написать  
Задачка
    #33333498
Stax.
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Stax.
Участник
andrey_anonymous
Попробую резюмировать:
- оно работает

Код: plaintext
1.
2.
3.
4.
5.
SQL> delete ane_test;
delete ane_test
       *
ERROR at line  1 :
ORA- 08102 : индексированный ключ не найден, obj#  20873 , dba  21300500  ( 2 )
...
Рейтинг: 0 / 0
19.10.2005, 16:16
| Ответить | Цитировать | Написать  
Задачка
    #33333738
Фотография andrey_anonymous
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
andrey_anonymous
Участник
Stax. andrey_anonymous
Попробую резюмировать:
- оно работает

Код: plaintext
1.
2.
3.
4.
5.
SQL> delete ane_test;
delete ane_test
       *
ERROR at line  1 :
ORA- 08102 : индексированный ключ не найден, obj#  20873 , dba  21300500  ( 2 )
Хм... А у меня так не получается, все исправно удаляется...
Можно весь сценарий?
И еще один момент - Вы "патчик" прикрутили? (иначе будут проблемы со сценариями, включающими rebuild индекса) =)
...
Рейтинг: 0 / 0
19.10.2005, 17:43
| Ответить | Цитировать | Написать  
Задачка
    #33333813
Stax.
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Stax.
Участник
andrey_anonymous Stax. andrey_anonymous
Попробую резюмировать:
- оно работает

Код: plaintext
1.
2.
3.
4.
5.
SQL> delete ane_test;
delete ane_test
       *
ERROR at line  1 :
ORA- 08102 : индексированный ключ не найден, obj#  20873 , dba  21300500  ( 2 )
Хм... А у меня так не получается, все исправно удаляется...
Можно весь сценарий?
И еще один момент - Вы "патчик" прикрутили? (иначе будут проблемы со сценариями, включающими rebuild индекса) =)
патчык не прыкручивал, токо инсерт и делете,
правда я поле ID добавил в таблицу, мне так удобнее,
счас удалю все и постараюсь поторить,
или мона с
Код: plaintext
1.
2.
3.
4.
Name                            Null?    Type
------------------------------- -------- ----
NOM_OTD                         NOT NULL NUMBER
NAME1                           NOT NULL VARCHAR2( 300 )
ID                                       NUMBER( 38 )
жду ответа?
........
Stax
...
Рейтинг: 0 / 0
19.10.2005, 18:17
| Ответить | Цитировать | Написать  
Задачка
    #33333889
Stax.
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Stax.
Участник
Stax.жду ответа?

Ладно пересоздал
Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
 17 : 15 : 03  SQL> create table ane_test( nom_otd number not null -- номер отдела
 17 : 49 : 09     2                        , name1 varchar2( 300 ) not null -- фамилия
 17 : 49 : 09     3   );
create table ane_test( nom_otd number not null -- номер отдела
             *
ERROR at line  1 :
ORA- 00955 : имя уже задействовано для существующего объекта


 17 : 49 : 11  SQL> drop table ane_test;

Table dropped.

 17 : 49 : 24  SQL> create table ane_test( nom_otd number not null -- номер отдела
 17 : 49 : 29     2                        , name1 varchar2( 300 ) not null -- фамилия
 17 : 49 : 29     3   );

Table created.

 17 : 49 : 31  SQL> create unique index ane_test_f_unq on ane_test (substr(ane_test_p.f(nom_otd,name1), 1 , 2000 ));

Index created.

 17 : 50 : 21  SQL> 
 17 : 50 : 21  SQL> ed
Wrote file afiedt.buf

   1   insert into ane_test select  1 ,'q' from dual union all
   2 * select  1 ,'q' from dual
 17 : 51 : 33  SQL> /
insert into ane_test select  1 ,'q' from dual union all
            *
ERROR at line  1 :
ORA- 04068 : существующее состояние пакетов было сброшено
ORA- 04061 : существующее состояния package body "STAX.ANE_TEST_P" стало неприемлемым


 17 : 51 : 34  SQL> /

 2  rows created.

 17 : 51 : 38  SQL> commit;

Commit complete.

 17 : 53 : 15  SQL> delete ane_test;

 2  rows deleted.

 17 : 54 : 27  SQL> commit;

Commit complete.

 17 : 54 : 39  SQL>

Вторая сессия
Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
   1   insert into ane_test select  1 ,'q' from dual union all
   2 * select  1 ,'q' from dual
 17 : 52 : 57  SQL> /
insert into ane_test select  1 ,'q' from dual union all
            *
ERROR at line  1 :
ORA- 04068 : существующее состояние пакетов было сброшено
ORA- 04061 : существующее состояния package body "STAX.ANE_TEST_P" стало неприемлемым
ORA- 04065 : не выполнено, package body "STAX.ANE_TEST_P" изменено или удалено


 17 : 52 : 59  SQL> /
insert into ane_test select  1 ,'q' from dual union all
*
ERROR at line  1 :
ORA- 00001 : нарушено ограничение уникальности (STAX.ANE_TEST_F_UNQ)


 17 : 53 : 15  SQL> /
insert into ane_test select  1 ,'q' from dual union all
*
ERROR at line  1 :
ORA- 00001 : нарушено ограничение уникальности (STAX.ANE_TEST_F_UNQ)


 17 : 53 : 51  SQL> /

 2  rows created.

 17 : 54 : 39  SQL> commit;

Commit complete.

 17 : 55 : 08  SQL> delete ane_test;
delete ane_test
*
ERROR at line  1 :
ORA- 08102 : индексированный ключ не найден, obj#  20879 , dba  21300500  ( 2 )
 17 : 55 : 17  SQL>

.......
Stax
...
Рейтинг: 0 / 0
19.10.2005, 18:59
| Ответить | Цитировать | Написать  
Задачка
    #33333972
Фотография andrey_anonymous
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
andrey_anonymous
Участник
Stax.жду ответа?
Млин, ну ведь терзали же душу смутные сомнения когда я решал - "взрывать" при переполнении или возвращать дублирующий ключ.
Бага.
Все строки
Код: plaintext
rzt := calc_key(p_nom_otd, p_name1, 1 ); -- DUP_VAL_ON_INDEX
следует заменить, к примеру, на
Код: plaintext
raise_application_error(- 20000 ,'в отделе '||p_nom_otd||' не может быть более трех сотрудников с фамилией '||p_name1);
, иначе в некоторых вариантах сценария
- сессия1: delete все вхождения для заданного nom_otd, Name1
- сессия2: insert с переполнением для nom_otd, Name1 (висит, ждет)
- сессия1: commit
- сессия2: _диагностировано переполнение, но запись вставляется, поскольку "гарантированно дублированный ключ" .../#/1 только что удален.
Индекс порушен.
Но к ROWID это не имеет никакого отношения.

Если при обнаружении переполнении просто рвать - все стастается.
Вариант - иметь технологическую запись и в качестве "дублирующего" возвращать "технологический" ключ.
...
Рейтинг: 0 / 0
19.10.2005, 20:23
| Ответить | Цитировать | Написать  
Задачка
    #33333989
Фотография andrey_anonymous
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
andrey_anonymous
Участник
[quot andrey_anonymousЕсли при обнаружении переполнении просто рвать - все стастается.[/quot]
Не срастается :(
Да, приведенный алгоритм организации "стека" ущербен.
1: insert
1: insert
1: commit;
1: delete all
2: insert
1: commit;
2: commit;
тоже приведет к нарушению структуры индекса, поскольку последующий delete будут удалять ключ /#/1, а индексе останется /#/3
:(
...
Рейтинг: 0 / 0
19.10.2005, 20:41
| Ответить | Цитировать | Написать  
Задачка
    #33334002
Stax.
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Stax.
Участник
andrey_anonymous[quot andrey_anonymousЕсли при обнаружении переполнении просто рвать - все стастается.
Не срастается :(
[/quot]
во-во я тож пробовал fbi но на мутацию нарвался,
а автономная не видит даже себя
.....
Stax
...
Рейтинг: 0 / 0
19.10.2005, 21:04
| Ответить | Цитировать | Написать  
Задачка
    #33334016
Фотография andrey_anonymous
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
andrey_anonymous
Участник
Stax.во-во я тож пробовал fbi но на мутацию нарвался,
а автономная не видит даже себя
.....
StaxЯ этого так не оставлю
Задача ставится так: необходимо обеспечить алгоритм формирования индексных ключей такой, что:
1. ключи различны для трех идентичных вхождений (nom_otd, name1).
2. ключ не должен формироваться для четвертого и последующих идентичных вхождений (nom_otd, name1).
3. ключ должен непротиворечиво вычисляться в операциях удаления и вставки узлов индекса.

Собственно, задача - алгоритмическая.
Цимус только в решении без привлечения "посторонних" табличек.
Нужен дешевый способ проверки существования узла индекса с заданным ключем. Тогда delete сможет "пробовать" предполагаемые к удалению ключи, а insert - отыскивать возможные "дырки" и не давать ложных срабатываний.
Можно было бы посмотреть в сторону indextype, но это неспортивно и не позволит оставить за собой приз
...
Рейтинг: 0 / 0
19.10.2005, 21:21
| Ответить | Цитировать | Написать  
Задачка
    #34023389
Oldwalkman
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Oldwalkman
Участник
Подниму-ка... (искал материал по теме truncate vs alter table move вот и наткнулся :) ) Задачка интересная.

Все сказанное ИМХО.

Решение можно найти только тремя путями: (1) +доп таблица, (2) +доп колонка и (3) задействовать AQ.

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

А вот 1 и 2 -- близнецы, только 2-й путь более корявый.

Итак, способ с доп таблицей.

Код: plaintext
1.
2.
3.
4.
5.
6.
create table LOCK2
(
  OTDEL       number        not null,
  LASTNAME    varchar2( 255 ) not null,
  constraint  PK_LOCK2 primary key (OTD, LASTNAME)
);

Оговариваем особо -- все действия по записи в таблицу делать только через наш пакет: insert, move, rename, delete (вставка работника, перемещение меж отделами, смена фамилии, увольнение). Самый простой алгоритм -- для вставки.

0. savepoint lock_begin
1. Делаем insert в таблицу lock2 номера отдела и фамилии
2. Считаем количество однофамильцев в отделе
2.1. Если уже есть три -- rollback до lock_begin и нафик.
2.2. Иначе вставляем в таблицу полноценную запись
3. выполняем delete from lock2 строки с номером отдела и фамилией.
4. commit

В случае переименования или перемещения -- в lock2 пишем две строки. Лучше за один раз, что бы избежать потенциального deadlock (соседняя сессия, к примеру, синхронно вставляет те же две строки, но в обратном порядке)

Удаление -- ну тут как скажут, либо удаляем просто, либо помечаем как уволенного (к слову, у нас ОК не удаляет, employee_id уже за 50К перевалило)

Предложенный способ должен кмк работать в любых случаях -- мы пользуемся стандартными локингами БД, второй инсерт в lock2 с теми же отделом и фамилией просто будет ждать окончательного коммита первого. При чем не мешая работе с другими парами.

Алгоритм, надеюсь, понятен.
...
Рейтинг: 0 / 0
29.09.2006, 18:39
| Ответить | Цитировать | Написать  
Задачка
    #34023456
Sergei.Agalakov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Sergei.Agalakov
Участник
Затык а том, что корректная проверка условия может быть осуществлена только при коммите.
Два пути решения:
1. Было у Tom Kyte несколько раз, например
тут
Строится MV refresh on commit в котором считается число однофамильцев, а на MV создается check constraint.
2. Было в старом Oracle Magazine. В основной таблице создается дополнительная колонка с числом однофамильцев, поддерживается триггерами. На ней висит deferred constaraint, который при коммите проверяет, чтобы однофамильцев было не больше 3.
...
Рейтинг: 0 / 0
29.09.2006, 19:30
| Ответить | Цитировать | Написать  
Задачка
    #34026186
Oldwalkman
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Oldwalkman
Участник
Sergei.AgalakovЗатык а том, что корректная проверка условия может быть осуществлена только при коммите.


Сергей, изюминка в том, что в моем способе получается, что с конкретной парой Отдел-Фамилия работает строго один процесс, остальные с такой же парой ждут. Если пара другая -- "мы друг другу не мешаем".

=Максим
...
Рейтинг: 0 / 0
02.10.2006, 14:58
| Ответить | Цитировать | Написать  
Задачка
    #34026346
Фотография Timm
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Timm
Участник
Офф. забавный термин: "стандартные локинги БД"
хочется добавить: лэтчинги, коммитинги, процедуринги
...
Рейтинг: 0 / 0
02.10.2006, 15:35
| Ответить | Цитировать | Написать  
Задачка
    #34026493
mcureenab
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
mcureenab
Участник
Oldwalkman...
Алгоритм, надеюсь, понятен.

Понятен, только условие авторСоздать триггер базы данных не выполнено.
...
Рейтинг: 0 / 0
02.10.2006, 16:22
| Ответить | Цитировать | Написать  
145 сообщений из 145, показаны все 6 страниц
  все  
Форумы / Oracle [игнор отключен] [закрыт для гостей] / Задачка
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение   Полит. конфиден.  
NoSQL.ru       Кролег: Проекты, Новости, Чат       РЕД ФОРУМ       Фотоальбом: Участники, Встречи
Закрыть
start [/forum/topic.php?all=1&fid=52&tid=1956403]:
 0ms
get settings:
 7ms
get forum list:
 10ms
check forum access:
 3ms
check topic access:
 3ms
track hit:
 39ms
get topic data:
 9ms
get forum data:
 3ms
get page messages:
 137ms
get tp. blocked users:
 1ms
others: 212ms
total:  424ms
созд. / загр.: 424ms
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]