(ORACLE) Global Temporary Table과 계층 구조 만들기

MSSQL을 Oracle로 옮기는 작업 중 메뉴에 대한 계층 쿼리를 수정해야 하는 상황이 발생했다.

기존에 MSSQL에서는 테이블 변수를 생성해서 전체 테이블을 담고, MaxLevel을 구한 후, ParentId와 본인의 ID를 통하여 반복문을 사용하여 계층 구조를 만들거나, WITH절을 사용하여 계층 형태로 구성을 하였는데 Oracle에서는 위의 방법이 생각처럼 쉽지가 않았다.

---

Oracle의 Global Temporary Table

오라클에서는 MSSQL의 테이블 변수처럼 사용할 수 있는 Global Temporary Table(편의상 GTT라고 부르겠다)이란 것이 있다.

GTT는 쉽게 설명하면 세션 혹은 트랜잭션 수명 주기와 관련이 있는 임시 테이블이라고 생각하면 되겠다.

GTT를 만드는것에는 2가지 방법이 있는데, Session 단위의 DATA를 유지하는 형태와 TransAction 단위의 DATA를 유지하는 형태 2가지가 존재한다.

1. Transaction 단위의 GTT

CREATE GLOBAL TEMPORARY TABLE TEST(
	TEST_NO NUMBER(20)
)
ON COMMIT DELETE ROWS;

transaction 단위의 GTT를 생성하는 PL/SQL문은 다음과 같다.

위와 같이 테이블을 생성한 후 값을 넣으면 해당 Transaction 단위로 데이터의 조회가 가능하다.

쉽게 설명하면 위 테이블에 데이터를 인서트한 후 Select를 하면 정상적으로 조회가 된다.

하지만 COMMIT을 하게 되면 Transaction이 갈라지기 때문에 데이터를 넣어두고 COMMIT을 하는 순간 데이터가 소실되는 것을 확인 할 수 있다.

 

2. Session 단위의 GTT

CREATE GLOBAL TEMPORARY TABLE TEST(
	TEST_NO NUMBER(20)
)
ON COMMIT PRESERVE ROWS;

Session 단위의 GTT를 만드는 PL/SQL 구문이다.

잘 보면 뒤의 ON 절만 달라진 것을 확인 할 수 있다.

이렇게 테이블을 생성하면 Session이 열린 순간부터 닫힐때 까지 해당 데이터를 조회할 수 있고 Session이 변경되는 순간 테이블의 모든 데이터가 소실된다.

즉, COMMIT을 해도 데이터를 조회할 수 있지만, Session이 변경되어 다른 유저가 들어오게 된다면 해당 데이터를 조회할 수 없다.

---

계층형 구조 생성은?

그럼 그냥 GTT에 같은 로직으로 데이터 담으면 되는거 아니냐고 물을 수 있는데, 또 마냥 그런것도 아니였다.

Migration 툴로 프로시저를 옮겼기 떄문에, GTT가 자동으로 테이블을 만든 상태였는데, 문제가 된 부분은 프로시저 상에서 해당 테이블에 Insert 로직이 존재하는데 GTT가 TransAction 단위로 만들어진 것이였다.

따라서 COMMIT을 기본적으로 사용할 수 없는 구조였는데 (Transaction 단위이므로 COMMIT을 하는 순간 데이터가 소실된다) COMMIT 사용하지 않으니 .NET 코드 단에서 인출 시퀀스 에러가 계속 발생하는게 문제였다.

그래서 GTT를 Session 단위로 바꿔 버리고 쓸까 하다가 어차피 단순히 계층형 구조로 메뉴 호출하는건데 그냥 바로 할 수 있는 방법이 있나 해서 찾아보다가 오라클에서는 계층구조를 굉장히 간단하게 만들 수 있다는 것을 찾아냈다.

 

오라클에는 START WITH 와 CONNECT BY라는 구문이 존재하는데, 이둘 을 사용하면 간단하게 계층형 쿼리를 생성해 낼 수 있다.

CREATE TABLE Employee (
    EmployeeID NUMBER PRIMARY KEY,
    Name VARCHAR2(50),
    ManagerID NUMBER
);

INSERT INTO Employee (EmployeeID, Name, ManagerID)
VALUES (1, 'John', NULL);

INSERT INTO Employee (EmployeeID, Name, ManagerID)
VALUES (2, 'Alice', 1);

INSERT INTO Employee (EmployeeID, Name, ManagerID)
VALUES (3, 'Bob', 1);

INSERT INTO Employee (EmployeeID, Name, ManagerID)
VALUES (4, 'Eve', 2);

INSERT INTO Employee (EmployeeID, Name, ManagerID)
VALUES (5, 'Charlie', 2);

예를 들어 위와 같이 테이블에 데이터가 들어있다고 생각해보자.

각 데이터는 EmployeeId와 ManagerID값으로 연결되어 있다.

그렇다면 우리는 START WITH 와 CONNECT BY 구문을 통하여 간단하게 계층 구조를 만들어 낼 수 있다.

-- 직원의 계층적 구조 조회
SELECT EmployeeID, Name, LEVEL
FROM Employee
START WITH [시작할 행의 조건]
CONNECT BY PRIOR [자식컬럼] = [부모컬럼]

이전에 프로시저로 길~게 만들었던 것을 단 4줄로 처리 할 수 있는 것이다.

예를 들어 EmployeeId를 기준으로 MagaerId가 부모ID라고 생각해 보자.

그러면 최상위의 데이터에는 ManagerID가 비어있을 것이다.

또한, PRIOR라는 구문이 있는데 이것은 찾아갈 컬럼의 위치를 결정한다.

예를 들어 다음과 같이 PL/SQL을 완성했다고 생각해보자.

-- 직원의 계층적 구조 조회
SELECT EmployeeID, Name, LEVEL
FROM Employee
START WITH ManagerID IS NULL
CONNECT BY PRIOR EmployeeID = ManagerID;

여기서 시작 조건은 ManagerID가 Null인 것을 알 수 있다.

그럼 오라클은 ManagerId가 Null인 데이터를 찾아 제일 상단에 둘것이다.

그것을 기준으로 계층형 구조를 생성하는데 Prior가 EmployeeId이 이므로 EmployeeId를 기준으로 ManagerId를 검색하게 된다.

따라서 정방향의 계층 구조를 생성할 수 있는 것.

그렇다면 반대로 PRIOR를 ManagerID쪽에 붙이면 어떻게 될까?

ManagerID는 부모 컬럼이므로 부모가 자식을 찾아가게 되어 역방향의 계층 구조를 생성하게 된다.

결과적으로 위의 쿼리를 실행한다면 다음과 같은 결과를 얻을 수 있을 것이다.

 

EMPLOYEEID | NAME     | LEVEL
-----------|----------|------
1          | John     | 1
2          | Alice    | 2
4          | Eve      | 3
5          | Charlie  | 3
3          | Bob      | 2

 

이제 남은 것은 프로시저 내에서 조건을 맞춰주고 해당 데이터 셋을 cursor에 담아 던져주기만 하면 되는것.

실제로 변경해보니 잘 작동하는 것을 볼 수 있었다.