반응형

/**********************************************************************************************
-- 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;

반응형

+ Recent posts