none
T-SQL Хитрый курсор или Как обойтись без рекурсии для удаления сообщения из n - уровневой структуры RRS feed

  • Вопрос

  • Есть база состоящая из одной таблицы:
    create table myMessage
    (
       idM int primary key,
       idR int foreign key references myMessage(idM),
       myText varchar(50),
       label int
    )
    Необходимо Написать процедуру которая удалит все сообщения с меткой - 1, и если у сообщения есть "подчиненные", то удалить и их, а если у подчинённых есть ещё подч....
    У меня получилось удалять только двухуровневую структуру, т е
    Данные для двухуровневой структуры:
    insert into myMessage(idM,idR,myText,label) values(1,null,'Message 1, Root null', 0)
    insert into myMessage(idM,idR,myText,label) values(2,1,'Message 2, Root 1', 0)
    insert into myMessage(idM,idR,myText,label) values(3,1,'Message 3, Root 1', 0)
    insert into myMessage(idM,idR,myText,label) values(4,null,'Message 4, Root null',1)
    insert into myMessage(idM,idR,myText,label) values(5,4,'Message 5, Root 4', 0)
    insert into myMessage(idM,idR,myText,label) values(6,4,'Message 6, Root 4', 0)
    Моя процедура для удаления сообщений c label=1:
    drop proc delM
    go
    create proc delM
    as
        declare @tempR int
        declare cursorR cursor for select idM from myMessage where label=1
        open cursorR
        fetch next from cursorR into @tempR
        while @@fetch_status=0
        begin
            if(exists(select * from myMessage where idR is null and idM=@tempR))
           
             begin
       
               declare @tempChild int
               declare cursorChild cursor for select idM from myMessage where idR= @tempR
               open cursorChild
               fetch next from cursorChild into @tempChild

               while @@fetch_status=0
                   begin
                       
                       delete from myMessage where idM=@tempChild and idR=@tempR
                       fetch next from cursorChild into @tempChild
                end
               close cursorChild
               deallocate cursorChild
               delete from myMessage where idM=@tempR and idR is null
               
           
               
            end
      else if(exists(select * from myMessage where idR is not null and idM=@tempR))
          begin
              delete from myMessage where idM=@tempR
          end
          fetch next from cursorR into @tempR
      end
        close cursorR
        deallocate cursorR
    go

     

    Поясняющий рисунок -  http://s50.radikal.ru/i129/1004/28/755fecc444ef.jpg

    • Перемещено Siddharth Chavan 2 октября 2010 г. 0:17 MSDN Forums Consolidation (От:SQL Server для разработчиков)
    18 апреля 2010 г. 15:05

Ответы

  • Нет, ваш код всё таки не работает. У моей подруги получилось сделать, через динамический курсор следующим образом:

    База:

    drop table message
    go
    create table message (
        id int identity (1,1),
        idRoot int,
        text_ text,
        metka int not null
    );

    Заполнение базы:

    insert into message(idRoot,text_,metka) values (null,'Root is null, Massage 1',1)
    insert into message(idRoot,text_,metka) values (1,'Root is 1, Massage 2',0)
    insert into message(idRoot,text_,metka) values (1,'Root is 1, Massage 3',0)
    insert into message(idRoot,text_,metka) values (1,'Root is 1, Massage 4',0)
    insert into message(idRoot,text_,metka) values (null,'Root is null, Massage 1',0)
    insert into message(idRoot,text_,metka) values (5,'Root is 5, Massage 6',0)
    insert into message(idRoot,text_,metka) values (5,'Root is 5, Massage 7',0)
    insert into message(idRoot,text_,metka) values (5,'Root is 5, Massage 8',1)
    insert into message(idRoot,text_,metka) values (8,'Root is 5, Massage 9',0)
    insert into message(idRoot,text_,metka) values (8,'Root is 5, Massage 10',0)

     

    Процедура с динамическим курсором:

    drop proc deleteMetka
    go
    create proc deleteMetka
    as

    declare curs cursor dynamic
    for select a.id, b.id from message as a inner join message as b
    on a.id = b.idRoot
    where a.metka<>0 and b.metka = 0
    order by a.id desc, b.id desc

    declare @a int, @b int, @metka int
    set @metka = -1

    open curs
    fetch next from curs into @a, @b


    while @@fetch_status = 0
    begin

    update message set metka = @metka where id = @b
    set @metka = @metka - 1


    fetch next from curs into @a, @b
    close curs
    open curs
    end

    close curs
    deallocate curs

    declare cursDel cursor
    for select id, idRoot, metka from message where metka <> 0

    open cursDel

    fetch next from cursDel into @a, @b, @metka
    while @@fetch_status = 0
    begin

    delete from message where id = @a

    fetch next from cursDel into @a, @b, @metka
    end

    close cursDel
    deallocate cursDel
    go

     

    Вызов процедуры и результат:

    deleteMetka
    select * from message

     

    Всё равно спасибо вам большое и привет вам из Национального исследовательского университета информационных технологий, механики и оптики))


    • Помечено в качестве ответа I.Vorontsov 20 апреля 2010 г. 6:42
    19 апреля 2010 г. 13:59

Все ответы

  • Версия сервера?
    18 апреля 2010 г. 21:36
  • SQL Server 2005 and up:

    -- Test query
    create table myMessage
    (
     idM int primary key,
     idR int foreign key references myMessage(idM),
     myText varchar(50),
     label int
    )
    insert into myMessage(idM,idR,myText,label) values(1,null,'Message 1, Root null', 0)
    insert into myMessage(idM,idR,myText,label) values(2,1,'Message 2, Root 1', 0)
    insert into myMessage(idM,idR,myText,label) values(3,1,'Message 3, Root 1', 0)
    insert into myMessage(idM,idR,myText,label) values(4,null,'Message 4, Root null',1)
    insert into myMessage(idM,idR,myText,label) values(5,4,'Message 5, Root 4', 0)
    insert into myMessage(idM,idR,myText,label) values(6,4,'Message 6, Root 4', 0)
    
    ;with cte as
    (select idM, idR as parent, myText, label, label as RootLabel from myMessage where idR IS Null
    union all
     select T.idM, C.parent as Parent, T.mytext, t.Label,C.RootLabel from myMessage T inner join cte C on t.idR = C.idM)
     
     --select * from cte where RootLabel = 1 
     delete myMessage from myMessage inner join cte on myMessage.idM = cte.idM where cte.RootLabel = 1
     
     select * from myMessage

    CTE and hierarchical queries


    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog

    19 апреля 2010 г. 2:50
  • Спасибо большое. Работает. Только жаль нам преподаватель сказал это сделать именно через динамический курсор. Может у вас будут мысли сделать тоже самое именно через Cursor. Был бы очень рад.

    В любом случае спасибо.

    19 апреля 2010 г. 12:42
  • SQL Server 2005 Management Studio Express
    19 апреля 2010 г. 12:42
  • google "SQL Server 2000 T-SQL recursion".
    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    19 апреля 2010 г. 13:10
  • Нет нам запретил преподаватель использовать рекурсию.

    P.s.: Ваш код не работает при следующем формировании базы

    insert into myMessage(idM,idR,myText,label) values(1,null,'Message 1, Root null', 0)
    insert into myMessage(idM,idR,myText,label) values(2,1,'Message 2, Root 1', 1)
    insert into myMessage(idM,idR,myText,label) values(3,1,'Message 3, Root 1', 0)
    insert into myMessage(idM,idR,myText,label) values(4,null,'Message 4, Root null',0)
    insert into myMessage(idM,idR,myText,label) values(5,4,'Message 5, Root 4', 0)
    insert into myMessage(idM,idR,myText,label) values(6,4,'Message 6, Root 4', 0)
    insert into myMessage(idM,idR,myText,label) values(7,5,'Message 7, Root 5', 0)
    insert into myMessage(idM,idR,myText,label) values(8,5,'Message 8, Root 5', 1)
    insert into myMessage(idM,idR,myText,label) values(9,8,'Message 7, Root 5', 0)
    insert into myMessage(idM,idR,myText,label) values(10,8,'Message 8, Root 5', 0)

     

    Т.е. я наверно не так объяснил верхние узлы не обязательно помечены 1. Единице могут быть помечены сообщения в середине структуры и нужно в соответствии удалить именно их подчинённые сообщения и их самих. Или просто их если у них нет подчинённых massage'ей

    19 апреля 2010 г. 13:17
  • -- Test query
    drop table myMessage
    create table myMessage
    (
      idM int primary key,
      idR int foreign key references myMessage(idM),
      myText varchar(50),
      label int
    )
    insert into myMessage(idM,idR,myText,label) values(1,null,'Message 1, Root null', 0)
    insert into myMessage(idM,idR,myText,label) values(2,1,'Message 2, Root 1', 1)
    insert into myMessage(idM,idR,myText,label) values(3,1,'Message 3, Root 1', 0)
    insert into myMessage(idM,idR,myText,label) values(4,null,'Message 4, Root null',0)
    insert into myMessage(idM,idR,myText,label) values(5,4,'Message 5, Root 4', 0)
    insert into myMessage(idM,idR,myText,label) values(6,4,'Message 6, Root 4', 0)
    insert into myMessage(idM,idR,myText,label) values(7,5,'Message 7, Root 5', 0)
    insert into myMessage(idM,idR,myText,label) values(8,5,'Message 8, Root 5', 1)
    insert into myMessage(idM,idR,myText,label) values(9,8,'Message 7, Root 5', 0)
    insert into myMessage(idM,idR,myText,label) values(10,8,'Message 8, Root 5', 0)
    
    ;with cte as
    (select idM, idR as parent, myText, label, label as RootLabel from myMessage where idR IS Null
    union all
     select T.idM, T.idR as Parent, T.mytext, t.Label,C.RootLabel from myMessage T inner join cte C on t.idR = C.idM)
     
     --select * from cte where RootLabel = 1 or label = 1
     delete myMessage from myMessage inner join cte on myMessage.idM = cte.idM where cte.RootLabel = 1 or cte.label = 1
     
     select * from myMessage

    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    19 апреля 2010 г. 13:32
  • Нет, ваш код всё таки не работает. У моей подруги получилось сделать, через динамический курсор следующим образом:

    База:

    drop table message
    go
    create table message (
        id int identity (1,1),
        idRoot int,
        text_ text,
        metka int not null
    );

    Заполнение базы:

    insert into message(idRoot,text_,metka) values (null,'Root is null, Massage 1',1)
    insert into message(idRoot,text_,metka) values (1,'Root is 1, Massage 2',0)
    insert into message(idRoot,text_,metka) values (1,'Root is 1, Massage 3',0)
    insert into message(idRoot,text_,metka) values (1,'Root is 1, Massage 4',0)
    insert into message(idRoot,text_,metka) values (null,'Root is null, Massage 1',0)
    insert into message(idRoot,text_,metka) values (5,'Root is 5, Massage 6',0)
    insert into message(idRoot,text_,metka) values (5,'Root is 5, Massage 7',0)
    insert into message(idRoot,text_,metka) values (5,'Root is 5, Massage 8',1)
    insert into message(idRoot,text_,metka) values (8,'Root is 5, Massage 9',0)
    insert into message(idRoot,text_,metka) values (8,'Root is 5, Massage 10',0)

     

    Процедура с динамическим курсором:

    drop proc deleteMetka
    go
    create proc deleteMetka
    as

    declare curs cursor dynamic
    for select a.id, b.id from message as a inner join message as b
    on a.id = b.idRoot
    where a.metka<>0 and b.metka = 0
    order by a.id desc, b.id desc

    declare @a int, @b int, @metka int
    set @metka = -1

    open curs
    fetch next from curs into @a, @b


    while @@fetch_status = 0
    begin

    update message set metka = @metka where id = @b
    set @metka = @metka - 1


    fetch next from curs into @a, @b
    close curs
    open curs
    end

    close curs
    deallocate curs

    declare cursDel cursor
    for select id, idRoot, metka from message where metka <> 0

    open cursDel

    fetch next from cursDel into @a, @b, @metka
    while @@fetch_status = 0
    begin

    delete from message where id = @a

    fetch next from cursDel into @a, @b, @metka
    end

    close cursDel
    deallocate cursDel
    go

     

    Вызов процедуры и результат:

    deleteMetka
    select * from message

     

    Всё равно спасибо вам большое и привет вам из Национального исследовательского университета информационных технологий, механики и оптики))


    • Помечено в качестве ответа I.Vorontsov 20 апреля 2010 г. 6:42
    19 апреля 2010 г. 13:59