혹시나 아직 플그래밍 입문이거나 한참 하고있는 넘들중에

쿼리 짤때 동적쿼리로 좀 짜지 마라 두번짜지 마라 세번짜지 마라

아오 조ㄹ 라 빡쳐서 ㄴㅣㅁㅣ 아오 이런 수박

이전에 프로젝트에서 한 초보 코더랑 나름 10년이상 짬밥있다는 차장이 플젝트 님 내에서 한 업무 팀맡아서 하던거 두사람다 나가서

물려 받았는데 프로시져 하나 뜯을때마다 욕이 절로나온다

유지보수 할꺼 생각해서라도 동적쿼리 짜면 진짜 뒤진다

짠사람도 모른다 볼사람도 모른다

한눈에 들어오는게 없다 니미..

어디가 FROM 절이고 어디가 웨어절이고 아놔 미나어미아ㅓ빚럽

절때 동적쿼리 쓰지 말아라.....플그래밍 입문자들 동적쿼리 알려줘도 절대좀 쓰지마라

어쩔수 없다고 동적 쿼리 짜라는것도 잘찾아보면 답 없는거 없다 정적 쿼리좀 쓰자 제발

CREATE OR REPLACE PROCEDURE NEOE.UP_CG_C_CUST_PRE_SELECT1 (
    P_CD_COMPANY          IN    VARCHAR2    DEFAULT NULL,           --  회사코드
    P_DT_SPLY_FROM        IN    VARCHAR2    DEFAULT \'00000000\',     --  공급일자
    P_DT_SPLY_TO          IN    VARCHAR2    DEFAULT \'99999999\',     --  공급일자
    P_CD_ZONE             IN    VARCHAR2    DEFAULT NULL,           --  지역관리소   
    P_CHA_ISPT            IN    VARCHAR2    DEFAULT NULL,           --  차수   
    P_GB_PAYTM            IN    VARCHAR2    DEFAULT NULL,           --  납기구분    
    P_GB_AREA             IN    VARCHAR2    DEFAULT \'\',             --  순로구분
    P_CD_AREA_ISPT        IN    VARCHAR2    DEFAULT \'\',             --  검침구역
    P_CD_DONG             IN    VARCHAR2    DEFAULT \'\',             --  법정동
    P_CD_BLD              IN    VARCHAR2    DEFAULT \'\',             --  건물
    P_GB_HS               IN    VARCHAR2    DEFAULT \'\',             --  주택구분
    P_YN_EMP              IN    VARCHAR2    ,                       --    관리담당자(Y:유, N:무, 공백)     
    P_GB_ISPT             IN    VARCHAR2    ,                       --    안전점검
    RC1                   OUT   SYS_REFCURSOR
)
AS
/*******************************************
**  System          : -- 통합정보시스템
**  Sub System      : 고객관리 > 고객정보
**  Page            : 검침및 점검담당자 등록(건물 조회)
**  Desc            : UP_CG_C_CUST_PRE_SELECT1
**  Return Values
**  작    성    자  : choi
**  작    성    일  : 2010.05.08
**  수    정    자  :
********************************************
** Change History
*********************************************
*********************************************/
V_SELECT VARCHAR2(3000);

V_WHERE1 VARCHAR2(3000);
V_WHERE2 VARCHAR2(2000);
V_WHERE3 VARCHAR2(1000);
V_ORDER  VARCHAR2(1000);

BEGIN
 
   
    --ELSE
    --END IF;
    V_SELECT :=V_SELECT ||\'SELECT   A.CD_COMPANY, A.CD_ZONE,A.CD_DONG,A.CD_BLD,A.NM_BLD, A.NM_TERR, \'\'\'\' AS ERR_MSG, A.CNT, \';
    V_SELECT :=V_SELECT ||\'         A.NEW_ADDR,A.OLD_ADDR,CD_TERR, CD_TERR AS OLD_TERR ,GB_AREA ,GB_PAYTM ,CHK, \'\'\' || P_CHA_ISPT || \'\'\' AS CHA_ISPT,\';
   
    IF P_GB_AREA = \'01\'  THEN  --검침
      V_SELECT :=V_SELECT ||\'       FN_CG_GET_NM(A.CD_COMPANY,\'\'CG_C_CHARG\'\',A.CD_ZONE,\'\'01\'\',A.OLD_TERR) NM_OLD_TERR\'; 
   
    ELSE
       V_SELECT :=V_SELECT ||\'      FN_CG_GET_NM(A.CD_COMPANY,\'\'CG_C_CHARG\'\',A.CD_ZONE,\'\'02\'\',A.OLD_TERR) NM_OLD_TERR\';
    END IF;
    V_SELECT :=V_SELECT ||\'     FROM ( SELECT   A.CD_COMPANY, A.CD_ZONE,\';                              
    V_SELECT :=V_SELECT ||\'                     A.CD_DONG,A.CD_BLD,B.NM_BLD, COUNT(A.NO_CUST) AS CNT, \';
 
    V_SELECT :=V_SELECT ||\'                     C.ADDR_SIDO || \'\' \'\'|| C.ADDR_GUGUN ||  \'\' \'\'|| B.ADDR_ROAD || \'\' \'\' ||B.L_ADDR  || DECODE(NVL(B.S_ADDR,\'\' \'\'), \'\' \'\', \'\'\'\', \'\'-\'\' || B.S_ADDR)  AS NEW_ADDR,\';
    V_SELECT :=V_SELECT ||\'                     C.ADDR_DONG || \'\' \'\'|| B.L_LOT || \'\' \'\'|| DECODE(NVL(B.S_LOT,\'\' \'\'), \'\' \'\', \'\'\'\', \'\'-\'\' || B.S_LOT) AS OLD_ADDR,\';
    IF P_GB_AREA = \'01\'  THEN  --검침
        V_SELECT :=V_SELECT ||\'                 MAX(A.CD_TERR_ISPT) AS CD_TERR,\';
        V_SELECT :=V_SELECT ||\'                 FN_CG_GET_NM(A.CD_COMPANY,\'\'CG_C_CHARG\'\',A.CD_ZONE,\'\'01\'\',MAX(A.CD_TERR_ISPT)) NM_TERR, \';
        V_SELECT :=V_SELECT ||\'                 decode(NVL(MAX(A.CD_TERR_ISPT),\'\' \'\'), \'\' \'\',\';
        V_SELECT :=V_SELECT ||\'                                                       FN_CG_GET_C_FIND_TERR(A.CD_COMPANY,\'\'1\'\',\'\'\'||P_GB_PAYTM ||\'\'\',A.CD_BLD)\';
        V_SELECT :=V_SELECT ||\'                                                       ,MAX(A.CD_TERR_ISPT)) OLD_TERR,\';
        V_SELECT :=V_SELECT ||\'                 \'\'01\'\' GB_AREA,\';
        V_SELECT :=V_SELECT ||\'                 \'\'\'||P_GB_PAYTM ||\'\'\' as GB_PAYTM,\';
    ELSE
        V_SELECT :=V_SELECT ||\'                 MAX(A.CD_TERR_INSP) AS CD_TERR,\';
        V_SELECT :=V_SELECT ||\'                 FN_CG_GET_NM(A.CD_COMPANY,\'\'CG_C_CHARG\'\',A.CD_ZONE,\'\'02\'\', MAX(A.CD_TERR_INSP)) NM_TERR,\';
        V_SELECT :=V_SELECT ||\'                 decode(NVL( MAX(A.CD_TERR_INSP),\'\' \'\'), \'\' \'\',\';
        V_SELECT :=V_SELECT ||\'                 FN_CG_GET_C_FIND_TERR(A.CD_COMPANY,\'\'2\'\',\'\'\'||P_GB_PAYTM ||\'\'\',A.CD_BLD)\';
        V_SELECT :=V_SELECT ||\'                 , MAX(A.CD_TERR_INSP)) OLD_TERR,\';
        V_SELECT :=V_SELECT ||\'                 \'\'02\'\' GB_AREA,\';
        V_SELECT :=V_SELECT ||\'                 \'\'\'||P_GB_PAYTM ||\'\'\' as GB_PAYTM,\';
    END IF;
  
    V_SELECT :=V_SELECT ||\'                     \'\'N\'\' CHK\';
    V_SELECT :=V_SELECT ||\'               FROM  CG_C_CUST A,CG_Z_BLD  B ,CG_Z_DONG C, CG_U_USER D \';
   
    V_WHERE1 := \'                         WHERE A.CD_COMPANY     = \'\'\'||P_CD_COMPANY ||\'\'\'\'; 
    V_WHERE1 := V_WHERE1  ||\'               AND NVL(TRIM(A.DT_MAINCHG), \'\'\'|| P_DT_SPLY_FROM ||\'\'\')     >=  \'\'\'||P_DT_SPLY_FROM ||\'\'\'\';
    V_WHERE1 := V_WHERE1  ||\'               AND NVL(TRIM(A.DT_MAINCHG), \'\'\'|| P_DT_SPLY_FROM ||\'\'\')     <=  \'\'\'|| P_DT_SPLY_TO||\'\'\'\';
    V_WHERE1 := V_WHERE1  ||\'               AND A.CHA_ISPT     =   \'\'\'|| P_CHA_ISPT||\'\'\'\';
    V_WHERE1 := V_WHERE1  ||\'               AND  A.CD_COMPANY = C.CD_COMPANY\';
    V_WHERE1 := V_WHERE1  ||\'               AND  A.CD_DONG    = C.CD_DONG\';
    V_WHERE1 := V_WHERE1  ||\'               AND  A.CD_COMPANY = B.CD_COMPANY\';
    V_WHERE1 := V_WHERE1  ||\'               AND  A.CD_BLD     = B.CD_BLD\';
    V_WHERE1 := V_WHERE1  ||\'               AND  A.CD_COMPANY = D.CD_COMPANY(+)\';
    V_WHERE1 := V_WHERE1  ||\'               AND  A.CD_FCLT_BLD     = D.CD_FCLT_BLD(+)\';
    V_WHERE1 := V_WHERE1  ||\'               AND  A.ST_CUST     NOT IN  (\'\'40\'\', \'\'34\'\')  \';
    IF P_GB_AREA = \'02\' THEN   
        V_WHERE1 := V_WHERE1  ||\'           AND  D.GB_FCLT <> \'\'10\'\' \';
    END IF;
   
    IF NVL(P_GB_PAYTM, \' \') <> \' \' THEN
    V_WHERE1 := V_WHERE1  ||\'               AND A.GB_PAYTM   IN ( SELECT CD_SYSDEF \';
    V_WHERE1 := V_WHERE1  ||\'                                       FROM MA_CODEDTL  \';
    V_WHERE1 := V_WHERE1  ||\'                                       WHERE CD_COMPANY =\'\'\'||P_CD_COMPANY ||\'\'\'\';
    V_WHERE1 := V_WHERE1  ||\'                                       AND CD_FIELD    = \'\'CG_C_00015\'\'\';
    V_WHERE1 := V_WHERE1  ||\'                                       AND CD_FLAG2    =   \'\'\'|| P_GB_PAYTM||\'\'\'\';
    V_WHERE1 := V_WHERE1  ||\'                                                                                   ) \' ;
    END IF;
    IF NVL(P_CD_ZONE,\' \') != \' \' THEN
       V_WHERE1 := V_WHERE1  ||\'            AND A.CD_ZONE        =\'\'\'||   P_CD_ZONE || \'\'\'\';
    END IF;
   
    IF NVL(P_CD_DONG,\' \') != \' \' THEN
       V_WHERE1 := V_WHERE1  ||\'            AND A.CD_DONG        =\'\'\'||   P_CD_DONG || \'\'\'\';
    END IF;
   
    IF NVL(P_CD_BLD,\' \') != \' \' THEN
       V_WHERE1 := V_WHERE1  ||\'            AND A.CD_BLD        =\'\'\'||   P_CD_BLD || \'\'\'\';
    END IF;

    IF NVL(P_GB_HS,\' \') != \' \' THEN
       V_WHERE1 := V_WHERE1  ||\'            AND B.GB_HS         =\'\'\'||   P_GB_HS || \'\'\'\';
    END IF;
       
    IF P_GB_AREA = \'01\' THEN

        IF NVL(P_CD_AREA_ISPT,\' \')  != \' \' AND  P_CD_AREA_ISPT <> \'%\' THEN
        V_WHERE2:= V_WHERE2  ||\'            AND A.CD_TERR_ISPT = \'\'\' ||  P_CD_AREA_ISPT || \'\'\'\';
        END IF;
        IF NVL( P_YN_EMP ,\' \') =\'Y\' THEN--  관리담당자(Y:유, N:무, 공백)
         V_WHERE2:= V_WHERE2  ||\'           AND NVL(A.CD_TERR_ISPT, \'\' \'\') <> \'\' \'\'\';
        ELSIF NVL( P_YN_EMP ,\' \') =\'N\' THEN--  관리담당자(Y:유, N:무, 공백)
         V_WHERE2:= V_WHERE2  ||\'           AND NVL(A.CD_TERR_ISPT, \'\' \'\') = \'\' \'\'\';
        END IF;
    ELSIF P_GB_AREA = \'02\' THEN

        IF NVL(P_CD_AREA_ISPT,\' \')  != \' \'  AND  P_CD_AREA_ISPT <> \'%\' THEN

         V_WHERE2:= V_WHERE2  ||\'           AND A.CD_TERR_INSP = \'\'\' ||  P_CD_AREA_ISPT || \'\'\'\';

        END IF;
        IF NVL( P_YN_EMP ,\' \') =\'Y\' THEN --  관리담당자(Y:유, N:무, 공백)
        V_WHERE2:= V_WHERE2  || \'           AND NVL(A.CD_TERR_INSP, \'\' \'\') <> \'\' \'\'\';
        ELSIF NVL( P_YN_EMP ,\' \') =\'N\' THEN --  관리담당자(Y:유, N:무, 공백)
        V_WHERE2:= V_WHERE2  ||\'            AND NVL(A.CD_TERR_INSP, \'\' \'\') = \'\' \'\'\';
        V_WHERE2:= V_WHERE2  ||\'            AND D.GB_FCLT <> \'\'10\'\' \';
        END IF;
       
        V_WHERE2:= V_WHERE2  || \'           AND B.YM_INSP1 LIKE \'\'\' || P_GB_ISPT || \'\'\' || \'\'%\'\' \';
--        IF (NVL(P_GB_ISPT,\'\') <> \'\') THEN
--            V_WHERE2:= V_WHERE2  || \' AND B.YM_INSP1 = \'\'\' || P_GB_ISPT || \'\'\' \';
--        END IF;
    END IF;
 
    V_WHERE2 := V_WHERE2  ||\'               GROUP BY  A.CD_COMPANY, A.CD_ZONE,A.CD_DONG,A.CD_BLD, B.NM_BLD, C.ADDR_SIDO , C.ADDR_GUGUN,\';
    V_WHERE2 := V_WHERE2  ||\'                           B.ADDR_ROAD ,B.L_ADDR, B.S_ADDR,\';
    V_WHERE2 := V_WHERE2  ||\'                           C.ADDR_DONG, B.L_LOT ,B.S_LOT, DECODE(\'\'\' || P_GB_AREA || \'\'\', \'\'01\'\', A.CD_TERR_ISPT, A.CD_TERR_INSP) )A \';
    V_ORDER := \'                    ORDER BY A.CD_COMPANY,A.CD_DONG,A.CD_BLD \' ;

--   RAISE_APPLICATION_ERROR (-20001, \'UP_CG_C_CUST_SELECT10\' || \'=\' ||  V_WHERE1);
--   RETURN;
DELETE FROM CG_C_TEST;
INSERT INTO CG_C_TEST
(
    TEST_SQL
)
VALUES
(
V_SELECT ||V_WHERE1 ||V_WHERE2 || V_WHERE3 || V_ORDER
);
    OPEN RC1 FOR
    V_SELECT ||V_WHERE1 ||V_WHERE2 || V_WHERE3 || V_ORDER;
  
 
    RETURN;
EXCEPTION
        WHEN OTHERS
        THEN
            RAISE_APPLICATION_ERROR (-20001, \'UP_CG_C_CUST_PRE_SELECT1\' || SQLERRM);
END UP_CG_C_CUST_PRE_SELECT1;

혹여나 나중에 내 아랫사람이 이따구로 쿼리 코딩하다가 걸리면 진짜 뒤진다