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

Откуда:
Сообщений: 21
Всем доброго времени суток! Заранее скажу,что я студент университета и знаний по БД у меня не так много,как у вас,поэтому не судите строго пожалуйста..) В чем суть вопроса...в методичке по лабораторным наткнулся на понятие рекурсивных хранимых процедур (InterBase), но так как работаю в MSSQL, то естественно не вполне понял код ХП.. В базе данных необходимо вывести категории и подкатегории, естественно связав таблицу с собой же, но и с этим возникла небольшая проблема.. В общем, кто может помочь перевести с InterBase на MSSQL код рекурсивной ХП и вообще, реально ли реализовать ее в MSSQL? Заранее благодарю за ответы.

Текст ХП:

+
CREATE PROCEDURE GETFULLNAME (ID_GOOD2SHOW INTEGER)

RETURNS (FULL_GOODS_NAME VARCHAR(1000),

ID_CHILD_GOOD INTEGER)

AS

DECLARE VARIABLE CURR_CHILD_NAME VARCHAR(80);

BEGIN


FOR SELECT gtl.id_good, gtl.good_name

FROM GoodsTree gtl

WHERE gtl.id_parent_good=:ID_good2show

INTO:ID_CHILD_GOOD, :full_goods_name

DO

BEGIN


IF (NOT EXISTS(

SELECT * FROM GoodsTree

WHERE GoodsTree.id_parent_good=:id_child_good))

THEN

BEGIN


SUSPEND;

END

ELSE


BEGIN


CURR_CHILD_NAME=full_goods_name;


FOR

SELECT ID_CHILD_GOOD, full_goods_name

FROM GETFULLNAME (:ID_CHILD_GOOD)

INTO:ID_CHILD_GOOD, :full_goods_name

DO BEGIN


full_goods_name=CURR_CHILD_NAME| ' ' | full_goods_name,-

SUSPEND; /* возвращаем полное имя товара*/

END

END

END

END


К сообщению приложен файл. Размер - 13Kb
11 дек 14, 11:41    [16982303]     Ответить | Цитировать Сообщить модератору
 Re: Рекурсивная хранимая процедура в MSSQL Server 2008 R2  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 7868
Aleksandr1008, читайте справку по рекурсивным CTE выражениям.
11 дек 14, 12:00    [16982459]     Ответить | Цитировать Сообщить модератору
 Re: Рекурсивная хранимая процедура в MSSQL Server 2008 R2  [new]
LexusR
Member

Откуда: Novosibirsk
Сообщений: 1873
declare @GoodsTree table(id_bank int,id_parent_bank int,name_place varchar(100))
insert into @GoodsTree(id_bank,id_parent_bank,name_place)
values(1,0,'Категории'),(2,1,'АТБ'),(6,2,'Амурская область'),(14,6,'г.Благовещенск')

declare @parent_id int
set @parent_id = 1

-- рекурсивный запрос
;with cte(ID_CHILD_GOOD,FULL_GOODS_NAME)AS
(
select id_bank as ID_CHILD_GOOD,cast(name_place as varchar(max)) as FULL_GOODS_NAME
FROM @GoodsTree where id_parent_bank = @parent_id
union all
select id_bank as ID_CHILD_GOOD,FULL_GOODS_NAME+' | '+name_place as FULL_GOODS_NAME
FROM @GoodsTree g
join cte  c on c.ID_CHILD_GOOD = g.id_parent_bank
)
select * from cte
11 дек 14, 12:01    [16982475]     Ответить | Цитировать Сообщить модератору
 Re: Рекурсивная хранимая процедура в MSSQL Server 2008 R2  [new]
Aleksandr1008
Member

Откуда:
Сообщений: 21
Владислав Колосов
Aleksandr1008, читайте справку по рекурсивным CTE выражениям.


если бы я еще знал,что это вообще такое..к сожалению мы этого не затрагивали..

LexusR
declare @GoodsTree table(id_bank int,id_parent_bank int,name_place varchar(100))
insert into @GoodsTree(id_bank,id_parent_bank,name_place)
values(1,0,'Категории'),(2,1,'АТБ'),(6,2,'Амурская область'),(14,6,'г.Благовещенск')

declare @parent_id int
set @parent_id = 1

-- рекурсивный запрос
;with cte(ID_CHILD_GOOD,FULL_GOODS_NAME)AS
(
select id_bank as ID_CHILD_GOOD,cast(name_place as varchar(max)) as FULL_GOODS_NAME
FROM @GoodsTree where id_parent_bank = @parent_id
union all
select id_bank as ID_CHILD_GOOD,FULL_GOODS_NAME+' | '+name_place as FULL_GOODS_NAME
FROM @GoodsTree g
join cte  c on c.ID_CHILD_GOOD = g.id_parent_bank
)
select * from cte


спасибо огромное!!! Но не могли бы вы объяснить,что тут происходит, и какие параметры передать в ХП чтобы попросить ее выдать данные по определенному банку?
11 дек 14, 12:20    [16982665]     Ответить | Цитировать Сообщить модератору
 Re: Рекурсивная хранимая процедура в MSSQL Server 2008 R2  [new]
LexusR
Member

Откуда: Novosibirsk
Сообщений: 1873
Данный пример собирает все дочерние записи с полным наименованием от заданного родителя.
Если Вам нужно собрать полное наименование от заданного банка то можно это получить 2 путями.
Выполнить запрос с параметром самого верхнего родителя и отфильтровать по нужному банку
declare @GoodsTree table(id_bank int,id_parent_bank int,name_place varchar(100))
insert into @GoodsTree(id_bank,id_parent_bank,name_place)
values(1,0,'Категории'),(2,1,'АТБ'),(6,2,'Амурская область'),(14,6,'г.Благовещенск')

declare @parent_id int
set @parent_id = 0

-- рекурсивный запрос
;with cte(ID_CHILD_GOOD,FULL_GOODS_NAME)AS
(
select id_bank as ID_CHILD_GOOD,cast(name_place as varchar(max)) as FULL_GOODS_NAME
FROM @GoodsTree where id_parent_bank = @parent_id
union all
select id_bank as ID_CHILD_GOOD,FULL_GOODS_NAME+' | '+name_place as FULL_GOODS_NAME
FROM @GoodsTree g
join cte  c on c.ID_CHILD_GOOD = g.id_parent_bank
)
select * from cte
where ID_CHILD_GOOD = 14

или переписать рекурсивный запрос для собирания полного имени снизу вверх
declare @child_id int
set @child_id = 14
;with cte(id_bank,id_parent_bank,fool_name)AS
(
select id_bank,id_parent_bank,cast(name_place as varchar(max)) as fool_name
FROM @GoodsTree where id_bank = @child_id
union all
select g.id_bank,g.id_parent_bank,name_place+' | '+fool_name as fool_name
FROM @GoodsTree g
join cte  c on c.id_parent_bank = g.id_bank
)
select top 1 * from cte
order by LEN(fool_name) desc
11 дек 14, 12:38    [16982818]     Ответить | Цитировать Сообщить модератору
 Re: Рекурсивная хранимая процедура в MSSQL Server 2008 R2  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31431
Aleksandr1008
спасибо огромное!!! Но не могли бы вы объяснить,что тут происходит, и какие параметры передать в ХП чтобы попросить ее выдать данные по определенному банку?
Происходит выполнение рекурсивного CTE
В ХП нужно передать параметр @id_bank, который соответствует переменной @parent_id из примера
11 дек 14, 12:38    [16982821]     Ответить | Цитировать Сообщить модератору
 Re: Рекурсивная хранимая процедура в MSSQL Server 2008 R2  [new]
Aleksandr1008
Member

Откуда:
Сообщений: 21
LexusR,

но ведь чтобы вытащить данные по id_bank, который я должен указать при вызове процедуры, во временную таблицу не обязательно помещать данные о всех банках вручную?...
11 дек 14, 13:03    [16983022]     Ответить | Цитировать Сообщить модератору
 Re: Рекурсивная хранимая процедура в MSSQL Server 2008 R2  [new]
o-o
Guest
Aleksandr1008,

пятничный неприкрытый дурдом
вам LexusR для примера заполнил табличную переменную @GoodsTree в надежде, что вы сообразите в его коде ее заменить своей таблицей GoodsTree
11 дек 14, 13:12    [16983080]     Ответить | Цитировать Сообщить модератору
 Re: Рекурсивная хранимая процедура в MSSQL Server 2008 R2  [new]
LexusR
Member

Откуда: Novosibirsk
Сообщений: 1873
Aleksandr1008
LexusR,

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


временная таблица просто эмулирует вашу рабочую таблицу

в запрос вместо неё поставьте название вашей рабочей таблицы.
11 дек 14, 13:13    [16983086]     Ответить | Цитировать Сообщить модератору
 Re: Рекурсивная хранимая процедура в MSSQL Server 2008 R2  [new]
Aleksandr1008
Member

Откуда:
Сообщений: 21
LexusR,

извините,завтыкал,утомился за сегодня немного,вот и туплю потихой..) причем туплю настолько,что не могу сообразить,как передать параметр... аж стыдно за это(((
11 дек 14, 13:18    [16983114]     Ответить | Цитировать Сообщить модератору
 Re: Рекурсивная хранимая процедура в MSSQL Server 2008 R2  [new]
Aleksandr1008
Member

Откуда:
Сообщений: 21
Aleksandr1008
LexusR,

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


все,параметр передал,осталось связать это с остальными таблицами и реализовать в приложении..) спасибо большое, LexusR!!! Вы меня очень выручили!!!
11 дек 14, 13:25    [16983182]     Ответить | Цитировать Сообщить модератору
 Re: Рекурсивная хранимая процедура в MSSQL Server 2008 R2  [new]
o-o
Guest
Aleksandr1008
LexusR,

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

мне вот тоже только что подсказали, что сегодня вообще-то четверг :)
так что не только вы там
а может, у вас не только пятница, но и Oracle?
синтаксис самой процедуры, да и само задание параметров наводят на мысли...
11 дек 14, 13:26    [16983194]     Ответить | Цитировать Сообщить модератору
 Re: Рекурсивная хранимая процедура в MSSQL Server 2008 R2  [new]
o-o
Guest
о блин, InterBase, извините. все, мне отсюда пора
11 дек 14, 13:27    [16983206]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить