보통 부서의 상위 관계추적을 할 때 사용한다.
상위 하위 관계가 존재하는 트리구조와 같은 형태에서 필요한 구조라고 할까?
TB_DEPT
| 부서코드 (dept_code) | 부서명(dept_name) | 상위부서코드(parent_dept_code) |
| A | 무지좋은 회사 | |
| B | 그저그런 부서 | A |
| C | 최악의 팀 | B |
TB_USER
| 이름 (NAME) | 부서코드 (DEPT_CODE) |
| 홍길동 | C |
| 둘리 | B |
| 사장님 | A |
와 같은 테이블이 있다고 했을때,
하위부서코드 하나로 관계된 상위 부서코드까지 뽑아내는것이 필요하다.
즉, C라는 부서코드를 넣으면 C-B-A 같은 값을 뽑아내야 하는 상황이라는 것...
즉,
SELECT NAME, DEPT_CODE, dbo.FN_TAR_DEPT(DEPT_CODE,'','-') AS FULL_DEPTCODE FROM TB_USER
의 결과는 요렇게
| NAME | DEPT_CODE | FULL_DEPTCODE |
| 홍길동 | C | C-B-A |
| 둘리 | B | B-A |
| 사장님 | A | A |
/**********************************************
* 하위 코드와 상위코드값으로 전체 코드값을 가져옴
* arg -----------------------------------------
* @pkey == 내 자신의 키
* @outputtmp == 초반에 시작할 값 (재귀에서는 값전달용)
* @ del == 구분자
*
* output -------------------------------------
* 예 : FN_TAR_DEPT('C', '', '-')
* 결과 C-B-A
*
* MAKE BY 정민철 (MSSQL용)
***********************************************/
CREATE FUNCTION FN_TAR_DEPT(@pkey varchar(50), @outputtmp varchar(1000), @del varchar(2)) RETURNS varchar(1000) AS
BEGIN
--상위 코드값을 가져옴
DECLARE curTab CURSOR FOR
SELECT top 1 tb_dept.parent_dept_code FROM tb_dept WHERE deptcode = @pkey
OPEN curTab
DECLARE @output varchar(1000)
DECLARE @par_dept varchar(50)
SET @output = ''
SET @par_dept = ''
FETCH NEXT FROM curTab INTO @par_dept
IF @@FETCH_STATUS = 0
BEGIN
IF @pkey = '' -- 재귀의 종료조건(ROOT의 부모코드는 '' 임)
BEGIN
return @outputtmp
END
ELSE
BEGIN -- 값있으면 상위코드를 추적(재귀)
SET @output = dbo.FN_TAR_DEPT(@par_dept,@outputtmp,@del)
END
-- [시작] 결과값을 묶어줌 (TAR) --
IF @output = ''
BEGIN
SET @output = @pkey
END
ELSE
BEGIN
SET @output = @output + @del + @pkey
END
-- [끝] 결과값을 묶어줌 (TAR) --
END
CLOSE curTab
DEALLOCATE curTab
return @output
END
* 하위 코드와 상위코드값으로 전체 코드값을 가져옴
* arg -----------------------------------------
* @pkey == 내 자신의 키
* @outputtmp == 초반에 시작할 값 (재귀에서는 값전달용)
* @ del == 구분자
*
* output -------------------------------------
* 예 : FN_TAR_DEPT('C', '', '-')
* 결과 C-B-A
*
* MAKE BY 정민철 (MSSQL용)
***********************************************/
CREATE FUNCTION FN_TAR_DEPT(@pkey varchar(50), @outputtmp varchar(1000), @del varchar(2)) RETURNS varchar(1000) AS
BEGIN
--상위 코드값을 가져옴
DECLARE curTab CURSOR FOR
SELECT top 1 tb_dept.parent_dept_code FROM tb_dept WHERE deptcode = @pkey
OPEN curTab
DECLARE @output varchar(1000)
DECLARE @par_dept varchar(50)
SET @output = ''
SET @par_dept = ''
FETCH NEXT FROM curTab INTO @par_dept
IF @@FETCH_STATUS = 0
BEGIN
IF @pkey = '' -- 재귀의 종료조건(ROOT의 부모코드는 '' 임)
BEGIN
return @outputtmp
END
ELSE
BEGIN -- 값있으면 상위코드를 추적(재귀)
SET @output = dbo.FN_TAR_DEPT(@par_dept,@outputtmp,@del)
END
-- [시작] 결과값을 묶어줌 (TAR) --
IF @output = ''
BEGIN
SET @output = @pkey
END
ELSE
BEGIN
SET @output = @output + @del + @pkey
END
-- [끝] 결과값을 묶어줌 (TAR) --
END
CLOSE curTab
DEALLOCATE curTab
return @output
END