We have created a awesome themeFar far away,behind the word mountains, far from the countries
INFO ½ÇÀü DB¸ðµ¨¸µ°ú SQL°úÁ¤
¡á¿¹ 8_3_1 || ¸®Äõ½Ãºê - ÇÏÀ§ ¸Þ´º °Ë»öSELECT LEVEL, LPAD(' ',4*LEVEL)|| MENU_ID, MENU_NAMEFROM A_MENU_LISTCONNECT BY PRIOR MENU_ID = UPPER_MENU_IDSTART WITH MENU_ID = 'toc40000';
¡á¿¹ 8_3_2 || ¸®Äõ½Ãºê - ¸Þ´º¸¦ levelº°·Î Á¤·ÄSELECT LEVEL, LPAD(' ',4*LEVEL)||MENU_ID, MENU_NAMEFROM 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);
¡á¿¹ 8_3_3 || ¸®Äõ½Ãºê - »óÀ§ ¸Þ´º °Ë»öSELECT LEVEL, LPAD(' ',4*LEVEL)|| MENU_ID, MENU_NAMEFROM A_MENU_LISTCONNECT BY PRIOR UPPER_MENU_ID = MENU_ID START WITH MENU_ID = 'toc55556';
¡á[SQL 8_3_3]____SELECT LPAD(' ', 2*LEVEL)||COL1, COL2 .FROM TABLE_1CONNECT BY PRIOR ID1 = UPPER_ID1 AND PRIOR ID2 = UPPER_ID2START WITH UPPER _ID1 = 'AAA' AND UPPER _ID2 = '100000';
¡á¿¹ 8_3_4 || ¸®Äõ½Ãºê - ƯÁ¤ °æ¿ì Á¦¿ÜSELECT LEVEL, LPAD(' ',4*LEVEL)|| MENU_ID, MENU_NAMEFROM A_MENU_LISTWHERE MENU_ID <> 'toc40110' --ÀÌ Äڵ常 Á¦¿ÜÇÏ°íCONNECT BY PRIOR MENU_ID = UPPER_MENU_IDSTART WITH MENU_ID = 'toc40000';
¡á¿¹ 8_4_1 || Á¶ÀÎÀ¸·Î Á¶È¸SELECT T1.SABUN, T1.NAME, --T1.POS_GBN_CODE, --'C03' T2.CODE_NAME, --Á÷À§ --T1.CLASS_GBN_CODE, --'B01' T3.CODE_NAME, --µî±Þ T1.PHONE, T1.HP, T1.ADDR1, T1.JOIN_DAYFROM INSA T1, CMM_CODE_DETAIL T2, CMM_CODE_DETAIL T3WHERE T1.POS_GBN_CODE = T2.CODE_NO(+)AND T2.CLASS_CODE(+) = 'C03' --Á÷À§±¸ºÐÄÚµåAND T1.CLASS_GBN_CODE = T3.CODE_NO(+)AND T3.CLASS_CODE(+) = 'B01' --µî±Þ±¸ºÐÄÚµå;
¡á¿¹ 8_4_2 || ¼ºêÄõ¸® Á¶È¸SELECT T1.SABUN, T1.NAME, --T1.POS_GBN_CODE, --'C03' ( SELECT CODE_NAME FROM CMM_CODE_DETAIL T2 WHERE T1.POS_GBN_CODE = T2.CODE_NO AND T2.CLASS_CODE = 'C03' --Á÷À§±¸¹®ÄÚµå) AS POS_GBN_NAME, --Á÷À§¸í --T1.CLASS_GBN_CODE, --'B01' ( SELECT CODE_NAME FROM CMM_CODE_DETAIL T3 WHERE T1.CLASS_GBN_CODE = T3.CODE_NO AND T3.CLASS_CODE = 'B01' --µî±Þ±¸ºÐÄÚµå) AS CLASS_GBN_NAME, --µî±Þ¸í T1.PHONE, T1.HP, T1.ADDR1, T1.JOIN_DAYFROM INSA T1;
¡á¿¹ 8_4_3 || °øÅëÄÚµåÁ¶È¸ FUNCTION ÀÛ¼ºCREATE OR REPLACE FUNCTION GET_CMM_CODE_NAME(V_CLASS_CODE IN CMM_CODE_DETAIL.CLASS_CODE%TYPE, V_CODE_NO IN CMM_CODE_DETAIL.CODE_NO%TYPE)RETURN VARCHAR2ISV_CMM_CODE_NAME CMM_CODE_DETAIL.CODE_NAME%TYPE;
BEGIN
SELECT CODE_NAME INTO V_CMM_CODE_NAME FROM____ CMM_CODE_DETAIL WHERE CODE_NO = V_CODE_NO AND CLASS_CODE = V_CLASS_CODE; RETURN V_CMM_CODE_NAME;EXCEPTION WHEN NO_DATA_FOUND THEN RETURN V_CMM_CODE_NAME; --DBMS_OUTPUT.PUT_LINE('ÀÔ·ÂÇÑ ÄÚµå´Â ¾ø½À´Ï´Ù.'); WHEN TOO_MANY_ROWS THEN DBMS_OUTPUT.PUT_LINE('ÀÚ·á°¡ 2°Ç ÀÌ»óÀÔ´Ï´Ù.'); WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('±âŸ ¿¡·¯ÀÔ´Ï´Ù.');END;/
¡á¿¹ 8_4_4 || FUNCTION ÀÌ¿ëÇÑ °øÅëÄÚµå Á¶È¸SELECT T1.SABUN, T1.NAME, --T1.POS_GBN_CODE, --'C03' GET_CMM_CODE_NAME('C03', T1.POS_GBN_CODE) AS POS_GBN_NAME, --Á÷À§¸í GET_CMM_CODE_NAME('B01', T1.CLASS_GBN_CODE) AS CLASS_GBN_NAME, --µî±Þ¸í T1.PHONE, T1.HP, T1.ADDR1, T1.JOIN_DAYFROM INSA T1;