/**********************************************************************************************
-- Title : [2k5] BOM 구조 구현
-- Reference : dBRang.com
-- Key word : bom, recursive, cursor
**********************************************************************************************/
/********************
-- Base 테이블 생성
********************/
use TempDB
if object_id('bom') is not null
drop table bom
go
--Test용 bom 테이블 작성과 데이터 입력
create table bom
(Item_cd char(2) not null constraint pk_bom primary key clustered
,pItem_cd char(2) null
,Item_Type tinyint not null
, constraint fk_bom_bom foreign key
(pItem_cd) references bom (Item_cd)
)
insert bom values('A',null,1)
insert bom values('A1','A',0)
insert bom values('A2','A',0)
insert bom values('A3','A',0)
insert bom values('AA','A1',0)
insert bom values('AB','A1',0)
insert bom values('AI','A2',0)
insert bom values('AJ','A2',0)
insert bom values('AM','A3',0)
insert bom values('AN','A3',0)
insert bom values('AQ','A1',0)
insert bom values('AW','AQ',0)
insert bom values('AY','AW',0)
insert bom values('AU','AY',0)
insert bom values('AO','A3',0)
insert bom values('B',null,1)
insert bom values('C',null,1)
insert bom values('C1','C',0)
insert bom values('C2','C',0)
insert bom values('B1','B',0)
insert bom values('B2','B',0)
insert bom values('B3','B',0)
insert bom values('B4','B2',0)
insert bom values('B5','B1',0)
insert bom values('B6','B4',0)
insert bom values('B7','B4',0)
insert bom values('B8','B7',0)
insert bom values('B9','B7',0)
insert bom values('C3','C1',0)
insert bom values('C4','C1',0)
insert bom values('C5','C4',0)
insert bom values('C6','C5',0)
select * from bom;
/************************************
-- bom트리구조를 단순 Cursor로 작성
************************************/
if object_id('up_Getbom_useCursor') is not null
drop procedure up_Getbom_useCursor
go
create procedure up_Getbom_useCursor
@Item_cd char(2)
as
/* 레벨를 저장하기위한 테이블을 생성한다.*/
set nocount on
declare @pItem_cd char(2)
,@Item_Type tinyint
,@Level tinyint
create table #bom
(Seq int IDENTITY
,Item_cd char(2)
,pItem_cd char(2)
,Item_Type tinyint
,Level tinyint
)
set @level = 0
insert into #bom
select Item_cd, pItem_cd, Item_Type, @Level
from bom
where Item_cd = @Item_cd
--Level 1 시작
declare bom_1 cursor fast_forward
for
select Item_cd, pItem_cd, Item_Type
from bom
where pItem_cd = @Item_cd
open bom_1
fetch next from bom_1 into @Item_cd, @pItem_cd, @Item_Type
while @@fetch_status = 0
begin
set @Level = 1
insert into #bom values( @Item_cd, @pItem_cd, @Item_Type, @Level)
--Level 2 시작
declare bom_2 cursor fast_forward
for
select Item_cd, pItem_cd, Item_Type
from bom
where pItem_cd = @Item_cd
open bom_2
fetch next from bom_2 into @Item_cd, @pItem_cd, @Item_Type
while @@fetch_status = 0
begin
set @Level = 2
insert into #bom values( @Item_cd, @pItem_cd, @Item_Type, @Level)
--Level 3 시작
declare bom_3 cursor fast_forward
for
select Item_cd, pItem_cd, Item_Type
from bom
where pItem_cd = @Item_cd
open bom_3
fetch next from bom_3 into @Item_cd, @pItem_cd, @Item_Type
while @@fetch_status = 0
begin
set @Level = 2
insert into #bom values( @Item_cd, @pItem_cd, @Item_Type, @Level)
-- Level 4 이하부터는 여기에 계속 기록...
fetch next from bom_3 into @Item_cd, @pItem_cd, @Item_Type
end
close bom_3
deallocate bom_3
--Level 3 종료
fetch next from bom_2 into @Item_cd, @pItem_cd, @Item_Type
end
close bom_2
deallocate bom_2
--Level 2 종료
fetch next from bom_1 into @Item_cd, @pItem_cd, @Item_Type
end
close bom_1
deallocate bom_1
--Level 1 종료
select * from #bom order by Seq
go
exec up_Getbom_useCursor 'a'
go
/*******************************************************************
-- bom트리구조 Cursor를 이용하여 작성
-- Cursor 기본이 Global이기 때문에 충돌이 발생하므로 Local로 해야함
-- @Level 에 대해 @@NestLevel 함수로 대체
*******************************************************************/
if object_id('up_Getbom_useCursorRecursive') is not null
drop procedure up_Getbom_useCursorRecursive
go
create procedure up_Getbom_useCursorRecursive
@Item_cd char(2)
as
set nocount on
declare @pItem_cd char(2)
,@Item_Type tinyint
,@Level tinyint
create table #bom
(Seq int IDENTITY
,Item_cd char(2)
,pItem_cd char(2)
,Item_Type tinyint
,Level tinyint
)
select @level = 0
insert into #bom
select Item_cd, pItem_cd, Item_Type, @Level
from bom
where Item_cd = @Item_cd
exec up_Getbom_useCursorRecursive_Loop @Item_cd
select * from #bom order by Seq
go
if object_id('up_Getbom_useCursorRecursive_Loop') is not null
drop procedure up_Getbom_useCursorRecursive_Loop
go
create procedure up_Getbom_useCursorRecursive_Loop
@Item_cd char(2)
as
set nocount on
declare @pItem_cd char(2)
,@Item_Type smallint
-- ,@Level smallint
declare bom cursor local fast_forward
for
select Item_cd, pItem_cd, Item_Type
from bom
where pItem_cd = @Item_cd
open bom
fetch next from bom into @Item_cd, @pItem_cd, @Item_Type
while @@fetch_status = 0
begin
--set @Level = 1 @@NestLevel로 대체
insert into #bom values( @Item_cd, @pItem_cd, @Item_Type, @@NestLevel - 1)
if exists(select Item_cd, pItem_cd, Item_Type
from bom
where pItem_cd = @Item_cd)
exec up_Getbom_useCursorRecursive_Loop @Item_cd
fetch next from bom into @Item_cd, @pItem_cd, @Item_Type
end
close bom
deallocate bom
go
exec up_Getbom_useCursorRecursive 'A'
go
/******************************************
-- bom트리구조를 Level 별 set Base로 처리
******************************************/
if object_id('up_Getbom_useset') is not null
drop procedure up_Getbom_useset
go
create proc up_Getbom_useset
@Item_cd as char(2)
as
set nocount on
create table #tree
(Item_cd char(2) not null
,pItem_cd char(2) null
,Item_Type tinyint null
,Level tinyint not null
,Hierarchy_cd varchar(250)
)
declare @Level as int
set @Level = 0
insert into #tree
select Item_cd, pItem_cd, Item_Type, @Level, Item_cd
from bom where Item_cd = @Item_cd
-- 임시 테이블에 삽입되는 행이 없을 때까지 반복수행
while @@rowcount> 0
begin
-- 적용될 레벨로 증가시키고 아래에서 지금 적용된 레벨(@Level-1)을 조인에 사용
set @Level = @Level + 1
insert into #tree
select B.Item_cd, B.pItem_cd, B.Item_Type, @Level, T.Hierarchy_cd + '.' + B.Item_cd
from bom as B JOIN #tree as T
on B.pItem_cd = T.Item_cd AND T.Level = @Level - 1
end
select * from #tree order by Hierarchy_cd
go
exec up_Getbom_useset 'A'
go
/********************************************************************************
-- bom트리구조를 Level 별 set Base로 Recursive 처리
*********************************************************************************/
if object_id('up_Getbom_usesetRecursive_loop') is not null
drop proc up_Getbom_usesetRecursive
go
create proc up_Getbom_usesetRecursive_loop
@level as INT
as
if EXisTS( select * from bom as B JOIN #tree as T
on B.pItem_cd = T.Item_cd AND T.level = @level)
begin
insert into #tree
select B.*, @level + 1, T.Hierarchy_cd + '.' + B.Item_cd
from bom as B JOIN #tree as T
on B.pItem_cd = T.Item_cd AND T.level = @level
set @level = @level + 1
exec up_Getbom_usesetRecursive_loop @level
end
go
if object_id('up_Getbom_usesetRecursive') is not null
drop proc up_Getbom_usesetRecursive
go
create proc up_Getbom_usesetRecursive
@item_cd char(2)
as
set nocount on
create table #tree
(Item_cd char(2) not null
,pItem_cd char(2) null
,Item_Type tinyint null
,level tinyint null
,Hierarchy_cd varchar(250)
)
insert into #tree select *, 0, Item_cd from bom where Item_cd =@item_cd
exec up_Getbom_usesetRecursive_loop 0
select * from #tree order by Hierarchy_cd
go
exec up_Getbom_usesetRecursive 'a'
/********************************************************************************
-- 사용자 정의 함수 처리
*********************************************************************************/
create function ufn_GetbomFuntion
(@Item_cd char(2))
returns @tree table
(Item_cd char(2) not null
,pItem_cd char(2) null
,Item_Type tinyint null
,Level tinyint not null
,Hierarchy_cd varchar(250)
)
as
begin
declare @Level as int
set @Level = 0
insert into @tree
select Item_cd, pItem_cd, Item_Type, @Level, Item_cd
from bom where Item_cd = @Item_cd
-- 임시 테이블에 삽입되는 행이 없을 때까지 반복수행
while @@rowcount> 0
begin
-- 적용될 레벨로 증가시키고 아래에서 지금 적용된 레벨(@Level-1)을 조인에 사용
set @Level = @Level + 1
insert into @tree
select B.Item_cd, B.pItem_cd, B.Item_Type, @Level, T.Hierarchy_cd + '.' + B.Item_cd
from bom as B JOIN @tree as T
on B.pItem_cd = T.Item_cd AND T.Level = @Level - 1
end
return
end
go
select * from ufn_GetbomFuntion('A') order by Hierarchy_cd
/********************************************************************************
-- 최종 성능 비교
*********************************************************************************/
checkpoint
DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS
-- 재귀커서 처리
exec up_Getbom_useCursorRecursive 'A'
go
-- SET BASE 처리
exec up_Getbom_useset 'A'
go
-- SET BASE 재귀처리
exec up_Getbom_usesetRecursive 'a'
go
-- 함수 처리
select * from ufn_GetbomFuntion('A') order by Hierarchy_cd
go
/*
1) exec up_Getbom_useCursorRecursive 'A'(재귀커서 처리)
SQL 쿼리 분석기 sa 20 410 0 220
2) exec up_Getbom_useset 'A'(Set-Base 처리)
SQL 쿼리 분석기 sa 50 745 0 210
3) exec up_Getbom_usesetRecursive 'a'(Set-Base 재귀 처리)
SQL 쿼리 분석기 sa 70 803 0 320
4) select * from ufn_GetbomFuntion('A') order by Hierarchy_cd(함수 처리)
SQL 쿼리 분석기 sa 30 387 0 200
*/
/********************************************************************************
-- SQL 2005의 CTE 활용 처리
*********************************************************************************/
with cte_bom (item_cd, pitem_cd, item_type, level)
as
( select item_cd,pitem_cd,item_type, 0
from bom
where item_cd = 'A'
union all
select b.item_cd, b.pitem_cd, b.item_type, c.level+1
from bom b inner join cte_bom c
on b.pitem_cd = c.item_cd
)
select * from cte_bom order by level;
[2k5] BOM 구조 구현
2008. 9. 6. 23:48
반응형
반응형