ReSQL.ru
     
powered by simpleCommunicator - 2.0.61     © 2026 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Рекурсия и Хранимая процедура
32 сообщений из 32, показаны все 2 страниц
  все  
Рекурсия и Хранимая процедура
    #32070304
Chainiko
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Chainiko
Гость
Существует таблица FILES вида
Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
  NodeId     NodeName NodeType ParentId PreviousId     Size
        1            C:    Drive              
        2           DOS   Folder         1 
        3    config.sys     File         2                    62 
        4     himem.sys     File         2            3        128 
        5           DOC   Folder         1 
        6        SUBDOC   Folder         5 
        7        s1.doc     File         6                 45578 
        8        s2.doc     File         6            7        654 
        9          1 .txt     File         5            6       1244 
       10          2 .txt     File         5            9        458 
       11     MSDOS.SYS     File         1            5         54 
       12   COMMAND.COM     File         1           11      15486

описывающая дерево каталогов на диске С:

C:\DOS\config.sys
C:\DOS\himem.sys
C:\DOC\SUBDOC\s1.doc
C:\DOC\SUBDOC\s2.doc
C:\DOC\1.txt
C:\DOC\2.txt
C:\MSDOS.SYS
C:\COMMAND.COM

ParentId и PreviousId левыми джойнами связаны с NodeId.

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

Проблема в подсчете объема файлов в заданном каталоге - например DOC. Обход любого дерева проще всего сделать используя рекурсию, но хочется использовать хранимую процедуру.
Помогите сделать хранимую процедуру, которая будет возвращать объем файлов (всех с подкаталогами).

У меня получилось что-то типа:

Код: 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.
CREATE PROCEDURE GetSize @n_id int, @allsize int output as

DECLARE cicle_cursor CURSOR FOR
  SELECT F1.node_id As nchild_id
  FROM FILES AS F LEFT JOIN FILES AS F1 ON F.node_id = F1.parent_id
  WHERE F.node_id=@n_id AND F1.node_type= "Folder" 

  OPEN cicle_cursor

  FETCH NEXT FROM cicle_cursor
  INTO @nchild_id

  WHILE @@FETCH_STATUS =  0   /* Цикл по всем нодам типа Folder в текущей ноде. */ 
   BEGIN
	SELECT @allsize = @allsize + (SELECT Sum(F1.size)
	FROM FILES AS F LEFT JOIN FILES AS F1 ON F.node_id = F1.parent_id
	WHERE F.node_id=@nchild_id)
	 /* Вот здесь неплохо бы рекурсивно вызвать самого себя ????? */ 
	SEELCT @allsize = @allsize + exec GetSize @nchild_id  /* только будет ли так работать */ 

	FETCH NEXT FROM cicle_cursor
	INTO @nchild_id
   END
GO


Бред, конечно, но какие еще есть варианты? Pls!
...
Рейтинг: 0 / 0
20.11.2002, 16:46:19
| Ответить | Цитировать | Написать  
Рекурсия и Хранимая процедура
    #32070425
Vetal
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Vetal
Участник
Так у тебя точно ничего не получится, так как, насколько я понимаю, курсор вещь глобальная, и если ты его открываешь, а потом входишь в рекурсию, то ты внутри рекурсии открыть курсор не сможешь, так как он уже был однажды открыт (проверено). Более того, когда ты сделаешь FETCH внутри рекурсии, то получишь из курсора то значение, которое ты получил бы из своей функции GetSize самого высокого уровня. Вот если бы курсоры были типа локальных переменных - тогда другой вопрос.

Можно попробовать сделать это через рекурсивные триггеры, правда, они имеют ограничение на глубину рекурсии 32, но этого должно хватить.

К примеру, нужно посчитать обьем каталога DOC:
Для этого нужно
Создать глобальную переменную AllSize и UPDATE_FLAG. В опциях базы данных включить рекурсивные триггеры. Создать процедуру GetSize:

CREATE PROCEDURE GetSize
AS
// UPDATE_FLAG - некоторая созд. тобой глоб. перем
// показывающая, что приложение в режиме подсчета
SET @@UPDATE_FLAG = 1
SET @@allsize = 0
DELETE FILES WHERE NodeName = 'DOC'
SET @@UPDATE_FLAG = 0
GO


Создать триггер на обновление.

CREATE TRIGGER tU_Files ON Files
FOR UPDATE
AS
BEGIN
IF(@@UPDATE_FLAG=1)
BEGIN
IF (SELECT Count(NodeID) FROM Deleted) <> 0
BEGIN
SELECT @@allsize = @@allsize + (SELECT Sum(F1.size) FROM FILES WHERE Files.ParentID = ANY (SELECT NodeID FROM Deleted)
DELETE FROM Files
WHERE Files.ParentID = ANY (SELECT NodeID FROM Deleted) AND Files.Node_Type = "Folder"
END;
INSERT INTO Files FROM Deleted
END;
END;

Суть в чем:
Ты удаляешь каталог, который хочешь подсчитать, в триггере на удаление ты считываешь ID удаленного каталога, К переменной allsize добавляешь обьем всех дочерних файлов, и удаляешь все дочерние каталоги. Для всех удаленных каталогов ты подсчитываешь обьем всех дочерних файлов и удаляешь все дочерние для удаленных каталоги. И т.д. до каталогов вложенности 32 максимум (тебе должно хватить с головой). При завершении триггеров все удаленные каталоги восстановлятся. Данные не пропадут, так как все выполняется в контексте одной транзакции
...
Рейтинг: 0 / 0
20.11.2002, 20:08:57
| Ответить | Цитировать | Написать  
Рекурсия и Хранимая процедура
    #32070430
Фотография VVG_
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
VVG_
Участник
Если SQL2000, то можно создать пару рекурсивных функций, с помощью которых это решается. Если интересует, то завтра подробно расскажу.
...
Рейтинг: 0 / 0
20.11.2002, 20:20:27
| Ответить | Цитировать | Написать  
Рекурсия и Хранимая процедура
    #32070441
Chainiko
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Chainiko
Гость
Re VVG_. Спасибо, подожду до завтра.
...
Рейтинг: 0 / 0
20.11.2002, 21:02:50
| Ответить | Цитировать | Написать  
Рекурсия и Хранимая процедура
    #32070445
Chainiko
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Chainiko
Гость
Re Vetal: Идея неплоха, я ее потестирую, но:
- во-первых, глубина рекурсии 32 - очень мала, это в данном примере, для понимания сути приведены файлы в каталогах, но в реальной базе глубина дерева достигает нескольких сотен, единиц тысяч...
- во-вторых, не очень понятно с удалением, если транзакция не завершается, то физического удаления не происходит, и, можно считать, что на времени выполнения это не сказывается?

Если на рекурсии накладываются такие ограчничения, можно ли в такой таблице обойтись без рекурсии?
...
Рейтинг: 0 / 0
20.11.2002, 21:13:28
| Ответить | Цитировать | Написать  
Рекурсия и Хранимая процедура
    #32070456
Фотография Gobzo Kobler
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Gobzo Kobler
Участник
PreviousId - бред сивой кобылы, ошибка дизайна. Ну да ладно, это маловажная вещь. Но зачем вам понадобилось строить дерево неопределенной глубины на реляционной базе и делать обработку на сервере - вот этого уже я никак понять не могу.
Сама концепция реляционной базы предотвращяет ее использование для древовидных данных. Обрабатывайте деревья на клиенте и будет вам радость. Сервер закопается стопудово, особенно если вы увлечетесь курсорами.
...
Рейтинг: 0 / 0
20.11.2002, 21:50:19
| Ответить | Цитировать | Написать  
Рекурсия и Хранимая процедура
    #32070470
Chainiko
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Chainiko
Гость
Re: Gobzo Kobler
Дело в том, что дерево с кучей узлов и неопределенной глубины - это всего одна таблица - основа базы. К каждой ноде привязано куча таблиц и данных в них именно реляционных. Там уже тысячи запросов, сотни мегабайт данных, поэтому переделывать эту таблицу не хочу. С такой структурой таблицы (ParentID, PreviousID) методами FirstChild и Next в рекурсии на контроле строится дерево и поддержаны куча методов по движению нод, и т.д. и т.п, а зная NodeId, основная работа ведется с недревовидной частью базы.

Но это уже тема отдельного разговора. Вернемся к баранам, может можно обойтись без рекурсии?
...
Рейтинг: 0 / 0
20.11.2002, 23:06:08
| Ответить | Цитировать | Написать  
Рекурсия и Хранимая процедура
    #32070484
Vetal
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Vetal
Участник
Re Chainiko
Вообще-то ограничение на глубину рекурсии насколько я знаю, накладывается только на триггеры. Если все написать как-то через хранимые процедуры, то там таких ограничений вроде как нет.

Что касается того, что данные не удаляются, то ведь они же и не должны удаляться, информация должна только подсчитываться. А на производительность это конечно же влияет, так как время на выполнение кода в триггере все-равно тратится.

Если же ты все это хочешь сделать на стороне сервера, то напиши расширенную хранимую процедуру (extended sp), в ней ты можешь воспользоваться функиями языка C, и эта процедура будет выполняться на сервере.
...
Рейтинг: 0 / 0
21.11.2002, 00:46:28
| Ответить | Цитировать | Написать  
Рекурсия и Хранимая процедура
    #32070534
Фотография Chicago
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Chicago
Участник
Если циклов нет, то будет работать

Код: 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.
create procedure GetSize 
        @node_id int, 
        @size int output 
as
begin tran

declare @err int, @rc int

 -- таблица для очереди узлов
 
create table #queue(node_id int) on [primary]

set @err=@@error
if @err<> 0  goto l_err

alter table #queue add constraint
	PK_queue primary key clustered 
	(
	node_id
	) ON [PRIMARY]

set @err=@@error
if @err<> 0  goto l_err

insert into #queue(node_id) values(@node_id)
set @err=@@error
if @err<> 0  goto l_err

declare @current_id int, @node_size int

set @size= 0 

while  1 = 1 
begin
        select top  1  @current_id=node_id from #queue        
        select @err=@@error, @rc=@@rowcount
        if @err<> 0  or @rc= 0  break

        set @node_size= 0 

        select @node_size=isnull([size],  0 ) from files where nodeid=@current_id
        select @err=@@error
        if @err<> 0  break

        set @size = @size + @node_size
        
        delete from #queue where node_id=@current_id

        insert into #queue(node_id) select nodeid from files where parentid=@current_id
        select @err=@@error
        if @err<> 0  break
end

if @err<> 0  goto l_err
drop table #queue

commit tran
return @@error

l_err:
        if @@trancount> 0  rollback tran
        set @size=null
        return @err
...
Рейтинг: 0 / 0
21.11.2002, 09:10:11
| Ответить | Цитировать | Написать  
Рекурсия и Хранимая процедура
    #32070623
Фотография VVG_
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
VVG_
Участник
Вот вариант в функциями:
Код: 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.
create table Files 
(NodeID int not null, NodeName varchar( 1024 ) not null, 
NodeType varchar( 20 ) not null, ParentId int null, 
PreviousID int null, Size int null)
go
insert into files
select  1 ,'c:','Drive',null,null,null union 
select  2 ,'DOS','Folder', 1 ,null,null union
select  3 ,'config.sys','File', 2 ,null, 62  union
select  4 ,'himem.sys','File', 2 , 3 , 128  union
select  5 ,'DOC','Folder', 1 ,null,null union
select  6 ,'SUBDOC','Folder', 5 ,null,null union
select  7 ,'s1.doc','File', 6 ,null, 45578  union
select  8 ,'s2.doc','File', 6 , 7 , 654  union
select  9 ,'1.txt','File', 5 , 6 , 1244  union
select  10 ,'2.txt','File', 5 , 9 , 458  union
select  11 ,'MSDOS.SYS','File', 1 , 5 , 54  union
select  12 ,'COMMAND.COM','File', 1 , 11 , 15486 
go
create function dbo.udf_Nodes(@ParentID int)
returns @Tbl table (NodeID int,NodeSize int)
as
begin
 insert into @Tbl
 select NodeID,dbo.udf_NodeSize(NodeID) from files where ParentID=@ParentID
 return
end
go
create function dbo.udf_NodeSize (@NodeID int)
returns int
as
begin
 declare @Size int
 select @Size=case NodeType
  when 'Drive' then (select sum(NodeSize) from dbo.udf_Nodes(@NodeID))
  when 'Folder' then (select sum(NodeSize) from dbo.udf_Nodes(@NodeID))
  when 'File' then Size
 end from files where NodeID=@NodeID
 return @Size
end
go
select *,dbo.udf_NodeSize(NodeID) as NodeSize from files
go
drop table files
drop function udf_Nodes
drop function udf_NodeSize
...
Рейтинг: 0 / 0
21.11.2002, 11:17:09
| Ответить | Цитировать | Написать  
Рекурсия и Хранимая процедура
    #32070643
Фотография Chicago
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Chicago
Участник
Мой предыдущий вариант лобовой и, следовательно, не слишком оптимальный. А вот более прогрессивное решение.

Код: 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.
create procedure GetSize 
        @node_id int, 
        @size int output 
as
begin tran

declare @err int, @rc int

create table #subtree(node_id int not null, [size] int, layer int) on [primary]

set @err=@@error
if @err<> 0  goto l_err

alter table #subtree add constraint
	PK_queue primary key clustered 
	(
	node_id
	) ON [PRIMARY]

set @err=@@error
if @err<> 0  goto l_err

declare @layer int
set @layer =  0 

insert into #subtree(node_id, [size], layer) 
        select nodeid, [size], @layer from files where nodeid=@node_id

set @err=@@error
if @err<> 0  goto l_err

while  1 = 1 
begin
        set @layer=@layer+ 1 

        insert into #subtree(node_id, [size], layer) 
                select nodeid, [size], @layer from files 
                        where parentid in (select node_id from #subtree where layer=@layer- 1 )
                        
        select @err=@@error, @rc=@@rowcount
        if @err<> 0  or @rc= 0  break
end

select @size=sum([size]) from #subtree
set @size=isnull(@size,  0 )

if @err<> 0  goto l_err
drop table #subtree

commit tran
return @@error

l_err:
        if @@trancount> 0  rollback tran
        set @size=null
        return @err


ЗЫ. Итерация свойственна человеку, рекурсия - божественна
...
Рейтинг: 0 / 0
21.11.2002, 11:44:58
| Ответить | Цитировать | Написать  
Рекурсия и Хранимая процедура
    #32070658
nandji
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
nandji
Участник
интересное решение Древовидные структуры в SQL
...
Рейтинг: 0 / 0
21.11.2002, 12:06:13
| Ответить | Цитировать | Написать  
Рекурсия и Хранимая процедура
    #32070808
Vetal
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Vetal
Участник
Мой модифицированный вариант. Вместо триггеров теперь рекурсивно вызываются процедуры, и, соответственно, нет ограничения 32.

CREATE TABLE #TmpFilesTable(NodeID integer)
go

// Добавляем во временную таблицу ID папки Docs
// размер которой нужно подсчитать
INSERT INTO #TmpFilesTable VALUES(5);
GO

SET @@AllSize = 0
GO

CREATE PROCEDURE GETSIZE
AS
BEGIN
IF (SELECT Count(NodeID) FROM #TmpFilesTable) <> 0
BEGIN
SELECT @@allsize = @@allsize + (SELECT Sum(Files.size) FROM FILES WHERE Files.ParentID = ANY (SELECT NodeID FROM #TmpFilesTable)
DELETE FROM #TmpFilesTable
INSERT INTO #TmpFilesTable VALUES (SELECT NodeID FROM FILES WHERE Files.ParentID = ANY (SELECT NodeID FROM #TmpFilesTable))
EXEC GETSIZE;
END;
END;
go

DELETE TABLE #TmpFilesTable

Можешь переменную AllSize использовать как возвращаемый параметр функции, а не как глобальную переменную
...
Рейтинг: 0 / 0
21.11.2002, 15:05:30
| Ответить | Цитировать | Написать  
Рекурсия и Хранимая процедура
    #32070831
Фотография Chicago
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Chicago
Участник
2Vetal:

Вместо триггеров теперь рекурсивно вызываются процедуры, и, соответственно, нет ограничения 32.

Смотрим BOL, раздел SQL Server Architecture / Implementation Details / Maximum Capacity Specifications. Видим
Nested stored procedure levels = 32

Nested trigger levels = 32

No comments
...
Рейтинг: 0 / 0
21.11.2002, 15:21:00
| Ответить | Цитировать | Написать  
Рекурсия и Хранимая процедура
    #32070852
Фотография Chicago
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Chicago
Участник
2 VVG:

Ваша функция не работает. Вставляем в соответствующем месте вашего скрипта

Код: 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.
insert into files
select  1 ,'c:','Drive',null,null,null union 
select  2 ,'DOS','Folder', 1 ,null,null union
select  3 ,'config.sys','File', 2 ,null, 62  union
select  3 ,'config.sys','File', 2 ,null, 62  union
select  4 ,'config.sys','File', 3 ,null, 62  union
select  5 ,'config.sys','File', 4 ,null, 62  union
select  6 ,'config.sys','File', 5 ,null, 62  union
select  7 ,'config.sys','File', 6 ,null, 62  union
select  8 ,'config.sys','File', 7 ,null, 62  union
select  9 ,'config.sys','File', 8 ,null, 62  union
select  10 ,'config.sys','File', 9 ,null, 62  union
select  11 ,'config.sys','File', 10 ,null, 62  union
select  12 ,'config.sys','File', 11 ,null, 62  union
select  13 ,'config.sys','File', 12 ,null, 62  union
select  14 ,'config.sys','File', 13 ,null, 62  union
select  15 ,'config.sys','File', 14 ,null, 62  union
select  16 ,'config.sys','File', 15 ,null, 62  union
select  17 ,'config.sys','File', 16 ,null, 62  union
select  18 ,'config.sys','File', 17 ,null, 62  union
select  19 ,'config.sys','File', 18 ,null, 62  union
select  20 ,'config.sys','File', 19 ,null, 62  union
select  21 ,'config.sys','File', 20 ,null, 62  union
select  22 ,'config.sys','File', 21 ,null, 62  union
select  23 ,'config.sys','File', 22 ,null, 62  union
select  24 ,'config.sys','File', 23 ,null, 62  union
select  25 ,'config.sys','File', 24 ,null, 62  union
select  26 ,'config.sys','File', 25 ,null, 62  union
select  27 ,'config.sys','File', 26 ,null, 62  union
select  28 ,'config.sys','File', 27 ,null, 62  union
select  29 ,'config.sys','File', 28 ,null, 62  union
select  30 ,'config.sys','File', 29 ,null, 62  union
select  31 ,'config.sys','File', 30 ,null, 62  union
select  32 ,'config.sys','File', 31 ,null, 62  union
select  33 ,'config.sys','File', 32 ,null, 62  union
select  34 ,'config.sys','File', 33 ,null, 62  union
select  35 ,'config.sys','File', 34 ,null, 62

и на прогоне получаем

Server: Msg 217, Level 16, State 1, Procedure udf_Nodes, Line 10
Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).

Вот так. На UDF ограничение вложенности распространяется.
...
Рейтинг: 0 / 0
21.11.2002, 15:48:21
| Ответить | Цитировать | Написать  
Рекурсия и Хранимая процедура
    #32070860
Фотография VVG_
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
VVG_
Участник
Млин, отвоевал ты свой вариант, отвоевал.
Но иметь 32 уровня вложенности папок на диске - это перебор.
...
Рейтинг: 0 / 0
21.11.2002, 15:53:15
| Ответить | Цитировать | Написать  
Рекурсия и Хранимая процедура
    #32071101
Chainiko
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Chainiko
Гость
Да, господа, много я проспал. Пока выяснялось, что на ХП тоже есть ограничение на рекурсию я слепил кусок кода, работающего на Visual Basic. Не хочу использовать триггер, может кто-нибудь поможет банально транслировать код в ХП? Я не очень владею синтаксисом ХП, но чем мне нравится этот вариант, так это отсутствием создания временных таблиц. Осталось лишь оформить ХП. Мне не очень понятно, что будет с @nodeID в первом запросе, если он вернет 0 записей? NULL? 0? Или она вообще не проинициализируется? На что ее проверять? И как звучит exit PROCEDURE на диалекте ХП?
Код: 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.
Public allSize As Long
Public cnn As ADODB.Connection

Private Sub Form_Load()
  Set cnn = New ADODB.Connection
  cnn.Provider =  "Microsoft.Jet.OLEDB.4 . 0 "
  cnn.Mode = adModeShareExclusive
  cnn.Open  "E:\Progects\databasefile\db1.mdb" 
  Tree  5    /*DOC : NodeID = 5 */ 
  Debug.Print allSize
End Sub

Private Sub Tree(ParentNodeID As Long)
  Dim nodeID As Long
  Dim RS As ADODB.Recordset
  Dim strSQL As String
    strSQL =  "SELECT * "  & _
       "FROM FILES AS F LEFT JOIN FILES AS F1 ON F.node_id = F1.parent_id "  & _
       "WHERE F.node_id = "  & ParentNodeID &  " AND F1.previous_id Is Null" 
  Set RS = cnn.Execute(strSQL)
  nodeID =  0 
  On Error Resume Next
  nodeID = RS( "F1.node_id" )  /* получаем первого Childа */ 
  If nodeID =  0  Then RS.Close: Exit Sub ' /* если Childа нет выходим */ 
  allSize = allSize + RS( "F1.size" )  /* если Child есть берем его размер */ 
  RS.Close
  
  Do While True
    Tree (nodeID)  /*вызываем сами себя*/ 
    strSQL =  "SELECT * "  & _
         "FROM FILES "  & _
         "WHERE FILES.previous_id = "  & nodeID
    Set RS = cnn.Execute(strSQL, adOpenKeyset)
    nodeID =  0 
    On Error Resume Next
    nodeID = RS( "node_id" )  /*получаем Nextа */ 
    If nodeID =  0  Then RS.Close: Exit Sub  /*сли Nextа нет выходим */ 
    allSize = allSize + RS( "size" )  /*если Next есть берем его размер */ 
    RS.Close
  Loop
End Sub


ЗЫ. На используемого провайдера просьба бочку не катить, это так, для тестов.
...
Рейтинг: 0 / 0
22.11.2002, 00:36:44
| Ответить | Цитировать | Написать  
Рекурсия и Хранимая процедура
    #32071118
Фотография Chicago
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Chicago
Участник
2 Chainiko:

Не-е-е, сам транслируй свой VB в T-SQL. Скажу честно, на твоей задаче мы вчера классно оттянулись, написали то, что считали нужным написать в данном случае и писать по другому нам банально не интересно.

Плохо знаешь синтаксис T-SQL? Ну вот и замечательно! Отличный повод потренироваться!!! В ветке полно интересных примеров. Их можно использовать как источник вдохновения.

return procedure -> return [<return value>]

Я тоже не знаю чему будет равен @nodeid, если запрос не вернет записей. В документации этот вопрос слабо освещен. Поэтому я бы вообще не делал на этот счет никаких предположений. Сегодня так, а в MS SQL Server XP 2010 может быть по другому. Зато точно известно, что если запрос не вернет записей, то сразу после его завершения переменная @@rowcount будет равна 0. В нашем случае это все, что нужно.

P.S. Не следует обижаться, но VB вам тоже нужно подучить.
...
Рейтинг: 0 / 0
22.11.2002, 07:51:37
| Ответить | Цитировать | Написать  
Рекурсия и Хранимая процедура
    #32071125
Flint-San
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Flint-San
Гость
Тут вроде код sp просили, предлагаю свой вариант.
Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
CREATE PROCEDURE GetSize @n_id int, @allsize bigint output 
AS
 SET @allsize= 0 

 SELECT DISTINCT NodeId INTO #AllChilds FROM FILES WHERE ParentId=@n_id

 WHILE(@@ROWCOUNT> 0 )
   INSERT INTO #AllChilds(NodeID) 
   SELECT DISTINCT F.NodeId FROM #AllChilds a, FILES F 
   WHERE F.ParentId=a.NodeId
     AND NOT EXISTS(SELECT TOP  1   1  FROM #AllChilds X WHERE X.NodeId=F.NodeId)
 
 SELECT @allsize=sum(ISNULL(F.Size, 0 )) FROM #AllChilds a,FILES F WHERE F.NodeId=a.NodeId
GO

DECLARE @allsize bigint
EXEC GetSize  5 , @allsize OUT
SELECT @allsize
...
Рейтинг: 0 / 0
22.11.2002, 08:36:18
| Ответить | Цитировать | Написать  
Рекурсия и Хранимая процедура
    #32071575
Chainiko
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Chainiko
Гость
Re Chicago:

P.S. Не следует обижаться, но VB вам тоже нужно подучить.
Если Вас не затруднит, не укажете ли на ошибки, это конечно не VB форум, но если уж А, то и Б нужно говорить. Мне это только на пользу пойдет.
...
Рейтинг: 0 / 0
23.11.2002, 01:23:43
| Ответить | Цитировать | Написать  
Рекурсия и Хранимая процедура
    #32071576
Chainiko
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Chainiko
Гость
Re: Flint-San
Очень понравилась Ваша процедура. Коротко и со вкусом. Только нихрена не понятно как работает! Не могди бы объяснить Чайнику, коротко, в двух-трех словах? Заранее благодарю.
...
Рейтинг: 0 / 0
23.11.2002, 01:27:21
| Ответить | Цитировать | Написать  
Рекурсия и Хранимая процедура
    #32071651
bantik
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
bantik
Участник
Господа !

А в пылу спора слабо поднять топик "Древовидные структуры в SQL" ? И узнать что такой способ хранения с использованием ParentID используют только лохи :-))

См например

Удачи !
...
Рейтинг: 0 / 0
23.11.2002, 20:45:05
| Ответить | Цитировать | Написать  
Рекурсия и Хранимая процедура
    #32071652
bantik
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
bantik
Участник
Вот ссылка

http://www.farpost.com/vit/w3design/server/db/sql/tree.htm
...
Рейтинг: 0 / 0
23.11.2002, 20:45:59
| Ответить | Цитировать | Написать  
Рекурсия и Хранимая процедура
    #32071667
Chainiko
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Chainiko
Гость
Re: bantik

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

-Давайте увеличим время обхода дерева вдвое!

Следующий запрос будет брать уволенного служащего как параметр и удалять поддерево, расположенное под ним/ней. ... но другие операции, которые зависят от плотности номеров, не будут работать в дереве с промежутками. Например, Вы не сможете находить листья, используя предикат (right-left=1), и не сможете найти число узлов в поддереве, используя значения left и right его корня.

-Давайте удаление узла будем сопровождать перелопачиванием всего(!) хвоста дерева с удаленной ноды! (Время - не деньги)

Тип дерева определяется задачами, а не задача типами. Если мне нужно подсчитать суммарный размер всех листьев, то там это делается как 2 пальца намочить, а как операции удаление - добавление - давайте потратим серверное время (девать нам его некуда)!

В обсуждаемом варианте дерева добавление узла осуществляется лобавлением всего одной записи, удаление - удаляй любой узел - дети сами каскадно отвалятся. Если есть нужда цеплять детей к бабушкам развлекись со своим Нортон Коммандером! И нехрен по некомпетентности ссылки кидать и лохов вспоминать!
...
Рейтинг: 0 / 0
23.11.2002, 21:51:22
| Ответить | Цитировать | Написать  
Рекурсия и Хранимая процедура
    #32071727
Фотография MiCe
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
MiCe
Участник
поиск посату работает.....
...
Рейтинг: 0 / 0
25.11.2002, 03:17:23
| Ответить | Цитировать | Написать  
Рекурсия и Хранимая процедура
    #32071750
Фотография Chicago
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Chicago
Участник
Если Вас не затруднит, не укажете ли на ошибки, это конечно не VB форум, но если уж А, то и Б нужно говорить. Мне это только на пользу пойдет.

Ошибки стиля рассматривать не будем, стиль у каждого разный и не все есть ошибка. (Хотя очень хочется :-) Также разбор полетов ограничим процедурой Tree, поскольку все остальное писано специально для тестирования и некоторые шероховатости не имеют большого значения.

Начнем с того что Rs("F1.node_id") писать нельзя. Нужно Rs("node_id"). А если в запросе 2 node_id из разных таблиц, то извольте указать им различные псевдонимы и используйте последние как имена полей в VB. В противном случае всегда будете получать значение того node_id, что в коллекции Rs.Fields является первым. Далее будем полагать, что этой ошибки нет.

Ваша основная ошибка заключается в стратегии обработки ошибок. У вас есть 2 строки вида
on error resume next . При этом вторая, та что внутри while (стиль ужасен, извините, не удержался 8-) по большому счету не нужна. Начиная с первой такой строки и вплоть до выхода из процедуры, управление при ошибках будет передаваться на следующий оператор. В связи с этим возможны следующие сценарии.

Первый селект вернул строку, не имеющую размера (size is null). Тогда строка allSize = allSize + RS("F1.size") вызывает ошибку (нельзя складывать Null и целое число). Но об этой ошибке вы не узнаете. И конечное значение будет не вполне корректно. (Хотя, возможно, null=0, есть правильно)


Сценарий второй. Очередной cnn.Execute внутри цикла while. Завершился неудачей (Timeout expired, connection broken или еще какая-нибудь хрень). Вы об этом не узнаете (действует первый on error resume next). Выполнение продолжится. В строке nodeID = RS("node_id") происходит ошибка, ведь рекордсет не открыт, и nodeID остается равным 0. В последующем If мы закрываем закрытый рекордсет. Ошибка! А нам все пофиг, resume next, вашу мать. Дальше следует exit sub. Все это тихо и молча. В результате какая-то часть узлов не оказывает влияния на результат, а вы об этом ничего не знаете. Для вас процедура завершилась успешно.

Ну и под занавес все таки об ошибке стиля. Какого хрена allSize глобальная переменная? Это же результат вычислений! Он должен возвращаться! Только так и без объяснений. (Извините, но если начну объяснять, то сразу вспомню, как мне пришлось отлаживать программулину, писанную одним товарищем, чтоб ему... там было 3247 глобальных переменных... говорить об этом могу только матом).
...
Рейтинг: 0 / 0
25.11.2002, 09:25:16
| Ответить | Цитировать | Написать  
Рекурсия и Хранимая процедура
    #32071757
Фотография Chicago
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Chicago
Участник
По поводу процедуры Flint-San:

Основная идея на первом шаге он помещает во временную таблицу id корневого узла. Далее в цикле он добавляет вершины, отцы которых уже находятся в таблице. При этом если вершина уже добавлена, то она не добавляется. Цикл продолжается, пока на очередном шаге не будет добавлено ни одной вершины. В таблице оказываются все узлы поддерева, суммарный вес вершин, которого и нужно подсчитать. Что и делается в последнем select.

Кстати его процедура не учитывает вес корневого узла. Если это необходимо замениете
Код: plaintext
SELECT DISTINCT NodeId INTO #AllChilds FROM FILES WHERE ParentId=@n_id
на
Код: plaintext
SELECT DISTINCT NodeId INTO #AllChilds FROM FILES WHERE NodeId=@n_id


Фактически это то же, что и в моем варианте №2. Короче, потому что начисто проигнорированна обработка ошибок. Ну и о временной таблице вам придется самому заботиться. Insert Into ее создаст конечно, а вот удаления или очистки там нет. В результате если вы в рамках одного коннекта вызовите процедуру дважды, для произвольной вершины и затем для ее потомка, то при первом вызове получите корректный результат, а при втором нет. Вернется вес первого поддерева, а не второго. Вот она прелесть глобальных данных! (это я к своему предыдущему постингу)
...
Рейтинг: 0 / 0
25.11.2002, 09:40:10
| Ответить | Цитировать | Написать  
Рекурсия и Хранимая процедура
    #32071830
Фотография Chicago
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Chicago
Участник
Следующий текст из предыдущего постинга:

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

просьба считать несуществующим :-) Sorry, прогнал...
...
Рейтинг: 0 / 0
25.11.2002, 11:33:39
| Ответить | Цитировать | Написать  
Рекурсия и Хранимая процедура
    #32071890
Flint-San
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Flint-San
Гость
Не думал, что это тема еще обсуждается.
Chicago:
Код правильный и ненужно его корректировать так как
из задачи корневой узел это дирректорий, по вставляемым
записям у дирректория Size IS NULL и размер определ-ся только по файлом.
Теперь к вам вопрос, что даст ваше исправление?
Код: plaintext
1.
 SELECT DISTINCT NodeId INTO #AllChilds FROM FILES WHERE NodeId=@n_id
вместо
Код: plaintext
1.
 SELECT DISTINCT NodeId INTO #AllChilds FROM FILES WHERE ParentId=@n_id

Правильно абсолютно ничего как для файла так и для каталога. Смысл вашего первого запроса, бесмысленен.
У этой процедуры есть только один существенный недостаток, а именно ее скорость будет зависить от числа вложенных каталогов.
Что же касается удаления временной таблицы... Процедура автоматически удаляет временную таблицу по выходу из нее.
Chainiko:
просто нашел всех детей(NodeId) по родителям(ParentID)
при добавлении в таблицу проверяю есть ли там уже такой child или нет.
...
Рейтинг: 0 / 0
25.11.2002, 12:38:49
| Ответить | Цитировать | Написать  
Рекурсия и Хранимая процедура
    #32072305
Flint-San
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Flint-San
Гость
К Chicago:
>>Правильно абсолютно ничего как для файла так и для >>каталога. Смысл вашего первого запроса, бесмысленен.
Признаю свою ошибку , так как смыл вашего исправления
позволит расчитать размер одного файла.
Chainiko замени плиз код с
Код: plaintext
 SELECT DISTINCT NodeId INTO #AllChilds FROM FILES WHERE ParentId=@n_id

на
Код: plaintext
SELECT DISTINCT NodeId INTO #AllChilds FROM FILES WHERE NodeId=@n_id

чтобы процедура могла расчитывать абсолютно все, включая и размер одного файла.
...
Рейтинг: 0 / 0
26.11.2002, 07:35:45
| Ответить | Цитировать | Написать  
Рекурсия и Хранимая процедура
    #32072314
Фотография Chicago
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Chicago
Участник
Я думаю пришли к консенсусу.

2Flint-San:

Спасибо. Я от вас узнал для себя нечто новое:

Что же касается удаления временной таблицы... Процедура автоматически удаляет временную таблицу по выходу из нее.

If you create a local temporary table inside a stored procedure, the temporary table exists only for the purposes of the stored procedure; it disappears when you exit the stored procedure.

Что-то редко такое стало происходить... Деградировать что-ли начинаю? ;-(
...
Рейтинг: 0 / 0
26.11.2002, 08:17:45
| Ответить | Цитировать | Написать  
Рекурсия и Хранимая процедура
    #32072520
Chainiko
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Chainiko
Гость
Re Chicago
Огромное спасибо. Очень признателен за указанные ошибки! Приятно, что Вы все очень подробно описали. Со всем согласен.
On Error Resume Next - 2 раза действительно погорячился, а влепил его потому, что много надо было писать кода на проверки возвращаемых значений: есть ли записи в рекордсете, если есть, то поле NULL или нет, и только когда есть значение - инкрементировать.
Еще раз благодарю.
...
Рейтинг: 0 / 0
26.11.2002, 14:24:03
| Ответить | Цитировать | Написать  
32 сообщений из 32, показаны все 2 страниц
  все  
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Рекурсия и Хранимая процедура
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение   Полит. конфиден.  
NoSQL.ru       Кролег: Проекты, Новости, Чат       РЕД ФОРУМ       Фотоальбом: Участники, Встречи
Закрыть
start [/forum/topic.php?all=1&fid=46&tid=1818499]:
 0ms
get settings:
 6ms
get forum list:
 10ms
check forum access:
 2ms
check topic access:
 2ms
track hit:
 54ms
get topic data:
 8ms
get forum data:
 2ms
get page messages:
 44ms
get tp. blocked users:
 1ms
others: 198ms
total:  327ms
созд. / загр.: 327ms
x
x
Закрыть


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