결론부터 말하자면 트리메뉴에서 Depth별 검색 기능을 넣고 싶다면 불가능하다.
단, 트리의 Depth가 정해저 있다면 (예 : 대, 중, 소) 가능은 하다.
가능한 방법은 하기와 같다.
테이블을 생성한다.
--------------------------------------------------------
-- Spare Parts 대중소 관련 테이블
--------------------------------------------------------
CREATE TABLE "S_SPAREPART_CATEGORY"
(
"SPAREPART_CATEGORY_ID" VARCHAR2(20 BYTE),
"TOP_SPAREPART_CATEGORY_ID" VARCHAR2(20 BYTE),
"PARENT_ID" VARCHAR2(20 BYTE),
"SPAREPART_CATEGORY_LEVEL" NUMBER(3,0),
"SPAREPART_CATEGORY_ORDER" NUMBER(3,0),
"SPAREPART_CATEGORY_NAME" VARCHAR2(500 BYTE),
"SPAREPART_CATEGORY_ENG_NAME" VARCHAR2(500 BYTE),
"REMARK" VARCHAR2(500 BYTE),
"STATUS" CHAR(1 BYTE),
"REG_ID" VARCHAR2(100 BYTE),
"REG_DATE" DATE,
"MOD_ID" VARCHAR2(100 BYTE),
"MOD_DATE" DATE
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS" ;
comment ON TABLE S_SPAREPART_CATEGORY IS 'Spare Parts 분류 관련 테이블' ;
comment ON COLUMN S_SPAREPART_CATEGORY.SPAREPART_CATEGORY_ID IS '키 ID';
comment ON COLUMN S_SPAREPART_CATEGORY.TOP_SPAREPART_CATEGORY_ID IS '최상위 키 ID';
comment ON COLUMN S_SPAREPART_CATEGORY.PARENT_ID IS '상위 키 ID';
comment ON COLUMN S_SPAREPART_CATEGORY.SPAREPART_CATEGORY_LEVEL IS '대중소 분류 레벨';
comment ON COLUMN S_SPAREPART_CATEGORY.SPAREPART_CATEGORY_ORDER IS '순서';
comment ON COLUMN S_SPAREPART_CATEGORY.SPAREPART_CATEGORY_NAME IS '분류 한글명';
comment ON COLUMN S_SPAREPART_CATEGORY.SPAREPART_CATEGORY_ENG_NAME IS '분류 영문명';
comment ON COLUMN S_SPAREPART_CATEGORY.REMARK IS '비고';
comment ON COLUMN S_SPAREPART_CATEGORY.STATUS IS '사용여부';
comment ON COLUMN S_SPAREPART_CATEGORY.REG_ID IS '등록자';
comment ON COLUMN S_SPAREPART_CATEGORY.REG_DATE IS '등록일';
comment ON COLUMN S_SPAREPART_CATEGORY.MOD_ID IS '수정자';
comment ON COLUMN S_SPAREPART_CATEGORY.MOD_DATE IS '수정일';
-- DROP TABLE S_SPAREPART_CATEGORY;
COMMIT;
검색 쿼리는 하기와 같다.
SELECT
SPAREPART_CATEGORY_ID As "sparePartCategoryId", /* 키 */
NVL(TOP_SPAREPART_CATEGORY_ID, '0') As "topSparePartCategoryId", /* 최상위 키 */
NVL(PARENT_ID, '0') As "parentId", /* 부모 키 */
SPAREPART_CATEGORY_LEVEL As "sparePartCategoryLevel", /* Depth */
SPAREPART_CATEGORY_ORDER As "sparePartCategoryOrder", /* 순서 */
SPAREPART_CATEGORY_NAME As "sparePartCategoryName", /* 대중소 한글명 */
SPAREPART_CATEGORY_ENG_NAME, /* 대중소 영문명 */
LPAD(' ', 2*LEVEL-1)||SPAREPART_CATEGORY_NAME As "sparePartCategoryLevelName", /* 대중소 한글 레벨명 */
REMARK As "remark", /* 비고 */
STATUS As "status", /* 상태(Y:사용, N:미사용) */
REG_ID As "regId", /* 등록자ID */
MOD_ID As "modId", /* 수정자ID */
REG_DATE As "regDate", /* 등록일자 */
MOD_DATE As "modDate" /* 수정일자 */
FROM S_SPAREPART_CATEGORY
WHERE 1 = 1
/* 중분류 있는 경우 조건 */
AND SPAREPART_CATEGORY_ID IN (
SELECT
A.SPAREPART_CATEGORY_ID
FROM S_SPAREPART_CATEGORY A
INNER JOIN
(
/* 대분류 */
SELECT
SPAREPART_CATEGORY_ID
FROM S_SPAREPART_CATEGORY
WHERE SPAREPART_CATEGORY_ID IN (SELECT PARENT_ID FROM S_SPAREPART_CATEGORY WHERE SPAREPART_CATEGORY_NAME LIKE '%하위%' AND SPAREPART_CATEGORY_LEVEL = 2)
UNION
/* 중분류 */
SELECT SPAREPART_CATEGORY_ID FROM S_SPAREPART_CATEGORY WHERE SPAREPART_CATEGORY_NAME LIKE '%하위%' AND SPAREPART_CATEGORY_LEVEL = 2
UNION
/* 소분류 */
SELECT
SPAREPART_CATEGORY_ID
FROM S_SPAREPART_CATEGORY
WHERE PARENT_ID IN (SELECT SPAREPART_CATEGORY_ID FROM S_SPAREPART_CATEGORY WHERE SPAREPART_CATEGORY_NAME LIKE '%하위%' AND SPAREPART_CATEGORY_LEVEL = 2)
) B ON A.SPAREPART_CATEGORY_ID = B.SPAREPART_CATEGORY_ID
)
/* 소분류 있는 경우 조건 */
AND SPAREPART_CATEGORY_ID IN (
SELECT
A.SPAREPART_CATEGORY_ID
FROM S_SPAREPART_CATEGORY A
INNER JOIN
(
/* 대분류 */
SELECT
SPAREPART_CATEGORY_ID
FROM S_SPAREPART_CATEGORY
WHERE SPAREPART_CATEGORY_ID IN (
SELECT
PARENT_ID
FROM S_SPAREPART_CATEGORY
WHERE SPAREPART_CATEGORY_ID IN (SELECT PARENT_ID FROM S_SPAREPART_CATEGORY WHERE SPAREPART_CATEGORY_NAME LIKE '%하위%' AND SPAREPART_CATEGORY_LEVEL = '3')
)
UNION
/* 중분류 */
SELECT
SPAREPART_CATEGORY_ID
FROM S_SPAREPART_CATEGORY
WHERE SPAREPART_CATEGORY_ID IN (SELECT PARENT_ID FROM S_SPAREPART_CATEGORY WHERE SPAREPART_CATEGORY_NAME LIKE '%하위%' AND SPAREPART_CATEGORY_LEVEL = '3')
UNION
/* 소분류 */
SELECT SPAREPART_CATEGORY_ID FROM S_SPAREPART_CATEGORY WHERE SPAREPART_CATEGORY_NAME LIKE '%하위%' AND SPAREPART_CATEGORY_LEVEL = '3'
) B ON A.SPAREPART_CATEGORY_ID = B.SPAREPART_CATEGORY_ID
)
/* 대분류 있는 경우 조건 */
START WITH SPAREPART_CATEGORY_ID IN (
SELECT
SPAREPART_CATEGORY_ID
FROM S_SPAREPART_CATEGORY
WHERE SPAREPART_CATEGORY_NAME LIKE '%엔진%' AND SPAREPART_CATEGORY_LEVEL = '1'
)
CONNECT BY PRIOR SPAREPART_CATEGORY_ID = PARENT_ID
ORDER SIBLINGS BY SPAREPART_CATEGORY_ORDER;
여기서 MyBatis에서의 사용은 하기와 같다
SELECT
SPAREPART_CATEGORY_ID As "sparePartCategoryId", /* 키 */
NVL(TOP_SPAREPART_CATEGORY_ID, '0') As "topSparePartCategoryId", /* 최상위 키 */
NVL(PARENT_ID, '0') As "parentId", /* 부모 키 */
SPAREPART_CATEGORY_LEVEL As "sparePartCategoryLevel", /* Depth */
SPAREPART_CATEGORY_ORDER As "sparePartCategoryOrder", /* 순서 */
<if test="language =='' or language =='ko'">
SPAREPART_CATEGORY_NAME As "sparePartCategoryName", /* 대중소 한글명 */
LPAD(' ', 2*LEVEL-1)||SPAREPART_CATEGORY_NAME As "sparePartCategoryLevelName", /* 대중소 한글 레벨명 */
</if>
<if test="language == 'en'">
SPAREPART_CATEGORY_ENG_NAME As "sparePartCategoryName", /* 대중소 영문명 */
LPAD(' ', 2*LEVEL-1)||SPAREPART_CATEGORY_ENG_NAME As "sparePartCategoryLevelName", /* 대중소 영문 레벨명 */
</if>
REMARK As "remark", /* 비고 */
STATUS As "status", /* 상태(Y:사용, N:미사용) */
REG_ID As "regId", /* 등록자ID */
MOD_ID As "modId", /* 수정자ID */
REG_DATE As "regDate", /* 등록일자 */
MOD_DATE As "modDate" /* 수정일자 */
FROM S_SPAREPART_CATEGORY
WHERE 1 = 1
<if test="mediumName != ''">
/* 중분류 있는 경우 조건 */
AND SPAREPART_CATEGORY_ID IN (
SELECT
A.SPAREPART_CATEGORY_ID
FROM S_SPAREPART_CATEGORY A
INNER JOIN
(
/* 대분류 */
SELECT
SPAREPART_CATEGORY_ID
FROM S_SPAREPART_CATEGORY
WHERE SPAREPART_CATEGORY_ID IN (SELECT PARENT_ID FROM S_SPAREPART_CATEGORY WHERE UPPER(<if test="language =='' or language =='ko'">SPAREPART_CATEGORY_NAME</if><if test="language == 'en'">SPAREPART_CATEGORY_ENG_NAME</if>) LIKE UPPER('%'||#{mediumName}||'%') AND SPAREPART_CATEGORY_LEVEL = 2)
UNION
/* 중분류 */
SELECT SPAREPART_CATEGORY_ID FROM S_SPAREPART_CATEGORY WHERE UPPER(<if test="language =='' or language =='ko'">SPAREPART_CATEGORY_NAME</if><if test="language == 'en'">SPAREPART_CATEGORY_ENG_NAME</if>) LIKE UPPER('%'||#{mediumName}||'%') AND SPAREPART_CATEGORY_LEVEL = 2
UNION
/* 소분류 */
SELECT
SPAREPART_CATEGORY_ID
FROM S_SPAREPART_CATEGORY
WHERE PARENT_ID IN (SELECT SPAREPART_CATEGORY_ID FROM S_SPAREPART_CATEGORY WHERE UPPER(<if test="language =='' or language =='ko'">SPAREPART_CATEGORY_NAME</if><if test="language == 'en'">SPAREPART_CATEGORY_ENG_NAME</if>) LIKE UPPER('%'||#{mediumName}||'%') AND SPAREPART_CATEGORY_LEVEL = 2)
) B ON A.SPAREPART_CATEGORY_ID = B.SPAREPART_CATEGORY_ID
)
</if>
<if test="SmallName != ''">
/* 소분류 있는 경우 조건 */
AND SPAREPART_CATEGORY_ID IN (
SELECT
A.SPAREPART_CATEGORY_ID
FROM S_SPAREPART_CATEGORY A
INNER JOIN
(
/* 대분류 */
SELECT
SPAREPART_CATEGORY_ID
FROM S_SPAREPART_CATEGORY
WHERE SPAREPART_CATEGORY_ID IN (
SELECT
PARENT_ID
FROM S_SPAREPART_CATEGORY
WHERE SPAREPART_CATEGORY_ID IN (SELECT PARENT_ID FROM S_SPAREPART_CATEGORY WHERE UPPER(<if test="language =='' or language =='ko'">SPAREPART_CATEGORY_NAME</if><if test="language == 'en'">SPAREPART_CATEGORY_ENG_NAME</if>) LIKE UPPER('%'||#{SmallName}||'%') AND SPAREPART_CATEGORY_LEVEL = 3)
)
UNION
/* 중분류 */
SELECT
SPAREPART_CATEGORY_ID
FROM S_SPAREPART_CATEGORY
WHERE SPAREPART_CATEGORY_ID IN (SELECT PARENT_ID FROM S_SPAREPART_CATEGORY WHERE UPPER(<if test="language =='' or language =='ko'">SPAREPART_CATEGORY_NAME</if><if test="language == 'en'">SPAREPART_CATEGORY_ENG_NAME</if>) LIKE UPPER('%'||#{SmallName}||'%') AND SPAREPART_CATEGORY_LEVEL = 3)
UNION
/* 소분류 */
SELECT SPAREPART_CATEGORY_ID FROM S_SPAREPART_CATEGORY WHERE UPPER(<if test="language =='' or language =='ko'">SPAREPART_CATEGORY_NAME</if><if test="language == 'en'">SPAREPART_CATEGORY_ENG_NAME</if>) LIKE UPPER('%'||#{SmallName}||'%') AND SPAREPART_CATEGORY_LEVEL = 3
) B ON A.SPAREPART_CATEGORY_ID = B.SPAREPART_CATEGORY_ID
)
</if>
/* 대분류 있는 경우 조건 */
START WITH SPAREPART_CATEGORY_ID IN (
SELECT
SPAREPART_CATEGORY_ID
FROM S_SPAREPART_CATEGORY
WHERE UPPER(<if test="language =='' or language =='ko'">SPAREPART_CATEGORY_NAME</if><if test="language == 'en'">SPAREPART_CATEGORY_ENG_NAME</if>) LIKE UPPER('%'||#{largeName}||'%') AND SPAREPART_CATEGORY_LEVEL = 1
)
CONNECT BY PRIOR SPAREPART_CATEGORY_ID = PARENT_ID
ORDER SIBLINGS BY SPAREPART_CATEGORY_ORDER
'IT > 오라클' 카테고리의 다른 글
| 결과가 없을 때 변수에 null 이라도 대입하기 (0) | 2018.05.15 |
|---|---|
| Oracle SQL Developer 프로지져 결과 보기 (0) | 2018.05.15 |
| 테이블 생성 및 설명 쿼리 (0) | 2018.05.02 |
| 테이블 칼럼, 타입, 설명 조회 (0) | 2016.02.01 |