Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Oracle Новый топик    Ответить
Топик располагается на нескольких страницах: Ctrl  назад   1 [2] 3   вперед  Ctrl      все
 Re: Головоломка  [new]
Dimitry Sibiryakov
Member

Откуда:
Сообщений: 51204

НеофитSQL
Условие этой головоломки - определить представляет ли таблица правильное дерево.

Если создание описанных ограничений на реальной таблице закончится с ошибкой их проверки -
дерево неправильное.

Posted via ActualForum NNTP Server 1.5

15 сен 20, 16:07    [22197563]     Ответить | Цитировать Сообщить модератору
 Re: Головоломка  [new]
НеофитSQL
Member

Откуда: Маями
Сообщений: 87
Dimitry Sibiryakov

НеофитSQL
Условие этой головоломки - определить представляет ли таблица правильное дерево.

Если создание описанных ограничений на реальной таблице закончится с ошибкой их проверки -
дерево неправильное.


Так тоже можно.
Начать с предположения что дерево правильное, попробовать применить операции которые вылетят на плохом дереве, и словить exception, который скажет об определенном аспекте неправильности дерева. Это теория; пока кода нет, море по колено :)

На практике, вместо вылета кода можно попасть в бесконечный цикл, вычисляя уровень иерархии на цикле.
Бесконечный цикл не соответствует постановке задачи (запрос/функция должна вернуться в конечное время).
15 сен 20, 16:17    [22197566]     Ответить | Цитировать Сообщить модератору
 Re: Головоломка  [new]
booby
Member

Откуда:
Сообщений: 2071
НеофитSQL,

про бесконечный цикл вам рассказали во второй строке вот этого сообщения:

andrey_anonymous
Тривиально: пройтись по всем записям, выстраивая иерархию от подчиненного к менеджеру.
Обработать exception "connect-by loop in user data" либо проконтролировать соответствующий флаг.
Отобрать листья.
Сгруппировать листья и убедиться, что результатом группировки является единственная запись и эта запись соответствует корню иерархии.
Фсьо.


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

Сообщение было отредактировано: 15 сен 20, 16:28
15 сен 20, 16:30    [22197586]     Ответить | Цитировать Сообщить модератору
 Re: Головоломка  [new]
НеофитSQL
Member

Откуда: Маями
Сообщений: 87
Обкатав мое решение на нескольких таблицах разной степени поломанности,
я комфортабельно его размещаю как одно из возможных правильных.
Вчера я не знал, как это написать в виде запроса, только алгоритмически с PL/SQL.
Сегодня, благодаря участникам форума я освоил один из вариантов connect-by.

-- проверка на древесность:
-- 1) убедиться что ровно один корень (это также отсечет пустые таблицы)
-- 2) начиная с корня, посчитать всех подчиненых. Если совпало с размером таблицы - это дерево!
-- 3) "nocycle" здесь не требуется, этот селект защищен от встречи с циклами
-- 
lock table EMPLOYEES in share mode; -- защитить проверку от реорганизации в головоломке :)
select 'Дерево' as answer 
from dual
where (select count(*) from EMPLOYEES where mid is null) = 1 and
      (select count(*) from EMPLOYEES)  = 
      (select count(*) from EMPLOYEES
        start with mid is null
      connect by prior eid = mid);


Мне неизвестна скорость данного решения, и нет возможности проверить эмпирически.
15 сен 20, 16:42    [22197601]     Ответить | Цитировать Сообщить модератору
 Re: Головоломка  [new]
xtender
Member

Откуда: Мск
Сообщений: 5617
НеофитSQL
проверка на древесность
тихий ужас...
НеофитSQL
lock table EMPLOYEES in share mode; -- защитить проверку от реорганизации в головоломке :)
мда. ну пусть хотя бы так пока...

НеофитSQL
1) убедиться что ровно один корень (это также отсечет пустые таблицы)

это можно сделать навесив уникальный ключ на decode(mid,null,1)
15 сен 20, 16:57    [22197618]     Ответить | Цитировать Сообщить модератору
 Re: Головоломка  [new]
Dimitry Sibiryakov
Member

Откуда:
Сообщений: 51204

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

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

Posted via ActualForum NNTP Server 1.5

15 сен 20, 17:14    [22197643]     Ответить | Цитировать Сообщить модератору
 Re: Головоломка  [new]
НеофитSQL
Member

Откуда: Маями
Сообщений: 87
Подведем итоги.

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

connect-by - отличная штука, и даже умеет обрабатывать циклы, когда требуется.

Как было неоднократно упомянуто уважаемыми коллегами, использование constraints позволяет сохранить ссылочную целостность, хотя бы частично. Например, в головоломке используется таблица EMPLOYEES такого сорта:

create table EMPLOYEES
(
  eid INTEGER not null,
  mid INTEGER
)
tablespace USERS
...
alter table EMPLOYEES
  add constraint PK__EMPLOYEES_EID primary key (EID)
  using index 
...
alter table EMPLOYEES
  add constraint FK__EMPLOYEES_MID foreign key (MID)
..
-- Create/Recreate check constraints 
alter table EMPLOYEES
  add constraint NOSELFREPORT  check (mid != eid);
alter table EMPLOYEES
  add constraint ONE_CEO       check (/*селект=1 тут нельзя*/);


Уникальные ID? сделано через PK
Контроль ссылок менеджера? сделано через FK (но позволен null, для гендиректора)
Защита от тривиальных циклов? Есть, проверка NOSELFREPORT
Защита от лишних гендиректоров? Тут проблема, синтакс не позволяет select,
поэтому такую проверку скорее всего делают в триггере.
15 сен 20, 17:43    [22197674]     Ответить | Цитировать Сообщить модератору
 Re: Головоломка  [new]
Dimitry Sibiryakov
Member

Откуда:
Сообщений: 51204

НеофитSQL
поэтому такую проверку скорее всего делают в триггере.

Нет, не делают, потому что и там она совершенно неработоспособна.

Posted via ActualForum NNTP Server 1.5

15 сен 20, 17:44    [22197675]     Ответить | Цитировать Сообщить модератору
 Re: Головоломка  [new]
env
Member

Откуда: Россия, Москва
Сообщений: 6153
НеофитSQL,

Молодец. А практический смысл у этой задачи какой?
15 сен 20, 17:47    [22197679]     Ответить | Цитировать Сообщить модератору
 Re: Головоломка  [new]
Stax
Member

Откуда: Ukraine,Lviv
Сообщений: 2557
НеофитSQL


Мне неизвестна скорость данного решения, и нет возможности проверить эмпирически.


табличка не дерево
SQL> ed
Wrote file afiedt.buf

  1  with EMPLOYEES as (
  2  select 0 eid , null mid from  dual union all
  3  select 1,0 from dual union all
  4  select 3,1 from dual union all
  5  select 4,0 from dual union all
  6  select 3,4 from dual union all
  7  select 5,3 from dual union all
  8  select 8,9 from dual )
  9  select 'Дерево' as answer
 10  from dual
 11  where (select count(*) from EMPLOYEES where mid is null) = 1 and
 12        (select count(*) from EMPLOYEES)  =
 13        (select count(*) from EMPLOYEES
 14          start with mid is null
 15*       connect by prior eid = mid)
SQL> /

ANSWER
------
Дерево


.....
stax
15 сен 20, 17:49    [22197681]     Ответить | Цитировать Сообщить модератору
 Re: Головоломка  [new]
НеофитSQL
Member

Откуда: Маями
Сообщений: 87
Stax,

точно, это не дерево. Но нарушена ссылочная целостность, которая .... погодите... так и не была указана в условиях задачи.
Контрпример принимается - алгоритм не все ловит. Наметаный у вас, однако, глаз!

Оказывается дело даже не в желтой строчке, мой алгоритм отлавливает несуществующих менеджеров.
Дело в том, что нарушена уникальность работников (номер 3 есть дважды), и вот это привело к +1/-1.

Использую ваш контрпример, чтобы добавить еще одно условие - уникальность eid и его наличие.

with EMPLOYEES as (
  select 0 eid , null mid from  dual union all
  select 1,0 from dual union all
  select 3,1 from dual union all
  select 4,0 from dual union all
  select 3,4 from dual union all
  select 5,3 from dual union all
  select 8,9 from dual )
  select 'Дерево' as answer
  from dual
   where (select count(*) from EMPLOYEES where mid is null) = 1 and
         (select count(*) from EMPLOYEES where eid is null) = 0 and
         (select count(*) from EMPLOYEES)  = 
         (select count(*) from (select distinct eid from EMPLOYEES)) and 
         (select count(*) from EMPLOYEES)  =
         (select count(*) from EMPLOYEES
           start with mid is null
         connect by prior eid = mid)
15 сен 20, 18:29    [22197715]     Ответить | Цитировать Сообщить модератору
 Re: Головоломка  [new]
SY
Member

Откуда: Middlebury, CT USA
Сообщений: 9950
НеофитSQL


В многопользовательском режиме CONNECT BY исполняется по-другому, и ему нельзя доверять?


Похоже у тебя нет понятия что такое многопользовательский режим. Если Петя и Вася открыли один и тот-же файл в notepad Петя не видит Васины изменения а Вася Петины. То же и в Oracle. Петя поменял mgr Маши на себя но еще не закоммитил а в это время Вася поменял Петиного mgr на Машу. Оба запустили проверку и все пучком ибо Петя не видит Васины изменения а Вася Петины хотя есть цикл. И без сериализации тут никак.

SY.
15 сен 20, 18:38    [22197721]     Ответить | Цитировать Сообщить модератору
 Re: Головоломка  [new]
xtender
Member

Откуда: Мск
Сообщений: 5617
НеофитSQL
Головоломка решена
не решена. И впредь читай правила форума и давай адекватное название топику.
НеофитSQL
Были поползновения сделать вклад от некоторых участников, но побрюзжав с галерки, они так и не родили ни единой строчки рабочего кода, сберегая силы.Несомненно, что справились бы одной левой если б только..
пиши-пиши-пиши - доки не читай, поиском не пользуйся...
15 сен 20, 19:01    [22197733]     Ответить | Цитировать Сообщить модератору
 Re: Головоломка  [new]
xtender
Member

Откуда: Мск
Сообщений: 5617
Адекватные решения известны и сто раз на форуме озвучивались. Самое правильное - использовать единую точку входа, т.е создать процедуру и использовать ее.

Простой пример:
+ тестовая табличка с данными
create table t(
   id int, 
   parent_id int, 
   constraint t_pk primary key(id), 
   constraint t_parent_fk foreign key (parent_id) references t(id) 
)
/
create index t_ix_par on t(parent_id,id)
/
insert into t select level,nullif(level,1)-1 from dual connect by level<=5;
commit;

SQL> select * from t order by id;

        ID  PARENT_ID
---------- ----------
         1       NULL
         2          1
         3          2
         4          3
         5          4

+ пример процедуры для изменения ссылки на родительскую запись
create or replace procedure update_parent(pID int, pNewParent int) as
  cnt int;
  -- connect_by in user data:
  e_cycle exception;
  pragma exception_init(e_cycle, -1436 );
  
  procedure lock_parents(pLockID in int)
  as
     parents sys.odcinumberlist;
     cr sys_refcursor;
  begin
      select id
         bulk collect into parents
      from t
      start with id=pLockID
      connect by prior parent_id = id;

      open cr for select * from t where id in (select * from table(parents)) for update;
  end;
begin
   -- разрешаем иметь несколько ROOTs:
   if pNewParent is not null then
      -- залочим будущих родителей
      lock_parents(pNewParent);

      -- проверим на iscycle:
      select count(*)
         into cnt
      from t
      where connect_by_iscycle = 1
      or pID = id
      start with id=pNewParent
      connect by nocycle prior parent_id = id;

      if cnt>0 then
         raise e_cycle;
      end if;
   end if;
   -- update если все ок:
   update t set parent_id=pNewParent where id=pID;
end;
/

+ Пример обновлений:
SQL> select * from t order by id;

        ID  PARENT_ID
---------- ----------
         1
         2          1
         3          2
         4          3
         5          4

SQL> exec update_parent(3,5);
BEGIN update_parent(3,5); END;

*
ERROR at line 1:
ORA-01436: CONNECT BY loop in user data
ORA-06512: at "XTENDER.UPDATE_PARENT", line 36
ORA-06512: at line 1

SQL> exec update_parent(3,1);

PL/SQL procedure successfully completed.

SQL> select * from t order by id;

        ID  PARENT_ID
---------- ----------
         1
         2          1
         3          1
         4          3
         5          4

5 rows selected.


Другое решение (крайне не рекомендуемое из-за потенциальной несогласованности) - использовать materialized view on commit c проверкой:
+ создаем функцию с проверкой
create or replace function check_parents(pID in int) return int deterministic as
  -- connect_by in user data:
  e_cycle exception;
  pragma exception_init(e_cycle, -1436 );

  function check_parents(pLockID in int) return int
  as
  cnt int;
     parents sys.odcinumberlist;
  begin
      -- проверим на iscycle:
      select count(*) into cnt
      from t
      where connect_by_iscycle = 1
      start with id=pLockID
      connect by nocycle prior parent_id = id;

      return cnt;
  end;
begin
   if check_parents(pID) >0 then
      raise e_cycle;
   end if;
   return 1;
end;
/
+ создаем materialized view
create materialized view log on t with rowid, primary key;
create materialized view t_mv 
refresh complete on commit
as
select check_parents(id) chk,id,parent_id from t
/
+ пример обновлений
SQL> select * from t order by id;

        ID  PARENT_ID
---------- ----------
         1
         2          1
         3          2
         4          3
         5          4

5 rows selected.

SQL> update t set parent_id=5 where id=3;

1 row updated.

SQL> select * from t order by id;

        ID  PARENT_ID
---------- ----------
         1
         2          1
         3          5
         4          3
         5          4

5 rows selected.

SQL> commit;
commit
*
ERROR at line 1:
ORA-12008: error in materialized view or zonemap refresh path
ORA-01436: CONNECT BY loop in user data
ORA-06512: at "XTENDER.CHECK_PARENTS", line 13
ORA-06512: at "XTENDER.CHECK_PARENTS", line 32


SQL> select * from t order by id;

        ID  PARENT_ID
---------- ----------
         1
         2          1
         3          2
         4          3
         5          4

5 rows selected.
между проверкой и коммитом может успеть пройти чужой коммит. Такая же хрень будет и с триггерной проверкой.

Итого: без блокировки родительских строк 100% обеспечить надежность проверки нельзя

Сообщение было отредактировано: 15 сен 20, 19:48
15 сен 20, 19:26    [22197743]     Ответить | Цитировать Сообщить модератору
 Re: Головоломка  [new]
НеофитSQL
Member

Откуда: Маями
Сообщений: 87
SY
НеофитSQL


В многопользовательском режиме CONNECT BY исполняется по-другому, и ему нельзя доверять?


Похоже у тебя нет понятия что такое многопользовательский режим. Если Петя и Вася открыли один и тот-же файл в notepad Петя не видит Васины изменения а Вася Петины. То же и в Oracle. Петя поменял mgr Маши на себя но еще не закоммитил а в это время Вася поменял Петиного mgr на Машу. Оба запустили проверку и все пучком ибо Петя не видит Васины изменения а Вася Петины хотя есть цикл. И без сериализации тут никак.

SY.


Это объяснение на доступном мне языке. Пример внятный, но он относится к писакам, а не к читакам.
Я по неопытности перепугался что мне вася/петя могут таблицу изменить во время исполнения моего запроса.
Но, почитав документацию, успокоился - Оракл гарантирует что моему запросу на лету таблицу не изменят,
поэтому программистам читающего кода о таких вещах беспокоиться не приходится.
Тем более в головоломке на сообразительность.

Про сериализацию нашел материал, почитаю как Оракл разрешает коммит- и другие конфликты. Спасибо
15 сен 20, 19:28    [22197745]     Ответить | Цитировать Сообщить модератору
 Re: Проверка на циклы в иерархической таблице(Головоломка)  [new]
xtender
Member

Откуда: Мск
Сообщений: 5617
virtual columns + check constraint основанные на такой функции будут вызывать ORA-04091: table is mutating, trigger/function may not see it:
+
create or replace function check_parents_virtual(pID in int, pParentId in int) return int deterministic as
  -- connect_by in user data:
  e_cycle exception;
  pragma exception_init(e_cycle, -1436 );

  function check_parents(pLockID in int) return int
  as
  cnt int;
     parents sys.odcinumberlist;
  begin
      -- проверим на iscycle:
      select count(*) into cnt
      from t
      where connect_by_iscycle = 1
      start with id=pLockID
      connect by nocycle prior parent_id = id;

      return cnt;
  end;
begin
   if check_parents(pID) >0 then
      raise e_cycle;
   end if;
   return 1;
end;
/
alter table t
  add chk number(1) generated always as (check_parents_virtual(id,parent_id));
alter table t
  add constraint t_chk check (chk=1) INITIALLY DEFERRED DEFERRABLE;
 
create index t_ix_chk on t(chk);
SQL> update t set parent_id=5 where id=3;
update t set parent_id=5 where id=3
*
ERROR at line 1:
ORA-04091: table XTENDER.T is mutating, trigger/function may not see it
ORA-06512: at "XTENDER.CHECK_PARENTS_VIRTUAL", line 11
ORA-06512: at "XTENDER.CHECK_PARENTS_VIRTUAL", line 27


Сообщение было отредактировано: 15 сен 20, 19:49
15 сен 20, 19:43    [22197757]     Ответить | Цитировать Сообщить модератору
 Re: Проверка на циклы в иерархической таблице(Головоломка)  [new]
SY
Member

Откуда: Middlebury, CT USA
Сообщений: 9950
НеофитSQL

Я по неопытности перепугался что мне вася/петя могут таблицу изменить во время исполнения моего запроса.
Но, почитав документацию, успокоился - Оракл гарантирует что моему запросу на лету таблицу не изменят


Ты, по неопытности, не пугашлся ибо лезешь напролом не осозновая опасностей. "Оракл гарантирует что моему запросу на лету таблицу не изменят" - это и есть statement level read consistency, a.k.a. запрос видит данные так как они были на момент запроса. Т.е. изменения даже закоммиченные но после твоего запроса проверки целостности дерева не увидит и цикл прошляпит.

SY.
15 сен 20, 19:46    [22197760]     Ответить | Цитировать Сообщить модератору
 Re: Проверка на циклы в иерархической таблице(Головоломка)  [new]
НеофитSQL
Member

Откуда: Маями
Сообщений: 87
xtender

Итого: без блокировки родительских строк 100% обеспечить надежность проверки нельзя


Много красивого и интересного кода, с удовольствием посмотрел, кое-что понял.

SY и xtender подняли тему важную тему сериализации/блокировки, важную для сохранения целостности данных.

Мое мнение по этому поводу: программисты которые пишут только читающий код (те же запросы), имеют полный иммунитет,
и вправе писать запросы как если бы они были единственным пользователем этой базы.

select count(*) from TABLE всегда даст правильное число строчек для того контекста, в котором она вызвана.
Для писаки Васи это пять, для писаки Пети восемь, а для читаки Нины - три.

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

Я не представляю ситуацию, когда читающий код возьмет лок, без всякого намерения писать в базу.
Случай (SELECT ... FOR UPDATE) сюда не входит, читаки его не вызывают.
15 сен 20, 19:47    [22197761]     Ответить | Цитировать Сообщить модератору
 Re: Проверка на циклы в иерархической таблице(Головоломка)  [new]
НеофитSQL
Member

Откуда: Маями
Сообщений: 87
SY
НеофитSQL

Я по неопытности перепугался что мне вася/петя могут таблицу изменить во время исполнения моего запроса.
Но, почитав документацию, успокоился - Оракл гарантирует что моему запросу на лету таблицу не изменят


Ты, по неопытности, не пугашлся ибо лезешь напролом не осозновая опасностей. "Оракл гарантирует что моему запросу на лету таблицу не изменят" - это и есть statement level read consistency, a.k.a. запрос видит данные так как они были на момент запроса. Т.е. изменения даже закоммиченные но после твоего запроса проверки целостности дерева не увидит и цикл прошляпит.

SY.


"Чукча не писатель, чукча читатель".

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

Это интересная и сложная тема, с которой я буду разбираться когда начну писать в таблицы.
15 сен 20, 20:07    [22197774]     Ответить | Цитировать Сообщить модератору
 Re: Проверка на циклы в иерархической таблице(Головоломка)  [new]
andrey_anonymous
Member

Откуда: Москва
Сообщений: 18150
НеофитSQL
размещаю как одно из возможных правильных.

Ну раз размещаете...
то вот Вам еще одно "возможно правильное" :)
Я обычно в таких ситуациях накидываю 5-6 вариантов, но сегодня что-то слишком много смеялся.
select coalesce(decode(count(count(e.mid)), 1, 'Дерево'), 'Не дерево') "Дерево?"
 from EMPLOYEES e
connect by nocycle eid = prior mid 
group by mid
having max(connect_by_isleaf) = 1

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

Примите во внимание - и коллеги не будут обходиться одними лишь ехидными комментариями.
15 сен 20, 20:22    [22197778]     Ответить | Цитировать Сообщить модератору
 Re: Проверка на циклы в иерархической таблице(Головоломка)  [new]
andrey_anonymous
Member

Откуда: Москва
Сообщений: 18150
НеофитSQL
select count(*) from TABLE всегда даст правильное число строчек для того контекста, в котором она вызвана.

Чтите концепты ибо они рулез.
Умолчательный уровень изоляции делает Ваше утверждение в общем случае не верным, если Вы выдаете несколько запросов подряд.
К примеру, следующий за select count(*) запрос может зависеть от полученного результата, но "контекст" (на самом деле точка согласования, контекст в oracle - это другое) у него будет уже иной.
15 сен 20, 20:32    [22197787]     Ответить | Цитировать Сообщить модератору
 Re: Проверка на циклы в иерархической таблице(Головоломка)  [new]
НеофитSQL
Member

Откуда: Маями
Сообщений: 87
andrey_anonymous
НеофитSQL
select count(*) from TABLE всегда даст правильное число строчек для того контекста, в котором она вызвана.

Чтите концепты ибо они рулез.
Умолчательный уровень изоляции делает Ваше утверждение в общем случае не верным, если Вы выдаете несколько запросов подряд.
К примеру, следующий за select count(*) запрос может зависеть от полученного результата, но "контекст" (на самом деле точка согласования, контекст в oracle - это другое) у него будет уже иной.


Резонно. Я с этим не сталкивался, но из похожих соображений не так давно переделал код из такого:

-- ожидается 0 или 1. если 1, обработать
if (select count(*) from tbl where <condition>) = 1 then
begin
     select col1 into var1 from tbl where <condition>;
    ....


в код, где ловил exception. Не было уверенности что эти два селекта вызвались бы на одном и том же контексте точке согласования.

Я ожидаю много интересного когда потребуется делать изменения в базу.
Благо, в моем коллективе есть опытные кодеры.
15 сен 20, 20:49    [22197808]     Ответить | Цитировать Сообщить модератору
 Re: Проверка на циклы в иерархической таблице(Головоломка)  [new]
andrey_anonymous
Member

Откуда: Москва
Сообщений: 18150
НеофитSQL
-- ожидается 0 или 1. если 1, обработать
if (select count(*) from tbl where <condition>) = 1 then
begin
     select col1 into var1 from tbl where <condition>;
    ....

Один из самых дурных антипаттернов в моем личном рейтинге.
Если уж очень-очень надо, то существование записи лучше проверять
for i in (select null from dual where exists(select null from tbl where <condition>)) loop
 -- делаем что-то полезное при существовании записи, отвечающей условиям
end loop;

но это тоже не фонтан.

Сообщение было отредактировано: 15 сен 20, 20:55
15 сен 20, 20:55    [22197813]     Ответить | Цитировать Сообщить модератору
 Re: Проверка на циклы в иерархической таблице(Головоломка)  [new]
SY
Member

Откуда: Middlebury, CT USA
Сообщений: 9950
НеофитSQL,

Последняя попытка.

+

DROP TABLE TBL PURGE
/
CREATE TABLE TBL(
                 EMPNO VARCHAR2(10),
                 MGR VARCHAR2(10)
                )
/
INSERT
  INTO TBL
  VALUES(
         'BOSS',
         NULL
        )
/
INSERT
  INTO TBL
  VALUES(
         'VASYA',
         'BOSS'
        )
/
INSERT
  INTO TBL
  VALUES(
         'PETYA'
         'BOSS'
        )
/
INSERT
  INTO TBL
  VALUES(
         'MASHA',
         'BOSS'
        )
/
COMMIT
/



Имеем:

SQL> SELECT  *
  2    FROM  TBL
  3  /

EMPNO      MGR
---------- ----------
BOSS
MASHA      BOSS
PETYA      BOSS
VASYA      BOSS

SQL>


Сессия 1:

SQL> UPDATE TBL
  2     SET MGR = 'VASYA'
  3     WHERE EMPNO = 'MASHA'
  4  /

1 row updated.

SQL>-- Проверяем на циклы
SQL> SELECT  *
  2    FROM  TBL
  3    CONNECT BY MGR = PRIOR EMPNO
  4  /

EMPNO      MGR
---------- ----------
PETYA      BOSS
VASYA      BOSS
MASHA      VASYA
MASHA      VASYA
BOSS
PETYA      BOSS
VASYA      BOSS
MASHA      VASYA

8 rows selected.

SQL>


Все пучком. В это же время сессия 2:

SQL> UPDATE TBL
  2     SET MGR = 'MASHA'
  3     WHERE EMPNO = 'VASYA'
  4  /

1 row updated.

SQL>-- Проверяем на циклы
SQL> SELECT  *
  2    FROM  TBL
  3    CONNECT BY MGR = PRIOR EMPNO
  4  /


EMPNO      MGR
---------- ----------
MASHA      BOSS
VASYA      MASHA
PETYA      BOSS
VASYA      MASHA
BOSS
MASHA      BOSS
VASYA      MASHA
PETYA      BOSS

8 rows selected.

SQL>


Все пучком и обе сессии со спокойной совестью делают commit. В результате имеем:

SQL>-- Проверяем на циклы
SQL> SELECT  *
  2    FROM  TBL
  3    CONNECT BY MGR = PRIOR EMPNO
  4  /
ERROR:
ORA-01436: CONNECT BY loop in user data



no rows selected

SQL>


SY.
15 сен 20, 21:12    [22197828]     Ответить | Цитировать Сообщить модератору
 Re: Проверка на циклы в иерархической таблице(Головоломка)  [new]
andrey_anonymous
Member

Откуда: Москва
Сообщений: 18150
SY
Последняя попытка.


Соломон, не надо нагнетать.
ТС не ставил задачу "обеспечить целостность".
Он ставил задачу "проконтролировать, что в таблице дерево" на предмет "поучиться sql-ить".
И даже пробовал отбиться от ревнителей посредством блокировки :)
15 сен 20, 21:19    [22197833]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: Ctrl  назад   1 [2] 3   вперед  Ctrl      все
Все форумы / Oracle Ответить