Implementação não recursiva de classificação infinita (versão de procedimento armazenado)

Mais ou menos, criamos menus de dois ou três níveis, como diretórios em árvore e classificações de produtos. Se encontrarmos mais níveis de classificação, geralmente usamos recursão. Usar a recursão em um programa aumentará mais ou menos alguma sobrecarga de desempenho.

Eu usei ASP.net para implementar um diretório de classificação de nível infinito não recursivo no programa antes, mas considerando que a portabilidade não é forte, mudei para um procedimento armazenado e enviei para todos estudarem juntos. durante o processo de teste, não foram encontrados problemas. Além disso, o código não foi otimizado.

Normalmente, a maioria de nossas operações é para ler diretórios, portanto, na implementação a seguir, precisamos apenas de uma instrução Select para ler. Sem recursão, o nível é teoricamente infinito ~!
================================================= ====================
Estrutura da tabela:
Nome da tabela: Tb_Column
Estrutura da tabela (todos os campos não estão vazios):
Column_ID int chave primária (nota: não identificador)
Column_Name nome da classificação nvarchar(50)
Parent_ID int ID da categoria pai (valor padrão 0)
Caminho de classificação Column_Path nvarchar(1000)
Column_Depth profundidade de classificação interna (valor padrão 0)
Classificação interna Column_Order (padrão 0)
Column_Intro descrição da classificação nvarchar (1000)

============================================ === ==================
Procedimento armazenado um: Crie uma nova classificação

CREATE PROCEDURE sp_Column_Insert
(
@Parent_ID int,
@Column_Name nvarchar(50),
@Column_Intro nvarchar(1000)
)
COMO
Declarar @Err como int
Definir @Err=0

Iniciar Tran
--Obter o ID da coluna dos registros existentes
Declarar @Column_ID como int
Declare @Column_Depth como int
Selecione @Column_ID = Max(Column_ID) de Tb_Column
SE @Column_ID não for nulo
Definir @Column_ID = @Column_ID+1
Outro
Definir @Column_ID = 1

– Determine se é uma coluna de nível superior e defina seu Column_Path e Column_Order
Declare @Column_Path como nvarchar(1000)
Declare @Column_Order como int
SE @Parent_ID = 0
Começar
Definir @Column_Path =Ltrim(Str(@Column_ID))
Selecione @Column_Order = Max(Column_Order) de Tb_Column
SE @Column_Order não for nulo
Definir @Column_Order = @Column_Order + 1
Else --Se nenhum registro for encontrado, significa que este é o primeiro registro
Definir @Column_Order = 1

--Profundidade
Definir @Column_Depth = 1
Fim
Outro
Começar
--Obtenha o caminho e a profundidade do nó pai
Selecione @Column_Path = Column_Path ,@Column_Depth = Column_Depth de Tb_Column Onde

Column_ID=@Parent_ID
SE @Column_Path for nulo
Começar
Definir @Err = 1
Vá até o fim
End

--Obtém o número de sequência máximo no mesmo nó pai
Selecione @Column_Order = Max(Column_Order) de Tb_PicColumn Onde Column_Path como

''+@Column_Path+'|%' Ou Column_ID = @Parent_ID
IF @Column_Order não for nulo --Se o número de sequência existir, adicione 1 a todos os números de sequência após o número de sequência
Começar
--Atualiza os números de sequência de todos os nós após o nó atual a ser inserido
Atualizar conjunto Tb_Column Column_Order = Column_Order +1 Onde Column_Order

>@Column_Order
--O número de sequência máximo no mesmo nó pai mais 1 forma seu próprio número de sequência
Definir @Column_Order = @Column_Order + 1
Fim
Outro
Começar
Definir @Err=1
Vá até o fim
End

--O caminho do nó pai mais seu próprio número de ID formam seu próprio caminho
Definir @Column_Path = @Column_Path + '|' + Ltrim(Str(@Column_ID))

--Profundidade
Definir @Column_Depth = @Column_Depth+1

End

Inserir em Tb_Column(Column_ID,Column_Name,Parent_ID,Column_Path,

Column_Depth,Column_Order,Column_Intro) Valores(@Column_ID,@Column_Name,@Parent_ID,@Column_Path,@Column_Depth,@Column _Order,@ Coluna_Intro )

SE @@Erro<>0
Começar
Definir @Err=1
Vá até o fim
End

--Atualiza a ORDEM do registro após o registro atual
--Update Tb_Column Set Column_Order = Column_Order+1 Onde Column_Order > @Column_Order

theEnd:
SE @Err=0
Começar
Confirmar Trans
Retornar @Column_ID
Fim
Outro
Começar
Reverter Tran
Retorno 0
Fim
VAI

================================================ ============================
Procedimento armazenado dois: excluir categoria
CRIAR PROCEDIMENTO sp_Column_Delete
(
@Column_IDint
)
COMO
Declarar @Err como int
Definir @Err = 0
Iniciar Tran
--Primeiro verifique se há nós filhos sob o nó
Selecione Column_ID de Tb_Column onde Parent_ID = @Column_ID
SE @@RowCount<>0
Começar
Definir @Err = 1
Vá até o fim
End

- Obtenha o Column_Order do nó, para classificar a ordem dos outros registros após a exclusão
Declare @Column_Order como int
Selecione @Column_Order = Column_Order de Tb_Column Onde Column_ID = @Column_ID
SE @Column_Order for NUlo
Começar
Definir @Err =2
Vá até o fim
End

--Atualiza o Column_Order de outros registros
Atualizar conjunto Tb_Column Column_Order = Column_Order -1 Onde Column_Order >@Column_Order
SE @@Erro<>0
Começar
Definir @Err =3
Vá até o fim
Fim

da operação --delete
Excluir de Tb_Column onde Column_ID=@Column_ID
SE @@Erro<>0
Começar
Definir @Err =4
Vá até o fim
End

--Atualiza o Column_ID de outros registros
--Update Tb_Column Set Column_ID= Column_ID - 1 Onde Column_ID >@Column_ID
--SE @@Erro<>0
-- Começar
-- Definir @Err=5
- Vá para o fim
-- Fim

do fim:
SE @Erro = 0
Começar
Confirmar Trans
Retornar 0 --Excluir com sucesso
Fim
Outro
Começar
SE @Err=1
Começar
Reverter Tran
Retorno 1 --tem nós filhos
Fim
Outro
Começar
Reverter Tran
Retorno 2 – Erro desconhecido
Fim
Fim
IR
================================================= ================
Procedimento armazenado três: Editar classificação
CRIAR PROCEDIMENTO sp_Column_Update
(
@Column_IDint,
@Parent_ID int,
@Column_Name nvarchar(50),
@Column_Intro nvarchar(1000)
)
COMO
Declarar @Err como int
Set @Err=0

Begin Tran

--Obtenha os valores antes da modificação: Parent_ID, Column_Depth, Column_Order
Declare @oParent_ID como int
Declare @oColumn_Depth como int
Declare @oColumn_Order como int
Declare @oColumn_Path como nvarchar(1000)

Selecione @oParent_ID = Parent_ID, @oColumn_Depth = Column_Depth,@oColumn_Order = Column_Order, @oColumn_Path = Column_Path de Tb_Column Onde Column_ID = @Column_ID
SE @oParent_ID for nulo
Começar
Definir @Err = 1
Vá até o fim
Fim

--Se o ID pai não foi alterado, modifique diretamente o nome da coluna e a introdução da coluna.
SE @oParent_ID = @Parent_ID
Começar
Atualizar conjunto Tb_Column Column_Name = @Column_Name,Column_Intro = @Column_Intro Onde Column_ID = @Column_ID
SE @@Erro <> 0
Definir @Err = 2
Vá até o fim
Fim

Declare @nColumn_Path como nvarchar(1000)
Declare @nColumn_Depth como int
Declare @nColumn_Order As int

- Obtenha o número de nós contidos no nó atual como o nó pai [incluindo ele mesmo] Observação: se "1" for retornado, significa que é um único nó
Declare @theCount como int
Selecione @theCount = Count(Column_ID) de Tb_Column Onde Column_ID=@Column_ID Ou Column_Path como ''+@oColumn_Path+'|%'
SE @theCount for nulo
Começar
Definir @Err = 3
Vá até o fim
End

IF @Parent_ID=0 --Se estiver definido como um nó de nível superior, defina o nó como o último nó de nível superior
Começar
--Print 'Definir como coluna de nível superior'
Definir @nColumn_Path = Ltrim(Str(@Column_ID))
Definir @nColumn_Depth =1

Selecione @nColumn_Order = Max(Column_Order) de Tb_Column
SE @nColumn_Order for NULO
Começar
Definir @Err = 4
Vá até o fim
End

Set @nColumn_Order = @nColumn_Order - @theCount + 1

--Atualizar três partes 1 O próprio nó 2 Todos os nós filhos 2 A ordem dos registros subsequentes antes que esta árvore mude
--Print 'Atualizar todas as colunas após a posição anterior desta coluna [excluindo subcolunas nesta coluna]: Column_Order'
Atualizar Tb_Column Set Column_Order = Column_Order-@theCount Where (Column_Order >@oColumn_Order) And (Column_Path Diferente de ''+@oColumn_Path+'|%' )
SE @@Erro <> 0
Começar
Definir @Err = 7
Vá até o fim
End

--Print 'Atualizar esta coluna: Parent_ID, Column_Path, Column_Depth, Column_Order, Column_Name, Column_Intro'
Imprimir 'Ordem: '+Ltrim(Str(@nColumn_Order))
Atualizar conjunto Tb_Column Parent_ID=@Parent_ID,Column_Path = @nColumn_Path,Column_Depth = @nColumn_Depth,Column_Order = @nColumn_Order, Column_Name = @Column_Name,Column_Intro = @Column_Intro Onde Column_ID = @Column_ID
SE @@Erro <> 0
Começar
Definir @Err = 5
Vá até o fim
End

--Print 'Atualiza todas as subcolunas nesta coluna: Column_Path, Column_Depth, Column_Order'
Atualizar Tb_Column Set Column_Path = Replace(Column_Path,@oColumn_Path,@nColumn_Path),Column_Depth = Column_Depth + (@nColumn_Depth-@oColumn_Depth),Column_Order = Column_Order+( @nColumn_Order-@oColumn_Order) Onde Column_Path como ''+@oColumn_Path+ '|%'
SE @@Erro <> 0
Começar
Definir @Err = 6
Vá até o fim
Fim

Fim
Outro
Começar
--Obtenha as informações relevantes do futuro nó pai e defina os valores relevantes deste nó
Selecione @nColumn_Depth = Column_Depth,@nColumn_Path = Column_Path de Tb_Column Onde Column_ID = @Parent_ID
SE @nColumn_Depth for NULO ou @nColumn_Path for nulo
Começar
Definir @Err = 8
Vá até o fim
Fim
Definir @nColumn_Depth = @nColumn_Depth +1
Selecione @nColumn_Order =Max(Column_Order) de Tb_Column Onde Column_ID = @Parent_ID Ou Column_Path como ''+@nColumn_Path+'|%'
SE @nColumn_Order for NULO
Começar
Definir @Err = 9
Vá até o fim
End

Set @nColumn_Path = @nColumn_Path +'|'+ Ltrim(Str(@Column_ID))

IF @nColumn_Order = @oColumn_Order+1 --Se o novo nó pai for o irmão mais próximo acima da posição original, a ordem de todos os nós será seja diferente.
Começar
Atualizar conjunto Tb_Column Parent_ID=@Parent_ID,Column_Path = @nColumn_Path,Column_Depth = @nColumn_Depth, Column_Name = @Column_Name,Column_Intro = @Column_Intro Onde Column_ID = @Column_ID
SE @@Erro <> 0
Começar
Definir @Err = 10
Vá até o fim
Fim
End

Set @nColumn_Order = @nColumn_Order + 1

--Atualizar três partes 1 A ordem dos registros seguintes (ou anteriores) antes que esta árvore mude 1 O próprio nó 3 Todos os nós filhos
--Dividido em movimento ascendente ou movimento descendente
--Print 'Atualizar todas as colunas após a posição anterior desta coluna [ou a posição após esta coluna] [excluindo subcolunas nesta coluna]: Column_Order'
SE @nColumn_Order < @oColumn_Order
Começar
Atualizar Tb_Column Set Column_Order = Column_Order+@theCount Where Column_Order<@oColumn_Order And Column_Order >=@nColumn_Order And (Column_Path Diferente de ''+@oColumn_Path+'|%' ) E Column_ID<>@Column_ID
SE @@Erro <> 0
Começar
Definir @Err = 12
Vá até o fim
Fim
Fim
Outro
Começar
Atualizar Tb_Column Set Column_Order = Column_Order-@theCount Where Column_Order >@oColumn_Order And Column_Order<@nColumn_Order And (Column_Path Diferente de ''+@oColumn_Path+'|%' ) E Column_ID<>@Column_ID
SE @@Erro <> 0
Começar
Definir @Err = 13
Vá até o fim
Fim
End

--Print 'Atualizar esta coluna: Parent_ID, Column_Path, Column_Depth, Column_Order, Column_Name, Column_Intro'
Imprimir 'Ordem: '+Ltrim(Str(@nColumn_Order))
SE @nColumn_Order > @oColumn_Order
Definir @nColumn_Order = @nColumn_Order - @theCount
Atualizar conjunto Tb_Column Parent_ID=@Parent_ID,Column_Path = @nColumn_Path,Column_Depth = @nColumn_Depth,Column_Order = @nColumn_Order, Column_Name = @Column_Name,Column_Intro = @Column_Intro Onde Column_ID = @Column_ID
SE @@Erro <> 0
Começar
Definir @Err = 10
Vá até o fim
End

--Print 'Atualiza todas as subcolunas nesta coluna: Column_Paht, Column_Depth, Column_Order'
Atualizar Tb_Column Set Column_Path = Replace(Column_Path,@oColumn_Path,@nColumn_Path),Column_Depth = Column_Depth + (@nColumn_Depth-@oColumn_Depth),Column_Order = Column_Order+(@nColumn_Order-@oColumn_Order) Onde Column_Path como ''+@oColumn_Path+' |%'
SE @@Erro <> 0
Começar
Definir @Err = 11
Vá até o fim
Fim
Acabar com

o fim:
IF @Err<>0 --Se houver um erro, retorne o número do erro
Começar
Reverter Tran
Retornar @Err
Fim
Else --retorna 0 se não houver erro
Começar
Confirmar Trans
Retorno 0
Fim
IR
================================================= =========================
Procedimento armazenado quatro: classificação de exibição (apenas uma instrução select)
Lista de categorias:
CRIAR PROCEDIMENTO sp_Column_List
COMO
SELECIONE Column_ID, Column_Name, Parent_ID, Column_Path, Column_Depth,
Ordem_Coluna, Introdução_Coluna
DE Tb_Coluna
ORDER BY Coluna_Ordem
VAI

======================================
Aqui está um exemplo de uso em ASP.NET, postado no fórum de um amigo:
http://www.mzline.com/bbs/dispbbs.asp?boardID=67&ID=5044&replyID=25788&star=1&skin=0#25788