MSSQL 특정노드의 하위노드 전체 가져오기(Top Down)

조직도에서 하위조직 모두 가져오기

오늘 함께 알아볼 과제는 "특정노드의 하위노드 전체를 가져오는 탑다운(Top Down) 방식의 함수 만들기" 입니다. 특정조직코드를 넘기면 그 조직을 포함한 하위조직 모두를 테이블형태로 넘기는 사용자 정의함수를 만들도록 하겠습니다. 다음의 예제를 통해 알아보죠.

국내 굴지의 대기업 사성전자는 다음과 같은 조직도를 가지고 있습니다. 앞의 글에서 회사의 조직도는 트리구조를 가지고 있다고 했습니다. "회사"를 루트로 자식노드들이 나무의 가지와 같이 뻗어 있는 형태죠.

그림1. 사성전자의 조직도

회사조직도 트리 이미지

이와 같은 트리구조를 구현하려면 테이블에 다음과 같이 데이터를 구성해야 합니다. 트리구조의 조직도 데이터는 루트노드(사성전자, 10000)와 이 루트노드를 부모노드로 하는 자식노드들로 구성됩니다. 모든 조직은 자신의 코드와 코드명, 부모조직 코드를 가지게 됩니다.

조직도 테이블 데이터구성

학습에 필요한 회사 조직도 테이블과 샘플 데이터를 생성하는 쿼리는 다음과 같습니다.

--회사조직도 테이블 생성
CREATE TABLE [dbo].[Company](
	[DeptCd] [char](5) NOT NULL,
	[DeptName] [varchar](50) NULL,
	[ParentDeptCd] [char](5) NULL,
CONSTRAINT [PK_Comp] PRIMARY KEY CLUSTERED 
(
	[DeptCd] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
)ON [PRIMARY]
GO

--조직데이터 생성
INSERT INTO Company(DeptCd, DeptName, ParentDeptCd) VALUES('10000', '사성전자', null) -- Root Node
INSERT INTO Company(DeptCd, DeptName, ParentDeptCd) VALUES('11000', '경영지원본부', '10000')
INSERT INTO Company(DeptCd, DeptName, ParentDeptCd) VALUES('11100', '정보기술실', '11000')
INSERT INTO Company(DeptCd, DeptName, ParentDeptCd) VALUES('11110', '정보화전략1팀', '11100')
INSERT INTO Company(DeptCd, DeptName, ParentDeptCd) VALUES('11120', '정보화전략2팀', '11100')
INSERT INTO Company(DeptCd, DeptName, ParentDeptCd) VALUES('11200', '인사총무실', '11000')
INSERT INTO Company(DeptCd, DeptName, ParentDeptCd) VALUES('11210', '총무팀', '11200')
INSERT INTO Company(DeptCd, DeptName, ParentDeptCd) VALUES('11220', '인사팀', '11200')
INSERT INTO Company(DeptCd, DeptName, ParentDeptCd) VALUES('12000', '영업본부', '10000')
INSERT INTO Company(DeptCd, DeptName, ParentDeptCd) VALUES('12100', '국내영업', '12000')
INSERT INTO Company(DeptCd, DeptName, ParentDeptCd) VALUES('12200', '해외영업', '12000')

테이블 반환 사용자 정의함수 만들기

여기서 만들려는 함수는 조직코드를 파라메터로 넘기면 그 조직을 포함한 하위조직 전부를 반환하는 테이블 함수입니다.

Top Down함수는 WITH CTE(Common Table Expression)의 재귀공통 테이블식을 사용하여 구현할 수 있습니다.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- 특정조직의 하위조직 찾아오기
ALTER FUNCTION [dbo].[GetTopDown](@DeptCd char(5))
RETURNS @rTable TABLE (
	[DeptCd] nvarchar(50),
	[DeptName] nvarchar(50),
	[ParentDeptCd] nvarchar(50),
	[Depth] int
) 
AS
BEGIN
	WITH OrgTree(DeptCd, DeptNAME, ParentDeptCd, Depth) AS (
		SELECT DeptCd, DeptNAME, ParentDeptCd, 0 AS Depth
		FROM Company
		WHERE DeptCd = @DeptCd

		UNION ALL

		SELECT COM.DeptCd, COM.DeptNAME, COM.ParentDeptCd, ORG.Depth + 1
		FROM Company AS COM 
		INNER JOIN OrgTree AS ORG 
			ON COM.ParentDeptCd = ORG.DeptCd
	)
	INSERT @rTable
	SELECT DeptCd, DeptNAME, ParentDeptCd, Depth
	FROM OrgTree
	OPTION (MAXRECURSION 100);  

    RETURN 
END

결과조회

자 그럼 이렇게 만든 함수가 잘 작동하는지 실행해 보도록 하겠습니다.

--상황1. 사성전자(Root) 하위 조직 모두 가져오기
SELECT * FROM dbo.GetTopDown('10000')

--상황2. 경영지원본부 하위 조직 모두 가져오기
SELECT * FROM dbo.GetTopDown('11000')

--상황3. 영업본부 하위 조직정보 모두 가져오기
SELECT * FROM dbo.GetTopDown('12000')

쿼리결과1

탑다운 쿼리결과

쿼리결과2

탑다운 쿼리결과

쿼리결과3

탑다운 쿼리결과

프로젝트의 성공을 기원합니다.

2018/12/07 - [MS-SQL] - 트리구조(Tree Structure)란?

2018/11/29 - [분류 전체보기] - MSSQL WITH CTE

2018/12/03 - [MS-SQL] - MSSQL 특정노드의 상위노드 전체 가져오기(Bottom Up)