connect by

<!-- 메뉴 목록을 CMCTB_MENU 테이블에서 조회한다. -->

SELECT * FROM
(
SELECT
SUB_SYS_CD
,SUB_SYS_TP_CD
,MENU_ID
,MENU_NM
,MENU_LVL
,HGRK_MENU_ID
FROM CMCTB_MENU
WHERE 1=1
AND SUB_SYS_TP_CD = 'IX'
/*
#if ($system.length() > 0 )
AND SUBSTR(TRIM(SUB_SYS_CD),1,2) = :system
#end
*/
#if ($subSystem.length() > 0 )
AND SUB_SYS_CD = :subSystem
#end

#if ($menu.length() > 0)
#if ($condition=="MENU_ID")
AND MENU_ID LIKE '%' || :menu || '%'
#end
#if ($condition=="MENU_NM")
AND MENU_NM LIKE '%' || :menu || '%'
#end
#end
)  
START WITH MENU_LVL = 1
CONNECT BY PRIOR MENU_ID = HGRK_MENU_ID
ORDER BY MENU_ID