혹시나 아직 플그래밍 입문이거나 한참 하고있는 넘들중에
쿼리 짤때 동적쿼리로 좀 짜지 마라 두번짜지 마라 세번짜지 마라
아오 조ㄹ 라 빡쳐서 ㄴㅣㅁㅣ 아오 이런 수박
이전에 프로젝트에서 한 초보 코더랑 나름 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;
혹여나 나중에 내 아랫사람이 이따구로 쿼리 코딩하다가 걸리면 진짜 뒤진다
언니 회사이름 노출되도 되는거요?
// dot 어짜피 수주업체라 상관없는데 일단 지웠다
주석이 작성자 정보 하나네-,,
ㄴ 지금 저위 쿼리중 중요한건 주석이 아니자나 주석 잘달아주는건 바라지도 않는다. 동적쿼리라는게 문제다
아 동적쿼리가 안좋은거임?