|
■예 11_2_1 || 사원번호로 메뉴 구성
____SELECT LEV, MENU_ID, MENU_NAME, PGM_URL||A.PGM_ID PGM_URL , DESCRIPTION
FROM (--담당자의 롤과 관련된 프로그램을 가져온다
SELECT PGM_ID
FROM A_PGM_AUTH
WHERE ROLE_CD = (SELECT ROLE_CD
FROM
( SELECT ROLE_CD
FROM A_ADMIN_REG
WHERE EMP_NO = '200000118' --로그인 사번
UNION ALL
SELECT '02' ROLE_CD
FROM DUAL )
WHERE ROWNUM = 1 )
UNION
SELECT 'X' PGM_ID FROM DUAL ) A,
(--메뉴에 달린 전체를 가져온다
SELECT A.LEV, A.MENU_ID, A.MENU_NAME, DECODE(B.PGM_ID,NULL,'X',B.PGM_ID) PGM_ID, B.DESCRIPTION, B.PGM_URL
FROM ( SELECT LEVEL LEV, MENU_ID, MENU_NAME
FROM A_MENU_LIST
CONNECT BY PRIOR MENU_ID = UPPER_MENU_ID
START WITH MENU_ID IN ( SELECT MENU_ID
FROM____ A_MENU_LIST
WHERE UPPER_MENU_ID IS NULL )
ORDER BY MENU_ID ) A,
( SELECT A.MENU_ID, A.PGM_ID, A.DESCRIPTION, '/'||B.PGM_URL PGM_URL
FROM A_MENU_PGM A,
A_PGM_LIST B
WHERE A.PGM_ID = B.PGM_ID(+) )B
WHERE A.MENU_ID = B.MENU_ID(+) ) B
WHERE A.PGM_ID = B.PGM_ID(+)
AND LEV IS NOT NULL
ORDER BY MENU_ID;
|
INFO
실전 DB모델링과 SQL과정
