Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 помогите избаивтся от рекурсии  [new]
Sergey Prudnikov
Member

Откуда:
Сообщений: 65
вот написал процедуру

CREATE PROCEDURE sp_full_tree (@root_node_id int) AS

declare @tree table
(
id int NOT NULL identity(1,1) primary key,
_id int NOT NULL,
_title varchar (150) COLLATE Cyrillic_General_CI_AS NOT NULL,
_level int
)

declare @title varchar (150), @id int, @child bit, @level int

set @level = 0
set @id = @root_node_id

while (select root_node_id from tree where id=@id) is not null begin

set @id = (select id from tree where id=(select root_node_id from tree where id=@id))

if (select root_node_id from tree where id=@id) is not null set @level = @level + 1
end

declare tree cursor local read_only fast_forward for
select id, title, child from tree where root_node_id=@root_node_id
order by child desc, order_index, title

open tree


fetch from tree into @id, @title, @child
while @@fetch_status = 0 begin
insert into @tree (_id, _title, _level) values (@id, @title, @level)
/*print @title + ' ' + convert(varchar, @level)*/

if @child = 1 exec sp_full_tree @id

fetch tree into @id, @title, @child
end


close tree
deallocate tree

select * from @tree
GO

вот жирным выделил, то от чего хочу избавиться, смысл такой чтобы данные запиались в одну целостную таблицу, а не в много маленьких, потому что от них мне толку никакого,
13 июл 04, 11:41    [802234]     Ответить | Цитировать Сообщить модератору
 Re: помогите избаивтся от рекурсии  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 37101
https://www.sql.ru/forum/actualthread.aspx?bid=-2&tid=83263
https://www.sql.ru/forum/actualthread.aspx?bid=1&tid=10197
13 июл 04, 11:49    [802263]     Ответить | Цитировать Сообщить модератору
 Re: помогите избаивтся от рекурсии  [new]
Константин Заровный
Member

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

/****** Object:  Stored Procedure dbo.strsw11m1    Script Date: 05.10.2001 18:23:24 ******/
CREATE    PROCEDURE strsw11m1 (@nizd integer,
			    @Kol_Izdel integer = 1,
                            @key_se    integer = 0,
                            @dse       integer = 0 output,
                            @se        integer = 0 output,
                            @top_lev   integer = 0 output,
                            @low_lev   integer = 0 output)
AS
declare @Lvl               integer,
	@Lvl00             integer,
        @LIndent           integer,
        @LIndentOld        integer,
        @cod_predm         varchar(80), 
        @cod_prin          varchar(4),
        @err_mes           varchar(150),
        @cex               integer,
	@cexC              integer,
        @p_var_tech        varchar(1),
        @lev               integer,
        @check_predm       varchar(80),
        @check_owner       varchar(80),
        @key_check_predm   integer,
        @key_check_owner   integer,
        @Table             Varchar(100),
        @StrE              varchar(255),
        @msg_required      integer,
	@Strlev		   varchar (50),
	@Strlvl		   varchar (50)

begin

 set nocount on

 create table #TMP_TST (
	Key_TST    integer       identity (1,1),
	Lvl        integer, 
	Nmbr       varchar(90),
	Key_Predm  integer, 
	Norm_Rasx  numeric(10,3),
	cod_predm  varchar(80),
	cod_prin   varchar(4)    null,
	key_owner  integer       null,
        owner      integer       null,
        cex        integer       null,
        var_m_was  varchar(1)    default ' '


	)
  
	create index #TMP_Idx on #TMP_TST (Key_Predm) 

 create table #TMP_ErorCheck (Key_Predm integer)

      /* Уровень */    /* Посл. сген. знач. счетчика */
 select  @Lvl=0,          @LIndentOld=0
 delete from #TMP_TST

 if not exists ( select key_predm  from t_predm where Key_predm = @nizd )

 begin

  select @err_mes = "Не найдено изделие " + @cod_predm + ". Проверьте правильность ввода данных."
  raiserror (@err_mes, 1, 1)
  return -1
 end


 select @cod_prin = cod_prin, @cex = key_sub, @p_var_tech = p_var_tech, @cod_predm = cod_predm from t_predm where Key_predm = @nizd
 select @cexC = key_sub from t_izdel where key_izdel = @nizd
 /* Первая запись - о предмете, для которого расписывается состав */
 
 if @key_se = 0

  insert into #TMP_TST (Lvl,  Nmbr, Key_Predm, Norm_Rasx, cod_predm,  cod_prin,  cex )
                values (@Lvl, '0001',  @nizd,  @Kol_Izdel,         @cod_predm, @cod_prin, @cex )
 else
  insert into #TMP_TST (Lvl,  Nmbr, Key_Predm, Norm_Rasx, cod_predm,  cod_prin,  cex )

                values (@Lvl, '0001',  @key_se,  @Kol_Izdel,         @cod_predm, @cod_prin, @cex )

 select @LIndent=@@IDENTITY 

 if ( @cex = 1 ) update #tmp_tst set cex = ( select key_sub from t_izdel where key_izdel = @nizd )

 if ( ( @p_var_tech = '1' ) and exists ( select cod_cexi from t_uk where key_predm = @nizd
                                            and cod_cexo = @cexC ) )
  update #tmp_tst set cex = ( select cod_cexi from t_uk where key_predm = @nizd
                                 and cod_cexo = @cexC ),
	        var_m_was = ( select Variant_M from t_uk where key_predm = @nizd
                                 and cod_cexo = @cexC )
 /* Пока количество вновь вставленных записей не равно нулю... (определяется по тому, изменилось ли значение автоинкрем. поля */
 /* с момента последнего прохода цикла)                                                                                       */



 while @LIndent <> @LIndentOld
 begin
  select @LIndentOld=@LIndent

  select @Lvl=@Lvl+1
   /* Не более 20 уровней */

   if @Lvl > 20 
   begin
	raiserror ('Превышено максимально возможное количество уровней. Обратитесь к программистам.', 1, 1)
	return -3

   end


   /* Вставить во временную таблицу записи о составе всех предметов предыдущего уровня. В качестве номера Nmbr выбирается */
   /* номер Nmbr соотв. записи с предыдущего уровня                                                                       */

  

   insert into #TMP_TST (Lvl , Nmbr , Key_Predm, Norm_Rasx, cod_predm,cod_prin, key_owner, owner, cex ) 
    select @Lvl, T1.Nmbr, T2.Key_Predm_What, t2.Norm_Rasx * t1.Norm_Rasx, t3.cod_Predm ,t3.cod_prin, t1.Key_Predm,
           t1.key_tst, t3.key_sub
     from 	#TMP_TST t1 , 

		T_Sostav t2, 


		t_predm t3 

     where 
		t1.Key_Predm      = t2.key_predm_where
	and 	t1.lvl            = @Lvl-1 

	and 	T2.Key_Predm_What = t3.key_predm
     order by t3.cod_prin,t3.cod_predm
     
   select @LIndent=@@IDENTITY

   /* Проверка на рекурсию состава */

   select @lev = @lvl - 1

   while @lev >= 0
   begin

    select @Strlev=Str(@lev), @Strlvl=STR(@lvl)
      
    exec ("Insert into #TMP_ErorCheck (Key_Predm) select t1.key_predm from #tmp_tst t1, #tmp_tst t2(index=2)  where  t1.lvl= " + @Strlvl+
	  "  and   t2.lvl                              =" + @Strlev+
	  "  and   t1.key_predm                        = t2.key_predm "+
          "  and   substring(t1.nmbr,1,("+@strlev+" + 1) * 4) = substring (t2.nmbr,1,("+@strlev+" + 1) * 4)

	")
    if exists (select * from #TMP_ErorCheck)
    begin

     select @key_check_predm = t1.key_predm, @key_check_owner = t5.key_predm, @check_predm = t3.cod_predm, @check_owner = t4.cod_predm
     from   #tmp_tst t1, #tmp_tst t2, t_predm t3, t_predm t4, #tmp_tst t5
     where  t1.lvl                              = @lvl
      and   t2.lvl                              = @lev
      and   t1.key_predm                        = t2.key_predm
      and   substring(t1.nmbr,1,(@lev + 1) * 4) = substring (t2.nmbr,1,(@lev + 1) * 4)
      and   t3.key_predm                        = t1.key_predm
      and   t5.key_tst                          = t1.owner
      and   t4.key_predm                        = t5.key_predm

     if (@dse = 0) select @msg_required = 1 else select @msg_required = 0

     select @dse = @key_check_predm, @se = @key_check_owner, @top_lev = @lev, @low_lev = @lvl

     if ( @msg_required = 1 )
     begin
      select @err_mes = 'Рекурсия состава по ДСЕ ' + @check_predm + ' (СЕ ' + @check_owner + ') на уровнях '
                        + ltrim(str(@lev)) + ',' + ltrim(str(@lvl)) + '    '
      raiserror(@err_mes,1,1)
     end

     return -2

    end

    select @lev = @lev - 1
   end





   /* Вычисление нормы расхода */

   update  #TMP_TST set  
	Norm_Rasx=Norm_Rasx / (select Norm_Rasx from #TMP_TST  t0 where t0.Key_tst=#TMP_TST.owner) 
    where Lvl = @Lvl and not ( substring(cod_prin,1,1) <> '7'
                            or (select substring(t0.cod_prin,1,1) from #TMP_TST t0 where t0.Key_tst = #TMP_TST.owner) = '7' )

 update #TMP_TST set cex = ( select t1.cod_cexi from t_uk t1, t_predm t2, #tmp_tst t3
                             where
                                   t3.key_tst      = #tmp_tst.owner
                             and   t2.key_predm    = #tmp_tst.key_predm

                             and   t2.p_var_tech   = '1'


                             and   t1.key_predm    = #tmp_tst.key_predm
                             and   t1.cod_cexo     = @cexC ),

               var_m_was = ( select t1.variant_m from t_uk t1, t_predm t2, #tmp_tst t3
                             where
                                   t3.key_tst      = #tmp_tst.owner
                             and   t2.key_predm    = #tmp_tst.key_predm
                             and   t2.p_var_tech   = '1'
                             and   t1.key_predm    = #tmp_tst.key_predm
                             and   t1.cod_cexo     = @cexC )

 where exists ( select cod_cexi from t_uk t1, t_predm t2, #tmp_tst t3
                where

                                   t3.key_tst      = #tmp_tst.owner
                             and   t2.key_predm    = #tmp_tst.key_predm
                             and   t2.p_var_tech   = '1'

                             and   t1.key_predm    = #tmp_tst.key_predm
                             and   t1.cod_cexo     = @cexC )

      and lvl = @lvl

 update #tmp_tst set cex = ( select t1.cex from  #tmp_tst t1 where #tmp_tst.owner = t1.key_tst )

        where cex = 1 and lvl = @lvl and lvl > 0



   /* См. пояснения ниже */

   update #TMP_TST set Nmbr=Nmbr + '   0' where Lvl < @Lvl

   /* См. пояснения ниже. Key_TST-@LIndentOld - номер вставляемой записи на текущем уровне (т.е. реальный номер записи минус номер */
   /* последней записи предыдущего уровня)                                                                                         */

   update #TMP_TST set Nmbr=Nmbr+SUBSTRING('0000'+convert(varchar(4),Key_TST-@LIndentOld),(datalength('0000'+convert(varchar(4),Key_TST-@LIndentOld)))-3,4) where Lvl=@Lvl

   /* Не более 9999 составных частей */
   if ( @LIndent - @LIndentOld ) > 9990
   begin
	raiserror ('Системная ошибка. Обратитесь к программистам.', 1, 1)

	return -4
   end  

 end

 /*********************************************************************************************************************************/

 /* Использование Nmbr необходимо для правильного вывода состава, т.е. чтобы после каждого изделия выводились его составные части */
 /* Типовое решение - рекурсия - с MS SQL работает слишком медленно. Поэтому принято предположение: каждое изделие включает в     */
 /* себя не более 9999 других. Для правильного вывода используется строковое поле Nmbr во временной таблице.                      */
 /* Принцип такой: формировать этот номер при добавлении записей о составе во временную таблицу таким образом, чтобы,             */

 /* отсортировав полученную таблицу по этому полю, получить необходимый формат вывода.                                            */

 /* Формирование Nmbr: при добавлении записей на каждом уровне к Nmbr ВСЕХ предыдущих уровней добавляется '0000', а Nmbr          */
 /* текущего уровня формируется так: Nmbr предыдущего уровня + (номер вставляемой записи на текущеи уровне + дополнение его       */

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

 /* (автоинкрементное поле) последнее сгенерированное для таблицы значение на предыдущем шаге (хранится в @LIdentOld)             */

 /* Пример:                                                                                                                       */
 /* Нулевой уровень (т.е. само исходное изделие): Nmbr = 1                                                                        */
 /* Первый уровень: три изделия. Nmbr 0-го уровня: 1 0000                                                                         */
 /*                              Nmbr 1-го уровня: 1 1000                                                                         */



 /*                                                1 2000                                                                         */

 /*                                                1 3000                                                                         */
 /* Изделие с Nmbr 1 1000 с первого уровне состоит из двух компонентов, с Nmbr 1 3000 - из трех. Тогда:                           */
 /* Второй уровень: пять изделий. Nmbr 1-го уровня: 1 0000 0000                                                                   */
 /*                               Nmbr 2-го уровня: 1 1000 0000                                                                   */
 /*                                                 1 2000 0000                                                                   */
 /*                                                 1 3000 0000                                                                   */
 /*                               Nmbr 3-го уровня: 1 1000 1000                                                                   */
 /*                                                 1 1000 2000                                                                   */
 /*                                                 1 3000 1000                                                                   */
 /*                                                 1 3000 2000                                                                   */

 /*                                                 1 3000 3000                                                                   */

 /* В результате сортировки по этому полю будет получена следующая последовательность:                                            */
 /* 1 0000 0000                                                                                                                   */

 /* - 1 1000 0000                                                                                                                 */
 /*   - 1 1000 1000                                                                                                               */
 /*   - 1 1000 2000                                                                                                               */
 /* - 1 2000 0000                                                                                                                 */

 /* - 1 3000 0000                                                                                                                 */
 /*   - 1 3000 1000                                                                                                               */
 /*   - 1 3000 2000                                                                                                               */
 /*   - 1 3000 3000                                                                                                               */
 /* Что и требовалось доказать                                                                                                    */

 /*********************************************************************************************************************************/

 set nocount off

 /* Формирование окончательного вывода */



 /* Формирование окончательного вывода */

  SELECT 
  @nizd  key_Izdel ,                                  /* n_IZDEL,        */
  convert ( numeric ( 2 ), TMP_TST.Lvl ) Lvl ,         /* N_UROVEN,       */
  TMP_TST_1.Key_Predm,                                /* n_predm_WHERe,  */
  TMP_TST_1.cod_prin,                                 /* cod_PRI_WHEre,  */
  TMP_TST.Key_Predm,                                  /* n_predm_was,    */
  TMP_TST.cod_prin,                                   /* COD_PRI_was,    */
  TMP_TST.var_m_was,                                  /* Var_m_was,      */
  isnull(T_sostav.Cod_Ed_Izm,'') cod_ed_Izm,
  convert ( numeric ( 10, 3 ), T_Sostav.Norm_Rasx ) Norm_Rasx, /* NORM_RASX,      */
  TMP_TST.Norm_Rasx,                                  /* KOLWO,          */
  Subjects.Key_Sub,                                   /* KeyCEXPOLUCH    */
  T_Predm.Cod_Postav                                  /* VID_POST,       */

 FROM 
  S_Subjects     S_Subjects, 
  T_Predm        T_Predm, 
  T_Sostav       T_Sostav, 
  #TMP_TST       TMP_TST, 
  #TMP_TST       TMP_TST_1,
  s_subjects     subjects

 WHERE
       TMP_TST.owner          = TMP_TST_1.Key_TST
  AND  S_Subjects.Key_Sub     = T_Predm.Key_Sub
  AND  T_Predm.Key_Predm      = TMP_TST.Key_Predm
  AND  TMP_TST.Key_Predm      = T_Sostav.Key_Predm_What
  AND  TMP_TST_1.Key_Predm    = T_Sostav.Key_Predm_Where
  AND  subjects.key_sub       = TMP_TST.cex  

 ORDER BY TMP_TST.Nmbr, tmp_tst.lvl

 /* Дропаем нашу временную таблицу */



 drop table #TMP_TST 

end



К сожалению это уже готовая и отлаженная процедура заменяющая старую рекурсивную - а для ваших целей ее можно упростить раза в 2 и занимать она будет места в 2 раза меньше.
13 июл 04, 12:18    [802438]     Ответить | Цитировать Сообщить модератору
 Re: помогите избаивтся от рекурсии  [new]
Sergey Prudnikov
Member

Откуда:
Сообщений: 65
ОГРОМНОЕ СПАСИБО Гавриленко Сергею Алексеевичу и SergSuper.
вот реально работающий код
CREATE PROCEDURE sp_full_tree (@root_node_id int) AS

set nocount on

declare @tree table
(
	id int NULL,
	_id int NOT NULL,
	_root_node_id int NULL,
	_level int
)

declare @level int set @level = 0

insert @tree select 0, @root_node_id, null, @level

while exists(select * from tree t, @tree _t where _level = @level and t.id = _t._id) begin

	insert @tree select null, t.id, t.root_node_id, @level + 1
		from tree t, @tree _t
		where _level = @level and t.root_node_id=_t._id

	set @level = @level + 1

	update  t
		set id = id + (select count(*) from @tree t1, @tree t2 where t2.id is null and t2._root_node_id = t1._id and t1.id < t.id)
	from @tree t

	update t1
		set  id = t2.id + (select count(*) from @tree t3 where t3._root_node_id = t1._root_node_id and t3._id <= t1._id)
	from @tree t1, @tree t2
	where t1.id is null and t1._root_node_id = t2._id
end

select _t._id, space(_level * 2) + title as title, _t._level from @tree _t, tree t
where _t._id = t.id
order by _t.id

GO
13 июл 04, 12:59    [802644]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить