[SQL] 사용자 정의함수로 BOM 구현

SQL 2005 이상에서는 CTE를 제공 해주기 때문에 재귀적으로 BOM을 풀어 낼 수가 있으나

SQL 2000 에서는 CTE 문법을 사용할 수 없으므로 사용자 정의함수로 동일하게 BOM 테이블을 조회 할 수 있다.

CREATE FUNCTION ufn_GetBOM (
    @Parent AS VARCHAR(20)
)
RETURNS @BOM table
(
    Child      VARCHAR(20),
    Parent    VARCHAR(20),
    Qty        FLOAT(8),
    LevelNo   INT,
    OrdNo   VARCHAR(2000) 
)
AS
     BEGIN
          DECLARE @LevelNo AS int
          SET @LevelNo = 0

          INSERT INTO @BOM
          SELECT Child, Parent, Qty, @LevelNo, 
          CAST(SUBSTRING(Child + '00000000000000000000', 1, 20) AS VARCHAR(2000)) as OrdNo
          FROM BOM
          WHERE Parent = @Parent
          
          WHILE @@ROWCOUNT > 0
          BEGIN
               SET @LevelNo = @LevelNo + 1

               INSERT INTO @BOM
               SELECT a.Child, a.Parent, a.Qty, @LevelNo, 
               CAST(b.OrdNo + SUBSTRING(a.Child + '00000000000000000000', 1, 20) AS VARCHAR(2000))
               FROM BOM a 
                     JOIN @BOM b
                         ON a.Parent = b.Child AND b.LevelNo = @LevelNo - 1
          END

          RETURN
     END
GO

위와 같이 사용자 정의함수를 정의한 후, 사용시에는 다음과 같이 최상위 품목번호를 넘겨 주면

하위 품목이 모두 조회 된다.

SELECT * FROM dbo.ufn_GetBOM('A2006010' ) 
ORDER BY ordno 

이를 클라이언트에서 트리 형태로 보여 주려면 다음과 같이 한다.

다음은 델파이에서 TreeView에 가져온 BOM 구조를 보여 주는 함수를 만들어 보았다.

procedure TFrom1.ShowBOM;
var
  CurrentNode: TTreeNode;
function makeNodeText(): String;
begin
  with DM.qryBOM do
    Result := FieldByName('Child').AsString + '(' + FieldByName('Name').AsString + '/' +
              FieldByName('Spec').AsString + '/' + FieldByName('Company').AsString + '/' +
              FieldByName('MatType').AsString + '),' + FieldByName('Qty').AsString;
end;

begin
  TreeView1.Items.Clear;
  CurrentNode := nil;

  with DM.qryBOM do begin
    while not EOF do begin
      if CurrentNode = nil then
        CurrentNode := TreeView1.Items.Add(ATreeView.Selected, makeNodeText())
      else begin
        if FieldByName('LevelNo').AsInteger > CurrentNode.Level then
          CurrentNode := TreeView1.Items.AddChild(CurrentNode, makeNodeText())
        else begin
          while FieldByName('LevelNo').AsInteger < CurrentNode.Level do begin
            if (CurrentNode.Parent <> nil) then
              CurrentNode := CurrentNode.Parent
            else
              break;
          end;

          CurrentNode := TreeView1.Items.Add(CurrentNode, makeNodeText())
        end;
      end;

      Next;
    end;
  end;

end;