Отправляет email-рассылки с помощью сервиса Sendsay

Открыто о СУБД Oracle на русском

Рассылка закрыта

При закрытии подписчики были переданы в рассылку "СУБД Oracle "с нуля"" на которую и рекомендуем вам подписаться.

Вы можете найти рассылки сходной тематики в Каталоге рассылок.

←   Октябрь 2002   →
 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
Автор
Сергей

Статистика

10.773 подписчиков
-8 за неделю
←   Все выпуски   →

Открыто о СУБД Oracle на русском : рекурсивное усечение таблиц


Информационный Канал Subscribe.Ru

Выпуск 16

Рекурсивное усечение таблиц

Уважаемые подписчики! Этот выпуск посвящен процедуре рекурсивного усечения (TRUNCATE) таблиц, связанных ограничениями целостности ссылок. Том Кайт отвечал на этот вопрос последний раз 16 октября 2002 года. Вашему вниманию предлагается сокращенный перевод соответствующего обсуждения.

Я получил формальное разрешение Тома Кайта на использование его материалов на сайте и в рассылке: 

As long as you include a "copyright Oracle Corporation 2002", refer to 
me and have a link to the asktom.oracle.com website -- I have no problem 
with it.


Valeriy Kravchuk wrote:

>Dear Sir,
>
>I would like to translate some of your great articles/answers from your
>asktom.oracle.com site into Russian, put these translations
>on my site (http://openxs.net/projects/oracle/) and send them to my
>subscribers (about 3000) through the appropriate mailing list. These
>translations will be interesting and relevant to many Oracle DBAs and
>developers in Russia and other CIS countries.
>
>Would you, please, permit me to make and distribute these translations
>with references to the original publications. Is it really possible? Inform
>me about any additional conditions and requirements, if any.
>
>Thank you in advance.
>
>Sincerely yours,
>
>  Valeriy Kravchuk,
>  OpenXS Intitiative.
>
>  
>

-- 
Thanks
-- 
Thomas Kyte tkyte@oracle.com
Vice President, Core Technologies, OGEH

Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/ AskTom online:
http://asktom.oracle.com/

Чтобы выполнить формальные требования этого разрешения, я информирую вас о том, что в выпусках, построенных на материалах сайта AskTom, авторское право на текст принадлежит корпорации Oracle, о чем я и буду писать в конце выпуска. Авторское право на перевод и комментарии принадлежит мне. Я буду явно начинать свои комментарии строкой "Комментарий В.К." и завершать строкой "Конец комментария".

Рекурсия

Привет, Том!

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

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

Комментарий В.К.
Далее шел текст процедуры для решения этой проблемы, но она выдавала ошибку. Я выкинул его, поскольку Том Кайт его не комментировал, а просто предложил свое решение.
Конец комментария

Не могли бы вы помочь разобраться с этим?

Ответ Тома Кайта:

Для этого я использую следующую процедуру:

ops$tkyte@ORA920.US.ORACLE.COM> create table p1 (x int primary key); 

Table created. 

ops$tkyte@ORA920.US.ORACLE.COM> create table p2 (x int primary key); 

Table created. 

ops$tkyte@ORA920.US.ORACLE.COM> create table c1 (x int primary key, y references p1, z references p2); 

Table created. 

ops$tkyte@ORA920.US.ORACLE.COM> create table c2 (x int primary key, a references c1, b references p1); 

Table created. 

ops$tkyte@ORA920.US.ORACLE.COM> create table c3 (x int primary key, m references c2, n references p2, o references c3); 

Table created. 

ops$tkyte@ORA920.US.ORACLE.COM> create table c4 (x int primary key, y references p2); 

Table created. 

ops$tkyte@ORA920.US.ORACLE.COM> create table c5 (x int primary key, z references c4); 

Table created. 

ops$tkyte@ORA920.US.ORACLE.COM> create table c6 (x int primary key, a references c5, b references p1); 

Table created. 

ops$tkyte@ORA920.US.ORACLE.COM> create or replace package util
  2  as
  3      procedure recursive_truncate(p_tname in varchar2, p_level in number default 1);
  4  end;
  5  / 

Package created. 

ops$tkyte@ORA920.US.ORACLE.COM> create or replace package body util
  2  as
  3
  4  type stack is table of varchar2(32000) index by binary_integer;
  5  g_stack stack;
  6  g_empty_stack stack;
  7
  8  procedure do_ddl(p_stmt in long, p_level in number)
  9  is
 10  begin
 11      dbms_output.put_line(rpad(chr(9), p_level-1, chr(9)) || p_stmt);
 12      execute immediate p_stmt;
 13  end do_ddl;
 14
 15  procedure recursive_truncate(p_tname in varchar2, p_level in number default 1)
 16  as
 17  begin
 18      if (p_level = 1)
 19      then
 20          g_stack := g_empty_stack;
 21      end if;
 22
 23      dbms_output.put_line(rpad(chr(9), p_level-1, chr(9)) || 'Processing Table ' || p_tname);
 24      for x in (select *
 25                  from user_constraints
 26                 where constraint_type = 'R'
 27                   and table_name <> p_tname
 28                   and r_constraint_name in (select constraint_name
 29                                               from user_constraints
 30                                              where table_name = p_tname
 31                                                and constraint_type in ('P', 'U')
 32                                            )
 33               )
 34      loop
 35          do_ddl('alter table ' || x.table_name || ' disable constraint ' || x.constraint_name, p_level);
 36          g_stack(g_stack.count+1) := 'alter table ' || x.table_name || ' enable constraint ' || x.constraint_name;
 37          recursive_truncate(x.table_name, p_level+1);
 38          do_ddl('truncate table ' || x.table_name, p_level);
 39      end loop;
 40      if (p_level = 1)
 41      then
 42          for i in 1 .. g_stack.count
 43          loop
 44              do_ddl(g_stack(i), p_level);
 45          end loop;
 46      end if;
 47  end recursive_truncate;
 48
 49  end util;
 50  / 

Package body created. 

ops$tkyte@ORA920.US.ORACLE.COM> exec util.recursive_truncate('P1');
Processing Table P1
alter table C1 disable constraint SYS_C003158
        Processing Table C1
        alter table C2 disable constraint SYS_C003161
                Processing Table C2
                alter table C3 disable constraint SYS_C003164
                        Processing Table C3
                truncate table C3
        truncate table C2
truncate table C1
alter table C2 disable constraint SYS_C003162
        Processing Table C2
        alter table C3 disable constraint SYS_C003164
                Processing Table C3
        truncate table C3
truncate table C2
alter table C6 disable constraint SYS_C003173
        Processing Table C6
truncate table C6
alter table C1 enable constraint SYS_C003158
alter table C2 enable constraint SYS_C003161
alter table C3 enable constraint SYS_C003164
alter table C2 enable constraint SYS_C003162
alter table C3 enable constraint SYS_C003164
alter table C6 enable constraint SYS_C003173 

PL/SQL procedure successfully completed. 

ops$tkyte@ORA920.US.ORACLE.COM> exec util.recursive_truncate('P2');
Processing Table P2
alter table C1 disable constraint SYS_C003159
        Processing Table C1
        alter table C2 disable constraint SYS_C003161
                Processing Table C2
                alter table C3 disable constraint SYS_C003164
                        Processing Table C3
                truncate table C3
        truncate table C2
truncate table C1
alter table C3 disable constraint SYS_C003165
        Processing Table C3
truncate table C3
alter table C4 disable constraint SYS_C003168
        Processing Table C4
        alter table C5 disable constraint SYS_C003170
                Processing Table C5
                alter table C6 disable constraint SYS_C003172
                        Processing Table C6
                truncate table C6
        truncate table C5
truncate table C4
alter table C1 enable constraint SYS_C003159
alter table C2 enable constraint SYS_C003161
alter table C3 enable constraint SYS_C003164
alter table C3 enable constraint SYS_C003165
alter table C4 enable constraint SYS_C003168
alter table C5 enable constraint SYS_C003170
alter table C6 enable constraint SYS_C003172 

PL/SQL procedure successfully completed. 

ops$tkyte@ORA920.US.ORACLE.COM>

Комментарий читателя от 12 октября 2002 года

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

create table x1 (col1 number, col_fk number);
alter table x1 add constraint x1_pk primary key (col1); 

create table x2 (col1 number, col_fk number);
alter table x2 add constraint x2_pk primary key (col1); 

alter table x1 add constraint x1_x2_fk foreign key (col_fk) references x2(col1);
alter table x2 add constraint x2_x1_fk foreign key (col_fk) references x1(col1); 

insert into x1 values (1, null);
insert into x2 values (1, null);
insert into x1 values (2, 1);
insert into x2 values (2, 1); 

begin
util.recursive_truncate('X1');
end; 

Processing Table X1
alter table X2 disable constraint X2_X1_FK
    Processing Table X2
    alter table X1 disable constraint X1_X2_FK
        Processing Table X1
        alter table X2 disable constraint X2_X1_FK
            Processing Table X2
            alter table X1 disable constraint X1_X2_FK
                Processing Table X1
                alter table X2 disable constraint X2_X1_FK
                    Processing Table X2
                    alter table X1 disable constraint X1_X2_FK
                        Processing Table X1
                        alter table X2 disable constraint X2_X1_FK
                            Processing Table X2
                            ...
                           
ORA-20000: ORU-10028: line length overflow, limit of 255 bytes per line
ORA-06512: at "SYS.DBMS_OUTPUT", line 84
ORA-06512: at "SYS.DBMS_OUTPUT", line 58

Ответ Тома Кайта

Отлично, - и как решить эту проблему? (Кстати, вы действительно используете ссылающиеся друг на друга таблицы, - в них и вставлять-то данные будет непросто!)

Добавить, например, следующий код? Комментарий читателя от 12 октября 2002 года

package body util
  as   type stack is table of varchar2(32000) index by binary_integer;
  g_stack       stack;
  g_empty_stack stack;
  type tabs is table of varchar2(30) index by binary_integer;
  g_tab_to_truncate   tabs;
  g_empty_tabs        tabs;

  procedure do_ddl(p_stmt in long, p_level in number)
  is
  begin
      dbms_output.put_line(rpad(chr(9), p_level-1, chr(9)) || p_stmt);
      execute immediate p_stmt;
  end do_ddl;

  procedure recursive_truncate(p_tname in varchar2, p_level in number default 1)
  as
      e_already_truncated  exception;
  begin
      if (p_level = 1)
      then
          g_stack           := g_empty_stack;
          g_tab_to_truncate := g_empty_tabs;
      end if;       

      dbms_output.put_line(rpad(chr(9), p_level-1, chr(9)) || 'Processing Table ' || p_tname);
      g_tab_to_truncate(g_tab_to_truncate.count+1) := p_tname;
     
      for x in (select *
                  from user_constraints
                 where constraint_type = 'R'
                   and table_name <> p_tname
                   and r_constraint_name in (select constraint_name
                                                from user_constraints
                                               where table_name = p_tname
                                                 and constraint_type in ('P', 'U')
                                            )
               )
      loop
         begin
            do_ddl('alter table ' || x.table_name || ' disable constraint ' || x.constraint_name, p_level);
            g_stack(g_stack.count+1) := 'alter table ' || x.table_name || ' enable constraint ' || x.constraint_name;

            for i in reverse 1 .. g_tab_to_truncate.count
            loop
                if g_tab_to_truncate(i) = x.table_name
                then  
                   raise e_already_truncated;
                end if;
            end loop;

            recursive_truncate(x.table_name, p_level+1);
            do_ddl('truncate table ' || x.table_name, p_level);
         exception
         when e_already_truncated
         then
              dbms_output.put_line('Table '|| x.table_name ||' already truncated, skipping...');
         end;
      end loop;
      if (p_level = 1)
      then
          do_ddl('truncate table ' || p_tname, p_level);
          for i in 1 .. g_stack.count
          loop
              do_ddl(g_stack(i), p_level);
          end loop;
      end if;
  end recursive_truncate;

end util;
/ 

exec util2.recursive_truncate('X1'); 

Processing Table X1
alter table X2 disable constraint X2_X1_FK
    Processing Table X2
    alter table X1 disable constraint X1_X2_FK
Table X1 already truncated, skipping...
truncate table X2
truncate table X1
alter table X2 enable constraint X2_X1_FK
alter table X1 enable constraint X1_X2_FK

Добавил также усечение p_tname в конце...

Ответ Тома Кайта

Именно так я и собирался сначала делать - обратите внимание на созданный стек...

Спасибо

Комментарий В.К.
Далее некий товарищ 16 октября попытался переписать процедуры без пакета, и у него возникли проблемы... Я позволил себе код этого товарища выкинуть - он все равно не работает. Желающие могут найти его в оригинале обсуждения. Том спросил, почему не используются пакеты, и получил следующий ответ.
"Конец комментария"

Комментарий читателя от 16 октября 2002 года

В нашем приложении в связи с принятыми приемами кодирования можно использовать только хранимые процедуры, но не пакеты. Сейчас код приложения, зависящий от СУБД, должен быть в виде хранимых процедур. Поверьте, обсуждать это с руководством просто бесполезно. Меня очень удивляет поведение Oracle в моем случае. Связано ли это с локальной переменной "stmt", создаваемой динамически? Спасибо за ответ.

Ответ Тома Кайта

Извините, но я не могу справиться с тем, что называю "игнорирование".

Создайте пакет, затем - отдельную процедуру, вызывающую процедуры пакета, - пусть она будет отдельной. Не говорите руководству, что вы использовали "эти паскудные пакеты".

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

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

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

Оригинал обсуждения этого вопроса можно найти здесь.

Copyright © 2002 Oracle Corporation

В следующем выпуске

Думаю сделать авторский выпуск - не все же корифеев цитировать. Выпуск выйдет в конце следующей недели. Следите за новостями на сайте проекта Open Oracle.

С наилучшими пожеланиями,

  В.К.

http://subscribe.ru/
E-mail: ask@subscribe.ru 		Отписаться
Убрать рекламу
В избранное