More or less, we have made two- or three-level menus such as tree directories and product classifications. If we encounter more levels of classification, we generally use recursion. Using recursion in a program will more or less increase some performance overhead.
I used ASP.net to implement a non-recursive infinite-level classification directory in the program before, but considering that the portability is not strong, I changed it to a stored procedure and sent it out for everyone to study together. So far, during the test process, there is no Problems were found. In addition, the code has not been optimized.
Normally, most of our operations are to read directories, so in the following implementation, we only need one Select statement to read. Without recursion, the level is theoretically infinite~!
================================================== ====================
Table structure:
Table name: Tb_Column
Table structure (all fields are non-empty):
Column_ID int primary key (note: non-identifier)
Column_Name nvarchar(50) classification name
Parent_ID int Parent category ID (default value 0)
Column_Path nvarchar(1000) classification path
Column_Depth int classification depth (default value 0)
Column_Order int sort (default 0)
Column_Intro nvarchar(1000) classification description
============================================== ==================
Stored procedure one: Create a new classification
CREATE PROCEDURE sp_Column_Insert
(
@Parent_ID int,
@Column_Name nvarchar(50),
@Column_Intro nvarchar(1000)
)
AS
Declare @Err As int
Set @Err=0
Begin Tran
--Get the column ID from existing records
Declare @Column_ID As int
Declare @Column_Depth As int
Select @Column_ID = Max(Column_ID) From Tb_Column
IF @Column_ID Is Not Null
Set @Column_ID = @Column_ID+1
Else
Set @Column_ID = 1
-- Determine whether it is a top-level column and set its Column_Path and Column_Order
Declare @Column_Path As nvarchar(1000)
Declare @Column_Order As int
IF @Parent_ID = 0
Begin
Set @Column_Path =Ltrim(Str(@Column_ID))
Select @Column_Order = Max(Column_Order) From Tb_Column
IF @Column_Order Is Not Null
Set @Column_Order = @Column_Order + 1
Else --If no record is found, it means this is the first record
Set @Column_Order = 1
--Depth
Set @Column_Depth = 1
End
Else
Begin
--Get the path and depth of the parent node
Select @Column_Path = Column_Path ,@Column_Depth = Column_Depth From Tb_Column Where
Column_ID=@Parent_ID
IF @Column_Path Is Null
Begin
Set @Err = 1
Goto theEnd
End
--Get the maximum sequence number under the same parent node
Select @Column_Order = Max(Column_Order) From Tb_PicColumn Where Column_Path like
''+@Column_Path+'|%' Or Column_ID = @Parent_ID
IF @Column_Order Is Not Null --If the sequence number exists, then add 1 to all sequence numbers after the sequence number
Begin
--Update the sequence numbers of all nodes after the current node to be inserted
Update Tb_Column Set Column_Order = Column_Order +1 Where Column_Order
>@Column_Order
--The maximum sequence number under the same parent node plus 1 forms its own sequence number
Set @Column_Order = @Column_Order + 1
End
Else
Begin
Set @Err=1
Goto theEnd
End
--The path of the parent node plus its own ID number form its own path
Set @Column_Path = @Column_Path + '|' + Ltrim(Str(@Column_ID))
--Depth
Set @Column_Depth = @Column_Depth+1
End
Insert Into Tb_Column(Column_ID,Column_Name,Parent_ID,Column_Path,
Column_Depth,Column_Order,Column_Intro) Values(@Column_ID,@Column_Name,@Parent_ID,@Column_Path,@Column_Depth,@Column _Order,@Column_Intro)
IF @@Error<>0
Begin
Set @Err=1
Goto theEnd
End
--Update the ORDER of the record after the current record
--Update Tb_Column Set Column_Order = Column_Order+1 Where Column_Order > @Column_Order
theEnd:
IF @Err=0
Begin
Commit Trans
Return @Column_ID
End
Else
Begin
Rollback Tran
Return 0
End
GO
================================================= ============================
Stored procedure two: delete category
CREATE PROCEDURE sp_Column_Delete
(
@Column_ID int
)
AS
Declare @Err As int
Set @Err = 0
Begin Tran
--First check whether there are child nodes under the node
Select Column_ID From Tb_Column Where Parent_ID = @Column_ID
IF @@RowCount<>0
Begin
Set @Err = 1
Goto the End
End
-- Get the Column_Order of the node, in order to sort out the order of other records after deletion
Declare @Column_Order As int
Select @Column_Order = Column_Order From Tb_Column Where Column_ID = @Column_ID
IF @Column_Order Is NUll
Begin
Set @Err =2
Goto theEnd
End
--Update the Column_Order of other records
Update Tb_Column Set Column_Order = Column_Order -1 Where Column_Order >@Column_Order
IF @@Error<>0
Begin
Set @Err =3
Goto theEnd
End
--delete operation
Delete From Tb_Column Where Column_ID=@Column_ID
IF @@Error<>0
Begin
Set @Err =4
Goto the End
End
--Update the Column_ID of other records
--Update Tb_Column Set Column_ID= Column_ID - 1 Where Column_ID >@Column_ID
--IF @@Error<>0
-- Begin
-- Set @Err =5
-- Goto the End
-- End
theEnd:
IF @Err = 0
Begin
Commit Trans
Return 0 --Delete successfully
End
Else
Begin
IF @Err=1
Begin
Rollback Tran
Return 1 --has child nodes
End
Else
Begin
Rollback Tran
Return 2--Unknown error
End
End
GO
================================================== ================
Stored procedure three: Edit classification
CREATE PROCEDURE sp_Column_Update
(
@Column_ID int,
@Parent_ID int,
@Column_Name nvarchar(50),
@Column_Intro nvarchar(1000)
)
AS
Declare @Err As int
Set @Err=0
Begin Tran
--Get the values before modification: Parent_ID, Column_Depth, Column_Order
Declare @oParent_ID As int
Declare @oColumn_Depth As int
Declare @oColumn_Order As int
Declare @oColumn_Path As nvarchar(1000)
Select @oParent_ID = Parent_ID, @oColumn_Depth = Column_Depth,@oColumn_Order = Column_Order, @oColumn_Path = Column_Path From Tb_Column Where Column_ID = @Column_ID
IF @oParent_ID Is Null
Begin
Set @Err = 1
Goto the End
End
--If the parent ID has not changed, directly modify the column name and column introduction.
IF @oParent_ID = @Parent_ID
Begin
Update Tb_Column Set Column_Name = @Column_Name,Column_Intro = @Column_Intro Where Column_ID = @Column_ID
IF @@Error <> 0
Set @Err = 2
Goto the End
End
Declare @nColumn_Path As nvarchar(1000)
Declare @nColumn_Depth As int
Declare @nColumn_Order As int
-- Get the number of nodes contained in the current node as the parent node [including itself] Note: If "1" is returned, it means it is a single node
Declare @theCount As int
Select @theCount = Count(Column_ID) From Tb_Column Where Column_ID=@Column_ID Or Column_Path like ''+@oColumn_Path+'|%'
IF @theCount Is Null
Begin
Set @Err = 3
Goto the End
End
IF @Parent_ID=0 --If it is set as a top-level node, set the node to the last top-level node
Begin
--Print 'Set as top-level column'
Set @nColumn_Path = Ltrim(Str(@Column_ID))
Set @nColumn_Depth =1
Select @nColumn_Order = Max(Column_Order) From Tb_Column
IF @nColumn_Order Is NULL
Begin
Set @Err = 4
Goto the End
End
Set @nColumn_Order = @nColumn_Order - @theCount + 1
--Update three parts 1 The node itself 2 All child nodes 2 The order of subsequent records before this tree changes
--Print 'Update all columns after the previous position of this column [excluding sub-columns under this column]: Column_Order'
Update Tb_Column Set Column_Order = Column_Order-@theCount Where (Column_Order >@oColumn_Order) And (Column_Path Not like ''+@oColumn_Path+'|%' )
IF @@Error <> 0
Begin
Set @Err = 7
Goto the End
End
--Print 'Update this column: Parent_ID, Column_Path, Column_Depth, Column_Order, Column_Name, Column_Intro'
Print 'Order : '+Ltrim(Str(@nColumn_Order))
Update Tb_Column Set Parent_ID=@Parent_ID,Column_Path = @nColumn_Path,Column_Depth = @nColumn_Depth,Column_Order = @nColumn_Order, Column_Name = @Column_Name,Column_Intro = @Column_Intro Where Column_ID = @Column_ID
IF @@Error <> 0
Begin
Set @Err = 5
Goto the End
End
--Print 'Update all sub-columns under this column: Column_Path, Column_Depth, Column_Order'
Update 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) Where Column_Path like ''+@oColumn_Path+'|%'
IF @@Error <> 0
Begin
Set @Err = 6
Goto theEnd
End
End
Else
Begin
--Get the relevant information of the future parent node and set the relevant values of this node
Select @nColumn_Depth = Column_Depth,@nColumn_Path = Column_Path From Tb_Column Where Column_ID = @Parent_ID
IF @nColumn_Depth Is NULL Or @nColumn_Path Is Null
Begin
Set @Err = 8
Goto theEnd
End
Set @nColumn_Depth = @nColumn_Depth +1
Select @nColumn_Order =Max(Column_Order) From Tb_Column Where Column_ID = @Parent_ID Or Column_Path like ''+@nColumn_Path+'|%'
IF @nColumn_Order Is NULL
Begin
Set @Err = 9
Goto theEnd
End
Set @nColumn_Path = @nColumn_Path +'|'+ Ltrim(Str(@Column_ID))
IF @nColumn_Order = @oColumn_Order+1 --If the new parent node is the nearest sibling above the original position, the order of all nodes will be different. Change
Begin
Update Tb_Column Set Parent_ID=@Parent_ID,Column_Path = @nColumn_Path,Column_Depth = @nColumn_Depth, Column_Name = @Column_Name,Column_Intro = @Column_Intro Where Column_ID = @Column_ID
IF @@Error <> 0
Begin
Set @Err = 10
Goto the End
End
End
Set @nColumn_Order = @nColumn_Order + 1
--Update three parts 1 The order of the following (or previous) records before this tree changes 1 The node itself 3 All child nodes
--Divided into upward movement or downward movement
--Print 'Update all columns after the previous position of this column [or the position after this column] [excluding sub-columns under this column]: Column_Order'
IF @nColumn_Order < @oColumn_Order
Begin
Update Tb_Column Set Column_Order = Column_Order+@theCount Where Column_Order<@oColumn_Order And Column_Order >=@nColumn_Order And (Column_Path Not like ''+@oColumn_Path+'|%' ) And Column_ID<>@Column_ID
IF @@Error <> 0
Begin
Set @Err = 12
Goto the End
End
End
Else
Begin
Update Tb_Column Set Column_Order = Column_Order-@theCount Where Column_Order >@oColumn_Order And Column_Order<@nColumn_Order And (Column_Path Not like ''+@oColumn_Path+'|%' ) And Column_ID<>@Column_ID
IF @@Error <> 0
Begin
Set @Err = 13
Goto theEnd
End
End
--Print 'Update this column: Parent_ID, Column_Path, Column_Depth, Column_Order, Column_Name, Column_Intro'
Print 'Order : '+Ltrim(Str(@nColumn_Order))
IF @nColumn_Order > @oColumn_Order
Set @nColumn_Order = @nColumn_Order - @theCount
Update Tb_Column Set Parent_ID=@Parent_ID,Column_Path = @nColumn_Path,Column_Depth = @nColumn_Depth,Column_Order = @nColumn_Order, Column_Name = @Column_Name,Column_Intro = @Column_Intro Where Column_ID = @Column_ID
IF @@Error <> 0
Begin
Set @Err = 10
Goto theEnd
End
--Print 'Update all sub-columns under this column: Column_Paht, Column_Depth, Column_Order'
Update 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) Where Column_Path like ''+@oColumn_Path+'|%'
IF @@Error <> 0
Begin
Set @Err = 11
Goto the End
End
End
the End:
IF @Err<>0 --If there is an error, return the error number
Begin
Rollback Tran
Return @Err
End
Else --returns 0 if there is no error
Begin
Commit Trans
Return 0
End
GO
================================================== =========================
Stored procedure four: display classification (just a select statement)
Category list:
CREATE PROCEDURE sp_Column_List
AS
SELECT Column_ID, Column_Name, Parent_ID, Column_Path, Column_Depth,
Column_Order, Column_Intro
FROM Tb_Column
ORDER BY Column_Order
GO
=======================================
Here is an example of use under ASP.NET, posted on a friend's forum:
http://www.mzline.com/bbs/dispbbs.asp?boardID=67&ID=5044&replyID=25788&star=1&skin=0#25788