In developing database applications, we often need to represent multi-level relationships, such as BOM, multi-level organizational structure, multi-level role relationships in the MRP ... When we describe this relationship in the database, we usually use the parent. The node points to the design table structure, such as the table structure of the department is defined as follows:
CREATE TABLE TDEPARTMENT (PARENTNO VARCHAR (25), / * Super-level department number, indicating that the current record is a top department. * / DepArtmentno varchar (25) Primary Key Clustered, / * Current department number, primary key field. * / Name nvarchar (100) Not null, / * department name. * / Nameex nvarchar (100), / * extension name. * / Remark NVARCHAR (500) / * Remarks. * /)
Functional requirements: Get the record set of the subordinate sectors of the specified department number and all or the specified hierarchies thereof.
In the previous development, we usually handle the above requirements in the client or intermediate layer, usually by programming language (VB, Delphi, Java / .NET) to process the record set returned from the database, this method usually needs Receive a record of the entire table from the database, because we cannot determine which subordinate records have of given the primary key, and then use the recursive manner in the programming code to obtain the relevant sub-record. This way of using recursive is often difficult to avoid the dead cycle problems caused by illegal records in the data (of course, you can also handle this problem, just some trouble, and usually not beautiful enough). So, can handle this problem in the database, which can reduce unnecessary data transmission, and avoid the negative impact of the recursive, it is best to automatically block the problem of illegal data points (current The recorded parent pointer field points to its subordinate record). Oh, it's time to get it, show your dagger (code, transact-sql / ms-sqlserver 2000):
/ * Copyright All (c) 2004 zhongfeng, http://blog.9cbs.net/sw515 * / create procedure dbo.pselectDepartmentHierarchy @DEPARTNO VARCHAR (25), @Depth int = -1AS set nocount on
Declare @TSTACK TABLE (Indicator VARCHAR (50))
Declare @index int set @Index = 0
INSERT INTO @tstack Select Departmentno from dbo.tdepartment Where Departmentno Like @DEPARTNO
While @@ rowcount> 0 and (@index <@Depth or @Depth <0) begin set @index = @index 1
INSERT INTO @TStack SELECT DepartmentNo FROM dbo.TDepartment WHERE ParentNo IN (SELECT Indicator FROM @TStack) AND DepartmentNo NOT IN (SELECT Indicator FROM @TStack) ENDSELECT dbo.TDepartment. * FROM @TStack AS t INNER JOIN dbo.TDepartment ON t. Indicator = dbo.tDepartment.DepartmentNogo
PS:
If you use Oracle, you don't have to be so complicated. Oracle provides recursive query features, such as the above requirements, only need a SQL statement to easily implement: SELECT PARENTNO, Departmentno, Name, Nameex, Remark from TDEPARTMENT START WITH DepartmentNO = Specified department number connectNo = departmentno;
